CODEWITHSUNDEEP
c#sql-serverdatetime
user466663
user466663

Reputation: 845

Linq and datetime validation

I am trying to insert a DateTime value in sqlserver using linq. The DateTime value in the csharp side may be null. The corresponding field in sqlserver is a nullable datetime field. Following is my code:

using (var dataContext = GetDataContext())
{
    DateTime dateTime;
    var combinedWorkBasket = new CombinedWorkBasket()
    {

        FirstName = combinedWorkbasketData.FirstName,
        LastName = combinedWorkbasketData.LastName,
        Address1 = combinedWorkbasketData.Address1,
        RetirementDate = Convert.ToDateTime(combinedWorkbasketData.RetirementDate),

    };
    dataContext.CombinedWorkBaskets.InsertOnSubmit(combinedWorkBasket);
    dataContext.SubmitChanges();
}

When combinedWorkbasketData.RetirementDate happens to be null, which is a string value, which could be a valid date or null, then sqlserver throws error saying the date should be within range. When combinedWorkbasketData.RetirementDate happens to be null, Convert.ToDateTime(combinedWorkbasketData.RetirementDate) translates to some invalide data value. I tried the following, still same issue.

RetirementDate = DateTime.TryParse(combinedWorkbasketData.RetirementDate, out temp) ? Convert.ToDateTime(combinedWorkbasketData.RetirementDate) : Convert.ToDateTime(null)

I simply want to accomplish the following: When combinedWorkbasketData.RetirementDate is a valid date insert RetirementDate, otherwise don't insert it but insert other values such as Firstname etc

Thanks

Upvotes: 0

Views: 1080

Answers (3)

Rajat Saini
Rajat Saini

Reputation: 557

The problem is that datetime does not holds null value but it holds minimum time i.e. some thing like this. '1/1/0001 12:00:00 AM'. Instead of directly passing the date time try using

DateTime.MinValue== Convert.ToDateTime(combinedWorkbasketData.RetirementDate)?Null:Convert.ToDateTime(combinedWorkbasketData.RetirementDate)

I have not tested the code use it as reference u might be needing some refinement.

Upvotes: 1

Adil
Adil

Reputation: 3268

I think RetirementDate must be of nullable type for Entity framework to insert DBNull.

So make it like

public class CombinedWorkBasket
{
    // other fields
    public DateTime? RetirementDate { get; set; }
}

Then try assigning Null as per logic with associated column as "Allow Null" in database.

Hopefully it will insert null.

Upvotes: 0

JohnB
JohnB

Reputation: 13713

You might want to use a nullable data type, but this helps only if your database permits null values in the RetirementDate column. In addition, you must make the RetirementDate field in class CombinedWorkBasket nullable, too.

using (var dataContext = GetDataContext())
{
    DateTime? dateTime;
    var combinedWorkBasket = new CombinedWorkBasket()
    {

        FirstName = combinedWorkbasketData.FirstName,
        LastName = combinedWorkbasketData.LastName,
        Address1 = combinedWorkbasketData.Address1,
        RetirementDate = combinedWorkbasketData.RetirementDate != null ?
            Convert.ToDateTime(combinedWorkbasketData.RetirementDate) : null;

    };
    dataContext.CombinedWorkBaskets.InsertOnSubmit(combinedWorkBasket);
    dataContext.SubmitChanges();
}

Upvotes: 1

Related Questions