How to: Use the ADO.NET and Visual C # .NET to call the stored procedure with parameters
対 対 品
The release number of this article has been CHS310070
For Microsoft Visual Basic .NET versions of this article, see
308049.
For Microsoft Visual C .NET versions of this article, see
310071.
For Microsoft Visual J # .NET versions of this article, see
320627.
This article references the following Microsoft .NET Framework Class Bank Name Space:
System.data.sqlclient system.data.oledb
This task content
summary
Use the DataReader Return line and parameters Use the Command object's ExecuteScalar method using the Command object's ExecutenonQuery method reference
SUMMARY Equal to call the stored procedure using ADO.NET and gets the return value and returns parameters, including:
Using the DataSet object, you can collect and use the returned data rows in addition to the return value and return parameters. Use the DataReader object to collect the returned rows, traverse these rows, and collect the return value and return parameters. Use the ExecuteScalar method to return the value of the first column of the first row in the result, and return the value and return parameters. This is especially useful for the polymerization function. Use the ExecuteNonQuery method to return only the parameters and return values. Any returned row will be discarded. This is especially useful for performing operational queries. This article demonstrates the last three methods and uses
SQLCommand and
OLEDBCOMMAND two objects. Be sure to copy only the code corresponding to the managed provider you are using. If you are not sure which hosting provider should use, visit the following Microsoft Developer Network Web site:
.NET data provider http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconadonetProviders.asp In each example of this article, these parameters are added Until
Command object
Parameters collection. use
When the SQLCommand object, you don't have to add parameters in any particular order, but you must ensure that the parameter name is correct. use
When the OLEDBCommand object, you must add parameters in the correct order to use the parameters according to the name.
Back to top
Use DataReader to return row and parameters you can use
The DataReader object returns to read-only forward data streams.
The information contained in the DataReader can come from a stored procedure. This example is used
The DataReader object runs a stored procedure with input parameters and output parameters, and then traverses the return record to view the return parameters.
Create the following stored procedure on the server 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. Using the USING statement for System and System.Data namespace, this is not necessary to define declarations in these namespaces in the following code. Add this code to the top of the Form Code Module. Make sure you only copy the code corresponding to the provider you selected. SQL client Using system.data.sqlclient; OLE DB data provider Using system.data.oledb; replaces the code in the private form_load event with the following code: SQL client SQLConnection 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.It);
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 Provider OLEDBConnection Pubsconn = New OLEDBCONNECTION
("Provider = SQLOLOLDB; DATA SOURCE = Server;"
"Integrated Security = SSPI; Initial Catalog = PUBS;");
OLEDBCommand testcmd = new oledbcommand
("TestProcedure", Pubsconn;
Testcmd.commandtype = commandtype.storedProcedure; OLEDBPARETER 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 NUMTIS = 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 of the Connection object to point to the computer running SQL Server. Run this code. Note that DataReader retrieves the record and returns the parameter value. You can use the Read method of the DataReader object to traverse the returned record. The output window displays the title, return value 5 and output parameters of two books, which contain the number of records (2). Note that you must close DataReader in your code to see the parameter value. Also note that if DataReader is closed, you don't have to traverse all records in order to view the return parameters.
Back to top
ExecuteScalar method using a Command object You can use
Command object
ExecuteScalar method retrieves parameter values. In addition,
ExecuteScalar returns the first column of the first row of the stored procedure. This is especially useful for the polymerization function, as shown in the following example.
Create the following stored procedure on the server 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. Using the USING statement for System and System.Data namespace, this is not necessary to define declarations in these namespaces in the following code. Add this code to the top of the Form Code Module. Make sure you only copy the code corresponding to the provider you selected. SQL client using system.data.sqlclient; OLE DB data provider Using system.data.oledb; add the following code to the Form_Load event: SQL client string 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.It);
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 Provider String Strcount;
OLEDBCONNECTION PUBSCONN = New OLEDBCONNECTION
("Provider = SQLOLOLDB; 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; idinciRection.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 of the Connection object to point to the computer running SQL Server. Run this code. Note that the ExecuteScalar method of the Command object returns the parameters. ExecuteScalar also returns the value of the first line of the first line of the returned row collection. Therefore, the value of IntCount is the result of the statistical function of the stored procedure.
Back to top
Use the ExecutenonQuery method of the Command object this example
The ExecuteNonQuery method runs the query and returns the parameter value.
ExecuteNonQuery also returns the number of records that are affected after running this query. but,
ExecuteNonQuery does not return any rows or columns from this stored procedure.
If you only need to know the number of changes to the change, then when using the INSERT, UPDATE or DELETE statement,
The ExecuteNonQuery method is particularly useful. You will receive -1 when you use the SELECT statement during the stored procedure, because the query does not affect any rows.
Create the following stored procedure on a computer 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. Using the USING statement for System and System.Data namespace, this is not necessary to define declarations in these namespaces in the following code. Add this code to the top of the Form Code Module. Make sure you only copy the code corresponding to the provider you selected. SQL client using system.data.sqlclient; OLE DB data provider Using system.data.oledb; replacing the code after the Private Form_Load event in the Form1 code module with the following code: SQL client string strowaffect;
SqlConnection Pubsconn = New SqlConnection
("Data Source = Server; Integrated Security = SSPI;"
"inTIAL CATALOG = PUBS;");
SQLCommand testcmd = new sqlcommand
("TestProcedure3", Pubsconn;
Testcmd.commandtype = commandtype.storedProcedure;
SQLParameter Retval = Testcmd.Parameters.Add
("Retval", SqldbType.It);
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 ();
StrowAffect = Testcmd.executenonQuery () .tostring ();
Console.writeline ("Number of Rows:" StrowAffect);
Console.writeline ("Return Value:" RetVal.Value); OLE DB Data Provider Int IntrowAffected;
OLEDBCONNECTION PUBSCONN = New OLEDBCONNECTION
("Provider = SQLOLOLDB; 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;
OLEDBPARETER 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 of the Connection object to point to the computer running SQL Server. Run this code. The Output window displays the value of the affected row (INTrowAffect) and the return parameter. Back to top
Refer to other information, visit the following MSDN Web site:
.NET framework library delisting http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconthenetframeworkClassLibrary.asp Using DataReader Search data http://msdn.microsoft. COM / library / default.asp? URL = / library / en-us / cpguide / html / cpcontheadonetdatareader.asp
