JSON datetime to SQL Server database via WCF

Posted by moikey on Stack Overflow See other posts from Stack Overflow or by moikey
Published on 2012-03-25T10:31:09Z Indexed on 2012/03/25 11:30 UTC
Read the original article Hit count: 203

Filed under:
|

I have noticed a problem over the past couple of days where my dates submitted to an sql server database are wrong. I have a webpage, where users can book facilities. This webpage takes a name, a date, a start time and an end time(BookingID is required for transactions but generated by database), which I format as a JSON string as follows:

{"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}

This is then passed to a WCF service, which handles the database insert as follows:

[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; }

}

Booking.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);

    }

Booking Model:

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();

        }
    }

This updates the database but the time ends up "1970-01-01 00:00:02.013" each time I submit the date in the above json format. However, when I do a query in SQL server management studio with the above date format ("YYYY-MM-DD HH:MM:SS.mmm"), it inserts the correct values. Also, if I submit a millisecond datetime to the wcf, the correct date is being inserted. The problem seems to be with the format I am submitting. I am a little lost with this problem. I don't really see why it is doing this. Any help would be greatly appreciated. Thanks.

© Stack Overflow or respective owner

Related posts about c#

Related posts about wcf