question

BE-7931 avatar image
0 Votes"
BE-7931 asked ErlandSommarskog commented

C# time problem

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-generaldotnet-csharp
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Use the C# TimeSpan structure. Your code example is trying to pass a string which forces SQL to dynamically cast the string to a time(7) type. you should use explicit types.

0 Votes 0 ·

TimeSpan is not active, do you know how can I use it ?

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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;
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

erland ; after l did wht you said,

127465-image.png


127460-image.png



I have this kind of error msg

0 Votes 0 ·
image.png (32.8 KiB)
image.png (93.9 KiB)

@BE-7931
I think it should be like this:

             command.Parameters.Add(@"OD_NOD", SqlDbType.Time).Value = Convert.ToDateTime(dtr[4].ToString());
             command.Parameters["OD_NOD"].Precision = 7;
0 Votes 0 ·

thank you ;

and 1 for (if) part & 2 for (else) part ?

0 Votes 0 ·

Could not read again :(

0 Votes 0 ·
Show more comments

Sorry about the error. Thankfully, Timon was able to correct me.

0 Votes 0 ·
karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered

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



figure1.png (6.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered

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



time1.png (9.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.