1

As a quick background, I'm a beginner at database programming so forgive me if anything that I post here doesn't make sense or is outright dumb.

I'm trying to make a WinForm application that will allow users to connect to a .mdb Access 97 database of their choosing, allow the user to make changes (e.g. add new data rows on WinForm which will then be applied to the original .mdb database), and sync those changes across different .mdb Access 97 databases if desired.

To give you an idea of what I have so far. I have successfully connected to a .mdb Access 97 database using C# and I output the database table contents into a DataSet object from which I then dumped into a C1FlexGrid (code will follow momentarily). Now before anyone mentions anything, I cannot upgrade the database file to a newer version, so it has to stay as an Access 97 version file.

Here is the code I used to connect:

dbConnection  = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path);
dbCommand     = new OleDbCommand("SELECT * from MAIN", dbConnection);
dbDataAdapter = new OleDbDataAdapter(dbCommand);

dbDataSet = new DataSet("MasterLanguageDB");
dbConnection.Open();
dbConnectionIsOpen = true;

// connection is successful, unlock connected mode features
EnterDatabaseConnectedMode();

dbDataAdapter.Fill(dbDataSet);
flexGrid.DataSource = dbDataSet.Tables[0];

My question is this: is the C1FlexGrid that I now have populated with the .mdb database file contents "binded" to the .mdb file? Because 1, I didn't bind the datasource the way Microsoft suggests doing it by way of the Add Data Source wizard in Visual Studio 2008 (because the user can connect to any .mdb database they choose -- not just one) and 2, I want whatever changes I make to the C1FlexGrid to apply to the original database.

If the answer is no, how do I create that "binding" or add the rows to the database?

Danchez
  • 1,266
  • 2
  • 13
  • 28

1 Answers1

0

The ADO.NET classes like Dataset or DataTable are disconnected objects.
Meaning that they can't directly update the database.

The OleDbDataAdapter has a method called Update that takes care to send all the modified data to the database. So, supposing you have a button somewhere to save your changes, then you need to call in the click event

  dbDataAdapter.Update(dbDataSet);

However this requires that you keep the object instances at the class global level
Another point is the need to prepare the dbDataAdapter.InsertCommand, dbDataAdapter.UpdateCommand and dbDataAdapter.DeleteCommand.

These commands could be created simply using an instance of OleDbCommandBuilder just after you have set the SelectCommand 

  dbDataAdapter = new OleDbDataAdapter(dbCommand);
  OleDbCommandBuilder cb = new OleDbCommandBuilder(dbDataAdapter);
Steve
  • 213,761
  • 22
  • 232
  • 286
  • Hey Steve, thanks for your input. Really informative! So help me along here so I understand a little more. The new record entries on my WinForm are done by way of typing into two text boxes and then clicking an Add Record button. So clicking that Add Record button should invoke the action of adding that new data row to the **DataSet** and NOT the C1FlexGrid correct? (Because I will be calling dbDataAdapter.Update(dbDataSet) ) – Danchez Apr 17 '13 at 14:53
  • I don't know the inner workings of a `C1FlexGrid`, but, if it works as every other grid in the net world, then you add your new data to the grid (How to add a row and fill cells should be described in their docs). At this point, the grid should pass this new row into the binded DataTable object. At Update time the DataSet contains the table with the added row. You could check in debugger calling `ds.Tables[0].GetChanges(DataRowState.Added);`. It returns an array of DataRow with state=Added. Of course before calling the Update method. – Steve Apr 17 '13 at 15:07
  • Steve, you sir are a Godsend! Thank you so much for your help! I wish I knew more about how to migrate my train of thought from the concrete manipulation of items to the more abstract sense, because in taking this whole route, I'm directly accessing the database at every wake and turn. Not sure when I'll figure out how to take an alternative approach...but nonetheless, what you provided gave me the insight I needed. I made that `OleDbCommandBuilder` like you said and pretty much just put `dbDataAdapter.Update(dbDataSet)` everywhere. I can see the changes being made from the `C1FlexGrid` now. – Danchez Apr 19 '13 at 15:01
  • Glad to be of help, keep working on it and sooner or later it becomes natural – Steve Apr 19 '13 at 15:39