Web Design that taps into the haromny and vision of your dreams.

Calling SQL Server Stored Procedures from C#

A look at SQL Server Stored Procedures

Written By on in C#

340 words, estimated reading time 2 minutes.

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.

Active Data Objects (ADO.Net) Series
  1. What are Active Data Objects?
  2. Connecting to a SQL Server Database with C#
  3. Consuming Data in Windows Forms C# Applications
  4. Using ADO.Net Controls in ASP.Net Pages
  5. Modifying Data and Updating Databases with C#
  6. Connecting to MySql Databases with C# / ASP.Net
  7. Reading Excel Spreadsheets with C# ADO.Net
  8. Creating SQL Server Stored Procedures in C#
  9. Calling SQL Server Stored Procedures from C#
  10. Importing and Exporting XML from a DataSet

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

Last updated on: Thursday 22nd June 2017

 

Comments
Dave

Dave

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

Reply to Dave
Mahantesh

Mahantesh

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

Reply to Mahantesh
Dominic Palacio

Dominic Palacio

Hey Its Great!!! good job

Reply to Dominic Palacio

 

Leave a Reply

Your email address will not be published.





If you find something abusive or that does not comply with our terms or guidelines please flag it as inappropriate.

Copyright © 2001-2018 Tim Trott, all rights reserved. Web Design by Azulia Designs

This web page is licensed for your personal, private, non-commercial use only.

Disclaimer, Privacy & LegalSitemapContact Me