C# time problem

Question

I have a table in sql and data type is (time(7),null) and I am uploading data to table via c# app. now in code part what ever I did, I could not find "time" data type;

now code for this column is like that;

comm.Parameters.AddWithValue("@TESNO_KALNO", dtr[3].ToString());

(this part is old part) //comm.Parameters.AddWithValue("@OD_NOD", dtr[4].ToString());

                            if (!string.IsNullOrEmpty(dtr[4].ToString()))
                            {
                         comm.Parameters.AddWithValue("@OD_NOD", Convert.ToDateTime(dtr[4].ToString()));
                            }
                            else
                            {
                                comm.Parameters.AddWithValue("@OD_NOD", DBNull.Value);

OD_NOD column is the source of problem.

what should I do ?

Thank you

Answer 1

To start with, you should never use AddWithValue. It's evil, see Dan Guzman's blog post to learn why: https://www.dbdelta.com/addwithvalue-is-evil/.

So you your code should probably be:

comm.Parameters.Add("@OD_NOD", SqlDbType.Time) dtr[4];
comm.Parameters["@OD_NOT"].Precision = 7;

Answer 2

Here is an example of an update with time(7), full source.

public bool UpdateStartTime(int id, TimeSpan startTime)  
{  
    bool success = false;  
    mHasException = false;  
    const string updateStatement = "UPDATE dbo.TimeTable SET StartTime = @TimeValue  WHERE id = @Id";  
  
    using (var cn = new SqlConnection() {ConnectionString = ConnectionString})  
    {  
        using (var cmd = new SqlCommand() {Connection = cn, CommandText = updateStatement })  
        {  
            cmd.Parameters.AddWithValue("@Id", id);  
            cmd.Parameters.Add("@TimeValue", SqlDbType.Time).Value = startTime;  
            cn.Open();  
            success = cmd.ExecuteNonQuery() == 1;  
        }  
    }  
  
    return success;  
}  

A read operation

public class Person  
{  
    public int Id { get; set; }  
    public string FirstName { get; set; }  
    public string LastName { get; set; }  
    public TimeSpan StartTime { get; set; }  
    public TimeSpan EndTime { get; set; }  
  
}  


public List<Person> ReadTimeTable()  
{  
    var pList = new List<Person>();  
  
    mHasException = false;  
  
    const string selectStatement = "SELECT  id,FirstName,LastName,StartTime,EndTime " +   
                                    "FROM dbo.TimeTable;";  
  
    using (var cn = new SqlConnection() { ConnectionString = ConnectionString })  
    {  
        using (var cmd = new SqlCommand() { Connection = cn, CommandText = selectStatement })  
        {  
            try  
            {  
                cn.Open();  
                var reader = cmd.ExecuteReader();  
                while (reader.Read())  
                {  
                    pList.Add(new Person()  
                    {  
                        Id = reader.GetInt32(0),  
                        FirstName = reader.GetString(1),  
                        LastName = reader.GetString(2),  
                        StartTime = reader.GetTimeSpan(3),  
                        EndTime = reader.GetTimeSpan(4)  
                    });  
                }  
            }  
            catch (Exception e)  
            {  
                mHasException = true;  
                mLastException = e;  
            }  
        }  
    }  
  
    return pList;  
}  

Answer 3

What I've seen are good recommendations although you (with no disrespect) are blindly making attempts without understanding the mechanics. When stuck like this step outside of the application, create either a unit test project or console project and figure out how to do what is needed which not only solves the problem but also gives you knowledge.

For example in the following console project I mocked up two different ways to contain a TimeSpan in a DataTable which in a real application would had been read from a database table.

• UsingString() stores time as a string, not recommended, always strong type objects/values
• StronglyTyped() stores time as a TimeSpan

To keep code clean and also usable in the real application here are several extension methods to consider and are used in the code below.

public static class Extensions  
{  
    public static bool IsNullOrWhiteSpace(this string sender) => string.IsNullOrWhiteSpace(sender);  
    public static TimeSpan ToTimeSpan(this string sender) => TimeSpan.Parse(sender);  
    public static bool IsValidTimeFormat(this string sender) => TimeSpan.TryParse(sender, out _);  
    public static T GetValue<T>(this IDataReader sender, string columnName)  
    {  
        var value = sender[columnName];  
        return value == DBNull.Value ? default : (T)value;  
    }  
}  

Code samples to learn from

using System;  
using System.Data;  
  
namespace TimeSpanDataTable  
{  
    class Program  
    {  
        static void Main(string[] args)  
        {  
            Console.Title = "Code sample";  
  
            UsingString();  
            StronglyTyped();  
              
            Console.ReadLine();  
  
        }  
  
        private static void UsingString()  
        {  
            Console.ForegroundColor = ConsoleColor.Cyan;  
            Console.WriteLine($"{nameof(UsingString)}: Using string");  
            Console.ResetColor();  
              
            DataTable dataTable = new DataTable();  
            dataTable.Columns.Add(new DataColumn("StartTime", typeof(string)));  
  
            dataTable.Rows.Add("10:10:0");  
            dataTable.Rows.Add("");  
            dataTable.Rows.Add("10:10:0");  
            dataTable.Rows.Add("10-10/0");  
  
            foreach (DataRow row in dataTable.Rows)  
            {  
                if (row.Field<string>("StartTime").IsNullOrWhiteSpace())  
                {  
                    Console.WriteLine("Empty");  
                }  
                else  
                {  
                    if (row.Field<string>("StartTime").IsValidTimeFormat())  
                    {  
                        Console.WriteLine(row.Field<string>("StartTime").ToTimeSpan());  
                    }  
                    else  
                    {  
                        Console.WriteLine($"{row.Field<string>("StartTime")} not valid");  
                    }  
                }  
            }  
  
            Console.WriteLine();  
        }  
  
        private static void StronglyTyped()  
        {  
            Console.ForegroundColor = ConsoleColor.Cyan;  
            Console.WriteLine($"{nameof(StronglyTyped)}: Using TimeSpan");  
            Console.ResetColor();  
  
  
  
            DataTable dataTable = new DataTable();  
            dataTable.Columns.Add(new DataColumn("StartTime", typeof(TimeSpan)));  
  
            dataTable.Rows.Add(DateTime.Now.TimeOfDay);  
            dataTable.Rows.Add();  
            dataTable.Rows.Add(new TimeSpan(10, 0, 0));  
  
            foreach (DataRow row in dataTable.Rows)  
            {  
                var test = row["StartTime"];  
                if (row["StartTime"] != DBNull.Value)  
                {  
                    Console.WriteLine(row.Field<TimeSpan>("StartTime"));  
                }  
                else  
                {  
                    Console.WriteLine("Null");  
                }  
            }  
        }  
    }  
}