Business
Editor's Note
This article originally appeared in our sister publication, Builder Australia, as part of the Ask Chuck column.
I want to create a Windows Application that allows a user to enter/edit/access data that he or she creates without connecting to a remote database. I want all of the data accessing to be local. Is this possible? I was thinking maybe using XML files to store the data and read the data.
I hear this question quite often and have answered a related question in March. In that article I showed one Builder reader how to persist data from a remote RDBMS to the local hard drive and then read that persisted data back into the application. Your question however calls out two different areas that I did not cover in that answer:
How do you insert/update this local data?
How do you create a local data store without first starting with an existing table in an RDBMS?
Use XML as a local data store
Starting with the easiest part of your question first, what format do you use to store the data in? You mention XML files—and I would wholeheartedly agree with this. Visual Studio .NET (or most any XML editor) makes it easy to create the “tables” (an XML schema in XML lingo) for your XML files.
To do this in Visual Studio.NET, start with an existing project and add a new item of type dataset (Figure A). This generates a dataset schema and opens the dataset designer with which you can add columns (elements) via a right-click. In Figure B, I have added 4 elements to my dataset that will simulate my customer table.
At this point, I have enough to create a simple application that binds to the XML table (which is an XML schema) and displays the table, which is empty right now. By placing the following code in the form_load event of a Windows application, the customer columns (XML elements) will be displayed in a datagrid using ADO.NET.
Dim ds As New System.Data.DataSet
ds.ReadXml("..\dataset1.xsd")
DataGrid1.DataSource = ds.Tables(0)
Using the Row object
Now, for the trickier part of your question, inserting new data into a local XML file. To do this, I will continue using ADO.NET but introduce a new class called the Row object to do the work of appending the text into the correct elements of the XML document (Figure C).
In the code below, I have created a Row object then set the columns to some sample data for purposes of illustration, and finally added the row into the dataset.
Dim ds As New Dataset1 ‘Use the schema created with the column definitions
‘ds.ReadXml("..\mydata.xml")
‘After the first time this code is run you can uncomment out the line above to have the
‘application append new data to the end of the mydata.xml dataset
Dim drCurrent As DataRow
' Obtain a new DataRow object from the DataTable.
drCurrent = ds.Tables(0).NewRow
drCurrent("Customer_id") = "9"
drCurrent("Customer_address") = Now.ToLongTimeString
drCurrent("Customer_name") = "Charles Sterling"
drCurrent("Customer_telephone") = "0405179872"
ds.Tables(0).Rows.Add(drCurrent)
Now that the new data is in the dataset, in memory, what is needed is to write it to the XML file on the hard drive and finally update the datagrid to display the current data. To write/save files in the .NET Framework, you use the classes found in the System.IO namespace as shown below.
Dim sw As System.IO.StreamWriter = New System.IO.StreamWriter("..\mydata.xml")
sw.Write(ds.GetXml.ToString) '//place the xml into the text file
sw.Flush()
sw.Close()
Finally, to display the data back to the user, all that is required is to clear the existing data and read in the dataset and display it (Figure D).
ds.Clear() 'get rid of the old data from the dataset
'display the updated data in the datagrid
ds.ReadXml("..\mydata.xml")
DataGrid1.DataSource = ds.Tables(0)
Your question also mentions wanting to know how to update the XML file. Once again the ADO.NET classes, specifically the Row object, will do this work for you. The code given below walks through all the records in the dataset and updates the Customer_Id to 11.
(Note: This does not persist the updates back to the local XML file; to update the data on the hard drive you would need to use System.IO classes as shown above.)
ds.Clear() 'get rid of the old data from the dataset
'display the updated data in the datagrid
ds.ReadXml("..\mydata.xml") 'populate the dataset
For Each drCurrent As DataRow In ds.Tables(0).Rows
drCurrent.BeginEdit()
drCurrent("Customer_id") = 11
drCurrent.EndEdit()
Next
DataGrid1.DataSource = ds.Tables(0)
Hope this helps and until next time,
Chuck
This article originally appeared in our sister publication, Builder Australia, as part of the Ask Chuck column.
I want to create a Windows Application that allows a user to enter/edit/access data that he or she creates without connecting to a remote database. I want all of the data accessing to be local. Is this possible? I was thinking maybe using XML files to store the data and read the data.
I hear this question quite often and have answered a related question in March. In that article I showed one Builder reader how to persist data from a remote RDBMS to the local hard drive and then read that persisted data back into the application. Your question however calls out two different areas that I did not cover in that answer:
How do you insert/update this local data?
How do you create a local data store without first starting with an existing table in an RDBMS?
Use XML as a local data store
Starting with the easiest part of your question first, what format do you use to store the data in? You mention XML files—and I would wholeheartedly agree with this. Visual Studio .NET (or most any XML editor) makes it easy to create the “tables” (an XML schema in XML lingo) for your XML files.
| Figure A |
 |
| Using Visual Studio .NET to add a Dataset |
To do this in Visual Studio.NET, start with an existing project and add a new item of type dataset (Figure A). This generates a dataset schema and opens the dataset designer with which you can add columns (elements) via a right-click. In Figure B, I have added 4 elements to my dataset that will simulate my customer table.
| Figure B |
 |
| Adding elements to the dataset for the customer table |
At this point, I have enough to create a simple application that binds to the XML table (which is an XML schema) and displays the table, which is empty right now. By placing the following code in the form_load event of a Windows application, the customer columns (XML elements) will be displayed in a datagrid using ADO.NET.
Dim ds As New System.Data.DataSet
ds.ReadXml("..\dataset1.xsd")
DataGrid1.DataSource = ds.Tables(0)
Using the Row object
Now, for the trickier part of your question, inserting new data into a local XML file. To do this, I will continue using ADO.NET but introduce a new class called the Row object to do the work of appending the text into the correct elements of the XML document (Figure C).
| Figure C |
 |
| Empty form |
In the code below, I have created a Row object then set the columns to some sample data for purposes of illustration, and finally added the row into the dataset.
Dim ds As New Dataset1 ‘Use the schema created with the column definitions
‘ds.ReadXml("..\mydata.xml")
‘After the first time this code is run you can uncomment out the line above to have the
‘application append new data to the end of the mydata.xml dataset
Dim drCurrent As DataRow
' Obtain a new DataRow object from the DataTable.
drCurrent = ds.Tables(0).NewRow
drCurrent("Customer_id") = "9"
drCurrent("Customer_address") = Now.ToLongTimeString
drCurrent("Customer_name") = "Charles Sterling"
drCurrent("Customer_telephone") = "0405179872"
ds.Tables(0).Rows.Add(drCurrent)
Now that the new data is in the dataset, in memory, what is needed is to write it to the XML file on the hard drive and finally update the datagrid to display the current data. To write/save files in the .NET Framework, you use the classes found in the System.IO namespace as shown below.
Dim sw As System.IO.StreamWriter = New System.IO.StreamWriter("..\mydata.xml")
sw.Write(ds.GetXml.ToString) '//place the xml into the text file
sw.Flush()
sw.Close()
Finally, to display the data back to the user, all that is required is to clear the existing data and read in the dataset and display it (Figure D).
ds.Clear() 'get rid of the old data from the dataset
'display the updated data in the datagrid
ds.ReadXml("..\mydata.xml")
DataGrid1.DataSource = ds.Tables(0)
| Figure D |
 |
| XML dataset displayed in a form |
Your question also mentions wanting to know how to update the XML file. Once again the ADO.NET classes, specifically the Row object, will do this work for you. The code given below walks through all the records in the dataset and updates the Customer_Id to 11.
(Note: This does not persist the updates back to the local XML file; to update the data on the hard drive you would need to use System.IO classes as shown above.)
ds.Clear() 'get rid of the old data from the dataset
'display the updated data in the datagrid
ds.ReadXml("..\mydata.xml") 'populate the dataset
For Each drCurrent As DataRow In ds.Tables(0).Rows
drCurrent.BeginEdit()
drCurrent("Customer_id") = 11
drCurrent.EndEdit()
Next
DataGrid1.DataSource = ds.Tables(0)
Hope this helps and until next time,
Chuck