在做三层架构的时候，特别是对表做查询的时候，有时候并不确定查询条件的个数，比如查询学生表：有可能只输入学号，或者姓名，或者性别，总之查询条件的参数个数并不确定，下面是我用List实现传值的代码：
附图如下:
在这里附上数据库的表结构：
CREATE TABLE Student(
StuId VARCHAR(6) PRIMARY KEY,
StuName VARCHAR(10) NOT NULL,
MajorId INT NOT NULL,
Sex VARCHAR(2) NOT NULL DEFAULT '男',
Birthdate SMALLDATETIME NOT NULL,
Credit FLOAT,
Remark VARCHAR(200)
)
------------------------------------------------------------------------------------------------
程序代码如下：
Model层----------------------------------------------包含两个类------------------------
------------------表的实体类Student-----------------
public class Student
{
private string stuId;
public string StuId
{
get { return stuId; }
set { stuId = value; }
}
private string stuName;
public string StuName
{
get { return stuName; }
set { stuName = value; }
}
private int majorId;
public int MajorId
{
get { return majorId; }
set { majorId = value; }
}
private string sex;
public string Sex
{
get { return sex; }
set { sex = value; }
}
private DateTime birthdate;
public DateTime Birthdate
{
get { return birthdate; }
set { birthdate = value; }
}
private float credit;
public float Credit
{
get { return credit; }
set { credit = value; }
}
private string remark;
public string Remark
{
get { return remark; }
set { remark = value; }
}
}
------------------Condition主要用于传递参数，这个类也可以定义在别的地方-------------------
public class Condition
{
public string paramName { get; set; }
public string paramValue { get; set; }
public ConditionOperate Operation { get; set; }
// 查询所用到的运算操作符
public enum ConditionOperate : byte
{
Equal, // 等于
NotEqual, // 不等于
Like, // 模糊查询
Lessthan, // 小于等于
GreaterThan // 大于
}
}
---------------------DAL层-----------------------------------------------------------------
------------------DBHelper类---------------------------------------------
public class DBHelper
{
private SqlConnection conn;
private SqlCommand cmd;
private SqlDataAdapter sda;
private DataSet ds;
public DBHelper()
{
conn = new SqlConnection(ConfigurationManager.ConnectionStrings["key"].ConnectionString);
}
// 不带参数的查询
public DataSet GetResult(string sql, CommandType type)
{
cmd = new SqlCommand(sql, conn);
sda = new SqlDataAdapter(cmd);
conn.Close();
ds = new DataSet();
sda.Fill(ds, "student");
return ds;
}
// 带参数的查询
public DataSet GetResult(string sql, CommandType type, params SqlParameter[] paras)
{
cmd = new SqlCommand(sql, conn);
if (type == CommandType.StoredProcedure)
{
cmd.CommandType = CommandType.StoredProcedure;
}
cmd.Parameters.AddRange(paras);
sda = new SqlDataAdapter(cmd);
conn.Close();
ds = new DataSet();
sda.Fill(ds, "student");
return ds;
}
}
-----------------------------对Student表操作类
public class StudenDAL
{
public DataSet GetStudent(List<Condition> condition)
{
DataSet ds = new DataSet();
DBHelper db = new DBHelper();
string sql = "select * from student";
// 如果带查询语句带参数
if (condition.Count > 0)
{
sql += SqlString(condition);
ds = db.GetResult(sql, CommandType.Text, SqlParas(condition));
}
else
{
ds = db.GetResult(sql, CommandType.Text);
}
return ds;
}
----------------------以下两个可以写成一个类，以便如果有多张表时，可以实现代码的复用----------------------------------
// 获取查询参数
public SqlParameter[] SqlParas(List<Condition> cond)
{
List<SqlParameter> paras = new List<SqlParameter>();
for (int i = 0; i < cond.Count; i++)
{
SqlParameter para = new SqlParameter("@" + cond[i].paramName, cond[i].paramValue);
if (cond[i].Operation == Condition.ConditionOperate.Like)
{
para.SqlValue = "%" + cond[i].paramValue + "%";
}
paras.Add(para);
}
return paras.ToArray();
}
// 获取SQL查询语句的where子句
public string SqlString(List<Condition> cond)
{
string sqlWhere = string.Empty;
List<string> where = new List<string>();
// 数组元素的顺序应该与ConditionOperate枚举值顺序相同
string[] operateType = { " = ", " <> ", " Like ", " <= ", " >= " };
for (int i = 0; i < cond.Count; i++)
{
int index = (int)cond[i].Operation;
where.Add(string.Format("{0}" + operateType[index] + "{1}", cond[i].paramName, "@" + cond[i].paramName));
}
sqlWhere = " where " + string.Join(" and ", where.ToArray());
return sqlWhere;
}
}
------------------------------BLL层---------------------------
public class StudentBLL
{
private readonly StudenDAL stuDal = new StudenDAL();
public DataSet GetStudent(List<Condition> condition)
{
return stuDal.GetStudent(condition);
}
}
------------------------------UI层，查询按钮的单击事件-------------------------------------
protected void btnSearch_Click(object sender, EventArgs e)
{
Condition condition;
StudentBLL stu = new StudentBLL();
List<Condition> list = new List<Condition>();
if (txtId.Text!="")
{
condition = new Condition()
{
paramName = "stuId",
paramValue = txtId.Text,
Operation = Condition.ConditionOperate.Equal
};
list.Add(condition);
}
if (txtName.Text!="")
{
condition = new Condition()
{
paramName = "stuName",
paramValue = txtName.Text,
Operation = Condition.ConditionOperate.Equal
};
list.Add(condition);
}
if (txtSex.Text != "")
{
condition = new Condition()
{
paramName = "Sex",
paramValue = txtSex.Text,
Operation = Condition.ConditionOperate.Equal
};
list.Add(condition);
}
GridView1.DataSource = stu.GetStudent(list);
GridView1.DataBind();
}