formatting dates in queries

  • Thread starter Thread starter ZenRhapsody
  • Start date Start date
Z

ZenRhapsody

BACKGROUND INFO:
I'm using the common DbConnection object in my application because the user
can have data hosted in Excel, Access, SQL Server, or Oracle. I'm using the
common DbProviderFactories.GetFactory(providerName);
to get the appropriate factory object for creating a specific connection
object.

All well and good.

So my problem comes in when trying to write queries that have dates in the
where clause. Using JET for Excel and Access, I have to write something
like "WHERE dte > #5/15/2006#" while in SQL Server it's "WHERE dte >
'05/15/2006' "

(I know regional settings for cultures are important, and am planning to
handle them)

MY QUESTION IS THIS:
Anyone have any good guidance on how to decide at runtime which format to
use? Or is there any format that works for all database providers? I have
a variable set for format which I am currently setting when the connection
string is used to build the connection. This code selects XLS, MDB, or SQL.
But, if a user specifies something else for 'providerName' that works on his
machine (let's say some new database vendor writes a NET 2.0 data provider
that I don't have), I won't know how to handle that.
 
You need a parameterized query. Trouble is, you have to accomodate every
different provider's picky parameter preferences, which is no paltry
process, plebe! Parameter parsing probes into private procedures in each
provider's CommandBuilder. Pretty easy to produce the proper parameters
using reflection, however.

Go snag the ParamBuilder class I wrote in this very newsgroup. Here's the
URL:
http://groups.google.com/group/micr...7f58?lnk=st&q=&rnum=12&hl=en#519e9efb82fc7f58

(watch for wordwrap on that!)

Once you have a class that can give you either a named or unnamed parameter,
properly formatted and customized for the provider you're using, you can now
write a parameterized database-independent query:

// Assumes "factory" is a DbProviderFactory, and "connection" is an existing
DbConnection object
string marker;
ParamBuilder builder = new ParamBuilder(factory, connection);
DbParameter param = builder.CreateParameter("param1", out marker);

DbCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT FOO FROM BAR WHERE dte > " + marker;
cmd.Parameters.Add(param);
param.Value = DateTime.Parse("05/15/2006");

cmd.ExecuteXXX();

Robert Simpson
Programmer at Large
 
Thanks for the info.. the situation s*cks...but that's life.

Question - why do you need to use InvokeMember?

<QUOTE> param.ParameterName =
(string)typeof(DbCommandBuilder).InvokeMember("GetParameterName",
System.Reflection.BindingFlags.Instance |
System.Reflection.BindingFlags.InvokeMethod |
System.Reflection.BindingFlags.NonPublic, null, _builder, new object[] {
parameterName });
</QUOTE>
 
Because GetParameterName() is a protected member of the DbCommandBuilder
class and can't be called directly without using reflection.

ZenRhapsody said:
Thanks for the info.. the situation s*cks...but that's life.

Question - why do you need to use InvokeMember?

<QUOTE> param.ParameterName =
(string)typeof(DbCommandBuilder).InvokeMember("GetParameterName",
System.Reflection.BindingFlags.Instance |
System.Reflection.BindingFlags.InvokeMethod |
System.Reflection.BindingFlags.NonPublic, null, _builder, new object[] {
parameterName });
</QUOTE>




Robert Simpson said:
You need a parameterized query. Trouble is, you have to accomodate every
different provider's picky parameter preferences, which is no paltry
process, plebe! Parameter parsing probes into private procedures in each
provider's CommandBuilder. Pretty easy to produce the proper parameters
using reflection, however.

Go snag the ParamBuilder class I wrote in this very newsgroup. Here's
the URL:
http://groups.google.com/group/micr...7f58?lnk=st&q=&rnum=12&hl=en#519e9efb82fc7f58

(watch for wordwrap on that!)

Once you have a class that can give you either a named or unnamed
parameter, properly formatted and customized for the provider you're
using, you can now write a parameterized database-independent query:

// Assumes "factory" is a DbProviderFactory, and "connection" is an
existing DbConnection object
string marker;
ParamBuilder builder = new ParamBuilder(factory, connection);
DbParameter param = builder.CreateParameter("param1", out marker);

DbCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT FOO FROM BAR WHERE dte > " + marker;
cmd.Parameters.Add(param);
param.Value = DateTime.Parse("05/15/2006");

cmd.ExecuteXXX();

Robert Simpson
Programmer at Large
Click to expand...
Click to expand...
 
'YYYY-MM-DD' works in SQL Server; does not work in JET prior to Access 2003.
JET pre 2003 still requires the funky '#' delimiter!

I have no idea about Oracle.... and does Oracle correctly understand this
format in all countries/languages/regional settings?
 
Ah yes! I just realised that in one of my customer sites I use YYYY-MM-DD
for Jet but with cdate(). My mistake.
 
Back
Top