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:
- Stored procedure returning data in the form of table
- The Stored procedure returning data through output parameter
- 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:
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:
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:
3. About Ado.Net Stored Procedure Example
The example application that we are going to create is shown in the below picture:
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:
|
1 2 |
//Sample 01: Namespace for the SqlClient using System.Data.SqlClient; |
Code for clearing the output window (Marked as 1) is straightforward. The clear button event handler is below:
|
1 2 3 4 |
private void btnClear_Click(object sender, EventArgs e) { txtOutput.Text = ""; } |
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
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:
|
1 2 3 4 |
//Sample 2.1: Open connection to Pubs Db string con_str = Properties.Settings.Default.PubsDB; SqlConnection PubsCon = new SqlConnection(con_str); PubsCon.Open(); |
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:
|
1 2 3 4 5 |
//Sample 2.2: Create Command for Stored Procedure SqlCommand cmd = new SqlCommand(); cmd.CommandText = "AuthNameCity"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = PubsCon; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
//Sample 2.3: Iterate through the data SqlDataReader Reader = cmd.ExecuteReader(); while (Reader.Read()) { string data; data = string.Format("Author Name:{0}" + Environment.NewLine + "From: {1}, {2}", Reader["AuName"], Reader["City"], Reader["State"]); txtOutput.Text = txtOutput.Text + data + Environment.NewLine; txtOutput.Text = txtOutput.Text + "=======================================================" + "====================" + Environment.NewLine; } |
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:
|
1 2 3 |
//Sample 3.3: Pack Parameters cmd.Parameters.AddWithValue("@City", txtCity.Text); cmd.Parameters.AddWithValue("@State", txtState.Text); |
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:
|
1 2 3 4 5 6 7 8 9 |
//Sample 3.4: Check Data Present for the passed-in parameters SqlDataReader Reader = cmd.ExecuteReader(); if (Reader.HasRows == false) { txtOutput.Text = "No Data Available for supplied input"; Reader.Close(); PubsCon.Close(); return; } |
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:
|
1 2 3 4 5 6 7 8 9 |
//Sample 4.3.2: Output Parameter SqlParameter param_out1 = new SqlParameter("@TotalAuthInSameCity", SqlDbType.Int); param_out1.Direction = ParameterDirection.Output; SqlParameter param_out2 = new SqlParameter("@TotalAuthInSameState", SqlDbType.Int); param_out2.Direction = ParameterDirection.Output; cmd.Parameters.Add(param_out1); cmd.Parameters.Add(param_out2); |
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:
|
1 2 3 4 5 |
//Sample 4.3.3: Return value as Parameter SqlParameter retValue = new SqlParameter(); retValue.ParameterName = "@ReturnValue"; retValue.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(retValue); |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
//Sample 4.4: Get return output (Return Value and Output parameter) txtOutput.Text = txtOutput.Text + "Number of author in same city (OutParam1) : " + cmd.Parameters["@TotalAuthInSameCity"].Value.ToString() + Environment.NewLine; txtOutput.Text = txtOutput.Text + "Number of author in same State (OutParam2) : " + cmd.Parameters["@TotalAuthInSameState"].Value.ToString() + Environment.NewLine; txtOutput.Text = txtOutput.Text + "Total Number of records found (Return Value) :" + cmd.Parameters["@ReturnValue"].Value.ToString() + Environment.NewLine; lblOutVal.Text = cmd.Parameters["@TotalAuthInSameCity"].Value.ToString() + ", " + cmd.Parameters["@TotalAuthInSameState"].Value.ToString(); lblReturnVal.Text = cmd.Parameters["@ReturnValue"].Value.ToString(); |
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
|
1 2 3 4 5 6 7 |
Create Procedure AuthNameCity As Begin Select Au_FName + ', ' + au_lname as 'AuName', City, State From Authors; End; Go |
Listing 1.2
|
1 2 3 4 5 6 7 8 9 |
Create Procedure AuthNameCityFilter @City as varchar(20), @State as Char(2) As Begin Select Au_FName + ', ' + au_lname as 'AuName', City, State From Authors where City = @City and State = @State; End; |
Code Listing 1.3
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Create Procedure AuthNameCityFilterRet @City as varchar(20), @State as Char(2), @TotalAuthInSameCity as int output, @TotalAuthInSameState as int output As Begin -- Set First Out Parameter Select @TotalAuthInSameCity = count(au_id) from authors where City = @City; -- Set Second Out Parameter Select @TotalAuthInSameState = count(au_id) from authors where State = @State; -- Return Value return @@rowcount; End; Go |
Listing 2.1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
//Sample 02: Read stored procedure without parameter private void ExecStorProcNoParam_Click(object sender, EventArgs e) { //Sample 2.0: Clear the output before we start txtOutput.Text = ""; //Sample 2.1: Open connection to Pubs Db string con_str = Properties.Settings.Default.PubsDB; SqlConnection PubsCon = new SqlConnection(con_str); PubsCon.Open(); //Sample 2.2: Create Command for Stored Procedure SqlCommand cmd = new SqlCommand(); cmd.CommandText = "AuthNameCity"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = PubsCon; //Sample 2.3: Iterate through the data SqlDataReader Reader = cmd.ExecuteReader(); while (Reader.Read()) { string data; data = string.Format("Author Name:{0}" + Environment.NewLine + "From: {1}, {2}", Reader["AuName"], Reader["City"], Reader["State"]); txtOutput.Text = txtOutput.Text + data + Environment.NewLine; txtOutput.Text = txtOutput.Text + "========================================================" + "===================" + Environment.NewLine; } //Sample 2.4: Close the objects Reader.Close(); PubsCon.Close(); } |
Listing 2.2
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
//Sample 03: Read stored procedure with parameter private void ExecStorProcWithParam_Click(object sender, EventArgs e) { //Sample 3.0: Clear the output before we start txtOutput.Text = ""; //Sample 3.1: Open connection to Pubs Db string con_str = Properties.Settings.Default.PubsDB; SqlConnection PubsCon = new SqlConnection(con_str); PubsCon.Open(); //Sample 3.2: Create Command for Stored Procedure SqlCommand cmd = new SqlCommand(); cmd.CommandText = "AuthNameCityFilter"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = PubsCon; //Sample 3.3: Pack Parameters cmd.Parameters.AddWithValue("@City", txtCity.Text); cmd.Parameters.AddWithValue("@State", txtState.Text); //Sample 3.4: Check Data Present for the passed-in parameters SqlDataReader Reader = cmd.ExecuteReader(); if (Reader.HasRows == false) { txtOutput.Text = "No Data Available for supplied input"; Reader.Close(); PubsCon.Close(); return; } //Sample 3.5: Iterate through records while (Reader.Read()) { string data; data = string.Format("Author Name:{0}" + Environment.NewLine + "From: {1}, {2}", Reader["AuName"], Reader["City"], Reader["State"]); txtOutput.Text = txtOutput.Text + data + Environment.NewLine; txtOutput.Text = txtOutput.Text + "================================================" + "===========================" + Environment.NewLine; } //Sample 3.5: Close the objects Reader.Close(); PubsCon.Close(); } |
Code Listing 2.3
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
//Sample 04: Reading stored procedure return value private void btnStoredProcReturnValue_Click(object sender, EventArgs e) { //Sample 4.0: Clear the output before we start txtOutput.Text = ""; //Sample 4.1: Open connection to Pubs Db string con_str = Properties.Settings.Default.PubsDB; SqlConnection PubsCon = new SqlConnection(con_str); PubsCon.Open(); //Sample 4.2: Create Command for Stored Procedure SqlCommand cmd = new SqlCommand(); cmd.CommandText = "AuthNameCityFilterRet"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = PubsCon; //Sample 4.3: Pack Parameters //Sample 4.3.1: Normal Parameter cmd.Parameters.AddWithValue("@City", txtCity.Text); cmd.Parameters.AddWithValue("@State", txtState.Text); //Sample 4.3.2: Output Parameter SqlParameter param_out1 = new SqlParameter("@TotalAuthInSameCity", SqlDbType.Int); param_out1.Direction = ParameterDirection.Output; SqlParameter param_out2 = new SqlParameter("@TotalAuthInSameState", SqlDbType.Int); param_out2.Direction = ParameterDirection.Output; cmd.Parameters.Add(param_out1); cmd.Parameters.Add(param_out2); //Sample 4.3.3: Return value as Parameter SqlParameter retValue = new SqlParameter(); retValue.ParameterName = "@ReturnValue"; retValue.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(retValue); SqlDataReader Reader = cmd.ExecuteReader(); //Sample 4.4: Get return output //(Return Value and Output parameter) txtOutput.Text = txtOutput.Text + "Number of author in same city (OutParam1) : " + cmd.Parameters["@TotalAuthInSameCity"].Value.ToString() + Environment.NewLine; txtOutput.Text = txtOutput.Text + "Number of author in same State (OutParam2) : " + cmd.Parameters["@TotalAuthInSameState"].Value.ToString() + Environment.NewLine; txtOutput.Text = txtOutput.Text + "Total Number of records found (Return Value) :" + cmd.Parameters["@ReturnValue"].Value.ToString() + Environment.NewLine; lblOutVal.Text = cmd.Parameters["@TotalAuthInSameCity"].Value.ToString() + ", " + cmd.Parameters["@TotalAuthInSameState"].Value.ToString(); lblReturnVal.Text = cmd.Parameters["@ReturnValue"].Value.ToString(); //Sample 4.5: Close objects Reader.Close(); PubsCon.Close(); } |
Source Code: Download Ado.Net Execute Stored Procedure Example (Google Drive)
Categories: Ado.Net
Tags: AddWithValue, CommandType.StoredProcedure, ParameterDirection.Output, ParameterDirection.ReturnValue, SqlParameter, SqlParameter.Direction
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.