. Net call stored procedure details

Configure app. config (Note: This document uses the northwind database as an example)

 
<?XML Version= "1.0" Encoding= "UTF-8"?>
 

 
<Configuration>
 

 
<Connectionstrings>
 

<Add Name= "Northwindconnectionstring" Connectionstring= "Data Source =.; initial catalog = northwind; Integrated Security = true"
 

 
Providername= "System. Data. sqlclient" />
 

 
</Connectionstrings>
 

 
</Configuration>
 

 Obtain the connection string

 
StringConstr = configurationmanager. connectionstrings ["Northwindconnectionstring"]. Connectionstring;
 

 

 

1. Return the stored procedure of a single record set

 
Sqlconnection conn =NewSqlconnection (constr );
 

 
Sqlcommand cmd =NewSqlcommand ();
 

 
Cmd. Connection = conn;
 

 
Cmd. commandtext ="Categoriestest1";
 

// Specify the execution statement as a stored procedure
 

 
Cmd. commandtype = commandtype. storedprocedure;
 

 
Sqldataadapter dp =NewSqldataadapter (CMD );
 

 
Dataset DS =NewDataset ();
 

 
DP. Fill (DS );
 

 
Gridview1.datasource = Ds;
 

 
Gridview1.databind ();
 

Stored Procedure categoriestest1

 
Create ProcedureCategoriestest1
 

 
As
 

 
Select*
 

 
FromCategories
 

 
Go
 

 

2. Stored Procedure without input/output

 
Sqlconnection conn =NewSqlconnection (constr );
 

 
Sqlcommand cmd =NewSqlcommand ();
 

 
Cmd. Connection = conn;
 

 
Cmd. commandtext ="Categoriestest2";
 

 
Cmd. commandtype = commandtype. storedprocedure;
 

 
Conn. open ();
 

 
Label1.text = cmd. executenonquery (). tostring ();
 

 
Conn. Close ();
 

 

 

 

Stored Procedure categoriestest2

 
Create ProcedureCategoriestest2As
 

 
InsertIntoDBO. Categories
 

 
(Categoryname, [description], [Picture])
 

Values('Test1','Test1',Null)
 

 
Go
 

 

 

 

 

 

 

 

3. Stored Procedures with returned values

 
Sqlconnection conn =NewSqlconnection (constr );
 

 
Sqlcommand cmd =NewSqlcommand ();
 

 
Cmd. Connection = conn;
 

Cmd. commandtext ="Categoriestest3";
 

 
Cmd. commandtype = commandtype. storedprocedure;
 

 
// Create parameters
 

 
Idataparameter [] parameters = {NewSqlparameter ("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]);
 

 
Conn. open ();
 

 
// Execute the stored procedure and return the number of affected rows
 

 
Label1.text = cmd. executenonquery (). tostring ();
 

 
Conn. Close ();
 

 
// Display the number of affected rows and returned values
 

 
Label1.text + ="-"+ Parameters [0]. value. tostring ();
 

Stored Procedure categoriestest3

 
Create ProcedureCategoriestest3
 

 
As
 

 
InsertIntoDBO. Categories
 

 
(Categoryname, [description], [Picture])
 

 
Values('Test1','Test1',Null)
 

 
Return @ Rowcount
 

 
Go
 

 

 

 

4. Stored Procedures with Input and Output Parameters

Sqlconnection conn =NewSqlconnection (constr );
 

 
Sqlcommand cmd =NewSqlcommand ();
 

 
Cmd. Connection = conn;
 

 
Cmd. commandtext ="Categoriestest4";
 

 
Cmd. commandtype = commandtype. storedprocedure;
 

 
// Create parameters
 

 
Idataparameter [] parameters =
 

 
{
 

NewSqlparameter ("@ ID", Sqldbtype. Int, 4 ),
 

 
NewSqlparameter ("@ Categoryname", Sqldbtype. nvarchar, 15 ),
 

 
};
 

 
// Set the parameter type
 

 
Parameters [0]. Direction = parameterdirection. output;// Set as output parameter
 

 
Parameters [1]. value ="Testcategoryname";
 

// Add Parameters
 

 
Cmd. Parameters. Add (parameters [0]);
 

 
Cmd. Parameters. Add (parameters [1]);
 

 
Conn. open ();
 

 
// Execute the stored procedure and return the number of affected rows
 

 
Label1.text = cmd. executenonquery (). tostring ();
 

 
Conn. Close ();
 

 
// Display the number of affected rows and Output Parameters
 

 
Label1.text + ="-"+ Parameters [0]. value. tostring ();
 

Stored Procedure categoriestest4

 
Create ProcedureCategoriestest4
 

 
@ IDInt Output,
 

 
@ Categoryname nvarchar (15)
 

 
As
 

 
InsertIntoDBO. Categories
 

 
(Categoryname, [description], [Picture])
 

 
Values(@ Categoryname,'Test1',Null)
 

Set@ ID =@ Identity
 

 
Go
 

 

 

 

 

 

 

 

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

 
Sqlconnection conn =NewSqlconnection (constr );
 

 
Sqlcommand cmd =NewSqlcommand ();
 

 
Cmd. Connection = conn;
 

 
Cmd. commandtext ="Categoriestest5";
 

Cmd. commandtype = commandtype. storedprocedure;
 

 
// Create parameters
 

 
Idataparameter [] parameters =
 

 
{
 

 
NewSqlparameter ("@ ID", Sqldbtype. Int, 4 ),
 

 
NewSqlparameter ("@ Categoryname", Sqldbtype. nvarchar, 15 ),
 

 
NewSqlparameter ("Rval", Sqldbtype. Int, 4)
 

 
};
 

 
// Set the parameter type
 

 
Parameters [0]. Direction = parameterdirection. output;// Set as output parameter
 

 
Parameters [1]. value ="Testcategoryname";// Assign values to input parameters
 

 
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]);
 

 
Conn. open ();
 

 
// Execute the stored procedure and return the number of affected rows
 

 
Label1.text = cmd. executenonquery (). tostring ();
 

 
Conn. 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

 
Create ProcedureCategoriestest5
 

 
@ IDInt Output,
 

 
@ Categoryname nvarchar (15)
 

 
As
 

 
InsertIntoDBO. Categories
 

 
(Categoryname, [description], [Picture])
 

 
Values(@ Categoryname,'Test1',Null)
 

Set@ ID =@ Identity
 

 
Return @ Rowcount
 

 
Go
 

 

 

6. Stored Procedures that return both parameters and Record Sets

 
Sqlconnection conn =NewSqlconnection (constr );
 

 
Sqlcommand cmd =NewSqlcommand ();
 

 
Cmd. Connection = conn;
 

 
Cmd. commandtext ="Categoriestest6";
 

Cmd. commandtype = commandtype. storedprocedure;
 

 
// Create parameters
 

 
Idataparameter [] parameters =
 

 
{
 

 
NewSqlparameter ("@ ID", Sqldbtype. Int, 4 ),
 

 
NewSqlparameter ("@ Categoryname", Sqldbtype. nvarchar, 15 ),
 

 
NewSqlparameter ("Rval", Sqldbtype. Int, 4)// Return Value
 

 
};
 

 
// Set the parameter type
 

 
Parameters [0]. Direction = parameterdirection. output;// Set as output parameter
 

 
Parameters [1]. value ="Testcategoryname";// Assign values to input parameters
 

 
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 =NewSqldataadapter (CMD );
 

 
Dataset DS =NewDataset ();
 

 
DP. Fill (DS );
 

 
Gridview1.datasource = Ds. Tables [0];
 

 
Gridview1.databind ();
 

Label1.text =String. Empty;
 

 
// Display output parameters and return values
 

 
Label1.text + = parameters [0]. value. tostring () +"-"+ Parameters [2]. value. tostring ();
 

Stored Procedure categoriestest6

 
Create ProcedureCategoriestest6
 

 
@ IDInt Output,
 

 
@ Categoryname nvarchar (15)
 

 
As
 

InsertIntoDBO. Categories
 

 
(Categoryname, [description], [Picture])
 

 
Values(@ Categoryname,'Test1',Null)
 

 
Set@ ID =@ Identity
 

 
Select*FromCategories
 

 
Return @ Rowcount
 

 
Go
 

 

 

 

 

 

 

 

7. Return the stored procedure of multiple Record Sets

 
Sqlconnection conn =NewSqlconnection (constr );
 

 
Sqlcommand cmd =NewSqlcommand ();
 

 
Cmd. Connection = conn;
 

 
Cmd. commandtext ="Categoriestest7";
 

 
Cmd. commandtype = commandtype. storedprocedure;
 

Sqldataadapter dp =NewSqldataadapter (CMD );
 

 
Dataset DS =NewDataset ();
 

 
DP. Fill (DS );
 

 
Gridview1.datasource = Ds. Tables [0];
 

 
Gridview1.databind ();
 

 
Gridview2.datasource = Ds. Tables [1];
 

 
Gridview2.databind ();
 

Stored Procedure categoriestest7

 
Create ProcedureCategoriestest7
 

As
 

 
Select*FromCategories
 

 
Select*FromCategories
 

 
Go