Click here to Skip to main content
Click here to Skip to main content

Xml Database Demo

, 31 Jul 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
This article walks you through creating a simple windows database with an Xml file as it's datasource.

Xml Database Demo Preview

xmldemorunning.jpg

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.
addnewitem.jpg

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:

customerstable1.jpg

  • 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:

        • AutoIncrement = True
          AutoIncrementSeed=0
          AutoIncrementStep=1
          DataType=System.Int32
          Unique=True

  • 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:

relation.jpg

Finally, click OK to close the Relation window.


Your DataSet should no look like this:

dataset2.jpg

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.
datasources.jpg
    • 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:

frmmaindesign.jpg


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.
bindingnavigator.jpg


  • 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.
componentstray.jpg


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)
  • Add Saving: Now that we have the data loading correctly, let's update the Saving of the data.

    • Right-Click on the Code window
      Select View Designer
      On the BindingNavigator at the top of the form, look for the Save button.
      This button has the picture of a floppy disk on, and should be grayed out.
  • Enable the Save button:

    • Left-Click on the button
      Right-Click on the button
      Select Properties
      Set the Enabled property to "True"

  • Double-Click on the Save button to create the CustomersBindingNavigatorSaveItem_Click event.

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:

code.jpg

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.
  • Notice the following:
  • 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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

CS Rocks
Software Developer DataPrint, LLC
United States United States

Comments and Discussions

 
Questionneed help to create app for account Pinmembersagar panwala19-Oct-13 11:09 
Questionhi PinmemberMember 999349622-Apr-13 16:46 
GeneralMy vote of 5 Pinmemberrajkumar24039-Jan-13 23:48 
Questionget all data from one datatable Pinmembermikko riese19-Dec-12 5:33 
GeneralMy vote of 5 PinmemberOsama El Shammari13-Jul-12 4:28 
Questionadding a combo box Pinmemberlock22727-May-12 20:12 
AnswerRe: adding a combo box PinmemberArmyman12b2-Aug-12 5:42 
GeneralData Base Xml Demo PinmemberVovaZ19-Apr-12 4:44 
QuestionI want to display in reportViewer1 report.rdlc data from this xml file Pingroupelmeksaoui110-Mar-11 1:25 
AnswerRe: I want to display in reportViewer1 report.rdlc data from this xml file PinmemberArmyman12b4-May-11 11:17 
GeneralXml Database Demo Pinmemberbaccko17-Jan-11 8:55 
GeneralI'm coding from Windows Mobile 6.5 and the ribbon not exist PinmemberMember 272884926-Oct-10 21:49 
GeneralMy vote of 5 PinmemberMember 272884926-Oct-10 21:46 
GeneralIt's great Pinmembermindserve27-Sep-10 16:04 
Generalthought I'd simplify question Pinmembermla_ca52010-Aug-10 14:29 
GeneralThis is Great...thanks! A question about Dataset Pinmembermla_ca5204-Jun-10 13:57 
QuestionHow do I execute SQL statements on the db??? PinmemberKyferEz13-Mar-10 7:16 
GeneralOne thing missing ... PinmemberJunner20064-Jan-10 17:30 
GeneralRe: One thing missing ... PinmemberVB Rocks5-Jan-10 8:19 
GeneralRe: One thing missing ... PinmemberJunner20065-Jan-10 8:59 
GeneralA question Pinmembervmts28-Sep-09 1:39 
GeneralRe: A question PinmemberVB Rocks28-Sep-09 4:41 
GeneralRe: A question Pinmembervmts29-Sep-09 5:24 
QuestionThanks, and a question Pinmembermohammed_taher5-Jul-09 8:58 
AnswerRe: Thanks, and a question PinmemberVB Rocks13-Aug-09 4:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.1411023.1 | Last Updated 31 Jul 2008
Article Copyright 2008 by CS Rocks
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid