Extending GetStoredProcommandWithSourceColumns to support SAP-HANA and adapter.Update(dataTable) method

Topics: Data Access Application Block, Enterprise Library Core
Aug 21, 2012 at 6:14 AM
Edited Aug 21, 2012 at 6:15 AM

Hi there,

We are extendting an exiting application to support SAP-HANA database. The application currently supports Oracle and SQL Server.

We are using Adapator's Update method to update the database. Insert command for Adaptor is prepared by using Database.GetStoredProcCommandWithSourceColumns methods.

Above works perfect for Oracle and SQL Server both, but does not work with SAP-HANA database, throws following exception.

Parameter discovery is not supported for connections using GenericDatabase. You must specify the parameters explicitly, or configure the connection to use a type deriving from Database that supports parameter discovery.

In my case we have an existing product which works with SQL and Oracle by using "Database.GetStoredProcCommandWithSourceColumns" and then calling adapter.Update(dataTable). I can not change existing code therefor we opt for extending the method GetStoredProcCommandWithSourceColumns of Database class of Enterprise Library.

Code 

Private Function GetInsertLineItemDetailsCommand() As DbCommand
Return Database.GetStoredProcCommandWithSourceColumns("StoreProcInsertLineItmDtl", "HeaderID", "DetailID", "DetailValue1", "DetailValue2")
End Function

Protected Function UpdateDataTable(ByVal dataTable As DataTable, ByVal insertCommand As DbCommand, ByVal updateCommand As DbCommand, ByVal deleteCommand As DbCommand) As Integer
Dim rowCount As Integer

Using adapter As DbDataAdapter = Database.GetDataAdapter()
                Dim explicitAdapter As IDbDataAdapter = CType(adapter, IDbDataAdapter)
                If Not insertCommand Is Nothing Then
                    insertCommand.Connection = dbContext.Connection
                    insertCommand.Transaction = dbContext.Transaction
                    insertCommand.CommandTimeout = 0
                    explicitAdapter.InsertCommand = insertCommand
                End If

                If Not updateCommand Is Nothing Then
                    updateCommand.Connection = dbContext.Connection
                    updateCommand.Transaction = dbContext.Transaction
                    updateCommand.CommandTimeout = 0
                    explicitAdapter.UpdateCommand = updateCommand
                End If

                If Not deleteCommand Is Nothing Then
                    deleteCommand.Connection = dbContext.Connection
                    deleteCommand.Transaction = dbContext.Transaction
                    explicitAdapter.DeleteCommand = deleteCommand
                End If
                rowCount = adapter.Update(dataTable)
            End Using

            Return rowCount

End Function

'Now calling the update Table

'MyDS is a Typed Data set and dtItemDetails contains around 10K records and based on rowstate Stored Procedure is called  

Public Sub SaveLineItemDetails(ByVal dtLineItemDetails As MyDS.tblLineItemDetailsDataTable)
            UpdateDataTable(dtItemDetails, GetInsertLineItemDetailsCommand, GetInsertupdateItemDetailsCommand, Nothing)
        End Sub

our requirement is to extend GetStoredProcCommandWithSourceColumns to work consistently for all the databases supported by our Product, as product is stable for SQL Server and Oracle hence no scope in code change above Data Access Layer's core.

Therefore we have created new MyDatabase class and created new implementation for GetStoredProcCommandWithSourceColumns.

 Public Class MyDatabase
        Inherits GenericDatabase
        Sub New(ByVal constr As String, ByVal dbprovider As DbProviderFactory)
            MyBase.New(constr, dbprovider)
        End Sub
        Public Shadows Function GetStoredProcCommandWithSourceColumns(ByVal stringSP As String, ByVal ParamArray ParamArrays As String()) As DbCommand
            If "DataBaseNameFromConfigFile" = "SAPHANA" Then
                'Populate Parameter one by one
                Dim dbcommand As DbCommand
                dbcommand = GetStoredProcCommand(stringSP)
                Dim odbcparam As Odbc.OdbcParameter
                For Each Item As String In ParamArrays
                    odbcparam = New Odbc.OdbcParameter()
                    odbcparam.ParameterName = Item
                    odbcparam.SourceColumn = Item
                    'odbcparam.Size=? 
                    'odbcparam.Direction=?
                    dbcommand.Parameters.Add(odbcparam)
                Next
                Return dbcommand
            Else
                Return MyBase.GetStoredProcCommandWithSourceColumns(stringSP, ParamArrays)
            End If
        End Function
    End Class


Need help on how to get other required parameters value like ParameterDirection, DataType, Siz in above function to work this correctly?

Aug 22, 2012 at 4:58 AM

For SQL Server a stored procedure call is made to determine the result set information.

I'm not familiar with SAP-HANA so I'm not sure if that is possible.  If it's not, then you would have to manually supply that information either in code or configuration. I would consider creating a custom configuration information that the custom database class could use to discover the parameter information.

Instead of hiding GetStoredProcCommandWithSourceColumns you could also consider overriding DeriveParameters.

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com