Executing SQL Stored Procedure in ADO.Net

1. Ado.net and stored procedure

In ado.net, just like returning data from the table, we can get data by executing the stored procedure as well. A stored procedure can return data to the caller in many forms. They are:

  1. Stored procedure returning data in the form of table
  2. The Stored procedure returning data through output parameter
  3. Stored procedure returning data through return statement.

In this article, we are going to consume these returned data from the front-end C-Sharp application using Ado.Net.

2. Stored Procedures of this Example

This section shows you short detail of about the stored procedure used in this example. This is enough for a front end coder. But to know more about the back-end coding, refer this article: Creating and Using Stored Procedure.

2.1 SQL Procedure Returning Table of Data

A Stored Procedure AuthNameCity returns a table to its caller. The body of this Stored Proc has a single static select statement, and it picks data from the authors table of the pubs DB. Below is this example procedure:

Stored procedure without parameter
Stored Procedure Returning a Table

2.2 SQL Procedure With Parameter

The next procedure named AuthNameCityFilter is to study how we can pass params from Ado.net front-end. This procedure accepts two parameters of type: varchar and char. The body of the procedure uses the arguments (Highlighted in yellow) to return filtered rows back to the caller. This example procedure is below:

SQL Stored Procedure With Parameters
SQL Stored Procedure With Parameters

2.3 SQL Procedure With OUT Param & Return Value

The last procedure is AuthNameCityFilterRet. From ADO.Net front end, we call it to learn how to read the data returned by return statement and the output parameters. Here, we can see the procedure accepts four params and last two params are marked with output keyword. In our example, the procedure body assigns values to the Output params through a simple aggregate function count. The return statement is a row count of the last executed query. In our case, it is always one. Below is the example procedure:

Stored Procedure with Return Value & Output Parameter
Stored Procedure with Return Value & Output Parameter

3. About Ado.Net Stored Procedure Example

The example application that we are going to create is shown in the below picture:

Ado.Net Stored Procedure Example
Ado.Net Stored Procedure Example

In this example, we placed a multi-line text box control in the top portion of the sample application (Marked as 1) and this control shows the data returned by executing the stored procedure. The clear button (Marked as 2) will clear the current content in the multi-line text box. The button marked as 3 in the example executes the stored procedure listed in listing 1.1. Button marked as 4 is used to execute the stored procedure which accepts parameters (Listing 1.2).

Two text box items (Marked as 4 and 5) supply the parameters for the stored procedures in listing 1.2 and 1.3. The button marked as 7 in  our example executes the stored procedure that has a return statement and accepts output parameters (Listing 1.3). The value received is shown in the labels marked as 8 and 9. That is all about this example application and you can see the video at the end of this article to see how it works.

4. Executing Stored Procedure From ADO.NET Front End

Like the select queries, a stored proc can also return data to the caller. In ado.net, running the stored proc from the font-end demands some extra work. This is because a procedure may vary in the way it talks with the caller. Some procedure has return statement, some may accept parameters. In this section, we will write code for the example which we designed in the early section and also learn to execute three procedures examined in section 2.

Before we start coding, in the form-behind code, we will add a using statement for easy access to the SQLClient name space. The code is below:

Code for clearing the output window (Marked as 1) is straightforward. The clear button event handler is below:

Before we write the code, we should set the connection string for the Microsoft supplied Pubs database as part of the application property. You can create the Pubs and NorthWnd database by downloading the script files available in the SQL 2005 Category Page of this website. To know how a connection string can be placed as part of application property, watch the below video:

Video 1: Forming Connection String to Pubs DB

Forming Connection String for Our Example

4.1 Stored Proc Returning a Table

A stored procedure returning table of data in ADO.Net point of view is executing a select query. In ado.net, all the work goes same as what we do for executing a select query. The only difference is, in the command object we specify command type as Stored Procedure. Let us explore the code for the click event handler of the button marked as 3. Listing 2.1 towards the end of this article has complete code for this handler. Now in the sub-section of 4.1, we will learn more about code Listing 2.1.

4.1.1 SQL DB Connection

First, we get the connection string for the pubs database from the application settings. Once connection string is on hand, we create SqlConnection object supplying this connection string to it. Then the open method call will connect to pubs DB. The code is below:

4.1.2 Setting CommandText and CommandType

After setting up the SqlCommand object, we set the Procedure name in the CommandText property which we will execute on the DB. Also note, via  CommandType property we tell to ado.net that we will run a stored procedure and do not expect a select query in the CommandText property. The enum  CommandType.StoredProcedure is set to the CommandType property. Code for this is below:

4.1.3 Iterate Stored Procedure Result via SqlDataReader

Once the SqlCommand is set properly, we can call the ExecuteReader method in it. This call will run the stored procedure in the SQL Server DB. The stored procedure will return the result of select query and the call to  ExecuteReader method on the command that we set in the past section return the  SqlDataReader. So, the SqlDataReader has the result of running the stored procedure. We iterate this reader object to fetch the table of data returned by the stored procedure. Code is below:

4.2 Supply Procedure Parameters via AddWithValue

The Parameter Collection of the SqlCommand object is a list of one or more parameter(s) expected by the stored procedure. The collection has a method called  AddWithValue which we can use to add a parameter. Here, we supply the parameter name with @ symbol as first param and value as second param. The entire handler for the button marked as 4 is given in Listing 2.2 at the end of this article. Let us explore this event handler:

4.2.1 Fill Parameters Collection via AddWithValue()

In this section, we pack the SqlCommand object with the parameters. We make a call to the AddWithValues() function which is exposed by the Parameters collection of SqlCommand object. The parameter names @City, @State (Look at the procedure in Listing 1.2) are supplied as first parameter to the call AddWithValue. Also, the second parameter to the function AddWithValue supplies actual value to the stored procedure parameter. In our case, we supply the actual values from the text boxes marked as 5 and 6. Code is below:

4.2.2 Check for Valid Data

As we are passing the parameters to the Stored Procedure, there is a chance that procedure may not return any data. This is because of the where clause of the procedure. So, we need to check executing the stored procedure returns any valid data. Ado.Net sets  HasRows Boolean property of the SqlDataReader to true when there are valid rows in the SqlDataReader.  Code is below:

Rest of the event handler is same as that of the previous one discussed.

4.3 Ado.net Dealing With Output Procedure Parameter

Ado.net reads the return value from the stored procedure as a parameter. In the past section, we saw how to add a parameter to the parameter collection by making use of the function call  AddWithValue. The same way we can also read output parameter from the Parameter collection, but after the procedure execution. To deal with the output parameter and return value, we need to construct the  SqlParameter object and then set its  Direction property. This direction property is the key for defining the value movement. When we does not point out the direction, Ado.net treats data flow is from Front-End to the stored procedure. To set the direction, we use ParameterDirection Enum. The click event handler for the button marked as 7 is provided in the Listing 2.3 at the end of this article.

4.3.1 Output Parameter as SqlParameter

To control the parameter flow, first we should create an object of type: SqlParameter. For output param, we should set the Direction property with a constant from the Enum:  ParameterDirection.Output. This tells the command object to expect value in the out param after the procedure execution. In our example, we created two output params for running the procedure AuthNameCityFilterRet. After creating these params with proper direction, we add them to the parameter collection. The code for this is below:

4.3.2 Return Value as SqlParameter

Just like the output param, ADO.Net treats the return value also as a param. We can inform this to Ado.net by setting the parameter direction with the Enum constant:  ParameterDirection.ReturnValue. The code is below:

4.3.3 Reading Output Param and Return Value

After running the procedure in SQL DB via ADO.Net command object, we can read the values from the output param and return value param. Ado.net packs these values in the parameter collection of the  SqlCommand object.  The below code shows reading those values and showing them in the Multi-line text box control and the label controls. These controls are marked as 8 and 9 in the sample form screenshot.

5. Running the application

You can download the sample and run it after creating the Stored Procedures in SQL Server. Also adjust the connection string property such that it is valid for your box. After this, you can run the example with no concern. The video below shows executing the SQL DB Stored Procedures from the Front-End:

Video 2: Running the Example

6. Code Listings

Listing 1.1

Listing 1.2

Code Listing 1.3

Listing 2.1

Listing 2.2

Code Listing 2.3

Source Code: Download Ado.Net Execute Stored Procedure Example (Google Drive)

Do you like this Example? Please comment about it for others!!

This site uses Akismet to reduce spam. Learn how your comment data is processed.