SQL 紧凑日期时间怪异
我遇到了一个关于 Microsoft SQL Compact Edition 的奇怪问题。在 Windows.Forms 应用程序中，我尝试修改由同一（.Net 2.0）应用程序创建的数据库。数据库稍后会发送到 Windows Mobile Phone，但这并不重要。重要的是，在我的计算机的正常区域设置（英语（美国））上，在数据库中插入日期时间值不会出现任何问题。但是，当我将语言环境切换为荷兰语（荷兰）时，出现以下错误：
“日期格式的一部分出现错误。[表达式（如果已知）= ]”
我追踪到 DateTime 的外观在荷兰。但是，我不使用 DateTime.ToString()。我将其纯粹作为“column =” + DateTime 添加到 SQL 插入/更新语句中。这在“en-US”中完美运行，但当我切换到荷兰语时，它就崩溃了。
我解决这个问题的方法是为 DateTime 数据类型创建一个扩展方法，如下所示：
/// <summary>
/// Transforms a DateTime from various cultures into what SQL compact expects to get.
/// </summary>
/// <param name="original">DateTime to process.</param>
/// <returns>A SQL-friendly string.</returns>
public static string _ToSQLDateTimeString (this DateTime? original)
{ //No provided Date? Bye.
if (original == null) return null;
IFormatProvider usDate = new CultureInfo("en-US");
return ((DateTime)original).ToString(usDate);
}
但我希望有人能够确认/改进我的解决方案。也许我错过了什么？
I'm having a weird issue concerning Microsoft SQL Compact Edition. In a Windows.Forms application, I try to modify a database which was created by the same (.Net 2.0) application. The database gets sent to a Windows Mobile Phone later, but this is not important. What's important is that on the normal regional settings of my computer, which is English (USA), inserting DateTime values in the database happens without any problems. However, when I switch the locale to Dutch (Netherlands), I get the following error:
"There was an error in a part of the date format. [ Expression (if known) = ]"
I tracked it down to the way DateTime looks in NL. However, I do not use DateTime.ToString(). I add it to the SQL insert/update statements purely as "column = " + DateTime. This works perfectly in "en-US" but when I switch to Dutch, it blows up.
The way I fixed this is by creating an extension method for the DateTime datatype like so:
/// <summary>
/// Transforms a DateTime from various cultures into what SQL compact expects to get.
/// </summary>
/// <param name="original">DateTime to process.</param>
/// <returns>A SQL-friendly string.</returns>
public static string _ToSQLDateTimeString (this DateTime? original)
{ //No provided Date? Bye.
if (original == null) return null;
IFormatProvider usDate = new CultureInfo("en-US");
return ((DateTime)original).ToString(usDate);
}
But I would like somebody to maybe confirm / improve my solution. Maybe I missed something?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
但是
"column = " + DateTime隐式调用DateTime上的ToString()。您应该使用
System.Data.SqlClient.SqlCommand并使用command.Parameters.Add(...)添加日期（以及所有其他参数）。这将解决日期、浮点数等所有本地化问题。并保护您的应用程序免受 SQL 注入攻击。
But
"column = " + DateTimeimplicitly callsToString()on theDateTime.You should use
System.Data.SqlClient.SqlCommandand add the dates (and all other parameters) withcommand.Parameters.Add(...).This will fix all localization issues with dates, floats, etc. And protect your application from SQL injection attacks.
如果要将 DATETIME 作为字符串推送到数据库中，最好使用 2010-08-25T07:26:05 格式 - 但是，更好的方法是创建一个具有正确类型参数的命令对象，然后设置将 DATETIME 参数传递给 DateTime 对象，而不是对其进行字符串化。
SQL Server 不会转换任意日期字符串格式 - 它将使用其运行所在区域设置指定的格式（默认为美国）。
If you want to push the DATETIME into the database a string, you're best of using the 2010-08-25T07:26:05 format - however, better would be to create a command object with parameters of the right types and then set the DATETIME parameter to the DateTime object rather than stringifying it.
SQL Server won't convert abritrary date string formats - it'll use the format dictated by whichever locale it's running under (default is US).