Converting Strings to DateTimes for Use in ADO.NET Parameterized Queries using the Data Access Applications Block 3.0

• Download source files - 1 Kb
• Download demo project - 16 Kb

Introduction

Recently, I needed to pass a start date and an end date to SQL server and return the results. The start and end dates would be coming as user input and would be validated on the client side as formatted strings in the format: mm/dd/yyyy. Passing these strings to SQL Server turned out to be a little more of a challenge than I anticipated. If I could pass these strings directly to SQL Server as part of a string query, this would be simple. I could just have:

sql = "SELECT * FROM Orders WHERE OrderDate BETWEEN '" + startdate + " ' AND '" + enddate + "'"; 

However, since the code was coming from a user, I did not want to do this because of SQL injections attacks, etc. As a result, I needed to create parameters for my query instead. In this particular case, I could not use a stored procedure so I used a parameterized query.

Below is the code I used to accomplish this. It also includes an example of how to use the version 3.0 of the Data Access Application Block, which allows for complete database independence (unlike version 2.0). If you have not checked that project out, it is very useful. You can reach it at:

http://www.gotdotnet.com/Workspaces/Workspace.aspx?id=c20d12b0-af52-402b-9b7c-aaeb21d1f431

I struggled for a while when I tried to figure out how to do a parameterized query using the Data Access Application Blocks so hopefully this code will serve as an example for that also. To do this, you first have to tack on 12:00AM to the start date and 11:59PM to the end date. This makes sure you get the entire day. If you are using stored procedures you can do this inside the procedure.

So here is the code:

using System;
using GotDotNet.ApplicationBlocks.Data;
using System.Data; 
public class Converter {
 public static void Main( string[] args ) {
  if( args.Length != 2 ) {
   Console.WriteLine( "Please supply a start and end date" );
   return;
  }
  IDataReader rdr = GetTheDates( args[0], args[1] );
   
  while( rdr.Read() ) {
   Console.WriteLine( "OrderId: {0} - CustomerId: {1}", rdr[ "OrderId" ], rdr[ "CustomerId" ] );
  }
 } 
 public static IDataReader GetTheDates( string startdate, string enddate ) {
  string dbcon = "Data Source=localhost; Integrated Security=SSPI;Initial Catalog=northwind";
  SqlServer s = new SqlServer();
  startdate += " 12:00AM";
  enddate += " 11:59PM";
  IDataParameter[] p = new IDataParameter[2];
  p[0] = s.GetParameter( "@StartDate", DateTime.Parse( startdate ));
  p[1] = s.GetParameter( "@EndDate", DateTime.Parse( enddate ));
  string sql = "Select * From Orders WHERE OrderDate BETWEEN @StartDate AND @EndDate";
  return s.ExecuteReader( dbcon, CommandType.Text, sql, p );
 }
}
// COMPILE USING: csc string_datetime_convert.cs /r:GotDotNet.ApplicationBlocks.Data.dll

I hope this article was helpful. It is written for beginners in C# who are just learning how to connect to databases. Again, I would highly recommmend using the Data Access Application Block instead of the ADO.NET functions directly. The DAAB is very simple to use and reduces that amount of code drastically. This is my first article here so any suggestions about the content, formatting, would be appreciated.