Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Import Rows between 2 datasets for all tables with matching columns

Posted on 2014-01-23
13
Medium Priority
?
754 Views
Last Modified: 2014-02-12
Hi there,

I have a rather unusual situation of which I would greatly appreciate some help on. Basically I have a typed ADO.Net Dataset that does not contain any data just a schema with relations and all. We can call this CurrentDataSet, it has about 56 tables in it..(I know right not very clever).

Anyway, I use this dataset to bind data to controls on a specific form with tabs. OK hope that is clear. Now I am getting the data from an API call which returns XML Data of which I then read and create a new Dataset from this using the below method:

  Dim web As New WebClient()
            Dim url As String = String.Format("https://test.co.uk/api/company?id=12345&apiKey=xxxx")
            Dim response As String = web.DownloadString(url)

            Dim ds As New DataSet()
          
            Using stringReader As New StringReader(response)
                ds = New DataSet
                ds.ReadXml(stringReader)
            End Using

Open in new window


I then can get the datatables from this new Dataset using ds.Tables("TableName"). However what I would like to do is populate CurrentDataSet with the data that comes from ds, so that it would be easier for me to use the bindingsource from CurrentDataset to bind to my controls. I know you can use the importrow method to import rows into CurrentDataSet...but there is a major caveat.

The problem is that the returned datatables from the API call aren't always present if there is no information in them. The API only returns datatables and fields from the relevant datatables if there are rows or data in them.

CurrentDataSet contains all possible fields and tables that the API could possibly return, now I want to know how I can update CurrentDataSet with row items only where table and field names exist between ds and CurrentDataSet? Ideally if I could merge the new dataset(ds) into matching tables/field in one go rather than looping through each datatable and columns, that would be great

I am putting maxmimum points on this because I think it is really difficult, thanks in advance.
0
Comment
Question by:databarracks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39806161
DataTable.Merge method does not require the schema of two datatables to be same. It can merge the columns that exist in both datatables. You can also control how it deals with additional columns that exist in source table but not in target table.

http://msdn.microsoft.com/en-us/library/wtk78t63(v=vs.110).aspx

Give it a try.
0
 

Author Comment

by:databarracks
ID: 39806207
I have tried this in code but it  didn't work as it kept saying that the column already exists or datatype integer keys already exist etc.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39806266
Can you show the code?
0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:databarracks
ID: 39806282
  Dim web As New WebClient()
            Dim url As String = String.Format("https://test.co.uk/api/company?id=12345&apiKey=xxxx")
            Dim response As String = web.DownloadString(url)

            Dim ds As New DataSet()
          
            Using stringReader As New StringReader(response)
                ds = New DataSet
                ds.ReadXml(stringReader)
            End Using

CurrentDataSet.Merge(ds,True,MissingMappingAction.Add)

Open in new window


So I have tries MissingMappingAction.Ignore and still breaks. THe CurrentDataSet is placed on my form at design time
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39806931
I would recommend merging datatables instead of datasets.

Also, you need an instance of the CurrentDataSet to merge with.
0
 

Author Comment

by:databarracks
ID: 39807106
Ok could you show me a code snippet of your plan?
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 39809386
Create an populate an instance of CurrentDataSet

Dim OriginalDataSet As New CurrentDataSet
MyTableAdapter.Fill(OriginalDataSet)

Dim NewDataSet As New DataSet
MyNewTableAdapter.Fill(NewDataSet)

OriginalDataSet.Tables(0).Merge(NewDataSet.Tables(0))


Notice that above line assumes that Tables(0) of OriginalDataSet and Tables(0) of NewDataSet are corresponding tables that you want to merge.
0
 

Author Comment

by:databarracks
ID: 39811217
Hi CodeCruiser,

I have tried your approach and I get the below error

<target>.id and <source>.id have conflicting properties: DataType property mismatch.

Open in new window


Also please be aware that I don't have any TableAdapter's for my datasets due to the nature of the returned api
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39813538
So you have same columns in both tables but these columns have different datatype in each table. Can you confirm which column it is and why it has different datatypes?
0
 

Author Comment

by:databarracks
ID: 39814253
HI there it is saying that the primary key field has different data types but they both have Int32 data types. To be honest because of the nature of the api I just don't see a way that I am going to get this to work as it doesn't always return all the fields from the table if there is no information in the column.

It is really annoying but I might just bind the new dataset from the api directly onto my forms instead of using my permanent dataset. I built the dataset using an XML to XSD converter in VS2012 if that helps.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39814719
Can you show code that you use to populate the permanent dataset, the new dataset and the merge code?
0
 

Author Comment

by:databarracks
ID: 39852857
Hi there,

Apologies for abandoning this question. I worked around this by simply trapping loads of possible scenarios as it just wasn't feasible any other way.

Thanks for the guidance as always CodeCruiser
0
 

Author Closing Comment

by:databarracks
ID: 39852859
Very helpful as always
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question