. Net call stored procedure details

Connection string

 Copy codeThe Code is as follows:
String conn = ConfigurationManager. ConnectionStrings ["NorthwindConnectionString"]. ConnectionString;
 

Confige File
 Copy codeThe Code is as follows:
<ConnectionStrings>
<Add name = "NorthwindConnectionString" connectionString = "Data Source = ..; Initial Catalog = Northwind; Integrated Security = True" providerName = "System. Data. SqlClient"/>
</ConnectionStrings>
 
1. Only the stored procedure of a single record set is returned.
 Copy codeThe Code is as follows:
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
 Copy codeThe Code is as follows:
Create procedure Categoriestest1
AS
Select *
From Categories
GO
 

2. Stored Procedure without input/output
C # code
Copy codeThe 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 Procedure Categoriestest2
Copy codeThe Code is as follows:
Create procedure Categoriestest2
Insert into dbo. Categories
(CategoryName, [Description], [Picture])
Values ('test1', 'test1', null)
GO

3. Stored Procedures with returned values
C # code
Copy codeThe 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)
};
// 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
Copy codeThe 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 and Output Parameters
C # code
Copy codeThe 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 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
Copy codeThe 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
Copy codeThe 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 the parameter type
Parameters [0]. Direction = ParameterDirection. Output; // set it to an Output parameter.
Parameters [1]. Value = "testCategoryName"; // assign a Value to the 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 ();
// Execute the stored procedure and return the number of affected rows
Label1.Text = cmd. ExecuteNonQuery (). ToString ();
Sqlconn. Close ();
// Displays the number of affected rows, output parameters, and returned values.
Label1.Text + = "-" + parameters [0]. Value. ToString () + "-" + parameters [2]. Value. ToString ();

Stored Procedure Categoriestest5
Copy codeThe 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. Stored Procedures that return both parameters and Record Sets
C # code
Copy codeThe 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 the parameter type
Parameters [0]. Direction = ParameterDirection. Output; // set it to an Output parameter.
Parameters [1]. Value = "testCategoryName"; // assign a Value to the 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 in dataset
Dp. Fill (ds );
// Display the 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 Procedure Categoriestest6
Copy codeThe 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 the stored procedure of multiple Record Sets
C # code
 Copy codeThe 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 in 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 Procedure Categoriestest7
 Copy codeThe Code is as follows:
Create procedure Categoriestest7
AS
Select * from Categories
Select * from Categories
GO