Working with SQL Server Stored Procedures and .NET

By: Artemakis Artemiou | Updated: 2018-12-06 | Comments (4) | Related: > Application Development

Problem

In previous tips (see tip1, tip 2), we've learned how to get started with .NET and SQL Server data access. To this end, we've learned how to connect to SQL Server from a C# program and run a simple query, as well as how to query SQL Server tables from .NET and process the results. In this tip, we will learn how to work with SQL Server stored procedures from within a .NET Application.

Solution

There is more than one way to work with SQL Server stored procedures from within a .NET Application. However, when using parameters, in order to avoid exposing your code to "SQL Injection" vulnerabilities, there is a certain way based on which you should write your code. The examples in this tip will be taking the above into consideration.

Sample Database

Just like in my previous tips, our example, will be based on the database "SampleDB" which can be found on our test SQL Server 2017 named instance on the local machine, which is called "SQL2K17".

Here's a screenshot of the SQL Server instance, as it can be seen in SSMS:

Screenshot of the sample database with its tables and stored procedures that will be used in this tip

The sample database has two tables named "employees" and "location", as well as two stored procedures named "uspEmployeeInfo" and "uspUpdateEmployeeLocation".

Since we will be working with stored procedures in this tip, below you can see the DDL T-SQL scripts for the two above stored procedures:

USE [SampleDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[uspEmployeeInfo]
   @employeeID int
   AS
BEGIN
SELECT e.id,e.code,e.firstName,e.lastName,l.code,l.descr 
FROM employees e 
INNER JOIN location l on e.locationID=l.id
where e.id=@employeeID
END
GO

The stored procedure "uspEmployeeInfo" takes as an input parameter the employee id and returns basic employee information.

USE [SampleDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[uspUpdateEmployeeLocation]
   @employeeID int,
   @locationID int
AS
BEGIN
   UPDATE employees SET locationID=@locationID WHERE id=@employeeID;
END
GO

The stored procedure "uspUpdateEmployeeLocation" takes as an input parameter the employee id and the location id and updates the relevant employee record with the specified location id.

Sample Data

In the below screenshot, you can take a look at the data currently stored in the "employees" and "location" tables.

Screenshot of the sample tables and their data that will be used in this tip

Calling the Stored Procedures from a .NET Application

The next step in our example, is to write a .NET application and more specifically a C# application, that connects to the database server and properly calls these two stored procedures.

This example is based on the examples originally presented in my two previous tips (see tip1 and tip 2).

One of the main points, is that I will make use of the .NET Class SqlParameter in order to write more secure code, thus minimizing the risk for SQL injections.

Connecting to the SQL Server Instance - Connection String

Again, like in my previous tips, the connection string will be the same:

string connString = @"Server =.\SQL2K17; Database = SampleDB; Trusted_Connection = True;";

Using the above connection string, I will connect to the named instance "SQL2K17" on the local machine, using a trusted connection, that is, with my windows account.

Calling Stored Procedure "uspEmployeeInfo"

As you will see in the below full .NET code, I'm using the SqlParameter class, in order to properly set the input parameter "employeeID" for calling the "uspEmployeeInfo" stored procedure.

After setting the SqlParameter object, I'm adding it as a parameter in the SqlCommand object.

 //set stored procedure name
string spName = @"dbo.[uspEmployeeInfo]";

//define the SqlCommand object
SqlCommand cmd = new SqlCommand(spName, conn);

//Set SqlParameter - the employee id parameter value will be set from the command line
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@employeeID";
param1.SqlDbType = SqlDbType.Int;
param1.Value = int.Parse(args[0].ToString());

//add the parameter to the SqlCommand object
cmd.Parameters.Add(param1);

Great! Let's see the full code, where besides properly calling the stored procedure, I'm also getting the results and display it on screen.

Note: Prior to start writing the code, similarly to my previous tips on .NET (see tip1, tip 2), you will need to create a new Visual C# project in Visual Studio, and select the "Console App (.NET Framework)" template. In my example, I named the project " TestApp3-Call SP1" and saved it in the "C:\temp\demos" folder on my local machine (a new subfolder with the name of the project was created).

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace TestApp3_Call_SP1
{
    class Program
    {
        static void Main(string[] args)
        {
            //set the connection string
            string connString = @"Server =.\SQL2K17; Database = SampleDB; Trusted_Connection = True;";

            //variables to store the query results
            int empID;
            string empCode, empFirstName, empLastName, locationCode, locationDescr;

            try
            {
                //sql connection object
                using (SqlConnection conn = new SqlConnection(connString))
                {

                    //set stored procedure name
                    string spName = @"dbo.[uspEmployeeInfo]";

                    //define the SqlCommand object
                    SqlCommand cmd = new SqlCommand(spName, conn);

                    //Set SqlParameter - the employee id parameter value will be set from the command line
                    SqlParameter param1 = new SqlParameter();
                    param1.ParameterName = "@employeeID";
                    param1.SqlDbType = SqlDbType.Int;
                    param1.Value = int.Parse(args[0].ToString());

                    //add the parameter to the SqlCommand object
                    cmd.Parameters.Add(param1);

                    //open connection
                    conn.Open();

                    //set the SqlCommand type to stored procedure and execute
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataReader dr = cmd.ExecuteReader();

                    Console.WriteLine(Environment.NewLine + "Retrieving data from database..." + Environment.NewLine);
                    Console.WriteLine("Retrieved records:");

                    //check if there are records
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            empID = dr.GetInt32(0);
                            empCode = dr.GetString(1);
                            empFirstName = dr.GetString(2);
                            empLastName = dr.GetString(3);
                            locationCode = dr.GetString(4);
                            locationDescr = dr.GetString(5);

                            //display retrieved record
                            Console.WriteLine("{0},{1},{2},{3},{4},{5}", empID.ToString(), empCode, empFirstName, empLastName, locationCode, locationDescr);
                        }
                    }
                    else
                    {
                        Console.WriteLine("No data found.");
                    }

                    //close data reader
                    dr.Close();

                    //close connection
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                //display error message
                Console.WriteLine("Exception: " + ex.Message);
            }


        }
    }
}

Right after you finish writing the code, you will need to compile and run the program. To do this, within our project in Visual Studio, by pressing the key F6 or by clicking on the "Build" menu and then click on "Build Solution", our program will be compiled and if everything is OK, that is if we get no errors and see the "Build succeeded" notification on the bottom left corner of the window, it means that the program is now ready for execution.

To execute the program, since this is a console application, we need to navigate to the project's directory and run the executable. In this demo, the project directory is "C:\temp\demos\TestApp3-Call SP1". Therefore, from the command prompt, we need to navigate to "C:\temp\demos\TestApp3-Call SP1\TestApp3-Call SP1\bin\Debug" and execute the "TestApp3-Call SP1.exe" program (or if you changed the assembly name, run the proper executable file that is generated upon compiling the program) along with passing the parameter value. For example, I'm running the below command:

C:\temp\demos\TestApp3-Call SP1\TestApp3-Call SP1\bin\Debug>"TestApp3-Call SP1.exe" 1

Here's the output of the program's execution:

Running it again along with passing a parameter value of 2:

Calling Stored Procedure "uspUpdateEmployeeLocation"

Now let's create another C# program, that calls the stored procedure "uspUpdateEmployeeLocation" which takes two input parameters and updates the "employees" table records.

The below code block shows how I will be setting the two parameters in my C# code for properly calling the stored procedure:

//set stored procedure name
string spName = @"dbo.[uspUpdateEmployeeLocation]";

//define the SqlCommand object
SqlCommand cmd = new SqlCommand(spName, conn);

//Set SqlParameter - the employee id parameter value will be set from the command line
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@employeeID";
param1.SqlDbType = SqlDbType.Int;
param1.Value = int.Parse(args[0].ToString());

//Set SqlParameter - the location id parameter value will be set from the command line
SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@locationID";
param2.SqlDbType = SqlDbType.Int;
param2.Value = int.Parse(args[1].ToString());

//add the parameter to the SqlCommand object
cmd.Parameters.Add(param1);
cmd.Parameters.Add(param2);

As you can see in the above code block, I defined 2 SqlParameter objects, and then added them as parameters to the SqlCommand object.

Now let's see the full code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace TestApp3_Call_SP2
{
    class Program
    {
        static void Main(string[] args)
        {
            //set the connection string
            string connString = @"Server =.\SQL2K17; Database = SampleDB; Trusted_Connection = True;";

            //variables to store the query results
            int empID;
            string empCode, empFirstName, empLastName, locationCode, locationDescr;

            try
            {
                //sql connection object
                using (SqlConnection conn = new SqlConnection(connString))
                {

                    //set stored procedure name
                    string spName = @"dbo.[uspUpdateEmployeeLocation]";

                    //define the SqlCommand object
                    SqlCommand cmd = new SqlCommand(spName, conn);

                    //Set SqlParameter - the employee id parameter value will be set from the command line
                    SqlParameter param1 = new SqlParameter();
                    param1.ParameterName = "@employeeID";
                    param1.SqlDbType = SqlDbType.Int;
                    param1.Value = int.Parse(args[0].ToString());

                    //Set SqlParameter - the location id parameter value will be set from the command line
                    SqlParameter param2 = new SqlParameter();
                    param2.ParameterName = "@locationID";
                    param2.SqlDbType = SqlDbType.Int;
                    param2.Value = int.Parse(args[1].ToString());

                    //add the parameter to the SqlCommand object
                    cmd.Parameters.Add(param1);
                    cmd.Parameters.Add(param2);

                    //open connection
                    conn.Open();

                    //set the SQLCommand type to StoredProcedure
                    cmd.CommandType = CommandType.StoredProcedure;

                    Console.WriteLine(Environment.NewLine + "Executing stored procedure..." + Environment.NewLine);

                    //execute the stored procedure                   
                    cmd.ExecuteNonQuery();


                    Console.WriteLine(Environment.NewLine + "The stored procedure has been successfully executed." + Environment.NewLine);


                    //close connection
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                //display error message
                Console.WriteLine("Exception: " + ex.Message);
            }


        }
    }
}

Since this second stored procedure performs just an update and does not return any results, there is no need to use an SqlDataReader object. It is for the same reason that in the code, the SqlCommand was executed by calling the method SqlCommand.ExecuteNonQuery.

After compiling the program, to execute it, since this is a console application, we need to navigate to the project's directory and run the executable. In this demo, the project directory is "C:\temp\demos\TestApp3-Call SP2". Therefore, from the command prompt, we need to navigate to "C:\temp\demos\TestApp3-Call SP2\TestApp3-Call SP2\bin\Debug" and execute the "TestApp3-Call SP2.exe" program (or if you changed the assembly name, run the proper executable file that is generated upon compiling the program) along with passing the parameter value.

For example, I'm running the below command:

C:\temp\demos\TestApp3-Call SP2\TestApp3-Call SP2\bin\Debug>"TestApp3-Call SP2.exe" 1 3

By executing the above stored procedure, the goal is to update employee with id=1 and set his/her location to location with id=3.

Here's the output of my program:

Let's double check in SQL Server to confirm that the location of employee 1 has been updated:

As you can see, now employee 1 has location set to 3, thus it has been successfully updated by the stored procedure's execution.

Conclusion

In this tip, we discussed how you can properly work with SQL Server stored procedures from a .NET application. We developed a simple C# application which calls the stored procedures in a best-practice manner, by utilizing .NET's SqlParameter class.

In subsequent tips, we will see more examples of performing different data operations from .NET.

Next Steps

Check out my tip: How to Get Started with SQL Server and .NET
Check out my tip: Querying SQL Server Tables from .NET
Check out my tip: Understanding SQL Server Connection Pooling in ADO.NET
Check the MS Docs article: .NET Framework Data Providers
Check the MS Docs article: SqlConnection Class
Check the MS Docs article: SqlCommand Class
Check the MS Docs article: SqlParameter Class

About the author

Artemakis Artemiou is a Senior SQL Server and Software Architect, Author, and a former Microsoft Data Platform MVP (2009-2018).

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2018-12-06

Friday, July 7, 2023 - 9:52:48 AM - Tahir Syed	Back To Top (91368)
Can I use MS SQL Stored Procedure to extract data from the MSSQL database, manipulate it in Temp-Table, and immediately update it in the DB2 table? (All in one SP)

Monday, February 24, 2020 - 4:48:29 PM - Alex	Back To Top (84776)
Best tutorial ever. Thanks for sharing. I didn't know how to connect to a database from C# code before, but your article helped me so much. Thanks man.