Thursday, February 04, 2016

[ Ado.net ] - Accessing Stored Procedures from Ado.net C# Application front-end

1. Ado.net and stored procedure


In ado.net, just like returning data from the table, data can be retrieved by executing the stored procedure also. 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. 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 used in this example


This section shows you short information (What is required for a front-end developer) about the stored procedure used in this example. To know more information about the back-end coding, refer this article: Creating and Using Stored Procedure.

2.1 Example procedure returning a table of data


A stored procedure called AuthNameCity is created and this procedure returns a table to its caller. The body of the procedure has a single static select statement which picks data from the authors table of the pubs database. Below is this example procedure:

 
Fig 1: Stored procedure without parameter

2.2 Example procedure with parameters


The next procedure named AuthNameCityFilter is created to demonstrate how the parameters can be passed from Ado.net front-end. This procedure accepts two parameters of type varchar and char. In the body of the procedure, passed-in parameters are utilized (Highlighted in yellow) to return filtered rows back to the caller. Example procedure is below:

Fig 2. Stored Procedure with Parameter



2.3 Example procedure with return statement and output parameter


The final procedure called AuthNameCityFilterRet is created to read the data returned by return statement as well as the output parameters. Here, you can see the procedure accepts four parameters and last two parameters are actually output parameters. In the body of the procedure, the output parameters are assigned values through a simple aggregate function count. The return statement is row count of the last executed query. In our case it is always 1. Below is the example procedure:

Fig 3. Stored Procedure with return Value




3. About the Example


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

 
Fig 4. Example Ado.Net Application
A multi-line text box control is placed in the top portion of the sample application (Marked as 1) and this control is used to show the data returned by executing the stored procedure. The clear button (Marked as 2) is used to clear the current content in the multi-line textbox. The button marked as 3 is used to execute 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 textbox 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 is used to execute the stored procedure that has return statement as well as accepts output parameters (Listing 1.3) and the value received is shown in the labels marked as 8 and 9. That is all about this sample 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


Like the select queries, a stored procedure can also return data to the caller. In ado.net, executing the stored procedure from the font-end requires some additional work as procedure may vary in the way it interact with the caller (Some procedure have return statement, some may accept parameters etc). In this section, we will write code for the sample application designed in the previous section and also learn executing three kind of procedures discussed in section 2.

Before we start, in the code for the form, put a using statement for easy access to the SQLClient name space. The code is shown below:

//Sample 01: Namespace for the SqlClient
using System.Data.SqlClient;

Code for clearing the output window (Marked as 1) is straight forward and the clear button event handler is shown below:
//Sample 05: Clear output window
private void btnClear_Click(object sender, EventArgs e)
{
    txtOutput.Text = "";
}

Before we start writing the code, a connection string for the Microsoft supplied Pubs database is formed as part of the application property. You can create the Pubs and NorthWnd database by downloading the script files available in the SQL 2005 article Page of this web site. To know how a connection string can be placed as part of application property, watch the below video:



4.1 Stored procedure without parameter – returns table of data


A stored procedure returning table of data can be seen as 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 that command type is stored procedure. Let us explore the code for the click event handler of the button marked as 3. Listing 2.1 has complete code for this handler.

4.1.1 Opening connection


The connection string for the pubs database is retrieved from the application settings. Once connection string is on hand, SqlConnection object is created by supplying this connection string to it and the open method call will establish the connection to pubs database by making use of the connection string supplied during the SqlConnection construction. The code is shown below:

//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-up the SQLCommand


After constructing the SqlCommand object, the Procedure name that we are willing to execute is specified in the CommandText property. Also note that through “CommandType” property we can say ado.net that we are going execute a stored procedure and do not expect a select query in the CommandText property. The enumeration “CommandType.StoredProcedure” set to the CommandType property. Code for this is shown below:

//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 data through SqlDataReader


Once the SqlCommand is set properly, it is time to iterate the data through the SqlDataReader object. The call to ExecuteReader method on the command that we set in the previous section return the SqlDataReader. This reader object is iterated to retrieve the table of data returned by the stored procedure. Code is below:

//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 Stored procedure with parameter & Parameter collection


The “Parameters collection” of the SqlCommand object is used to supply one or more parameter(s) expected by the stored procedure. The collection has a useful method called “AddWithValue” which can be used to add a parameter to the parameter collection by supplying the parameter name (Remember the @ symbol) and value that needs to be passed as part of that parameter. The entire handler for the button marked as 4 is given in Listing 2.2. Let us explore this event handler:

4.2.1 Packing the parameters using AddWithValue


The SqlCommand object is packed with the parameters and its corresponding values by making call to the function AddWithValue on the SqlParameterCollection object. The parameters 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, the actual values are taken from the text boxes marked as 5 and 6. Code is shown below:

//Sample 3.3: Pack Parameters
cmd.Parameters.AddWithValue("@City", txtCity.Text);
cmd.Parameters.AddWithValue("@State", txtState.Text);

4.2.2 Check row(s) exist


As we are passing the parameters to stored procedure, there is chance that procedure may not return any data as the match won’t occur in the where clause of the procedure. So we need to check executing the stored procedure returns any valid data. The “HasRows” Boolean property is set to true when there is valid rows for the iteration to happen. Code is shown below:

//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;
}

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


4.3 Stored procedure with output parameter & return statement


Ado.net reads the return value from the stored procedure as parameter. The output parameter also read from the parameter after the procedure execution. In the previous section we saw adding a parameter to the parameter collection by making use of the function call AddWithValue. To deal with the output parameter and return value we need to construct the “SqlParameter” object and then set the “Direction” property. This direction property is the key for specifying the value movement. When direction is not specified, Ado.net treats the movement of data flow is from Front-End application to stored procedure. To set the direction, “ParameterDirection” enumeration is used. The click event handler for the button marked as 7 is provided in the Listing 2.3.

4.3.1 Output Parameter as SqlParameter


For output parameter, the parameter direction is set to “ParameterDirection.Output”. This informs the command object to expect value in that parameter after the procedure execution. In our example, we created two output parameters for executing the procedure AuthNameCityFilterRet. Once the parameters with proper direction are created, they are added to the parameter collection. The code for this is given below:

//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 parameter, return value also treated as parameter and created by specifying the parameter direction using the enumeration constant “ParameterDirection.ReturnValue”. The code is given below:

//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 the data from parameters


After executing the procedure, we can read the values from the output parameter and return value parameter which is packed in the parameter collection of the SqlCommand object.  The below code shows reading the parameter value and showing that in the Multi-line textbox control as well as the label controls marked as 8 and 9.


//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 procedures in SQL Server. Also modify the connection string property so that it is valid in your environment. Once it is done you can run the application without any trouble. The video attached below shows executing the stored procedures from the front-end:

Video 2: Running the Application

6. Code Listings

Listing 1.1

Create Procedure AuthNameCity
As
Begin
       Select Au_FName + ', ' + au_lname as 'AuName', City, State
       From Authors;
End;
Go


Listing 1.2

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;


Listing 1.3

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

//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

//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();
}


Listing 2.3

//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

Like this site? Tell it to your Firend :)

*** When a New Article posted, get Notification to the email that you give below: ***

Follow us on G+

C++ Articles