StrataFrame Forum

Generic Data Access for Custom Methods

http://forum.strataframe.net/Topic14816.aspx

By Ertan Deniz - 3/12/2008

What is the best way to write custom methods to be generic for data access ? Plus, It must not have a conflict with the framework ?

FillDataTable method of business object accept System.Data.Common.DbCommand. I use this method to run commands.

Do I handle provider specific properties "SqlCommand and SqlParameter", "OracleCommand and OracleParameter" with my coding or any other alternative exists in the framework. 

By Trent L. Taylor - 3/12/2008

It sounds like you are trying to program your application so that you can swap between SQL and Oracle.  The first thing that you need to do is create a base business object class that inherits from our BusinessLayer.  Then all of your business objects will inherit from this base business object.

Once this is done, you will need to have some way of determining what database type is being run within the BO.  You could create a property on the base BO if you have the need for this to be determined on a per BO basis, otherwise, just create a shared property in a class that indicates what you are using.

Public Enum DatabaseType As Integer
    SQLServer = 0
    Oracle = 1
End Enum

Public Class MyBaseBo
    Inherits MicroFour.StrataFrame.Business.BusinessLayer

    Private Shared _DatabaseQueryType As DatabaseType = SQLServer

    Public Shared Property DatabaseQueryType As DatabaseType
        Get
           Return _DatabaseQueryType
        End Get
        Set (Byval value As DatabaseType)
            _DatabaseQueryType = value
        End Set
    End Property

    Private Function ConvertSqlToOracleCommand(Byval cmd As SqlCommand) As OracleCommand
       '-- Convert the SqlCommand into an Oracle Command
    End Function

    '-- Override the base FillDataTable and add a test to see if the command should be converted to Oracle
    Public Overrides Sub FillDataTable(Byval cmd As DbCommand)
        If MyBoBase.DataQueryType = Oracle Then cmd = ConvertSqlToOracleCommand(cmd)

        MyBase.FillDataTable(cmd)
    End Sub
End Class

When you develop your application, you should always code for a single database type.  This makes life much easier from a development stand-point.  You can then convert from your default command into the exception.  For example, you can see in the pseudo class above that I overwrote the FillDataTable and added a test to see whether I needed to convert the SqlCommand (which is what I would be expecting from my code) into an Oracle command.

By Ertan Deniz - 3/12/2008

Thanks for the solution. I 'll will continue with the way you have explained for the command object. But what about the parameter objects ?

I'm trying to write a query class (But complex query text is supplied with XML documents) with parameters. The paramters will be extracted with parsing the query. For the parameters collection, I will need provider specific parameter class.

What should I do ? Should I follow same way as with command object ?

By StrataFrame Team - 3/13/2008

Yes, the SqlCommand.Parameters collection only accepts SqlParameter objects, while the OracleCommand.Parameters collection only accepts OracleParameter objects (and so forth: OleDbCommand.Parameters -> OleDbParameter, etc.).

Other than chaning the object type, you can generally use the same parameter.Value and stick with setting the parameter.DbType property to define the parameter's database type.  There are very few cases where you will have to set the provider-specific type (SqlParameter.SqlDbType or OracleParameter.OracleType).  The DbType is shared by all providers.

Also, when you're creating/converting parameters, remember that some commands use ? as the placeholder for parameters and go off of the order of the parameters, while others use @name or :name for parameters and link off of the name of the parameter.

If you're only going between Oracle and SQL Server, then you'll just have to swap the @ and : since they both use named parameters.  But, if you're using DB2 or Access (or other OLE DB or ODBC connections), then you'll have to ensure the order of the parameters matches the use of them within the command and use ? as the placeholders.