C#连接MS Access数据库时SqlCommand无返回结果问题排查

场景重现 #

我用C# WebForms连接MS Access数据库，编写了以下代码：

参数定义代码 #

var parameters = new KeyValuePair<string, object>[]
{
    new KeyValuePair<string, object>("@ProjectId", currentInfo.ProjectId),
    new KeyValuePair<string, object>("@core", Convert.ToInt16(catalogue.Core.ToString())),
    new KeyValuePair<string, object>("@sizee", catalogue.Size), // 注：原代码此处多了一个多余的右括号，需修正
    new KeyValuePair<string, object>("@applicationId", Convert.ToInt32(applicationComboBox.SelectedValue.ToString()))
};
DataTable typesTable = helper.GetRowsByQuery("FillType", parameters, currentInfo.DBPath);

数据库操作方法实现 #

public DataTable GetRowsByQuery(string queryOrTable, KeyValuePair<string, object>[] parameters, string dbPath)
{
    DataTable table = new DataTable();

    OleDbDataAdapter adapter = new OleDbDataAdapter();
    OleDbConnection connection = new OleDbConnection(GetStringConnection(dbPath));

    string sqlCommand = GetSqlCommand(queryOrTable);

    OleDbCommand command = new OleDbCommand(sqlCommand, connection);

    if (parameters != null)
    {
        foreach (KeyValuePair<string, object> o in parameters)
        {
            var parameter = new OleDbParameter(o.Key, o.Value);
            SetOleDbTypeAndSize(parameter, o.Value);
            command.Parameters.Add(parameter);                
        }
    }

    command.CommandText = sqlCommand;

    adapter.SelectCommand = command;

    try
    {
        connection.Open();

        if (table == null)
        {
            table = new DataTable();
            table.Locale = System.Globalization.CultureInfo.InvariantCulture;
            adapter.FillSchema(table, SchemaType.Source);
        }

        adapter.Fill(table);
    }
    finally
    {
        if (connection.State == ConnectionState.Open)
        {
            connection.Close();
        }
    }

    return table;
}

执行的SQL查询语句 #

SELECT DISTINCT Catalogue.Type  
FROM (CableProperty  
INNER JOIN Catalogue ON CableProperty.CatalogueId = Catalogue.Id)    
WHERE (Catalogue.Core = @core) 
  AND (Catalogue.[Size] = '@sizee') 
  AND (CableProperty.ProjectId = @projectId) 
  AND (CableProperty.CableApplicationId = @applicationId) 

问题现象 #

• 该查询在MS Access中直接执行能返回结果，但在C#应用中无任何结果
• 移除(Catalogue.Core = @core) AND (Catalogue.[Size] = '@sizee')条件后，应用中能正常返回结果

解决方案 #

问题出在SQL语句里的@sizee参数被错误地加了单引号：

• 参数化查询中，参数不需要用单引号包裹，OleDb会自动处理参数的类型匹配
• 加了单引号后，数据库会把'@sizee'当成字面字符串去匹配Catalogue.[Size]字段，而不是使用你传入的参数值，自然查不到结果

修正后的SQL语句：

SELECT DISTINCT Catalogue.Type  
FROM (CableProperty  
INNER JOIN Catalogue ON CableProperty.CatalogueId = Catalogue.Id)    
WHERE (Catalogue.Core = @core) 
  AND (Catalogue.[Size] = @sizee) 
  AND (CableProperty.ProjectId = @projectId) 
  AND (CableProperty.CableApplicationId = @applicationId) 

另外注意原参数定义代码里的语法错误：new KeyValuePair<string, object>("@sizee", catalogue.Size)),多了一个右括号，需要去掉，否则编译会报错。

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