CODEWITHSUNDEEP
c#sql-serverdatetimetimespan
SQLserving
SQLserving

Reputation: 400

Cannot implicitly convert DateTime to Timespan

Sql Server has the variable TEST_TIME data type as Time(7)
I created the tables in C# and it automatically assigned the Timespan datatype.
Now, i'm trying to upload csv file data to the SQL database and it is giving me an error " Cannot implicitly convert DateTime to Timespan". What would be the best way to fix this? The user first selects the CSV file: 

    private void button8_Click(object sender, EventArgs e)  
        {  
               try  
                    {  
                        using (OpenFileDialog openfiledialog1 = new OpenFileDialog()   
                        {Filter = "Excel Workbook 97-2003|*.xls|Excel Workbook|*.xlsx|Excel Workbook|*.xlsm|Excel Workbook|*.csv|Excel Workbook|*.txt", ValidateNames = true })                      
                        {   
--After some IFs--  

    else if (openfiledialog1.FilterIndex == 4)
                                {

                                    DataTable oDataTable = null;
                                    int RowCount = 0;
                                    string[] ColumnNames = null;
                                    string[] oStreamDataValues = null;
                                    //using while loop read the stream data till end
                                    while (!oStreamReader.EndOfStream)
                                    {
                                        String oStreamRowData = oStreamReader.ReadLine().Trim();
                                        if (oStreamRowData.Length > 0)
                                        {
                                            oStreamDataValues = oStreamRowData.Split(',');
                                            //Bcoz the first row contains column names, we will populate 
                                            //the column name by
                                            //reading the first row and RowCount-0 will be true only once
                                            if (RowCount == 0)
                                            {
                                                RowCount = 1;
                                                ColumnNames = oStreamRowData.Split(',');
                                                oDataTable = new DataTable();

                                                //using foreach looping through all the column names
                                                foreach (string csvcolumn in ColumnNames)
                                                {
                                                    DataColumn oDataColumn = new DataColumn(csvcolumn.ToUpper(), typeof(string));

                                                    //setting the default value of empty.string to newly created column
                                                    oDataColumn.DefaultValue = string.Empty;

                                                    //adding the newly created column to the table
                                                    oDataTable.Columns.Add(oDataColumn);
                                                }
                                            }
                                            else
                                            {
                                                //creates a new DataRow with the same schema as of the oDataTable            
                                                DataRow oDataRow = oDataTable.NewRow();

                                                //using foreach looping through all the column names
                                                for (int i = 0; i < ColumnNames.Length; i++)
                                                {
                                                    oDataRow[ColumnNames[i]] = oStreamDataValues[i] == null ? string.Empty : oStreamDataValues[i].ToString();
                                                }

                                                //adding the newly created row with data to the oDataTable       
                                                oDataTable.Rows.Add(oDataRow);
                                            }
                                        }
                                    }
                                    result.Tables.Add(oDataTable);
                                    //close the oStreamReader object
                                    oStreamReader.Close();
                                    //release all the resources used by the oStreamReader object
                                    oStreamReader.Dispose();
                                    dataGridView5.DataSource = result.Tables[oDataTable.TableName];
                                }

Here is the Code: 

    private void button9_Click(object sender, EventArgs e)
            {

                try
                {               


                    DataClasses1DataContext conn = new DataClasses1DataContext();
        else if (textBox3.Text.Contains("GEN_EX"))
            {
                foreach (DataTable dt in result.Tables)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        GEN_EX addtable = new GEN_EX()
                        {

                            EX_ID = Convert.ToByte(dr[0]),
                            DOC_ID = Convert.ToByte(dr[1]),
                            PATIENT_NO = Convert.ToByte(dr[2]),
                            TEST_DATE = Convert.ToDateTime(dr[3]),                    
                            **TEST_TIME = Convert.ToDateTime((dr[4])),**

                        };
                        conn.GEN_EXs.InsertOnSubmit(addtable);
                    }
                }
                conn.SubmitChanges();
                MessageBox.Show("File uploaded successfully");
            }  
     else
        {
            MessageBox.Show("I guess table is not coded yet");
        }
}

EDIT

The TEST_TIME represents HH:MM:SS
The Typed Data Set is defined as: 

public virtual int Update(
                    byte EX_ID, 
                    byte DOC_ID, 
                    byte PATIENT_NO, 
                    System.DateTime TEST_DATE, 
                    System.TimeSpan TEST_TIME)

Upvotes: 1

Views: 3577

Answers (2)

Chetan
Chetan

Reputation: 6891

Based on your input that dr[4] represents time values in hours:minutes:seconds format I recommend following solution.

private TimeSpan GetTimeSpan(string timeString)
{
    var timeValues = timeString.Split(new char[] { ':' });
    //Assuming that timeValues array will have 3 elements.
    var timeSpan = new TimeSpan(Convert.ToInt32(timeValues[0]), Convert.ToInt32(timeValues[1]), Convert.ToInt32(timeValues[2]));
    return timeSpan;
}

Use above method as following.

else if (textBox3.Text.Contains("GEN_EX"))
{
    foreach (DataTable dt in result.Tables)
    {
        foreach (DataRow dr in dt.Rows)
        {
            GEN_EX addtable = new GEN_EX()
            {

                EX_ID = Convert.ToByte(dr[0]),
                DOC_ID = Convert.ToByte(dr[1]),
                PATIENT_NO = Convert.ToByte(dr[2]),
                TEST_DATE = Convert.ToDateTime(dr[3]),                    
                **TEST_TIME = GetTimeSpan(dr[4].ToString()),**

            };
            conn.GEN_EXs.InsertOnSubmit(addtable);
        }
    }
    conn.SubmitChanges();
    MessageBox.Show("File uploaded successfully");
}

This should give your the value you want. You will face runtime issues if value of dr[4] is not in hours:minutes:seconds format. That I will leave it up to you.

Upvotes: 3

Lostblue
Lostblue

Reputation: 419

First of all Timespan and DateTime are 2 differents type without implicit conversion available. Since Timespan is a time value between two DateTime, you need to know your referenced time (DateTime) used to start the mesure of your Timespan.

For example it could be from DateTime dtReferential = new DateTime(1900, 01, 01);

In order to give a SQL Timespan value you need to give it a C# Timespan! Change TEST_TIME value to a Timespan. And finally, give it the substracted value of your referenced time.

Using the previous example:

else if (textBox3.Text.Contains("GEN_EX"))
{
    foreach (DataTable dt in result.Tables)
    {
        foreach (DataRow dr in dt.Rows)
        {
            GEN_EX addtable = new GEN_EX()
            {

                EX_ID = Convert.ToByte(dr[0]),
                DOC_ID = Convert.ToByte(dr[1]),
                PATIENT_NO = Convert.ToByte(dr[2]),
                TEST_DATE = Convert.ToTimespan(dr[3]),                    
                TEST_TIME = dtReferential.Subtract(Convert.ToDateTime(dr[4]))

            };
            conn.GEN_EXs.InsertOnSubmit(addtable);
        }
    }
    conn.SubmitChanges();
    MessageBox.Show("File uploaded successfully");
}

Upvotes: 1

Related Questions