Problem

While working in one of our project we came across a problem. Database tables contains null data in date time columns. In a C# code a “DataTable” is prepared to retrieve data from Database using ADO.net. If In database query DateTime type column is null then c# “DataTable” DateTime” type column hold minimum date by default ({01/01/1900 12:00:00 AM}). We want to avoid min values and wanted to hold null value coming from database query.

Solution

One way we can make System.Nullable C# structure and then hold null values in to it. Alternatively we prepared C# DataTable with SqlDataTypes (e.g. SqlDateTime) and then retrieve data from database this using ADO.NET and C#. This time I can hold null values in the c# DataTable's DateTime type columns as coming from database.

Code:

DataTable dtDateTime = new DataTable();
dtDateTime.Columns.Add("Id", typeof(SqlInt64));
dtDateTime.Columns.Add("DateVal", typeof(SqlDateTime));
SqlConnection myConnection = new SqlConnection();
myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
SqlCommand cmd = new SqlCommand("SELECT ID, DateVal FROM Test", myConnection);
SqlDataAdapter adpSample = new SqlDataAdapter(cmd);
adpSample.Fill(dtDateTime);
Response.Write(dtDateTime.Rows[0]["DateVal"]);

I prepared sample using SQL Server database and using SQL Server classes to establish connection with SQL Server. Same code can be used with oracle Database using oracle classes it works in both databases.