. Net call a stored procedure returns the return value _ Practical tips

1. Stored Procedures

Set ANSI_NULLS on 
go 
SET quoted_identifier in 
go 
-============================================= - 
-Author: <Author,,Name>- 
Create Date: <create date,,>- 
Description: <description, >- 
-============================================= 
alter PROCEDURE getorderline 
@orderId varchar ( As 
BEGIN 
--SET NOCOUNT on added to prevent extra result sets from-- 
interfering with SELECT stat Ements. 
SET NOCOUNT on; 

SELECT * from orderline where OrderId = @orderId; 

return 123; 
End Go 

Note Stored procedures can only return int types, and if a string is returned, a type conversion error will be reported

2 Background call

DataTable dt = new DataTable (); String connstr = system.configuration.configurationmanager.connectionstrings["BLL. Properties.Settings.ShoppingDBConnectionString "]. 
ToString (); 
Using (SqlConnection conn= New SqlConnection (ConnStr)) {string callname = "Getorderline"; 
using (SqlCommand command = new SqlCommand (CALLNAME, conn)) {command.commandtype = CommandType.StoredProcedure; Sqlparameter[] sps = {new SqlParameter ("@orderId", sqldbtype.varchar,50), New SqlParameter ("@return", SqlDbType.Int)// 

Registration return value type}; Sps[0]. 
Value = "43C7CF15-6B2F-4D18-92B2-DBE827F30DFC"; SPS[1]. Direction = ParameterDirection.ReturnValue; Returns the argument type command. 
Parameters.addrange (SPS); Using (SqlDataAdapter SDA =new SqlDataAdapter ()) {SDA. 
SelectCommand = command; Sda. 
Fill (DT); Console.WriteLine (SDA. Getfillparameters () [1]. 
Value); Console.WriteLine (Sps[1]. Value); Fetch to the returned value}} if (dt. rows.count>0) {for (int i = 0; i < dt. rows.count;i++) {Console.WriteLine (dt. rows[i]["ProductId"]+ ":" +dt. Rows[i]["Productprice"]+ ":" +dt. 
rows[i]["ProductCount"]); } console.readline ();