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>
ASKER CERTIFIED SOLUTION
vbturbo

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 7 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 7 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004