Working with TIME type in SQL and ASP .Net

Scenario: a teacher schedules his courses on a random different dates in the near future, but he always know the start time. For sample in the current week he can schedule a course for Tuesday and Friday, at 08:00 AM.
In a database there would be the following tables:
1. course (title, summary, ..)
2. course_schedule(course_id, location, start_time) where data about that schedule is preserved( ie. location)
3. course_schedule_span(schedule_id, course_date) for storing the dates when the schedule spans

The dates have always the same start time, I knew this before designing the database, so in the first time I let the time in the course_schedule_span table, in course_date column (ie. 31/12/2011 16:00). Later the teacher wanted to set and the end time so I decided to move the start time into course_schedule table and this new end time field to add it there also.
The first attempt was to store the time in a varchar(5) column, this would suffice for storing like hh:mm values. Later I changed my mind, when I found out about the TIME type from SQL Server.

The minimal option to store hh:mm is TIME(0) which means the fractional seconds precision is 0 ( size is 3 bytes, less than the 5 bytes used by varchar(5)).

The first task was to update the time from the existing courses in the hh:mm format (just to be sure there are no seconds somewhere).


update course_schedule
set start_time = (select top 1
convert(time(0),substring(convert(varchar, convert(time(0),course_schedule_span.course_date)),1,5))
from course_schedule_span
where course_schedule_span.schedule_id = course_schedule.schedule_id)

Extract time from date with convert(time(0)), convert to varchar to substring the hour and minute and then convert back to time(0).

The next thing was the update the Entity Framework model, I tried with Update model from database, but it didn't add the newly created columns, maybe because it can't handle TIME types, so I used "Add scalar property" function, I set the type to Time and with "Table mappings" I set the rest of the mappings.

The page needs to update only the hours and the minutes, not the seconds, so the Bind functions need to use the format parameter to show only the hour and the minutes.
Here I had some problems. First was an exception "Input string was not in a correct format", which occurs when I used the following expression
 
Text='<%# Bind("end_time","{0:hh:mm}") %>'

After some tests I figured out the semicolon it caused this so I escaped it with \.
However, when I used the Eval construct, in other page, with the same format, I got the following error "CS1009: Unrecognized escape sequence". A bit confused here, and when I checked the pages's generated class I saw that the generator it escapes the backslash when is in Bind constructs, but it doesn't do this for Eval constructs.

Eval needs the format string to be escaped
Line 1877:         public void @__DataBinding__control28(object sender, System.EventArgs e) {
ASP.controls_ldbtextfield_ascx dataBindingExpressionBuilderTarget;
System.Web.UI.WebControls.FormView Container;
dataBindingExpressionBuilderTarget = ((ASP.controls_ldbtextfield_ascx)(sender));
Container = ((System.Web.UI.WebControls.FormView)(dataBindingExpressionBuilderTarget.BindingContainer));

#line 56 "...\schedules\event-schedule-details.aspx"
dataBindingExpressionBuilderTarget.Text = global::System.Convert.ToString( Eval("start_time","{0:hh\:mm}") , global::System.Globalization.CultureInfo.CurrentCulture);

#line default
#line hidden
}

Bind escapes the string used as a format parameter

Line 1950: public void @__DataBinding__control29(object sender, System.EventArgs e) {
ASP.controls_ldbtextfield_ascx dataBindingExpressionBuilderTarget;
System.Web.UI.WebControls.FormView Container;
dataBindingExpressionBuilderTarget = ((ASP.controls_ldbtextfield_ascx)(sender));
Container = ((System.Web.UI.WebControls.FormView)(dataBindingExpressionBuilderTarget.BindingContainer));
if ((this.Page.GetDataItem() != null)) {

#line 57 "...\schedules\event-schedule-details.aspx"
dataBindingExpressionBuilderTarget.Text = global::System.Convert.ToString(this.Eval("start_time", "{0:hh\\:mm}"), global::System.Globalization.CultureInfo.CurrentCulture);

#line default
#line hidden
}
}

So, to format a TimeSpan in hh:mm style, Bind needs non-escaped format and Eval needs an escaped format.

Text='<%# Bind("start_time","{0:hh\:mm}") %>'
and
Text='<%# Eval("start_time",@"{0:hh\:mm}") %>'

Comments

Popular posts from this blog

IIS 7.5, HTTPS Bindings and ERR_CONNECTION_RESET

Table Per Hierarchy Inheritance with Column Discriminator and Associations used in Derived Entity Types

Verify ILogger calls with Moq.ILogger