Introduction:
In this article,i am going to explain about how to effectively use stored procedures in asp.net application development.

Main:
Why Should we use stored procedures?

Maintenance: Hard-coded queries on individual pages mean a string of SQL on each and every one of those pages. If you use the same query on several pages and need to change the query, you have to make the changes in every page. Stored procedures make maintaining the site easier by having only one copy of the query.

Security: Allowing direct access to the tables within the database to applications, as you’ve already seen in Chapter 2, forces you to grant “too much” access to the database. By using stored procedures, you allow the user access to the tables only through stored procedures, and you can apply suitable controls.

Speed: Depending on the database server that you’re using, you may also gain a speed advantage from using stored procedures. If you’re using MySQL 5.0 and pass a query to the server, the query must be parsed and an execution plan calculated for the query. If you pass the same query three times, MySQL 5.0 calculates the execution plan three times. MySQL 5.0 stored procedures are cached when they’re created, and thereafter the precached versions are used. With SQL Server 2005, stored procedures do not process much more quickly than queries, because it also caches and reuses the execution plans of queries passed directly.

Reduced network traffic: Stored procedures allow you to process the results at the database and return only the required results to the page.

Passing Parameters to Stored Procedures

myCommand.CommandText = "spGetPlayersByManufacturer"; myCommand.CommandType = CommandType.StoredProcedure;   // get the manufacturer value from the querystring string strManufacturerID = Request.QueryString["manufacturerid"];   // determine the correct value as an integer int intManufacturerID = 0; if (strManufacturerID != null) { intManufacturerID = Convert.ToInt32(strManufacturerID); }   // create the parameter SqlParameter myParameter1 = new SqlParameter(); myParameter1.ParameterName = "@manufacturer"; myParameter1.SqlDbType = SqlDbType.Int; myParameter1.Value = intManufacturerID;   // add it to the command object myCommand.Parameters.Add(myParameter1);   // open the database connection myConnection.Open();   // run query SqlDataReader myReader = myCommand.ExecuteReader();

This simple example demonstrates that passing parameters to stored procedures is the same as passing parameters to queries.

You call a stored procedure by creating a Command object. You set the CommandText to the name of the stored procedure, spGetPlayersByManufacturer, and the CommandType to CommandType. StoredProcedure. You then check to see whether a ManufacturerID has been added to the query string. As you know, Request.QueryString returns null if the requested value isn’t present, and you use this fact to default to a ManufacturerID of 0 if the query string value isn’t present:

// do we need to add the @manufacturer parameter
string strManufacturerID = Request.QueryString["manufacturerid"];

// determine the correct value as an integer
int intManufacturerID = 0;
if (strManufacturerID != null)
{
intManufacturerID = Convert.ToInt32(strManufacturerID);
}

You then need to add the parameter to the Command object. You create a SqlParameter object and give it the correct name. Because you’re using the SqlCommand object, you need to use the name that the stored procedure expects, so you use @manufacturer, like so:

// create the parameter
SqlParameter myParameter1 = new SqlParameter();
myParameter1.ParameterName = “@manufacturer”;

You then specify the type of the parameter from the SqlDbType enumeration and set the value of the parameter to the value from the query string, like so:

myParameter1.SqlDbType = SqlDbType.Int;
myParameter1.Value = intManufacturerID;

Once the parameter has been created and correctly populated, you can add it to the Parameters collection of the SqlCommand object, like so:

// add it to the command object
myCommand.Parameters.Add (myParameter1);

You then use the ExecuteReader() method of the SqlCommand object to return a SqlDataReader object and bind this to the data grid.

When the stored procedure is executed, the route that’s taken depends on the value passed as the parameter to the SqlCommand object, as discussed when you created the stored procedure in the previous example.

Returning Parameters from Stored Procedures

SqlParameter myParameter2 = new SqlParameter(); myParameter2.ParameterName = "@rowcount"; myParameter2.SqlDbType = SqlDbType.Int; myParameter2.Direction = ParameterDirection.Output; myCommand.Parameters.Add (myParameter2);

You then execute the stored procedure as you normally would, bind the reader to the GridView, and then close the DataReader. Once the DataReader has been closed, you can retrieve the values of the output parameters simply by using the name of the parameter as the index to the Parameters collection of the Command object, like so:

// now get the output parameter
Label1.Text = Convert.ToString(myCommand.Parameters["@rowcount"].Value);

You’re after the value of the parameter, so you use the Value property. This property returns an Object that you can then cast to whatever type you want. In this case, you want to set the Text property of a Label, so you convert the value to a string.

When using the Command object and output parameters, it’s important to remember that you must close the DataReader object you’re using before you can access the output parameters. If it isn’t closed, the output parameters will not be populated with the results you expect.

Conclusion:
Hope this helps,
Happy Coding.