How to call stored procedures in ASP. NET

ASP. NET and SQL Server are the best, slightly largerProgramGenerally, the first consideration is SQL Server, but access is used only when economic considerations exist. With SQL Server, stored procedures are generally used to improve database efficiency. Because stored procedures are fast to run and advanced query functions can be implemented. For example, some data parameters are input, but the SQL Execution process may be different.

The following is an example. To create a new role, the role name must be unique. The following is a stored procedure.

 
Create procedure sp_accountrole_create
@ Categoryid int,
@ Rolename nvarchar (10 ),
@ Description nvarchar (50 ),
@ Roleid int output
As
Declare @ count int
-- Query for records with the same name
Select @ COUNT = count (roleid) from account_role where
Rolename = @ rolename
If @ COUNT = 0
Insert into account_role
(Categoryid, rolename, description) Values
(@ Categoryid, @ rolename, @ description)
Set @ roleid = @ identity
Return 1
Go
 

C # process for executing the stored procedure:

Sqlconnection dbconnection = new sqlconnection (mconnectionstring );
Sqlcommand command = new sqlcommand ("sp_accountrole_create", dbconnection );
Dbconnection. Open (connectstring );
// Deprecated the sqlcommand attribute as the Stored Procedure
Command. commandtype = commandtype. storedprocedure;
Command. Parameters. Add ("@ categoryid", sqldbtype. Int, 4 );
Command. Parameters. Add ("@ rolename", sqldbtype. nvarchar, 10 );
Command. Parameters. Add ("@ description", sqldbtype. nvarchar, 50 );
Command. Parameters. Add ("@ roleid", sqldbtype. Int, 4 );
// Return Value
Command. Parameters. Add ("returnvalue ",
Sqldbtype. Int,
4, // size
Parameterdirection. returnvalue,
False, // is nullable
0, // byte precision
0, // byte Scale
String. empty,
Datarowversion. Default,
Null );
 
 
Command. Parameters ["@ categoryid"]. value = permission. categoryid;
Command. Parameters ["@ rolename"]. value = permission. permissionname;
Command. Parameters ["@ description"]. value = permission. description;
// Returns a new ID value.
Command. Parameters ["@ roleid"]. Direction = parameterdirection. output;
 
Int rowsaffected = command. executenonquery ();
Int result = command. Parameters ["returnvalue"]. value;
Int newid = command. Parameters ["@ roleid"]. value;
 

The function is quite powerful. We can get three values: Row impact value, stored procedure return value, and new id value.