troubleshooting Question

XML to Dataset and then Load into SQL Server CE problem

Avatar of cscg1976
cscg1976 asked on
.NET Programming
7 Comments1 Solution2045 ViewsLast Modified:
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>
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros