Background
I'm currently working on a database-based ASP.NET application where I have 3 date fields on the screen. I use TextBox controls for the Date fields and the column format in the database is "date".
Saving to and reading from the database was no problem as long as the input type of the TextBox was datettime, but I ran into a major issue when I wanted to have the TextBox of input type "date" - the code could be compiled but when executing, the program crashed.
Wrong Turn
My first attempt to map from the TextBox to the database looked something like this:DataTable DB_Table = new DataTable //I'm doing everything in the code with DataTables reflecting the input parameters of stored procedures
[... create table structure, etc. ...]
DB_Table["DateColumn"] = TextBox_Date.Text // This worked fine as long as this was single line or datetime
[... update table in DB ...]
Solution
After a bit of searching the web and try and error I came up with the following solution, which works, but which still has the disadvantge of hard coded date format. I believe, someone more clever then me could do this in a more generic way:Mapping DB date format to TextBox date format
As one can see, the mapper inputs and outputs strings, which have to have the right format. As I am living in Germany, I chose the date format to be dd.MM.yyyy:
private String convertDBDateToTextBoxDate(String inputDateString)
{
String outputDateString;
String inputFormat = "dd.MM.yyyy HH:mm:ss";
String outputFormat = "{0:dd.MM.yyyy}";
DateTime closedDate =
DateTime.ParseExact(inputDateString,
inputFormat, CultureInfo.InvariantCulture);
// This line is actually the main point!!!
outputDateString =
String.Format(outputFormat, closedDate);
return outputDateString;
}
Mapping TextBox date format to DB date format
The approach above can be used as well vice versa:
private String convertTextBoxDateToDBDate(String inputDateString)
{
String outputDateString;
String outputFormat = "{0:dd.MM.yyyy HH:mm:ss}";
String inputFormat = "dd.MM.yyyy";
DateTime closedDate =
DateTime.ParseExact(inputDateString,
inputFormat, CultureInfo.InvariantCulture);
outputDateString = String.Format(outputFormat,
closedDate);
return outputDateString;
}
Surrrounding Code - Procedures to Update DB
First the code to map screen values to DB:
private DataRow updateValuesFromScreen(DataRow ActionItem)
{
if (TB_Date.Text != "") {
theDataRow["MeetingDate"] =
convertTextBoxDateToDBDate(TB_Date.Text);
}
[... code filling all the other fields ...]
return theDataRow;
}Then the code of the ButtonSave_Click event:
protected void ButtonSave_Click(object sender, EventArgs e)
{
DataRow theDataRow;
classDataHandler theDataHandler =
new classDataHandler();
// The data handler is a class where I do the processing of data from / to DB
theDataRow =
theDataHandler.getSingleRow(<keyValuesInDB>);
// This is required, because theDataRow is not filled due to the REST paradigm.
theDataRow = updateValuesFromScreen(theDataRow);
theDataHandler.updateActionItem(theDataRow);
}
Surrrounding Code - Procedures to Update UI
The method getActionItem() updates the screen-fields by 1) fetching data from DB and 2) calling the conversion method convertDBDateToTextBoxDate (see above)private void getActionItems()
{
classDataHandler theDataHandler =
new classDataHandler();
tableWithRows =
theDataHandler.getRows(<db keys>);
[... some more code ...]
setScreenTexts(tableWithRows);
}
private void setScreenTexts(DataTable theDataTable)
{
DataRow theDataRow =
theDataTable.Rows[<index>];
if (theDataRow["MyDate"].ToString() != "")
{
TB_Date.Text =
convertDBDateToTextBoxDate(theDataRow["MyDate"].ToString());
}
else { TB_Date.Text = ""; }
[... other code ...]}
I hope this helps.
Cheers
W
