MS Access、命名参数和列名 [英] MS Access, Named parameters and Column Names

问题描述

我在 Access 数据库上执行了以下查询.该查询在 Access 中运行时会返回准确的结果.但是，当从代码运行时，我会返回数据库中的所有项目，即使是那些超出我正在搜索的日期范围的项目.

I have the following query which I am executing on an Access database. The query, when run in Access returns accurate results. However when run from the code I get back all of the items in the database, even those which fall outside the date range I am searching for.

我想知道是不是因为参数名和表中的列名一样，所以我改变了参数名@StartDate 和@EndDate成为 @FromDate 和 @ToDate 并且这解决了问题，如果参数名称不同，我会得到正确的结果集.这让我担心，因为在我正在研究的项目中，这种模式到处都是重复的.但是，我使用名为 @Id 的参数通过 Id(db 表中的列名)更新记录，这工作正常.这是一个奇怪的边缘情况吗?任何人都可以解释这种行为.

I was wondering if the issue was because the parameter names are the same as the column names in the table, so I changed the parameter names @StartDate and @EndDate to be @FromDate and @ToDate and this fixed the problem, if the parameter names are different I get the right result set back. This concerns me because in the project that I am working on this pattern is duplicated all over the place. However I am using a parameter named @Id to update the records by Id (column name in db table) and this is working fine. Is this a weird edge case? Can anyone shed any light on this behaviour.

为大量代码示例道歉，但在这种情况下，我认为需要整个方法.

Apologies for the massive code sample, but in this case I think the whole method is needed.

  public override AcademicCycleTransportCollection FetchForDateRange(DateTime startDate, DateTime endDate) {
     const String query =
        "PARAMETERS 
" +
        "   @StartDate DATE, 
" +
        "   @EndDate DATE 
" +
        "   ; 
" +
        "SELECT 
" +
        "      [AcademicCycle].[Id] AS [Id], 
 "  +
        "      [AcademicCycle].[Name] AS [Name], 
 "  +
        "      [AcademicCycle].[AcademicCycleCategoryId] AS [AcademicCycleCategoryId], 
 "  +
        "      [AcademicCycle].[ParentAcademicCycleId] AS [ParentAcademicCycleId], 
 "  +
        "      [AcademicCycle].[StartDate] AS [StartDate], 
 "  +
        "      [AcademicCycle].[EndDate] AS [EndDate], 
 "  +
        "      [AcademicCycle].[IsPerpetual] AS [IsPerpetual], 
 "  +
        "      [AcademicCycle].[IsLocked] AS [IsLocked] 
 " +
        "FROM 
" +
        "  AcademicCycle 
" +
        "WHERE 
" +
        "  (StartDate <= @EndDate AND EndDate >= @StartDate) OR 
" +
        "  IsPerpetual <> 0";

     AcademicCycleTransportCollection transportCollection = new AcademicCycleTransportCollection();

     OleDbCommand _fetchForDateRangeCommand = null;

     if (_fetchForDateRangeCommand == null) {
        OleDbConnection connection = _parentDataConnection.Connection;
        _fetchForDateRangeCommand = new OleDbCommand(query, connection);
        _fetchForDateRangeCommand.Parameters.Add("@StartDate", OleDbType.Date);
        _fetchForDateRangeCommand.Parameters.Add("@EndDate", OleDbType.Date);
     }

     _fetchForDateRangeCommand.Transaction = _parentDataConnection.Transaction;

     _fetchForDateRangeCommand.Parameters["@StartDate"].Value = startDate;
     _fetchForDateRangeCommand.Parameters["@EndDate"].Value = endDate;

     using (OleDbDataReader dbReader = _fetchForDateRangeCommand.ExecuteReader()) {
        NullableDataReader reader = new NullableDataReader(dbReader);

        while (reader.Read()) {
           AcademicCycleTransport transport = FillTransport(reader);
           transportCollection.Add(transport);
        }
        if (!reader.IsClosed) {
           reader.Close();
        }
     }

     return transportCollection;
  }

推荐答案

你这样做的方式，OleDb 是使用位置参数插入，所以你在 SQL 中的第一个参数 '@EndDate' 被替换为第一个传递的参数， '@开始日期'.使用位置插入时，完全忽略参数名称.

The way you have done it, OleDb is using positional parameter insertion, so your first parameter in SQL, '@EndDate' is being substituted with the first parameter passed, '@StartDate'. The names of the parameters are completely ignored when using positional insertion.

然而，OleDb 实际上确实接受命名参数是一个鲜为人知的事实.您还需要在 SQL 中声明参数.

However, it's a little-known fact that OleDb actually DOES accept named parameters. You've just got to declare the parameters in SQL as well.

参见:low-bandwidth.blogspot.com.au/2013/12/positional-msaccess-oledb-parameters.html

如果你不在SQL中声明参数，OleDb使用纯粹的位置参数插入，并且参数名称是否与SQL匹配，或者参数在SQL中使用两次都没有关系 - 它只会遍历并盲目地将 SQL 中找到的任何参数从头到尾替换为通过的参数.

If you DON'T declare the parameters in SQL, OleDb uses purely positional parameter insertion, and it doesn't matter if the names of the parameters match the SQL, or if parameters are used twice in the SQL - it will just go through and blindly replace any found parameters in the SQL in order from start to end, with those passed.

但是，如果您确实正确声明了参数，您将受益于命名参数和允许在 SQL 语句中重复多次的参数.

However if you DO declare the parameters correctly, you get the benefit of named parameters and parameters allowed to be repeated multiple times within the SQL statement.

这篇关于MS Access、命名参数和列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！