databarracks
asked on
Import Rows between 2 datasets for all tables with matching columns
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:
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.
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
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.
ASKER
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.
Can you show the code?
ASKER
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)
So I have tries MissingMappingAction.Ignor
I would recommend merging datatables instead of datasets.
Also, you need an instance of the CurrentDataSet to merge with.
Also, you need an instance of the CurrentDataSet to merge with.
ASKER
Ok could you show me a code snippet of your plan?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi CodeCruiser,
I have tried your approach and I get the below error
Also please be aware that I don't have any TableAdapter's for my datasets due to the nature of the returned api
I have tried your approach and I get the below error
<target>.id and <source>.id have conflicting properties: DataType property mismatch.
Also please be aware that I don't have any TableAdapter's for my datasets due to the nature of the returned api
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?
ASKER
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.
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.
Can you show code that you use to populate the permanent dataset, the new dataset and the merge code?
ASKER
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
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
ASKER
Very helpful as always
http://msdn.microsoft.com/en-us/library/wtk78t63(v=vs.110).aspx
Give it a try.