In my previous article I explained Calling Select SQL Server Stored Procedures using ADO.Net

If you need to know more about the syntax of Stored Procedures refer my article Using Stored Procedures in SQL Server Database

Here I am explaining how to call stored procedures that insert data into the SQL Server Database using ADO.Net.

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



Insert Stored Procedures

Here is an example of Simple Insert Stored procedure which does not return anything. It just takes the values of the new record to be added as parameters and inserts the same.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[AddEmployee]

      @FirstName varchar(50),

      @LastName varchar(50),

      @BirthDate datetime,

      @City varchar(50),

      @Country varchar(50)

AS

BEGIN

      SET NOCOUNT ON;

      INSERT INTO  Employees (FirstName, LastName, BirthDate, City, Country)

      VALUES (@FirstName, @LastName, @BirthDate, @City, @Country)

END

 

And here is how I call it in the ASP.Net Web page. As you will notice I am passing the values to the parameters @FirstName, @LastName, @BirthDate, @City and @Country using the TextBoxes txtFirstName, txtLastName, txtBirthDate, 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 = "AddEmployee";

cmd.Parameters.Add("@FirstName",SqlDbType.VarChar).Value =  txtFirstName.Text.Trim();

cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text.Trim();

cmd.Parameters.Add("@BirthDate", SqlDbType.DateTime).Value = txtBirthDate.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 inserted 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 = "AddEmployee"

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text.Trim()

cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text.Trim()

cmd.Parameters.Add("@BirthDate", SqlDbType.DateTime).Value = txtBirthDate.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 inserted successfully"

Catch ex As Exception

      Throw ex

Finally

      con.Close()

      con.Dispose()

End Try

 

This completes the Part 2 of the article on Stored Procedures. You can download the code in C# and VB.Net along with the Stored Procedure SQL Scripts using the link below

InsertStoredProc.zip (4.18 kb)


<< Part - I Calling Select SQL Server Stored Procedures using ADO.Net

Part - III Getting ID of the newly inserted record in SQL Server Database using ADO.Net >>