Using Stored Procedure in ASP.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.
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.
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.
Now we create a stored procedure in the database named storlog. The stored procedure looks like Figure 2.
Step 3: Calling stored procedure
Taking three TextBoxes, one Button and a Label control on the form, the form looks like this.
Now double-click on the Save button control and add the following code.
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);
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;
Label1 .Text= "record has been saved";
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.
Now run the application and enter the username, password and Id in the textboxes.
The form looks like this.
Now click on the save Button. All record has been saved in the database.
Now open the database and test it.