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 exposed to applications accessing relational databases. The subroutine is stored within the database data dictionary and is 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 is based on an example from the Microsoft Help and Support.
The Authors database has a stored procedure called GetAuthorsByLastName which accepts one parameter of the author's last name. The procedure returns the number of records found.
In C# (ASP.Net, WinForms or any other project type) we need to establish a connection to the database in the usual way.
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 create a connection 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 to be 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.
You will be able to 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 that are 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.
We need to create a connection to the SQL Server and execute a command that will interrogate the stored procedures for the database and store the results in the SqlDataReader. If the DataReader does not contain any rows then the stored procedure does not exist and it is safe to create it. If it does exist you may wish to 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();
} 
