1. Only the stored procedure of a single record set is returned.
Sqlconnection sqlconn = new sqlconnection (conn );
Sqlcommand cmd = new sqlcommand ();
// Set the SQL connection
Cmd. Connection = sqlconn;
// If the statement is executed
Cmd. commandtext = "categoriestest1 ";
// Specify the execution statement as a stored procedure
Cmd. commandtype = commandtype. storedprocedure;
Sqldataadapter dp = new sqldataadapter (CMD );
Dataset DS = new dataset ();
// Fill in Dataset
DP. Fill (DS );
// The following is the Display Effect
Gridview1.datasource = Ds;
Gridview1.databind (); Stored Procedure categoriestest1
Create procedure categoriestest1
As
Select *
From categories
Go 2. Stored Procedure without input/output
Sqlconnection sqlconn = new sqlconnection (conn );
Sqlcommand cmd = new sqlcommand (); Cmd. Connection = sqlconn;
Cmd. commandtext = "categoriestest2 ";
Cmd. commandtype = commandtype. storedprocedure;
Sqlconn. open ();
// Execute and display the number of affected rows
Label1.text = cmd. executenonquery (). tostring ();
Sqlconn. Close ();
Stored Procedure categoriestest2
Create procedure categoriestest2
Insert into DBO. Categories
(Categoryname, [description], [Picture])
Values ('test1', 'test1', null)
Go 3. Stored Procedures with returned values
Sqlconnection sqlconn = new sqlconnection (conn );
Sqlcommand cmd = new sqlcommand ();
Cmd. Connection = sqlconn;
Cmd. commandtext = "categoriestest3 ";
Cmd. commandtype = commandtype. storedprocedure;
// Create parameters
Idataparameter [] parameters = {
New sqlparameter ("rval", sqldbtype. Int, 4)
};
// Set the parameter type to the return value type
Parameters [0]. Direction = parameterdirection. returnvalue;
// Add Parameters
Cmd. Parameters. Add (parameters [0]); Sqlconn. open ();
// Execute the stored procedure and return the number of affected rows
Label1.text = cmd. executenonquery (). tostring ();
Sqlconn. Close ();
// Display the number of affected rows and returned values
Label1.text + = "-" + parameters [0]. value. tostring ();
Stored Procedure categoriestest3
Create procedure categoriestest3
As
Insert into DBO. Categories
(Categoryname, [description], [Picture])
Values ('test1', 'test1', null)
Return @ rowcount
Go 4. Stored Procedures with Input and Output Parameters
Sqlconnection sqlconn = new sqlconnection (conn );
Sqlcommand cmd = new sqlcommand ();
Cmd. Connection = sqlconn;
Cmd. commandtext = "categoriestest4 ";
Cmd. commandtype = commandtype. storedprocedure;
// Create parameters
Idataparameter [] parameters = {
New sqlparameter ("@ ID", sqldbtype. Int, 4 ),
New sqlparameter ("@ categoryname", sqldbtype. nvarchar, 15 ),
};
// Set the parameter type
Parameters [0]. Direction = parameterdirection. output; // set it to an output parameter.
Parameters [1]. value = "testcategoryname ";
// Add Parameters
Cmd. Parameters. Add (parameters [0]);
Cmd. Parameters. Add (parameters [1]); Sqlconn. open ();
// Execute the stored procedure and return the number of affected rows
Label1.text = cmd. executenonquery (). tostring ();
Sqlconn. Close ();
// Display the number of affected rows and Output Parameters
Label1.text + = "-" + parameters [0]. value. tostring ();
Stored Procedure categoriestest4
Create procedure categoriestest4
@ ID int output,
@ Categoryname nvarchar (15)
As
Insert into DBO. Categories
(Categoryname, [description], [Picture])
Values (@ categoryname, 'test1', null)
Set @ ID = @ identity
Go |
