Advertisement

02.22.2007 at 09:50PM PST, ID: 22408458
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

XML to Dataset and then Load into SQL Server CE problem

Tags: dataset, xml, load, sql
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>
Start your free trial to view this solution
Question Stats
Zone: Programming
Question Asked By: cscg1976
Solution Provided By: vbturbo
Participating Experts: 1
Solution Grade: A
Views: 64
Translate:
Loading Advertisement...
02.23.2007 at 02:32AM PST, ID: 18594806

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
02.23.2007 at 02:48AM PST, ID: 18594873

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
02.23.2007 at 03:07AM PST, ID: 18594932

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
02.23.2007 at 03:10AM PST, ID: 18594939

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
02.23.2007 at 07:21AM PST, ID: 18596523

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
02.24.2007 at 10:45AM PST, ID: 18602616

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
02.24.2007 at 10:52AM PST, ID: 18602633

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
Loading Advertisement...
Microsoft
  • Internet Protocols
  • Applications
  • Development
  • OS
  • Hardware
  • Windows Security
Apple
  • Operating Systems
  • Hardware
  • Programming
  • Networking
  • Software
Internet
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Spy / Ad Blockers
  • Web Browsers
  • New Net Users
  • Web Development
  • Chat / IM
  • Anti Spam
  • Web Servers
  • Anti-Virus
  • Email Clients
Gamers
  • Tips
  • Online / MMORPG
  • Puzzle
  • Emulators
  • Action / Adventure
  • Role Playing
  • Consoles
  • Game Programming
  • Strategy
  • Sports
  • Misc
  • Computer Games
Digital Living
  • Hardware
  • New Net Users
  • New Users
  • Software
  • Digital Music
  • Gaming World
  • Home Security
  • Apple
  • Networking Hardware
Virus & Spyware
  • Vulnerabilities
  • IDS
  • Encryption
  • Anti-Virus
  • Operating Systems Security
  • Software Firewalls
  • WebApplications
  • Cell Phones
  • Operating Systems
  • Internet
  • Hardware Firewalls
Hardware
  • Handhelds / PDAs
  • Displays / Monitors
  • Components
  • Networking Hardware
  • Peripherals
  • Laptops/Notebooks
  • Storage
  • Servers
  • Desktops
  • New Users
  • Misc
  • Apple
Software
  • System Utilities
  • Industry Specific
  • Network Management
  • Photos / Graphics
  • Page Layout
  • VMWare
  • Misc
  • Web Development
  • OS
  • CYGWIN
  • Voice Recognition
  • Message Queue
  • Quality Assurance
  • Security
  • Firewalls
  • MultiMedia Applications
  • Development
  • Database
  • Office / Productivity
  • Business Management
  • OS/2 Apps
  • Server Software
  • Internet / Email
ITPro
  • OS
  • Storage
  • Encryption
  • Operating Systems Security
  • Apple Hardware
  • Laptops & Notebooks
  • Servers
  • Networking Hardware
  • Peripherals
  • Devices
  • Displays / Monitors
  • WebTrends / Stats
  • Search Engines
  • Firewalls
  • WebApplications
  • IDS
  • Vulnerabilities
  • Email Clients
  • File Sharing
  • Spy / Ad Blockers
  • Web Browsers
  • Web Servers
  • Networking
  • Anti-Virus
  • Chat / IM
  • Anti Spam
Developer
  • Web Servers
  • Web Browsers
  • Game Programming
  • Dev Tools
  • Industry Specific
  • Office / Productivity
  • Database
  • CYGWIN
  • Web Development
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Programming
  • Content Management
  • Application Servers
  • Protocols
Storage
  • Removable Backup Media
  • Storage Technology
  • Servers
  • Grid
  • Remote Access
  • Backup / Restore
  • Misc
  • Hard Drives
OS
  • Miscellaneous
  • Security
  • Development
  • Linux
  • VMWare
  • MainFrame OS
  • Unix
  • Apple
  • OS / 2
  • AS / 400
  • BeOS
  • Microsoft
  • VMS / OpenVMS
Database
  • Oracle
  • Miscellaneous
  • MySQL
  • Software
  • Sybase
  • Contact Management
  • PostgreSQL
  • Data Manipulation
  • Clarion
  • InterSystems Cache
  • Siebel
  • MUMPS
  • OLAP
  • SQLBase
  • SAS
  • GIS & GPS
  • 4GL
  • Berkeley DB
  • DB2
  • Informix
  • Interbase / Firebird
  • FoxPro
  • Reporting
  • LDAP
  • Filemaker Pro
  • MS SQL Server
  • dBase
  • MS Access
Security
  • Misc
  • Web Browsers
  • Software Firewalls
  • Operating Systems Security
  • File Sharing
  • Spy / Ad Blockers
  • Vulnerabilities
  • WebApplications
  • IDS
  • Anti-Virus
  • Encryption
  • Anti Spam
  • Email Clients
  • VPN
  • Chat / IM
Programming
  • Editors IDEs
  • Installation
  • Handhelds / PDAs
  • Multimedia Programming
  • System / Kernel
  • Algorithms
  • Game
  • Signal Processing
  • Project Management
  • Open Source
  • Database
  • Misc
  • Languages
  • Processor Platforms
  • Theory
Web Development
  • Scripting
  • Blogs
  • Web Servers
  • Software
  • Search Engines
  • Web Graphics
  • Images
  • Internet Marketing
  • Images and Photos
  • Components
  • Document Imaging
  • Web Languages/Standards
  • Illustration
  • WebApplications
  • Fonts
  • WebTrends / Stats
  • Authoring
  • Digital Camera Software
  • Miscellaneous
Networking
  • Protocols
  • Apple Networking
  • Network Management
  • Message Queue
  • Application Servers
  • Content Management
  • File Servers
  • Email Servers
  • Misc
  • Java Editors & IDEs
  • Wireless
  • Networking Hardware
  • Backup / Restore
  • System Utilities
  • ISPs & Hosting
  • Web Servers
  • Storage Technology
  • Removable Backup Media
  • Servers
  • Broadband
  • Grid
  • OS / 2
  • Novell Netware
  • Unix Networking
  • Windows Networking
  • Security
  • Telecommunications
  • Operating Systems
  • Linux Networking
Other
  • Community Advisor
  • Lounge
  • Community Support
  • New Net Users
  • Philosophy / Religion
  • Math / Science
  • Miscellaneous
  • URLs
  • Expert Lounge
  • Politics
  • Puzzles / Riddles
Community Support
  • Suggestions
  • New to EE
  • New Topics
  • Community Advisor
  • CleanUp
  • Announcements
  • General
  • Feedback
  • Input
  • EE Bugs
 
02.23.2007 at 02:32AM PST, ID: 18594806
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
 
02.23.2007 at 02:48AM PST, ID: 18594873
meant

Do you have a lot of rows in the xml ?
 
02.23.2007 at 03:07AM PST, ID: 18594932
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
 
02.23.2007 at 03:10AM PST, ID: 18594939
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
 
02.23.2007 at 07:21AM PST, ID: 18596523
Hi,

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

Thanks.
 
02.24.2007 at 10:45AM PST, ID: 18602616
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
 
02.24.2007 at 10:52AM PST, ID: 18602633
Hi

Another approach

for i as integer = 0 to ds.Tables("Ferie").Rows.Count()

mDataSet.Tables("mytable").ImportRow(ds.Tables("myxml").Rows(i))

next

It really all comes down to how / wich data attributes you want to collect.

vbturbo
Accepted Solution
 
 
20080236-EE-VQP-29