WPF C# MVVM项目Access SQL查询WHERE参数如何传入全选值

方案1：修改参数化SQL适配空值 #

你之前的写法逻辑是可行的，出现数据类型混乱大概率是Access按位置匹配参数的特性没有适配，同时参数传值时没有正确处理空值。

修正后SQL语句 #

SELECT IDBug, [Date], ReportedBy, Replicated, ReproducableSteps, AppArea, Status, FixedVer, FixedBy, Notes, Title
FROM Bugs
WHERE 
    ([Date] >= ? OR ? IS NULL)
    AND ([Date] <= ? OR ? IS NULL)
    AND (Status = ? OR ? IS NULL)
    AND (AppArea = ? OR ? IS NULL)
    AND (Replicated = ? OR ? IS NULL)
    AND (ReportedBy = ? OR ? IS NULL)

注意：Access的参数是按传入顺序匹配的，所以每个筛选条件的两个?需要传入完全相同的值，比如第一个和第二个?都传起始日期，第三个和第四个都传结束日期，以此类推。

C#端参数传值处理 #

你之前的代码中FirstOrDefault会给未匹配到的ID返回默认值0，会错误筛选ID为0的记录，需要改为未选择筛选条件时传入DBNull.Value：

// 处理各参数，未选择时赋值为DBNull.Value
object startParam = StartDate.HasValue ? StartDate : DBNull.Value;
object stopParam = StopDate.HasValue ? StopDate : DBNull.Value;
object statusParam = string.IsNullOrEmpty(State) ? DBNull.Value : State;

int idAr = 0;
object areaParam = DBNull.Value;
if (!string.IsNullOrEmpty(AreaSe?.ToString()))
{
    idAr = (from DataRow dr in ars.GetData().Rows
            where (string)dr["Area"] == AreaSe.ToString()
            select (int)dr["IDArea"]).FirstOrDefault();
    areaParam = idAr;
}

int idCust = 0;
object custParam = DBNull.Value;
if (!string.IsNullOrEmpty(CustomS?.ToString()))
{
    idCust = (from DataRow dr in custs.GetData().Rows
              where (string)dr["CustomerName"] == CustomS.ToString()
              select (int)dr["IDCustomer"]).FirstOrDefault();
    custParam = idCust;
}

object repParam = replicationS == null ? DBNull.Value : replicationS;

// 按SQL参数顺序传值，每个参数传两次
BugTable = bugs.GetT(startParam, startParam, stopParam, stopParam, statusParam, statusParam, areaParam, areaParam, repParam, repParam, custParam, custParam);

注意事项 #

如果用的是强类型DataSet的TableAdapter，需要先在DataSet设计器中，把对应查询的所有参数的AllowDbNull属性设置为True，否则传入空值时会触发类型错误。

方案2：动态拼接筛选条件（更高效） #

如果不想传双倍参数，可以在C#端根据用户输入动态拼接WHERE子句，查询效率更高，也不会有类型匹配问题：

List<string> conditions = new List<string>();
List<object> parameters = new List<object>();

// 只有用户填写了对应筛选条件时才加入查询
if (StartDate.HasValue)
{
    conditions.Add("[Date] >= ?");
    parameters.Add(StartDate);
}
if (StopDate.HasValue)
{
    conditions.Add("[Date] <= ?");
    parameters.Add(StopDate);
}
if (!string.IsNullOrEmpty(State))
{
    conditions.Add("Status = ?");
    parameters.Add(State);
}
if (!string.IsNullOrEmpty(AreaSe?.ToString()))
{
    int idAr = (from DataRow dr in ars.GetData().Rows
                where (string)dr["Area"] == AreaSe.ToString()
                select (int)dr["IDArea"]).FirstOrDefault();
    conditions.Add("AppArea = ?");
    parameters.Add(idAr);
}
if (replicationS != null)
{
    conditions.Add("Replicated = ?");
    parameters.Add(replicationS);
}
if (!string.IsNullOrEmpty(CustomS?.ToString()))
{
    int idCust = (from DataRow dr in custs.GetData().Rows
                  where (string)dr["CustomerName"] == CustomS.ToString()
                  select (int)dr["IDCustomer"]).FirstOrDefault();
    conditions.Add("ReportedBy = ?");
    parameters.Add(idCust);
}

// 拼接最终SQL
string baseSql = "SELECT IDBug, [Date], ReportedBy, Replicated, ReproducableSteps, AppArea, Status, FixedVer, FixedBy, Notes, Title FROM Bugs";
if (conditions.Any())
{
    baseSql += " WHERE " + string.Join(" AND ", conditions);
}
// 执行SQL传入parameters列表即可

内容的提问来源于stack exchange，提问作者kozemir12