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)