Carlos Borau Carlos Borau - 4 months ago 17
Vb.net Question

IDataAdapter and basic commands (insert, update, delete)

I have a function to bind data from a SQL database to a gridview. So far it worked well using this:

Dim dad As SqlDataAdapter
Dim bs As BindingSource
Dim dtb As DataTable
Private Sub getDataSQL(ByVal selectCommand As String, ByVal connectionString As String)
dtb = New DataTable
Dim cnn As New SqlConnection(connectionString)
cnn.Open()
dad = New SqlDataAdapter(selectCommand, cnn)
Dim basiccommands = New SqlCommandBuilder(dad)
dad.InsertCommand = basiccommands.GetInsertCommand
dad.UpdateCommand = basiccommands.GetUpdateCommand
dad.DeleteCommand = basiccommands.GetDeleteCommand
dad.Fill(dtb)
bs = New BindingSource
bs.DataSource = dtb
navigator_main.BindingSource = bs 'this is a binding navigator
dgv_main.DataSource = bs 'this is the main datagridview
cnn.Close()
End Sub


When the user adds/edits/deletes a row I call:

bs.EndEdit()
dad.Update(dtb)


Now I must reuse the getData function to populate the gridview by connecting to different types of databases (mainly MySQL, ODB, SQL). For that I created an interface that allows reusing a lot of functions by indicating the type of connection:

Public Interface IDataBaseHelper
Function openConnection(ByVal conn_string As String) As Boolean
Function executeQuery(ByVal query As String) As IDataReader
Function executeQuery(ByVal command As IDbCommand) As IDataReader
Function executeNonQuery(ByVal query As String) As Integer
Function createSQLCommand(ByVal query As String) As IDbCommand
Function getDataAdapter(ByVal query As String) As IDataAdapter
Sub closeConnection()
End Interface


The new function looks like this:

Private mBindingSource As New BindingSource()
Private mDataAdapter As IDataAdapter
Private mDataSet As DataSet
Private Sub getData(ByVal selectCommand As String, ByVal connectionString As String, ByVal connectionType As String)
'Connection types are defined in FDataBaseHelper: FDataBaseHelper.SQLSERVER_ .MYSQL_ .ODBC_
Dim cnn = FDataBaseHelper.createInstance(connectionType)
cnn.openConnection(connectionString)
mDataAdapter = cnn.getDataAdapter(selectCommand)
mDataSet = New DataSet()
Dim dtb As New DataTable
mDataAdapter.Fill(mDataSet)
dtb = mDataSet.Tables(0)
mBindingSource.DataSource = dtb
navigator_main.BindingSource = mBindingSource
dgv_main.DataSource = mBindingSource
End Sub


where the clase FDataBaseHelper looks like:

Public Class FDataBaseHelper
'Class factory depending on type of connection
Public Const SQLSERVER_ = "SQLServer"
Public Const MYSQL_ = "MySQL"
Public Const ODBC_ = "ODBC"

Public Shared Function createInstance(conn_type As String) As IDataBaseHelper
If conn_type = SQLSERVER_ Then
Return New DataBaseHelperSQL
ElseIf conn_type = MYSQL_ Then
Return New DataBaseHelperMySQL
ElseIf conn_type = ODBC_ Then
Return New DataBaseHelperODBC
End If
Return Nothing
End Function

End Class


And the getDataAdapter function (e.g. for the DataBaseHelperSQL) is:

Public Function getDataAdapter(query As String) As IDataAdapter Implements IDataBaseHelper.getDataAdapter
Dim command = createSQLCommand(query)
Dim adapter = New SqlDataAdapter(command)
Return adapter
End Function


The datagridview gets populated nicely, but any changes made by the user are not stored in the database. The problem is that IDataAdapter does not have InsertCommand, UpdateCommand and DeleteCommand as members, and I'm getting an error in:

mAdapter.Update(mDataSet)


The error says: "Update requires a valid InsertCommand when passed DataRow collection with new rows"
I have tried several suggested solutions but to no avail, since I've not found anyone doing it using an interface.
Any help would be greatly appreciated

EDIT: finally the solution was as easy as moving the definition of the basic commands inside the helper classes. E.g. for the DataBaseHelperSQL the getDataAdapter method stands:

Public Function getDataAdapter(query As String) As IDataAdapter Implements IDataBaseHelper.getDataAdapter
Dim command = createSQLCommand(query)
Dim adapter = New SqlDataAdapter(command)
Dim basiccommands = New SqlCommandBuilder(adapter)
adapter.InsertCommand = basiccommands.GetInsertCommand
adapter.UpdateCommand = basiccommands.GetUpdateCommand
adapter.DeleteCommand = basiccommands.GetDeleteCommand
Return adapter
End Function

Answer

Finally the solution was as easy as moving the definition of the basic commands inside the helper classes. E.g. for the DataBaseHelperSQL the getDataAdapter method stands:

Public Function getDataAdapter(query As String) As IDataAdapter Implements IDataBaseHelper.getDataAdapter
        Dim command = createSQLCommand(query)
        Dim adapter = New SqlDataAdapter(command)
        Dim basiccommands = New SqlCommandBuilder(adapter)
        adapter.InsertCommand = basiccommands.GetInsertCommand
        adapter.UpdateCommand = basiccommands.GetUpdateCommand
        adapter.DeleteCommand = basiccommands.GetDeleteCommand
            Return adapter
End Function
Comments