VB.Net Object Guide for generated code

Introduction

• Singular classes: Represent a single record from a table or view
• Collection classes: Represent a collection of records from a table or view
• Error class: Provides errorhandling.
• DataAccess class: Provides connection and transaction management.
• Global module: Contains global variables and functions
• Metaclass module: Contains class configuration data

Table of Contents

1. Singular classes
1. Reading a record
2. Creating a new record
3. Modifying a record
4. Deleting a record
5. Accessing relational data
2. Collection classes
1. Reading records
2. Creating records
3. Modifying records
4. Deleting records

Singular classes

You can use these classes to read and update a single record from your database. For each table and view that you selected in Code-Engine, a singular class will be generated. Instances of singular classes represent a single record from a table in your database. So for instance: If you would have tables Order, Customer and Supply in your database, would create the classes clsOrder, clsCustomer and tblSupply.

Reading a record

To read a record from your database, you have to populate the object with one of the Populate methods. Several different populate methods are generated, use one of them to get your data. All Populate methods return a Boolean value specifying if a record was returned. If the methods return False, you should always check if an error has occurred using the Object.Error.ErrorHasOccurred property.

With the primary key

Dim objSingularClass As New clsSingularClass ' Populate the record by specifying the primary key value If Not objSingularClass.PopulateByPrimaryKeyID(nPrimaryKeyID) Then ' Error handling code End If So, for instance, if you have a Customer table in your database, getting the customer record with primary key value 1, would require the following code: Dim objCustomer As New clsCustomer ' Get the customer record with CustomerID=1 If Not objCustomer.PopulateByCustomerID(1) Then ' Error handling code End If

With Unique field(s)

If you have defined a unique constraint over 1 or more columns in a table, Code-Engine will generate a populate method for them. ' Populate the record by specifying the value for a unique field If Not objSingularClass.PopulateByUniqueField(UniqueFieldValue) Then ' Error handling code End If OR
' Populate the record by specifying the values for all fields ' that make up the unique index. If Not objSingularClass.PopulateByField1AndField2(FieldValue1, FieldValue2) Then ' Error handling code End If

With a custom query

You can use the PopulateBySQL method if you need more control over the SQL statement that will be executed to get the data. If your query returns more than one record, only the first record will be populated. Use the GetBySQL method from a collection class to get all records returned by the query.

Note: Unless you have specified a specific FielFilter, make sure all columns of the table are returned, or an error will occur.

Dim objCustomer As New clsCustomer ' Set some properties If Not objCustomer.PopulateBySQL("select tblCustomer.* from tblCustomer " & _ "inner join tblOrder on tblOrder.nCustomerID=tblCustomer.nCustomerID " & _ "where tblOrder.strTicketNr='1234-3232-2210291214'") Then ' Error handling code End If

Creating a new record

You can create a new record by instantiating a new object, setting its properties and calling the Update method. Dim objCustomer As New clsCustomer ' Set some properties objCustomer.Name = "Tech Corp" objCustomer.Street = "Tech avenue" objCustomer.AddressNr = 48 objCustomer.City = "Techvill" If objCustomer.Update Then ' Update was successful MsgBox "New customer added with ID " & objCustomer.CustomerID Else ' Error handling code End If

Modifying a record

You can modify an existing record by populating an existing record, setting its properties and calling the Update method. Dim objCustomer As New clsCustomer If objCustomer.PopulateByCustomerID (nCustomerID) Then ' Change some properties objCustomer.AddressNr = 84 objCustomer.City = "Tech vill" If Not objCustomer.Update Then ' Error handling code End If Else ' Error handling code End If You can also update a record without populating it first. Make sure you set the primary key field(s) to the appropriate data before calling the Update() function, or a new record will be created. Only the fields that you have assigned a value to will be updated in the respective record. objCustomer.CustomerID = nCustomerID objCustomer.AddressNr = 84 objCustomer.City = "Tech vill" If Not objCustomer.Update() Then ' Error handling code End If Please note that if you use this way to update a record that the properties you haven't assigned a value to before calling the update method, remain unassigned afterwards. You HAVE to populate the object to get the right values for all properties. Example: objCustomer.CustomerID = nCustomerID objCustomer.AddressNr = 84 objCustomer.City = "Tech vill" objCustomer.Update() MsgBox(objCustomer.Street) ' Contains "" because object was not populated!

Deleting a record

You can delete a record by populating the record, and calling the Delete method. Dim objCustomer As New clsCustomer If objCustomer.PopulateByCustomerID(nCustomerID) Then If Not objCustomer.Delete() Then ' Error handling code End If Else ' Error handling code End If

Accessing relational data

Code-Engine generates properties in your classes for all relational data in your database. For instance, a Customer record can have 1 or more Order records referencing it, because it has a many-to-1 relationship with the Order table. In this situation, the generated clsCustomer class will contain a Property "Orders", which (once accessed) will be populated with all referential data from table Order. Dim objCustomer As New clsCustomer Dim objOrder As clsOrder If objCustomer.PopulateByCustomerID(nCustomerID) Then lblOrders.Text = "Customer has " & _ objCustomers.Orders.Count & " orders:" & vbCrLf ' Iterate through all this customer's orders For Each objOrder In objCustomer.Orders lblOrders.Text += "Order " & objOrder.OrderID & ": " & _ objOrder.Product.Name Next End If You probably noticed the line where objOrder.Product.Name is accessed. The Product property accessed on this line is another example of an object that was exposed because of a relationship in the database: the many-to-1 relationship between tables Order and Product.

Many-to-many relationships

Many-to-many relationships are relationships from a certain table that implicitly link to another table through a third table: a linktable. For instance, a customer uses one or more of your products. The customers are stored in the Customer table, the products in the Product table, and the products they use are stored in a linktable called Customer_Product. Code-Engine does not generate code for these linktables, because you can manage their content through the objects that are exposed in the related objects.

Linking 2 objects together

To link two objects together in the linktable, see the code below. Note that the Update Method must be called after the product has been added. ' Add a Product to the Customer: Dim objCustomer As New clsCustomer If objCustomer.PopulateByCustomerID(nCurrentCustomerID) Then Dim objProduct As New clsProduct If objProduct.PopulateByProductID(nCurrentProductID) Then ' Add the product to the linked collection objCustomer.Products.Add(objProduct) End If ' Call the Update method to add a record to the linktable objCustomer.Products.Update() End If You can also do this the other way around: ' Add a Customer to the Product: Dim objProduct As New clsProduct If objProduct.PopulateByProductID(nCurrentProductID) Then Dim objCustomer As New clsCustomer If objCustomer.PopulateByCustomerID(nCurrentCustomerID) Then ' Add the customer to the linked collection objProduct.Customers.Add(objCustomer) End If ' Call the Update method to add a record to the linktable objProduct.Customers.Update() End If Note: objects that are accessed through a linked relationship will update the linktable and the record itself when the Update() Method is called. Any properties you have set or changed in the related object will be saved as well.

Removing a link

To remove a link between two records, you can call the Delete method on a item in the linked collection. Calling this method in this context will only delete the appropriate record from the linktable, not the record itself. ' Deleting a product from a customer: Dim objCustomer As New clsCustomer Dim objProduct As clsProduct If objCustomer.PopulateByCustomerID(nCurrentCustomerID) Then ' Get a product that is linked to this customer through ' the tlnkCustomer_Product linktable. objProduct = objCustomer.Products.ItemByID(nCurrentProductID) If Not objProduct Is Nothing Then ' Delete the linking record. If objProduct.Delete() Then _ MsgBox "Product was successfully removed from customer!" Else MsgBox "Product is not linked to this customer" End If End If Please note that objects that are accessed through a linked relationship, will delete the linking record from the linktable only when calling the Delete method. The related record itself will not be deleted. If you want to delete that record as well, you should call the Delete method once more. See the code below: ... objProduct = objCustomer.Products.ItemByID(nCurrentProductID) If Not objProduct Is Nothing Then objProduct.Delete() ' Delete the link objProduct.Delete() ' Delete the product record as well End If ... Because the code above might be a little confusing, and even unpredictable if you don't check for errors, it might be better (and clearer and neater) to write your code as follows: ... objProduct = objCustomer.Products.ItemByID(nCurrentProductID) If Not objProduct Is Nothing Then objProduct.Delete() ' Delete the link ' Delete the product itself now: objProduct = New clsProduct objProduct.PopulateBy(nCurrentProductID) objProduct.Delete() ' Delete the product itself End If ...

Removing all links

To remove all linked records at once, simply call the Delete method of the linked collection: ... If objProductPackage.Products.Delete() Then MsgBox "All products have been removed from customer!" End If ...

Collection classes

Just like singular classes, collection classes are also generated for each selected table and view in your database. These classes inherit from the CollectionBase class and contain a collection of singular class objects. The collections represent multiple records from a table or view in your database.

Reading records

In this paragraph I'll show you how you can use collection objects to easily retrieve multiple records from your database.

Retreive all records from a table

To populate your collection with all records in a certain table, you can use the GetAll*** Method (where *** is the plural name of the table) provided by the collection. Dim objCustomers As New clsCustomers ' Get all customers If Not objCustomers.GetAllCustomers() Then ' Error handling code End If

With a filter

Dim objCustomers As New clsCustomers ' Get a selection of customers using a Where statement and ' order them by customer name. If Not objCustomers.GetAllCustomers("blnActive=1", "strName") Then ' Error handling code End If

With a foreign key value

For each many-to-1 relationship (foreign keys from this table to others), a seperate GetBy method is generated. Dim objCustomers As New clsCustomers ' Get all customers that are active in region with ID 10 If Not objCustomers.GetCustomersByRegionID(10, "blnActive=1") Then ' Error handling code End If

Creating records

The following example shows how you can add multiple records to the database. Dim nCount As Integer Dim objCustomers As New clsCustomers Dim objNewCustomer As clsCustomer For nCount = 1 To 100 objNewCustomer = New clsCustomer objNewCustomer.Name = "Customer" & nCount objCustomers.Add(objNewCustomer) Next objCustomers.Update()

Modifying records

The Update() Method updates all records in its collection that were changed since they were populated. The following example shows how you can easily update multiple records in your database. Dim objCustomers As New clsCustomers Dim objCustomer As clsCustomer If objCustomers.GetAllCustomers() Then ' Iterate trough all records For Each objCustomer In objCustomers ' If the customer has been so for more than 5 years, ' grant them an extra discount If (objCustomer.Active And _ objCustomer.CreatedDate <= Now.AddYears(-5)) Then objCustomer.DiscountPercentage += 10 objCustomer.LastUpdatedDate = Now() End If Next ' Update all records that have been altered If Not objCustomers.Update() Then ' One or more updates failed. Check the last error MsgBox objCustomers.Error.Description End If ElseIf objCustomers.Error.ErrorOccurred Then ' Error handling code Else ' No records returned. End If

Transactional updates

The updates normally are not executed through a transaction, so if one or more Update statements fail, the successful ones will not be rolled back.

If you do want updates to use a transaction, make a call to the BeginTransaction() Method from the modGlobal.objDataAccess object before calling the Update() method, and CommitTransaction() or RollbackTransaction() after updating. See the code below, or chapter 'Using transactions' for more information.

.... modGlobal.objDataAccess.BeginTransaction() If objCustomers.Update() Then ' All updates succeeded, commit transaction modGlobal.objDataAccess.CommitTransaction() Else ' One or more updates failed. Rollback transaction modGlobal.objDataAccess.RollbackTransaction() ' Handle error End If ....

Deleting records

You can delete all records in a collection instantly by calling the Delete() Method. The deletions will not be performed in a transaction, see chapter 'Using Transactions' if you want this. Dim objCustomers As New clsCustomers objCustomers.GetAllCustomers() If Not objCustomers.Delete() Then ' Error handling code End If

Managing items in the collection

You can manage the items in the collection through the Add, Insert and Remove methods.

Collections exposed by relationships

More about this soon..