Inserting records into a database table using a stored procedure and ADO.NET

This article demonstrates how to use ASP.NET and ADO.NET with Visual C# .NET to create and to call a Microsoft SQL Server stored procedure.

Stored Procedure

A stored procedure is a batch of Transact-SQL statements (like select, insert and update) compiled into a single execution that can be re-used. If you find yourself using the same query over and over again, it would make sense to put them into a stored procedure. Every time you write a query it is parsed in the database. If you have written a stored procedure for it, it will be compiled once and can be executed multiple times.

There are two steps involved in executing a stored procedure from your program. First, you set the command object property CommandText as the stored procedure name; second, you set the CommandType property as CommandType.StoredProcedure.

Step 1

Now we Create a table in a database named logintab and it has the three columns Login, Password and Id.
The Database table looks like the Figure 1 shown below.



Figure 1

Step 2

Now we create a stored procedure in the database named storlog. The stored procedure looks like Figure 2.



Figure 2

Step 3: Calling stored procedure

Taking three TextBoxes, one Button and a Label control on the form, the form looks like this.



Figure 3

Now double-click on the Save button control and add the following code.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.Data;

 

namespace StoreProcedure

{

    public partial class WebForm1 : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

        }

        protected void ButtonSave_Click(object sender, EventArgs e)

        {

            string str = "Data Source=.;uid=sa;pwd=Password$2;database=master";

            SqlConnection con = new SqlConnection(str);

            con.Open();

            SqlCommand com = new SqlCommand("storlog", con);

            com.Parameters.Add("@username", SqlDbType.VarChar).Value = TextBoxUsername .Text;

            com.Parameters.Add("@Password", SqlDbType.VarChar).Value = TextBoxPassword.Text;

            com.Parameters.Add("@Id", SqlDbType.Int).Value = TextBoxId.Text ;

            com.CommandType = CommandType.StoredProcedure;

            com.ExecuteNonQuery();

            Label1 .Text= "record has been saved";

            con.Close();

        }

    }

}

The above code defines the connection with the database and command object property CommandText as the stored procedure name; second, you set the CommandType property as CommandType.StoredProcedure.

Step 4

Now run the application and enter the username, password and Id in the textboxes.

The form looks like this.



Figure 4

Step 5

Now click on the save Button. All record has been saved in the database.



Figure 5

Step 6

Now open the database and test it.



Figure 6