Home > Uncategorized > Handling null values in DataTable’s DateTime columns using “SqlDateTime” Type

Handling null values in DataTable’s DateTime columns using “SqlDateTime” Type

One the most important aspects of writing this article to share knowledge and making open and easily available so that other can be utilized.

Problem

While working in one of our project we came across a problem.

  1. Database tables contains null data in date and time columns
  2. In a C# code a “DataTable” is prepared to retrieve data from Database using ADO.net
  3. If In database query Date type column is null then c# “DataTable’s” DateTime” type column hold  minimum date by default ({1/1/0001 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

This time I can hold null values in the c# DataTable’s DateTime type columns as coming from database.

Codebase

DataTable dtSample = new DataTable();

dtSample.Columns.Add(“Id”, typeof(SqlInt64));

dtSample.Columns.Add(“StartDate”, typeof(SqlDateTime));

OracleConnection myConnection = new OracleConnection();

myConnection.ConnectionString = ConfigurationManager.ConnectionStrings[“MyConnection”].ToString();

OracleCommand cmd = new OracleCommand(“SELECT  ID,  StartDate FROM Sample”, myConnection);

OracleDataAdapter adpSample = new OracleDataAdapter(cmd);

adpSample.Fill(dtSample);

Response.Write(dtSample.Rows[0][1]);

I prepared sample using Oracle database and using Oracle classes to establish connection with oracle

Same code can be used with Sql Database using Sql classes it works in both databases i tested in both databases.

Refrences

Please refer to link for more information about SqlDateTime structure

http://msdn.microsoft.com/en-us/library/system.data.sqltypes.sqldatetime.aspx

Thanks for reading article I hope this find helpful.

Thank you

Advertisements
Categories: Uncategorized Tags: , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: