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.