Methods for calling stored procedures in asp. net
- 2021-07-22 09:31:05
- OfStack
This article illustrates the method of calling stored procedures in asp. net. Share it for your reference, as follows:
1. Create and call a stored procedure with no parameters as follows:
CREATE PROCEDURE All students <dbo.selectUsers>
AS SELECT * FROM Students
GO
EXEC All students
Create and call a stored procedure with parameters as follows:
CREATE PROCEDURE Student enquiries 1
@SNAME VARCHAR(8),@SDEPT VARCHAR(20)
AS SELECT * FROM Students WHERE Name =@SNAME AND Department =@SDEPT
GO
EXEC Student enquiries 1 ' Zhang 3',' Computer Department '
Or:
EXEC Student enquiries 1 @SNAME=' Zhang 3',@SDEPT=' Computer Department '
(2) Delete stored procedures:
DROP PROCEDURE< Stored procedure name group >
2. Call the access procedure in asp. net:
DBHelper.cs
// Without parameters
public static DataTable GetList(string sqlDBO)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sqlDBO, Connection);
cmd.CommandType = CommandType.StoredProcedure; // Specify the command type as stored procedure
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
// With parameters
public static DataTable GetList(string sqlDBO,params SqlParameter[] values)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sqlDBO, Connection);
cmd.CommandType = CommandType.StoredProcedure; // Specify the command type as stored procedure
cmd.Parameters.AddRange(values);
//cmd.Parameters.AddWithValue("@ Parameter 1", Value 1);
//cmd.Parameters.AddWithValue("@ Parameter 2", Value 2);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
UsersService.cs
// Without parameters
public static IList<Users> GetUserList()
{
List<Users> list = new List<Users>();
DataTable table = DBHelper.GetList(" Stored procedure name ");
foreach (DataRow row in table.Rows)
{
Users users = new Users();
users.Id=(int)row["id"];
users.UserName=(string)row["userName"];
users.Password=(string)row["password"];
list.Add(users);
}
return list;
}
// With parameters
public static IList<Users> GetUserList(string userName,string password)
{
List<Users> list = new List<Users>();
SqlParameter[] para=new SqlParameter[]
{
new SqlParameter("@userName",userName),
new SqlParameter("@password",password)
};
DataTable table = DBHelper.GetList(" Stored procedure name ",para);
foreach (DataRow row in table.Rows)
{
Users users = new Users();
users.Id=(int)row["id"];
users.UserName=(string)row["userName"];
users.Password=(string)row["password"];
list.Add(users);
}
return list;
}
For more readers interested in asp. net, please check out the topics on this site: "asp. net String Operation Skills Summary", "asp. net Operation Skills Summary", "asp. net File Operation Skills Summary", "asp. net ajax Skills Summary" and "asp. net Cache Operation Skills Summary".
I hope this article is helpful to everyone's asp. net programming.