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