formatting dates in queries

[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.

 

[Robert Simpson]

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

 

[Cor Ligthert [MVP]]

Zen,

Both SQLserver and Jet works better with parameters.

Samples
SQLClient
http://www.vb-tips.com/dbpages.aspx?ID=2e452783-527a-402a-b37a-ae802ee71c86

OleDB (Jet)
http://www.vb-tips.com/dbpages.aspx?ID=550279ec-6767-44ff-aaa3-eb8b44af0137

Factory have a look at the update command (The sample is not so new anymore
I should change the text)
http://www.vb-tips.com/dbpages.aspx?ID=8c3dc2d7-1232-4dd1-817e-22eaaebb2723

I hope this helps,

Cor

 

[ZenRhapsody]

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]

Because GetParameterName() is a protected member of the DbCommandBuilder
class and can't be called directly without using reflection.

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>

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

 

[BlackWasp]

Have you tried YYYY-MM-DD format?

 

[ZenRhapsody]

'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?

 

[BlackWasp]

Ah yes! I just realised that in one of my customer sites I use YYYY-MM-DD
for Jet but with cdate(). My mistake.