Calling a Stored Procedure from C#

In ADO.NET, put the procedure's name in a SqlCommand, set its CommandType to StoredProcedure, and add each parameter by name. Then read the results as from any query.

Journeyman · C# · T-SQL · Updated

By

After this lesson you can

  • Call a stored procedure from C# with SqlCommand, CommandType.StoredProcedure and parameters

Key takeaways

  • To call a procedure, put its name in the SqlCommand and set CommandType to CommandType.StoredProcedure.
  • Add each parameter by its name, @ included: command.Parameters.AddWithValue("@CategoryName", "Produce").
  • Read the rows a procedure returns with ExecuteReader, and a single value with ExecuteScalar, as with any query.
  • Leave out a parameter that has a default, and the procedure uses the default.
  • Without CommandType.StoredProcedure, the procedure runs with no parameters, and SQL Server says one was not supplied.

Calling a stored procedure from C# is almost the same as running a query with ADO.NET, from Connecting Apps to SQL Server. Three things change:

  1. The command's text is the procedure's name, not a SELECT.
  2. The command's CommandType is CommandType.StoredProcedure.
  3. Each parameter is added by its name, @ included, as in the procedure.

Reading rows

This program calls GetProductsByCategory, from the last lesson, and prints what it returns:

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

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";

using SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

using SqlCommand command = new SqlCommand("GetProductsByCategory", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@CategoryName", "Produce");

using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    string name = (string)reader["ProductName"];
    decimal price = (decimal)reader["UnitPrice"];
    Console.WriteLine(quot;{name}: {price:0.00}");
}
Longlife Tofu: 10.00
Manjimup Dried Apples: 53.00
Rössle Sauerkraut: 45.60
Tofu: 23.25
Uncle Bob's Organic Dried Pears: 30.00

CommandType lives in the System.Data namespace, hence the extra using. The reader reads the procedure's columns by name, exactly as it would a query's.

Reading one value

CountProductsInCategory returns a single value, so call it with ExecuteScalar:

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

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";

using SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

foreach (string category in new[] { "Seafood", "Produce", "Beverages" })
{
    using SqlCommand command = new SqlCommand("CountProductsInCategory", connection);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@CategoryName", category);

    int count = (int)command.ExecuteScalar();
    Console.WriteLine(quot;{category}: {count} products");
}
Seafood: 12 products
Produce: 5 products
Beverages: 12 products

Leaving out a parameter with a default

SearchProducts has a default for @MaxPrice. Leave that parameter out in C#, and the default is used, just as in EXEC:

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

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";

using SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

using SqlCommand command = new SqlCommand("SearchProducts", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Text", "Sauce");

using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(reader["ProductName"]);
}
Northwoods Cranberry Sauce
Louisiana Fiery Hot Pepper Sauce

Common problems

What you see What to do
Could not find stored procedure 'GetProductsByCategory' The procedure doesn't exist in the database you connected to. Create it in SSMS, in the right database.
Procedure or function 'GetProductsByCategory' expects parameter '@CategoryName', which was not supplied A parameter without a default is missing or misspelled, or CommandType wasn't set to StoredProcedure: without it, the procedure runs with no parameters at all.

Do it: set up the command that counts a category's products

The store's stock page shows how many products each category has. It gets each number from the stored procedure CountProductsInCategory, from the last lesson, which takes one parameter, @CategoryName. Write the method that sets up the command for it.

The practice box can't reach SQL Server, so the bottom of Program.cs has a stand-in for SqlCommand, named StandInCommand, and for the CommandType values. Leave them as they are. A StandInCommand has what a SqlCommand has for this job:

  • CommandText: the text the command runs, given when it's made: new StandInCommand("...").
  • CommandType: CommandType.Text (a query, the default) or CommandType.StoredProcedure.
  • Parameters: the parameters, each added with Parameters.AddWithValue(name, value).

The method gets one value:

  • categoryName: the category whose products to count, a string ("Seafood").

For example, CountCommand("Seafood") returns a command set up like this:

Part Value
CommandText "CountProductsInCategory"
CommandType CommandType.StoredProcedure
Parameters one: @CategoryName, holding "Seafood"

The tests read the parameters as a list of lines, one per parameter, written name = value. For this example, that list is ["@CategoryName = Seafood"].

Your job: write the method StandInCommand CountCommand(string categoryName).

  • Make the command with the procedure's name, "CountProductsInCategory", as its text.
  • Set its CommandType to CommandType.StoredProcedure.
  • Add one parameter, named "@CategoryName" (the @ included), holding categoryName.
  • Return the command.

Practiced live on the lesson page: write the C# right there and check it.

Check yourself

1 question to try on the lesson page.

So what

A procedure's name, CommandType.StoredProcedure and named parameters: that's all it takes for your C# to use logic stored in the database. The DAOs you write later can call procedures and queries side by side.

Key terms

Cheat sheets