This is the last part of the SQL Server Stored Procedure series where I will explain how to call and create stored procedures that update existing data in the SQL Server database using ADO.Net Classes.
If you need to know more about the syntax of Stored Procedures refer my article Using Stored Procedures in SQL Server Database
For this tutorial I am using the Employees Table of NorthWind Database. You can download the NorthWind Database from here
Connection String
<connectionStrings>
<add name="conString"
connectionString="Data Source=.\SQLEXPRESS;
database=Northwind;Integrated Security=true"/>
</connectionStrings>
Namespaces
You will need to import the following namespaces
C#
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Update Stored Procedures
Below is an Update stored procedure that takes FirstName and LastName of the employee as parameters and based on that it updates the city and country of the employee.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UpdateEmployeeDetails]
@FirstName varchar(50),
@LastName varchar(50),
@City varchar(50),
@Country varchar(50)
AS
BEGIN
SET NOCOUNT ON;
UPDATE Employees SET City = @City, Country = @Country
WHERE FirstName=@FirstName AND LastName=@LastName
END
Now in order to execute the above stored procedure in your ASP.Net Web applications refer the code below. As you will notice I am passing the values to the parameters @FirstName, @LastName, @City and @Country using the TextBoxes txtFirstName, txtLastName, txtCity and txtCountry respectively. Since the stored procedure does not return anything I am using the ExecuteNonQuery method of the SQL Command object
C#
String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "UpdateEmployeeDetails";
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text.Trim();
cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text.Trim();
cmd.Parameters.Add("@City", SqlDbType.VarChar).Value = txtCity.Text.Trim();
cmd.Parameters.Add("@Country", SqlDbType.VarChar).Value = txtCountry.Text.Trim();
cmd.Connection = con;
try
{
con.Open();
cmd.ExecuteNonQuery();
lblMessage.Text = "Record updated successfully";
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
con.Dispose();
}
VB.Net
Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
Dim con As New SqlConnection(strConnString)
Dim cmd As New SqlCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "UpdateEmployeeDetails"
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text.Trim()
cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text.Trim()
cmd.Parameters.Add("@City", SqlDbType.VarChar).Value = txtCity.Text.Trim()
cmd.Parameters.Add("@Country", SqlDbType.VarChar).Value = txtCountry.Text.Trim()
cmd.Connection = con
Try
con.Open()
cmd.ExecuteNonQuery()
lblMessage.Text = "Record updated successfully"
Catch ex As Exception
Throw ex
Finally
con.Close()
con.Dispose()
End Try
This completes the final part of stored procedure article. You can download the code in VB.Net and C# for the above part using the link below.
UpdateStoredProc.zip (4.12 kb)