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.