DataSet and XML 1

[5679]

I have loaded an xml file in a dataset. i want to save the resulting data in a database. any help or hints?

 

[kneejnick]

Both the DataRow and DataTable classes also have AcceptChanges methods. Invoking AcceptChanges on the DataSet causes AcceptChanges to be called on each table in a DataSet. Consequently, calling AcceptChanges on each DataTable causes each DataRow object's AcceptChanges method to be called. In this manner, you have multiple levels at which the method can be invoked. Calling the AcceptChanges of the DataSet however, allows you to invoke the method on all subordinate objects (for example, tables and rows) with one call.

myDataSet.AcceptChanges();

 

[obislavu]

You can build a DataSet object from many sources. One that you did is from an Xml file, another could be just created on the fly at run time , with one or more DataTable(s)and each Table with columns created also at run time.
You cannot put this DataSet into a DB by using AcceptChanges() because the DB doesn't know anything about the DataSet that you created. In this case you have to put back the DataSet into DB. If the tables loaded from the xml file are not present in the DB you should create first from your code (if permitted) and after that send data to DB.
-obislavu-

 

[transparent]

Okay,

so I have a dataset which mimicks the structure of my database. I now want to put this data into the database. How do I do this? I cannot find any code examples!

What does the code look like?

 

[obislavu]

A DataSet object is working very well with XML file and this is very powerfull for persisting DataSet objects in a XML file and also when used by Web/Windows services.
Only the DataAdapter serves as a bridge between a DataSet and a data source for retrieving and saving data.
The DataAdapter provides this bridge by using Fill()mapping function, which changes the data in the DataSet to match the data in the data source, and the Update(), which changes the data in the data source to match the data in the DataSet.
A solution for you could be done but you have to check very well the structure and the model of the DataSet object loaded from a xml file with the structure of the existing data source. If the both structures are identical then you can use such DataAdapter bridge to save the DataSet into the data source but it is not easy when you take a look what are the checks performed when the Update() function is called.
Another way is to have permissions to create the data source tables, their constraints, primary keys etc. using the structure of the DataSet object loaded from a xml file.
This can be done in C# code directly or calling a stored procedure.
Next, iterate through all tables in the DataSet and insert records into the data source.
-obislavu-

 

[transparent]

Cheers fore your help.

Assuming the structure of the dataset and the database are identical and I dont create the database from the dataset, what would the code look like to just update existing tables in the database (I have had a lot of trouble finding examples)

 

[obislavu]

Here is an example with one table in the xml file which is working e.g. load xml file, connect to a SQL db and update the Test_A2 table.

public void Test_A2()
{
			
	try
	{
		
		string sTable = "Test_A2";
		string sCon = "user id=sa;Password=admin;Initial Catalog=testdb;Data Source=localhost;Connect Timeout=5";
		SqlConnection myConn = new SqlConnection(sCon);
		SqlDataAdapter myDataAdapter = new SqlDataAdapter();
		myDataAdapter.SelectCommand = new SqlCommand("SELECT TOP 0 * FROM " + sTable, myConn);
		SqlCommandBuilder custCB = new SqlCommandBuilder(myDataAdapter);
		myConn.Open();
		DataSet DS = new DataSet("DataSetName");
		myDataAdapter.FillSchema(DS,SchemaType.Source,sTable);
		myDataAdapter.Fill(DS,sTable);
					
		DataSet ds2 = new DataSet();
		ds2.ReadXml("C:\\ds.xml");
		myDataAdapter.Update(ds2, sTable);
		myConn.Close();
		myConn.Dispose();
		myDataAdapter.Dispose();
		ds2.Dispose();			
	}
	catch(Exception e)
	{
		//Log.Write(e.GetType() + " : " + e.Message);
	}                			
}

Here is the content of the loaded "ds.xml" file:

<?xml version="1.0" standalone="yes"?>
<DataSetName>
  <Test_A2>
    <model>TestModel2</model>
    <sn>TestS'N"2</sn>
    <ds>ds2</ds>
    <mycheck>true</mycheck>
  </Test_A2>
  <Test_A2>
    <model>George Test</model>
    <sn>TestS'N"2</sn>
    <ds>DS2</ds>
    <mycheck>true</mycheck>
  </Test_A2>
  <Test_A2>
    <model>TestSN</model>
    <sn>TestModel</sn>
    <ds>TEST&gt;&gt;&gt;</ds>
    <mycheck>true</mycheck>
  </Test_A2>
</DataSetName>

-obislavu-