OleDb database to DataSet and back in c#?

Question

I'm writing a program that lets a user:

1. Connect to an (arbitrary) database that the user specifies
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.

Answer 1

You can create an OleDbAdapter and then configure it to treat the records, but you should give the Insert, Update and Delete statements, because the DataSet per se doesn't know where the data comes from; it could be a Access Database or a Xml or text file.

It would be something like this (not tested obviously)

DataSet ds = new DataSet("myData");
var da = new OleDbDataAdapter("SELECT * FROM employee", connection);
// filling your dataset
da.Fill(ds);

// Setting up the dataAdapter, could be a stored procedure
da.InsertCommand = new OleDbCommand("INSERT employee (id, name) VALUES (@id, @name)");
da.UpdateCommand = new OleDbCommand("UPDATE employee SET name = @name WHERE id = @id");

// Updating Database from data on DataSet
da.Update(ds);