Calling a Stored Procedure using Asp.Net, C#.Net and Sqlserver

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

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

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.

Creating a Simple Registration Form

Now in the button click event write the following code

{

    SqlConnection con = new SqlConnection(" your sql connection");

    SqlCommand cmd = new SqlCommand();

    // create a command object 

               

    string sql = "RegisterUser";

    //Write the stored procedure name here

    cmd.Parameters.AddWithValue("@ID", ID.text);

    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);

    }

}