XML file to MS Access using C#

[codemama]

I have created xml files in C# using the DataSet ds.WriteXml method.

I now need to take an xml file and be able to do an insert or delete to a table in MS Access.
How would I go about that?

Do I have to load the XML into a DataSet and then Create command parameters based on the records in the Dataset or is there a simpler way to do this?

Thanks

[mldisibio]

As far as writing Xml to a file and then reading it back into a dataset, that should be straightforward. Here are two links:
DataSet.ReadXml(string fileName)
and
CodeProject: Convert XML Data to DataSet and Back.

You ask:

Do I have to ... Create command parameters based on the records in the Dataset

Well, the Insert and Delete parameters would be based on the fields. However, if you use a DataAdapter and call the DataAdapter.Update method IDataAdapter.Update Method (System.Data) to send any changes back to the database, then you probably can base this on the RowState of the rows you want to update or delete. However, you would have to load the original rows, then delete or insert the new rows within the DataSet, then call AcceptChanges(), and then DataAdapter.Update().

Yes, you will have to set the Update and InsertCommand text or stored procedure. You can try using CommandBuilder.GetUpdate/InsertCommand method, but if you ever look at what it actually produces, it is very verbose.

I have done the following to update/insert from a dataSet based off xml:
1. In SqlServer, you can actually send the XmlDocument as a stored procedure parameter...that is cool...but you are using Access.
2. Fill a DataSet and use the CommandBuilder to generate the Insert/Update/Delete Commands.
3. Write my own sql text command or stored procedures which basically take only the needed fields to do the update or delete. The Command Parameter(s) can be hooked up to one or more DataTable columns, and the DataSet can be updated in such a way that the command/stored procedure is run against each row.

[codemama]

Thanks much..
The thing I was missing was I needed to use the CommandBuilder..

The only issue I have right now is I can not get the delete to work. According to MS I am suppose to use the Delete() method on the record to flag it for delete so when I do an Update it will use the DeleteCommand. However when I do that it Deletes the records and does not flag them.
The RowState is a ReadOnly property so I cant just set that..

Here is my code..

IDbCommand cmd = CreateCommand("SELECT * FROM VehicleFPM", CommandType.Text);
DataSet ds = new DataSet();
//Read XML file into dataset
ds.ReadXml(xmlFile, XmlReadMode.Auto);
 
//Mark the records so they will be deleted
int rows = ds.Tables[0].Rows.Count;
for (int i = 0; i < rows; i++)
//Does not flag the record for delete - It Deletes them!!
ds.Tables[0].Rows[0].Delete();
 
OleDbDataAdapter testadp = new OleDbDataAdapter();
 
testadp.SelectCommand = (OleDbCommand) cmd;
OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(testadp);
//Put brackets around Field Names incase they have spaces in the names.
cmdBuilder.QuotePrefix = "[";
cmdBuilder.QuoteSuffix = "]";
 
//Auto generate the Delete and Insert Command. This is done based
testadp.DeleteCommand = cmdBuilder.GetDeleteCommand();
testadp.InsertCommand = cmdBuilder.GetInsertCommand();
 
recordsAffected = testadp.Update(ds, "VehicleFPM");
ds.AcceptChanges();
 

If you have any ideas on how to flag the recods for delete.. That would be a help..

Thanks

[mldisibio]

First, I was wrong in saying you need to execute "AcceptChanges()" before "Update()". You did not do this, so you code is basically correct. Doing so would have reset the RowState properties.

That said, and apologies if this is confusing...you might need to call AcceptChanges after loading the Xml document, but before deleting the rows.
I am not positive, but I have had similar difficulties myself and often is is simply resolved by tinkering with the order of statements.

Also, the DataTable maintains both the original version of the rows and the changes version. So even if it appears that the rows are simply deleted and not there anymore, there is a DataRowVersion http://msdn.microsoft.com/en-us/library/system.data.datarowversion(VS.80).aspx property associated with the before and after state of the row.

Or it might be an issue of command statement order. Check out this snippet from MSDN:

DataTable table = dataSet.Tables["Customers"];
 
// First process deletes.
adapter.Update(table.Select(null, null, DataViewRowState.Deleted));
 
// Next process updates.
adapter.Update(table.Select(null, null, 
  DataViewRowState.ModifiedCurrent));
 
// Finally, process inserts.
adapter.Update(table.Select(null, null, DataViewRowState.Added));

Here is another useful link to see if you can resolve the problem:
http://msdn.microsoft.com/en-us/library/33y2221y(VS.80).aspx

[codemama]

That was it..
When I pulled in the XML into the dataset the records are marked as Added. This is great for an insert.

However, since the records are already marked as Added in order to now mark them as Deleted you must first AcceptChanges so they are now marked as Unchanged then the Delete() will mark them as Deleted.

Why it actually Deletes the records if not in the Unchanged RowState seems a bit odd to me.. But what ever..

Thanks much for the information..
Sometimes just a set of second eyes really helps alot..

Here is the Code incase anyone else runs into the issue...

DataSet ds = new DataSet();
//Determine if the xml file has schema. If so then use it.
//If not the infer it from the file.
ds.ReadXml(xmlFile, XmlReadMode.Auto);
string tablename = ds.Tables[0].TableName;
IDbCommand cmd = CreateCommand("SELECT * FROM " + tablename, CommandType.Text);
if (procedureType == UpdateType.Delete)
 {
      //Records are currently marked as Added.
      //Must AcceptChanges so the records will be marked as Deleted
      ds.AcceptChanges();
       //Mark the records so they will be deleted
       foreach (DataRow dr in ds.Tables[0].Rows)
       {
              dr.Delete();
        }
 }
 
IDbDataAdapter dataAdpt = CreateDataAdapter(cmd);
 
dataAdpt.SelectCommand = (OleDbCommand) cmd;
OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder((OleDbDataAdapter) dataAdpt);
 //Put brackets around Field Names incase they have spaces in the names.
 cmdBuilder.QuotePrefix = "[";
 cmdBuilder.QuoteSuffix = "]";
  //Auto generate the Delete and Insert Command. This is done based
 dataAdpt.DeleteCommand = cmdBuilder.GetDeleteCommand();
 dataAdpt.InsertCommand = cmdBuilder.GetInsertCommand();
 dataAdpt.TableMappings.Add("Table", tablename);
 recordsAffected = dataAdpt.Update(ds);
 
ds.AcceptChanges();