@parameter - is it portable

Guest · Mar 11, 2006

Hi;

I have my sql commands (.net 2.0) as:

cmd.CommandText = "insert into Datasource (title, description, providerType,
sqlVendor, sqlServer, sqlDatabase, sqlConnectionString, xmlFilename,
useCredentials, username, password) VALUES (@title, @description,
@providerType, @sqlVendor, @sqlServer, @sqlDatabase, @sqlConnectionString,
@xmlFilename, @useCredentials, @username, @password)";

Will this work for all Sql databases (also in selects) or do I need to use a
char other than @?

Cor Ligthert [MVP] · Mar 12, 2006

David,

AFAIK does this works for all. Be aware that by instance in OleDB the names
are not important, however. In OleDB you have to be very much attent that
yuo fill the parameters in the sequence as in the string.

I hope this was your question,

Cor

W.G. Ryan eMVP · Mar 12, 2006

David Thielen said:
Hi;

I have my sql commands (.net 2.0) as:

cmd.CommandText = "insert into Datasource (title, description,
providerType,
sqlVendor, sqlServer, sqlDatabase, sqlConnectionString, xmlFilename,
useCredentials, username, password) VALUES (@title, @description,
@providerType, @sqlVendor, @sqlServer, @sqlDatabase, @sqlConnectionString,
@xmlFilename, @useCredentials, @username, @password)";

Will this work for all Sql databases (also in selects) or do I need to use
a
char other than @?

--Dave, while it should work in the sense of not throwing an exception, I
would not say that it's portable. Let's say that in a SELECT statement you
used @title in three conditions. Because it's a named parameter, in Sql
Server you'd only need to add it to the params collection once and it's
position that you added in wouldn't matter. However in other DB's, the @
syntax isn't recognized so it would only be recognized by position and in
this case, you'd be short two parameters. As such, the code you wrote would
need to change dramatically depending on the back end db you were using.
Simply adding a condition with a parameter to any of your statements would
require you to change your code. And the code that would need to be in place
in Access very well might cause issues with Sql Server. To that end, it's
not portable. Also, there's no guarantee (that I know of) that this syntax
will work with specific providers. I haven't used it in a while but I'm
pretty sure that Oracle won't accept params with the @ symbol in them [I'm
not positive but if my memory serves me well that will cause a problem if
used with OracleClient but you can get away with it if you hit oracle
through OleDb.] Finally, SQL syntax isn't truly portable between databases,
for instance the above query would blow up in access b/c of the use of
reserved words in column names so even if the params are, the rest might not
be.

HTH,

Bill

Guest · Mar 12, 2006

Hi;

That is what I was afraid of - remember @ being a problem in Oracle too. Is
there a way to make this truly portable (short of using Hibernate)? Or do I
have to have some client specific code here?

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

W.G. Ryan eMVP said:
David Thielen said:

Hi;

I have my sql commands (.net 2.0) as:

cmd.CommandText = "insert into Datasource (title, description,
providerType,
sqlVendor, sqlServer, sqlDatabase, sqlConnectionString, xmlFilename,
useCredentials, username, password) VALUES (@title, @description,
@providerType, @sqlVendor, @sqlServer, @sqlDatabase, @sqlConnectionString,
@xmlFilename, @useCredentials, @username, @password)";

Will this work for all Sql databases (also in selects) or do I need to use
a
char other than @?

Click to expand...

--Dave, while it should work in the sense of not throwing an exception, I
would not say that it's portable. Let's say that in a SELECT statement you
used @title in three conditions. Because it's a named parameter, in Sql
Server you'd only need to add it to the params collection once and it's
position that you added in wouldn't matter. However in other DB's, the @
syntax isn't recognized so it would only be recognized by position and in
this case, you'd be short two parameters. As such, the code you wrote would
need to change dramatically depending on the back end db you were using.
Simply adding a condition with a parameter to any of your statements would
require you to change your code. And the code that would need to be in place
in Access very well might cause issues with Sql Server. To that end, it's
not portable. Also, there's no guarantee (that I know of) that this syntax
will work with specific providers. I haven't used it in a while but I'm
pretty sure that Oracle won't accept params with the @ symbol in them [I'm
not positive but if my memory serves me well that will cause a problem if
used with OracleClient but you can get away with it if you hit oracle
through OleDb.] Finally, SQL syntax isn't truly portable between databases,
for instance the above query would blow up in access b/c of the use of
reserved words in column names so even if the params are, the rest might not
be.

HTH,

Bill

Paul Gielens · Mar 12, 2006

Hi David,

Depends on what you consider to be an SQL database. For instance,
programming an OleBbCommand against an MS Access store fails since MS Access
cannot handle named parameters.

Best regards,
Paul Gielens

Visit my blog @ http://weblogs.asp.net/pgielens/
###

Triax · Mar 12, 2006

Dave,

You could use the Enterprise Library, which abstracts a lot of the code
between SQL Server and Oracle, including the different parameter needs
between the two. If you need an ORM, then EasyObjects will work on top of
the EntLib and supports both databases.

Enterprise Library
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/entlib.asp

EasyObjects.NET
http://www.easyobjects.net

Triax · Mar 12, 2006

Paul,

That's not entirely true...

The documentation for the OleDb adapter states that you should pass a '?'
for the parameter placeholder:

SELECT * FROM Employees WHERE EmployeeID = ?

but it will accept this as well:

SELECT * FROM Employees WHERE EmployeeID = @EmployeeID

However, just because you used a named parameter, doesn't mean there is an
association between that and the OleDbParameter with the same name. For the
OleDbParameter, you *must* add them to the OleDbCommand in the same order as
they are in the SQL statement, otherwise you will get some very strange
errors.

Guest · Mar 13, 2006

Hi;

This looks like it would do it EXCEPT the connection string is stored in
it's config file and I think it can't be encrypted.

Triax · Mar 13, 2006

Not true. See the section entitled Security:

http://msdn.microsoft.com/msdnmag/issues/05/07/DataPoints/

W.G. Ryan - MVP · Mar 13, 2006

Dave, when you say "Except" are you referring to the Enterprise Library? If
so, you have the source code and it's pretty striaghtforward to do. You
don't have to use the block as is. I don't have the code on me but it's a
pretty common thing to do. If I run home at lunch I'll try to shoot you a
sample.

W.G. Ryan - MVP · Mar 13, 2006

Paul:

The crux of my original post is that this is a particular pernicious problem
precisely b/c it *will* work with OleDb but it *won't* work as you'd expect
it to. Because it uses the index as opposed to the name, it will make a
substitution for you. But if you added your parameters in a different order
than they appear in the query, they behave based on the order they were
entered. Worse, if you have @BillRyan as a parameter used three times, then
even if you add a param named @BillRyan, the query will blow up. Anyone
that isn't well versed in the mechanics of it will waste a good while trying
to figure out why before realizing what the problem is. Since it doesn't
blow up, it's probably the most dangerous type of problem b/c it may yeild
correct results sometimes, wrong ones others, and throw exceptions in
others. Yet the code appears to be fine. This will confuse people at best
and cause huge problems at worst. Imagine that a new developer sees the
named params and thinks that's how it works, after all it works that way
with Sql Server. Then moves the params around. It might be in production
before anyone figures out that something 'bad' just happened.

Robert Simpson · Mar 14, 2006

Getting generic parameter names and formats isn't all that difficult.
Here's a testcase I threw together to generate parameters as well as the
parameter marker that goes into the SQL statement:

// -- CUT HERE --
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
DbProviderFactory fact;
DbConnection cnn;

// Test using SqlClient
fact = DbProviderFactories.GetFactory("System.Data.SqlClient");
cnn = fact.CreateConnection();
cnn.ConnectionString = "Data Source=(local);Initial
Catalog=master;Integrated Security=True";
cnn.Open();
Test(fact, cnn);

// Test using Access
fact = DbProviderFactories.GetFactory("System.Data.OleDb");
cnn = fact.CreateConnection();
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\\db1.mdb";
cnn.Open();
Test(fact, cnn);
}

static void Test(DbProviderFactory factory, DbConnection connection)
{
ParamBuilder builder;
DbParameter param;
string marker;

builder = new ParamBuilder(factory, connection);
param = builder.CreateParameter("param1", out marker);
Console.WriteLine(String.Format("Name: {0}, SQL statement placeholder:
{1}", param.ParameterName, marker));
}
}

class ParamBuilder
{
private DbProviderFactory _factory;
private DbCommandBuilder _builder;
private string _parameterMarkerFormat;
public ParamBuilder(DbProviderFactory factory)
: this(factory, null)
{
}
public ParamBuilder(DbProviderFactory factory, DbConnection source)
{
_factory = factory;
_builder = _factory.CreateCommandBuilder();
if (source != null)
{
using (DataTable tbl =
source.GetSchema(DbMetaDataCollectionNames.DataSourceInformation))
{
_parameterMarkerFormat =
tbl.Rows[0][DbMetaDataColumnNames.ParameterMarkerFormat] as string;
}
}
if (String.IsNullOrEmpty(_parameterMarkerFormat))
_parameterMarkerFormat = "{0}";
}

public DbParameter CreateParameter(string parameterName, out string
parameterMarker)
{
DbParameter param = _factory.CreateParameter();
param.ParameterName =
(string)typeof(DbCommandBuilder).InvokeMember("GetParameterName",
System.Reflection.BindingFlags.Instance |
System.Reflection.BindingFlags.InvokeMethod |
System.Reflection.BindingFlags.NonPublic, null, _builder, new object[] {
parameterName });

parameterMarker =
String.Format(System.Globalization.CultureInfo.InvariantCulture,
_parameterMarkerFormat, param.ParameterName);

return param;
}
}
}
// -- CUT HERE --

The console output:
Name: @param1, SQL statement placeholder: @param1
Name: param1, SQL statement placeholder: ?

So to adapt the code, throw the ParamBuilder class into your project, feed
it a DbProviderFactory and an open DbConnection, then call CreateParameter()
as many times as you need to during your SQL code generation process. It'll
format the parameters and provide you with the marker string to insert into
the SQL statement to represent the parameter.

You'll probably have to make some minor tweaks to determine whether or not
the provider supports named parameters. To do that, you'll have to examine
the DataSourceInformation metadata (I get it in the constructor for the
ParamBuilder class). I *think* the ParameterMarkerPattern is null or empty
if the provider doesn't support named parameters. Either that, or the
ParameterMarkerFormat does not contain "{0}"

Robert

Guest · Mar 14, 2006

This is great - thanks

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Robert Simpson said:
Getting generic parameter names and formats isn't all that difficult.
Here's a testcase I threw together to generate parameters as well as the
parameter marker that goes into the SQL statement:

// -- CUT HERE --
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
DbProviderFactory fact;
DbConnection cnn;

// Test using SqlClient
fact = DbProviderFactories.GetFactory("System.Data.SqlClient");
cnn = fact.CreateConnection();
cnn.ConnectionString = "Data Source=(local);Initial
Catalog=master;Integrated Security=True";
cnn.Open();
Test(fact, cnn);

// Test using Access
fact = DbProviderFactories.GetFactory("System.Data.OleDb");
cnn = fact.CreateConnection();
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\\db1.mdb";
cnn.Open();
Test(fact, cnn);
}

static void Test(DbProviderFactory factory, DbConnection connection)
{
ParamBuilder builder;
DbParameter param;
string marker;

builder = new ParamBuilder(factory, connection);
param = builder.CreateParameter("param1", out marker);
Console.WriteLine(String.Format("Name: {0}, SQL statement placeholder:
{1}", param.ParameterName, marker));
}
}

class ParamBuilder
{
private DbProviderFactory _factory;
private DbCommandBuilder _builder;
private string _parameterMarkerFormat;
public ParamBuilder(DbProviderFactory factory)
: this(factory, null)
{
}
public ParamBuilder(DbProviderFactory factory, DbConnection source)
{
_factory = factory;
_builder = _factory.CreateCommandBuilder();
if (source != null)
{
using (DataTable tbl =
source.GetSchema(DbMetaDataCollectionNames.DataSourceInformation))
{
_parameterMarkerFormat =
tbl.Rows[0][DbMetaDataColumnNames.ParameterMarkerFormat] as string;
}
}
if (String.IsNullOrEmpty(_parameterMarkerFormat))
_parameterMarkerFormat = "{0}";
}

public DbParameter CreateParameter(string parameterName, out string
parameterMarker)
{
DbParameter param = _factory.CreateParameter();
param.ParameterName =
(string)typeof(DbCommandBuilder).InvokeMember("GetParameterName",
System.Reflection.BindingFlags.Instance |
System.Reflection.BindingFlags.InvokeMethod |
System.Reflection.BindingFlags.NonPublic, null, _builder, new object[] {
parameterName });

parameterMarker =
String.Format(System.Globalization.CultureInfo.InvariantCulture,
_parameterMarkerFormat, param.ParameterName);

return param;
}
}
}
// -- CUT HERE --

The console output:
Name: @param1, SQL statement placeholder: @param1
Name: param1, SQL statement placeholder: ?

So to adapt the code, throw the ParamBuilder class into your project, feed
it a DbProviderFactory and an open DbConnection, then call CreateParameter()
as many times as you need to during your SQL code generation process. It'll
format the parameters and provide you with the marker string to insert into
the SQL statement to represent the parameter.

You'll probably have to make some minor tweaks to determine whether or not
the provider supports named parameters. To do that, you'll have to examine
the DataSourceInformation metadata (I get it in the constructor for the
ParamBuilder class). I *think* the ParameterMarkerPattern is null or empty
if the provider doesn't support named parameters. Either that, or the
ParameterMarkerFormat does not contain "{0}"

Robert

@parameter - is it portable

Guest

Cor Ligthert [MVP]

W.G. Ryan eMVP

Guest

Paul Gielens

Triax

Triax

Guest

Triax

W.G. Ryan - MVP

W.G. Ryan - MVP

Robert Simpson

Guest