Using parameter for SQL Command

Topics: Data Access Application Block
Jun 8, 2007 at 1:19 PM
Hi,
For DAAB I need to use DBParameter and add it on a SQL command (not a SP).

For example I need to write a command like "SELECT ID FROM Products WHERE Name = @ParName". So what I need is a way on how I can specify a parameter in to SQL string.

My solution to sort ou this problem will be :
1) use @ character in the SQL string in order to find parameters.
2) test database factory to understand which db vendor we use
3) replace @ with the specific DB vendor (e.g. ":" for Oracle)
4) test database factory to understand which db vendor we use
5) create the "casted" DB Parameter.
6) create the command
7) add parameter to the command

Is correct ?
May be I can use the parameter features the DAAB use for SP ?

Thanks
Luca

Thanks
Luca
Jun 9, 2007 at 3:15 PM
The DAAB already does this for you.

You can do (Psuedocode):

Database database = DatabaseFactory.CreateDatabase();
 
DbCommand command = database.GetSqlStringCommand("SELECT ID FROM Products WHERE Name = @ParName");
database.AddInParameter(command,"ParName",...);

Note I did not specify the @ in the AddInParameter Command. The DAAB will automatically add this for you when using SqlServer. If Oracle, it would use the symbol appropriate for Oracle, etc.

The only thing that gives away you are using SQL Server is that you hardcoded the @ in the SQL String.

Regards,

Dave

_________________________________

David Hayden
Microsoft MVP C#
Jun 11, 2007 at 8:37 AM
Thanks David,
now I understand that I can use AddInParameter for SQLString command also.

So, for Oracle I need a command like this :
Database database = DatabaseFactory.CreateDatabase();
 
DbCommand command = database.GetSqlStringCommand("SELECT ID FROM Products WHERE Name = :ParName");
database.AddInParameter(command,"ParName",...);

So the AddInParameter may be used for Oracle also and the "ParName" and "DBType" will be automatically mapped.

I will try it and, in case, I will posts a new message...

Thanks again.

Regards,
Luca
Jun 11, 2007 at 1:52 PM
You add the DbType as one of the parameters in the AddInParameter Method, but yes, the database class will automatically prefix your parameter name with the prefix used for your database.

Regards,

Dave

______________________________

David Hayden
Microsoft MVP C#
Jun 11, 2007 at 4:16 PM
Dave,
I include DbType in my post because normally I map all the types (DbType) into specific types (eg. OracleType or SqlDbType).

Now I check that DAAB maps GUID only and use the "default" for the other types.

What will be good in a future release is the possibility to add custom mappings for db type.

For example in my project that must runs at least on Oracle, SQLServer and DB2 I can't use boolean on DB Server side but small integer...

Regards,
Luca