Convert SQL datetime to C# datetime while abstracted behind an object

Question

I am working on an application where one can manage patient data, including their date of birth. I can insert this data just fine into SQL using dapper, but pulling the date of birth out is its own challenge.

I have a SQL query that returns a list of relevant patients based on the search and in c# I cast these into the Patient class I have defined in the application. However, when I do this, I can't specify how the string containing the date of birth gets casted into a date time.

This means that instead of it using my custom format to cast to, it uses whatever default format it is made to do. This is causing the date to simply return 1/1/0001 12:00 AM. Here is my code for pulling out of the database:

public List<PatientModel> SearchPatientsByName(string name)
{
    List<PatientModel> output;
    using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString(db)))
    {
        var p = new DynamicParameters();
        p.Add("@Name", name);

        output = connection.Query<PatientModel>("spPatients_SearchByName", p, commandType: CommandType.StoredProcedure).AsList();
    }
    return output;
}

Here is my code for inserting into Sql:

       public PatientModel CreatePatientModel(PatientModel model)
    {

        using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString(db)))
        {
            model.Address = AesCryp.Encrypt(model.Address);
            model.CIN = AesCryp.Encrypt(model.CIN);


            var p = new DynamicParameters();
            p.Add("@Name", model.Name);
            p.Add("@DOB", model.DateOfBirth);
            p.Add("@Address", model.Address);
            p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
            p.Add("@LastName", model.LastName);
            p.Add("@Gender", model.Gender);
            p.Add("@City", model.City);
            p.Add("@PostalCode", model.PostalCode);
            p.Add("@Agency", model.AgencyId);
            p.Add("@CIN", model.CIN);
            p.Add("@CardCode", 0);
            p.Add("@MobileNumber", model.MobileNumber);
            p.Add("@HomeNumber", model.HomeNumber);
            p.Add("@InsuranceCompany", model.InsuranceCoId);
            connection.Execute("spPatient_Insert", p, commandType: CommandType.StoredProcedure);

            model.Id = p.Get<int>("@id");

            return model;

        }
    }

A proper DateTime is simply a value and has no format. So it sounds like maybe you are storing the Date as a string (*in my custom format*) which is sounds like it may be the root of the problem. — Ňɏssa Pøngjǣrdenlarp, Dec 24 '18 at 01:14
Welcome to StackOverflow. *"Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself."* Without the definition of the table and the SP, others cannot replicate your problem - it might be as simple as your query not selecting the DateOfBirth column. You may also want to [edit] your question to add the [tag:sql-server] tag, since not all SQL vendors support the `date` type. — Richardissimo, Dec 24 '18 at 07:07

Caius Jard · Answer 1 · 2018-12-24T07:44:13.873

0

As welcomeoverflow has indicated, you should ensure your setup is:

PatientModel has a BirthDateTime property that is a c# DateTime type
Your database table has a column for the birth datetime that is of some date/time type like DateTime or DateTime2(0)

Dapper will be able to map these two types together so they're equivalent. Keep dates in a properly typed variable. Never convert them to string for storage. Only convert them to string with a particular display format when you're putting them into a report

If you want to update your question with more detail as to your table structure and patient model I'll revise this answer accordingly (please comment after you've done so) with more info

edited Dec 24 '18 at 07:44

answered Dec 24 '18 at 06:01

Caius Jard

72,509
5
49
80

I would suggest that `date` would be a better SQL type to use for DateOfBirth, rather than *"DateTime or DateTime2(0)"*. – Richardissimo Dec 24 '18 at 07:08
I understand your point (and birthday is a poor name), but I disagree; we know nothing about this system or its requirements - it could be for tracking new born babies and the time component be important. You can easily CAST a DATETIME to a DATE if you don't need the time component for a particular operation so it makes sense to use datetime most of the time. The other reason that I recommend datetime as a primitive over date, is that people who get into the habit of using date columns one day need to store a time as well, and do something like have another column that's a varchar or an int. – Caius Jard Dec 24 '18 at 07:39
Knowing nothing about the system doesn't prevent `date` from being offered as one of the possible alternatives. Also: `DateTime` is superseded by `DateTime2`, so suggesting `datetime` isn't the best option ... https://stackoverflow.com/questions/1334143/datetime2-vs-datetime-in-sql-server – Richardissimo Dec 24 '18 at 07:58
@CaiusJard, I tried to use a datetime in sql and simply insert the unconverted datetime into the table but it gave me the following exception: 'The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.' Do you know why this would occur? I have updated the question to include my code for the insert statement. – Moroccan Jockey Dec 24 '18 at 17:26
Something in the pipeline of your c# datetime through dapper, to the table column datetime, is a string instread of a datetime – Caius Jard Dec 24 '18 at 18:04
If you changed the db column from varchar to datetime, did you remember to refresh/regenerate dapper's data model? – Caius Jard Dec 24 '18 at 18:06
@CaiusJard how does one refresh the data model? – Moroccan Jockey Dec 24 '18 at 19:33
How did you make it the first time? (I've never used dapper) – Caius Jard Dec 24 '18 at 19:41
(I'm just going off the way the other ORMs I've used (nhibernate and EF ) work - if you change the db scheme you have to make changes in the classes that the ORM uses to reflect the db changes – Caius Jard Dec 24 '18 at 20:05
@MoroccanJockey You still haven't provided a [mcve], which is why the only answer offered so far is Caius' guess. As I quoted earlier *"Questions seeking debugging help ("why isn't this code working?") must include ... the shortest code necessary to reproduce it in the question itself."*. – Richardissimo Dec 24 '18 at 23:08
@CaiusJard I forgot to change the stored procedure to taking in a datetime rather than varchar, But it is still giving me 1/1/0001 12:00 AM which is not correct. – Moroccan Jockey Dec 25 '18 at 01:41
Are you saying that if you have a datetime such as DateTime.Now in your c#, that this does get saved to your database but when you query the database in a separate query tool (sql server management studio) it is showing a value of 01/01/0001 00:00 ? I can't help but notice that this is the real world value of the DateTime.MinValue constant. Are you sure you're not doing something like initialising a patients DOB in the constructor as `this.DOB = DateTime.MinValue` and then you're forgetting to set to the actual date of birth, meaning you pass 01/01/0001 to the database? – Caius Jard Dec 25 '18 at 18:34
As a test, change the code where you add the DOB from `p.Add("@DOB", model.DateOfBirth);` to `p.Add("@DOB", DateTime.Now);` - if it starts inserting the current time, something is wrong with your model – Caius Jard Dec 25 '18 at 18:37

Convert SQL datetime to C# datetime while abstracted behind an object

1 Answers1