Sunday, 17 February 2013

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 in, 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
    INSERT INTO TestDB_RegisterUser (registeruser_id,registeruser_username,registeruser_email,registeruser_password
    VALUES (@ID,@Username,@Email,@Password,@Createdon,@Modifiedon,@Rowstate)

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
        // execute the command
        ErrorMessage.Text = "Registered successfully.";                   
    catch(Exception ex)
        throw new Exception(ex.Message);

No comments:

Post a Comment