C# time problem

B E 1 Reputation point
2021-08-29T14:01:46.047+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,067 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,481 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 103.5K Reputation points MVP
    2021-08-29T18:39:02.903+00:00

    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;
    

  2. Karen Payne MVP 35,286 Reputation points
    2021-08-30T01:20:52.817+00:00

    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;  
    }  
    

    127230-figure1.png

    0 comments No comments

  3. Karen Payne MVP 35,286 Reputation points
    2021-08-30T11:32:54.113+00:00

    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");  
                    }  
                }  
            }  
        }  
    }  
      
    

    127540-time1.png

    0 comments No comments