Connection string
The
code is as follows:
String conn = configurationmanager.connectionstrings["NorthwindConnectionString"]. ConnectionString;
Confige file
The
code is as follows:
<connectionStrings>
<add name= "NorthwindConnectionString" connectionstring= "Data source=.;i Nitial catalog=northwind;integrated security=true "providername= System.Data.SqlClient"/>
</connectionStrings>
1. A stored procedure that returns only a single recordset
The
code is as follows:
SqlConnection sqlconn = new SqlConnection (conn);
SqlCommand cmd = new SqlCommand ();
//Set up SQL connection
cmd. Connection = sqlconn;
//If 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 ();
Fill DataSet
Dp. Fill (DS);
The following are the display effects
Gridview1.datasource = ds;
Gridview1.databind ();
Stored Procedures Categoriestest1
The
code is as follows:
CREATE PROCEDURE Categoriestest1
as
SELECT *
from Categories
Go
2. Stored procedures without input and output
C # code section
The
code is as follows:
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 Procedures Categoriestest2
Copy Code code as follows:
CREATE PROCEDURE Categoriestest2 as
INSERT INTO dbo. Categories
(Categoryname,[description],[picture])
values (' test1 ', ' test1 ', null)
Go
3. Stored procedures with return values
C # code section
The
code is as follows:
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)
};
//sets the parameter type to the return value type
Parameters[0]. Direction = ParameterDirection.ReturnValue;
//Add Parameters
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 ();
Show the number of rows and return values affected
Label1.Text = "-" + parameters[0]. Value.tostring ();
Stored Procedures CATEGORIESTEST3
The
code is as follows:
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
C # code section
The
code is as follows:
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 parameter type
Parameters[0]. Direction = ParameterDirection.Output; Set to output parameter
Parameters[1]. Value = "Testcategoryname";
//Add Parameters
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
The
code is as follows:
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
5. Stored procedures with return values, input parameters, and output parameters
C # code section
The
code is as follows:
SqlConnection sqlconn = new SqlConnection (conn);
SqlCommand cmd = new SqlCommand ();
cmd. Connection = sqlconn;
cmd.commandtext = "CATEGORIESTEST5";
cmd.commandtype = CommandType.StoredProcedure;
//Create Parameters
idataparameter[] Parameters = {
New SqlParameter ("@Id", sqldbtype.int,4),
New SqlParameter ("@CategoryName", sqldbtype.nvarchar,15),
New SqlParameter ("Rval", sqldbtype.int,4)
};
//Set parameter type
Parameters[0]. Direction = ParameterDirection.Output; Set to output parameter
Parameters[1]. Value = "Testcategoryname"; Assign a value to an input parameter
parameters[2]. Direction = ParameterDirection.ReturnValue; Set to return value
//Add Parameters
cmd. 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 ();
Shows the number of rows affected, output parameters, and return values
Label1.Text = "-" + parameters[0]. Value.tostring () + "-" + parameters[2]. Value.tostring ();
Stored Procedures CATEGORIESTEST5
The
code is as follows:
CREATE PROCEDURE Categoriestest5
@id int output,
@CategoryName nvarchar (15)
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
C # code section
The
code is as follows:
SqlConnection sqlconn = new SqlConnection (conn);
SqlCommand cmd = new SqlCommand ();
Cmd. Connection = sqlconn;
cmd.commandtext = "Categoriestest6";
cmd.commandtype = CommandType.StoredProcedure;
//Create Parameters
idataparameter[] Parameters = {
New SqlParameter ("@Id", sqldbtype.int,4),
New SqlParameter ("@CategoryName", sqldbtype.nvarchar,15),
New SqlParameter ("Rval", sqldbtype.int,4)//return value
};
//Set parameter type
Parameters[0]. Direction = ParameterDirection.Output; Set to output parameter
Parameters[1]. Value = "Testcategoryname"; Assign a value to an input parameter
parameters[2]. Direction = ParameterDirection.ReturnValue; Set to return value
//Add Parameters
cmd. Parameters.Add (Parameters[0]);
cmd. Parameters.Add (Parameters[1]);
cmd. Parameters.Add (parameters[2]);
SqlDataAdapter dp = new SqlDataAdapter (cmd);
DataSet ds = new DataSet ();
Fill DataSet
Dp. Fill (DS);
Show 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
The
code is as follows:
CREATE PROCEDURE Categoriestest6
@id int output,
@CategoryName nvarchar (15)
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
C # code section
The
code is as follows:
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 ();
Fill 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
The
code is as follows:
CREATE PROCEDURE Categoriestest7
as
SELECT * from Categories
SELECT * from Categories
Go
