How to call stored procedures in ASP.NET

ASP.NET and SQL SERVER are the best. For larger programs, SQL SERVER is usually the first choice. Only those who are very economical use ACCESS. When using SQL SERVER, in order to make the database more efficient, stored procedures are generally used because stored procedures execute quickly and can implement some advanced query and other functions. For example, some data parameters are passed in, but the SQL procedures executed may be different.

Here's an example to create a new role and require that the name of the role cannot be repeated. 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
-- Find if there are 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

SqlConnection DbConnection = new SqlConnection(mConnectionString);
 SqlCommand command = new SqlCommand( "sp_AccountRole_Create", DbConnection );
 DbConnection.Open(connectString);
 // Set the SqlCommand attribute to a 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;
 // New ID value can be returned
 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. You can get three values, namely the row impact value, the stored procedure return value, and the new ID value.