How do I create a new column in a type dataset

Question

I have a typed dataset object that fetches a table from my sql-server database. As far as I know, sql-server does not support TimeSpan type. How can I manage to add that new column in my C# Code ?

At the moment, I have a column which holds the ticks, so basically, in the database, I will store the amount of ticks, which will then be transformed as a TimeSpan and vice-versa (TimeSpan will be edit in the application and will then transformed into ticks to be then pushed to the database). My issue is that since it's a typed database that will be used along with a DevExpress Control (Gantt Control), I do not know how to add the new TimeSpan column and use it ?

The only solution I see is to create a new datatable based on the first one, copy the data in it, work with it and once user wants to update, push it back to my typed dataset and use sprocs to make the changes and then revert, but it does not seem like the best solution... Any ideas ?

Here is the code I use to generate a new property within my row, the issue is that I can't see my column.

public TimeSpan TimeSpanDuration
{
    get => IsDurationNull() ? new TimeSpan(Duration) : TimeSpan.Zero;
    set => Duration= value.Ticks;
}

Answer 1

Nothing stopping you adding properties to a typed dataset; it is, after all, merely what visual studio has done for you. Open your dataset, double click the datatable and you will be transported to code view with a prefilled partial class YourTablename. Right click YourTableName and choose Goto Definition to find the table inside the YourDataSet.Designer.cs file

Have a read; it's just a bunch of code that establishes named properties to the base DataTable and its columns. Use the dropdown at the top middle of the code window to find the partial class for your table's Rows. Here's how my Person table's Birthdate column looks on a row (the property that gets the birthdate-boxed-as-object from the datarow's item array, and casts it to a datetime so I don't have to):

        public System.DateTime Birthdate {
            get {
                try {
                    return ((global::System.DateTime)(this[this.tablePerson.BirthdateColumn]));
                }
                catch (global::System.InvalidCastException e) {
                    throw new global::System.Data.StrongTypingException("The value for column \'Birthdate\' in table \'Person\' is DBNull.", e);
                }
            }
            set {
                this[this.tablePerson.BirthdateColumn] = value;
            }
        }

Nothing stopping me declaring a partial class PersonRow of my own in my own code (don't do it in the designer file - it will be lost - do it in the file you were transported to when you double clicked the datatable in the designer) that has a property like:

        public int AgeInYears {
            get {
                try {
                    return (DateTime.Now - ((global::System.DateTime)(this[this.tablePerson.BirthdateColumn]))).Years;
                }
                catch (global::System.InvalidCastException e) {
                    throw new global::System.Data.StrongTypingException("The value for column \'Birthdate\' in table \'Person\' is DBNull. so the age cannot be calculated", e);
                }
            }
            set { //do we really want to allow setting via years??
                this[this.tablePerson.BirthdateColumn] = DateTime.Now.AddYears(-value);
            }
        }

Have a column that matches how SQLS stores the data (int64?) and build another set of properties to virtualize that column into how you want it (TimeSpan.FromMilliseconds?)