C# ADO.net Adapter Update Question.
-
I'm new to C# and am trying to access and update a Access Database. I've managed to create the database and can see the results in Access. I'm also able to open the Database and load the tables, but when I try to do an update it does not post back my changes. Below is the code with comments where things seem to fail. I do not get an exception, I just don't see the changes in the database file. // This method seems to work fine, it loads the data from the database into the six tables. public void openDatabase(DataSet ds, string filename) { // I have 6 tables in the dataset/database. string[] names = { "Project", "Portfolios", "Equations", "Scenarios", "Reports", "Rules" }; string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename; for (int i = 0; i < names.Length; i++) { string strAccessSelect = "SELECT * FROM " + names[i]; try { myAccessConn = new OleDbConnection(strAccessConn); } catch (Exception ex) { Console.WriteLine("Error: Failed to create a database connection. \n{0}", ex.Message); return; } try { OleDbCommand myAccessCommand = new OleDbCommand(strAccessSelect, myAccessConn); myDataAdapter = new OleDbDataAdapter(myAccessCommand); myAccessConn.Open(); myDataAdapter.Fill(ds, names[i]); } catch (Exception ex) { Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message); return; } finally { myAccessConn.Close(); } } } // This is the method I'm having trouble with, I sent the Dataset off to another routine which changes some // of the datarows, I can see the changes there in the dataset but update does not write them to disk. public void updateDatabase(DataSet ds) { try { dw = new DebugWindow(); // This opens up a window to display the dataset dw.Show(); dw.dumpDataSet(ds); // This dumps the dataset to the window, the dataset looks correct with my c
-
I'm new to C# and am trying to access and update a Access Database. I've managed to create the database and can see the results in Access. I'm also able to open the Database and load the tables, but when I try to do an update it does not post back my changes. Below is the code with comments where things seem to fail. I do not get an exception, I just don't see the changes in the database file. // This method seems to work fine, it loads the data from the database into the six tables. public void openDatabase(DataSet ds, string filename) { // I have 6 tables in the dataset/database. string[] names = { "Project", "Portfolios", "Equations", "Scenarios", "Reports", "Rules" }; string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename; for (int i = 0; i < names.Length; i++) { string strAccessSelect = "SELECT * FROM " + names[i]; try { myAccessConn = new OleDbConnection(strAccessConn); } catch (Exception ex) { Console.WriteLine("Error: Failed to create a database connection. \n{0}", ex.Message); return; } try { OleDbCommand myAccessCommand = new OleDbCommand(strAccessSelect, myAccessConn); myDataAdapter = new OleDbDataAdapter(myAccessCommand); myAccessConn.Open(); myDataAdapter.Fill(ds, names[i]); } catch (Exception ex) { Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message); return; } finally { myAccessConn.Close(); } } } // This is the method I'm having trouble with, I sent the Dataset off to another routine which changes some // of the datarows, I can see the changes there in the dataset but update does not write them to disk. public void updateDatabase(DataSet ds) { try { dw = new DebugWindow(); // This opens up a window to display the dataset dw.Show(); dw.dumpDataSet(ds); // This dumps the dataset to the window, the dataset looks correct with my c
DataAdapters are very simple; they can only do very simple things. They are really only good for writing quick-and-dirty demoes at Microsoft launch events. Any non-trivial application will quickly become complex enough that a DataAdapter will be of no use -- especially Update. I don't use DataAdapters and neither should you (or anyone else for that matter); they cause more trouble than they're worth. You would be much better off in the long run, learning how to access databases at a lower level. ExecuteScalar, ExecuteNonQuery, DataAdapter.Fill, and DataAdapter.Update all use ExecuteReader (and therefore DataReaders); learn to use DataReaders. (I think part of your problem above may be that myDataAdapter gets changed between calls to openDatabase and updateDatabase. But the whole thing should be rewritten. X| )
-
I'm new to C# and am trying to access and update a Access Database. I've managed to create the database and can see the results in Access. I'm also able to open the Database and load the tables, but when I try to do an update it does not post back my changes. Below is the code with comments where things seem to fail. I do not get an exception, I just don't see the changes in the database file. // This method seems to work fine, it loads the data from the database into the six tables. public void openDatabase(DataSet ds, string filename) { // I have 6 tables in the dataset/database. string[] names = { "Project", "Portfolios", "Equations", "Scenarios", "Reports", "Rules" }; string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename; for (int i = 0; i < names.Length; i++) { string strAccessSelect = "SELECT * FROM " + names[i]; try { myAccessConn = new OleDbConnection(strAccessConn); } catch (Exception ex) { Console.WriteLine("Error: Failed to create a database connection. \n{0}", ex.Message); return; } try { OleDbCommand myAccessCommand = new OleDbCommand(strAccessSelect, myAccessConn); myDataAdapter = new OleDbDataAdapter(myAccessCommand); myAccessConn.Open(); myDataAdapter.Fill(ds, names[i]); } catch (Exception ex) { Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message); return; } finally { myAccessConn.Close(); } } } // This is the method I'm having trouble with, I sent the Dataset off to another routine which changes some // of the datarows, I can see the changes there in the dataset but update does not write them to disk. public void updateDatabase(DataSet ds) { try { dw = new DebugWindow(); // This opens up a window to display the dataset dw.Show(); dw.dumpDataSet(ds); // This dumps the dataset to the window, the dataset looks correct with my c
Oi Adam Listen to what Piebald said, quick and dirty or DO NOT USE THEM. Learn about a Data Access Layer (DAL) and implement one of the many existing ones available here in the articles. Most accomplished developers build there own, I see a lot of new devs using the Enterprise library for their DAL these days.
-
I'm new to C# and am trying to access and update a Access Database. I've managed to create the database and can see the results in Access. I'm also able to open the Database and load the tables, but when I try to do an update it does not post back my changes. Below is the code with comments where things seem to fail. I do not get an exception, I just don't see the changes in the database file. // This method seems to work fine, it loads the data from the database into the six tables. public void openDatabase(DataSet ds, string filename) { // I have 6 tables in the dataset/database. string[] names = { "Project", "Portfolios", "Equations", "Scenarios", "Reports", "Rules" }; string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename; for (int i = 0; i < names.Length; i++) { string strAccessSelect = "SELECT * FROM " + names[i]; try { myAccessConn = new OleDbConnection(strAccessConn); } catch (Exception ex) { Console.WriteLine("Error: Failed to create a database connection. \n{0}", ex.Message); return; } try { OleDbCommand myAccessCommand = new OleDbCommand(strAccessSelect, myAccessConn); myDataAdapter = new OleDbDataAdapter(myAccessCommand); myAccessConn.Open(); myDataAdapter.Fill(ds, names[i]); } catch (Exception ex) { Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message); return; } finally { myAccessConn.Close(); } } } // This is the method I'm having trouble with, I sent the Dataset off to another routine which changes some // of the datarows, I can see the changes there in the dataset but update does not write them to disk. public void updateDatabase(DataSet ds) { try { dw = new DebugWindow(); // This opens up a window to display the dataset dw.Show(); dw.dumpDataSet(ds); // This dumps the dataset to the window, the dataset looks correct with my c
You can listen to the other peoples' advice to use ExecuteNonQuery(), etc. However, I am going to just talk about your piece of code. The adapter maintains four different query commands internally. One of them is the update query you implicitly called when you call myDataAdapter.Update(...). However, in order for it to work, the adapter has to know how to correctly construct the update query internally. What you need to make this program to work is to add the following line to tell the adapter how to construct the query internally: OleDbCommandBuilder builder=new OleDbCommandBuilder(myDataAdapter); Add the above line right after the line: myDataAdapter = new OleDbDataAdapter(myAccessCommand); After that, your Update(...) call should work.
-
DataAdapters are very simple; they can only do very simple things. They are really only good for writing quick-and-dirty demoes at Microsoft launch events. Any non-trivial application will quickly become complex enough that a DataAdapter will be of no use -- especially Update. I don't use DataAdapters and neither should you (or anyone else for that matter); they cause more trouble than they're worth. You would be much better off in the long run, learning how to access databases at a lower level. ExecuteScalar, ExecuteNonQuery, DataAdapter.Fill, and DataAdapter.Update all use ExecuteReader (and therefore DataReaders); learn to use DataReaders. (I think part of your problem above may be that myDataAdapter gets changed between calls to openDatabase and updateDatabase. But the whole thing should be rewritten. X| )
Thank you for your insight. I'll look into using a DataReader. When I first implemented this I actually read the .mdb in binary format and converted it line by line. It seems most books point to the DataAdapters, but as you say these seem only good for the quick and dirty. Thanks.
-
You can listen to the other peoples' advice to use ExecuteNonQuery(), etc. However, I am going to just talk about your piece of code. The adapter maintains four different query commands internally. One of them is the update query you implicitly called when you call myDataAdapter.Update(...). However, in order for it to work, the adapter has to know how to correctly construct the update query internally. What you need to make this program to work is to add the following line to tell the adapter how to construct the query internally: OleDbCommandBuilder builder=new OleDbCommandBuilder(myDataAdapter); Add the above line right after the line: myDataAdapter = new OleDbDataAdapter(myAccessCommand); After that, your Update(...) call should work.
Thanks dxlee, I gave this a try but update still does not seem to write to the disk. I tried to cut things down to one table as I think flipping between them is causing problems. I'll look more into the OleDBCommandBuilder. Thanks.