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字符串时,您不会使用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)'/"