ADO.NET 3.5 Cookbook, 2nd Edition

3.18. Retrieving Data Using a SQL Server Stored Procedure

Problem

You need to execute a SQL Server stored procedure to retrieve a result set.

Solution

Use a SqlCommand object with its CommandType property set to StoredProcedure.

The solution executes the stored procedure dbo.uspGetEmployeeManagers, stores the result set in a DataTable, and outputs the values in the DataTable to the console.

The C# code in Program.cs in the project RetreiveDataSqlServerStoredProcedure is shown in Example 3-23.

Example 3-23. File: Program.cs for RetrieveDataSqlServerStoredProcedure solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace RetrieveDataSqlServerStoredProcedure
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            string sqlSelect = "uspGetEmployeeManagers";

            // Create a connection
            SqlConnection connection = new SqlConnection(sqlConnectString);
            // Create the store procedure command
            SqlCommand command = new SqlCommand(sqlSelect, connection);
            command.CommandType = CommandType.StoredProcedure;
            // Add the parameter and set its value to 100
            command.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = 100;

            // Create a DataTable and fill it using the stored procedure
            DataTable dt = new DataTable( );
            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(dt);
            // Output the result set to the console
            foreach (DataRow row in dt.Rows) { Console.WriteLine("[{0}] {1} {2}, {3} ...