Detailed explanation of C# call stored procedures (with return value, parameter input and output, etc.)

This article describes the method of calling stored procedures in C#. Share it for your reference, as follows:

CREATE PROCEDURE [dbo].[GetNameById]
 @studentid varchar(8),
 @studentname nvarchar(50) OUTPUT
AS
BEGIN
 SELECT @studentname=studentname FROM student
  WHERE studentid=@studentid
 if @@Error<>0
 RETURN -1
 else
 RETURN 0
END

using (SqlConnection conn = new SqlConnection(connStr))
{
  try
  {
    SqlCommand cmd = new SqlCommand("GetNameById", conn);
     = ;
    ("@studentid", "09888888");// Assign values to the input parameters    SqlParameter parOutput =("@studentname", , 50);//Define the output parameters     = ;//The parameter type is Output    SqlParameter parReturn = new SqlParameter("@return", );
     = ; //The parameter type is ReturnValue    (parReturn);
    ();
    ();
    (());  //Display the value of the output parameter    (());//Show return value  }
  catch ( ex)
  {
    ();
  }
}

Create PROCEDURE AddOrderTran
  @country nvarchar(100),
  @adds nvarchar(100),
  @ynames nvarchar(100),
  @pids nvarchar(100),
  @cellp nvarchar(100),
  @cphone nvarchar(100),
  @amounts nvarchar(100),
  @cartnumber nvarchar(100)
as
  Declare @id int
  BEGIN TRANSACTION
    insert into Orders(Order_Country,Order_Adress,Order_UserName,Order_PostID,Cells,Order_Phone,Total_pay,CartNumber,IsPay)
      values (@country,@adds,@ynames,@pids,@cellp,@cphone,@amounts,@cartnumber,'0')
    Select @id=@@identity
    insert into Orders_Item (OrderNumber,ProductsID,Products_Color,Products_Price,Order_Qty,Item_Total)
      select @id,Carts_Item.ProductsID,Carts_Item.Products_Color,Carts_Item.Products_Price,Carts_Item.Item_Qty,Carts_Item.Total_Pay
      from Carts_Item where Carts_Item.CartNumber=@cartnumber
    delete Carts_Item where CartNumber=@cartnumber
    IF @@error &lt;&gt; 0 --An error occurred
    BEGIN
      ROLLBACK TRANSACTION
      RETURN 0
    END
    ELSE
    BEGIN
      COMMIT TRANSACTION
      RETURN @id  --Execution successfully
  END

#region Execute stored proceduresSqlParameter[] param = new SqlParameter[]
{
   new SqlParameter("@country",country),
   new SqlParameter("@adds",adds),
   new SqlParameter("@ynames",ynames),
   new SqlParameter("@pids", pids),
   new SqlParameter("@cellp",cellp),
   new SqlParameter("@cphone", cphone),
   new SqlParameter("@amounts",amounts),
   new SqlParameter("@cartnumber",cartnumber),
   new SqlParameter("@return",)
};
param[8].Direction = ;
("AddOrderTran", param);
object obj = param[8].Value; //Accept the return value//string connStr = ["ConStr"].ToString();
//using (SqlConnection conn = new SqlConnection(connStr))
//{
//  ();
//  SqlCommand cmd = new SqlCommand("AddOrderTran", conn);
//   = ;
//  SqlParameter para1 = new SqlParameter("@country", country);
// = ; // Parameter direction is the input parameter//  (para1);
//  SqlParameter para2 = new SqlParameter("@adds", adds);
//   = ;
//  (para2);
//  SqlParameter para3 = new SqlParameter("@ynames", ynames);
//   = ;
//  (para3);
//  SqlParameter para4 = new SqlParameter("@pids", pids);
//   = ;
//  (para4);
//  SqlParameter para5 = new SqlParameter("@cellp", cellp);
//   = ;
//  (para5);
//  SqlParameter para6 = new SqlParameter("@cphone", cphone);
//   = ;
//  (para6);
//  SqlParameter para7 = new SqlParameter("@amounts", amounts);
//   = ;
//  (para7);
//  SqlParameter para8 = new SqlParameter("@cartnumber", cartnumber);
//   = ;
//  (para8);
//  SqlParameter paraReturn = new SqlParameter("@return", );
// = ; // Parameter direction is the return parameter//  (paraReturn);
//  ();
//  object obj = paraReturn;
//  if (() == "0")
//  {
// //Stored procedure failed to execute//  }
//  else
//  {
//    //success//  }
//}
//#endregion

The database in this article uses the SQL Server's own data Northwind

1. Only return stored procedures for a single record set

SqlConnection sqlconn = new SqlConnection(conn);
SqlCommand cmd = new SqlCommand();
// Set up SQL connection = sqlconn;
// If the statement is executed = "Categoriestest1";
// Specify the execution statement as a stored procedure = ;
SqlDataAdapter dp = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
// Fill in dataset(ds);
// The following is the display effect = ds;
();

Stored procedure Categoriestest1

CREATE PROCEDURE Categoriestest1
 AS
 select *
 from Categories
 GO

2. Stored procedures without input and output

SqlConnection sqlconn = new SqlConnection(conn);
SqlCommand cmd = new SqlCommand();
 = sqlconn;
 = "Categoriestest2";
 = ;
();
// Execute and display the number of rows affected = ().ToString();
();

Stored procedure Categoriestest2

CREATE PROCEDURE Categoriestest2 AS
 insert into 
 (CategoryName,[Description],[Picture])
 values ('test1','test1',null)
 GO

3. Stored procedures with return values

SqlConnection sqlconn = new SqlConnection(conn);
SqlCommand cmd = new SqlCommand();
 = sqlconn;
 = "Categoriestest3";
 = ;
// Create parametersIDataParameter[] parameters = {
     new SqlParameter("rval", ,4)
   };
// Set the parameter type to return value typeparameters[0].Direction = ;
// Add parameters(parameters[0]);
();
// Execute stored procedure and return the number of rows affected = ().ToString();
();
// Show the number of rows and return values that are affected += "-" + parameters[0].() ;

Stored procedure Categoriestest3

CREATE PROCEDURE Categoriestest3
 AS
 insert into 
 (CategoryName,[Description],[Picture])
 values ('test1','test1',null)
return @@rowcount
 GO

4. Stored procedures with input parameters and output parameters

SqlConnection sqlconn = new SqlConnection(conn);
SqlCommand cmd = new SqlCommand();
 = sqlconn;
 = "Categoriestest4";
 = ;
// Create parametersIDataParameter[] parameters = {
     new SqlParameter("@Id", ,4) ,
     new SqlParameter("@CategoryName", ,15) ,
   };
// Set parameter typeparameters[0].Direction = ; // Set as output parameterparameters[1].Value = "testCategoryName";
// Add parameters(parameters[0]);
(parameters[1]);
();
// Execute stored procedure and return the number of rows affected = ().ToString();
();
// Display the affected number of rows and output parameters += "-" + parameters[0].() ;

Stored procedure Categoriestest4

CREATE PROCEDURE Categoriestest4
 @id int output,
 @CategoryName nvarchar(15)
 AS
 insert into 
 (CategoryName,[Description],[Picture])
 values (@CategoryName,'test1',null)
set @id = @@IDENTITY
 GO

5. Stored procedures with return values, input parameters, and output parameters at the same time

SqlConnection sqlconn = new SqlConnection(conn);
SqlCommand cmd = new SqlCommand();
 = sqlconn;
 = "Categoriestest5";
 = ;
// Create parametersIDataParameter[] parameters = {
     new SqlParameter("@Id", ,4) ,
     new SqlParameter("@CategoryName", ,15) ,
     new SqlParameter("rval", ,4)
   };
// Set parameter typeparameters[0].Direction = ;    // Set as output parameterparameters[1].Value = "testCategoryName";         // Assign values to input parametersparameters[2].Direction = ; // Set to return value// Add parameters(parameters[0]);
(parameters[1]);
(parameters[2]);
();
// Execute stored procedure and return the number of rows affected = ().ToString();
();
// Display the number of rows affected, output parameters and return values += "-" + parameters[0].() + "-" + parameters[2].();

Stored procedure Categoriestest5

CREATE PROCEDURE Categoriestest5
 @id int output,
 @CategoryName nvarchar(15)
 AS
 insert into 
 (CategoryName,[Description],[Picture])
 values (@CategoryName,'test1',null)
set @id = @@IDENTITY
return @@rowcount
 GO

6. Stored procedures that return both parameters and recordsets

SqlConnection sqlconn = new SqlConnection(conn);
SqlCommand cmd = new SqlCommand();
 = sqlconn;
 = "Categoriestest6";
 = ;
// Create parametersIDataParameter[] parameters = {
     new SqlParameter("@Id", ,4) ,
     new SqlParameter("@CategoryName", ,15) ,
     new SqlParameter("rval", ,4)          // Return value  };
// Set parameter typeparameters[0].Direction = ;    // Set as output parameterparameters[1].Value = "testCategoryName";          // Assign values to input parametersparameters[2].Direction = ;  // Set to return value// Add parameters(parameters[0]);
(parameters[1]);
(parameters[2]);
SqlDataAdapter dp = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
// Fill in dataset(ds);
// Show result set = [0];
();
 = "";
// Display output parameters and return values += parameters[0].() + "-" + parameters[2].();

Stored procedure Categoriestest6

CREATE PROCEDURE Categoriestest6
 @id int output,
 @CategoryName nvarchar(15)
 AS
 insert into 
 (CategoryName,[Description],[Picture])
 values (@CategoryName,'test1',null)
set @id = @@IDENTITY
 select * from Categories
return @@rowcount
 GO

7. Returns stored procedures for multiple recordsets

SqlConnection sqlconn = new SqlConnection(conn);
SqlCommand cmd = new SqlCommand();
 = sqlconn;
 = "Categoriestest7";
 = ;
SqlDataAdapter dp = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
// Fill in dataset(ds);
// Show result set 1 = [0];
();
// Show result set 2 = [1];
();

Stored procedure Categoriestest7

CREATE PROCEDURE Categoriestest7
 AS
 select * from Categories
 select * from Categories
 GO

For more information about C# related content, please check out the topic of this site:Tutorial on the usage of common C# controls》、《Summary of WinForm control usage》、《C# data structure and algorithm tutorial》、《Introduction to C# object-oriented programming tutorial"and"Summary of thread usage techniques for C# programming》

I hope this article will be helpful to everyone's C# programming.