Updating Records Using a Stored Procedure

Introduction

One of the jobs of a database developer or administrator is to update records when there is a new request. For example, imagine a company has decided to change the minimum salary of all employees and the manager asks you to make this change. If the company is small as having less than 10 employees, you can easily open the table or the form that holds the employees records, manually examine the employees with the lowest salary, and then change those who have a salary below the company's new set. You can also create an update query that would perform the task. The job here is to check all salaries but to make a change only to those that respond to a specific criterion. For example, if you must change the minimum salary to 8.55, your change would affect only those employees who are making less than $8.55/hr.

Practical Learning: Updating Records Using a Stored Procedure

  1. We will continue with the SuperMarket database created in our introduction to procedures and continued in procedures with arguments

    Open SQL Query Analyzer with a new blank window and create a new procedure by typing the following code:
     
    -- creating the store procedure
    IF EXISTS (SELECT name 
    	   FROM   sysobjects 
    	   WHERE  name = N'SetNewMinSalary' 
    	   AND 	  type = 'P')
        DROP PROCEDURE SetNewMinSalary
    GO
    
    CREATE PROCEDURE SetNewMinSalary 
    	@NewSalary SmallMoney
    AS
    	UPDATE Employees
            SET Salary = @NewSalary
    	FROM Employees WHERE Salary < @NewSalary
    GO

  2. Execute the statement (F5)
  3. Start Visual C++ .NET and create a new Windows Forms Application named SuperMarket3
  4. In Server Explorer, expand the server that holds the above database and the database itself followed by its Tables node
  5. Drag Employees to the form
  6. On the main menu, click Data -> Generate Dataset...
  7. Change the name of the dataset to dsEmployees and click OK
  8. Design the form as follows:
     
    Control Name Caption/Text Other Properties
    Form     StartPosition: CenterScreen
    DataGrid     Anchor: Top, Bottom, Left, Right
    AutoFormat: Professional 3
    DataSource: dsEmployees1.Employees
    Label   Set the company's new minimum salary to $ Anchor: Bottom, Left
    TextBox txtNewMinSal 6.05 TextAlign: Right
    Anchor: Bottom, Left
    Button btnNewMinSal Submit Anchor: Bottom, Left, Right
  9. Double-click an empty the form to access its Load event
  10. Return to the form and double-click the bottom Submit button
  11. Implement the events as follows:
     
    #pragma once
    
    namespace SuperMarket1
    {
    	using namespace System;
    	using namespace System::ComponentModel;
    	using namespace System::Collections;
    	using namespace System::Windows::Forms;
    	using namespace System::Data;
    	using namespace System::Drawing;
    	using namespace System::Data::SqlClient;
    
    	/// <summary> 
    	/// Summary for Form1
    	///
    	/// WARNING: If you change the name of this class, you will need to change the 
    	///          'Resource File Name' property for the managed resource compiler tool 
    	///          associated with all .resx files this class depends on.  Otherwise,
    	///          the designers will not be able to interact properly with localized
    	///          resources associated with this form.
    	/// </summary>
    	public __gc class Form1 : public System::Windows::Forms::Form
    	{	
    	public:
    		Form1(void)
    		{
    			InitializeComponent();
    		}
      
    			
    
    		}	
    
    private: System::Void Form1_Load(System::Object *  sender, System::EventArgs *  e)
    		 {
    			 this->sqlDataAdapter1->Fill(this->dsEmployees1);
    		 }		
    
    private: System::Void btnNewMinSal_Click(System::Object *  sender, System::EventArgs *  e)
    	 {
    		 // Create a new SqlCommand command, passing it the name of
    		 // the stored procedure we want to call and attaching it to 
    		 // the existing SQL connection
    		 SqlCommand* cmdNew     = new SqlCommand(S"SetNewMinSalary", this->sqlConnection1);
    		 SqlDataAdapter *sdaNew = new SqlDataAdapter(cmdNew);
    		 DataSet * dsEmployees  = new DataSet();
    
    		 // We will need a SQL parameter to carry the argument
    		 // Declare its variable
    		 SqlParameter *parNew   = new SqlParameter();
    		 // Specify the name of the argument
    		 parNew->ParameterName  = S"@NewSalary";
    		 // Specify the SQL data type of the argument
    		 parNew->SqlDbType      = SqlDbType::SmallMoney;
    		 // Specify the value passed as argument
    		 parNew->Value          = this->txtNewMinSal->Text;
    
    		 // Once the argument is ready, add it to the list of arguments
    		 cmdNew->Parameters->Add(parNew);
    		 // Let the SQL command know the type of command we are going to use
    		 // In this case, it is a stored procedure
    		 cmdNew->CommandType = CommandType::StoredProcedure;
    
    		 // Inform the user about the action that is going to occur
    		 // (You can also do this after the action has been carried
    MessageBox::Show(String::Concat(S"The new minimum salary throughout the company has been set to $",
    		                                 this->txtNewMinSal->Text));
    
    		 // Update the data set with the new information from the data adapter
    		 sdaNew->Fill(dsEmployees, S"Employees");
    		 // Update the information displayed in the datagrid
    		 this->sqlDataAdapter1->Fill(this->dsEmployees1, S"Employees");
    	}
    };
    }
  12. Test the application
     

 

   

Home Copyright 2004 FunctionX, Inc.