How to use c# and datasets to read/write DateTime to SQL Server

Question

I have a table in SQL Server that has a column named sql_DateTime of type datetime. I also have a C# structure that maps to a single record from this table; in this structure is a member named m_DateTime of type DateTime.

My problem occurs after I retrieve a record from this table, using a DataSet, and then try to get the sql_DateTime from the Dataset into my m_DateTime variable. I get an InvalidCastException when I try to do it similar to the way I handle other datatypes.

My hope is then to be able to use a DateTimePicker in my GUI to display and set a date and time.

My code is attached for your reference. Thanks for any guidance.

 public bool GetExperiment(ref Experiment exp, int ExperimentID, ref string statusMsg)
    {
        bool ret = true;
        statusMsg = "GetExperiment: ";

        try
        {
            // Open the connection
            conn.Open();

            // init SqlDataAdapter with select command and connection
            string SelectString =
                @"SELECT * " +
                "FROM Experiment " +
                "WHERE ExperimentID = " + ExperimentID.ToString();

            SqlDataAdapter daExperiments = new SqlDataAdapter(SelectString, conn);

            // fill the dataset
            DataSet dsExperiments = new DataSet();
            daExperiments.Fill(dsExperiments, "Experiment");

            // assign dataset values to proj object that was passed in
            exp.m_ExperimentID = (int)dsExperiments.Tables["Experiment"].Rows[0]["ExperimentID"];
            exp.m_ProjectID = (int)dsExperiments.Tables["Experiment"].Rows[0]["ProjectID"];
            exp.m_Name = (string)dsExperiments.Tables["Experiment"].Rows[0]["Name"];
            exp.m_Description = (string)dsExperiments.Tables["Experiment"].Rows[0]["Description"];
            exp.m_UserID = (int)dsExperiments.Tables["Experiment"].Rows[0]["UserID"];

            // PROBLEM OCCURS HERE
            exp.m_DateTime = (DateTime)dsExperiments.Tables["Experiment"].Rows[0]["DateTime"];

        }
        catch (Exception ex)
        {

            ret = false;
            statusMsg += "Failed - " + ex.Message;
        }
        finally
        {

            // Close the connection
            if (conn != null)
            {
                conn.Close();
            }
        }
        return ret;
    }


public class Experiment
{
    public int m_ExperimentID;
    public int m_ProjectID;
    public string m_Name;
    public string m_Description;
    public int m_UserID;
    public DateTime m_DateTime;
}

There are tons of articles online how to case a MSSQL DATETIME into a DateTime .NET Structure. My guess your using the wrong variable type and they cannot be casted for that reason. Read this: http://stackoverflow.com/questions/1181662/is-there-any-difference-between-datetime-in-c-sharp-and-datetime-in-sql-server — Security Hound, Nov 10 '11 at 18:00

score 0 · Answer 1 · answered Nov 10 '11 at 18:00

0

You say:

I have a table in SQL Server that has a column named sql_DateTime

And yet you use:

exp.m_DateTime = (DateTime)dsExperiments.Tables["Experiment"]
                                        .Rows[0]["DateTime"];

Note the name. I'm surprised that you're getting a casting problem though. Is it possible that in reality you've got the name right but your row doesn't contain a value, so you're trying to cast DBNull.Value to DateTime?

Additionally, I'd say:

There's no need to pass exp by reference
I would personally separate the exception handling from the database access; I'd let the UI handle the exception
I'd use a named parameter instead of including the experiment ID directly into the SQL

answered Nov 10 '11 at 18:00

Jon Skeet

1,421,763
867
9,128
9,194

Jon - i think you were right. The datetime column in my sql table was set to allow NULLS, and in this case the value was NULL. I simply changed it not to allow NULLS, put values in for the existing records, and the casting issue went away! I figured it was a silly mistake. I am curious why you said that I didn't need to pass exp by reference...I need to return the exp object to the calling code. Maybe I've missed something. Anyway, Thanks for you excellent comments. – Bryan Greenway Nov 10 '11 at 19:21
great article. Clears things up for me. Thanks again for the help. – Bryan Greenway Nov 10 '11 at 21:17

score 0 · Answer 2 · answered Nov 10 '11 at 18:00

0

Are you using data binding? If so, are you binding to the proper property? And is the SQL one a nullable type or not?

answered Nov 10 '11 at 18:00

Jared Peless

1,120
9
11

score 0 · Answer 3 · answered Nov 10 '11 at 18:01

0

Try this :

        exp.m_DateTime =  DateTime.Parse(dsExperiments.Tables["Experiment"].Rows[0]["DateTime"].ToString());

answered Nov 10 '11 at 18:01

GregM

2,634
3
22
37

score 0 · Answer 4 · answered Nov 10 '11 at 18:02

The problem might be that the DataTable containing the data (Tables["Experiment"]) does not think the column ["DateTime"] is of type DateTime but rather a string.

I think you have to alternatives:

If you are sure it's always a DateTime, simply parse it:

exp.m_DateTime =DateTime.Parse(dsExperiments.Tables["Experiment"].Rows[0]["DateTime"].ToString());

Set dsExperiments.Tables["Experiment"].Columns["DateTime"].DataType=typeof(DateTime); before you attempt to read it.

How to use c# and datasets to read/write DateTime to SQL Server

4 Answers4