拼接SQL查询条件传入SP存储过程
一、Form界面查询按钮事件
C# 全选
private void button1_Click(object sender, EventArgs e)
{
//查询条件
var input = new req_Query
{
DateFrom = DateTime.Today.AddDays(-30),
DateTo = DateTime.Today,
Name = "CSFramework"
};
var data = new DAL().QueryList(input);
dataGridView1.DataSource = data;
}
二、DAL层拼接查询条件
C# 全选
/// <summary>
/// DAL层查询方法
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public List<tb_Order> QueryList(req_Query input)
{
//拼接SQL
var where = new StringBuilder(" AND 1=1 ");
if (input.DateFrom > DateTime.Parse("1900-01-01"))
where.Append(" AND OrderDate>='" + input.DateFrom.ToString("yyyy/MM/dd"));
if (input.DateTo > DateTime.Parse("1900-01-01"))
where.Append(" AND OrderDate<='" + input.DateTo.ToString("yyyy/MM/dd"));
if (!String.IsNullOrWhiteSpace(input.Name))
{
input.Name = input.Name.Replace("'", "");//字符类型的参数，必须替换单引号，防止QL注入
where.Append(" AND CustomerName LIKE '%" + input.Name + "%'");
}
//创建存储过程参数
var p = _Database.CreateParameter("While", where.ToString());
//调用存储过程
var list = _Database.ExecuteSql<tb_Order>("QueryAndonDepartNeed",
System.Data.CommandType.StoredProcedure, p);
return list;
}
注意：这里使用 CSFramework.EF组件，执行存储过程并返回List<T>对象列表。
若返回DataTable，使用下面代码：
C# 全选
public DataTable QueryListByTable(req_Query input)
{
//拼接SQL
var where = new StringBuilder(" AND 1=1 ");
if (input.DateFrom > DateTime.Parse("1900-01-01"))
where.Append(" AND OrderDate>='" + input.DateFrom.ToString("yyyy/MM/dd"));
if (input.DateTo > DateTime.Parse("1900-01-01"))
where.Append(" AND OrderDate<='" + input.DateTo.ToString("yyyy/MM/dd"));
if (!String.IsNullOrWhiteSpace(input.Name))
{
input.Name = input.Name.Replace("'", "");//字符类型的参数，必须替换单引号，防止QL注入
where.Append(" AND CustomerName LIKE '%" + input.Name + "%'");
}
//创建存储过程参数
var p = _Database.CreateParameter("While", where.ToString());
//调用存储过程
var list = _Database.GetDataTable("QueryAndonDepartNeed",
System.Data.CommandType.StoredProcedure, p);
return list;
}
版权声明：本文为开发框架文库发布内容,转载请附上原文出处连接
NewDoc C/S框架网
