asked on

XML to Dataset and then Load into SQL Server CE problem

I have a problem when i'm trying to update an SQL Server CE Database from a dataset.

Basically I have 2 tables that are on the same dataset. One table is loaded directly using a dataadapter from SQL Server CE database and the other one is loaded from XML. What I need to do is to save the datatable that I loaded from the XML into the SQL Server CE database.

Before the last update the mywareDS containts 2 tables mywareDS.Tables[0] and mywareDS .Tables[1] that contain the correct data. The mywareDS.Tables[1] as you can see from the XSD has 2 fields;WAREH_ID and WAREH_NAME. The mywareDS.Tables[0] has more than 2 fields including WAREH_ID and WAREH_NAME.

I have the following code that does not work :

private void pictureBox1_Click_1(object sender, EventArgs e)
{

string temp = "temp.sdf";
string connstr = @"Data Source=" + temp;
DataSet mywareDS = new DataSet();
using (SqlCeConnection conn = new SqlCeConnection(connstr))
{
conn.Open();

string dmlPackageInfo = "SELECT * FROM WARH";
string dmlInsertPackage = "INSERT INTO " +
"WARH(WAREH_ID, WAREH_NAME) " +
"VALUES (?, ?)";
string dmlDeletePackage = "DELETE FROM " +
"WARH";

SqlCeDataAdapter daPackages = new SqlCeDataAdapter();
daPackages.SelectCommand = new SqlCeCommand(dmlPackageInfo, conn);

daPackages.InsertCommand = new SqlCeCommand(dmlInsertPackage, conn);
daPackages.InsertCommand.Parameters.Add("WAREH_ID", SqlDbType.Int);
daPackages.InsertCommand.Parameters.Add("WAREH_NAME", SqlDbType.NVarChar);

daPackages.DeleteCommand = new SqlCeCommand(dmlDeletePackage, conn);

daPackages.Fill(mywareDS);
String xmlFileName = "MyWAREH.xml";
String xsdFileName = "MyWAREH.xsd";

FileStream FsXSD = new FileStream(xsdFileName, FileMode.Open);
FileStream FsXML = new FileStream(xmlFileName, FileMode.Open);

// Load the schema into the DataSet.
XmlTextReader xtrXSD = new XmlTextReader(FsXSD);
mywareDS.ReadXmlSchema(xtrXSD);
xtrXSD.Close();
FsXSD.Close();

// Load the data into the DataSet.
XmlTextReader xtrXML = new XmlTextReader(FsXML);
mywareDS.ReadXml(xtrXML);
xtrXML.Close();
FsXML.Close();

//so basically the problem lies right here ***********************************************
daPackages.Update(mywareDS);

}

--- the xsd looks like the following

<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="WARH" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="WARH">
<xs:complexType>
<xs:sequence>
<xs:element name="WAREH_ID" type="xs:int" />
<xs:element name="WAREH_NAME" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="50" />
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="Constraint1" msdata:PrimaryKey="true">
<xs:selector xpath=".//WARH" />
<xs:field xpath="WAREH_ID" />
</xs:unique>
</xs:element>
</xs:schema>

vbturbo

Do you have a lot of rows in the dataset ?

One option is to iteriate trough the xml file and then add the rows to the dataset
Then you could do the da.update(mywareDS)

vbturbo

vbturbo

meant

Do you have a lot of rows in the xml ?

vbturbo

Now you have the xml data added to the dataset.

Private Sub Get_xml_data_into_dataset()

Dim reader As XmlTextReader = New System.Xml.XmlTextReader("c:\my.xml)
'("http://www.yourdomain.com/my.xml")
Dim dr As DataRow
Dim objNodes As New System.Xml.XmlDocument
objNodes.Load(reader)
Dim oNodes As System.Xml.XmlNodeList
oNodes = objNodes.SelectNodes("//WARH")
Dim node As System.Xml.XmlNode
For Each node In oNodes
Dim dr As DataRow
dr = ds.Tables("mywareDS").NewRow()
dr(0) = node.Attributes.GetNamedItem("WAREH_ID").Value
dr(1) = node.Attributes.GetNamedItem("WAREH_NAME").Value
ds.Tables("mywareDS").Rows.Add(dr)
Next
End Sub

hope this helps

vbturbo

vbturbo

Sorry

this needs to be corrected.

For Each node In oNodes
Dim dr As DataRow
dr = mywareDS.Tables("mytable").NewRow()
dr(0) = node.Attributes.GetNamedItem("WAREH_ID").Value
dr(1) = node.Attributes.GetNamedItem("WAREH_NAME").Value
mywareDS.Tables("mytable").Rows.Add(dr)
Next

cscg1976

ASKER

Hi,

Thanks for the response. One of the XML file might have more than 5000 entries...

Thanks.

vbturbo

Then have a look here

If your xml file is identical with the database table then you could do something like this

Public Sub loadTable()

Dim mDataAdapter As New OleDb.OleDbDataAdapter
Dim mDataSet As New System.Data.DataSet
Dim mConn As OleDb.OleDbConnection
Dim mCommandBuilder As OleDb.OleDbCommandBuilder
Try
mDataAdapter.SelectCommand = New OleDb.OleDbCommand("SELECT * FROM testTable;", mConn)
mCommandBuilder = New OleDb.OleDbCommandBuilder(mDataAdapter)
mDataAdapter.FillSchema(mDataSet, SchemaType.Mapped)

mDataSet = ReadXMLFile("c:\Test.xml")

mDataAdapter.Update(mDataSet.Tables(0))
mDataSet.AcceptChanges()

Catch ex as exception
MsgBox(ex.message)
End Try
End Sub

vbturbo

ASKER CERTIFIED SOLUTION

vbturbo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial