OleDb database to DataSet and back in c#?

Posted by Troy on Stack Overflow See other posts from Stack Overflow or by Troy
Published on 2010-03-23T18:22:04Z Indexed on 2010/03/23 18:33 UTC
Read the original article Hit count: 276

Filed under:
|
|
|
|

I'm writing a program that lets a user:

  1. Connect to an (arbitrary)
  2. View all of the tables in that database in separate DataGridViews
  3. Edit them in the program, generate random data, and see the results
  4. Choose to commit those changes or revert

So I discovered the DataSet class, which looks like it's capable of holding everything that a database would, and I decided that the best thing to do here would be to load everything into one dataset, let the user edit it, and then save the dataset back to the database. The problem is that the only way I can find to load the database tables is this:

set = new DataSet();
DataTable schema = connection.GetOleDbSchemaTable(
    OleDbSchemaGuid.Tables,
    new string[] { null, null, null, "TABLE" });
foreach (DataRow row in schema.Rows)
{
    string tableName = row.Field<string>("TABLE_NAME");
    DataTable dTable = new DataTable();
    new OleDbDataAdapter("SELECT * FROM " + tableName, connection).Fill(dTable);
    dTable.TableName = tableName;
    set.Tables.Add(dTable);
}

while it seems like there should be a simpler way given that datasets appear to be designed for exactly this purpose. The real problem though is when I try saving these things. In order to use the OleDbDataAdapter.Update() method, I'm told that I have to provide valid INSERT queries. Doesn't that kind of negate the whole point of having a class to handle this stuff for me?

Anyway, I'm hoping somebody can either explain how to load and save a database into a dataset or maybe give me a better idea of how to do what I'm trying to do. I could always parse the commands together myself, but that doesn't seem like the best solution.

© Stack Overflow or respective owner

Related posts about c#

Related posts about oledb