Update data from DataSet to Database

Question

I'm new to .NET and having problems to update my data from my DataSet using the easy(?)/fast Update() methods of the .NET DataAdapter.

 

I'm not a big fan of making apps completly with the IDE for databinding etc. (drag and drop bindingsource things and using the properties windows to link datafields).

I prefer the programmatical way.

 

My little  program reads an Xml file, puts it in a DataSet (ds.ReadXml) and then it stores all that data in my MS Access database looping all the rows of the DataTables of the DataSet with the Xml data.

For that I use the OleDBConnection and the OleDBCommand objects.

From the OleDBCommand I use the ExecuteNonQuery() method to insert all the rows (looping) into the database (Tables with the same name as the Xml entities exists in the Access database!).

 

Ex.:

dsOpenFile.ReadXml(strFilename);

 

for (int i = 0; i <= dsOpenFile.Tables["tests"].Rows.Count - 1; i++)

{

       cmd.CommandText = "INSERT INTO tests (…..”

cmd.ExecuteNonQuery();

}

 

This maybe not the fastest way but for me, at this moment, the easiest to understand coming from a programming world using straight SQL statements J

 

After the data of the XML file is inserted into the database I use the .NET Data objects to fill a new DataSet with data from the database and I also set the relations between all the tables (main reason that I use a new DataSet and not the first one with the plain XML data.)

 

Ex.:

// Declarations

OleDbConnection con = new OleDbConnection(stringConnection);

DataSet ds = new DataSet("TEST");

OleDbDataAdapter da = new OleDbDataAdapter();

           

// Fill DataSet

da.SelectCommand = new OleDbCommand("SELECT * FROM tests_a", con);

da.Fill(ds, "tests_a");

 

da.SelectCommand = new OleDbCommand("SELECT * FROM tests_b", con);

da.Fill(ds, "tests_b");

 

// Make relation between tests tables

DataRelation relEventTests;

DataColumn colMaster1;

DataColumn colDetail1;

colMaster1 = ds.Tables["tests_a"].Columns["id"];

colDetail1 = ds.Tables["tests_b"].Columns["id"];

relTests = new DataRelation("RelTests", colMaster1, colDetail1);

ds.Relations.Add(relTests);

 

If that’s done I use a DataViewManager object to bind all the data from the new DataSet with tables and relations.

 

Ex.:

// Declarations

DataViewManager dsView = new DataViewManager();

 

// Set default view manager of the dataset

dsView = ds.DefaultViewManager;

 

// Bind TextBox

txtTestID.DataBindings.Clear();

txtTestID.DataBindings.Add("Text", dsView, "events.id");

 

// Bind Combobox

cboSelectionTest.DataSource = dsView;

cboSelectionTest.DisplayMember = "test_a.relTests.test_name";

cboSelectionTest.ValueMember = "test_a.relTests.test_nr";

 

// Bind DataGridView

dataGridView1.DataSource = dsView;

dataGridView1.DataMember = "tets_a.relTests.relTest2";

 

….

 

This all works very fine!

If I change my ComboBox the data on the Textfields and DataGridView changes perfectly just  like I’ve made the relations between all the tables.

Also if I change the data in the textfields or in the DataGridView the modified data stays in the DataSet when changing the value of the main Combox and going back to the previous value of that ComboBox.

 

BUT, now I’m coming to my main question!!

 

How do I update the modified data from the DataSet, showing the data on my form perfectly, into my Database using simple data methods like the Update statement for example.

This method never works in my program.

I’m thinking this has something to do with bindingsources because all the information (books, internet) I find shows me code like this:

try

{

    this.Validate();

    this.customersBindingSource.EndEdit();

    this.customersTableAdapter.Update(this.northwindDataSet.Customers);

    MessageBox.Show("Update successful");

}

catch (System.Exception ex)

{

    MessageBox.Show("Update failed");

}

 

I don’t realy have bindingsources (I think)

My update code looks like this:

 

OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM tests", con);

OleDbCommandBuilder cb = new OleDbCommandBuilder(da);

 

try

{

da.Update(ds.Tables["tests"]);

       System.Windows.Forms.MessageBox.Show("Update successful");

}

catch (System.Exception ex)

{

       System.Windows.Forms.MessageBox.Show("Update failed");

}

 

You can guess which MessageBox message I always get...indeed “Update failed”.

 

What Am I doing wrong? What do I forget the implement, … ???

 

Thnx in advance for an answer

 

PS: I could use the straight SQL way. DELETE all data and re-INSERT it, but …  I think there must be an easier, not?