Working with SQL Server Stored Procedures from C#SQL Server Stored Procedures are functions within a database which are optimised queries. Here's how to use stored procedures from C#.
SQL Server Stored Procedures are subroutines, which are essentially blocks of code that can be called from other parts of a program. In the context of databases, these subroutines are stored within the database data dictionary and are often used to encapsulate data by providing validation or returning the results of a query.
Calling SQL Server Stored Procedures from C#
In this short example, we will look at a procedure stored in a database of authors, can call that stored procedure from C#, passing in parameters and returning a value.
This tutorial, which you can trust for its accuracy and reliability, is based on an example from the Microsoft Help and Support.
The Authors database has a stored procedure called GetAuthorsByLastName, which is used in this example. This procedure accepts one parameter of the author's last name. The procedure returns the number of records found, which can be used for various purposes in your application.
In C# (ASP.Net, WinForms or any other project type), we need to establish a connection to the database as usual.
string authorName = "Bob";
try
{
SqlConnection MyConnection = new SqlConnection("server=(local);database=pubs;UID=myUser;PWD=myPassword;");
SqlDataAdapter MyDataAdapter = new SqlDataAdapter("GetAuthorsByLastName", MyConnection);
MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@au_lname", SqlDbType.VarChar, 40));
MyDataAdapter.SelectCommand.Parameters["@au_lname"].Value = authorName.Trim();
MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@RowCount", SqlDbType.Int, 4));
MyDataAdapter.SelectCommand.Parameters["@RowCount"].Direction = ParameterDirection.Output;
DataSet DS = new DataSet();
MyDataAdapter.Fill(DS, "AuthorsByLastName");
Console.WriteLine(MyDataAdapter.SelectCommand.Parameters[1].Value + " Rows Found.");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
MyDataAdapter.Dispose();
MyConnection.Close();
} In this code example, we connect to the pub's sample database and create a SqlDataAdaptor specifying the name of the SQL Server Stored Procedures and the connection object. We set up the select command object as a stored procedure, configure some parameters and add them to the command object. This example has one input parameter (default) and one output parameter using the ParameterDirection.Output. The code snippet DS.Tables ["AuthorsByLastName"].DefaultView is used to access the data of the rows returned, which can be used as the data source for DataGridView or list views.
You can access the data of the rows returned using DS.Tables["AuthorsByLastName"].DefaultView as the data source for DataGridView or list views.
For reference, here is the SQL Server Stored Procedure:
Use Pubs
Go
Create Procedure GetAuthorsByLastName (@au_lname varchar(40), @RowCount int output)
as
select * from authors where au_lname like @au_lname;
/* @@ROWCOUNT returns the number of rows affected by the last statement. */
select @RowCount=@@ROWCOUNT Creating SQL Server Stored Procedures with C#
Connecting to MSSQL and creating SQL Server stored procedures with C# to check if a stored procedure exists and create one if it doesn't.
To create a connection to the SQL Server, you need to specify the server name, database name, and authentication details. Once the connection is established, you can execute a command to interrogate the stored procedures for the database and store the results in the SqlDataReader. If the DataReader contains no rows, then the stored procedure does not exist, and it is safe to create it. If it does exist, you can update or delete it.
try
{
SqlConnection MyConnection = new SqlConnection("server=(local);database=pubs;UID=myUser;PWD=myPassword;");
SqlCommand MyCommand = new SqlCommand("select * from sysobjects where id = object_id(N'GetAuthorsByLastName') and OBJECTPROPERTY(id, N'IsProcedure') = 1", MyConnection);
MyCommand.CommandType = CommandType.Text;
MyCommand.Connection.Open();
SqlDataReader MyDataReader = MyCommand.ExecuteReader();
// If any rows are returned, the stored procedure that you are trying
// to create already exists. Therefore, try to create the stored procedure
// only if it does not exist.
if (!MyDataReader.Read())
{
MyCommand.CommandText = "create procedure GetAuthorsByLastName(@au_lname varchar(40), select * from authors where au_lname like @au_lname; select @RowCount=@@ROWCOUNT";
MyDataReader.Close();
MyCommand.ExecuteNonQuery();
}
else
{
MyDataReader.Close();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
MyCommand.Dispose();
MyConnection.Close();
} 
