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
Nice. Would like to see an example where the stored procedure returns a cursor.
Thanks a lot.... this helped me ! to achieve what I wanted
Hey Its Great!!! good job