c# - 在存储过程的 SqlParameter 中使用 DateTime，格式错误

Question

我尝试使用 DateTime 作为 SqlParameter 的值从 C#、.NET 2.0 调用存储过程(在 SQL 2005 服务器上)。存储过程中的 SQL 类型是 'datetime'。

从 SQL Management Studio 执行存储过程工作正常。但每次我从 C# 调用它时，我都会收到有关日期格式的错误。

当我运行 SQL Profiler 来观察调用时，我会复制粘贴 exec 调用以查看发生了什么。这些是我对我所做尝试的观察和记录:

1) 如果我将 DateTime 直接作为 DateTime 传递或转换为 SqlDateTime，则该字段由一对单个引号，例如

@Date_Of_Birth=N''1/8/2009 8:06:17 PM''

2) 如果我将 DateTime 作为字符串传入，我只会得到单引号

3) 使用 SqlDateTime.ToSqlString() 不会生成 UTC 格式的日期时间字符串(即使在转换为通用时间之后)

4) 使用 DateTime.ToString() 不会生成 UTC 格式的日期时间字符串。

5) 手动将 SqlParameter 的 DbType 设置为 DateTime 不会改变上述观察结果。

那么，我的问题是，到底如何让 C# 在 SqlParameter 中传递格式正确的时间？当然这是一个常见的用例，为什么这么难开始工作？我似乎无法将 DateTime 转换为与 SQL 兼容的字符串(例如“2009-01-08T08:22:45”)

编辑

RE: BFree，实际执行sproc的代码如下:

using (SqlCommand sprocCommand = new SqlCommand(sprocName))
{
    sprocCommand.Connection = transaction.Connection;
    sprocCommand.Transaction = transaction;
    sprocCommand.CommandType = System.Data.CommandType.StoredProcedure;
    sprocCommand.Parameters.AddRange(parameters.ToArray());
    sprocCommand.ExecuteNonQuery();
}

要详细了解我尝试过的内容:

parameters.Add(new SqlParameter("@Date_Of_Birth", DOB));

parameters.Add(new SqlParameter("@Date_Of_Birth", DOB.ToUniversalTime()));

parameters.Add(new SqlParameter("@Date_Of_Birth", 
    DOB.ToUniversalTime().ToString()));

SqlParameter param = new SqlParameter("@Date_Of_Birth", 
    System.Data.SqlDbType.DateTime);
param.Value = DOB.ToUniversalTime();
parameters.Add(param);

SqlParameter param = new SqlParameter("@Date_Of_Birth", 
    SqlDbType.DateTime);
param.Value = new SqlDateTime(DOB.ToUniversalTime());
parameters.Add(param);

parameters.Add(new SqlParameter("@Date_Of_Birth", 
    new SqlDateTime(DOB.ToUniversalTime()).ToSqlString()));

额外编辑

我认为最有可能工作的那个:

SqlParameter param = new SqlParameter("@Date_Of_Birth",  
    System.Data.SqlDbType.DateTime);
param.Value = DOB;

如在 SQL 事件探查器中所见，在 exec 调用中产生此值

@Date_Of_Birth=''2009-01-08 15:08:21:813''

如果我修改为:

@Date_Of_Birth='2009-01-08T15:08:21'

它可以工作，但它不会用一对单引号解析，也不会正确地转换为日期和时间之间的空格以及末尾的毫秒数的 DateTime。

更新与成功

根据下面的请求，我复制/粘贴了上面的代码。为了简洁起见，我在这里和那里修剪了一些东西。原来我的问题出在我遗漏的代码中，我相信你们中的任何一个人都会立即发现这一点。我已经将我的存储过程调用包装在一个事务中。事实证明我根本就没有做 transaction.Commit()!!!!!不好意思说出来，但你知道了。

我仍然不知道我从探查器返回的语法发生了什么。一位同事在他的计算机上使用他自己的分析器实例进行了观察，它返回了正确的语法。从我的探查器中观察非常相同的执行显示了不正确的语法。它起到了转移注意力的作用，让我相信存在查询语法问题，而不是更简单和真实的答案，即我需要提交交易!

我在下面标记了一个答案是正确的，并对其他人投了一些赞成票，因为毕竟他们确实回答了这个问题，即使他们没有解决我的具体(脑残)问题。

Answer 1

您如何设置 SqlParameter ？你应该设置 SqlDbType property至 SqlDbType.DateTime然后传递 DateTime直接到参数(不要转换为字符串，那样你会遇到很多问题)。

您应该能够将值存入数据库。如果没有，这里有一个非常简单的示例来说明如何操作:

static void Main(string[] args)
{
    // Create the connection.
    using (SqlConnection connection = new SqlConnection(@"Data Source=..."))
    {
        // Open the connection.
        connection.Open();

        // Create the command.
        using (SqlCommand command = new SqlCommand("xsp_Test", connection))
        {
            // Set the command type.
            command.CommandType = System.Data.CommandType.StoredProcedure;

            // Add the parameter.
            SqlParameter parameter = command.Parameters.Add("@dt",
                System.Data.SqlDbType.DateTime);

            // Set the value.
            parameter.Value = DateTime.Now;

            // Make the call.
            command.ExecuteNonQuery();
        }
    }
}

我认为这里的部分问题是您担心 UTC 时间没有被传送到 SQL Server。为此，您不应该这样做，因为 SQL Server 不知道特定时间处于特定区域设置/时区。

如果要存储 UTC 值，则在将其传递给 SQL Server 之前将其转换为 UTC(除非您的服务器与生成 DateTime 的客户端代码具有相同的时区，即使这样，这是一个风险，IMO)。 SQL Server 将存储这个值，当你取回它时，如果你想以本地时间显示它，你必须自己做(DateTime 结构很容易做到)。

综上所述，如果您执行转换，然后将转换后的 UTC 日期(通过调用 ToUniversalTime method 获得的日期，而不是通过转换为字符串获得的日期)传递给存储过程。

当你取回值时，调用 ToLocalTime method获取本地时区的时间。