ADO.NET Stored Procedures: A Practical Guide with C# Code and a Test Database

Stored procedures simplify database interactions, boost performance, and enhance security. If you’re using ADO.NET, you have several ways to retrieve data from a stored procedure. This guide explores six common approaches with practical C# code examples—plus a sample SQL Server database and a fully functional console application to help you test them.

Setting Up the Sample Database

Before we dive into ADO.NET, let’s create a SQL Server database with sample data to work with.

Database Creation & Seed Script

-- Create a sample database
CREATE DATABASE SampleDB;
GO

USE SampleDB;
GO

-- Create a Users table
CREATE TABLE Users (
    UserID INT IDENTITY(1,1) PRIMARY KEY,
    UserName NVARCHAR(100) NOT NULL,
    Email NVARCHAR(100) UNIQUE NOT NULL,
    CreatedDate DATETIME DEFAULT GETDATE()
);
GO

-- Seed sample data
INSERT INTO Users (UserName, Email) VALUES 
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com'),
('Charlie', 'charlie@example.com');
GO

Stored Procedures for ADO.NET Retrieval Methods

Here are stored procedures that match the retrieval methods covered in this article.

1. Get User Count (Output Parameter)

CREATE PROCEDURE GetUserCount
    @UserCount INT OUTPUT
AS
BEGIN
    SELECT @UserCount = COUNT(*) FROM Users;
END;
GO

2. Get Record Status (Return Value)

CREATE PROCEDURE GetRecordStatus
AS
BEGIN
    RETURN (SELECT COUNT(*) FROM Users WHERE CreatedDate >= DATEADD(DAY, -30, GETDATE()));
END;
GO

3. Get All Users (DataReader, DataTable)

CREATE PROCEDURE GetUsers
AS
BEGIN
    SELECT UserID, UserName, Email FROM Users;
END;
GO

4. Accept Parameter & Return Output

CREATE PROCEDURE GetUserNameById
    @UserID INT,
    @UserName NVARCHAR(100) OUTPUT
AS
BEGIN
    SELECT @UserName = UserName FROM Users WHERE UserID = @UserID;
END;
GO

Retrieving Data in ADO.NET

1. Using Output Parameters

using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlCommand cmd = new SqlCommand("GetUserCount", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    SqlParameter outputParam = new SqlParameter("@UserCount", SqlDbType.Int) 
    { 
        Direction = ParameterDirection.Output 
    };
    cmd.Parameters.Add(outputParam);

    conn.Open();
    cmd.ExecuteNonQuery();
    
    int userCount = (int)outputParam.Value;
}

2. Using Return Values

using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlCommand cmd = new SqlCommand("GetRecordStatus", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    SqlParameter returnParam = new SqlParameter 
    { 
        Direction = ParameterDirection.ReturnValue 
    };
    cmd.Parameters.Add(returnParam);

    conn.Open();
    cmd.ExecuteNonQuery();

    int statusCode = (int)returnParam.Value;
}

3. Using ExecuteScalar() for a Single Value

using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM Users", conn);
    conn.Open();
    
    int userCount = (int)cmd.ExecuteScalar();
}

4. Using a DataReader for Multiple Rows

using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlCommand cmd = new SqlCommand("GetUsers", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    
    conn.Open();
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine(reader["UserName"]);
        }
    }
}

5. Using a DataTable or DataSet for Bulk Data

using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlCommand cmd = new SqlCommand("GetUsers", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    
    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    adapter.Fill(dt);

    foreach (DataRow row in dt.Rows)
    {
        Console.WriteLine(row["UserName"]);
    }
}

6. Passing an Input Parameter & Receiving an Output Parameter

using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlCommand cmd = new SqlCommand("GetUserNameById", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    // Input parameter
    cmd.Parameters.AddWithValue("@UserID", 1);

    // Output parameter
    SqlParameter outputParam = new SqlParameter("@UserName", SqlDbType.NVarChar, 100) 
    { 
        Direction = ParameterDirection.Output 
    };
    cmd.Parameters.Add(outputParam);

    conn.Open();
    cmd.ExecuteNonQuery();

    string userName = outputParam.Value.ToString();
    Console.WriteLine($"User Name: {userName}");
}

Running These Methods in a Console Application

To make testing even easier, here’s a C# console application that implements all the examples above. Just update your connection string and run the app.

Program.cs

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

namespace MyConsoleApp
{
    internal class Program
    {
        private const string _connectionString = @"Server=Judah;Database=SampleDB;Trusted_Connection=True;TrustServerCertificate=True;";

        static void Main(string[] args)
        {
            UseOutputParameters(_connectionString);
            GetAReturnValue(_connectionString);
            GetASingleValue(_connectionString);
            GetMultipleValues(_connectionString);
            GetMultipleValuesWithADatatable(_connectionString);
            UseIOParameters(_connectionString);

            Console.ReadKey();
        }

        // Define methods here...
    }
}

Download the complete project!

About the Author

Paul A. Jones Jr. is a software engineer and legal tech founder developing tools for professionals in law and other regulated industries. He writes about systems thinking, modern workflows, and SaaS applications at PaulJonesSoftware.com. Follow him on Twitter: @PaulAJonesJr.

You Might Also Enjoy

How a Single Composite Context Transformed My Calculator Architecture

recent posts

about