C # Call stored procedure details (with return value, parameter input and output, etc.) _c# tutorial

The example in this article describes how C # invokes a stored procedure. Share to everyone for your reference, specific as follows:

CREATE PROCEDURE [dbo]. [Getnamebyid]
 @studentid varchar (8),
 @studentname nvarchar 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);
    Cmd.commandtype = CommandType.StoredProcedure; Cmd.　　Parameters.addwithvalue ("@studentid", "09888888"); Assign value to input parameter SqlParameter paroutput =cmd.　　Parameters.Add ("@studentname", SqlDbType.NVarChar, 50);　　Define OUTPUT parameters paroutput.direction = ParameterDirection.Output;
    The parameter type is output SqlParameter Parreturn = new SqlParameter ("@return", SqlDbType.Int); 　　Parreturn.direction = ParameterDirection.ReturnValue; The parameter type is returnvalue cmd.
    Parameters.Add (Parreturn); Conn.
    Open (); Cmd.
    ExecuteNonQuery ();  MessageBox.Show (ParOutput.Value.ToString ());　　Displays the value of the output parameter MessageBox.Show (parReturn.Value.ToString ()); Displays the return value} catch (System.Exception ex) {MessageBox.Show (ex).
  message); }
}

Create PROCEDURE addordertran @country nvarchar (@adds nvarchar), @ynames nvarchar (MB), @pids nvarchar ( @cellp nvarchar (m), @cphone nvarchar, @amounts nvarchar (m), @cartnumber nvarchar (m) as Declare @id int BEGIN TRANSACTION INSERT INTO Orders (Order_country,order_adress,order_username,order_postid,cells,order_phon E,total_pay,cartnumber,ispay) VALUES (@country, @adds, @ynames, @pids, @cellp, @cphone, @amounts, @cartnumber, ' 0 ') Sele CT @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 <> 0--Error BEGIN ROLLBACK TRANSACTION return 0 End ELSE Begi N COMMIT TRANSACTION RETURN @id--Execution success End

 

#region execute stored procedure sqlparameter[] param = new sqlparameter[] {new SqlParameter ("@country", country), New SqlParameter (" @adds ", adds), New SqlParameter (" @ynames ", Ynames), New SqlParameter (" @pids ", PIDs), New SqlParameter (" @cellp ", cell p), New SqlParameter ("@cphone", Cphone), New SqlParameter ("@amounts", amounts), New SqlParameter ("@cartnumber", car
Tnumber), New SqlParameter ("@return", SqlDbType.Int)}; PARAM[8].
Direction = ParameterDirection.ReturnValue; MscL.
Sqlhelper.runprocedure ("Addordertran", param); Object obj = param[8]. Value; Accept the return value//string connstr = system.configuration.configurationmanager.appsettings["Constr"].
ToString (); using (SqlConnection conn = new SqlConnection (CONNSTR))//{//Conn.
Open ();
SqlCommand cmd = new SqlCommand ("Addordertran", conn);
Cmd.commandtype = CommandType.StoredProcedure;
SqlParameter para1 = new SqlParameter ("@country", country); Para1. Direction = ParameterDirection.Input; Parameter direction is input parameter//cmd. Parameters.Add (PARA1);
SqlParameter para2 = new SqlParameter ("@adds", adds); Para2.
Direction = ParameterDirection.Input; Cmd.
Parameters.Add (PARA2);
SqlParameter para3 = new SqlParameter ("@ynames", ynames); Para3.
Direction = ParameterDirection.Input; Cmd.
Parameters.Add (PARA3);
SqlParameter para4 = new SqlParameter ("@pids", PIDs); Para4.
Direction = ParameterDirection.Input; Cmd.
Parameters.Add (PARA4);
SqlParameter para5 = new SqlParameter ("@cellp", CELLP); Para5.
Direction = ParameterDirection.Input; Cmd.
Parameters.Add (PARA5);
SqlParameter Para6 = new SqlParameter ("@cphone", Cphone); Para6.
Direction = ParameterDirection.Input; Cmd.
Parameters.Add (PARA6);
SqlParameter para7 = new SqlParameter ("@amounts", amounts); Para7.
Direction = ParameterDirection.Input; Cmd.
Parameters.Add (PARA7);
SqlParameter para8 = new SqlParameter ("@cartnumber", Cartnumber); Para8.
Direction = ParameterDirection.Input; Cmd.
Parameters.Add (PARA8); SqlParameter Parareturn = new SqlParameter ("@return", SqlDbType.Int); Parareturn.direction = ParameterDirection.ReturnValue; Parameter direction is return parameter//cmd.
Parameters.Add (Parareturn); Cmd.
ExecuteNonQuery ();
Object obj = Parareturn; if (obj.

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

1. A stored procedure that returns only a single recordset

SqlConnection sqlconn = new SqlConnection (conn);
SqlCommand cmd = new SqlCommand ();
Set up SQL connection
cmd. Connection = sqlconn;
If the execution statement
cmd.commandtext = "Categoriestest1";
Specifies that the execution statement is a stored procedure
cmd.commandtype = CommandType.StoredProcedure;
SqlDataAdapter dp = new SqlDataAdapter (cmd);
DataSet ds = new DataSet ();
Populate the DataSet
DP. Fill (DS);
Below is the display effect
Gridview1.datasource = ds;
Gridview1.databind ();

Stored Procedures 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 ();
Cmd. Connection = sqlconn;
Cmd.commandtext = "Categoriestest2";
Cmd.commandtype = CommandType.StoredProcedure;
Sqlconn. Open ();
Executes and displays the number of rows affected
Label1.Text = cmd. ExecuteNonQuery (). ToString ();
Sqlconn. Close ();

Stored Procedures Categoriestest2

CREATE PROCEDURE Categoriestest2 as
 insert INTO dbo. Categories
 (categoryname,[description],[picture])
 values (' test1 ', ' test1 ', null)
 go

3. Stored procedures with return values

SqlConnection sqlconn = new SqlConnection (conn);
SqlCommand cmd = new SqlCommand ();
Cmd. Connection = sqlconn;
Cmd.commandtext = "Categoriestest3";
Cmd.commandtype = CommandType.StoredProcedure;
Create parameter
idataparameter[] Parameters = {
     new SqlParameter ("Rval", sqldbtype.int,4)
   };
Sets the parameter type to the return value type
parameters[0]. Direction = ParameterDirection.ReturnValue;
Add parameter
cmd. Parameters.Add (Parameters[0]);
Sqlconn. Open ();
Executes the stored procedure and returns the number of rows affected
Label1.Text = cmd. ExecuteNonQuery (). ToString ();
Sqlconn. Close ();
Shows the number of rows affected and the return value
Label1.Text + = "-" + parameters[0]. Value.tostring ();

Stored Procedures CATEGORIESTEST3

CREATE PROCEDURE Categoriestest3
 as
 insert INTO dbo. Categories
 (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 ();
Cmd. Connection = sqlconn;
Cmd.commandtext = "Categoriestest4";
Cmd.commandtype = CommandType.StoredProcedure;
Create parameter
idataparameter[] Parameters = {
     new SqlParameter ("@Id", sqldbtype.int,4),
     new SqlParameter ("@ CategoryName ", sqldbtype.nvarchar,15),
   };
Set the parameter type
parameters[0]. Direction = ParameterDirection.Output; Set to output parameter
parameters[1]. Value = "Testcategoryname";
Add parameter
cmd. Parameters.Add (Parameters[0]);
Cmd. Parameters.Add (Parameters[1]);
Sqlconn. Open ();
Executes the stored procedure and returns the number of rows affected
Label1.Text = cmd. ExecuteNonQuery (). ToString ();
Sqlconn. Close ();
Shows the number of rows affected and output parameters
Label1.Text + = "-" + parameters[0]. Value.tostring ();

Stored Procedures Categoriestest4

CREATE PROCEDURE Categoriestest4
 @id int output,
 @CategoryName nvarchar as insert into
 dbo. Categories
 (categoryname,[description],[picture])
 values (@CategoryName, ' test1 ', null)
set @id = @@ IDENTITY Go
 

5. Stored procedures with return values, input parameters, and output parameters

 SqlConnection sqlconn = new SqlConnection (conn);
SqlCommand cmd = new SqlCommand (); Cmd.
Connection = sqlconn;
Cmd.commandtext = "CATEGORIESTEST5";
Cmd.commandtype = CommandType.StoredProcedure; Create parameter idataparameter[] Parameters = {New SqlParameter ("@Id", sqldbtype.int,4), New SqlParameter ("@CategoryNa
Me ", sqldbtype.nvarchar,15), New SqlParameter (" Rval ", sqldbtype.int,4)}; Set the parameter type Parameters[0].    Direction = ParameterDirection.Output; Set to output parameter parameters[1].         Value = "Testcategoryname"; Assign value to input parameter parameters[2]. Direction = ParameterDirection.ReturnValue; Set to add parameter cmd for the return value//.
Parameters.Add (Parameters[0]); Cmd.
Parameters.Add (Parameters[1]); Cmd.
Parameters.Add (parameters[2]); Sqlconn.
Open (); Executes the stored procedure and returns the number of rows affected Label1.Text = cmd. ExecuteNonQuery ().
ToString (); Sqlconn.
Close (); Displays the number of rows affected, output parameters, and return values Label1.Text = "-" + parameters[0]. Value.tostring () + "-" + parameters[2].

Value.tostring (); 

Stored Procedures CATEGORIESTEST5

CREATE PROCEDURE Categoriestest5
 @id int output,
 @CategoryName nvarchar as insert into
 dbo. Categories
 (categoryname,[description],[picture])
 values (@CategoryName, ' test1 ', null)
set @id = @@ IDENTITY return
@ @rowcount
 Go

6. Return parameters and Recordset stored procedures at the same time

SqlConnection sqlconn = new SqlConnection (conn);
SqlCommand cmd = new SqlCommand (); Cmd.
Connection = sqlconn;
Cmd.commandtext = "Categoriestest6";
Cmd.commandtype = CommandType.StoredProcedure; Create parameter idataparameter[] Parameters = {New SqlParameter ("@Id", sqldbtype.int,4), New SqlParameter ("@CategoryNa
Me ", sqldbtype.nvarchar,15), New SqlParameter (" Rval ", sqldbtype.int,4)//return value}; Set the parameter type Parameters[0].    Direction = ParameterDirection.Output; Set to output parameter parameters[1].          Value = "Testcategoryname"; Assign value to input parameter parameters[2].  Direction = ParameterDirection.ReturnValue; Set to add parameter cmd for the return value//.
Parameters.Add (Parameters[0]); Cmd.
Parameters.Add (Parameters[1]); Cmd.
Parameters.Add (parameters[2]);
SqlDataAdapter dp = new SqlDataAdapter (cmd);
DataSet ds = new DataSet (); Populate the dataset DP.
Fill (DS); Display result set Gridview1.datasource = ds.
Tables[0];
Gridview1.databind ();
Label1.Text = ""; Display output parameters and return values Label1.Text + = Parameters[0]. Value.tostring () + "-" + parameters[2].

 Value.tostring ();

Stored Procedures Categoriestest6

CREATE PROCEDURE Categoriestest6
 @id int output,
 @CategoryName nvarchar as insert into
 dbo. Categories
 (categoryname,[description],[picture])
 values (@CategoryName, ' test1 ', null)
set @id = @@ IDENTITY
 SELECT * from Categories return
@ @rowcount
 Go

7. Return stored procedures for multiple recordsets

SqlConnection sqlconn = new SqlConnection (conn);
SqlCommand cmd = new SqlCommand ();
Cmd. Connection = sqlconn;
Cmd.commandtext = "Categoriestest7";
Cmd.commandtype = CommandType.StoredProcedure;
SqlDataAdapter dp = new SqlDataAdapter (cmd);
DataSet ds = new DataSet ();
Populate the DataSet
DP. Fill (DS);
Display result set 1
gridview1.datasource = ds. Tables[0];
Gridview1.databind ();
Display result set 2
Gridview2.datasource = ds. TABLES[1];
Gridview2.databind ();

Stored Procedures CATEGORIESTEST7

CREATE PROCEDURE Categoriestest7
 as
 select * FROM Categories
 select * to Categories
 go

Read more about C # Interested readers can view the site topics: "C # Common control usage Tutorial", "WinForm Control Usage Summary", "C # Data structure and algorithm tutorial", "C # object-oriented Program design Introductory Course" and "C # programming Thread Usage Skill Summary"

I hope this article will help you with C # programming.