How to execute an SQL Server stored procedure from a C# program?

Today, let us see how to execute an SQL Server Stored Procedure from a C# program. This is a very old topic, however, I am still getting questions regarding this from beginners. So, let us revisit this topic with a simple illustration. To start with, let us create a simple stored procedure to fetch some data and then create a C# console program to execute the stored procedure and display the result in the console.

Create The Stored Procedure

I am using SQL Server’s sample database WideWorldImporters for this illustration. Here is a simple stored procedure to fetch the list of customers from the Sales.Customers table. There are two parameters. One parameter is for getting the search term to filter the customer names. The other parameter is the number of records to fetch. This stored procedure returns two columns, CustomerID and CustomerName.

CREATE PROCEDURE [Sales].[Mtb_GetCustomers] 
@SearchText nvarchar(100), 
@MaximumRowsToReturn int 
AS
BEGIN 
    SELECT TOP(@MaximumRowsToReturn) 
           c.CustomerID, 
           c.CustomerName 
    FROM Sales.Customers AS c 
    WHERE c.CustomerName LIKE N'%' + @SearchText + N'%' 
    ORDER BY c.CustomerName; 
END; 
GO 
SQL Server stored procedure

C# Program

Here is the simple C# console application program to execute the stored procedure. In this program, open a connection using System.Data.SqlClient namespace’s SqlConnection object. Then create a SqlCommand object and add the values for the parameters. Finally execute the command and store the result from the stored procedure in the SqlDataReader object. Then you can iterate the SqlDataReader object and display the result.

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

namespace SampleConsoleApp
{
    class Program
    {
        public void RunStoredProc()
        {
            SqlConnection sqlConn = null;
            SqlDataReader sqlDr = null;

            try
            {
                // Open a connection to SQL Server
                sqlConn = new SqlConnection("Server=(local);DataBase=WideWorldImporters;Integrated Security=SSPI");
                sqlConn.Open();

                // Create a command object with parameters for stored procedure
                SqlCommand sqlCmd = new SqlCommand("[Sales].[Mtb_GetCustomers]", sqlConn);
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.AddWithValue("@SearchText", SqlDbType.NVarChar).Value = "And";
                sqlCmd.Parameters.AddWithValue("@MaximumRowsToReturn", SqlDbType.Int).Value = 10;

                // Execute the command and get the data in a data reader.
                sqlDr = sqlCmd.ExecuteReader();

                // Iterate through the datareader and write the data to the console
                Console.WriteLine("\nTop 10 Customer Names:\n");
                while (sqlDr.Read())
                {
                    Console.WriteLine(
                        "ID: {0,-25} Name: {1,6}",
                        sqlDr["CustomerID"],
                        sqlDr["CustomerName"]);
                }
            }
            finally
            {
                if (sqlConn != null)
                {
                    sqlConn.Close();
                }
                if (sqlDr != null)
                {
                    sqlDr.Close();
                }
            }
        }
        static void Main(string[] args)
        {
            Program p = new Program();
            p.RunStoredProc();
            Console.Read();
        }
    }
}

Result

Execute An SQL Server Stored Procedure From A C# Program

Related Article


Leave your thoughts...

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