Data Entry and Update Using Stored Procedures

Probably the best attribute of a stored procedure is that it allows the developer to have a direct programmatic with to the back-end database. Based on this relationship, you can use a stored procedure to perform data entry.

To create a new record in a table, you can use the INSERT TABLE expression of the SQL. If the table doesn't have a primary key, you can create an argument for each column of the table. If the table has a primary key, you can create an argument for each column of the table but you should/must omit one for the primary key. Here is an example of such a procedure from a database called Familia that has a table named Persons with the PersonID(Primary Key), FirstName, LastName, GenderID, and Notes columns:

IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'CreateNewRecord' 
	   AND 	  type = 'P')
    DROP PROCEDURE CreateNewRecord
GO

CREATE PROCEDURE CreateNewRecord 
  @FirstName VarChar(20),
  @LastName VarChar(20),
  @Gender int,
  @Notes Text
AS
  INSERT Persons(FirstName, LastName, GenderID, Notes)
              VALUES(@FirstName, @LastName, @Gender, @Notes)
GO

After creating the stored procedure, you can then create an application design the form to include the fields for columns represented in the table.

General Data Update Using a Stored Procedure

When a user is navigating through records and find information that is not accurate, if the user makes a change, you must provide a mechanism to keep the updated value as we did in the previous lesson. This scenario that involves changing existing data of records can be handled by a stored procedure. This is usually done by including an UPDATE statement as part of the procedure.

Criterion-Based Data Update Using a Stored Procedure

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. This type of assignment can be handled by a stored procedure by including an UPDATE statement as part of the procedure. 

Practical Learning: Updating Data Using a Stored Procedure

1. In Server Explorer, right-click the Stored Procedure node of the SuperMarket database and click New Stored Procedure
2. Change the statement as follows:
    
3. Save and close the procedure window
4. Open the SuperMarket1 project
5. Display the form (Form1.cs [Design]) and change its design as follows:
    

   Control Name Caption/Text Other Properties Label   Set the company's new minimum salary to $ Anchor: Bottom, Left TextBox txtNewMinSal 6.55 TextAlign: Right Anchor: Bottom, Left Button btnNewMinSal Submit Anchor: Bottom, Left

6. Double-click the new Submit button and implement its Click event as follows:
    

   private void btnNewMinSal_Click(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("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 = "@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("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, "Employees"); // Update the information displayed in the datagrid this.sqlDataAdapter1.Fill(this.dsEmployees1, "Employees"); }

7. Execute the application
    
8. Close the form