Database Objects
Hi-Integrity Systems
Home.NET Quickstart

Quick Start for .NET

Implementing a database table using classes and the DatabaseObjects library involves creating two classes. The first class, represents a database table, while the second class represent each database record in the table. For the classes to "hook into" the library the first class (or collection class) must inherit from DatabaseObjects while the second class must inherit from DatabaseObject. By inheriting from DatabaseObjects the collection class can specify which table it is tied to, the table's unique field, whether it should only represent a subset of the table, how the table should be sorted, if there are any related tables, etc. while the second class can specify how a database record is to be copied to and from the class. The MustOverride functions in the DatabaseObjects class are used to automatically generate the appropriate SQL statements for common database functions such as inserting a new record, updating an existing record, searching through a table, enumerating through a set of records, returning the number of records in a table, etc. The library also provides the mechanism for specifying attributes for field mappings, tables names etc.

The diagram below depicts how a Products database table might be implemented using the library. Two classes would be required; a Products class that inherits from DatabaseObjects and a Product class that inherits from DatabaseObject. Once the MustOverride functions have been implemented the library can then be used with the DatabaseObjects library's set of predefined, generic functions to automatically generate and execute the necessary SQL statements. For example, the Count property in the Products class could call one of the predefined DatabaseObjects functions: DatabaseObjects.ObjectsCount. This function creates an SQL statement using the value returned from DatabaseObjects.TableName (in this case "Products") to generate the following: SELECT COUNT(*) FROM Products The SQL statement is then executed and the result returned. If the DatabaseObjects.ObjectsCount function was called by passing a Customers class which had implemented DatabaseObjects.TableName to return "Customers" then the DatabaseObjects.ObjectsCount function would generate and execute the statement: SELECT COUNT(*) FROM Customers This basic technique is used by the DatabaseObjects library and it's set of generic functions.

 

Code Example

This example demonstrates using the DatabaseObjects library with the Products table from Microsoft's Northwind database. The Microsoft Access version of the Northwind database is included with Visual Basic 6 and by default is located at: C:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb. The following example assumes that the database exists at this location - although this can be modified. The database can also be downloaded here from the Microsoft website.

  1. Run Visual Studio
  2. Create a new Class Library
  3. Name the library NorthwindDB
  4. Add the DatabaseObjects library project
    1. Select File > Add > Existing Project
    2. Select the Browse tab
    3. Navigate to the DatabaseObjects.csproj project
    4. Select Open
    5. Select Project > Add Reference
    6. Select the Projects tab
    7. Select the DatabaseObjects project and select OK
  5. Rename Class1.vb to Database.vb
  6. Add the following code to the Database class
  7. This will connect to the nwind.mdb database. The nwind.mdb database is available here at the Microsoft website.

Public Class Database Private Shared psobjProducts As Products Public Shared Sub Connect() Const cstrDatabaseFilePath As String = _ "C:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb" Dim strConnection As String = _ "Data Source=" & cstrDatabaseFilePath & ";" & _ "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;" Dim objDatabase As New DatabaseObjects.Database( _ strConnection, DatabaseObjects.Database.ConnectionType.MicrosoftAccess) psobjProducts = New Products(objDatabase) End Sub Public Shared ReadOnly Property Products() As Products Get Return psobjProducts End Get End Property End Class

Product Class

  1. Add a new class called Product
  2. Set the class to inherit from DatabaseObjects.DatabaseObject
  3. Setup the class as per below. The DatabaseObjects.FieldMapping attribute indicates the database field that the field is automatically set and read when respectively loading and saving the object from the database.

Public Class Product Inherits DatabaseObjects.DatabaseObject 'DatabaseObjects.FieldMapping indicates that the 'ProductName' field should 'be automatically set to this class member <DatabaseObjects.FieldMapping("ProductName")> _ Private pstrName As String <DatabaseObjects.FieldMapping("UnitPrice")> _ Private pdecUnitPrice As Decimal Public Sub New() MyBase.New(Database.Products) End Sub Public Property Name() As String Get Return pstrName End Get Set(ByVal value As String) pstrName = value End Set End Property Public Property UnitPrice() As Decimal Get Return pdecUnitPrice End Get Set(ByVal value As Decimal) pdecUnitPrice = value End Set End Property End Class

Products Class

  1. Add a new class, name it Products
  2. Set the class to inherit from DatabaseObjects.Generic.DatabaseObjectsListKeyed. i.e. Inherits DatabaseObjects.Generic.DatabaseObjectsListKeyed(Of Product, String). This indicates that the class represents a collection of Product objects and can be accessed via a key of data type string.
  3. Override the functions are per below

'DistinctField() indicates that the ProductID field uniquely identifies each product record 'in the table and setting bAutoIncrements:=True indicates that the ProductID field is an 'automatically incrementing field and should be updated automatically by the DatabaseObjects 'library. 'Table() indicates that this collection uses the Products table. 'OrderBy() indicates that this collection should always be sorted in ascending order by the 'ProductName. Specifically, when enumerating through the collection (using ObjectByOrdinal or 'ObjectsList). 'KeyField() indicates that the ProductName field is a unique field within the product table 'and is used by the DatabaseObjects.ObjectByKey function. Specifically, the Products("Name") 'default property. 'ItemInstance() indicates that type that represents each database row / product 'in the Products table. 'This is optional because the type can be automatically determined from the (Of Product, ...) <DistinctField("ProductID", bAutoIncrements:=True)> _ <Table("Products")> _ <OrderBy("ProductName")> _ <KeyField("ProductName")> _ <ItemInstance(GetType(Product))> _ Public Class Products Inherits DatabaseObjects.Generic.DatabaseObjectsListKeyed(Of Product, String) Friend Sub New(ByVal objDatabase As DatabaseObjects.Database) MyBase.New(objDatabase) End Sub End Class

Exe Project

  1. Select File > Add > New Project
  2. Select Console Application
  3. Rename ConsoleApplication1 to NorthwindDBTest
  4. Right click on NorthwindDBTest and select Set As Startup Project
  5. Select the NorthwindDBTest project
  6. Select Project > Add Reference
  7. Select the NorthwindDB project reference in the Projects tab
  8. Paste the following code into Module1:

Module Module1 Sub Main() NorthwindDB.Database.Connect() For Each objProduct As NorthwindDB.Product In NorthwindDB.Database.Products Console.WriteLine(objProduct.Name & " - " & objProduct.UnitPrice.ToString("c")) Next Console.ReadLine() End Sub End Module

For more information and examples see the reference guide and demonstration program. Both are available here.