Executing Stored Procedures with C# and SQL Server

Introduction

In this tutorial, we will explore how to execute stored procedures from a C# application using ADO.NET. Executing stored procedures is an efficient way to handle complex operations directly within the database server, reducing network traffic by avoiding sending large datasets over the wire.

Understanding Stored Procedures

Stored procedures are precompiled SQL statements that reside in the database and can be executed with various input parameters. They offer benefits like improved performance, reusability of code, and better security through encapsulation.

Setting Up Your Environment

Before we begin, ensure you have:

1. A running instance of Microsoft SQL Server.
2. Visual Studio or any C# development environment installed on your machine.
3. Basic knowledge of C# programming and SQL Server database operations.

Creating a Stored Procedure in SQL Server

First, let’s create a simple stored procedure named test that demonstrates basic functionality:

USE master;
GO
CREATE PROCEDURE dbo.test AS
BEGIN
    DECLARE @i INT = 0;
    WHILE @i < 5
    BEGIN
        PRINT 'I VALUE ' + CONVERT(VARCHAR(20), @i);
        SET @i = @i + 1;
    END
END;
GO

This stored procedure simply prints numbers from 0 to 4.

Connecting C# with SQL Server

To call a stored procedure from C#, follow these steps:

1. Add necessary namespaces:
   Ensure your project references the System.Data.SqlClient namespace, which provides classes for accessing databases.

2. Create the connection string:
   A connection string contains information about the database server and the specific database to connect to. For a local SQL Server instance, it might look like:

   string connectionString = "Server=(local);Database=master;Integrated Security=True;";
   

3. Establish a database connection:
   Use SqlConnection to create a connection object.

4. Create and configure the command object:
   Utilize SqlCommand for executing stored procedures, setting its CommandType property to CommandType.StoredProcedure.

5. Execute the stored procedure:
   For non-query operations like stored procedures that don’t return results directly, use ExecuteNonQuery().

Sample Code: Executing a Stored Procedure

Here’s a complete example demonstrating how to execute our test stored procedure:

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

namespace AutomationApp
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Server=(local);Database=master;Integrated Security=True;";
            
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("dbo.test", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                
                try
                {
                    conn.Open();
                    // Execute the stored procedure
                    cmd.ExecuteNonQuery();
                    Console.WriteLine("Stored procedure executed successfully.");
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"An error occurred: {ex.Message}");
                }
            }

            Console.ReadLine();
        }
    }
}

Key Points to Remember

• Error Handling: Always use try-catch blocks around database operations to handle exceptions gracefully.

• Security Considerations: Use parameterized queries or stored procedures instead of concatenating SQL strings to prevent SQL injection attacks.

• Connection Management: Leverage using statements to ensure that connections are properly closed and disposed, even if an error occurs.

Conclusion

By following this guide, you have learned how to execute a stored procedure from C#. This skill is fundamental for developers working with databases in .NET applications. For further exploration, consider learning about handling output parameters or return values from stored procedures.