Learning never exhausts the mind

By

SQL Server Stored Procedures are sub routines 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 results of a query.

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, create a SqlDataAdaptor specifying the name of the stored procedure 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

 

Did you enjoy this post? Let others know!

If you enjoyed this post, please share it with others. Click one of the social media buttons below to share on Facebook, Twitter, LinkedIn, Pinterest or email to a friend.

3 thoughts on “Calling SQL Server Stored Procedures from C#
  • 16th December 2011 at 12:00 am

    Nice. Would like to see an example where the stored procedure returns a cursor.

    Reply
  • 8th November 2011 at 12:00 am

    Thanks a lot.... this helped me ! to achieve what I wanted

    Reply
  • 15th July 2011 at 12:00 am

    Hey Its Great!!! good job

    Reply

Leave a Reply

Fields marked with * are mandatory.

We respect your privacy, and will not make your email public. Hashed email address may be checked against Gravatar service to retrieve avatars. This site uses Akismet to reduce spam. Learn how your comment data is processed.