C# Working with SQL-Server time type in Windows forms
Introduction
The focus in this article is data binding time data from SQL-Server as a time data type which is represented client site as a TimeSpan.
SQL Server 2008 introduces a TIME data type which allows us to store the time without the date. TIME will do an implicit conversion from DATETIME and retain only the time portion. TIME will implicitly accept strings in most common time formats.
For perspective, the following shows datetime output (not to be confused with datetime2) to time field output between two different tables.
Example of a datetime field in a SQL-Server database table. The table has a primary key and datetime fields.
CREATE TABLE dbo.Events
(
EventID INT IDENTITY(1, 1) NOT NULL ,
StartDate DATETIME NOT NULL ,
EndDate DATETIME NOT NULL
);
SELECT statement to return data.
SELECT EventID ,
StartDate ,
EndDate
FROM DateTimeDatabase.dbo.Events;
Results for the SELECT where the highlight is the time portion of the datetime.
To separate date from time.
SELECT EventID ,
StartDate ,
CONVERT(VARCHAR(5), StartDate, 108) AS StartTime ,
EndDate ,
CONVERT(VARCHAR(5), EndDate, 108) AS EndTime
FROM dbo.Events;
The next example uses time fields rather than datetime.
Table structure
CREATE TABLE dbo.TimeTable
(
id INT IDENTITY(1, 1) NOT NULL ,
FirstName NVARCHAR(MAX) NULL ,
LastName NVARCHAR(MAX) NULL ,
StartTime TIME(7) NULL ,
EndTime TIME(7) NULL
)
Select
SELECT id ,
FirstName ,
LastName ,
StartTime ,
EndTime
FROM dbo.TimeTable;
Results
The following SELECT formats time fields with AM-PM.
SELECT id ,
FirstName ,
LastName ,
StartTime ,
FORMAT(CAST(StartTime AS DATETIME), 'hh:mm tt') BeginningTime ,
FORMAT(CAST(EndTime AS DATETIME), 'hh:mm tt') EnddingTime ,
EndTime
FROM dbo.TimeTable;
Results
In the last query the following provided hour:minutes AM/PM.
FORMAT(CAST(StartTime AS DATETIME), 'hh:mm tt') BeginningTime
Back in a project, a conversion would take only hour:minutes as highlighted in the first row start time above. You can also use the following to show just hour:minutes which can be helpful when data being returned and converted does not match what you expected.
FORMAT(CAST(StartTime AS DATETIME), 'hh:mm') BeginningTime
Note that when returning a time field into your program its data type is time yet when debugging viewing just the time field can be confusing while using hh:mm rather than hh:mm tt or raw is easier for debugging purposes.
When loading the above table into a DataTable you would access (in this case) StartTime as follows.
row.Field<TimeSpan>("StartTime")
If instead, a data reader was just to populate a list container were in the following reader is a SqlDataReader and StartTime is a property for a class instance.
const string selectStatement = "SELECT id,FirstName,LastName,StartTime,EndTime " +
"FROM dbo.TimeTable;";
StartTime = reader.GetTimeSpan(3)
Class used for the above.
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; }
}
With the above information for a grounding with datetime and time fields, it's time to work with data in a Visual Studio project.
With Entity Framework StartTime and EndTime are Nullable TimeSpan. And the extension methods presented can be applied to these fields as they are still TimeSpan where access is via Value and a null check can be done since they are nullable via timeTableItem.StartTime.HasValue.
public partial class TimeTable
{
public int id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public Nullable<System.TimeSpan> StartTime { get; set; }
public Nullable<System.TimeSpan> EndTime { get; set; }
}
Using time over DateTime
Using time may be the best choice when data time is not required or is redundant when storing a start and end time or for things like elapsed time.
Using DateTime with controls on a Windows form are straightforward. To collect date time information a developer places a DateTimePicker on the form, set a few properties and in turn is ready to receive your input. Backend operations on the selected datetime may prove challenging if there are start and end dates to deal although the DateTimePicker deals nicely with a foolproof control to collect date and time or date without time or time without a date.
If a task requires presenting records from a database table where one field is a DateTime a DataTimePicker can be data bound to a field.
The following DateTimePicker binds to a DateTime field from a BindingSource which has a DataTable as its DataSource.
StartTimeDateTimePicker.DataBindings.Add("Text", _bindingSource, "StartDate");
DateTimePicker for collecting time only
To collect a time value from a user, set a DateTimePicker Format property to "Time" and optionally set ShowUpDown property to true.
To get the current value access TimeOfDay property below Value.
Console.WriteLine(StartTimeDateTimePicker.Value.TimeOfDay);
If the currently selected time was 9:00 AM the above line would return 09:00:00. The property TimeOfDay type is TimeSpan which is suitable to update a value in a SQL-Server database table which will be discussed later. If the TimeOfDay value needs to be presented in a readable format the TimeSpan can be added to a DateTime object and formatted with.ToString method for the DateTime object. In this case, a language extension makes sense placed in a library within either a class within your project or in a class library which can be used in other projects.
public static string Formatted(this TimeSpan sender, string format = "hh:mm tt")
{
return DateTime.Today.Add(sender).ToString(format);
}
To use the extension method.
Console.WriteLine(StartTimeDateTimePicker.Value.TimeOfDay.Formatted());
Which will now return 09:00 AM. If the leading zero is unwanted the extension method can change the format as follows which excludes the leading zero.
public static string Formatted(this TimeSpan sender, string format = "h:mm tt")
{
return DateTime.Today.Add(sender).ToString(format);
}
DateTimePicker data binding issues with time only
The standard DateTimePicker when data bound to a time only field will properly read time for each row where the row may be a field in a DataRow of a DataTable or an instance of a class object when bound to a List<T> but update the that DateTimePicker value, tab to another control and the time reverts back to the unedited value. There are optional parameters for data binding which simply cause more issues which require more work then one should need to do.
A suitable workaround is to use a BindingSource component, set the BindingSource DataSource to a List<T> or a DataTable. Subscribe to the BindingSource event, CurrentChanged. In the following code snippet, the CurrentChange event calls a method HandleCurrentChanged which sets the proper field value (which is done rather than data binding as mentioned above). This only handles reading but taking care of when a user changes a value in the DataTimePicker. The HandleCurrentChanged event is used so when data is initially loaded a call is made to show the first row's time value as the CurrentChanged event is not called until a record movement is made from a search or one of the move methods of the BindingSource e.g. MoveNext, MoveLast etc.
private void _bindingSource_CurrentChanged(object sender, EventArgs e)
{
HandleCurrentChanged();
}
/// <summary>
/// Handle manually updating StartTime on current change of the BindingSource
/// </summary>
private void HandleCurrentChanged()
{
var row = ((DataRowView) _bindingSource.Current).Row;
StartTimeDateTimePicker.Value = row.Field<TimeSpan>("StartTime").ToDateTime();
}
To update the changed value subscribe to the DateTimePicker's ValueChanged event.
/// <summary>
/// Manually update StartTime
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void StartTimeDateTimePicker_ValueChanged(object sender, EventArgs e)
{
var row = ((DataRowView)_bindingSource.Current).Row;
row.SetField("StartTime", StartTimeDateTimePicker.Value.TimeOfDay);
}
To update the DateTimePicker value back to the database table get the underlying (in this case) DataRow.
var row = ((DataRowView)_bindingSource.Current).Row;
Create a method to do the SQL UPDATE which will require the primary key for the current record and the value to update. The call would look like this (which is included in the accompanying source code). If the update is not successful in this case the user is alerted there was an issue, otherwise, they are not informed of a successful update.
Alternate solutions
Create a custom DateTimePicker which handles data binding properly which for most developers is more work then the return investment. Another choice is using a third party control to handle time only with data binding which is always in a library with more controls then most developers need although using third party libraries fosters consistency if the developer always uses the library.
Third party control
Here is one custom TimePicker which has many options on how to present time.
Keeping with the example above, the TimePicker returns a DateTime and to get the Time the following code is required.
radTimePicker1.Value.Value;
Custom ComboBox
Create a custom ComboBox control which understands time. Implementation should include showing time for each hour of a day, each hour of the day with options to include quarter and half hour increments. Providing quarter and half hour increments go with what many time systems use rather than using 9:04 AM systems usually want 9:00 AM which goes similarly for increments of an hour. A utility class which is appropriate for creating hour ranges etc.
/// <summary>
/// Used to create a string array which represent time in a day.
/// </summary>
/// <remarks>
/// To get a range of TimeSpan rather than a string array.
/// var hours = new Hours();
/// var range = hours.Range(TimeIncrement.Quarterly).ToList().Select(time => time.TimeSpan());
/// In the above example a specific increment is used. This could easliy be a flexible language
/// extension method.
/// </remarks>
public class Hours
{
public string[] Range(TimeIncrement pTimeIncrement = TimeIncrement.Hourly)
{
const string timeHhMmTtformat = "hh:mm tt";
IEnumerable<DateTime> hours = Enumerable.Range(0, 24)
.Select((index) => (DateTime.MinValue.AddHours(index)));
var timeList = new List<string>();
foreach (var dateTime in hours)
{
timeList.Add(dateTime.ToString(timeHhMmTtformat));
if (pTimeIncrement == TimeIncrement.Quarterly)
{
timeList.Add(dateTime.AddMinutes(15).ToString(timeHhMmTtformat));
timeList.Add(dateTime.AddMinutes(30).ToString(timeHhMmTtformat));
timeList.Add(dateTime.AddMinutes(45).ToString(timeHhMmTtformat));
}
else if (pTimeIncrement == TimeIncrement.HalfHour)
{
timeList.Add(dateTime.AddMinutes(30).ToString(timeHhMmTtformat));
}
}
return timeList.ToArray();
}
}
Other considerations are what properties should be available such as TimeSpan for a selected item, Hour and minute properties so that nothing need be done in the user code. Various methods to find and set an item in the custom ComboBox by time string or TimeSpan. Sorting should be disabled as there is no reason to sort time values as they are needed to be presented in time order.
Custom ComboBox responsible for displaying time only.
public class TimeComboBox : ComboBox
{
private bool _shown = false;
public TimeComboBox()
{
DropDownStyle = ComboBoxStyle.DropDownList;
if (!DesignMode)
{
var hours = new Hours();
Items.AddRange(hours.Range(Increment));
}
Size = new Size(80, 21);
}
/// <summary>
/// Disable sorting
/// </summary>
#pragma warning disable 108,114
public bool Sorted => false;
#pragma warning restore 108,114
/// <summary>
/// Set current item in the ComboBox using a TimeSpan.
/// If the value passed in is not in the ComboBox -1 is returned.
/// </summary>
/// <param name="pTime"></param>
/// <returns>Index of item or -1 if not found</returns>
public int SetCurrentItem(TimeSpan pTime)
{
var dateTimeime = DateTime.Today.Add(pTime);
var displayTime = dateTimeime.ToString("hh:mm tt");
var index = FindString(displayTime);
SelectedIndex = index;
return index;
}
/// <summary>
/// Set current item by string which represents a valid TimeSpan
/// </summary>
/// <param name="pTime"></param>
/// <returns></returns>
public int SetCurrentItem(string pTime)
{
if (TimeSpan.TryParse(pTime, out var time))
{
var dateTimeime = DateTime.Today.Add(time);
var displayTime = dateTimeime.ToString("hh:mm tt");
var index = FindString(displayTime);
if (index > -1)
{
SelectedIndex = index;
}
return index;
}
else
{
return -1;
}
}
private TimeSpan _timeSpan;
/// <summary>
/// Get current selected item as a TimeSpan
/// </summary>
[Browsable(false)]
public TimeSpan TimeSpan => Convert.ToDateTime(Text.TrimStart('0')).TimeOfDay;
private int _hours;
/// <summary>
/// Get hour for selected item
/// </summary>
[Browsable(false)]
public int Hours => TimeSpan.Hours;
private int _Minutes;
/// <summary>
/// Get minutes for selected item
/// </summary>
[Browsable(false)]
public int Minutes => TimeSpan.Minutes;
/// <summary>
/// Determine if current selected item is AM
/// </summary>
[Browsable(false)]
public bool IsAM
{
get
{
var check = new DateTime(
DateTime.Now.Year,
DateTime.Now.Month,
DateTime.Now.Day,
TimeSpan.Hours,
TimeSpan.Minutes, 0);
return check.ToString("tt") == "AM";
}
}
/// <summary>
/// Determine if current selected item is PM
/// </summary>
[Browsable(false)]
public bool IsPM => !IsAM;
/// <summary>
/// Provides formatting e.g. 3:45 PM
/// </summary>
public string Formatted => TimeSpan.Formatted();
protected static TimeIncrement ParentIncrement { get; set; }
private TimeIncrement _increment;
/// <summary>
/// Get/set increment <see cref="TimeIncrement"/>
/// </summary>
[Category("Behavior"), Browsable(true), Description("Time increment")]
public TimeIncrement Increment
{
set
{
_increment = value;
ParentIncrement = value;
Items.Clear();
var hours = new Hours();
Items.AddRange(hours.Range(Increment));
}
get
{
return _increment;
}
}
private static string _hour = "";
[Category("Behavior"), Browsable(true), Editor(typeof(Editor), typeof(UITypeEditor)), Description("Hour get/set")]
public string Time
{
set
{
_hour = value;
SetHour();
}
get
{
return _hour;
}
}
private bool SetHour()
{
bool success = false;
if (string.IsNullOrWhiteSpace(Time))
{
Time = "00:00";
}
int result = FindString(Time);
if (result > -1)
{
SelectedIndex = result;
var item = Items.Count;
success = true;
}
else
{
SelectedIndex = 0;
}
return success;
}
private void TimeComboBox_VisibleChanged(object sender, EventArgs e)
{
if (!_shown)
{
SetHour();
_shown = true;
}
}
internal class Editor : UITypeEditor
{
private IWindowsFormsEditorService _svc;
public override UITypeEditorEditStyle GetEditStyle(ITypeDescriptorContext context)
{
return UITypeEditorEditStyle.DropDown;
}
public override object EditValue(ITypeDescriptorContext context, IServiceProvider provider, object value)
{
_svc = (IWindowsFormsEditorService)provider.GetService(typeof(IWindowsFormsEditorService));
var listBox = new ListBox();
var hours = new Hours();
var data = hours.Range(ParentIncrement);
foreach (var item in data)
{
listBox.Items.Add(item);
}
if (value != null)
{
listBox.SelectedItem = value;
}
_svc.DropDownControl(listBox);
value = (string)listBox.SelectedItem;
return value;
}
}
}
DomainUpDown
Another option for presenting time only is with a standard DomainUpDown control which would be populated using the same method as the Time ComboBox presented above. In the code block below a standard DomainUpDown is being populated.
var hours = new Hours();
HoursDomainUpDown.Items.AddRange(hours.Range(TimeIncrement.Quarterly));
In the included code sample methods are shown to keep the DomainUpDown control synchronized in this case with a List<T> read from SQL-Server database table.
When dealing with a DomainUpDown control loaded with time values which are strings which represent a TimeSpan method are needed to work with these values as a TimeSpan.
Extension method to parse a TimeSpan from a string that represents a valid TimeSpan.
public static TimeSpan TimeSpan(this string sender)
{
var sb = new StringBuilder(sender);
var result = sb.Replace(" AM", string.Empty).Replace(" PM", string.Empty).ToString();
return DateTime.TryParseExact(result, "hh:mm",
CultureInfo.InvariantCulture,DateTimeStyles.None, out var dt) ?
dt.TimeOfDay : new TimeSpan();
}
Now the above extension is a good example of code which appears like it should work but has issues. Perhaps the cool factor blinded the developer as it's concise and compact. A better method is shown below.
public static TimeSpan TimeSpan(this string sender)
{
var sb = new StringBuilder(sender);
var result = sb.Replace(" AM", string.Empty).Replace(" PM", string.Empty).ToString();
var tmp = result.Replace(":", "");
if (tmp.IsNumeric())
{
try
{
return System.TimeSpan.ParseExact(result, "h\\:mm", CultureInfo.CurrentCulture, TimeSpanStyles.None);
}
catch (FormatException)
{
return new TimeSpan();
}
}
else
{
return new TimeSpan();
}
}
Use to convert the currently select time string in a DomainUpDown control.
TimeSpan selectedTime = HoursDomainUpDown.Text.TimeSpan();
Formatting can be done by chaining another extension which formats with AM/PM designators.
public static string Formatted(this TimeSpan sender, string format = "hh:mm tt")
{
return DateTime.Today.Add(sender).ToString(format);
}
Example
Console.WriteLine($"DomainUpDown: {HoursDomainUpDown.Text.TimeSpan().Formatted()}");
Dealing with start and end times
Common rules would be that an end time may not be less than the selected start time and start time may not be greater than end time. Dependent on the control used the following extension methods will assist to determine if a value is out of range.
/// <summary>
/// Is end time prior to start time
/// </summary>
/// <param name="endTimeSpan"></param>
/// <param name="startTimeSpan"></param>
/// <returns></returns>
public static bool IsValidEndTime(this TimeSpan endTimeSpan, TimeSpan startTimeSpan)
{
return endTimeSpan.Hours < startTimeSpan.Hours;
}
/// <summary>
/// Is start time after end time
/// </summary>
/// <param name="startTimeSpan"></param>
/// <param name="endTimeSpan"></param>
/// <returns></returns>
public static bool IsValidStartTime(this TimeSpan startTimeSpan, TimeSpan endTimeSpan)
{
return endTimeSpan.Hours > startTimeSpan.Hours;
}
Time is in range
Another consideration is a selected time within a specific range when a control housing time does not have max and min values. The following generic language extension handles this requirement.
public static class GenericExtensions
{
public static bool Between<T>(this T actual, T lower, T upper) where T : IComparable<T>
{
return actual.CompareTo(lower) >= 0 && actual.CompareTo(upper) < 0;
}
}
Example where 8:15 is between 8:00 and 8:30.
var startTimeSpan = new TimeSpan(8, 00, 0);
var testTimeSpan = new TimeSpan(8,15,0);
var endTimeSpan = new TimeSpan(8, 30, 0);
if (testTimeSpan.Between(startTimeSpan,endTimeSpan))
{
Console.WriteLine("Yes");
}
else
{
Console.WriteLine("No");
}
References
- Date and Time types
- TimeSpan documentation
- Noda Time library
- TimeSpan2 library
- Time Period Library for .NET
- Data Forms Data Binding
Running code samples
In the Script project, open CreateDatabaseAndPopulate script in Visual Studio or SSMS (SQL-Server Management Studio) and execute the scripts to create the database, create two tables and populate both tables.
In the project BaseLibrary, class BaseSqlServerConnections change the property DatabaseServer from KARENS-PC to either .\SQLEXPRESS for the express edition of SQL-Server or a named instance for a full edition of SQL-Server, otherwise, a controlled exception will be raised informing you the property needs to change for your edition of SQL-Server.
There are three forms in the main project (WIndowsFormsApp1) where Form1 is setup as the main form. Compile and run, work through working with the custom ComboBox, DomainUpDown and DateTimePicker which work against SQL-Server data. While doing so traverse each record in the DataGridView (which has been setup to appear like a ListView) and note beneath the DataGridView there is a label which indicates if the end time is invalid against the start time e.g. end time is before start time. Once running the code review the underlying source code and set breakpoints to learn how everything works under the covers including various language extension methods.
Next in Program.cs change the startup form to ConventionalForm, build-run. In the form, data binding is done with a DateTime field bound to a DateTimePicker which works while as stated above if a time field was data bound changes would not be reflected properly.
Next in Program.cs change the startup for to DateTimePickerForm where in this case a time field is data bound to a DateTimePicker which relies on events to keep changes in the DateTimePicker in sync with the underlying DataTable which uses a TimeSpan instead of DateTime.
Take time to study the extension methods included in the source code in TimeLibrary.DateTimeExtensions. They can be used as is or copied into your project to use.
How to use in your projects
- TimeLibrary (class project) contains a class to create a range of times along with a custom ComboBox. Add this project to your Visual Studio solution to utilize the ComboBox and class to generate time range. This library also contains many useful language extensions for TimeSpan and DateTime conversions and formatting.
- Add a reference to your front end project for TimeLibrary.
- Build your solution.
- A new control, TimeComboBox will be added to your Visual Studio toolbox which can be used by following code samples in the source code provided with this article.
Summary
In this article the basics of understanding SQL-Server time field and receiving a time field data in a Visual Studio project along with obtaining time from SQL-Server datetime(7). Which field type to use is usually datetime(7) yet when date information is not required a time field fits in nicely.
Requires
- Microsoft Visual Studio 2017 or higher
- Microsoft SQL-Server
See also
- C#: Understanding Basics of DateTime and TimeSpan with an Example: Finding Working Day Difference Between Two Dates Based on Weekend And Bank Holidays
- T-SQL: Date-Related Queries
- T-SQL: Group by Time Interval
- Transact-SQL Portal
External
Source code
https://github.com/karenpayneoregon/WorkingWithTimeInVisualStudio