Cross-database parameterized SQL statement

Topics: Data Access Application Block
Aug 3, 2010 at 8:47 AM
Edited Aug 3, 2010 at 9:35 AM

Hi,

I'm trying to execute parameterized sql statements against both SQL Server and Oracle (using the latest 5.0 version).
All the examples I've found are either using stored procedures (which I'm not, for reasons we won't go into here), or hard-coded SQL with the SQL Server specific token "@" in front of the parameter within the SQL statement, like this:

where somecolumn = @somevalue

I know that I don't have to specify database specific "tokens", like "@" and ":", in front of the parameter names in call to "AddInParameter", but what about the actual SQL statement?

One solution would of course be to do a search/replace operation of the SQL statement, to replace "@" with ":" if I'm using Oracle, but seeing that parameter names are handled transparently in "AddInParameter", I'd like to think there existed a generic approach for custom SQLs as well.

Please enlighten me.

Aug 3, 2010 at 11:14 AM

Hi,

First of, I would like to say that in the AddInParameter method, only the "@" token are being replaced which is specific to SQLDatabase only while the ":" token of OracleDatabase will not. This is so far from what I know, documented and seen in the source code. 

As for the Sql statement tokens, yes I believe the tokens are still required. Though I'm not familiar with Oracle I believe it will still require the ":" token in your hardcoded sql statement and same with SQL's "@" token since DAAB doesn't do any command text manipulation/insertion. It only utilize the command text that have been passed from the GetSqlStringCommand.

Unfortunately there's no any generic approach available in the current DAAB that will be able to handle replacing of tokens in the sql statement. The approach you have mentioned for me will be the quick and easy approach. Another approach I can think of is updating the DAAB source code to be able to insert speific token depending on the database provider. HTH

Gino Terrado
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

 

Aug 4, 2010 at 9:06 AM

For the record, the AddInParameter does insert the required token for both SQL Server and Oracle. This is confirmed by my testing.

I've implemented an algorithm for handling the tokens in SQL statements, by adding a couple of extension methods to the DAAB (not wanting to risk altering the source code of DAAB itself).

   /// <summary>
   /// Contains extension methods for the Enterprise Library Data Access Application Block
   /// </summary>
   public static class DataAccessApplicationBlockExtensions
   {
      #region Database class extensions

      /// <summary>
      /// Gets a database specific token to be used as prefix for command and ad-hoc sql parameters.
      /// Will return "@" for SQL Server and ":" for Oracle.
      /// </summary>
      /// <param name="db">"Implicit" parameter defining which class to extend</param>
      /// <returns>A string token</returns>
      public static string GetDatabaseToken(this Database db)
      {
         string token = string.Empty;

         if (db is SqlDatabase)
         {
            token = "@";
         }
         else if (db is OracleDatabase)
         {
            token = ":";
         }

         return token;
      }

      /// <summary>
      /// Gets a DbCommand from an SQL query statement, by first preparing it according to the current database type.
      /// </summary>
      /// <remarks>
      /// Assumes that the default SQL statement uses the SQL Server token.
      /// </remarks>
      /// <param name="db">"Implicit" parameter defining which class to extend</param>
      /// <param name="query">SQL query</param>
      /// <returns>A command object with a prepared SQL query</returns>
      public static DbCommand GetSqlStringCommandAgnostic(this Database db, string query)
      {
         string token = db.GetDatabaseToken();

         // TODO: Possibly not perform this if not needed
         query = query.Replace("@", token);

         return db.GetSqlStringCommand(query);
      }

      #endregion Database class extensions
   }

 

So, instead of doing like this:

using (DbCommand command = db.GetSqlStringCommand(select))

I do this:

using (DbCommand command = db.GetSqlStringCommandAgnostic(select))
Aug 5, 2010 at 3:06 AM

Very nice approach indeed. Thanks a lot for sharing mgroetan :-)

Gino Terrado
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com