C# DateTime Class and Datetime in database

Question

Hello . I have the following problem.

I have an object with some DateTime properties , and a Table in database that I store all that objects , in Sql server I want to store the DateTime properties in some columns of DateTime Datatype, but the format of datetime in sql server is different from the DateTime class in c# and I got an sql exception saying "DateTime cannot be parsed". I know how to solve this by making the format yyyy-MM-dd but is this the proper and best solution to do this?

public void UpdateInvitation(string ownerId, string couponKey, string status, string invitedEmail, DateTime? sentDate, DateTime? redeemedDate)
    {
        using (var con = new SqlConnection(this.ConnectionString))
        {
            try
            {
                con.Open();
                var command =
                        new SqlCommand(
                                "UPDATE INVITATIONS SET Status='@status', InvitedEmail='@invitedEmail', SentDate='@sentDate', RedeemDate='@redeemedDate' WHERE CouponKey='@CouponKey' AND OwnerId='@OwnerId'");
                var ownerIdParam = new SqlParameter("@ownerId", ownerId);
                var couponKeyParam = new SqlParameter("@couponKey", couponKey);
                var statusParam = new SqlParameter("@status", status);
                var invitedEmailParam = new SqlParameter("@invitedEmail", invitedEmail);
                var sentDateParam = new SqlParameter("@sentDate", sentDate.Value) { SqlDbType = SqlDbType.DateTime };
                var redeemedDateParam = new SqlParameter("@redeemedDate", redeemedDate.Value) { SqlDbType = SqlDbType.DateTime };
                command.Parameters.AddRange(new[]
                                            {
                                                    ownerIdParam, couponKeyParam, statusParam, invitedEmailParam,
                                                    sentDateParam, redeemedDateParam
                                            });
                command.Connection = con;
                var rowsAffected = command.ExecuteNonQuery();
                Log.DebugFormat("Invitation updated for owner id : [{0}] with key {1}, {2} rows affected", ownerId,
                                couponKey, rowsAffected);
            }
            catch (Exception exception)
            {
                throw new InvitationDataContextException(exception);
            }
        }
    }

Answer 1

Well, without looking at your code I'd suggest that you start using Parameterized Queries.

Answer 2

Without the quotes

new SqlCommand("UPDATE INVITATIONS SET Status=@status, InvitedEmail=@invitedEmail, SentDate=@sentDate, ...

Answer 3

If you're using SQL Server 2008, the new datetime2 data type maps directly to the .NET DateTime object. If you're stuck with using SQL's old datetime field, be careful you don't pass an out-of-range date, or else you're looking at a runtime error. Trying to insert DateTime.MinValue is a common mistake.