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

本文关键字:SQL Server 数据库 传输 WCF 日期 时间 JSON | 更新日期: 2023-09-27 18:28:51

在过去的几天里,我注意到一个问题,提交到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提交一个毫秒的日期时间,则插入了正确的日期。问题似乎出在我提交的格式上。我对这个问题有点不知所措。我真的不明白它为什么要这么做。如有任何帮助,我们将不胜感激。谢谢

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

我想,在形成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());

如果您有一个.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元素,它有一个从服务器附加的字符串(我使用它进行测试)

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

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