Working with Stored Procedure in ASP.NET

This article is regarding working with stored procedure, i.e call a stored procedure from a web application. Here we will call a stored procedure to insert data to one of the table in the database.
Step 1: Creating the stored procedure
I have SQL Express 2005 installed, you may need to download the SQL server Management Studio express from microsoft website. Open the Management Studio then create a new database lets say "test". Then create a new Table called "login" with the following columns:

id (primary key) varchar(50)

password varchar(50)

email varchar(50)

No go to "Programmability">>"Stored Procedure">>right click>>"New Stored Procedure"

Here following is the stored procedure to insert values to the table we just created:

CREATE PROCEDURE submitrecord
(
@id varchar(50),
@password varchar(50),
@email varchar(50)
)
AS
insert into login (id, password,email)values (@id,@password,@email)
and save it.

Step 2: Execute the stored procedure from web application
From the Visual Studio create a new Web Application let it be "storedproc"
Design the default.aspx as following:
Now in the default.aspx.cs should look like this:


using System;
using System.Data;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
DataSet ds = new DataSet();
SqlConnection con;
SqlCommand cmd = new SqlCommand();
SqlParameter sp1 = new SqlParameter();
SqlParameter sp2 = new SqlParameter();
SqlParameter sp3 = new SqlParameter();
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
con = new SqlConnection("Trusted_Connection=Yes;database=test;data source=.\SQLEXPRESS;");
con.Open();
cmd = new SqlCommand("submitrecord", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@id", SqlDbType.VarChar).Value = txtId.Text;
cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = txtpwd.Text;
cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = txtemail.Text;
cmd.ExecuteNonQuery();
con.Close();
}
}

The code is self explainatory, and the main issue any person faces is with the connection string.