在SqlParameter中使用DateTime存储过程，格式错误 [英] Using DateTime in a SqlParameter for Stored Procedure, format error

查看：169
本文介绍了在SqlParameter中使用DateTime存储过程，格式错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我试图使用 DateTime 从C＃，.NET 2.0调用一个存储过程（在SQL 2005服务器上）作为一个 SqlParameter 。存储过程中的SQL类型为'datetime'。



从SQL Management Studio执行sproc可以正常工作。但是每次我从C＃中调用它，都会收到有关日期格式的错误。



当我运行SQL Profiler来观看调用时，我然后复制粘贴 exec 调用，看看发生了什么。这些是我对我尝试的观察和注意事项：



1）如果我直接通过 DateTime 作为 DateTime 或转换为 SqlDateTime ，该字段由单引号的PAIR包围，例如

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

2）如果我以一个字符串的形式传递了 DateTime ，我只得到单引号



3）使用 SqlDateTime.ToSqlString（）不会导致UTC格式的datetime字符串（即使转换为通用时间）



4）使用 DateTime.ToString（）不会导致UTC格式的datetime字符串。 / p>

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调查中显示此值中的值，如SQL Profiler中所示

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

如果我修改为：

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

它的工作原理，但不会解析对的单引号，并且不会将日期和时间之间的空间与末尾的毫秒数正确地转换为 DateTime 。



更新和成功



在下面的请求之后，我已经复制/粘贴了上面的代码。我在这里修剪的东西要简洁。原来我的问题是在我遗漏的代码中，我相信你们中的任何一个人都会发现。我把sproc电话包裹在一个交易中。事实证明，我根本就没有做到这一点。 transaction.Commit（） !!!!!我很ash愧地说，但是你有它。



我仍然不知道从分析器返回的语法正在发生什么。一个同事用他自己的电脑观察了他自己的分析器实例，并返回了正确的语法。从我的分析器看到非常SAME的执行显示错误的语法。它作为一个红鲱鱼，使我相信有一个查询语法问题，而不是更简单和真实的答案，这是我需要提交交易！



我将下面的答案标记为正确，并对其他人投了一些投票，因为他们毕竟回答了这个问题，即使他们没有修复我的具体（大脑流失）问题。

解决方案

你如何设置 SqlParameter ？您应该设置 SqlDbType 属性到 SqlDbType.DateTime ，然后传递 DateTime 直接参数（请勿转换为字符串，那么您在询问一堆问题）。



您应该能够将数据库中的值。如果没有，这里是一个非常简单的例子：

  static void Main（string [] args）
 {
 //创建连接。 
 using（SqlConnection connection = new SqlConnection（@Data Source = ...））
 {
 //打开连接。 
 connection.Open（）; 
 
 //创建命令。 
 using（SqlCommand command = new SqlCommand（xsp_Test，connection））
 {
 //设置命令类型。 
 command.CommandType = System.Data.CommandType.StoredProcedure; 
 
 //添加参数。 
 SqlParameter parameter = command.Parameters.Add（@ dt，
 System.Data.SqlDbType.DateTime）; 
 
 //设置值。 
 parameter.Value = DateTime.Now; 
 
 //拨打电话。 
 command.ExecuteNonQuery（）; 
} 
} 
}

我认为部分问题这里是你担心的时间是UTC的事实没有传达到SQL Server。为此，您不应该，因为SQL Server不知道特定时间在特定的区域/时区。



如果要存储UTC值，然后将其转换为UTC，然后将其转换为SQL Server（除非您的服务器与生成 DateTime 的客户端代码具有相同的时区，即使这样风险，IMO）。 SQL Server将存储这个值，当你收到它时，如果你想在当地时间显示，你必须自己去做（这个 DateTime struct很容易做到所有这一切，如果您执行转换，然后传递转换的UTC日期（通过调用 ToUniversalTime 方法，而不是转换为一个字符串）到存储过程。



当您获取该值时，请调用 ToLocalTime 方法在本地时区获取时间。 / p>

I'm trying to call a stored procedure (on a SQL 2005 server) from C#, .NET 2.0 using DateTime as a value to a SqlParameter. The SQL type in the stored procedure is 'datetime'.

Executing the sproc from SQL Management Studio works fine. But everytime I call it from C# I get an error about the date format.

When I run SQL Profiler to watch the calls, I then copy paste the exec call to see what's going on. These are my observations and notes about what I've attempted:

1) If I pass the DateTime in directly as a DateTime or converted to SqlDateTime, the field is surrounding by a PAIR of single quotes, such as

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

2) If I pass the DateTime in as a string, I only get the single quotes

3) Using SqlDateTime.ToSqlString() does not result in a UTC formatted datetime string (even after converting to universal time)

4) Using DateTime.ToString() does not result in a UTC formatted datetime string.

5) Manually setting the DbType for the SqlParameter to DateTime does not change the above observations.

So, my questions then, is how on earth do I get C# to pass the properly formatted time in the SqlParameter? Surely this is a common use case, why is it so difficult to get working? I can't seem to convert DateTime to a string that is SQL compatable (e.g. '2009-01-08T08:22:45')

EDIT

RE: BFree, the code to actually execute the sproc is as follows:

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();
}

To go into more detail about what I have tried:

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()));

Additional EDIT

The one I thought most likely to work:

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

Results in this value in the exec call as seen in the SQL Profiler

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

If I modify this to be:

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

It works, but it won't parse with pair of single quotes, and it wont convert to a DateTime correctly with the space between the date and time and with the milliseconds on the end.

Update and Success

I had copy/pasted the code above after the request from below. I trimmed things here and there to be concise. Turns out my problem was in the code I left out, which I'm sure any one of you would have spotted in an instant. I had wrapped my sproc calls inside a transaction. Turns out that I was simply not doing transaction.Commit()!!!!! I'm ashamed to say it, but there you have it.

I still don't know what's going on with the syntax I get back from the profiler. A coworker watched with his own instance of the profiler from his computer, and it returned proper syntax. Watching the very SAME executions from my profiler showed the incorrect syntax. It acted as a red-herring, making me believe there was a query syntax problem instead of the much more simple and true answer, which was that I need to commit the transaction!

I marked an answer below as correct, and threw in some up-votes on others because they did, after all, answer the question, even if they didn't fix my specific (brain lapse) issue.

解决方案

How are you setting up the SqlParameter? You should set the SqlDbType property to SqlDbType.DateTime and then pass the DateTime directly to the parameter (do NOT convert to a string, you are asking for a bunch of problems then).

You should be able to get the value into the DB. If not, here is a very simple example of how to do it:

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();
        }
    }
}

I think part of the issue here is that you are worried that the fact that the time is in UTC is not being conveyed to SQL Server. To that end, you shouldn't, because SQL Server doesn't know that a particular time is in a particular locale/time zone.

If you want to store the UTC value, then convert it to UTC before passing it to SQL Server (unless your server has the same time zone as the client code generating the DateTime, and even then, that's a risk, IMO). SQL Server will store this value and when you get it back, if you want to display it in local time, you have to do it yourself (which the DateTime struct will easily do).

All that being said, if you perform the conversion and then pass the converted UTC date (the date that is obtained by calling the ToUniversalTime method, not by converting to a string) to the stored procedure.

And when you get the value back, call the ToLocalTime method to get the time in the local time zone.

这篇关于在SqlParameter中使用DateTime存储过程，格式错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
C#/.NET最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆