OleDb database to DataSet and back in c#?
- by Troy
I'm writing a program that lets a user:
Connect to an (arbitrary)
View all of the tables in that database in separate DataGridViews
Edit them in the program, generate random data, and see the results
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.
