Okay, I know some of you are looking at that title and thinking I'm quite daft because obviously I have to have a dataadapter if I have a bindingsource. And you're right, there is a dataadapter, but it's not exposed to me.
I've taken over a winforms project (attached to a SQL Server database) that needs to completed quickly so am trying to work with what the other developer has rather than rewriting it all. He has an admin form with a listview and a datagridview. The idea is to allow a user to edit the lookup tables for the database (customer type, product list, etc). When the form is loaded, the listview is populated (hard-coded) with a list of available tables. Clicking on a table name fills the datagridview with the contents of the table. What he left incomplete was the updating of the database if the user makes updates/inserts to a table.
Now, I know that normally you would just call the Update method of the dataadapter, but he passes all his database calls for the entire project through a helper class that will execute parameterized sql queries (for updates/inserts), or return a bindingsource object for displaying data. But this helper class does not expose the underlaying dataadapter.
To populate the datagridview, he uses the following code in the ListBox1_SelectedIndexChanged event:
Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
' If this is the first time the list is rendered, it doens't seem to work properly
' This if...then circumvents errors associated with the initial population
If Me.ListBox1.SelectedValue.ToString = "System.Data.DataRowView" Then
Exit Sub
End If
' Create array to get data
Dim aryEditTable As New ArrayList
' Populate array
aryEditTable.Add("@TableName")
aryEditTable.Add(Me.ListBox1.SelectedValue)
' Create the binding source to fill the data grid
Dim bsDataGrid As New BindingSource
bsDataGrid = clsDB.GetRecsByParam("spFillGrid", aryEditTable)
' Bind the grid to the data
Me.dgv1.DataSource = bsDataGrid
End Sub
clsDB is his helper class. As you can see, the datagridview gets populated through a bindingsource, but I am not given access to the underlying dataadapter. Now, I know I can access the underlying datatable though something like:
dim dtTemp as New DataTable = DirectCast(bsDataGrid.DataSource, DataTable)
But I don't know if I can get to the underlying adapter from there. The .GetRecsByParam method of his class is merely a wrapper that builds the parameterized query, opens a connection to the sql database, executes the query and generates/returns a bindingsource. The heart of his helper class is this function here:
Public Shared Function GetBindingSource(ByVal cmd As SqlCommand) As BindingSource
' Declare the binding source
Dim oBS As New BindingSource
' Create new data adapter based on the specified query
Dim daGet As New SqlDataAdapter(cmd)
' Populate a new data table and bind it to the binding source
Dim dtGet As New DataTable
' Set the timeout of the cmd object
cmd.CommandTimeout = 240
dtGet.Locale = System.Globalization.CultureInfo.InvariantCulture
Try
' Fill data table
daGet.Fill(dtGet)
Catch ex As Exception
' Check for errors
MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Error recieving data")
Return Nothing
End Try
' Set the data source for the bindingsource to the datatable
oBS.DataSource = dtGet
' Return the bindignsource to the calling method/control
Return oBS
End Function
As you can see, the datatable and dataadapter are not persisted, instead being created each time the function is called.
The rest of the app deals with only one record at a time, so when an update is needed, he scrapes the data from the form, builds a parameter array and passes the array and the name of the stored proceedure that handles the update to a method of his helper class called .InsUpDel - which is simply another wrapper that builds a parameterized query, executes the passed stored proceedure and returns the success/fail state of update.
But, since this part of the app deals with an entire table rather than just one record, that method doesn't work. So, back to my question: How can I update the database when a change is made to datagridview when all I have is the bindingsource?
Hopefully I've been clear enough in my description, but I'd be happy to clarify or provide any more info if you feel I've jumbled the issue all up or left something out.