Click here to Skip to main content
Click here to Skip to main content
Go to top

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

, 23 Oct 2004
Rate this:
Please Sign up or sign in to vote.
This article shows you how to take a date string and use it in a parameterized sql server query

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

ritzcoder

United States United States
No Biography provided

Comments and Discussions

 
GeneralSource Code PinmemberTony Bermudez24-Sep-04 6:10 
GeneralRe: Source Code Pinmemberritzcoder23-Oct-04 9:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140921.1 | Last Updated 23 Oct 2004
Article Copyright 2004 by ritzcoder
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid