Parameter placeholders in SQL queries

[Massimo]

I'm trying to write some DB-independent code using ADO.NET 2.0 and the
System.Data.Common.DbX classes, as suggested before; but I'm encountering
another weird problem.

Here's some code (let's assume the provider name and the connection string
are correct):


public void TestQuery(string providername, string connectionstring)
{

// Factory

DbProviderFactory factory = DbProviderFactories.GetFactory(provider);


// Connection

DbConnection dbconn = factory.CreateConnection();

dbconn.ConnectionString = connectionstring;


// Command Preparation

DbCommand command = dbconn.CreateCommand();

command.CommandText = "SELECT * FROM Table WHERE Column = @Val";

DbParameter param = factory.CreateParameter();

param.DbType = DbType.Byte;
param.PArameterName = "Val";

command.Parameters.Add(param);


// Execution

dbconn.Open();

command.Parameters["Val"].Value = 42;

command.ExecuteReader();

return;
}


As you can see, I'm trying to keep the code as provider-indipendent as
possibile; this is much semplified code, in the real code the provider
selection happens at program startup and is handled by some classes, the
comand preparation happens somewhat later and is handled by totally
different parts of the application, and the command execution happens at
runtime, almost everywhere; I want the last two phases to not know anything
about the specific provider used.

The problem: this code works only with SQL Server, which uses "@Val" as a
parameter placeholder. Oracle uses ":Val", and I'm told other providers use
even different characters ("?").


How can this be solved without cluttering the command building with
provider-based IFs and CASEs?


Thanks


Massimo

P.S.
I know I could write some code like

public string GeneratePlaceHolder(string paramname);

and use it when building commands, but I'd like something simpler (and
hopefully less ugly) to use.

 

[Mary Chipman [MSFT]]

Unfortunately, the factory model in 2.0 does not do anything for you
in parameterized query scenarios when you don't know what provider
you're using ;-(

You're going to have to branch in your code or provide your own
framework (which is probably more work than you want to do).

--Mary

 

[Massimo]

Unfortunately, the factory model in 2.0 does not do anything for you
in parameterized query scenarios when you don't know what provider
you're using ;-(

You're going to have to branch in your code or provide your own
framework (which is probably more work than you want to do).

Actually, I quickly wrote some methods to handle this issue, and they're
(more or less) working; this code accepts queries in SQL Server format
(using '@name' for parameters) and rewrites them in order to work wih
different providers; it builds DbCommands on the fly with any number of
parameters.
It uses some members from the class it belongs to, but it should be quite
clear:

----------
protected DbCommand CreateCommand(string query,params DbType[] parameters)
{
DbCommand command = dbconn.CreateCommand();

string[] sections = query.Split('@');

string[] parameternames = new string[sections.Length - 1];

for(int p = 0;p < parameternames.Length;p++)
{
int i;

for(i = 0;i < sections[p + 1].Length;i++)
if(!char.IsLetterOrDigit(sections[p + 1]))
break;

parameternames[p] = sections[p + 1].Substring(0,i);
}

switch(dbtype)
{
case DBTypes.SQL:
command.CommandText = query;
break;
case DBTypes.Oracle:
command.CommandText = query.Replace('@',':');
break;
case DBTypes.MySQL:
// Not implemented yet
break;
}

for(int i = 0;i < parameternames.Length;i++)
{
DbParameter parameter = command.CreateParameter();

parameter.ParameterName = parameternames;
parameter.DbType = parameters;

command.Parameters.Add(parameter);
}

return(command);
}
----------

It can obviously be improved, but it's ok for my needs.

Howewer, it would nice if the framework handled this issue; it should be its
job, since ADO.NET 2.0 tries to provide an abstract data access layer.


Massimo

 

[Mary Chipman [MSFT]]

I agree with your conclusion -- it sure would be nice. Just make sure
you guard against SQL injection attacks

--Mary

Unfortunately, the factory model in 2.0 does not do anything for you
in parameterized query scenarios when you don't know what provider
you're using ;-(

You're going to have to branch in your code or provide your own
framework (which is probably more work than you want to do).

Actually, I quickly wrote some methods to handle this issue, and they're
(more or less) working; this code accepts queries in SQL Server format
(using '@name' for parameters) and rewrites them in order to work wih
different providers; it builds DbCommands on the fly with any number of
parameters.
It uses some members from the class it belongs to, but it should be quite
clear:

----------
protected DbCommand CreateCommand(string query,params DbType[] parameters)
{
DbCommand command = dbconn.CreateCommand();

string[] sections = query.Split('@');

string[] parameternames = new string[sections.Length - 1];

for(int p = 0;p < parameternames.Length;p++)
{
int i;

for(i = 0;i < sections[p + 1].Length;i++)
if(!char.IsLetterOrDigit(sections[p + 1]))
break;

parameternames[p] = sections[p + 1].Substring(0,i);
}

switch(dbtype)
{
case DBTypes.SQL:
command.CommandText = query;
break;
case DBTypes.Oracle:
command.CommandText = query.Replace('@',':');
break;
case DBTypes.MySQL:
// Not implemented yet
break;
}

for(int i = 0;i < parameternames.Length;i++)
{
DbParameter parameter = command.CreateParameter();

parameter.ParameterName = parameternames;
parameter.DbType = parameters;

command.Parameters.Add(parameter);
}

return(command);
}
----------

It can obviously be improved, but it's ok for my needs.

Howewer, it would nice if the framework handled this issue; it should be its
job, since ADO.NET 2.0 tries to provide an abstract data access layer.


Massimo

 

[Massimo]

I agree with your conclusion -- it sure would be nice. Just make sure
you guard against SQL injection attacks

I'll call that code from *my* application, I don't think I'me going to crack
it ;-)

By the way, do you know some better way to tokenize a text string? I
Split()ted it at every '@' and then manually terminated the substrings at
the first non-letter-or-digit character, but this seems quite horrible code
to me :-/


Massimo