A Public D3 Database C# Class

Introduction

This article provides a common class for accessing a D3 (Pick) Database using ODBC.

This is a work in progress and will be updated from time to time as new functionality is desired and my skills at C# improve.

The D3 ODBC class is not explained in this article. See the D3 ODBC documentation from the Tigerlogic website for a full description.

As this article is targeted at a D3 Developer audience, it is assumed that the reader has a D3 background and understanding of D3 terminology such as Account, File, Item, List etc..

Background

I've been working in a Pick environment for many years now. Over the past decade I've used VB6 to access Pick data in a D3 Database. During this time, I developed routines to give me access to D3's database and created a module of common procedures. For copyright reasons I am unable to share this code. However, now that I'm using C# to basically do similar things (in another company), I decided to write my own D3Database class to provide me with similar functionality. This code, I CAN share.

Note: This code references the D3CLODBC.DLL file which is installed using the D3 ODBC driver setup program. I developed this class using version C30 as I could only manage to get this one working. The driver installation program can be found on the Tigerlogic FTP site here. Documentation for the D3 ODBC driver can be found on the Tigerlogic website here.

Using the code

Before the class can be used it is important, nay mandatory, to set up a D3 ODBC DSN. As I developed this class on a 64bit Windows 7 desktop PC, I had to use the 32 bit ODBC Manager in the SysWOW64 folder to set up my DSN. Note: You will need to run this as Administrator to work properly. See the image below for an example:

I wanted to make the D3Database class as easy to use as possible. A colleague of mine suggest I use the "singleton" class. I did some research and concluded this may be the best way to code my class.

The D3Database object needs only to be instantiated once when your code begins execution. I do it immediately after the call to <code>InitializeComponent() of the main form of the project. This ensures the D3 database connection is ready for use from the outset. See the example below (substitute your own DSN as required):

public Form1()
{
    InitializeComponent();
    D3Database.Instance.OpenDB("DSN");
}

I use the main form's _FormClosed() event to close and destroy the database object(s) as in the example below:

private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
    D3Database.Instance.CloseDB();
}

D3Database Methods

There are currently 6 methods to this class:

1. OpenDB() - Opens the database
2. CloseDB() - Closes the database connection and destroys objects
3. ReadD3Item() - Reads a D3 item from a D3 File returning a D3 Dynamic Array
4. WriteD3Item() - Writes a D3 Dynamic Array to D3 item in a D3 File
5. SelectD3Items() - Selects a number of D3 items from a D3 file returning a D3 list of items
6. D3OConv() - Provides standard D3 Output Conversions

OpenDB

OpenDB() is declared as follows:

public void OpenDB(string d3AccountName)

This method establishes an ODBC connection the database defined in the named DSN. I typically name the DSN the same as the D3 Account I wish to connect to for ease of maintenance. Hence the naming of the parameter as d3AccountName as opposed to DSN.

The single required parameter is the name of the D3 DSN created using the ODBC Data Source Administrator.

See below for an example of its usage:

D3Database.Instance.OpenDB("D3TESTACCOUNT"); 

The above statement will establish an ODBC connection to the D3 database defined in the DSN D3TESTACCOUNT (which will be the D3 account D3TESTACCOUNT as I give the DSN the same name as the D3 account). Now that the connection has been established, we read or write data, select items from files, execute TCL commands and much more.

CloseDB

CloseDB() is declared as follows:

public void CloseDB()

This method closes the previously opened ODBC connection to the D3 database. It has no error checking to ensure that only an opened connection is closed. It is assumed that if this method is called then the database was opened at some stage.

ReadD3Item

ReadD3Item() is declared as follows:

<code>public bool ReadD3Item(string filename, string itemID, ref clsD3DynamicArray d3Rec)

<code>

This method is used to read data from a D3 File. It reads a single item from a single file. In SQL parlance, it reads a Record from a Table.

The method returns a boolean to indicate whether the read operation was successful or not. An unsuccessful read is usually due to a missing item (ID - perhaps misspelled), or a file system error (unlikely). It currently does NOT cater for a locked item, meaning the program will appear to hang until the item is unlocked. I plan to add code to check for this scenario at a later stage.

The data is returned as a (class) D3 Dynamic Array in the variable d3Rec. There are currently no checks as to the validity of the D3 filename or item id, so make sure these are either constants or programmatically generated. The example below attempts to read an item from a file and add attributes 1 and 2 of the read item to a listbox:

clsD3DynamicArray penRec = new clsD3DynamicArray();
itemID = "123456";
if (D3Database.Instance.ReadD3Item("Pensioner", itemID, ref penRec))
{
    listBox1.Items.Add(itemID + ": " + 
        penRec.brExtractStr(1) + ", " + 
        penRec.brExtractStr(2));
} 

WriteD3Item

WriteD3Item()is declared as follows:

public bool WriteD3Item(string filename, string itemID, clsD3DynamicArray d3Rec)

This method is used to write data onto a D3 File. It writes a single item to a single file. In SQL parlance, it writes a Record to a Table.

The method returns a boolean to indicate whether the write operation was successful or not. As we usually don't care, the method does not need to be assigned to a variable or checked as part of a conditional statement. Generally speaking, the only time the write would not be successful would be if the name of the file was incorrectly given, or there was a file system error. The D3 filename would normally be coded as a constant anyway, thus eliminating an incorrect filename as the problem for a failed write. the method currently does NOT cater for a locked item, meaning the program will appear to hang until the item is unlocked. I plan to add code to check for this scenario at a later stage.

The example below writes 3 attributes to an item on a D3 file without checking for a successful write:

clsD3DynamicArray tempRec = new clsD3DynamicArray();

tempRec.brInsertStr("Test writing D3 item from C#", 1);
tempRec.brInsertStr(listCount + " items selected", 2);
tempRec.brInsertStr("This is a MV", 2, 2);
tempRec.brInsertStr(DateTime.Now.ToString(), 3);

D3Database.Instance.WriteD3Item("CONTROL", "C#Test", tempRec);

SelectD3Items

SelectD3Items() is declared as follows:

public int SelectD3Items(string SelectionCriteria, ref clsD3SelectList D3List)

This method is used to select a number of items in a D3 file and return a list of the selected items.

The method takes two parameters, one is an input string consisting of the selection criteria and the other is the returned D3 list as a (class) D3 Select List. It also returns an integer value indicating the number of items selected. There is currently no validation of the selection criteria to ensure it does not contain any erroneous clauses (and probably never will be), so it is up to the programmer to ensure clean selection criteria is used. If you're expecting a number of items and you get 0 returned, it's good bet there's a mistake in the selection criteria.

The example below shows the use of the method in a loop to process a number of items:

//Declare vars 
int listCount = 0;
object oPenID = "";
clsD3SelectList D3List = null;

//Selection Criteria
string selectString = "SELECT PENSIONER with surname = \"smith\"";

//Perform the select
listCount = D3Database.Instance.SelectD3Items(selectString, ref D3List); 

//Loop thru the items in the list
while (D3List.brReadnext(ref oPenID)) //.brReadnext returns an OBJECT NOT a STRING!
{
   penID = (string)oPenID;  //Convert the object ID to a string ID
   if (penID.Length > 0)    //ALWAYS check for NULL item IDs to ignore...
   {
      //Process each ID here...
   }
}  

D3Conv

D3OConv() is declared as follows:

public string D3OConv(string stringToConvert, string conversionCode)

This method is used to access the standard D3 Output Conversions such as Date & Numeric conversions from internal to external format.

Usage is pretty straight forward, albeit a bit verbose. See the example below:

string DOB = D3Database.Instance.D3OConv(penRec.brExtractStr(8), "d4/")) 

The above code extracts the 8th attribute from the D3 Dynamic Array penRec and converts it into D4/ format (DD/MM/YYYY).

See the Generic D3 Reference Manual (Non-Platform specific) for more OConv formats.

See the zip file for a full example project showing the use of these methods.

Points of Interest

This class requires the D3CLODBC.DLL file to function (at all). I had plenty of trouble to get it working in the 1st instance (no pun intended) as not all versions of the D3 ODBC driver would work with C#. Even then, the data types shown in Intellisense don't always match up with the data types shown in the D3 ODBC documentation.

Note also that using this class consumes one D3 ODBC license whilst the program is running. This had never been an issue with the clients the (old VB6) software was written for, but may be an issue for smaller businesses. In that case, this class may not be suitable or may need to be modified, i.e. not a singleton class, to allow opening and closing the ODBC connection as needed.

The Project name is a slight misnomer as it purports that it is only a D3 Selection Test. I originally developed the project for this reason, but it soon became the testing ground for new D3 methods. As I haven't yet figured out how to rename a project so it compiles cleanly, I've decided to leave it as is.

Related Reading

Tigerlogic's D3 Documentation Page

Generic D3 Reference Manual (Non-Platform specific)

Generic D3 User's Guide (Non-Platform specific)

D3 ODBC and D3 SQL User's Manual

D3 V9 Class Library

There is also the D3 Discussion Forums. People there are helpful enough, but there's just not enough of them and it may take a while for a reply, particularly on difficult questions.

Tigerlogic also have an FTP site containing Updates, patches and documentation for the various platform versions of D3.

For some general reading on PICK, see Jonathan E. Sisk's “Pick/BASIC: A Programmer's Guide”

<o:p>

The Multivalue Community Webring is another site that can assist D3 Developers with problems.

Disclaimer

This code is supplied "as is" with no warranty implied or given. You use it at your own risk.

Acknowledgements

Thanks to:

Nathan Rector for his contribution regarding the cleanup of the database object(s) before exiting the program. I hope I've implemented it correctly and in the correct place. I'm still fairly green in C#.

Improvements

If anyone has any ideas on how I can improve this article or the code, please feel free to make suggestions. As I'm fairly green with C#, I'm sure there could be lots of improvements made to the code. I just ask one thing, please explain your suggestion clearly so I (and others) can understand it :-)

History

16/01/2012 First Release.

17/01/2012 Minor wording changes. Added DSN example image.

18/01/2012 Added more detail to the method descriptions.

19/01/2012 Added Related Reading links.

20/01/2012 Changed subsection from "General" to "Other Databases" and the Description.

23/02/2012 Added missing loop code to "SelectD3Items" example. Added "D3OConv" method and update code file(s).

01/03/2012 Added CloseDB method to the D3Database module. Uploaded updated example project. Slight cosmetic changes to article for easier viewing. Added disclaimer, Acknowledgements and Improvements sections.