Introduction

A stored procedure is a group of Transact-SQL statements compiled into a single execution plan. A stored procedure (sometimes called a proc, sproc, StoPro, StoredProc, sp or SP) is actually stored in the database data dictionary. Before starting how to call a stored procedure using c#.net in asp.net, letus take a look at the pros and cons of stored procedures

Pros

1. Reduced server/client network traffic
2. Stronger security
3. Reuse of code
4. Easier maintenance
5. Improved performance

Cons

1. Stored procedures are just as fast as parameterized queries but are not any faster after the first run.
2. Writing and maintaining stored procedures is most often a specialized skill set that not all developers possess.

Creating a Stored Procedure

• Open Sqlserver 2005 or 2008 management studio
• In Object Explorer, connect to an instance of Database Engine and then expand that instance.
• Select the database on which you want to create a SP
• Right Click on the databse select 'New Query'
• Or you can create a SP by expanding Database and expanding Programmability then Right-click Stored Procedures, and then click New Stored Procedure. This will give you a template in which you can write a stored procedure
• Once finished writing the stored procedure, open 'Query' menu in menu bar and click on 'F5' or simply press 'F5' to execute it.

The example explained here uses a database named 'TestDB'. And the aspx form is used to insert a record of user into the database table 'TestDB_RegisterUser' to register a user. To create DB and for other details please see the article

Now start writing SP as shown below.

Create Procedure RegisterUser
    @ID nvarchar(38),
    @Username varchar(250),
    @Password varchar(20),
    @Email varchar(100),
    @Createdon DateTime,
    @Modifiedon DateTime,
    @Rowstate TinyInt
As 
Begin
    INSERT INTO TestDB_RegisterUser (registeruser_id,registeruser_username,registeruser_email,registeruser_password
    ,registeruser_createdon,registeruser_modifiedon,registeruser_rowstate)
    VALUES (@ID,@Username,@Email,@Password,@Createdon,@Modifiedon,@Rowstate)
End

In the above code, 'Create Procedure' creates a SP with name 'RegisterUser'. The lines started with '@' are the parameters that passed to the SP. These are to be defined before the statement with datatypes that equivalent to column datatypes.

Now in the button click event write the following code

 using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["CM_Connection"].ConnectionString))
{
    SqlCommand cmd = new SqlCommand();
    // create a command object 
    Guid guid;
    guid = Guid.NewGuid();               
    string sql = "RegisterUser";
    //Write the stored procedure name here
    cmd.Parameters.AddWithValue("@ID", guid);
    cmd.Parameters.AddWithValue("@Username", UserName.Text.Trim());
    cmd.Parameters.AddWithValue("@Password", Password.Text);
    cmd.Parameters.AddWithValue("@Email", Email.Text.Trim());
    cmd.Parameters.AddWithValue("@Createdon", DateTime.Now);
    cmd.Parameters.AddWithValue("@Modifiedon", DateTime.Now);
    cmd.Parameters.AddWithValue("@Rowstate", 1);
    // add parameter to command, which will be passed to the stored procedure 
    cmd.Connection = con;
    //set connection instance to command object
    cmd.CommandText = sql;
    //set the commandtext property to command object, it would be T-Sql statement, 
    table name or a stored procedure name
    cmd.CommandType = CommandType.StoredProcedure;
    // set the command object so it knows to execute a stored procedure
    con.Open();
    try
    {
        cmd.ExecuteNonQuery();
        // execute the command
        ErrorMessage.Text = "Registered successfully.";                    
    }
    catch(Exception ex)
    {
        throw new Exception(ex.Message);
    }
}

Thus you can call a stored procedure as simple as shown in above code.