JSON日期时间通过WCF传输到SQL Server数据库

Question

在过去的几天里，我注意到一个问题，提交到sql server数据库的日期是错误的。我有一个网页，用户可以在那里预订设施。这个网页有一个名称、日期、开始时间和结束时间（交易需要BookingID，但由数据库生成），我将其格式化为JSON字符串，如下所示：

{"BookingEnd":"/Date(2012-26-03 09:00:00.000)/","BookingID":1,"BookingName":"client test 1","BookingStart":"/Date(2012-26-03 10:00:00.000)/","RoomID":4}

然后将其传递给WCF服务，WCF服务按如下方式处理数据库插入：

[WebInvoke(Method = "POST", RequestFormat = WebMessageFormat.Json, UriTemplate = "createbooking")]
    void CreateBooking(Booking booking);
[DataContract]
public class Booking
{
    [DataMember]
    public int BookingID { get; set; }
    [DataMember]
    public string BookingName { get; set; }
    [DataMember]
    public DateTime BookingStart { get; set; }
    [DataMember]
    public DateTime BookingEnd { get; set; }
    [DataMember]
    public int RoomID { get; set; }
}

预订svc

public void CreateBooking(Booking booking)
    {
        BookingEntity bookingEntity = new BookingEntity()
                                        {
                                            BookingName = booking.BookingName,
                                            BookingStart = booking.BookingStart,
                                            BookingEnd = booking.BookingEnd,
                                            RoomID = booking.RoomID
                                        };
        BookingsModel model = new BookingsModel();
        model.CreateBooking(bookingEntity);
    }

预订模式：

public void CreateBooking(BookingEntity booking)
    {
        using (var conn = new SqlConnection("Data Source=cpm;Initial Catalog=BookingDB;Integrated Security=True"))
        using (var cmd = conn.CreateCommand())
        {
            conn.Open();
            cmd.CommandText =
                @"IF NOT EXISTS ( SELECT * FROM Bookings WHERE BookingStart = @BookingStart AND BookingEnd = @BookingEnd AND RoomID= @RoomID )
                    INSERT INTO Bookings ( BookingName, BookingStart, BookingEnd, RoomID ) VALUES ( @BookingName, @BookingStart, @BookingEnd, @RoomID )";
            cmd.Parameters.AddWithValue("@BookingName", booking.BookingName);
            cmd.Parameters.AddWithValue("@BookingStart", booking.BookingStart);
            cmd.Parameters.AddWithValue("@BookingEnd", booking.BookingEnd);
            cmd.Parameters.AddWithValue("@RoomID", booking.RoomID);
            cmd.ExecuteNonQuery();
            conn.Close();
        }
    }

这会更新数据库，但每次我以上述json格式提交日期时，时间都会以"1970-01-01 00:00:02.013"结束。但是，当我在SQL server management studio中使用上述日期格式（"YYYY-MM-DD HH:MM:SS.mmm"）进行查询时，它会插入正确的值。此外，如果我向wcf提交一个毫秒的日期时间，则插入了正确的日期。问题似乎出在我提交的格式上。我对这个问题有点不知所措。我真的不明白它为什么要这么做。如有任何帮助，我们将不胜感激。谢谢

Answer 1

我想，在形成Json字符串时，您不会使用Json解析器。如果您使用一个，您将看到DateTime类没有序列化为2012-26-03 09:00:00.000 形式

它应该是类似的东西

{"BookingEnd":"/Date(1332683123765+0300)/"}

或

{"BookingEnd":"/Date(1332683194843)/"}

自epoch（1970,1,1）以来的毫秒数

---

public class TestClass
{
    public DateTime BookingEnd;
}
//Using Json.Net
var str1 = JsonConvert.SerializeObject(new TestClass() { BookingEnd = DateTime.Now });
//Using JavaScriptSerializer 
JavaScriptSerializer ser = new JavaScriptSerializer();
var str2 = ser.Serialize(new TestClass() { BookingEnd = DateTime.Now });
//Using DataContractJsonSerializer 
MemoryStream m = new MemoryStream();
DataContractJsonSerializer ser2 = new DataContractJsonSerializer(typeof(TestClass));
ser2.WriteObject(m, new TestClass() { BookingEnd = DateTime.Now });
string str3 = Encoding.UTF8.GetString(m.ToArray());

Answer 2

如果您有一个.NET客户端来执行POST，那么使用以下方法将您的请求体POST到RESTful服务：

private static byte[] ToByteArrayUsingJsonContractSer<T> (T requestBody)
        {
            byte[] bytes = null;
            var serializer1 = new DataContractJsonSerializer(typeof(T));
            var ms1 = new MemoryStream();
            serializer1.WriteObject(ms1, requestBody);
            ms1.Position = 0;
            var reader = new StreamReader(ms1);
            bytes = ms1.ToArray();
            return bytes;
        }

一旦您有了请求体的字节数，就可以将其发布到请求流中，并使用Fiddler对其进行监控，您应该会看到您的JSON请求。确保将您的内容类型设置为application/json

请找到我使用上述方法使用的一个示例请求，开始日期为DateTime.Now，预订结束日期为DateTime.Now.AddDays（1）

POST http://localhost/SampleService/Service1.svc/postmethod/new HTTP/1.1
Content-Type: application/json
Host: rajeshwin7
Content-Length: 144
Expect: 100-continue
Connection: Keep-Alive
{"BookingEnd":"/Date(1332842057631+0100)/","BookingID":1,"BookingName":"client sent","BookingStart":"/Date(1332755657631+0100)/","RoomID":2}

一旦我做了以上的事情，我会得到如下所示的回复：

HTTP/1.1 200 OK
Cache-Control: private
Content-Length: 334
Content-Type: application/xml; charset=utf-8
Server: Microsoft-IIS/7.5
X-AspNet-Version: 4.0.30319
Set-Cookie: ASP.NET_SessionId=XXXXXXXXXXXXXXXXXXXXXX; path=/; HttpOnly
X-Powered-By: ASP.NET
Date: Mon, 26 Mar 2012 09:54:53 GMT
<Booking xmlns="http://schemas.datacontract.org/2004/07/XMLService" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"><BookingEnd>2012-03-27T10:54:17.631+01:00</BookingEnd><BookingID>1</BookingID><BookingName>client sent from server</BookingName><BookingStart>2012-03-26T10:54:17.631+01:00</BookingStart><RoomID>2</RoomID></Booking>

注意bookingName元素，它有一个从服务器附加的字符串（我使用它进行测试）

Answer 3

你能检查你的JSON格式吗。它应该是yyyy-MM-dd。您有yyyy-dd MM

"/Date(2012-03-26 09:00:00.000)/"