[RESOLVED] Data Access Examples -> BindingSource

[Griminal]

I've been manipulating data for the last two years mostly by querying my databases directly with SQL... (came from VB6) I figured I should learn the "correct" way of doing it.

I've read mendhak's "An Introduction to ADO.NET" tutorial and jmcilhinney's "Data Access Examples". With both example's help, I've emulated mendhak's project with an application accessing MySQL. It works great.

I've tried my best to replicate the same type of database interaction with a current project I'm working on... only with the addition of a BindingSource. I'm doing my best to understand this BindingSource thingie but I'm having a tough time on my own. I can't seem to find a good tutorial that doesn't involve a DataGridView... which I'm not using.

I should be using a BindingSource with most my projects, right? How would I implement a BindingSource with mendhak's example? Am I completely on the wrong track here?

Here's a screenshot of what I'm trying to use BindingSource with:

[jmcilhinney]

The BindingSource simply provides a common place for you to manipulate the data. You don't have to touch the control(s) or the data source itself. You can access all the data from the BindingSource, plus navigate and manipulate it.

Instead of binding a DataTable to a DataGridView directly:

myDataGridView.DataSource = myDataTable

you do it through the BindingSource:

myBindingSource.DataSource = myDataTable
myDataGridView.DataSource = myBindingSource

You can now access the data by enumerating the BindingSource to get DataRowView objects. You can get the row currently selected in the grid from the Current property. You can navigate the data by calling the MoveFirst, MovePrevious, MoveNext and MoveLast methods or setting the Position property. You can add a new row by calling AddNew and delete the selected row by calling RemoveCurrent. You can sort and filter the data by setting the Sort and Filter properties.

The BindingSource is not limited to binding DataTables to controls. It can bind any IList or IListSource object, but the functionality it exposes is limited by the functionality of object bound to it. To get the whole kit and kaboodle the object must implement the IBindingListView interface. The DataTable class implements the IListSource inetrface, exposing its DefaultView property, which is of type DataView. The DataView class implements the IBindingListView interface, thus you get the full gamut of functionality when binding a DataTable via a BindingSource. You can implement IBindingListView in your own collections if you want. In your case none of that is required though, because you'll just be binding a DataTable.

Note also that you can associate a BindingSource with a BindingNavigator. That provides a user interface to many of the methods I mentioned above.

[Griminal]

Thanks for the help.


Sandbox Image:


I think I'm using the .EndEdit correctly.

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Dim strNewName As String = InputBox("Enter name to add:")
        If strNewName IsNot Nothing Then
            bs.AddNew()
            txtFirstName.Text = " "
            txtLastName.Text = strNewName
            txtLocation.Text = " "
            bs.EndEdit()
            da.Update(ds)
            ds.AcceptChanges()
 
            MessageBox.Show("Record inserted successfully", Me.Name, MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
    End Sub

This sub adds the new record to the database but when I try to immediately update it with code, I get a concurrency error. If I close my app, I can update the record without the error. I'm obviously not syncing something correctly.

Other than that, Delete and Update are working as they should. It's just after adding a record and trying to update it that is causing me issues now.

[jmcilhinney]

Does your table have a primary key that is generated by the database? When you insert a new record, is that key value passed back to your DataRow? If not then it will still contain the temporary ID generated by the DataTable, which is quite possibly different to the actual value generated by the database.

[Griminal]

Does your table have a primary key that is generated by the database? When you insert a new record, is that key value passed back to your DataRow? If not then it will still contain the temporary ID generated by the DataTable, which is quite possibly different to the actual value generated by the database.

Yes. I have an ID field that is auto numbering and that would be the problem. Would you be so kind as to give me a hint on how to handle that? I'm guessing I'd have to refresh the dataset after a new row is inserted or do I need to make sure that field is included in my initial Select command?

[jmcilhinney]

A table's primary key must be included in your DataAdapter's query or else you won't be able to update or delete records because you won't be able to uniquely identify them.

Often times it doesn't matter what ID values the database generates because you won't actually save the data until you're finished with it. Are you sure that that's not what you should be doing? Is it really necessary to save a record to the database as soon as it's created, then update it again later? Could you not just store all the changes locally and then perform a single save of all insertions, deletions and updates all together? In that case you wouldn't need to worry about the new ID because you wouldn't need to use it until next time you retrieved all the data.

Assuming that you aren't able to do that and you must save a new record immediately then you need to perform a query immediately after the insert to get the ID value generated by the database. Some ADO.NET providers, including SqlClient, allow you to specify multiple SQL statements per command. That allows you to include a SELECT statement in your CommandText, right after the INSER, that will immediately refresh the DataRow with the current data. That current data includes the new ID.

The Jet OLEDB provider, which you are using to interact with an Access database, does not support this feature. As such you need to either refill your DataTable using your DataAdapter, or else just run a query to get the last ID generated. If you're only inserting one record at a time the second option is far more efficient. The query would look like this:

SELECT @@IDENTITY

@@IDENTITY is a global variable supported by most databases that contains the value of the last ID generated. You can execute such a query by calling ExecuteScalar on a Command because it only returns a single value. See my Data Access thread for a code example. Once you've got the ID value from the database you can assign it to the appropriate field of the DataRow that was just inserted. Make sure you call AcceptChanges on the row afterwards, because it should not be considered Modified after such a change.

[Griminal]

Do you mean something like this?

da.InsertCommand = New MySqlCommand("INSERT INTO test.tbl_Master(FirstName, LastName, Location) VALUES(?FirstName,?LastName,?Location); SELECT LAST_INSERT_ID();")

I've converted mendhak's application to use MySQL. MySQL doesn't use @@IDENTITY, it uses LAST_INSERT_ID(). I think I'm still missing something though. Probably something to do with ExecuteScalar... but I'm not understanding how to utilize it in my scenario here.

Again. I appreciate the hand holding...

[Griminal]

Update. OK. I figured out how to make it work but I'm not really sure how the two SQL statement insert thing works.

da.InsertCommand = New MySqlCommand("INSERT INTO test.tbl_Master(FirstName, LastName, Location) VALUES(?FirstName,?LastName,?Location);SELECT EmployeeID FROM test.tbl_Master WHERE EmployeeID = LAST_INSERT_ID();")

If I'm thinking straight, after the first SQL command in the InsertCommand, the second select sets the EmployeeID field in the dataset to the new autonumber value generated by the MySQL database. Is that right?

...maybe I'll stop dreaming about SQL...

[jmcilhinney]

The idea of having a SELECT right after an INSERT is to refresh the DataRow with the data that is actually in the database, which will include any auto-generated ID and also any default column values. The easiest way to do it is to let the system do it for you. You don't have that luxury with MySQL by default but an SQL Server example should serve. Here's the SQL code generated for an INSERT statement by the Data Source Configuration Wizard:

INSERT INTO [dbo].[Person] ([FirstName], [LastName]) VALUES (@FirstName, @LastName);
SELECT ID, FirstName, LastName FROM Person WHERE (ID = SCOPE_IDENTITY())

Note that the entire record is retrieved so as to populate the entire schema of the DataTable. Note also that SCOPE_IDENTITY is used as the ID of the record to retrieve. SCOPE_IDENTITY is like @@IDENTITY in SQL Server except @@IDENTITY is global and SCOPE_IDENTITY is only within the scope of the current transaction. In your case you should use something specific to the current scope if you can, otherwise stick with LAST_INSERT_ID.

[Griminal]

Cool. Thanks. I actually understand it a bit now. Let's see if I can put it into practice.