Methods for calling stored procedures in asp. net

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.