Re: formatting dates in queries

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

"ZenRhapsody" <nospam@xxxxxxxxxxx> wrote in message
news:ufYUMSmuGHA.4544@xxxxxxxxxxxxxxxxxxxxxxx
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" <rmsimpson@xxxxxxxxxxxxxxx> wrote in message
news:eWvAOnkuGHA.1512@xxxxxxxxxxxxxxxxxxxxxxx
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/microsoft.public.dotnet.framework.adonet/browse_thread/thread/deb2bec6610b7f16/519e9efb82fc7f58?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


"ZenRhapsody" <nospam@xxxxxxxxxxx> wrote in message
news:OAw8zSkuGHA.2448@xxxxxxxxxxxxxxxxxxxxxxx
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.








.

Relevant Pages

  • Re: formatting dates in queries
    ... I'm using the common DbConnection object in my application because the ... to get the appropriate factory object for creating a specific connection ... Anyone have any good guidance on how to decide at runtime which format to ... data provider that I don't have), I won't know how to handle that. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: formatting dates in queries
    ... System.Reflection.BindingFlags.NonPublic, null, _builder, new object{ ... to get the appropriate factory object for creating a specific connection ... Anyone have any good guidance on how to decide at runtime which format to ... vendor writes a NET 2.0 data provider that I don't have), ...
    (microsoft.public.dotnet.framework.adonet)
  • Can not make a network connection using a local user context. The current user, SMSCCMBootAcct&
    ... NAL- CreatePath, wrong provider: ... NAL- Attempting to release a device connection. ... NAL- A deviceless connection has not yet been made. ...
    (microsoft.public.sms.admin)
  • RE: web parts and a database
    ... service's provider can not correctly establish ... connection to the underlying database. ... services(include membership, rolemanager, profile, personalization ...) ... Are you creating and developing the web project through HTTP/IIS server ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Custom membership provider problem
    ... user wizard and someone said I should switch to custom membership provider. ... >>An error has occurred while establishing a connection to the server. ... >>Boolean& failoverDemandDone, String host, String failoverPartner, String ...
    (microsoft.public.dotnet.framework.aspnet)