在做三層架構(gòu)的時候，特別是對表做查詢的時候，有時候并不確定查詢條件的個數(shù)，比如查詢學生表：有可能只輸入學號，或者姓名，或者性別，總之查詢條件的參數(shù)個數(shù)并不確定，下面是我用List實現(xiàn)傳值的代碼：

附圖如下:

在這里附上數(shù)據(jù)庫的表結(jié)構(gòu)：

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主要用于傳遞參數(shù)，這個類也可以定義在別的地方-------------------

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);

??????? }

??????? // 不帶參數(shù)的查詢

??????? 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;

??????? }

?????? // 帶參數(shù)的查詢

??????? 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";

??????????? // 如果帶查詢語句帶參數(shù)

??????????? if (condition.Count > 0)

??????????? {

??????????????? sql += SqlString(condition);

??????????????? ds = db.GetResult(sql, CommandType.Text, SqlParas(condition));

??????????? }

??????????? else

??????????? {

??????????????? ds = db.GetResult(sql, CommandType.Text);

??????????? }

??????????? return ds;

??????? }

----------------------以下兩個可以寫成一個類，以便如果有多張表時，可以實現(xiàn)代碼的復用----------------------------------

??????? // 獲取查詢參數(shù)

??????? 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>();

??????????? // 數(shù)組元素的順序應(yīng)該與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();

??? }

轉(zhuǎn)載于:https://www.cnblogs.com/ianunspace/p/3448524.html

總結(jié)

以上是生活随笔為你收集整理的ASP.NET三层架构之不确定查询参数个数的查询的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。