Transactional Database Tutorial

Print this page
Learn more about Verizon Connect GeoBase.
Get information about the latest release

Overview

The Telogis.GeoBase.Transactional namespace includes classes for creating, editing, querying and filtering GeoBase Transactional databases. These include the TransactionalRepository, Table, Record, Index, IndexColumn, ContainsFilter, EqualFilter, RangeFilter, ColumnFilter and EnsureTransaction classes, all of which are demonstrated in the tutorial below using a small example application.

Tip
Transaction databases are particularly useful for the following situations: when only simple database functions are needed, avoiding the need to write SQL when there is a need to easily display changing data on a map

Tip

Transaction databases are particularly useful for the following situations:

when only simple database functions are needed, avoiding the need to write SQL
when there is a need to easily display changing data on a map

TransactionalRepository is used to create simple transactional repositories (.gbtx format databases typically containing String, Integer, Polygon, LatLon and Time data that can, unlike read-only .gbfs format map data files, be written to on the fly). Table and Record create the tables and records that are contained within these repositories, while Index is used to create indexes that can be queried and filtered using the ContainsFilter, EqualFilter, and RangeFilter filters.

EnsureTransaction is used to create an EnsureTransaction safety object for a given repository. This ensures that Transactional methods only touch a database if a valid transaction is open (transactions can also be opened manually using the BeginTransaction method, and all transactions closed using EndTransaction). Only when a transaction is closed will data be committed and written.

In this tutorial we will create an application that demonstrates:

using EnsureTransaction and BeginTransaction to initiate a repository transaction
creating a new .gbtx transactional database containing tables and columns
creating a new table record, adding data for columns, and inserting these records into a .gbtx repository
using Index, IndexColumn and ColumnFilter to query and filter the contents of a transactional database
closing a transaction, and committing database changes, using EndTransaction

Creating the Application

Create a new Visual Studio Project (a Windows Forms Application) called 'MyTransactionalDatabase'.

Add a reference to geobase.net.dll. Then, to make the code simpler and more readable, add the following using directives to the top of the project form (Form1.cs).

Copy

using Telogis.GeoBase;
using Telogis.GeoBase.Routing;
using Telogis.GeoBase.Transactional;
using Telogis.GeoBase.ImageUtils;

Return to the 'Design' view (Shift+F7) and add the following controls to the form:

A textbox named statusBox with a ScrollBars setting of 'Vertical'
A map control named mapMain
A button named buttonPrimary with the button text 'Create/Load Transactional db'
A button named buttonDelete with the button text 'Delete Transactional db'
A button named buttonViewFolder with the button text 'View Database'
A combobox named comboBoxVehicles

Note
To set the ScrollBars value, click the text field in 'Design' view then open the 'Properties' panel (View \| Properties Window). Scroll down the properties list to the ScrollBars row; click the value field on the right, expand the value list by clicking the downward-facing arrow, and select the 'Vertical' option.

When run, your new application should appear similar to the screenshot below:

Note
The statusBox text box should be set to MultiLine. To do so, click the small arrow in the upper right corner of the text field when viewed in Visual Studio's 'Design' view and select the 'MultiLine' check box option.

Next, add the following code to the top of the project such that the items are global (immediately above the Form1 constructor).

These declarations are used for a string name specifying the .gbtx database name, several strings, LatLons and integers representing data to be used for internal processing by our application, or to be inserted into the database the application will create.

The RenderList will be used to add map objects (BalloonPushPins and Route highlights), the Route to create routes between locations specified in database entries, and the BoundingBox to adjust the map zoom.

Copy

String dbName = "MyTransactionalDatabase.gbtx";
int SYears, VGVWR, edgeBuffer;
String ID1, VID, FirstName, LastName, VManufacturer, VModel, VLicense, DStatus, DLicense, vloc_string, sel_string, veh_string;
LatLon VLoc, DLoc, SLoc, VLocation;
RendererList renderList = new RendererList();
BoundingBox BBox = new BoundingBox();
Route rte = new Route();
Dictionary<string, string> dlist = new Dictionary<string, string>();

Add a click event to buttonPrimary and update the click method to match the code below. This code checks whether the specified .gbtx database already exists, creates a new TransactionalRepository object named tr, and then disposes of it when the database interactions (which have not yet been specified) have concluded.

Note
The example below constructs a new transactional repository on disk without a specified location (doing so generates the repository with a relative path, resolving to the sample application's project folder in this case, for example MyTransactionalDatabase\bin\Debug or Release). It is also possible to create a transactional repository on disk at a specified location by including an absolute path in the constructor, for example TransactionalRepository tr = new TransactionalRepository("C:\database_folder\folder\database.gbtx");.

Note

The example below constructs a new transactional repository on disk without a specified location (doing so generates the repository with a relative path, resolving to the sample application's project folder in this case, for example MyTransactionalDatabase\bin\Debug or Release). It is also possible to create a transactional repository on disk at a specified location by including an absolute path in the constructor, for example TransactionalRepository tr = new TransactionalRepository("C:\database_folder\folder\database.gbtx");.

Copy

private void buttonPrimary_Click(object sender, EventArgs e) {
    // does the database exist?
    bool database_exists = System.IO.File.Exists(dbName);

    // create a repository object
    TransactionalRepository tr = new TransactionalRepository(dbName);

    // if the database exists then....
    if (database_exists) {

        ////  paste example code below here  ////

    // if the database doesn't exist then....
    } else {

        ////  paste example code above here  ////

    }
    // we're finished editing so dispose of the repository
    tr.Dispose();

    // advise that the repository is disposed
    if (tr.IsDisposed) {
        Console.Write("Transaction Complete\r\n");
    }
}

Copy and paste the following code snippet directly below the first if statement in the buttonPrimary_Click method. This code details the actions to be performed if a .gbtx repository is found to exist at the specified folder location (the application will load the database and write its tables' names to the console, then display the table records, as well as any results that match our specified filters).

Our code first creates a new EnsureTransaction object to encapsulate our actions within a using statement, then writes the names of the database's tables to the console. Two Index objects are then created, specifying the VehicleIndex and DriverIndex indexes for queries.

Next, it loops through the two tables, VehicleTable and DriversTable, writing out everything found in the tables' specified columns (IDN, Manufacturer, Model, GVWR, License, Location, and TimeStamp in the VehicleTable table; IDN, DriverNameFirst, DriverNameLast, DriverStatus, DriverLicense, StaffYears, and TimeStamp in the DriverTable table) to the statusBox text field.

Our first loop through the VehicleTable is used to populate the comboBoxVehicles combobox's dlist dictionary list. A string containing vehicle manufacturer, model and ID number details (pulled from the VehicleTable) is generated to be displayed as the combobox text, and each vehicle's ID number (from the IDN column in the VehicleTable) used as a key.

During the following loop through the DriversTable, information about the driver is added to the my_out string (to be displayed in the statusBox text box), and a BalloonPushPin added at each driver's location. Using the VLoc vehicle location obtained from the initial VehicleTable loop, we generate a route between the last vehicle record and each driver in the current loop, and use this to calculate the driving time and distance between the two. This information is displayed in the BalloonPushPin's name, and the route added to the RenderList to be displayed as a Route highlight, together with arrows indicating the direction of travel along the route from the driver to the vehicle (using the Directions properties RenderArrowLength, RenderArrowWidth and RenderArrowColor).

Below this we specify the visibility of the comboBoxVehicles combobox. If it is empty, we hide it. If it contains items, we display it.

Finally, we loop through the VehicleTable, adding additional vehicle details to the my_out string. BBox, our BoundingBox, is adjusted to encompass every driver and vehicle in the database, then used to zoom the map using ZoomToBoundingBox. This ensures that the map is zoomed in just enough to display all relevant driver and vehicle locations. A BalloonPushPin is then added for each vehicle, and the vehicle's manufacturer, model and ID number displayed on the BalloonPushPin label (using the Name and Information properties).

Copy

// the repository already exists so we will simply open it
String my_status = "Loaded transactional repository from disk.";
my_status = my_status + "\r\nThere is no need to create tables, columns or indexes.\r\n";
statusBox.Text = my_status;
renderList.Clear();
buttonViewFolder.Visible = true;

// make sure transactions are allowed
using (EnsureTransaction trans = new EnsureTransaction(tr)) {

    // loop through the repository writing out the table names found
    Console.WriteLine("Table Names:");
    foreach (String name in tr.Tables.Names) {
        Console.WriteLine("{0} ", name);
    }

    // specify the indexes we will use for queries
    Index id1 = tr.Indexes["VehicleIndex"];
    Index id2 = tr.Indexes["DriverIndex"];

    // create a StringBuilder to compile the the status box text
    StringBuilder my_out = new StringBuilder("\r\nDatabase Contents\r\n");

    // loop through the VehicleTable table to populate the combobox
    if (comboBoxVehicles.Items.Count == 0) {
        foreach (Record r in id1.Query(new ColumnFilter[0])) {
            vloc_string = r["Location"].ToString();
            veh_string = r["Manufacturer"].ToString() + " " + r["Model"].ToString();
            String dropdown_details = r["Manufacturer"] + " " + r["Model"] + " (Vehicle Asset No: " + r["IDN"] + ")";
            // pass the vehicle ID number as a key
            dlist.Add(r["IDN"].ToString(), dropdown_details);
            comboBoxVehicles.DataSource = new BindingSource(dlist, null);
            comboBoxVehicles.DisplayMember = "Value";
            comboBoxVehicles.ValueMember = "Key";
        }
    }

    // pull the vehicle location to use as a route destination
    VLoc = new LatLon(vloc_string);
    my_out.AppendFormat("\r\nDriversTable:\r\n");

    // loop through DriversTable, writing its content and adding 
    // balloon pushpins at the drivers' locations
    foreach (Record r2 in id2.Query(new ColumnFilter[0])) {
        my_out.AppendFormat("Driver ID = {0}\r\n", r2["IDN"]);
        my_out.AppendFormat("Driver's First Name = {0}\r\n", r2["DriverNameFirst"]);
        my_out.AppendFormat("Driver's Last Name = {0}\r\n", r2["DriverNameLast"]);
        my_out.AppendFormat("Current Driver Status = {0}\r\n", r2["DriverStatus"]);
        my_out.AppendFormat("Driver's License Type = {0}\r\n", r2["DriverLicense"]);
        my_out.AppendFormat("Driver's Location = {0}\r\n", r2["DriverLocation"]);
        my_out.AppendFormat("Years Employed = {0}\r\n", r2["StaffYears"]);
        my_out.AppendFormat("Record Timestamp = {0}\r\n\r\n", r2["TimeStamp"]);
        // expand the boundingbox to include each driver's location
        BBox.Add(new LatLon(r2["DriverLocation"].ToString()));
        // put a balloon pushpin at each driver's location
        BalloonPushPin c = new BalloonPushPin(new LatLon(r2["DriverLocation"].ToString()));
        // create a route from the driver to the vehicle
        rte.Start = new RouteStop(new LatLon(r2["DriverLocation"].ToString()));
        rte.End = new RouteStop(VLoc);
        // get a directions object from the route
        Directions dirs = rte.GetDirections();
        // calculate the distance from the driver to the vehicle
        decimal distShort = (decimal)(Math.Truncate(
            (double)dirs.GetTotalDistance(DistanceUnit.MILES) * 100.0) / 100.0);
        // calculate the time to drive from the driver location
        // to the vehicle location
        TimeSpan time = dirs.GetTotalTime();
        // use renderarrows to overlay the route direction from 
        // the driver to vehicle
        dirs.RenderArrowLength = 20;
        dirs.RenderArrowWidth = 12;
        dirs.RenderArrowColor = Color.White;
        dirs.RenderColor = Color.DodgerBlue;
        c.Name = r2["DriverNameFirst"].ToString() + " " + r2["DriverNameLast"].ToString() +
            "\r\n" + distShort + " miles from " + veh_string + "\r\nDrive time " + 
            time.Hours + "H:" + time.Minutes + "M:" + time.Seconds + "S";
        c.Icon = Icons.Man;
        renderList.Add(dirs);
        renderList.Add(c);
    }

    // hide the combobox if there are no items to display
    if (comboBoxVehicles.Items != null) {
        comboBoxVehicles.Visible = true;
    }

    my_out.AppendFormat("VehicleTable:\r\n");

    // loop through VehicleTable, writing its content and adding
    // balloon pushpins at the vehicles' locations
    foreach (Record r in id1.Query(new ColumnFilter[0])) {
        my_out.AppendFormat("Vehicle ID = {0}\r\n", r["IDN"]);
        my_out.AppendFormat("Manufacturer = {0}\r\n", r["Manufacturer"]);
        my_out.AppendFormat("Vehicle Model = {0}\r\n", r["Model"]);
        my_out.AppendFormat("GVWR Load Capacity = {0} lb\r\n", r["GVWR"]);
        my_out.AppendFormat("License Required = {0}\r\n", r["License"]);
        my_out.AppendFormat("Vehicle Location = {0}\r\n", r["Location"]);
        my_out.AppendFormat("Record Timestamp = {0}\r\n\r\n", r["TimeStamp"]);
        BBox.Add(new LatLon(r["Location"].ToString()));
        BalloonPushPin b = new BalloonPushPin(new LatLon(r["Location"].ToString()));
        b.Name = r["Manufacturer"].ToString() + " " + r["Model"].ToString();
        b.Information = "Asset No: " + r["IDN"].ToString();
        b.Icon = Icons.TruckRed;
        b.TitleBackgroundColor = Color.Transparent;
        renderList.Add(b);
    }

    // zoom the map to a boundingbox containing all drivers and vehicles in the database
    edgeBuffer = 50;
    mapMain.ZoomToBoundingBox(BBox, edgeBuffer);

Immediately below this, paste the following code.

Here we filter our queried data using the ContainsFilter, EqualFilter and RangeFilter filters. These are then used to create a ColumnFilter called myFilters which is applied to the id2 index (looking at the DriverIndex index).

These filter out records containing the text 'CDL' within the DriversLicense index column, any records that entirely match the text 'Available' within the DriverStatus index column, and any rows with a numeric value of between 3 and 50 in the StaffYears index column. Records that match these details will be displayed in the statusBox text box.

Copy

    // create a ContainFilter filter for a commercial driver's license
    ContainsFilter LicTypeIs = new ContainsFilter("DriverLicense", "CDL");

    // create an EqualFilter filter for drivers that are actively available
    EqualFilter AvailabilityIs = new EqualFilter("DriverStatus", "Available");

    // create a RangeFilter to filter out only experienced drivers 
    // (drivers who have been with the company for 3 years or more)
    // -- upper range value is arbitrarily 50 
    RangeFilter ExperienceIs = new RangeFilter("StaffYears", 3, 50);

    // create a ColumnFilter using our three filters
    ColumnFilter[] myFilters = new ColumnFilter[3] { LicTypeIs, AvailabilityIs, ExperienceIs };

    // update the status field with our results
    my_out.AppendFormat("Filter Results:\r\n(Results that contain the text 'CDL' ");
    my_out.AppendFormat("in the 'DriversLicense' column, 'Available' in the 'DriverStatus' ");
    my_out.Append("column and a value of '3' or more in the 'StaffYears' column.)\r\n");

    // loop through the DriverIndex index on DriversTable pulling data that match the filters
    foreach (Record r in id2.Query(myFilters)) {
        my_out.AppendFormat("Driver Name: {0}, {1}. ID Number {2}. Location: {3}. Years of Service: {4}.\r\n",
            r["DriverNameLast"],
            r["DriverNameFirst"],
            r["IDN"],
            r["DriverLocation"].ToString(),
            r["StaffYears"]);
    }
    statusBox.Text = my_status + my_out;
}

Copy and paste the following code snippet directly below the else statement in the buttonPrimary_Click method. This details the actions to be performed if the specified .gbtx repository is found not to exist (it will create a new database containing tables, columns and record data).

First, a database transaction is manually initiated using BeginTransaction, then two tables named VehicleTable and DriverTable created.

Seven columns, IDN (String data type), Manufacturer (String data type), Model (String data type), GVWR (Integer data type), License (String data type), Location (LatLon data type), and TimeStamp (Time data type) are added to the VehicleTable table. Eight columns are also added to the DriverTable table: IDN (String data type), DriverNameFirst (String data type), DriverNameLast (String data type), DriverStatus (String data type), DriverLicense (String data type), DriverLocation (LatLon data type), StaffYears (Integer data type), and TimeStamp (Time data type).

This creates two tables containing the following columns:

VehicleTable (vehicles)

IDN	Manufacturer	Model	GVWR	License	Location	TimeStamp

DriverTable (drivers)

IDN	DriverNameFirst	DriverNameLast	DriverStatus	DriverLicense	DriverLocation	StaffYears	TimeStamp

Copy

// manually begin the transaction so we can edit the gbtx repository
// ...alternatively, we could use EnsureTransaction
tr.BeginTransaction();

//// we can now add all the tables and 
//// indexes we need to the repository...

// create a copyright notice
tr.SetCopyrightString("Acme Shipping Inc.");

// create two tables, VehicleTable and DriverTable named 'vehicles' and 'drivers'
// table names are always converted to lower case
Table VehicleTable = tr.CreateTable("vehicles");
Table DriverTable = tr.CreateTable("drivers");

// add columns to VehicleTable
VehicleTable.AddField("IDN", DataType.String);
VehicleTable.AddField("Manufacturer", DataType.String);
VehicleTable.AddField("Model", DataType.String);
VehicleTable.AddField("GVWR", DataType.Integer);
VehicleTable.AddField("License", DataType.String);
VehicleTable.AddField("Location", DataType.LatLon);
VehicleTable.AddField("TimeStamp", DataType.Time);

// add columns to DriverTable
DriverTable.AddField("IDN", DataType.String);
DriverTable.AddField("DriverNameFirst", DataType.String);
DriverTable.AddField("DriverNameLast", DataType.String);
DriverTable.AddField("DriverStatus", DataType.String);
DriverTable.AddField("DriverLicense", DataType.String);
DriverTable.AddField("DriverLocation", DataType.LatLon);
DriverTable.AddField("StaffYears", DataType.Integer);
DriverTable.AddField("TimeStamp", DataType.Time);

Now we will create two indexes for the VehicleTable and DriverTable tables called VehicleIndex and DriverIndex. These will index all of columns present in both tables, with the exception of the Location and DriverLocation columns. LatLon locations cannot be indexed in transactional databases.

Two records, myVehiclesRecord and myDriversRecord, are then created using CreateRecord. Three records (rows of data entries) are inserted, populating the seven columns (IDN, Manufacturer, Model, GVWR, License, Location, and TimeStamp) of the VehicleTable table using Insert and four records (rows of data entries) inserted into the DriverTable table columns (IDN, DriverNameFirst, DriverNameLast, DriverStatus, DriverLicense, DriverLocation, StaffYears, and TimeStamp) within a short loop.

Following insertion of these records, our two tables will be populated as below:

VehicleTable

IDN	Manufacturer	Model	GVWR	License	Location	TimeStamp
CA497	Ford	F-650	10000	CDL-A	34.039549, -118.558001	<The Current PC Time>
CA453	Ford	F-750	37000	CDL-A	34.045520, -118.236250	<The Current PC Time>
CA425	Ford	Transit	2000	Passenger	34.061437, -117.493235	<The Current PC Time>

DriverTable

IDN	DriverNameFirst	DriverNameLast	DriverStatus	DriverLicense	DriverLocation	StaffYears	TimeStamp
LD0001	John	Jackson	Not Available	CDL-A	33.792927, -118.286960	1	<The Current PC Time>
LD0002	Susan	Chen	Available	CDL-B	34.068769,-117.942476	3	<The Current PC Time>
LD0003	Henry	Watts	Available	CDL-B	33.806277,-118.016414	7	<The Current PC Time>
LD0004	Solomon	Isaacs	Available	Passenger	33.722166,-117.863315	2	<The Current PC Time>

Paste the following code directly below DriverTable.AddField("TimeStamp", DataType.Time);.

Copy

// create an index for VehicleTable so we can query it later
VehicleTable.CreateIndex("VehicleIndex",
    new IndexColumn[6] {
        new IndexColumn("IDN"),
        new IndexColumn("Manufacturer"),
        new IndexColumn("Model"),
        new IndexColumn("GVWR"),
        new IndexColumn("License"),
        new IndexColumn("TimeStamp")
    });

// create an index for DriverTable so we can query it later
DriverTable.CreateIndex("DriverIndex",
    new IndexColumn[7] {
        new IndexColumn("IDN"),
        new IndexColumn("DriverNameFirst"),
        new IndexColumn("DriverNameLast"),
        new IndexColumn("DriverStatus"),
        new IndexColumn("DriverLicense"),
        new IndexColumn("StaffYears"),
        new IndexColumn("TimeStamp"),
    });

// create records
Record myVehiclesRecord = VehicleTable.CreateRecord();
Record myDriversRecord = DriverTable.CreateRecord();

// populate the fields of myVehiclesRecord
for (int i = 0; i < 3; i++) {
    if (i == 0) {
        VID = "CA497"; VManufacturer = "Ford"; VModel = "F-650";
        VGVWR = 10000; VLicense = "CDL-A";
        VLocation = new LatLon(34.039549, -118.558001);
    }
    if (i == 1) {
        VID = "CA453"; VManufacturer = "Ford"; VModel = "F-750"; 
        VGVWR = 37000; VLicense = "CDL-A";
        VLocation = new LatLon(34.045520, -118.236250);
    }
    if (i == 2) {
        VID = "CA425"; VManufacturer = "Ford"; VModel = "Transit";
        VGVWR = 2000; VLicense = "Passenger";
        VLocation = new LatLon(34.061437, -117.493235);
    }
    myVehiclesRecord["IDN"] = VID;
    myVehiclesRecord["Manufacturer"] = VManufacturer;
    myVehiclesRecord["Model"] = VModel;
    myVehiclesRecord["GVWR"] = VGVWR;
    myVehiclesRecord["License"] = VLicense;
    myVehiclesRecord["Location"] = VLocation;
    myVehiclesRecord["TimeStamp"] = System.DateTime.Now;

    // insert the myVehiclesRecord record into the VehicleTable
    VehicleTable.Insert(myVehiclesRecord);
}

// populate the fields of myDriversRecord
for (int i = 0; i < 4; i++) {
    if (i == 0) { 
        ID1 = "LD0001"; FirstName = "John"; LastName = "Jackson";
        DStatus = "Not Available"; DLicense = "CDL-A"; SYears = 1;
        DLoc = new LatLon(33.792927, -118.286960);
    }
    if (i == 1) { 
        ID1 = "LD0002"; FirstName = "Susan"; LastName = "Chen";
        DStatus = "Available"; DLicense = "CDL-B"; SYears = 3;
        DLoc = new LatLon(34.068769, -117.942476);
    }
    if (i == 2) { 
        ID1 = "LD0003"; FirstName = "Henry"; LastName = "Watts";
        DStatus = "Available"; DLicense = "CDL-B"; SYears = 7;
        DLoc = new LatLon(33.806277, -118.016414);
    }
    if (i == 3) { 
        ID1 = "LD0004"; FirstName = "Solomon"; LastName = "Isaacs";
        DStatus = "Available"; DLicense = "Passenger"; SYears = 2;
        DLoc = new LatLon(33.722166, -117.863315);
    }
    myDriversRecord["IDN"] = ID1;
    myDriversRecord["DriverNameFirst"] = FirstName;
    myDriversRecord["DriverNameLast"] = LastName;
    myDriversRecord["DriverStatus"] = DStatus;
    myDriversRecord["DriverLocation"] = DLoc;
    myDriversRecord["DriverLicense"] = DLicense;
    myDriversRecord["StaffYears"] = SYears;
    myDriversRecord["TimeStamp"] = System.DateTime.Now;

    // insert the myDriversRecord record into the DriverTable
    DriverTable.Insert(myDriversRecord);
}

Next, copy and paste the following code directly below the previous snippet (just beneath the for-loop). This will close the transaction using EndTransaction, writing and committing our new records to the database. The statusBox textbox is then updated with a 'success' message, and buttonViewFolder set to Visible, allowing application users to view the newly created database in Windows Explorer, if they want to.

Copy

// end the transaction and write the data
tr.EndTransaction();

// display a success message
Console.Write("Database Created!\r\n");
statusBox.Text = "Database has been created.\r\n(" + dbName + ")\r\n" +
 "Database copyright " + tr.CopyrightString;
buttonViewFolder.Visible = true;

Add a click event to buttonDelete and update the buttonDelete_Click method as follows.

This event is called when the user wants to delete an existing database, and simply checks whether the database exists; deleting it if found. mapMain is then set to its initial zoom and center, the RenderList and route cleared, and the buttonViewFolder button hidden.

Copy

private void buttonDelete_Click(object sender, EventArgs e) {
    // does the database exist?
    bool database_exists = System.IO.File.Exists(dbName);
    // if it does...
    if (database_exists) {
        comboBoxVehicles.Visible = false;
        // delete it
        System.IO.File.Delete(dbName);
        Console.Write("Transactional Database Deleted\r\n");
        // update the text status field
        statusBox.Text = "Database has been deleted.";
        // reset the map
        mapMain.Center = new LatLon(33.879692, -118.029161);
        mapMain.Zoom = 200;
        // clear the render list
        renderList.Clear();
        // hide the database folder button
        buttonViewFolder.Visible = false;
        rte.Clear();
    // if it doesn't....
    } else {
        // advise no database exists
        statusBox.Text = "Unable to delete. No database found.";
        Console.Write("Cannot Delete -- No Database Found\r\n");
    }
}

Add a click event to buttonViewFolder and update the buttonViewFolder_Click method as follows.

This event is called when the user wants to view the created database, and simply opens the database folder in Windows Explorer. The buttonViewFolder button ('View Database') will only visible to users when a database has been created.

Copy

private void buttonViewFolder_Click(object sender, EventArgs e) {
     // open the database folder
     System.Diagnostics.Process.Start(Application.StartupPath);
}

Add a SelectionChangeCommitted event to the comboBoxVehicles combobox and update the comboBoxVehicles_SelectionChangeCommitted method as follows.

This event is called when the user selects an item (a vehicle) from the dropdown list populated previously using the dlist dictionary. This action will update the mapMain map control, causing it to display route highlights between the selected vehicle and the drivers listed in the database, together with updated BalloonPushPin labels indicating the driving distance and time between the selected vehicle and the drivers in our newly created .gbtx transactional database.

You will note that this snippet is almost identical to that used previously, following the initial loading of the application and discovery of an existing database. These new actions differ only in the inclusion of manual vehicle selection and filtering, rather than simply using the last record found during a loop.

ContainsFilter is used to filter only the vehicle in the VehicleTable that corresponds with the ID passed by the comboBoxVehicles combobox Key (retrieved as the value string).

Copy

private void comboBoxVehicles_SelectionChangeCommitted(object sender, EventArgs e) {
    // pull the vehicle ID number passed by the combobox as the Key value
    string value = ((KeyValuePair<string, string>)comboBoxVehicles.SelectedItem).Key;
    if (value != null) {
        // clear the renderlist and the route 
        renderList.Clear();
        rte.Clear();
        // set the cursor to hourglass so users know something is happening
        Cursor.Current = Cursors.WaitCursor;
        // create the transactional repository object
        TransactionalRepository tr = new TransactionalRepository(dbName);
        // manually begin the transaction
        tr.BeginTransaction();
        // create two new index objects
        Index id3 = tr.Indexes["VehicleIndex"];
        Index id4 = tr.Indexes["DriverIndex"];
        // find the record where the value in the 'IDN' column matches the 
        // ID number passed from the combobox
        ContainsFilter VehicleID = new ContainsFilter("IDN", value);
        ColumnFilter[] IDFilter = new ColumnFilter[1] { VehicleID };
        foreach (Record r3 in id3.Query(IDFilter)) {
            // get the vehicle location as a string
            sel_string = r3["Location"].ToString();
            // get the vehicle manufacturer and model
            veh_string = r3["Manufacturer"].ToString() + " " + r3["Model"].ToString();
        }

        // convert the string location of the vehicle to 
        // a LatLon and use as the route end
        SLoc = new LatLon(sel_string);

        // loop through DriversTable, writing its content and adding 
        // balloon pushpins at the drivers' locations
        foreach (Record r4 in id4.Query(new ColumnFilter[0])) {
            BBox.Add(new LatLon(r4["DriverLocation"].ToString()));
            // put a balloon pushpin at each driver's location
            BalloonPushPin d = new BalloonPushPin(new LatLon(r4["DriverLocation"].ToString()));
            // create a route from the driver to the vehicle
            rte.Start = new RouteStop(new LatLon(r4["DriverLocation"].ToString()));
            rte.End = new RouteStop(SLoc);
            // get a directions object from the route
            Directions dirs = rte.GetDirections();
            // calculate the distance from the driver to the vehicle
            decimal distShort = (decimal)(Math.Truncate(
                (double)dirs.GetTotalDistance(DistanceUnit.MILES) * 100.0) / 100.0);
            // calculate the time to drive from the driver location
            // to the vehicle location
            TimeSpan time = dirs.GetTotalTime();
            // use renderarrows to overlay the route direction from 
            // the driver to vehicle
            dirs.RenderArrowLength = 20;
            dirs.RenderArrowWidth = 12;
            dirs.RenderArrowColor = Color.White;
            dirs.RenderColor = Color.DodgerBlue;
            // add details to display in the balloonpushpin
            d.Name = r4["DriverNameFirst"].ToString() + " " + r4["DriverNameLast"].ToString() +
                "\r\n" + distShort + " miles from " + veh_string + "\r\nDrive time " +
                time.Hours + "H:" + time.Minutes + "M:" + time.Seconds + "S";
            // change the balloonpushpin icon
            d.Icon = Icons.Man;
            // add the directions highlight and balloon pushpin the the renderlist
            renderList.Add(dirs);
            renderList.Add(d);
        }

        // loop through VehicleTable, adding
        // balloon pushpins at the vehicles' locations
        foreach (Record r5 in id3.Query(new ColumnFilter[0])) {
            BBox.Add(new LatLon(r5["Location"].ToString()));
            BalloonPushPin redraw_vehicles = new BalloonPushPin(new LatLon(r5["Location"].ToString()));
            redraw_vehicles.Name = r5["Manufacturer"].ToString() + " " + r5["Model"].ToString();
            redraw_vehicles.Information = "Asset No: " + r5["IDN"].ToString();
            redraw_vehicles.Icon = Icons.TruckRed;
            redraw_vehicles.TitleBackgroundColor = Color.Transparent;
            renderList.Add(redraw_vehicles);
        }

        // end the transaction and dispose of the transactional repository
        tr.EndTransaction();
        tr.Dispose();
        Console.Write("Transaction Complete. New Routes Generated.\r\n");

        // again zoom the map to a boundingbox containing all drivers and vehicles in 
        // the database in case the map has been panned or zoomed since its initial rendering
        edgeBuffer = 50;
        mapMain.ZoomToBoundingBox(BBox, edgeBuffer);

        // invalidate the map to force a redraw
        mapMain.Invalidate();

        // work is done so return the cursor to its default state
        Cursor.Current = Cursors.Default;
    }
}

Finally, update the Form1 constructor with the following (paste directly below the InitializeComponent(); statement).

This will set the initial statusBox text, the mapMain center location and zoom value. It also sets the DragBehavior to Hand, allowing the map to be panned with a mouse by users; adjusts the DropDownStyle of the comboBoxVehicles to prevent manual editing of the dropdown list through the application UI; and hides the buttonViewFolder button and comboBoxVehicles combobox.

Copy

// the initial state and text for the status box
statusBox.ReadOnly = true;
statusBox.BackColor = Color.White;
statusBox.Text = "No database content available yet.";

// set an initial map zoom and center
mapMain.Center = new LatLon(33.879692, -118.029161);
mapMain.Zoom = 200;

// change the drag behavior to hand, allowing the map to be
// moved using the mouse
mapMain.DragBehavior = DragBehavior.Hand;
mapMain.UiEnable = true;

// set the renderlist for the map
mapMain.Renderer = renderList;

// change the combobox style to prevent editing
comboBoxVehicles.DropDownStyle = ComboBoxStyle.DropDownList;

// hide the combobox and buttons until they are needed
buttonViewFolder.Visible = false;
comboBoxVehicles.Visible = false;

The example application is now complete and ready to be tested.

Testing

Run the application by pressing F5 on your keyboard, then click the 'Create/Load Transactional db' button. A .gbtx transactional database will be created in the Visual Studio project folder (<Path to Project>\MyTransactionalDatabase\bin\Debug, for example) containing two tables; each table housing seven (vehicles) or eight (driver) columns. Click the 'View Database' button to view the database in Windows Explorer.

Click the 'Create/Load Transactional db' button once again to view a listing of the contents of the newly created transactional database in the application's text field, and the results of our filters (scroll to the bottom of the text field).

The map will be updated to display the location of the vehicles and drivers in the database, and a route between each driver and the last vehicle record found in the VehicleTable.

Select a vehicle from the dropdown list to update the map to display routes between the selected vehicle and the drivers in the database. Note that the BalloonPushPins are updated with details of the driving time and distance between each driver and the selected vehicle.

Use your mouse to drag the map, and the scroll wheel to zoom in and out to view routes in more detail.

Click the 'Delete Transactional db' button to delete the database.

Note
The example application will be unable to delete the database if it is still open in WorkBench.

Complete Code

The complete code for this example project is included below:

Copy

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Telogis.GeoBase;
using Telogis.GeoBase.Routing;
using Telogis.GeoBase.Transactional;
using Telogis.GeoBase.ImageUtils;

namespace MyTransactionalDatabase {

    public partial class Form1 : Form {
        String dbName = "MyTransactionalDatabase.gbtx";
        int SYears, VGVWR, edgeBuffer;
        String ID1, VID, FirstName, LastName, VManufacturer, VModel, VLicense, DStatus, DLicense, vloc_string, sel_string, veh_string;
        LatLon VLoc, DLoc, SLoc, VLocation;
        RendererList renderList = new RendererList();
        BoundingBox BBox = new BoundingBox();
        Route rte = new Route();
        Dictionary<string, string> dlist = new Dictionary<string, string>();
        public Form1() {
            InitializeComponent();

            // the initial state and text for the status box
            statusBox.ReadOnly = true;
            statusBox.BackColor = Color.White;
            statusBox.Text = "No database content available yet.";

            // set an initial map zoom and center
            mapMain.Center = new LatLon(33.879692, -118.029161);
            mapMain.Zoom = 200;

            // change the drag behavior to hand, allowing the map to be
            // moved using the mouse
            mapMain.DragBehavior = DragBehavior.Hand;
            mapMain.UiEnable = true;

            // set the renderlist for the map
            mapMain.Renderer = renderList;

            // change the combobox style to prevent editing
            comboBoxVehicles.DropDownStyle = ComboBoxStyle.DropDownList;

            // hide the combobox and buttons until they are needed
            buttonViewFolder.Visible = false;
            comboBoxVehicles.Visible = false;
        }

        private void buttonPrimary_Click(object sender, EventArgs e) {
            // does the database exist?
            bool database_exists = System.IO.File.Exists(dbName);

            // create a repository object
            TransactionalRepository tr = new TransactionalRepository(dbName);

            // if the database exists then....
            if (database_exists) {

                ////  paste example code below here  ////

                // the repository already exists so we will simply open it
                String my_status = "Loaded transactional repository from disk.";
                my_status = my_status + "\r\nThere is no need to create tables, columns or indexes.\r\n";
                statusBox.Text = my_status;
                renderList.Clear();
                buttonViewFolder.Visible = true;

                // make sure transactions are allowed
                using (EnsureTransaction trans = new EnsureTransaction(tr)) {

                    // loop through the repository writing out the table names found
                    Console.WriteLine("Table Names:");
                    foreach (String name in tr.Tables.Names) {
                        Console.WriteLine("{0} ", name);
                    }

                    // specify the indexes we will use for queries
                    Index id1 = tr.Indexes["VehicleIndex"];
                    Index id2 = tr.Indexes["DriverIndex"];

                    // create a StringBuilder to compile the the status box text
                    StringBuilder my_out = new StringBuilder("\r\nDatabase Contents\r\n");

                    // loop through the VehicleTable table to populate the combobox
                    if (comboBoxVehicles.Items.Count == 0) {
                        foreach (Record r in id1.Query(new ColumnFilter[0])) {
                            vloc_string = r["Location"].ToString();
                            veh_string = r["Manufacturer"].ToString() + " " + r["Model"].ToString();
                            String dropdown_details = r["Manufacturer"] + " " + r["Model"] + " (Vehicle Asset No: " + r["IDN"] + ")";
                            // pass the vehicle ID number as a key
                            dlist.Add(r["IDN"].ToString(), dropdown_details);
                            comboBoxVehicles.DataSource = new BindingSource(dlist, null);
                            comboBoxVehicles.DisplayMember = "Value";
                            comboBoxVehicles.ValueMember = "Key";
                        }
                    }

                    // pull the vehicle location to use as a route destination
                    VLoc = new LatLon(vloc_string);
                    my_out.AppendFormat("\r\nDriversTable:\r\n");

                    // loop through DriversTable, writing its content and adding 
                    // balloon pushpins at the drivers' locations
                    foreach (Record r2 in id2.Query(new ColumnFilter[0])) {
                        my_out.AppendFormat("Driver ID = {0}\r\n", r2["IDN"]);
                        my_out.AppendFormat("Driver's First Name = {0}\r\n", r2["DriverNameFirst"]);
                        my_out.AppendFormat("Driver's Last Name = {0}\r\n", r2["DriverNameLast"]);
                        my_out.AppendFormat("Current Driver Status = {0}\r\n", r2["DriverStatus"]);
                        my_out.AppendFormat("Driver's License Type = {0}\r\n", r2["DriverLicense"]);
                        my_out.AppendFormat("Driver's Location = {0}\r\n", r2["DriverLocation"]);
                        my_out.AppendFormat("Years Employed = {0}\r\n", r2["StaffYears"]);
                        my_out.AppendFormat("Record Timestamp = {0}\r\n\r\n", r2["TimeStamp"]);
                        // expand the boundingbox to include each driver's location
                        BBox.Add(new LatLon(r2["DriverLocation"].ToString()));
                        // put a balloon pushpin at each driver's location
                        BalloonPushPin c = new BalloonPushPin(new LatLon(r2["DriverLocation"].ToString()));
                        // create a route from the driver to the vehicle
                        rte.Start = new RouteStop(new LatLon(r2["DriverLocation"].ToString()));
                        rte.End = new RouteStop(VLoc);
                        // get a directions object from the route
                        Directions dirs = rte.GetDirections();
                        // calculate the distance from the driver to the vehicle
                        decimal distShort = (decimal)(Math.Truncate(
                            (double)dirs.GetTotalDistance(DistanceUnit.MILES) * 100.0) / 100.0);
                        // calculate the time to drive from the driver location
                        // to the vehicle location
                        TimeSpan time = dirs.GetTotalTime();
                        // use renderarrows to overlay the route direction from 
                        // the driver to vehicle
                        dirs.RenderArrowLength = 20;
                        dirs.RenderArrowWidth = 12;
                        dirs.RenderArrowColor = Color.White;
                        dirs.RenderColor = Color.DodgerBlue;
                        c.Name = r2["DriverNameFirst"].ToString() + " " + r2["DriverNameLast"].ToString() +
                            "\r\n" + distShort + " miles from " + veh_string + "\r\nDrive time " + 
                            time.Hours + "H:" + time.Minutes + "M:" + time.Seconds + "S";
                        c.Icon = Icons.Man;
                        renderList.Add(dirs);
                        renderList.Add(c);
                    }

                    // hide the combobox if there are no items to display
                    if (comboBoxVehicles.Items != null) {
                        comboBoxVehicles.Visible = true;
                    }

                    my_out.AppendFormat("VehicleTable:\r\n");

                    // loop through VehicleTable, writing its content and adding
                    // balloon pushpins at the vehicles' locations
                    foreach (Record r in id1.Query(new ColumnFilter[0])) {
                        my_out.AppendFormat("Vehicle ID = {0}\r\n", r["IDN"]);
                        my_out.AppendFormat("Manufacturer = {0}\r\n", r["Manufacturer"]);
                        my_out.AppendFormat("Vehicle Model = {0}\r\n", r["Model"]);
                        my_out.AppendFormat("GVWR Load Capacity = {0} lb\r\n", r["GVWR"]);
                        my_out.AppendFormat("License Required = {0}\r\n", r["License"]);
                        my_out.AppendFormat("Vehicle Location = {0}\r\n", r["Location"]);
                        my_out.AppendFormat("Record Timestamp = {0}\r\n\r\n", r["TimeStamp"]);
                        BBox.Add(new LatLon(r["Location"].ToString()));
                        BalloonPushPin b = new BalloonPushPin(new LatLon(r["Location"].ToString()));
                        b.Name = r["Manufacturer"].ToString() + " " + r["Model"].ToString();
                        b.Information = "Asset No: " + r["IDN"].ToString();
                        b.Icon = Icons.TruckRed;
                        b.TitleBackgroundColor = Color.Transparent;
                        renderList.Add(b);
                    }

                    // zoom the map to a boundingbox containing all drivers and vehicles in the database
                    edgeBuffer = 50;
                    mapMain.ZoomToBoundingBox(BBox, edgeBuffer);

                    // create a ContainFilter filter for a commercial driver's license
                    ContainsFilter LicTypeIs = new ContainsFilter("DriverLicense", "CDL");

                    // create an EqualFilter filter for drivers that are actively available
                    EqualFilter AvailabilityIs = new EqualFilter("DriverStatus", "Available");

                    // create a RangeFilter to filter out only experienced drivers 
                    // (drivers who have been with the company for 3 years or more)
                    // -- upper range value is arbitrarily 50 
                    RangeFilter ExperienceIs = new RangeFilter("StaffYears", 3, 50);

                    // create a ColumnFilter using our three filters
                    ColumnFilter[] myFilters = new ColumnFilter[3] { LicTypeIs, AvailabilityIs, ExperienceIs };

                    // update the status field with our results
                    my_out.AppendFormat("Filter Results:\r\n(Results that contain the text 'CDL' ");
                    my_out.AppendFormat("in the 'DriversLicense' column, 'Available' in the 'DriverStatus' ");
                    my_out.Append("column and a value of '3' or more in the 'StaffYears' column.)\r\n");

                    // loop through the DriverIndex index on DriversTable pulling data that match the filters
                    foreach (Record r in id2.Query(myFilters)) {
                        my_out.AppendFormat("Driver Name: {0}, {1}. ID Number {2}. Location: {3}. Years of Service: {4}.\r\n",
                            r["DriverNameLast"],
                            r["DriverNameFirst"],
                            r["IDN"],
                            r["DriverLocation"].ToString(),
                            r["StaffYears"]);
                    }
                    statusBox.Text = my_status + my_out;
                }

            // if the database doesn't exist then....
            } else {

                // manually begin the transaction so we can edit the gbtx repository
                // ...alternatively, we could use EnsureTransaction
                tr.BeginTransaction();

                //// we can now add all the tables and 
                //// indexes we need to the repository...

                // create a copyright notice
                tr.SetCopyrightString("Acme Shipping Inc.");

                // create two tables, VehicleTable and DriverTable named 'vehicles' and 'drivers'
                // table names are always converted to lower case
                Table VehicleTable = tr.CreateTable("vehicles");
                Table DriverTable = tr.CreateTable("drivers");

                // add columns to VehicleTable
                VehicleTable.AddField("IDN", DataType.String);
                VehicleTable.AddField("Manufacturer", DataType.String);
                VehicleTable.AddField("Model", DataType.String);
                VehicleTable.AddField("GVWR", DataType.Integer);
                VehicleTable.AddField("License", DataType.String);
                VehicleTable.AddField("Location", DataType.LatLon);
                VehicleTable.AddField("TimeStamp", DataType.Time);

                // add columns to DriverTable
                DriverTable.AddField("IDN", DataType.String);
                DriverTable.AddField("DriverNameFirst", DataType.String);
                DriverTable.AddField("DriverNameLast", DataType.String);
                DriverTable.AddField("DriverStatus", DataType.String);
                DriverTable.AddField("DriverLicense", DataType.String);
                DriverTable.AddField("DriverLocation", DataType.LatLon);
                DriverTable.AddField("StaffYears", DataType.Integer);
                DriverTable.AddField("TimeStamp", DataType.Time);

                // create an index for VehicleTable so we can query it later
                VehicleTable.CreateIndex("VehicleIndex",
                    new IndexColumn[6] {
                        new IndexColumn("IDN"),
                        new IndexColumn("Manufacturer"),
                        new IndexColumn("Model"),
                        new IndexColumn("GVWR"),
                        new IndexColumn("License"),
                        new IndexColumn("TimeStamp")
                    });

                // create an index for DriverTable so we can query it later
                DriverTable.CreateIndex("DriverIndex",
                    new IndexColumn[7] {
                        new IndexColumn("IDN"),
                        new IndexColumn("DriverNameFirst"),
                        new IndexColumn("DriverNameLast"),
                        new IndexColumn("DriverStatus"),
                        new IndexColumn("DriverLicense"),
                        new IndexColumn("StaffYears"),
                        new IndexColumn("TimeStamp"),
                    });

                // create records
                Record myVehiclesRecord = VehicleTable.CreateRecord();
                Record myDriversRecord = DriverTable.CreateRecord();

                // populate the fields of myVehiclesRecord
                for (int i = 0; i < 3; i++) {
                    if (i == 0) {
                        VID = "CA497"; VManufacturer = "Ford"; VModel = "F-650";
                        VGVWR = 10000; VLicense = "CDL-A";
                        VLocation = new LatLon(34.039549, -118.558001);
                    }
                    if (i == 1) {
                        VID = "CA453"; VManufacturer = "Ford"; VModel = "F-750"; 
                        VGVWR = 37000; VLicense = "CDL-A";
                        VLocation = new LatLon(34.045520, -118.236250);
                    }
                    if (i == 2) {
                        VID = "CA425"; VManufacturer = "Ford"; VModel = "Transit";
                        VGVWR = 2000; VLicense = "Passenger";
                        VLocation = new LatLon(34.061437, -117.493235);
                    }
                    myVehiclesRecord["IDN"] = VID;
                    myVehiclesRecord["Manufacturer"] = VManufacturer;
                    myVehiclesRecord["Model"] = VModel;
                    myVehiclesRecord["GVWR"] = VGVWR;
                    myVehiclesRecord["License"] = VLicense;
                    myVehiclesRecord["Location"] = VLocation;
                    myVehiclesRecord["TimeStamp"] = System.DateTime.Now;

                    // insert the myVehiclesRecord record into the VehicleTable
                    VehicleTable.Insert(myVehiclesRecord);
                }

                // populate the fields of myDriversRecord
                for (int i = 0; i < 4; i++) {
                    if (i == 0) { 
                        ID1 = "LD0001"; FirstName = "John"; LastName = "Jackson";
                        DStatus = "Not Available"; DLicense = "CDL-A"; SYears = 1;
                        DLoc = new LatLon(33.792927, -118.286960);
                    }
                    if (i == 1) { 
                        ID1 = "LD0002"; FirstName = "Susan"; LastName = "Chen";
                        DStatus = "Available"; DLicense = "CDL-B"; SYears = 3;
                        DLoc = new LatLon(34.068769, -117.942476);
                    }
                    if (i == 2) { 
                        ID1 = "LD0003"; FirstName = "Henry"; LastName = "Watts";
                        DStatus = "Available"; DLicense = "CDL-B"; SYears = 7;
                        DLoc = new LatLon(33.806277, -118.016414);
                    }
                    if (i == 3) { 
                        ID1 = "LD0004"; FirstName = "Solomon"; LastName = "Isaacs";
                        DStatus = "Available"; DLicense = "Passenger"; SYears = 2;
                        DLoc = new LatLon(33.722166, -117.863315);
                    }
                    myDriversRecord["IDN"] = ID1;
                    myDriversRecord["DriverNameFirst"] = FirstName;
                    myDriversRecord["DriverNameLast"] = LastName;
                    myDriversRecord["DriverStatus"] = DStatus;
                    myDriversRecord["DriverLocation"] = DLoc;
                    myDriversRecord["DriverLicense"] = DLicense;
                    myDriversRecord["StaffYears"] = SYears;
                    myDriversRecord["TimeStamp"] = System.DateTime.Now;

                    // insert the myDriversRecord record into the DriverTable
                    DriverTable.Insert(myDriversRecord);
                }

                // end the transaction and write the data
                tr.EndTransaction();

                // display a success message
                Console.Write("Database Created!\r\n");
                statusBox.Text = "Database has been created.\r\n(" + dbName + ")\r\n" +
                 "Database copyright " + tr.CopyrightString;
                buttonViewFolder.Visible = true;
            }

            ////  paste example code above here  ////

            // we're finished editing so dispose of the repository
            tr.Dispose();

            // advise that the repository is disposed
            if (tr.IsDisposed) {
                Console.Write("Transaction Complete\r\n");
            }
        }

        private void buttonDelete_Click(object sender, EventArgs e) {
            // does the database exist?
            bool database_exists = System.IO.File.Exists(dbName);
            // if it does...
            if (database_exists) {
                comboBoxVehicles.Visible = false;
                // delete it
                System.IO.File.Delete(dbName);
                Console.Write("Transactional Database Deleted\r\n");
                // update the text status field
                statusBox.Text = "Database has been deleted.";
                // reset the map
                mapMain.Center = new LatLon(33.879692, -118.029161);
                mapMain.Zoom = 200;
                // clear the render list
                renderList.Clear();
                // hide the database folder button
                buttonViewFolder.Visible = false;
                rte.Clear();
            // if it doesn't....
            } else {
                // advise no database exists
                statusBox.Text = "Unable to delete. No database found.";
                Console.Write("Cannot Delete -- No Database Found\r\n");
            }
        }

        private void buttonViewFolder_Click(object sender, EventArgs e) {
            // open the database folder
            System.Diagnostics.Process.Start(Application.StartupPath);
        }

        private void comboBoxVehicles_SelectionChangeCommitted(object sender, EventArgs e) {
            // pull the vehicle ID number passed by the combobox as the Key value
            string value = ((KeyValuePair<string, string>)comboBoxVehicles.SelectedItem).Key;
            if (value != null) {
                // clear the renderlist and the route 
                renderList.Clear();
                rte.Clear();
                // set the cursor to hourglass so users know something is happening
                Cursor.Current = Cursors.WaitCursor;
                // create the transactional repository object
                TransactionalRepository tr = new TransactionalRepository(dbName);
                // manually begin the transaction
                tr.BeginTransaction();
                // create two new index objects
                Index id3 = tr.Indexes["VehicleIndex"];
                Index id4 = tr.Indexes["DriverIndex"];
                // find the record where the value in the 'IDN' column matches the 
                // ID number passed from the combobox
                ContainsFilter VehicleID = new ContainsFilter("IDN", value);
                ColumnFilter[] IDFilter = new ColumnFilter[1] { VehicleID };
                foreach (Record r3 in id3.Query(IDFilter)) {
                    // get the vehicle location as a string
                    sel_string = r3["Location"].ToString();
                    // get the vehicle manufacturer and model
                    veh_string = r3["Manufacturer"].ToString() + " " + r3["Model"].ToString();
                }

                // convert the string location of the vehicle to 
                // a LatLon and use as the route end
                SLoc = new LatLon(sel_string);

                // loop through DriversTable, writing its content and adding 
                // balloon pushpins at the drivers' locations
                foreach (Record r4 in id4.Query(new ColumnFilter[0])) {
                    BBox.Add(new LatLon(r4["DriverLocation"].ToString()));
                    // put a pushpin at each driver's location
                    BalloonPushPin d = new BalloonPushPin(new LatLon(r4["DriverLocation"].ToString()));
                    // create a route from the driver to the vehicle
                    rte.Start = new RouteStop(new LatLon(r4["DriverLocation"].ToString()));
                    rte.End = new RouteStop(SLoc);
                    // get a directions object from the route
                    Directions dirs = rte.GetDirections();
                    // calculate the distance from the driver to the vehicle
                    decimal distShort = (decimal)(Math.Truncate(
                        (double)dirs.GetTotalDistance(DistanceUnit.MILES) * 100.0) / 100.0);
                    // calculate the time to drive from the driver location
                    // to the vehicle location
                    TimeSpan time = dirs.GetTotalTime();
                    // use renderarrows to overlay the route direction from 
                    // the driver to vehicle
                    dirs.RenderArrowLength = 20;
                    dirs.RenderArrowWidth = 12;
                    dirs.RenderArrowColor = Color.White;
                    dirs.RenderColor = Color.DodgerBlue;
                    // add details to display in the balloonpushpin
                    d.Name = r4["DriverNameFirst"].ToString() + " " + r4["DriverNameLast"].ToString() +
                        "\r\n" + distShort + " miles from " + veh_string + "\r\nDrive time " +
                        time.Hours + "H:" + time.Minutes + "M:" + time.Seconds + "S";
                    // change the balloonpushpin icon
                    d.Icon = Icons.Man;
                    // add the directions highlight and pushpin the the renderlist
                    renderList.Add(dirs);
                    renderList.Add(d);
                }

                // loop through VehicleTable, adding
                // balloon pushpins at the vehicles' locations
                foreach (Record r5 in id3.Query(new ColumnFilter[0])) {
                    BBox.Add(new LatLon(r5["Location"].ToString()));
                    BalloonPushPin redraw_vehicles = new BalloonPushPin(new LatLon(r5["Location"].ToString()));
                    redraw_vehicles.Name = r5["Manufacturer"].ToString() + " " + r5["Model"].ToString();
                    redraw_vehicles.Information = "Asset No: " + r5["IDN"].ToString();
                    redraw_vehicles.Icon = Icons.TruckRed;
                    redraw_vehicles.TitleBackgroundColor = Color.Transparent;
                    renderList.Add(redraw_vehicles);
                }

                // end the transaction and dispose of the transactional repository
                tr.EndTransaction();
                tr.Dispose();
                Console.Write("Transaction Complete. New Routes Generated.\r\n");

                // again zoom the map to a boundingbox containing all drivers and vehicles in 
                // the database in case the map has been panned or zoomed since its initial rendering
                edgeBuffer = 50;
                mapMain.ZoomToBoundingBox(BBox, edgeBuffer);

                // invalidate the map to force a redraw
                mapMain.Invalidate();

                // work is done so return the cursor to its default state
                Cursor.Current = Cursors.Default;
            }
        }
    }
}