-
Jan 22nd, 2019, 11:22 AM
#1
Thread Starter
Lively Member
DataTable Relation - Want to save several Datatables into one xml
Hi,
I am using a DataSet to save my data into a .xml file and then read this data.
My program has several Windows forms, the idea is each one will be a Datatable in my DataSet.
I created Datatable1 to the firsr form. So far it is all good. I can save the data, and ready it back to the form.
My problem is when I also try to save a second Datatable2, that belongs to my DataSet and has a relation with the first one.
The idea is: Datatable1 works in one form, after saving the information, I jump to a second windows form, and then I also save the data the DataTable2 into the same XML file that I have already created. But, instead of adding information in the XML file, it is replacing the data from the DataTable1 for one in the Datatable2.
I know I am doing something wrong, but I could not understand yet how to manage it.
Some help would be great.
-- The code I use to save is: ( I used the property DataBiding to conect the controls with the datatable)
Code:
DataTable1BindingSource.EndEdit()
DataSeta.DataTable1.Rows.Add()
Try
DataSet.WriteXml(myXMLFilePath)
MsgBox("Data Saved Successfully!")
Catch ex As Exception
MsgBox("Something went wrong.. Erro: " & ex.ToString)
End Try
I am using this same code to both forms. I do not know if the problem is in this, or in the relation itself.
I appreciate the help.
-
Jan 22nd, 2019, 12:06 PM
#2
Re: DataTable Relation - Want to save several Datatables into one xml
I would say you have supplied maybe 15% of the code we could otherwise use to help you with your problem.
You should only have to read the data in when the application starts and use the same dataset in all your forms.
-
Jan 22nd, 2019, 03:51 PM
#3
Re: DataTable Relation - Want to save several Datatables into one xml
As far as I know, the WriteXML is a pretty simple beast, as the resulting XML file is also pretty simple. From your description, it sounds like you are entangling the tables in the dataset. How that is happening isn't related to the code shown, though. What you'd have to show would be more complicated and hard to show. In fact, I'm not quite sure where you should start, at this point, so answer this question:
How many tables are there in the dataset at the time you call WriteXML? I suspect that the answer is 1, but I don't know if there are other tables that are not related to the problem, but which are also in the dataset.
My usual boring signature: Nothing
-
Jan 22nd, 2019, 04:17 PM
#4
Thread Starter
Lively Member
Re: DataTable Relation - Want to save several Datatables into one xml
Originally Posted by Shaggy Hiker
As far as I know, the WriteXML is a pretty simple beast, as the resulting XML file is also pretty simple. From your description, it sounds like you are entangling the tables in the dataset. How that is happening isn't related to the code shown, though. What you'd have to show would be more complicated and hard to show. In fact, I'm not quite sure where you should start, at this point, so answer this question:
How many tables are there in the dataset at the time you call WriteXML? I suspect that the answer is 1, but I don't know if there are other tables that are not related to the problem, but which are also in the dataset.
The part I have working have only 1 DataTable. The idea is save the datatable of each form when the user finish entering the information, instead of wait all Datatables be filled to save in the end.
-
Jan 22nd, 2019, 05:22 PM
#5
Re: DataTable Relation - Want to save several Datatables into one xml
Create the DataSet, including all the DataTables and DataRelations, at the outset. You can then just call WriteXml on that DataSet to save everything at any time. You simply populate the existing DataTables as you go. If you need to learn how to create related tables in a DataSet, follow the CodeBank link in my signature below and check out my thread on Master/Detail Data-Binding.
-
Jan 22nd, 2019, 05:57 PM
#6
Re: DataTable Relation - Want to save several Datatables into one xml
If there is only one datatable, then it certainly makes sense that only one datatable will be saved. If each form is to have a different datatable, then there would be one datatable per form in the dataset. They will all be saving.
My usual boring signature: Nothing
-
Jan 22nd, 2019, 08:41 PM
#7
Re: DataTable Relation - Want to save several Datatables into one xml
Yeah, it sounds like the OP is expecting "DataSet.WriteXml(myXMLFilePath)" command to Append the table the second time it's called. But that's just a guess, like kpmc said, you really do need to provide more information.
-
Jan 22nd, 2019, 11:10 PM
#8
Re: DataTable Relation - Want to save several Datatables into one xml
I will share with you one of my solutions I recently created, and I just took some time to add some comments. This approach will allow you to 'append' tables and/or columns on the fly and will not overwrite your source XML or data therein. Please read carefully the comments and learn from this solution, do not just copy/paste your way to 6 pack abs
FormMain: (start form)
Code:
Public Class FormMain
'Path where your XML Data will live
Dim DataPath As String = My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\InvoiceData\"
'Create a var to the DataSet
Dim _Dset As DataSet = ModuleData.Dset
'Create a var to the DataSet that contains schema only
Dim _DsetSchema As DataSet = ModuleData.DsetSchema
Private Sub FormMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Width = Screen.PrimaryScreen.Bounds.Width - 75
Height = Screen.PrimaryScreen.Bounds.Height - 75
CenterToParent()
Try
'If path to souce doesnt exist, create it
If Not My.Computer.FileSystem.DirectoryExists(DataPath) Then My.Computer.FileSystem.CreateDirectory(DataPath)
'If source path and XML exist, read it in "_Dset" (this is your data)
If My.Computer.FileSystem.FileExists(DataPath & "InvoiceData.XML") Then _Dset.ReadXml(DataPath & "InvoiceData.XML")
'Sub routine to create DataSet that contains Schema "_DsetSchema"
ModuleData.SetDsetSchema()
'Sub routine that compares
LoadSchema(_DsetSchema, _Dset)
'Add relations if they do not exist
If Not _Dset.Relations.Contains("InvoicePeopleRelation") Then
_Dset.Relations.Add(New DataRelation("InvoicePeopleRelation",
_Dset.Tables("People").Columns("PersonID"),
_Dset.Tables("Invoice").Columns("PersonID"), True))
End If
If Not _Dset.Relations.Contains("InvoiceItemsRelation") Then
_Dset.Relations.Add(New DataRelation("InvoiceItemsRelation",
_Dset.Tables("Invoice").Columns("InvoiceID"),
_Dset.Tables("Items").Columns("InvoiceID"), True))
End If
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Private Sub LoadSchema(SourceDS As DataSet, TargetDS As DataSet)
'Boolean to flag if there are Schema Changes
Dim SchemaChanged As Boolean = False
Try
'Iterate tables in _DsetSchema
For Each Tbl As DataTable In SourceDS.Tables
'Check if table exists in _Dset (your data), if not add it
If Not TargetDS.Tables.Contains(Tbl.TableName) Then TargetDS.Tables.Add(New DataTable With {.TableName = Tbl.TableName})
'Iterate columns within table iteration
For Each Dcol As DataColumn In Tbl.Columns
'If column doesnt exist then add it
If Not TargetDS.Tables(Tbl.TableName).Columns.Contains(Dcol.ColumnName) Then
TargetDS.Tables(Tbl.TableName).Columns.Add(New DataColumn With {
.ColumnName = Dcol.ColumnName,
.DataType = Dcol.DataType,
.AutoIncrement = Dcol.AutoIncrement})
'If columns were added then schema has changed, set flag to true
SchemaChanged = True
End If
Next
Next
'If schema flad is true,'rewrite' the XML (your data)
If SchemaChanged Then
_Dset.WriteXml(DataPath & "InvoiceData.XML", XmlWriteMode.WriteSchema)
End If
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
ModuleData: (where your dataset/schema lives)
Code:
Module ModuleData
'Declare your Data DataSet (this is your Data)
Public WithEvents Dset As New DataSet
'Declare your Schema DataSet (Schema only)
Public WithEvents DsetSchema As New DataSet
'Sub Routine to create your Schema
Public Sub SetDsetSchema()
With DsetSchema
.Tables.Add(New DataTable With {.TableName = "People"})
With .Tables("People")
.Columns.Add(New DataColumn With {
.ColumnName = "PersonID",
.AutoIncrement = True,
.DataType = GetType(Integer)})
.Columns.Add("DateCreate", GetType(Date))
.Columns("DateCreate").DefaultValue = Now.Date
.Columns.Add("FullName", GetType(String))
.Columns.Add("Company", GetType(String))
.Columns.Add("Email", GetType(String))
.Columns.Add("Phone", GetType(String))
.Columns.Add("Address", GetType(String))
.Columns.Add("City", GetType(String))
.Columns.Add("State", GetType(String))
.Columns.Add("ZIP", GetType(String))
End With
.Tables.Add(New DataTable With {.TableName = "Invoice"})
With .Tables("Invoice")
.Columns.Add(New DataColumn With {
.ColumnName = "InvoiceID",
.AutoIncrement = True,
.DataType = GetType(Integer)})
.Columns.Add("PersonID", GetType(Integer))
.Columns.Add("FullName", GetType(String))
.Columns.Add("Company", GetType(String))
End With
.Tables.Add(New DataTable With {.TableName = "Items"})
With .Tables("Items")
.Columns.Add(New DataColumn With {
.ColumnName = "ItemID",
.AutoIncrement = True,
.DataType = GetType(Integer)})
.Columns.Add("InvoiceID", GetType(Integer))
.Columns.Add("PersonID", GetType(Integer))
.Columns.Add("ItemDesc", GetType(String))
End With
End With
End Sub
End Module
I suppose I could submit this to the vault as well...
-
Jan 23rd, 2019, 11:21 AM
#9
Thread Starter
Lively Member
Re: DataTable Relation - Want to save several Datatables into one xml
I have created one DataSet (did through Add/New Item/DataSet)
In the DataSet.xsd Window I created my tables and the relation between them)
I am calling WriteXML on that DataSet.
I read your thread on Master/Detail Data-Binding, jmcilhinney, but I am still confused about what I am doing wrong.
I describe exactly what I did in my exemple program attached ()
1. I created my forms, with the objects I need (TextBox, Radio Buttons, ComboBox, etc..)
2. I created my DataSet (going to Add/New Item/DataSet
3. Opened the DataSet Window and Created my Tables (dtproject is the parent one)
4. Created the Relation between tables
5. Back to the form, I clicked in the objects and changed the DataBiding Property (Text to the textbox/and selected value to the combo box) to the correspondent field in the correspondent table. Form1 is realted to Table dtProject. Form2 to dtOption A and Form 3 to DT Option B.
6. Created the Button New, Save and Next in the Form1
7. In the Form2: I have 3 radiobuttons, want to save which one the user chooses.
8. In the Form3: I have a Drag and Drop Function, the user can drag pictureboxes into a Panel. Want to save the pictures were dragged into the panel, and in which order. the function gives me an array, that I want to save, and access later because it will tell the order to open other forms (did not put theses other forms in the exemple to simplify).
In this form, I did not try to save, or created the DataTable properly, because I do not know how. What brings me to the questions below.
8.a ) By the way, how I save an array in my DataTable? Which Fields I need to have in the Datatable.?
I use the same code to New and Save Buttons in Form 1, 2.
Thanks
Last edited by Shaggy Hiker; Jan 23rd, 2019 at 05:03 PM.
-
Jan 23rd, 2019, 11:23 AM
#10
Thread Starter
Lively Member
Re: DataTable Relation - Want to save several Datatables into one xml
Originally Posted by kpmc
I will share with you one of my solutions I recently created, and I just took some time to add some comments. This approach will allow you to 'append' tables and/or columns on the fly and will not overwrite your source XML or data therein. Please read carefully the comments and learn from this solution, do not just copy/paste your way to 6 pack abs
(...)
I will look your code to see if it works somehow for me.. thank you.
-
Jan 23rd, 2019, 02:43 PM
#11
Re: DataTable Relation - Want to save several Datatables into one xml
Well you tried to provide a good description, but you still haven't shown us the necessary code. The description is a good start but we need to see how you've tried to implement it. We can't tell what mistakes you've made in your code without seeing the code. btw - if you've setup a relation between the tables in your dataset designer then you don't need to do it again manually. Did you drag and drop the tables onto the forms from the Datasources Window?
-
Jan 23rd, 2019, 03:43 PM
#12
Thread Starter
Lively Member
Re: DataTable Relation - Want to save several Datatables into one xml
Originally Posted by wes4dbt
Well you tried to provide a good description, but you still haven't shown us the necessary code. The description is a good start but we need to see how you've tried to implement it. We can't tell what mistakes you've made in your code without seeing the code. btw - if you've setup a relation between the tables in your dataset designer then you don't need to do it again manually. Did you drag and drop the tables onto the forms from the Datasources Window?
I sent the application in a DropBox Link!! This one >>
I could not attach the file to the forum post.
I thought this was better than just paste the code since I did some configuration in the Visual Studio DataSet and DataBiding Property of the controls.
Last edited by Shaggy Hiker; Jan 23rd, 2019 at 05:04 PM.
-
Jan 23rd, 2019, 05:07 PM
#13
Re: DataTable Relation - Want to save several Datatables into one xml
That second link gave a 404, but the first one had some things in it that have to be removed. We don't allow linking to compiled code. If you could please remove the bin and Obj folders, then re-post the link, that would be appreciated. The issue with compiled code is that it can hide anything. We've been burned by that before, hence the rule. I removed the second link, since there was something wrong with it, so you'd just get puzzled responses. Likely it was meant to be the same as the other link, but it wasn't.
You don't actually need the bin and obj folders. If you delete them, then they get created automatically by VS the next time you build the project. Therefore, deleting them is painless for all involved.
My usual boring signature: Nothing
-
Jan 23rd, 2019, 05:39 PM
#14
Thread Starter
Lively Member
Re: DataTable Relation - Want to save several Datatables into one xml
Originally Posted by Shaggy Hiker
That second link gave a 404, but the first one had some things in it that have to be removed. We don't allow linking to compiled code. If you could please remove the bin and Obj folders, then re-post the link, that would be appreciated. The issue with compiled code is that it can hide anything. We've been burned by that before, hence the rule. I removed the second link, since there was something wrong with it, so you'd just get puzzled responses. Likely it was meant to be the same as the other link, but it wasn't.
You don't actually need the bin and obj folders. If you delete them, then they get created automatically by VS the next time you build the project. Therefore, deleting them is painless for all involved.
Undestood. I am sorry. Now I was able to attach the file. Example.zip
-
Jan 23rd, 2019, 06:44 PM
#15
Re: DataTable Relation - Want to save several Datatables into one xml
Can't open the example.zip or extract the files. Just post the relevant code.
Last edited by wes4dbt; Jan 23rd, 2019 at 09:27 PM.
-
Jan 24th, 2019, 10:50 AM
#16
Thread Starter
Lively Member
Re: DataTable Relation - Want to save several Datatables into one xml
Originally Posted by wes4dbt
Can't open the example.zip or extract the files. Just post the relevant code.
The code is basically the one in my first message.
Code:
ublic Class Form1
Private myXMLFilePath As String = "C:\Users\veron\Desktop\myXMLFile.xml"
Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
Me.Validate()
Try
DataSet1.WriteXml(myXMLFilePath)
MsgBox("Saved Successfully!")
Catch ex As Exception
MsgBox("Something went wrong. Data not saved. Erro: " & ex.ToString)
End Try
End Sub
Private Sub btnNew_Click(sender As Object, e As EventArgs) Handles btnNew.Click
Try
DataSet1.dtProject.AdddtProjectRow(DataSet1.dtProject.NewdtProjectRow())
DtProjectBindingSource.MoveLast()
Catch message As Exception
MessageBox.Show(message.Message)
DataSet1.dtProject.RejectChanges()
End Try
End Sub
Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
If cmbType.Text = "Option A" Then
Form2.ShowDialog()
Me.Close()
ElseIf cmbType.Text = "Option B" Then
Form3.ShowDialog()
Me.Close()
End If
End Sub
End Class
I am trying to send the application because it is important to get the whole picture in this case. I did not created the DataTables through code, I used the DataSet Designer.
I think I corrupted the file while trying to reduce the size to fit in the attachment. I am sorry. One last attempt.
I deleted the folders bin and obj, upload in my oneDrive account. The file has about 1Mb. I tested download and open this one twice.
https://cocufrjbr-my.sharepoint.com/...wOr-g?e=UB6avM
-
Jan 24th, 2019, 02:18 PM
#17
Re: DataTable Relation - Want to save several Datatables into one xml
Couldn't download it, antivirus found "URL:MAL". Looks like all it contained was your solution file, none of the form files.
Don't see anything major wrong with what you posted but you said the problem was when you saved the data from form2 it over wrote the data from form1. so we need to see form2 code.
-
Jan 24th, 2019, 02:41 PM
#18
Re: DataTable Relation - Want to save several Datatables into one xml
His problem is mostlikely due to the fact he is reading the XML into form2 as well whilst it is still open in Form1. You need to either put 1 dataset in a module like i showed or pass the dataset to from2 as an argument from Form1 to Form2
-
Jan 24th, 2019, 02:56 PM
#19
Thread Starter
Lively Member
Re: DataTable Relation - Want to save several Datatables into one xml
Originally Posted by wes4dbt
Couldn't download it, antivirus found "URL:MAL". Looks like all it contained was your solution file, none of the form files.
Don't see anything major wrong with what you posted but you said the problem was when you saved the data from form2 it over wrote the data from form1. so we need to see form2 code.
It is exactly the same.
-
Jan 24th, 2019, 03:00 PM
#20
Thread Starter
Lively Member
Re: DataTable Relation - Want to save several Datatables into one xml
His problem is mostlikely due to the fact he is reading the XML into form2 as well whilst it is still open in Form1. You need to either put 1 dataset in a module like i showed or pass the dataset to from2 as an argument from Form1 to Form2
In your code your declare a DataSet and the DataTables. Do I need to do it? I thought if I create a DataSet as I did it would work for all forms, but it seems it does not. So intead of using the DataSet Designer, should I define it in the code?.
I am not getting how the DataSet works at all for the whole application.
-
Jan 24th, 2019, 03:15 PM
#21
Re: DataTable Relation - Want to save several Datatables into one xml
The second you drag a dataset from the designer and plop it I can no longer help. I didnt realize that is what you were doing.
-
Jan 24th, 2019, 04:16 PM
#22
Thread Starter
Lively Member
Re: DataTable Relation - Want to save several Datatables into one xml
Originally Posted by kpmc
The second you drag a dataset from the designer and plop it I can no longer help. I didnt realize that is what you were doing.
The thread goal is to help to understand how to save data from different forms into the same DataSet and Save it all in a XML file. I really need to understand how this works.
I described the Form with the DragandDrop to try to get help too in how to save an array into a DataTable. I did not mean to change the subject of this thread, I could ask it in another thread if you think is better.
-
Jan 24th, 2019, 04:31 PM
#23
Re: DataTable Relation - Want to save several Datatables into one xml
The example I put is pretty straightforward. You declare your dataset in a module and you can access that dataset from any form. The schema dataset just allows you to drop in tables and/or columns, so the next time you run the app and the schema dataset has more tables/columns than the Dataset it will just add them. There are several ways to accomplish such a thing, this is just one way. If you do not intend on adding more columns or tables then the procedure can be drastically simplified. To use the dataset in all your form you can create the dataset in a module as I have shown, or when you're calling the show/showdialog method to forms you can pass a refernce to the dataset to the form class being created.
-
Jan 24th, 2019, 05:52 PM
#24
Re: DataTable Relation - Want to save several Datatables into one xml
Originally Posted by veronica.sa
It is exactly the same.
If it's exactly the sane then that's your problem. Your using the same datatable as in form1, of course when you write the dataset to the XML then you'll only have data from one datatable, because that's all you have. When you write the dataset to the XML file it overwrites the previous file, it doesn't append.
-
Jan 24th, 2019, 06:13 PM
#25
Thread Starter
Lively Member
Re: DataTable Relation - Want to save several Datatables into one xml
Originally Posted by wes4dbt
If it's exactly the sane then that's your problem. Your using the same datatable as in form1, of course when you write the dataset to the XML then you'll only have data from one datatable, because that's all you have. When you write the dataset to the XML file it overwrites the previous file, it doesn't append.
I meant the same logic, but with the name of the other DataTable. Look
Code:
Private myXMLFilePath As String = "C:\Users\veron\Desktop\myXMLFile.xml"
Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
Me.Validate()
Try
DataSet1.WriteXml(myXMLFilePath)
MsgBox("Saved Successfully!")
Catch ex As Exception
MsgBox("Something went wrong. Data not saved. Erro: " & ex.ToString)
End Try
End Sub
Private Sub btnNew_Click(sender As Object, e As EventArgs) Handles btnNew.Click
Try
DataSet1.dtOptionA.AdddtOptionARow(DataSet1.dtOptionA.NewdtOptionARow())
DtOptionABindingSource.MoveLast()
Catch message As Exception
MessageBox.Show(message.Message)
DataSet1.dtOptionA.RejectChanges()
End Try
End Sub
-
Jan 24th, 2019, 06:35 PM
#26
Re: DataTable Relation - Want to save several Datatables into one xml
Well this is why I keep saying to post the code and you still haven't posted the necessary code and this is post #26. I need to see what dataset datatables are filled, probably in the form load event. But I'll take another guess, sense this was created by dragging the datatable to the form then the only datatable that is getting filled is dtOptionA, which means that dtProject is empty. so when you write the dataset to the XML file the only thing in the XML file is the dtoptionA data, because that's the only information in the dataset. The dataset on Form1 and the Dataset on form2 are 2 different instances.
-
Jan 24th, 2019, 06:59 PM
#27
Thread Starter
Lively Member
Re: DataTable Relation - Want to save several Datatables into one xml
Originally Posted by wes4dbt
Well this is why I keep saying to post the code and you still haven't posted the necessary code and this is post #26. I need to see what dataset datatables are filled, probably in the form load event. But I'll take another guess, sense this was created by dragging the datatable to the form then the only datatable that is getting filled is dtOptionA, which means that dtProject is empty. so when you write the dataset to the XML file the only thing in the XML file is the dtoptionA data, because that's the only information in the dataset. The dataset on Form1 and the Dataset on form2 are 2 different instances.
What I sent of code is actually what I have of code.
I created the DataSet clicking with right button in the project in the Solution Explorer window, then Add/DataSet. Then I created the DataTables in this DataSet Designer Window.
When I change the DataBiding property o my fields in the form it creates a DataTableBiding and a DataSet1 objects in the form. See pictures.
-
Jan 24th, 2019, 10:07 PM
#28
Re: DataTable Relation - Want to save several Datatables into one xml
I wont have time till tomorrow to look at this. But your not loading the data from the XML file into your dataset.
Anyway try this,
In the form load event put,
Code:
Private Sub Form1_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
'If the file exists, then read the data from the Xml file
' into the DataSet
If My.Computer.FileSystem.FileExists(myXmlFilePath) = True Then
DataSet1.ReadXml(myXmlFilePath)
End If
End Sub
Do this for each form (you have to fill the dataset otherwise you will lose all the data that's in the Xml file the next time you write to it.)
Maybe Shaggy or kpmc can chime in, they have more experience working with Xml file than I do.
-
Jan 25th, 2019, 08:26 AM
#29
Re: DataTable Relation - Want to save several Datatables into one xml
Every aspect of a XML solution is pretty well covered in the example I provided, this includes reading it in, rewriting if schema changes while preserving data, and even DataRelation example. The fact you have neglected to put forth a single bit of effort to make it work without Mr. Wizard tells me I am wasting my time with having you understand.
Cheers
-
Jan 25th, 2019, 08:45 AM
#30
Thread Starter
Lively Member
Re: DataTable Relation - Want to save several Datatables into one xml
Originally Posted by kpmc
Every aspect of a XML solution is pretty well covered in the example I provided, this includes reading it in, rewriting if schema changes while preserving data, and even DataRelation example. The fact you have neglected to put forth a single bit of effort to make it work without Mr. Wizard tells me I am wasting my time with having you understand.
Cheers
I did not neglect your example. I look at it. I see that works, but I am trying to understand how DataSet, the relation between DataTables, Forms and the whole thing works.
I want to learn to solve the problem, not just copy your solution without understanding, which was your advice by the way. I could copy it, change the tables to my purpose and be happy with, it would be the easy way. But I would continue not knowing how the whole thing works.
Why can't I make work using the DataSet Designer? Is doing everything through code is better? In my humble opinion, for a small project it would be great, if I have a few tables only. But my project will have in the end a dozen tables, with lots os columns. Correct me if I am wrong, but I think it is easier to not get lost using the Designer view, than have the Tables all define just in code. I am just starting using DataSet, I do not know yet the answer of each way is better. That is way I asked for help. And I appreciate all the effort of each one that respectfully posted their contributions here. But I am still struggling to understand.
Sincerely regards
-
Jan 25th, 2019, 09:02 AM
#31
Re: DataTable Relation - Want to save several Datatables into one xml
What youre asking is subject to opinion. "Is it better to use Mr. Wizard?" Is the question I read. My opinion is that A pilot can use auto-pilot, but if he doesnt know how to fly he will never land the thing. In technical terms, for me it is much more clear to see the table schema right in front of me to where I can add columns and tables and not have to click the deisgner objects and root through collections, that is a pain. Maybe it is slightly easier through the table designer, but thats just not my style. It adds layers to the solutions you dont need and in my opinion creates a mess. Someone else will have a different opinion, which is best? There is no best...
-
Jan 25th, 2019, 09:15 AM
#32
Thread Starter
Lively Member
Re: DataTable Relation - Want to save several Datatables into one xml
Originally Posted by kpmc
I will share with you one of my solutions I recently created, and I just took some time to add some comments. This approach will allow you to 'append' tables and/or columns on the fly and will not overwrite your source XML or data therein. Please read carefully the comments and learn from this solution, do not just copy/paste your way to 6 pack abs
FormMain: (start form)
Code:
Public Class FormMain
'Path where your XML Data will live
Dim DataPath As String = My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\InvoiceData\"
'Create a var to the DataSet
Dim _Dset As DataSet = ModuleData.Dset
'Create a var to the DataSet that contains schema only
Dim _DsetSchema As DataSet = ModuleData.DsetSchema
Private Sub FormMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Width = Screen.PrimaryScreen.Bounds.Width - 75
Height = Screen.PrimaryScreen.Bounds.Height - 75
CenterToParent()
Try
'If path to souce doesnt exist, create it
If Not My.Computer.FileSystem.DirectoryExists(DataPath) Then My.Computer.FileSystem.CreateDirectory(DataPath)
'If source path and XML exist, read it in "_Dset" (this is your data)
If My.Computer.FileSystem.FileExists(DataPath & "InvoiceData.XML") Then _Dset.ReadXml(DataPath & "InvoiceData.XML")
'Sub routine to create DataSet that contains Schema "_DsetSchema"
ModuleData.SetDsetSchema()
'Sub routine that compares
LoadSchema(_DsetSchema, _Dset)
'Add relations if they do not exist
If Not _Dset.Relations.Contains("InvoicePeopleRelation") Then
_Dset.Relations.Add(New DataRelation("InvoicePeopleRelation",
_Dset.Tables("People").Columns("PersonID"),
_Dset.Tables("Invoice").Columns("PersonID"), True))
End If
If Not _Dset.Relations.Contains("InvoiceItemsRelation") Then
_Dset.Relations.Add(New DataRelation("InvoiceItemsRelation",
_Dset.Tables("Invoice").Columns("InvoiceID"),
_Dset.Tables("Items").Columns("InvoiceID"), True))
End If
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Private Sub LoadSchema(SourceDS As DataSet, TargetDS As DataSet)
'Boolean to flag if there are Schema Changes
Dim SchemaChanged As Boolean = False
Try
'Iterate tables in _DsetSchema
For Each Tbl As DataTable In SourceDS.Tables
'Check if table exists in _Dset (your data), if not add it
If Not TargetDS.Tables.Contains(Tbl.TableName) Then TargetDS.Tables.Add(New DataTable With {.TableName = Tbl.TableName})
'Iterate columns within table iteration
For Each Dcol As DataColumn In Tbl.Columns
'If column doesnt exist then add it
If Not TargetDS.Tables(Tbl.TableName).Columns.Contains(Dcol.ColumnName) Then
TargetDS.Tables(Tbl.TableName).Columns.Add(New DataColumn With {
.ColumnName = Dcol.ColumnName,
.DataType = Dcol.DataType,
.AutoIncrement = Dcol.AutoIncrement})
'If columns were added then schema has changed, set flag to true
SchemaChanged = True
End If
Next
Next
'If schema flad is true,'rewrite' the XML (your data)
If SchemaChanged Then
_Dset.WriteXml(DataPath & "InvoiceData.XML", XmlWriteMode.WriteSchema)
End If
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
ModuleData: (where your dataset/schema lives)
Code:
Module ModuleData
'Declare your Data DataSet (this is your Data)
Public WithEvents Dset As New DataSet
'Declare your Schema DataSet (Schema only)
Public WithEvents DsetSchema As New DataSet
'Sub Routine to create your Schema
Public Sub SetDsetSchema()
With DsetSchema
.Tables.Add(New DataTable With {.TableName = "People"})
With .Tables("People")
.Columns.Add(New DataColumn With {
.ColumnName = "PersonID",
.AutoIncrement = True,
.DataType = GetType(Integer)})
.Columns.Add("DateCreate", GetType(Date))
.Columns("DateCreate").DefaultValue = Now.Date
.Columns.Add("FullName", GetType(String))
.Columns.Add("Company", GetType(String))
.Columns.Add("Email", GetType(String))
.Columns.Add("Phone", GetType(String))
.Columns.Add("Address", GetType(String))
.Columns.Add("City", GetType(String))
.Columns.Add("State", GetType(String))
.Columns.Add("ZIP", GetType(String))
End With
.Tables.Add(New DataTable With {.TableName = "Invoice"})
With .Tables("Invoice")
.Columns.Add(New DataColumn With {
.ColumnName = "InvoiceID",
.AutoIncrement = True,
.DataType = GetType(Integer)})
.Columns.Add("PersonID", GetType(Integer))
.Columns.Add("FullName", GetType(String))
.Columns.Add("Company", GetType(String))
End With
.Tables.Add(New DataTable With {.TableName = "Items"})
With .Tables("Items")
.Columns.Add(New DataColumn With {
.ColumnName = "ItemID",
.AutoIncrement = True,
.DataType = GetType(Integer)})
.Columns.Add("InvoiceID", GetType(Integer))
.Columns.Add("PersonID", GetType(Integer))
.Columns.Add("ItemDesc", GetType(String))
End With
End With
End Sub
End Module
I suppose I could submit this to the vault as well...
In your example, how do you relate the DataTables with the Form Controls (TextBoxes and etc)? How do I say to the program that I want the Text in the TextBox txtFullName, i.g., to be saved in the Column FullName? I could not see that link in the code. I appreciate the attention.
-
Jan 25th, 2019, 09:36 AM
#33
Re: DataTable Relation - Want to save several Datatables into one xml
You would create a bindingsource to the tables.
Code:
Private WithEvents MyTableBS As New BindingSource
Then set its datasource.
Code:
MyTableBS.DataSource = Dset.Tables("MyTable")
Then DataBind the control property to the BindingSource/DataProperty(column name)
Code:
TextBox1.DataBindings.Add("Text", MyTableBS, "MyColumn")
-
Jan 25th, 2019, 09:46 AM
#34
Thread Starter
Lively Member
Re: DataTable Relation - Want to save several Datatables into one xml
I got it. I will try it here. Thanks
-
Jan 25th, 2019, 11:09 AM
#35
Thread Starter
Lively Member
Re: DataTable Relation - Want to save several Datatables into one xml
Originally Posted by kpmc
You would create a bindingsource to the tables.
Code:
Private WithEvents MyTableBS As New BindingSource
Then set its datasource.
Code:
MyTableBS.DataSource = Dset.Tables("MyTable")
Then DataBind the control property to the BindingSource/DataProperty(column name)
Code:
TextBox1.DataBindings.Add("Text", MyTableBS, "MyColumn")
I declared this line in the beginning of my class. It is this right?
Code:
Public Class Form1
Private WithEvents MyTableBS As New BindingSource
.
.
.
End Class
Is this line in the FormLoad event?
Code:
MyTableBS.DataSource = Dset.Tables("MyTable")
I tried to add a row in the Table but the way I was going it giving me an error. How do I add a row in the table? I guess it is more simple than this:
Code:
Private Sub btnNew_Click(sender As Object, e As EventArgs) Handles btnNew.Click
Try
_DSet.MyTable.AddMyTableRow(_DSet.MyTable.NewMyTableRow())
DtOptionABindingSource.MoveLast()
Catch message As Exception
MessageBox.Show(message.Message)
_DSet.MyTable.RejectChanges()
End Try
End Sub
-
Jan 25th, 2019, 11:27 AM
#36
Re: DataTable Relation - Want to save several Datatables into one xml
Yes, Yes, and you dont add a row to the datatable. You 'add' it to the bindingsource which handles all the methods on the datatable for you. Call AddNew on the bindingsource. Any column in the table that is not bound to a textbox or other control can be set by casting the Bindingsource ListItem as datarowview and setting it without control as seen in this example. otherwise the DataBinding on the control will handle that for you.
When you are saving your data (WriteXML) you want to call .EndEdit on the bindingsource first
Code:
With MyTableBS
.AddNew()
'to alter columns that arent bound to textbox
DirectCast(.Current, DataRowView)("ColumnName") = "Whatever"
End With
-
Jan 25th, 2019, 12:09 PM
#37
Thread Starter
Lively Member
Re: DataTable Relation - Want to save several Datatables into one xml
My Module. Created one table so far.
Code:
Module ModuleData
'Declare your Data DataSet (this is your Data)
Public WithEvents Dset As New DataSet
'Declare your Schema DataSet (Schema only)
Public WithEvents DsetSchema As New DataSet
'Sub Routine to create your Schema
Public Sub SetDsetSchema()
With DsetSchema
.Tables.Add(New DataTable With {.TableName = "dtProjext"})
With .Tables("dtProject")
.Columns.Add(New DataColumn With {
.ColumnName = "IDProject",
.AutoIncrement = True,
.DataType = GetType(Integer)})
.Columns.Add("Name", GetType(String))
.Columns.Add("CreationDate", GetType(Date))
.Columns("CreationDate").DefaultValue = Now.Date
.Columns.Add("DiretoryProjeto", GetType(String))
.Columns.Add("TypoProject", GetType(String))
.Columns.Add("Description", GetType(String))
End With
my main form code: In this form I have button to save new project, and one to open a project. It is not working yet, it is not creating the file.
Could you take a look please?. See if I put any code wrong.
' I am using a global variable to keep the file path called myXMLFilePath, wich has the complete path to the file. Basically the user type the name of the file and chose the folder, and it generates the path. this part I did not pasted in the code below
Code:
Public Class MainForm
Private WithEvents dtProjectBindingSource As New BindingSource
'Path where your XML Data will live
' Dim myXMLFilePath As String = My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\InvoiceData\"
'Create a var to the DataSet
Dim _Dset As DataSet = ModuleData.Dset
'Create a var to the DataSet that contains schema only
Dim _DsetSchema As DataSet = ModuleData.DsetSchema
Private Sub MainForm_Load(sender As Object, e As EventArgs) Handles Me.Load
dtProjetoBindingSource.DataSource = _Dset.Tables("dtProjeto")
Try
'Sub routine to create DataSet that contains Schema "_DsetSchema"
ModuleData.SetDsetSchema()
'Sub routine that compares
LoadSchema(_DsetSchema, _Dset)
'Add relations if they do not exist
If Not _Dset.Relations.Contains("ProjetoUnidadeRelation") Then
_DsetSIDE.Relations.Add(New DataRelation("ProjetoUnidadeRelation",
_Dset.Tables("dtProjeto").Columns("IDProjeto"),
_Dset.Tables("dtUnidade").Columns("IDUnidade"), True))
End If
Catch ex As Exception
MsgBox(ex.ToString)
End Try
end sub
Private Sub tsSave_Click(sender As Object, e As EventArgs) Handles tsSave.Click
Me.Validate()
'check if there is a file with the same name in the chosen directory
If File.Exists(myXMLFilePath) Then
If MsgBox("Um arquivo com este nome já existe nesse diretório. Deseja substituir?", MsgBoxStyle.Question + MsgBoxStyle.YesNo, " ") = MsgBoxResult.Yes Then
File.Delete(myXMLFilePath)
End If
dtProjetoBindingSource.EndEdit()
Try
txtName.DataBindings.Add(txtName.Text, dtProjetoBindingSource, "NameProject")
txtCreation.DataBindings.Add(txtCreation.Text, dtProjetoBindingSource, "CreationDate")
txtDirectory.DataBindings.Add(txtDirectory.Text, dtProjetoBindingSource, "Directory")
cmbTypeProject.DataBindings.Add(cmbTypeProject.Text, dtProjetoBindingSource, "TypeProject")
txtDescription.DataBindings.Add(txtDescription.Text, dtProjetoBindingSource, "Description")
_Dset.WriteXml(myXMLFilePath)
MsgBox("Data saved successfully")
Catch ex As Exception
MsgBox("Some error occurred, your data was not saved. Erro: " & ex.ToString)
End Try
End Sub
Private Sub tsOpen_Click(sender As Object, e As EventArgs) Handles tsOpen.Click
Dim ofd As New OpenFileDialog With {
.Title = "Choose a file to open",
.Filter = "XML (*.xml)|*.xml",
.FilterIndex = 0,
.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)}
If ofd.ShowDialog = DialogResult.OK Then
Dim filename As String = ofd.FileName 'extract the address of the file
myXMLFilePath = filename
If (File.Exists(filename)) Then
_Dset.ReadXml(myXMLFilePath)
End If
End If
end sub
-
Jan 25th, 2019, 01:22 PM
#38
Re: DataTable Relation - Want to save several Datatables into one xml
Firstly, comment out all your try blocks. Dont use them unless youre expecting errors in your production build and need to handle errors, there really is no good reason to use them during debug/inception, in my opinion.
I dont have a lot of time currently. What path are you expecting to see the resulting XML. As it stands in the code youre showing the XML should reside in your documents folder in a subfolder named InvoiceData
-
Jan 25th, 2019, 03:36 PM
#39
Re: DataTable Relation - Want to save several Datatables into one xml
I did not neglect your example. I look at it. I see that works, but I am trying to understand how DataSet, the relation between DataTables, Forms and the whole thing works.
I want to learn to solve the problem, not just copy your solution without understanding, which was your advice by the way. I could copy it, change the tables to my purpose and be happy with, it would be the easy way. But I would continue not knowing how the whole thing works.
I see you've decided to change your approach and that's fine with me. You'll learn more about the objects involved. But if you want to learn how to work or setup a Relational Database then Xml is the wrong choice. Xml isn't even a database, it's just a flat file or text file. With some clever manipulation you can create a relational system but why not just use a Relational database Management System to begin with? Like Sql Server, MySql, Sqlite or Access.
As for using a datasource and drag and drop, I use them all the time, it saves a tremendous amount of time. But it will be helpful to learn to create dataset, datatable, commands, relations, datareaders and dataadapters manually.
If you'd like more information on creating a One to Many system I would suggest checking out this video by Beth Massi https://www.youtube.com/watch?v=lpnDUC6ilHY
It's old (think Vs 2008 or VS 2010) but nothing much has changed, except the VS IDE layout. If you like that video, she has many more, just google Beth Massi Forms Over Data videos.
-
Jan 25th, 2019, 03:53 PM
#40
Re: DataTable Relation - Want to save several Datatables into one xml
With some clever manipulation you can create a relational system
Wes, don't confuse the poor lad even more. There is absolutely no manipulation adding a data relation to XML file, there is only adding the DataRelation. It is exactly the same for any connection based DBM. The biggest difference is that with XML it will write the DataRelation to the XML, thus you need not try to add it again. This is why in my example it first checks for relations prior to adding them.
I think you should first ask him/her what the application of the dataset is prior to suggesting a DBM. There are plenty of applications why a simple XML based dataset is spot-on
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|