HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and
Visual C# .NET
View products that this article applies to.
This article was previously published under Q310070
For a Microsoft Visual Basic .NET version of this article, see 308049.
For a Microsoft Visual C++ .NET version of this article, see 310071.
For a Microsoft Visual J# .NET version of this article, see 320627.
This article refers to the following Microsoft .NET Framework Class
Library namespaces:
System.Data.SqlClient
System.Data.OleDb
IN THIS TASK
SUMMARY
Use DataReader to Return Rows and Parameters
Use the ExecuteScalar Method of the Command Object
Use the ExecuteNonQuery Method of the Command Object
REFERENCES
SUMMARY
There are several ways to use ADO.NET to call a stored procedure and
to get back return values and return parameters, including:
Use a DataSet object to gather the returned rows and to work with
these rows in addition to the return values and the return parameters.
Use a DataReader object to gather the returned rows, to move through
these rows, and then to gather return values and return parameters.
Use the ExecuteScalar method to return the value from the first column
of the results' first row with the return values and the return
parameters. This is most useful with aggregate functions.
Use the ExecuteNonQuery method to return only the return parameters
and the return values. Any returned rows are discarded. This is most
useful for executing action queries.
This article demonstrates the last three methods and uses both the
SqlCommand and the OleDbCommand objects. Make sure that you copy only
the code for the managed provider that you are using. If you are not
sure which managed provider you should use, visit the following
Microsoft Developer Network Web site:
..NET Data Providers
http://msdn.microsoft.com/library/de...tproviders.asp
In each of the samples in this article, the parameters are added to
the Parameters collection of the Command object. When you use the
SqlCommand object, you do not have add the parameters in any
particular order, but the parameters must have the correct name. When
you use the OleDbCommand object, you must add the parameters in the
correct order, and you cannot use the parameters by name.
back to the top
Use DataReader to Return Rows and Parameters
You can use the DataReader object to return a read-only, forward-only
stream of data. The information that the DataReader contains can come
from a stored procedure. This example uses the DataReader object to
run a stored procedure that has an input and an output parameter and
then moves through the returned records to view the return parameters.
Create the following stored procedure on the server that is running
Microsoft SQL Server:Create Procedure TestProcedure
(
@au_idIN varchar (11),
@numTitlesOUT Integer OUTPUT
)
AS
select A.au_fname, A.au_lname, T.title
from authors as A join titleauthor as TA on
A.au_id=TA.au_id
join titles as T
on T.title_id=TA.title_id
where A.au_id=@au_idIN
set @numTitlesOUT = @@Rowcount
return (5)
Create a new Visual C# .NET Windows Application project.
Use the using statement on the System and the System.Data namespaces
so that you do not have to qualify declarations in those namespaces
later in your code. Add this code to the top of the Form code module.
Make sure to copy only the code for the provider that you have
chosen.SQL Clientusing System.Data.SqlClient;
OLE DB Data Providerusing System.Data.OleDb;
Replace the code in the private Form_Load event with the following
code:SQL ClientSqlConnection PubsConn = new SqlConnection
("Data Source=server;integrated " +
"Security=sspi;initial catalog=pubs;");
SqlCommand testCMD = new SqlCommand
("TestProcedure", PubsConn);
testCMD.CommandType = CommandType.StoredProcedure;
SqlParameter RetVal = testCMD.Parameters.Add
("RetVal", SqlDbType.Int);
RetVal.Direction = ParameterDirection.ReturnValue;
SqlParameter IdIn = testCMD.Parameters.Add
("@au_idIN", SqlDbType.VarChar, 11);
IdIn.Direction = ParameterDirection.Input;
SqlParameter NumTitles = testCMD.Parameters.Add
("@numtitlesout", SqlDbType.VarChar, 11);
NumTitles.Direction = ParameterDirection.Output ;
IdIn.Value = "213-46-8915";
PubsConn.Open();
SqlDataReader myReader = testCMD.ExecuteReader();
Console.WriteLine ("Book Titles for this Author:");
while (myReader.Read())
{
Console.WriteLine ("{0}", myReader.GetString (2));
};
myReader.Close() ;
Console.WriteLine("Number of Rows: " + NumTitles.Value );
Console.WriteLine("Return Value: " + RetVal.Value);
OLE DB Data ProviderOleDbConnection PubsConn = new OleDbConnection
("Provider=SQLOLEDB;Data Source=server;" +
"integrated Security=sspi;initial catalog=pubs;");
OleDbCommand testCMD = new OleDbCommand
("TestProcedure", PubsConn);
testCMD.CommandType = CommandType.StoredProcedure;
OleDbParameter RetVal = testCMD.Parameters.Add
("RetVal", OleDbType.Integer);RetVal.Direction =
ParameterDirection.ReturnValue;
OleDbParameter IdIn = testCMD.Parameters.Add
("@au_idIN", OleDbType.VarChar, 11);
IdIn.Direction = ParameterDirection.Input;
OleDbParameter NumTitles = testCMD.Parameters.Add
("@numtitlesout", OleDbType.VarChar, 11);
NumTitles.Direction = ParameterDirection.Output;
IdIn.Value = "213-46-8915";
PubsConn.Open();
OleDbDataReader myReader = testCMD.ExecuteReader();
Console.WriteLine ("Book Titles for this Author:");
while (myReader.Read())
{
Console.WriteLine ("{0}", myReader.GetString (2));
};
myReader.Close() ;
Console.WriteLine("Number of Rows: " + NumTitles.Value );
Console.WriteLine("Return Value: " + RetVal.Value);
Modify the connection string for the Connection object to point to the
computer that is running SQL Server.
Run the code. Notice that the DataReader retrieves the records and
then returns the parameter values. You can use the Read method of the
DataReader object to move through the returned records.
The Output window displays the titles of two books, the return value
of 5, and the output parameter, which contains the number of records
(2). Notice that you must close the DataReader in the code to see the
parameter values. Additionally, note that you do not have to move
through all of the records to see the return parameters if the
DataReader is closed.
back to the top
Use the ExecuteScalar Method of the Command Object
You can use the ExecuteScalar method of the Command object to retrieve
parameter values. Additionally, ExecuteScalar returns the first column
of the first row of the stored procedure. This is most useful for
aggregate functions as in the following example.
Create the following stored procedure on the server that is running
SQL Server:Create Procedure TestProcedure2
(
@au_idIN varchar (11)
)
As
/* set nocount on */
select count (T.title)
from authors as A join titleauthor as TA on
A.au_id=TA.au_id
join titles as T
on T.title_id=TA.title_id
where A.au_id=@au_idIN
Return(5)
Create a new Visual C# .NET Windows Application project.
Use the using statement on the System and the System.Data namespaces
so that you do not have to qualify declarations in those namespaces
later in your code. Add this code to the top of the Form code module.
Make sure that you copy only the code for the provider that you have
chosen.SQL Clientusing System.Data.SqlClient;
OLE DB Data Providerusing System.Data.OleDb;
Add the following code to the Form_Load event:SQL Clientstring
strCount;
SqlConnection PubsConn = new SqlConnection
("Data Source=server;integrated " +
"Security=sspi;initial catalog=pubs;");
SqlCommand testCMD = new SqlCommand
("TestProcedure2", PubsConn);
testCMD.CommandType = CommandType.StoredProcedure;
SqlParameter RetVal = testCMD.Parameters.Add
("RetVal", SqlDbType.Int);
RetVal.Direction = ParameterDirection.ReturnValue;
SqlParameter IdIn = testCMD.Parameters.Add
("@au_idIN", SqlDbType.VarChar, 11);
IdIn.Direction = ParameterDirection.Input;
IdIn.Value = "213-46-8915";
PubsConn.Open();
strCount =testCMD.ExecuteScalar ().ToString() ;
Console.WriteLine("Number of Rows: " + strCount );
Console.WriteLine("Return Value: " + RetVal.Value);
OLE DB Data Providerstring strCount;
OleDbConnection PubsConn = new OleDbConnection
("Provider=SQLOLEDB;Data Source=server;" +
"integrated Security=sspi;initial catalog=pubs;");
OleDbCommand testCMD = new OleDbCommand
("TestProcedure2", PubsConn);
testCMD.CommandType = CommandType.StoredProcedure;
OleDbParameter RetVal = testCMD.Parameters.Add
("RetVal", OleDbType.Integer);
RetVal.Direction = ParameterDirection.ReturnValue;
OleDbParameter IdIn = testCMD.Parameters.Add
("@au_idIN", OleDbType.VarChar, 11);
IdIn.Direction = ParameterDirection.Input;
IdIn.Value = "213-46-8915";
PubsConn.Open();
strCount = testCMD.ExecuteScalar().ToString() ;
Console.WriteLine("Number of Rows: " + strCount);
Console.WriteLine("Return Value: " + RetVal.Value);
Modify the connection string for the Connection object to point to the
computer that is running SQL Server.
Run the code. Notice that the ExecuteScalar method of the Command
object returns the parameters. ExecuteScalar also returns the value of
column 1, row 1 of the returned rowset. Therefore, the value of
intCount is the result of the count function from the stored
procedure.
back to the top
Use the ExecuteNonQuery Method of the Command Object
This sample uses the ExecuteNonQuery method to run the query and to
return the parameter values. ExecuteNonQuery also returns the number
of records that are affected after the query runs. However,
ExecuteNonQuery does not return any rows or columns from the stored
procedure.
The ExecuteNonQuery method is most useful when you use INSERT, UPDATE,
or DELETE statements if you only have to know how many rows are
changed. In a stored procedure in which you are using only a SELECT
statement, you receive -1 because no rows are affected by the query.
Create the following stored procedure on the computer that is running
SQL Server:Create Procedure TestProcedure3
(
@au_idIN varchar (11),
@au_fnam varchar (30)
)
As
/* set nocount on */
Update authors set au_fname = @au_fnam
where au_id = @au_idin
return (5)
Create a new Visual C# .NET Windows Application project.
Use the using statement on the System and the System.Data namespaces
so that you do not have to qualify declarations in those namespaces
later in your code. Add this code to the top of the Form code module.
Make sure that you copy only the code for the provider that you have
chosen.SQL Clientusing System.Data.SqlClient;
OLE DB Data Providerusing System.Data.OleDb;
Replace the code below the private Form1_Load event in the Form1 code
module with the following code:SQL Clientstring strRowAffect;
SqlConnection PubsConn = new SqlConnection
("Data Source=server;integrated Security=sspi;" +
"initial catalog=pubs;");
SqlCommand testCMD = new SqlCommand
("TestProcedure3", PubsConn);
testCMD.CommandType = CommandType.StoredProcedure;
SqlParameter RetVal = testCMD.Parameters.Add
("RetVal", SqlDbType.Int);
RetVal.Direction = ParameterDirection.ReturnValue;
SqlParameter IdIn = testCMD.Parameters.Add
("@au_idIN", SqlDbType.VarChar, 11);
IdIn.Direction = ParameterDirection.Input;
SqlParameter FnameIn = testCMD.Parameters.Add
("@au_fnam", SqlDbType.VarChar, 30);
FnameIn.Direction = ParameterDirection.Input;
IdIn.Value = "213-46-8915";
FnameIn.Value = "Marjorie";
PubsConn.Open();
strRowAffect =testCMD.ExecuteNonQuery ().ToString() ;
Console.WriteLine("Number of Rows: " + strRowAffect );
Console.WriteLine("Return Value: " + RetVal.Value);
OLE DB Data Providerint intRowAffected;
OleDbConnection PubsConn = new OleDbConnection
("Provider=SQLOLEDB;Data Source=server;" +
"integrated Security=sspi;initial catalog=pubs;");
OleDbCommand testCMD = new OleDbCommand
("TestProcedure3", PubsConn);
testCMD.CommandType = CommandType.StoredProcedure;
OleDbParameter RetVal = testCMD.Parameters.Add
("RetVal", OleDbType.Integer);
RetVal.Direction = ParameterDirection.ReturnValue;
OleDbParameter IdIn = testCMD.Parameters.Add
("@au_idIN", OleDbType.VarChar, 11);
IdIn.Direction = ParameterDirection.Input;
OleDbParameter FnameIn = testCMD.Parameters.Add
("@au_fname", OleDbType.VarChar, 30);
FnameIn.Direction = ParameterDirection.Input;
IdIn.Value = "213-46-8915";
FnameIn.Value = "Marjorie";
PubsConn.Open();
intRowAffected = testCMD.ExecuteNonQuery();
Console.WriteLine("Number of Rows affected: " + intRowAffected);
Console.WriteLine(RetVal.Value);
Modify the connection string for the Connection object to point to the
computer that is running SQL Server.
Run the code. The Output window displays the number of affected rows
(intRowAffect) and the value of the return parameter.
back to the top
REFERENCES
For additional information, visit the following MSDN Web sites:
Introduction to the .NET Framework Class Library
http://msdn.microsoft.com/library/de...asslibrary.asp
Retrieving Data Using the DataReader
http://msdn.microsoft.com/library/de...datareader.asp
back to the top