Xml Database Demo Preview
Introduction
I ran across a request for a walk-through of creating a database with an Xml file as it's datasource. Since I decided to create the walk-through, I thought it could be useful to other new developers who might have similar questions about how to do it. So here its - from start to finish, or "A-Z" as you might say.
Background
This article walks you through creating a Strongly-Typed DataSet in Visual Studio that will be used to store data, to read data from an Xml file, and to write data to an Xml file.
Additionally, this article walks you through creating the user interface for presenting the data.
Creating the Project
To begin our demo, let's create the project:
- Start Visual Studio
Click on the File menu | New | Project…
When the "New Project" window opens:
Expand the Visual Basic node
Click on "Windows Application"
Name the Project "XmlDatabaseDemo"
Then click the OK button.
Adding the DataSet
When the project opens, view the Solution Explorer:
- View menu | Solution Explorer
Next, add a DataSet to the project:
- Click on the Project menu | Add New Item…
Select a DataSet.
Change the name to "DsXmlDbDemo"
(this stands for "DataSet Xml Database Demo")
Click the Add button.
The DataSet designer will immediately open after you add the DataSet to your project.
Adding the Customers DataTable
- Right-Click on the DataSet designer.
Select Add | DataTable
The DataTable will automatically be named "DataTable1"
Click on the word "DataTable1"
You should be able to edit the name.
Change the name to "Customers"
Now that we have added a Customers DataTable to our DataSet, we need to add some columns:
Add Columns to the Customers table:
- Add an AutoId column:
Right-Click on the Customers DataTable
Select Add | Column
Change the text of the new Column from "Column1" to "CustomerId"
- Now that we've added our id column, we need to make it an auto-incrementing column, so it will be a true id column:
- Left-Click on the "CustomerId" column that we just added, to select it.
Right-Click on the "CustomerId" column
Select Properties from the menu.
This should have opened the properties window.
Set the following properties in the properties window:
- AutoIncrement = True
AutoIncrementSeed=0
AutoIncrementStep=1
DataType=System.Int32
Unique=True
When you're done, it should look like the following:
- Next, add columns for "Name", "Address1", "Address2", "City", "St", "Zip" using the following instructions for each:
- Right-Click on the DataTable
Select Add | Column
Change the new column name to one of the column names listed above.
Repeat for each column.
Adding the Orders DataTable
Now that we have the Customer's DataTable added to our DataSet, let's add an Orders Table. The Orders table will be used to track the orders for our customers.
- Right-Click on the DataSet designer.
Select Add | DataTable
Rename the DataTable to "Orders"
Add Columns to the table:
- Add an AutoId column:
Right-Click on the Orders DataTable
Select Add | Column
Change the text of the new Column from "Column1" to "OrderId"
- Now that we've added our id column, we need to make it an auto-incrementing column, so it will be a true id column.
- Left-Click on the "OrderId" column that we just added, to select it.
Right-Click on the "OrderId" column
Select Properties from the menu.
This should have opened the properties window.
Set the following properties in the properties window:
- Add a CustomerId column:
Because each of the orders that gets added to the Orders DataTable belongs to a Customer, we need to create a column for the CustomerId. The column will actually be a "Foreign Key", typically denoted with "FK", although we won't actually use "FK" in the name of the column.
- Right-Click on the Orders DataTable
Select Add | Column
Change the text of the new Column to "CustomerId"
Left-Click on the new CustomerId column to select it.
Right-Click on the new CustomerId column
Select Properties.
Set the DataType property to "System.Int32"
- Add an Item column
- Right-Click on the Orders DataTable
Select Add | Column
Rename the column to "Item"
- Add a Quantity column
- Right-Click on the Orders DataTable
Select Add | Column
Rename the column to "Quantity"
Left-Click the new "Quantity" column to select it
Right-Click the column
Select Properties.
Set the DataType property to "System.Int32"
- Add a Price column
- Right-Click on the Orders DataTable
Select Add | Column
Rename the column to "Price"
Left-Click the new "Price" column to select it
Right-Click the column
Select Properties.
Set the DataType property to "System.Double"
- Add a Total column
- Right-Click on the Orders DataTable
Select Add | Column
Rename the column to "Total"
Left-Click the new "Total" column to select it
Right-Click the column
Select Properties.
Set the following properties:
DataType = "System.Double"
Expression = "Quantity*Price"
(this column will automatically multiply the Quantity and Price)
Adding the DataTable Relation
Now that we have a Customers table and an Orders table in our DataSet, we need to create a relationship between them. This will be done using the CustomerId column in the Customers DataTable, and the CustomerId column in the Orders DataTable.
- Left-Click (and hold down) on the CustomerId column in the Customers DataTable.
Drag your mouse over the CustomerId column in the Orders DataTable, then let go.
(This will bring up the Relation window.)
- Configure the Relation window as follows:
- Select "Both Relation and Foreign Key Constraint
Update Rule: Cascade
Delete Rule: Cascade.
The window should look like this:
Finally, click OK to close the Relation window.
Your DataSet should no look like this:
Save and Build your project (Build menu | Build Solution).
There should be no errors.
Close the DataSet Designer Window.
Designing the Form
Go to your form in the designer view.
- Let's change some properties on your form so it will look and respond a little better:
- Right-Click on your form.
Select Properties.
In the Properties window, set the following Properties:
- BackColor: White
StartPosition: CenterScreen
Text: Xml Database Demo
- Now let's add some controls to our form so we can view the data:
- Open the DataSources window:
- Data menu | Show DataSources
Notice the "DsXmlDbDemo" item in the DataSources window.
This is the new DataSet we just created.
- Next, expand the Customers node.
You should see a list of the columns from the Customers DataTable
Notice the Orders DataTable listed below the columns.
You should actually see 2 Orders DataTables.
The Orders DataTable that appears under the columns in the Customers DataTable actually represents the related records from the Orders DataTable.
This is the Orders DataTable we are going to use.
|
 |
- Adding Customer columns to the form:
- To add the columns to the form, just follow these steps for each column:
- Left-Click (and hold down) on the CustomerId column in the DataSources window.
- Drag the column onto the form, and drop it.
- Repeat this for all columns, including the Orders DataTable.
Make sure you drag the Orders DataTable that is just under the columns of the Customers DataTable.
Position the controls on your form however you like. Here's an example of how mine turned out:
Now you might have noticed that Visual Studio automatically added a few things for us:
- There is now a BindingNavigator at the top of the form. This is for navigating the records, and for adding and deleting records.
- There are also quite a few items in the components tray under our form. These will be used by Visual Studio to store and manage our data.
Adding the Code
- Double-Click on the blue title bar on our Form.
This will create a Form_Load event.
- Right above the new Form_Load event that was created, add a module-level variable to store the file path to the Xml file that will store our data:
Private mXmlFilePath As String = "c:\Temp\XmlDatabaseDemo.xml"
- Next, in the Form_Load event, add a line of code that checks to see if the Xml file exists, and if it does, then use the ReadXml method of the DsXmlDbDemo dataset to read the data from the file and load it into the DataSet:
If My.Computer.FileSystem.FileExists(mXmlFilePath) = True Then _
DsXmlDbDemo.ReadXml(mXmlFilePath)
Finally, just add these lines of code to the save event to save the data to the file:
Me.Validate()
CustomersBindingSource.EndEdit()
OrdersBindingSource.EndEdit()
DsXmlDbDemo.WriteXml(mXmlFilePath)
These methods commit the data to the Customers DataTable and the Orders DataTable
- As a note: You could put these lines of code in the Form_Closing event instead, and only write the data out to the Xml file 1 time. A down side to this is that if the application closes unexpectedly (because of an error, network connection failure, power-outage, etc.), then the user will loose all of the data they had entered. So it's up to you.
Add 1 more piece of code to commit any pending Customer entries before Order entries are made.
- Right-Click on the Code window.
Select View Designer.
Left-Click on the Orders grid (DataGridView) that is on the form.
Right-Click the grid.
Select Properties.
- View the events for the grid:
In the Properties window, look at the very top for the yellow lightening bolt, and
Click it to view the events for the grid.
Find the "Enter" method.
Double-Click on the word "Enter" to create the OrdersDataGridView_Enter event.
Add the following line of code to this event:
CustomersBindingSource.EndEdit()
The final code should look like this:
Running the Program
- When the program runs, First click the yellow plus sign (+) on the BindingNavigator on the top of the form to add your first record.
- Add a customers to your form.
- Add some orders for each customer.
- The OrderId number is automatically filled in. This is because it is an auto-incrementing number.
- The CustomerId number is automatically filled in with the current Customer's CustomerId number.
- Total column automatically calculates the Quantity and Price when your leave the row.
- Click the Save button to save both the Customer and the orders.
- Close the program, and then run the program again to verify that your entries are there!
Conclusion
I've included the source code for this tutorial for you. So, if you have any questions, take a look at it.
I hope it's helpful to you!
VBRocks
2008 MS Visual Basic MVP
www.VisualBasicRocks.com
