DAAB: Sql text query with parameters

Topics: Data Access Application Block
Jul 12, 2009 at 8:21 PM
Edited Jul 12, 2009 at 8:48 PM

Is it posible to issue a text query with parameters using DataBase class?
I can't figure out how to express the parameters placeholders in the query in an agnostic way. For example:

SELECT Name, Price FROM Products WHERE Id=(?)Id

Thanks.

 

Jul 13, 2009 at 4:27 AM

That's not supported.  If your query is for an sql server, you have to specify the correct parameter token (@).

SELECT Name, Price FROM Products WHERE Id=(@id)

 

Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com

Jul 13, 2009 at 6:35 PM

Well that's a big limitation, in these days parameters are really necesary to deal with security risks of users entries.
Maybe in a future realese could be implemented.

Thanks Sarah.

Jul 14, 2009 at 5:35 AM

You can pass parameters in SQL strings, but you have to set up the DbCommand objects explicitly and you need to write provider-specific SQL statements. Doing it in a db independent way would require us to have a TON more knowledge about provider-specific SQL than we currently do.

 

Jul 14, 2009 at 5:35 PM

Why a TON more knowledge?

You can chose a common token for example, "@" or "?", and then use a regular expression to replace the common token with the data base especific token that can be obtained from the property "DataBase.ParameterToken". This functionality can be added to "DataBase.GetSqlStringCommand()".

"DataBase.BuildParameterName()" is already doing fine with the name of the parameter.

Jul 15, 2009 at 9:05 AM

Hello,

I am facing exactly the same question and i am quiet agree with tiziano.

For now, i am trying to use the following method:

            Database db = DatabaseFactory.CreateDatabase("SAMPLE");
            string paramName = db.BuildParameterName("PARAM1");

            DbCommand command = db.GetSqlStringCommand(
                string.Format("SELECT * FROM [TABLE] WHERE [FIELD1] = {0}", paramName));

            db.AddInParameter(command, paramName, DbType.String, "VALUE");

 

 In the case of Oracle database, the "db.BuildParameterName()" should append a semi-colon to the parameter name, which is currently not the case.

Am i doing something wrong ? or is this definitively an inadvisable method ?

Thank you

Yohann