C# DateTime Class and Datetime in database

Question

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

Could you pls. the code you are using? especially the SQL Statements you are using in your code. — Arthur
– Arthur, Commented Jun 18, 2010 at 6:54

Arthur · Accepted Answer · 2010-06-18 07:02:51Z

7

Without the quotes

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

answered Jun 18, 2010 at 7:02

Arthur

8,1493 gold badges32 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bobby · Accepted Answer · 2010-06-18 06:55:40Z

0

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

answered Jun 18, 2010 at 6:55

Bobby

11.6k5 gold badges48 silver badges70 bronze badges

1 Comment

Jon Skeet Over a year ago

@Spyros: Then you shouldn't see this problem. Please post the code.

mattmc3 · Accepted Answer · 2010-06-19 22:29:47Z

0

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.

answered Jun 19, 2010 at 22:29

mattmc3

18.6k8 gold badges88 silver badges109 bronze badges

Collectives™ on Stack Overflow

C# DateTime Class and Datetime in database

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related