将DateTime从C#(LINQ)转换为SQL时出错 [英] Error convert DateTime from C# (LINQ) to SQL

问题描述

你好，

我在DateTime中插入变量类型有问题.

我在SQL Server Express 2005的数据库中具有以下表:

Hello,

I have a problem with insert variables types from DateTime.

I have the following Table in a DB in SQL Server Express 2005:

USE [SCCS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Log](
	[TimeID] [datetime] NOT NULL,
	[LogType] [int] NULL,
	[LogDesc] [nvarchar](50) COLLATE Modern_Spanish_CI_AS NULL,
 CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED 
(
	[TimeID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

然后我在数据库中有以下存储过程:

Then I have the following Stored Procedure in the DB:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spInsertLog](@UpdatedProdData nText)
AS
DECLARE @hDoc int   
 
 exec sp_xml_preparedocument @hDoc OUTPUT,@UpdatedProdData 

INSERT INTO [SCCS].[dbo].[Log]
           ([TimeID]
           ,[LogType]
           ,[LogDesc])
 SELECT 
	XMLProdTable.TimeID,
	XMLProdTable.LogType,
	XMLProdTable.LogDesc
    FROM OPENXML(@hDoc, ''ArrayOfLog/Log'', 2)   
       WITH (
				TimeID datetime,
				LogType int,
				LogDesc nvarchar(50)

            ) XMLProdTable

EXEC sp_xml_removedocument @hDoc
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

然后，在C#解决方案中，我在LINQ to SQL类(* .DBML文件)中包括表[Log]和存储过程dbo.spInsertLog.然后，我将使用以下代码将记录包括到该表中:

Then in my C# Solution I include in my LINQ to SQL Class (*.DBML File) the Table [Log] and the Stored Procedure dbo.spInsertLog. Then I will include records to this Table with the following code:

using (TestDataContext oDataBase = new TestDataContext())
{
    Log[] aRecords = new Log[2];
    for (int i = 0; i < 2; i++)
    {
        Log oRecord = new Log();
        oRecord.TimeID = DateTime.Now.AddDays(Convert.ToDouble(i));
        oRecord.LogType = i;
        oRecord.LogDesc = "Desc: " + i.ToString();
        aRecords[i] = oRecord;
    }

    StringBuilder sBuilder = new StringBuilder();
    System.IO.StringWriter sWriter = new System.IO.StringWriter(sBuilder);
    XmlSerializer serializer = new XmlSerializer(typeof(Log[]));
    serializer.Serialize(sWriter, aRecords);
    oDataBase.spInsertLog(sBuilder.ToString());
}

当我打电话给oDataBase.spInsertLog(sBuilder.ToString());我收到异常:
SqlException
将字符串转换为日期时间时的转换错误

有人可以告诉我我做错了吗?因为如果将DateTime类型更改为long类型，则可以使用.

When I call the line oDataBase.spInsertLog(sBuilder.ToString()); I receive a Exception:
SqlException
Conversion error when converting a string to datetime

Anyone can tell me I''m doing wrong?? Because if you change the DateTime type to long it works.

推荐答案

我认为XmlSerializer提供的日期/时间与OPENSQL期望的格式不匹配.如果我没记错的话，XmlSerializer包含一个时区.我不确定100％OPENXML支持它.我想尝试的一件事是在存储过程(OPENXML的"WITH"子句)中将datetime替换为datetimeoffset，看看它是否可以解决问题.

I think that the date/time the XmlSerializer provides does not match the format the OPENSQL expects. If I recall correctly, XmlSerializer includes a time zone; I am not 100% certain that OPENXML supports it. One thing I''d try is replacing datetime with datetimeoffset in the stored procedure (the ''WITH'' clause of OPENXML), and see if it fixes things.

这篇关于将DateTime从C#(LINQ)转换为SQL时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！