Executing a Stored Procedure Programmatically

This article has been excerpted from book "A Programmer's Guide to ADO.NET in C#".

As an application developer, most of the time you'll be executing stored procedure programmatically. You can execute a stored procedure programmatically using the command object. Instead of passing a SQL statement, you pass the stored procedure name as the SQL statement to execute a stored procedure. Each data provider provides a command object to execute SQL statements. The command class for the OleDb, Odbc, and Sql data provides are Oledbcommand, Odbccommand, and Sqlcommand, respectively. In listing 10-1, I'll use sqlcommand to execute a procedure programmatically against a SQL server database.

There are two steps involved in executing a stored procedure from your program. First, you set the command object property CommandText as the stored procedure name; second, you set the CommandType property as CommandType.StoredProcedure. Listing 10-1 executes the mySP stored procedure you created in the previous section. To test listing 10-1, I created a console application and typed listing 10-1 on the Main method. Don't forget to add a reference to the System.Data.dll assembly and add the following two namespaces to the project before using the Sql data provider classes:

using System.Data;

using System.Data.SqlClient;

Listing 10-1: Executing mySP stored procedure using Sql data provider

using System;

using System.Data;

using System.Data.SqlClient;

namespace Executing_a_Stored_Procedure

{

    class Program

    {

        static void Main(string[] args)

        {

            // Create a Connection Object

            string ConnectionString = "Integrated Security=SSPI;" +

            "Initial Catalog=Northwind;" +

            "Data Source = localhost;";

            SqlConnection conn = new SqlConnection(ConnectionString);

            conn.Open();

            SqlCommand cmd = new SqlCommand("mySP", conn);

            cmd.CommandType = CommandType.StoredProcedure;

            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())

            {

                Console.Write(reader[0].ToString());

                Console.Write(reader[1].ToString());

                Console.WriteLine(reader[2].ToString());

            }

            Console.Read();

            //Close reader and connection

            reader.Close();

            conn.Close();

        }

    }

}

As you can see from figure 10-1, I created SqlCommand object by passing the stored procedure as the first parameter of the SqlCommand constructor and then set the CommandType property CommandType.StoredProcedure. The result of listing 10-1 looks like Figure 10-14.

Figure 10-14. Output of stored procedure mySP

A stored procedure can also accept input, output, and both types of programmers. Now I'll modify the mySP stored procedure a little bit. This time I'll give the user an option to select the customers based on their country. Figure 10-15 shows the modified stored procedure.

Figure 10-15. Stored procedure with parameters 

As you can see from figure 10-15, I selected customers based on the country entered by the user. You can use the SqlParameter class to create a parameter. The SqlParameter class has properties such as Direction and Value. The Direction property defines the direction if the stored procedure is an input or output (or both) or has a return value. The ParameterDirection enumeration defines values of Direction (see Table 10-1).

Table 10-1: The ParameterDirection Members

MEMBER	DESCRIPTION
Input	Input parameter.
InputOutput	Both input and output parameter.
Output	Output only.
ReturnValue	The parameter returns a value returned by the stored procedure.

The Value property sets the value of the parameter. The following code adds a parameter with the value UK. After you execute the mySP stored procedure. It'll return customers from the United Kingdom only:

SqlParameter param = new SqlParameter();

param = StoredProcedureCommand.Parameters.Add("@country", SqlDbType.VarChar, 50);

param.Direction = ParameterDirection.Input;

param.Value = "UK";

The updated source code looks like listing 10-2, and the output of listing 10-2 looks like figure 10-16. In listing 10-2, I created SqlParameter as the country and set its value to UK. ExecuteReader only returns rows where Country = "UK".

Listing 10-2: Using parameters in a stored Procedure

             // Create a Connection Object

            string ConnectionString = "Integrated Security=SSPI;" +

            "Initial Catalog=Northwind;" +

            "Data Source=localhost;";

            SqlConnection conn = new SqlConnection(ConnectionString);

            SqlCommand StoredProcedureCommand = new SqlCommand("mySP", conn);

             StoredProcedureCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter param = new SqlParameter();

            param = StoredProcedureCommand.Parameters.Add("@country", SqlDbType.VarChar, 50);

            param.Direction = ParameterDirection.Input;

            param.Value = "UK";

            conn.Open();

            SqlDataReader reader = StoredProcedureCommand.ExecuteReader();

            while (reader.Read())

            {

                Console.Write(reader[0].ToString());

                Console.Write(reader[1].ToString());

                Console.WriteLine(reader[2].ToString());

            }

            Console.Read();

             // Close reader and connection

            reader.Close();

            conn.Close();

Figure 10-16: Output of listing 10-2

To return a value from a stored procedure, the only thing you need to do is change the stored procedure, which will store and return a value as a parameter, and set the parameter's Direction property as follows:

            SqlParameter param = new SqlParameter();

            param.Direction = ParameterDirection.ReturnValue;

Also, store the command execute results in a number variable like this:

            param = StoredProcedureCommand.Parameters.Add("@counter", SqlDbType.Int);

Note: See the following example for the complete source code.

Now I'll show you an example of using ParameterDirection.OutPut. To test this source code, create a console application and the following

using System;

using System.Data;

using System.Data.Common;

using System.Data.SqlClient;

Now create a stored procedure called AddCat1 that adds a row to the Categories table and returns the row count (see listing 10-3).

Listing 10-3: AddCat1 stored procedure

ALTER PROCEDURE dbo.AddCat1

@CategoryName nchar(15),

@Description char(16),

@Identity int OUT

AS

INSERT INTO Categories (CategoryName, Description)

VALUES(@CategoryName, @Description)

SET @Identity = @@Identity

RETURN @@ROWCOUNT

/* SET NOCOUNT ON */

RETURN

Listing 10-4 shows how to use output parameters. Everything is similar to the previous samples except that I used the parameter direction

ParameterDirection.Output.

Listing 10-4: Executing a stored procedure with output parameter

            string connString = "Data Source=localhost;Integrated Security=SSPI;"

            + "Initial Catalog=northwind";

            string sql = "SELECT CategoryID, CategoryName, Description FROM Categories";

            SqlConnection conn = new SqlConnection(connString);

            SqlDataAdapter da = new SqlDataAdapter(sql, conn);

            da.InsertCommand = new SqlCommand("AddCat1", conn);

             da.InsertCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter myParm = da.InsertCommand.Parameters.Add("@RowCount", SqlDbType.Int);

            myParm.Direction = ParameterDirection.ReturnValue;

             da.InsertCommand.Parameters.Add

            ("@CategoryName", SqlDbType.NChar, 15, "CategoryName");

             da.InsertCommand.Parameters.Add

            ("@Description", SqlDbType.Char, 16, "Description");

            myParm = da.InsertCommand.Parameters.Add

            ("@Identify", SqlDbType.Int, 0, "CotegoryID");

            myParm.Direction = ParameterDirection.Output;

            DataSet ds = new DataSet();

            da.Fill(ds, "Categories");

            DataRow row = ds.Tables["Categories"].NewRow();

            row["CategoryName"] = "Beverages";

            row["Description"] = "Chai";

            ds.Tables["Categories"].Rows.Add(row);

            da.Update(ds, "Categories");

            Console.WriteLine(da.InsertCommand.Parameters["@RowCount"].Value.ToString());

Conclusion

Hope this article would have helped you in understanding executing a Stored Procedure Programmatically. See other articles on the website also for further reference.

This essential guide to Microsoft's ADO.NET overviews C#, then leads you toward deeper understanding of ADO.NET.