locked
Getting neutral parameterized queries in my application framework RRS feed

  • Question

  • Ok,  I've been digging around looking for a solution to this one for a couple of days now.  Maybe someone in this forum has already done this.  What I'm trying to do is get to a completely neutral set of SQL in a new pluggable framework I designed.  The latest problem I'm dealing with is parameterized queries.  It's seems like such an easy problem to solve but I can't find any good answers.  Here's an example

    SQL Server Query:  "INSERT INTO MY_TBL VALUES(@parm1, @parm2, @parm3)"

    Oracle Query: "INSERT INTO MY_TBL VALUES(:parm1, :parm2, :parm3)"

    DB2 Query: "INSERT INTO MY_TBL VALUES(@parm1, @parm2, @parm3)"

    Hopefully the issue is obvious,  I don't want to have any differences between the queries (other than a connection string which I can configure centrally) and I can't seem to find a universal parameter prefix (the "@" vs ":").  I am using the Enterprise Library for .NET 1.1 and I don't see anything in there either that insulates me from this issue.

    Any help/ideas would be much appreciated.  I know if I must I can just keep multiple versions of the queries and do a dynamic switch based on the DB platform but that just seems so silly for something as simple as the parameter prefix.

    Thanks!

    Thursday, February 8, 2007 11:57 AM

All replies

  • Here is my immediate thought.

    For each database you have to create a specialized Command and Parameters ex:OracleCommand, SqlCommand etc...
    To help solve your main issue, you need to create your own set of classes, let's call then CommandWrapper, ParameterWrapper,
    make them have similar interface to the standard ones for consistency; may be u would like to implement IDbCommand.

    Now choose your favourite parameter sign whether @, :, or anything you like.
    Continue your coding normally. When calling CommandWrapper.ExecuteDataReader or ExecuteNonQuery
    the method should check the connection whether it is OracleConnection, SqlConnection, based on the connection type you should
    scan the CommandText and Parameters and change the parameter sign according the expected one (may be RegEx will be handy in here). It is better to use the Strategy pattern to implement this phase.

    here's a pseudo code

    class CommadWrapper{

      public IDataReader ExecuteDatareader(){
          if(Connection is OracleConnection){
                  OracleCommand oraCmd= new OracleCommand();
                  oraCmd.CommandText = ToOracleCommadText(CommadText);
                  // scan all parameters too
                 // oraParam.ParameterName = ToOracleParameterName(param.ParameterName)
                 return oraCmd.ExecuteDataReader();

          }
          // same for MsSql

      }

    }

     Careful when using neutral types for parameters, sometimes you don't really have one to one mapping. I once had problem when using DbType.String instead of OracleType.Varchar in OracleParameter, I donno if it was a bug.

    Hope this is was helpful

    regards

    Thursday, February 8, 2007 6:41 PM
  • Thanks for the response,  this is already the design implemented in the Enterprise Library and I guess I sort of assumed this would work this way.  I guess that makes this an enterprise library question.  I'll dive into the code and find out why this doesn't seem to be working.

    Thursday, February 8, 2007 9:22 PM