ADO.NET: File-Based Databases

	File-Based Databases

Introduction to Databases

Overview of Databases

In Lesson 1, to get a list of items, we created and stored it in a variable-based list. The user had no way of storing items in the computer. This meant that once the computer was shut down or the application was closed, the information created while using the application was lost. In list-oriented business applications, to make sure that the same information is not entered over and over again, any new information can be saved and stored somewhere. Such a type of file-based application can be used to create new items, save them, and process them the next time they are needed. This is the basis of databases.

A database is a information created as a list and stored somewhere so the list can be better managed. This means that the person using the database should be able to add information to the database, to retrieve information from the database, to change the information stored in the database, to manipulate it, to manage the database as an ensemble, and to save new or changed information.

Types of Databases

There are various types of databases used with different goals. The .NET Framework provides various means of creating and managing such various types of databases. Those we will review are categorized as follows:

File-Based Databases: A database is referred to as file-based, sometimes called a flat file database, when it uses the regular techniques of file processing. In other words, its information is stored in one or more regular Windows files with a familiar or unfamiliar file extension. For example, you can create text files (files that have the .txt extension), use them to save information, then open those files when needed and process them as regularly as you would a normal text file. In the same way, you can use any other file extension of your choice to store the information of a file. With a file-based database, you decide what extension(s) your file(s) would have and how to use it. You would be completely in charge and you can prevent other applications from using or accessing the files.
Advantages: File-based databases can be considered the easiest to create because they rely on normal techniques of file processing. Another positive point is that, because the .NET Framework provides an impressive support for file processing, file-based databases are easy to create.
Disadvantages: File-based databases are not relation-oriented. Because the information is stored in regular Windows files, you must constantly remember where a particular piece of information is stored. Since information is stored in distinguished files, a file can also be corrupted if not used or stored safely

File-based databases will be the subject of this lesson
XML-Based Databases: XML is a technique of describing data. By using XML, you can create information and save it in one or more files that can be accessed by any programming environment that can read and manipulate XML. Normally, the files of this type of database have the .xml extension and they can be universally used. If you use the .NET Framework to create your file(s), you would benefit from an impressive library that is highly XML-oriented but the files as normal XML entities can be accessed by any other application.
Advantages: XML files are the easiest files to create, easier than objects of file-based databases. An XML file can be created from one application by a certain person and the same file can be manipulated by another person using a completely different application without any danger of corrupting the file. This also means that different people and different applications from different operation systems can access the same XML file and manipulate it at will.
Disadvantages: Because XML has standard rules, you must first study XML before being able to create valid XML files. Because an XML file is a regular text-based database, an informed user can open it with any text editor and possibly corrupt it.

We studied XML in the previous lessons. Starting here and in future lessons, we will further use XML
Relational Databases: This is the default concept of a database. A relational database is an group of data-oriented objects (mostly tables) that act as an ensemble so information can flow from one object to another using predefined relationships.
Advantages: Relational databases provide the ideal techniques of creating, storing, and manipulating information. The relationships among objects are created inside of the objects (tables) that make up the database, then the database engine ensures that information flows from one object to another. This reduces many mistakes that would be produced during data entry and that result in duplicated information
Disadvantages: Relational databases have many rules that you, the database developer, should learn and master. Besides studying the techniques of creating a database using a library such as the .NET Framework, you would also need to learn an additional language called SQL

Introduction to File-Based Databases

As mentioned above, a file-based database consists of creating one or more files that can be used as a database. To do this, you can create an array of a list of objects as we learned in Lesson 1. The problem with arrays is that they have a fixed size and you must know from the beginning the number of items that can created in the list. The alternative is to use a linked-list. As seen in Lesson 1, this is easily supported through the ArrayList class.

Practical Learning: Introducing File-Based Databases

Start a new Windows Forms Application named DepartmentStore3
To add a new form, on the main menu, click Project -> Add Windows Form...
In the Templates list of the Add New Item dialog box, make sure Windows Form is selected.
Set the Name to NewStoreItem and press Enter

Design the form as follows:

Control	Name	Text	Other Properties
Label		Item #:
TextBox	txtItemNumber
Label		Item Name:
TextBox	txtItemName
Label		Size:
TextBox	txtSize
Label		Unit Price:
TextBox	txtUnitPrice		AlignText: Right
Button	btnAddItem	Add Item
Button	btnClose	Close
Form			AcceptButton: btnAddItem MaximizeBox: False StartPosition: CenterScreen

Arrange the Tab Order (View . Tab Order) so the Item # text box is the last in the sequence:

Double-click an unoccupied area of the form and implement its Click event as follows:

Dim lstStoreItems As ArrayList

Private Sub NewStoreItem_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        lstStoreItems = New ArrayList
End Sub

In the Class Name combo box, select btnClose
In the Method Name combo box, select Click

Implement the event as follows:

Private Sub btnClose_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Close()
End Sub

Display the first form (Form1.vb [Design])
Add a button to it with the following properties:
Text: New Store Item
Name: btnNewStoreItem

Double-click the New Store Item button and implement its Click event as follows:

Private Sub btnNewStoreItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNewStoreItem.Click
        Dim frmNewItem As New NewStoreItem

        frmNewItem.Show()
End Sub

Add a new Windows Form
Set its Name to StoreInventory and press Enter

Design the form as follows:

Control	Name	Text	Other Properties
DataGrid			Auto Format: Colorful 3
Button	bntLoad	Load
Button	btnClose	Close
Form			AcceptButton: bntLoad MaximizeBox: False StartPosition: CenterScreen

Double-click the Close button and implement its Click event as follows:

Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Close()
End Sub

Display the first form (Form1.vb [Design])
Add a button to it with the following properties:
Text: Store Inventory
Name: btnStoreInventory
Double-click the Store Inventory button

Implement its Click event as follows:

Private Sub btnStoreInventory_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStoreInventory.Click
        Dim frmInventory As New StoreInventory

        frmInventory.Show()
End Sub

Add a new Windows Form and name it CustomerOrder

Design the form as follows:

Control	Name	Text	Other Properties
Label		Item #
Label		Description
Label		Size
Label		Unit Price
Label		Qty
Label		Sub-Total
TextBox	txtItemNumber1
TextBox	txtDescription1
TextBox	txtSize1
TextBox	txtUnitPrice1	0.00	TextAlign: Right
TextBox	txtQuantity1	0	TextAlign: Right
TextBox	txtSubTotal1	0.00	TextAlign: Right
TextBox	txtItemNumber2
TextBox	txtDescription2
TextBox	txtSize2
TextBox	txtUnitPrice2	0.00	TextAlign: Right
TextBox	txtQuantity2	0	TextAlign: Right
TextBox	txtSubTotal2	0.00	TextAlign: Right
TextBox	txtItemNumber3
TextBox	txtDescription3
TextBox	txtSize3
TextBox	txtUnitPrice3	0.00	TextAlign: Right
TextBox	txtQuantity3	0	TextAlign: Right
TextBox	txtSubTotal3	0.00	TextAlign: Right
TextBox	txtItemNumber4
TextBox	txtDescription4
TextBox	txtSize4
TextBox	txtUnitPrice4	0.00	TextAlign: Right
TextBox	txtQuantity4	0	TextAlign: Right
TextBox	txtSubTotal4	0.00	TextAlign: Right
TextBox	txtItemNumber5
TextBox	txtDescription5
TextBox	txtSize5
TextBox	txtUnitPrice5	0.00	TextAlign: Right
TextBox	txtQuantity5	0	TextAlign: Right
TextBox	txtSubTotal5	0.00	TextAlign: Right
TextBox	txtItemNumber6
TextBox	txtDescription6
TextBox	txtSize6
TextBox	txtUnitPrice6	0.00	TextAlign: Right
TextBox	txtQuantity6	0	TextAlign: Right
TextBox	txtSubTotal6	0.00	TextAlign: Right
TextBox	txtItemNumber7
TextBox	txtDescription7
TextBox	txtSize7
TextBox	txtUnitPrice7	0.00	TextAlign: Right
TextBox	txtQuantity7	0	TextAlign: Right
TextBox	txtSubTotal7	0.00	TextAlign: Right
TextBox	txtItemNumber8
TextBox	txtDescription8
TextBox	txtSize8
TextBox	txtUnitPrice8	0.00	TextAlign: Right
TextBox	txtQuantity8	0	TextAlign: Right
TextBox	txtSubTotal8	0.00	TextAlign: Right
Label		This order will be saved in
DateTimePicker	dtpSaleDate		Format: Custom CustomFormat: ddd dd MMM yyyy
Label		Total Order:
TextBox	txtTotalOrder	0.00	TextAlign: Right
Button	btnSave	Save and Start a New Order
Button	btnClose	Close

Double-click the Close button and implement its Click event as follows:

Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Close()
End Sub

Display the first form (Form1.vb [Design])
Add a button to it with the following properties:
Text: New Customer Order
Name: btnNewOrder
Double-click the New Customer Order button

Implement its Click event as follows:

Private Sub btnNewOrder_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNewOrder.Click
        Dim frmOrder As New CustomerOrder

        frmOrder.Show()
End Sub

Add a new Windows Form and name it DailySales

Design the form as follows:

Control	Name	Text	Other Properties
Label		View Sales For
DateTimePicker	dtpSaleDate		Format: Custom CustomFormat: ddd dd MMM yyyy
DataGrid			Auto Format: Colorful 3
Button	btnClose	Close
Form			MaximizeBox: False StartPosition: CenterScreen

Double-click the Close button and implement its Click event as follows:

Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Close()
End Sub

Display the first form (Form1.vb [Design])
Add a button to it with the following properties:
Text: View Daily Sales
Name: btnDailySales
Double-click the View Daily Sales button

Implement its Click event as follows:

Private Sub btnDailySales_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDailySales.Click
        Dim frmSales As New DailySales

        frmSales.Show()
End Sub

Return to the main form. Add a new button to it and set its properties as follows:
Text: Close
Name: btnClose

Double-click the Close button and implement its Click event as follows:

Private Sub btnClose_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnClose.Click
        End
End Sub

Save all

File Processing

Introduction

A file-based database is primarily a technique of storing values in files so they can be retrieved when needed. Based on this, the files are created like any other and they can have any extension of your choice. When a file is created, its contents can be stored in a portable medium such as a hard disc, a floppy disc, a compact disc, or any valid and supported type of storage.

File processing is support in the .NET Framework through the System.IO namespace that contains many different classes to handle almost any type of file operation you may need to perform.

The Application's Directory

When creating a file-base application, you should know where the files are located, even if you don't explicitly communicate this to the user. This means that you may need to create a folder that would hold the files of your database. In some cases you can create a new folder on the C: drive, under the the Program Files folder or in the My Documents folder. In some other cases, you may use a network shared folder as the repository of users files. These are decisions you can make when planning the deployment.

Once the application has been installed, you can allow the user to directly save the files to default or selected folder without being prompted to specify the name or path of the file.

Saving a File

Before saving a file, you may first want to check its existence. This can be done by calling the File.Exists() method. Once you have decided to save a file, you can specify the type of operation using the FileMode option of the Stream-based class you are using. The options of the FileMode are FileMode.Append, FileMode.Create, FileMode.CreateNew, FileMode.Open, FileMode.OpenOrCreate, and FileMode.Truncate. The FileAccess option allows you to specify the type of access the user will need when saving the file. The options are FileAccess.Write, FileAccess.Read, and FileAccess.ReadWrite. The FileShare allows you to decide whether or how other users (actually, processes) can access the file while it is being accessed. The options of the FileShare are FileShare.Inheritable, FileShare.None, FileShare.Read, FileShare.Write, and FileShare.ReadWrite.

Object Serialization

Binary Serialization

The .NET Framework provides all means of serializing any managed class through a concept referred to as binary serialization. To proceed with object serialization, you must first specify the object you would use. As with any other program, you can either use one of the classes built-in the .NET Framework or you can use your own programmer-created class. If you decide to use your own class, you can specify whether the whole class or just some parts of the class can be saved.

Binary serialization consists of saving an object. This object is usually a class that either you create or is provided through the .NET Framework. For a class to be serializable, it must be marked with the Serializable attribute. This means that, when checking the MSDN documentation, any class you see with this attribute is already configured to be serialized. This is the case for almost all list-oriented classes such as Array, ArrayList, etc, including many of the classes we will use when we start studying ADO .NET and relational databases.

If you create your own class and want to be able to save values from its variables, you can (must) mark the class with the Serializable attribute. To do this, type [Serializable] before starting to create the class.

The Serializable attribute informs the compiler that values from the class can be serialized. When creating a serializable class, you have the ability to specify whether all of the member variables of the class can be serialized or just some of them.

Practical Learning: Creating a Serializable Class

To add a new project, on the main menu, click File -> Add Project -> New Project...
In the Project Types list of the Add New Project dialog box, make sure Visual C# Projects is selected
In the Templates list, click Class Library
Change the Name to StoreItemR2
Click OK

Change the file as follows:

<Serializable()> Public Class CStoreItem
    Private nbr As String
    Private nm As String
    Private sz As String
    Private uprice As Double

    Public Sub New()
        nbr = ""
        nm = ""
        sz = ""
        uprice = 0.0
    End Sub

    Public Sub New(ByVal number As String, ByVal name As String, _
                   ByVal size As String, ByVal price As Double)
        nbr = number
        nm = name
        size = sz
        uprice = price
    End Sub

    Public Property ItemNumber() As String
        Get
            Return nbr
        End Get
        Set(ByVal Value As String)
            nbr = Value
        End Set
    End Property

    Public Property ItemName() As String
        Get
            Return nm
        End Get
        Set(ByVal Value As String)
            nm = Value
        End Set
    End Property

    Public Property ItemSize() As String
        Get
            Return sz
        End Get
        Set(ByVal Value As String)
            sz = Value
        End Set
    End Property

    Public Property UnitPrice() As Double
        Get
            Return uprice
        End Get
        Set(ByVal Value As Double)
            uprice = Value
        End Set
    End Property
End Class

To create the library, in the Solution Explorer, right-click the StoreItemR2 node and click Build
To get a reference of this library to the department store project, in Solution Explorer, under DepartmentStore3, right-click References and click Add Reference...
In the Add Reference dialog box, click the Projects property page and make sure the StoreItemR2 project is selected:
Click Select and click OK

Change the Load event of the New Store Item form as follows:

Private Sub NewStoreItem_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        lstStoreItems = New ArrayList

        Dim tmeNow As Date = Now

        ' Get ready to create a random number
        Dim strItemNumber As String = "000000"

        Dim mls As Integer = tmeNow.Millisecond
        ' Generate two random numbers between 100 and 999
        Dim rndNumber As New Random(mls)
        Dim NewNumber1 As Integer = rndNumber.Next(100, 999)
        Dim NewNumber2 As Integer = rndNumber.Next(100, 999)
        ' Create an item number from the random numbers
        strItemNumber = NewNumber1.ToString() + "-" + NewNumber2.ToString()

        ' Display the created item number in the Item # text box
        Me.txtItemNumber.Text = strItemNumber
End Sub

Display the Customer Order form. Right-click it and click View Code
In the top section of the file, type Imports StoreItemR2 as the first line of the file

Create the following function above the End Class line:

Private Function LocateStoreItem(ByVal ItemNumber As String) As StoreItemR2.CStoreItem
        Dim item As New StoreItemR2.CStoreItem
        Dim i As Integer

        ' Get a list of all items in the store
        For i = 0 To lstAvailableItems.Count Step 1
            ' Get a reference to the current item
            item = lstAvailableItems(i)

            ' If the Item Number of the current item matches the argument,
            ' then return it
            If item.ItemNumber = ItemNumber Then
                Return item
            End If
        Next

        ' If the item was not found, then return nothing;
        Return Nothing
    End Function

In the same way, create the following function above the End Class line:

Private Sub CalculateTotalOrder()
        Dim subTotal1 As Double
        Dim subTotal2 As Double
        Dim subTotal3 As Double
        Dim subTotal4 As Double
        Dim subTotal5 As Double
        Dim subTotal6 As Double
        Dim subTotal7 As Double
        Dim subTotal8 As Double
        Dim orderTotal As Double

        ' Retrieve the value of each sub total
        subTotal1 = CDbl(Me.txtSubTotal1.Text)
        subTotal2 = CDbl(Me.txtSubTotal2.Text)
        subTotal3 = CDbl(Me.txtSubTotal3.Text)
        subTotal4 = CDbl(Me.txtSubTotal4.Text)
        subTotal5 = CDbl(Me.txtSubTotal5.Text)
        subTotal6 = CDbl(Me.txtSubTotal6.Text)
        subTotal7 = CDbl(Me.txtSubTotal7.Text)
        subTotal8 = CDbl(Me.txtSubTotal8.Text)

        ' Calculate the total value of the sub totals
        orderTotal = subTotal1 + subTotal2 + subTotal3 + _
                             subTotal4 + subTotal5 + subTotal6 + _
                            subTotal7 + subTotal8

        ' Display the total order in the appropriate text box
        Me.txtTotalOrder.Text = orderTotal.ToString("F")
End Sub

In the Class Name combo box, select txtItemNumber1