Calling Update SQL Server Stored Procedures using ADO.Net

his 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.