ADO.NET QuickStart

As with all other tutorials in the c-tree series, each example simplifies the concepts of database programming into four simple steps: Initialize(), Define(), Manage(), and you're Done() !

No matter what FairCom DB Interface language you are using, FairCom follows these same high-level stages in all the tutorials. This makes is easy for developers to "cross-over" from one interface language to another for these basic concepts apply to all:

• Initialize() - Every language requires some form of initial "logon" or "connection" procedure to establish a session with the database. This is done in the Initialize() stage of the program.
• Define() - Database definitions (DDL) , Table/File schema definitions, Index definitions, Table/File creation, and Table/File Open operations are all addressed in the Define() stage of the program.
• Manage() - This stage of the program is where the database is operated on, as in managing your data. Adding/Reading/Updating/Deleting records/rows are handle in this stage of the program.
• Done() - When the program ends, the database session should be closed. This stage handles the necessities to "de-init", by closing Tables/Files and issuing any required "logoff" or "disconnect" type procedures.

Presented here are four tutorials that follow the "Initialize(), Define(), Manage(), and you're Done() ! " approach. Each tutorial builds on the prior example, and are intended to be run in order.

Tutorial 1: Data Basics - Create, Insert, Update, Delete, Find, Retrieve

drivers\csharp.sql.ado.net\tutorials\CSharpTutorial1.cs

This tutorial will take you through the basic use of the FairCom DB ADO.NET Data Provider using C#.

As with all other examples in the c-tree tutorial series, this tutorial simplifies the creation and use of a database into four simple steps: Initialize(), Define(), Manage(), and You’re Done() !

Tutorial #1: Introductory - Simple Single Table

We wanted to keep this program as simple as possible. This program does the following:

• Initialize() - Connects to the FairCom Database Engine.
• Define() - Defines and creates a "customer master" (custmast) table/file.
• Manage() - Adds a few rows/records; Reads the rows/records back from the database; displays the column/field content; and then deletes the rows/records.
• Done() - Disconnects from FairCom Database Engine.

The best way to follow along is to use your debugger. Open the tutorial solution with Microsoft Visual Studio, and single-step through the code. If you have not already done so, launch Visual Studio, and open the solution that corresponds to the version of Visual Studio you have. The solution files (all called CSharpTutorials.sln) can be found in the following directory within the FairCom installation directories:

drivers\csharp.sql.ado.net\tutorials\IDEProjects\Microsoft Visual Studio 2012

drivers\csharp.sql.ado.net\tutorials\IDEProjects\Microsoft Visual Studio 2010

drivers\csharp.sql.ado.net\tutorials\IDEProjects\Microsoft Visual Studio 2019

From within the Microsoft Visual Studio IDE, open the Solutions Explorer to see the four tutorial projects. Be sure to set CSharpTutorials1 as the active project by right-clicking on it and selecting Set as StartUp Project as shown here:

Now press F11 to activate debugging and single step into the program. The program will be automatically built if necessary.

Note our simple Main() function:

using System;
using Ctree.Data.SqlClient;

namespace CSharpTutorial
{
   class Tutorial1
   {
      // declare connection, command and reader objects
      static CtreeSqlConnection conn;
      static CtreeSqlCommand cmd;
      static CtreeSqlDataReader rdr;

      //
      // main()
      //
      // The main() function implements the concept of "init, define, manage
      // and you're done..."
      //

      static void Main()
      {
         Initialize();

         Define();

         Manage();

         Done();

         Console.Write("\nPress <ENTER> key to exit . . .");
         Console.ReadLine();
      }

We suggest opening the source code with your own editor.

Continue now to review these four steps.

Init

First we need to open a connection to a database by providing the FairCom Database Engine with a user name, password and the database name.

Below is the code for Initialize():

      //
      // Initialize()
      //
      // Perform the minimum requirement of logging onto the c-tree Server
      //

      static void Initialize()
      {
         Console.WriteLine("INIT");

         try
         {
            // initialize connection object
            conn = new CtreeSqlConnection();
            conn.ConnectionString = "UID=ADMIN;PWD=ADMIN;Database=CtreeSQL;Server=localhost;Service=6597;";

            // initialize command object
            cmd = new CtreeSqlCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Connection = conn;
            cmd.Transaction = null;

            // connect to server
            Console.WriteLine("\tLogon to server...");
            conn.Open();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }

Define

Define() establishes specific data definitions. This involves defining columns/fields and creating the tables/files with optional indexes.

Below is the code for Define():

      //
      // Define()
      //
      // Create the table for containing a list of existing customers
      //

      static void Define()
      {
         Console.WriteLine("DEFINE");

         try
         {
            // create table
            Console.WriteLine("\tCreate table...");
            cmd.CommandText = "CREATE TABLE custmast (" +
               "cm_custnumb CHAR(4)," +
               "cm_custzipc CHAR(9)," +
               "cm_custstat CHAR(2)," +
               "cm_custrtng CHAR(1)," +
               "cm_custname VARCHAR(47)," +
               "cm_custaddr VARCHAR(47)," +
               "cm_custcity VARCHAR(47))";
            cmd.ExecuteNonQuery();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }

Manage

Manage() provides data management functionality for your application and/or process.

Below is the code for Manage():

      //
      // Manage()
      //
      // This function performs simple record functions of add, delete and gets
      //

      static void Manage()
      {
         Console.WriteLine("MANAGE");

         // delete any existing records
         Delete_Records();

         // populate the table with data
         Add_Records();

         // display contents of table
         Display_Records();
      }

      //
      // Delete_Records()
      //
      // This function deletes all the records in the table
      //

      static void Delete_Records()
      {
         Console.WriteLine("\tDelete records...");

         try
         {
            cmd.CommandText = "DELETE FROM custmast";
            cmd.ExecuteNonQuery();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }


      //
      // Add_Records()
      //
      // This function adds records to a table in the database from an
      // array of strings
      //

      static void Add_Records()
      {
         String[] data = {
            "('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')",
            "('1001','61434','CT','1','Michael Jordan','13 Main','Harford')",
            "('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')",
            "('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')"
         };

         Console.WriteLine("\tAdd records...");

         try
         {
            // add one record at time to table
            for (int i = 0; i < data.Length; i++)
            {
               cmd.CommandText = "INSERT INTO custmast VALUES " + data[i];
               cmd.ExecuteNonQuery();
            }
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }


      //
      // Display_Records()
      //
      // This function displays the contents of a table.
      //

      static void Display_Records()
      {
         Console.Write("\tDisplay records...");

         try
         {
            cmd.CommandText = "SELECT * FROM custmast";

            // get a resultset
            rdr = (CtreeSqlDataReader)cmd.ExecuteReader();

            // read the returned resultset
            while (rdr.Read())
            {
               Console.WriteLine("\n\t\t{0}   {1}", rdr.GetString(0), rdr.GetString(4));
            }
            // close the reader
            rdr.Close();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }

Done

When an application and/or process has completed operations with the database, it must release resources and close the open files by disconnecting from the database engine.

Below is the code for Done():

      //
      // Done()
      //
      // This function handles the housekeeping of closing, freeing,
      // disconnecting and logging out of the database
      //

      static void Done()
      {
         Console.WriteLine("DONE");

         try
         {
            // logout
            Console.WriteLine("\tLogout...");
            conn.Close();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }

Additional Resources

We encourage you to explore the additional resources listed here:

• Complete source code for this tutorial can be found in CSharpTutorial1.cs in your installation directory, within the csharp.sql.ado.net\tutorials directory for your platform.
   
• Additional documentation may be found on the FairCom Web site.

Relationships

drivers\csharp.sql.ado.net\tutorials\CSharpTutorial2.cs

Now we will build some table/file relationships using the FairCom DB ADO.NET Data Provider using C#.

This tutorial will advance the concepts introduced in the first tutorial by expanding the number of tables. We will define key columns/fields and create specific indexes for each table to form a relational model database.

Like all other examples in the c-tree tutorial series, this tutorial simplifies the creation and use of a database into four simple steps: Initialize(), Define(), Manage(), and You’re Done() !

Tutorial #2: Relational Model and Indexing

Here we add a bit more complexity, introducing multiple tables, with related indices in order to form a simple "relational" database simulating an Order Entry system. Here is an overview of what will be created:

• Initialize() - Connects to the FairCom Database Engine.
• Define() - Defines and creates the "custmast", "custordr", "ordritem" and the "itemmast" tables/files with related indexes.
• Manage() - Adds some related rows/records to all tables/files. Then queries the database.
• Done() - Disconnects from FairCom Database Engine.

Note our simple Main() function:

using System;
using System.Collections;
using Ctree.Data.SqlClient;

namespace CSharpTutorial
{
   class Tutorial2
   {
      // declare connection, command and reader objects
      static CtreeSqlConnection conn;
      static CtreeSqlCommand cmd;
      static CtreeSqlDataReader rdr;

      //
      // main()
      //
      // The main() function implements the concept of "init, define, manage
      // and you're done..."
      //

      static void Main()
      {
         Initialize();

         Define();

         Manage();

         Done();

         Console.Write("\nPress <ENTER> key to exit . . .");
         Console.ReadLine();
      }

We suggest opening the source code with your own editor.

Continue now to review these four steps.

Init

First we need to open a connection to a database by providing the FairCom Database Engine with a user name, password and the database name.

Below is the code for Initialize():

      //
      // Initialize()
      //
      // Perform the minimum requirement of logging onto the c-tree Server
      //

      static void Initialize()
      {
         Console.WriteLine("INIT");

         try
         {
            // initialize connection object
            conn = new CtreeSqlConnection();
            conn.ConnectionString = "UID=ADMIN;PWD=ADMIN;Database=CtreeSql;Server=localhost;Service=6597;";

            // initialize command object
            cmd = new CtreeSqlCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Connection = conn;
            cmd.Transaction = null;

            // connect to server
            Console.WriteLine("\tLogon to server...");
            conn.Open();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }

Define

Define() establishes specific data definitions. This involves defining columns/fields and creating the tables/files with optional indexes.

Below is the code for Define():

      //
      // Define()
      //
      // Create the tables
      //

      static void Define()
      {
         Console.WriteLine("DEFINE");

         Create_CustomerMaster_Table();
         Create_CustomerOrders_Table();
         Create_OrderItems_Table();
         Create_ItemMaster_Table();
      }


      //
      // Create_CustomerMaster_Table()
      //
      // Create the table CustomerMaster
      //

      static void Create_CustomerMaster_Table()
      {
         Console.WriteLine("\ttable CustomerMaster...");

         try
         {
            cmd.CommandText = "CREATE TABLE custmast (" +
                "cm_custnumb CHAR(4)," +
                "cm_custzipc CHAR(9)," +
                "cm_custstat CHAR(2)," +
                "cm_custrtng CHAR(1)," +
                "cm_custname VARCHAR(47)," +
                "cm_custaddr VARCHAR(47)," +
                "cm_custcity VARCHAR(47))";
            cmd.ExecuteNonQuery();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }

         try
         {
            cmd.CommandText = "CREATE UNIQUE INDEX cm_custnumb_idx ON custmast (cm_custnumb)";
            cmd.ExecuteNonQuery();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }


      //
      // Create_CustomerOrders_Table()
      //
      // Create the table CustomerOrders
      //

      static void Create_CustomerOrders_Table()
      {
         Console.WriteLine("\ttable CustomerOrders...");

         try
         {
            cmd.CommandText = "CREATE TABLE custordr (" +
                "co_ordrdate DATE," +
                "co_promdate DATE," +
                "co_ordrnumb CHAR(6)," +
                "co_custnumb CHAR(4))";
            cmd.ExecuteNonQuery();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }

         try
         {
            cmd.CommandText = "CREATE UNIQUE INDEX co_ordrnumb_idx ON custordr (co_ordrnumb)";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "CREATE INDEX co_custnumb_idx ON custordr (co_custnumb)";
            cmd.ExecuteNonQuery();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }


      //
      // Create_OrderItems_Table()
      //
      // Create the table OrderItems
      //

      static void Create_OrderItems_Table()
      {
         Console.WriteLine("\ttable OrderItems...");
         
         try
         {
            cmd.CommandText = "CREATE TABLE ordritem (" +
                "oi_sequnumb SMALLINT," +
                "oi_quantity SMALLINT," +
                "oi_ordrnumb CHAR(6)," +
                "oi_itemnumb CHAR(5))";
            cmd.ExecuteNonQuery();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Console.WriteLine("Application Exception : " + e.Message + "\n");
         }

         try
         {
            cmd.CommandText = "CREATE UNIQUE INDEX oi_ordrnumb_idx ON ordritem (oi_ordrnumb, oi_sequnumb)";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "CREATE INDEX oi_itemnumb_idx ON ordritem (oi_itemnumb)";
            cmd.ExecuteNonQuery();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }


      //
      // Create_ItemMaster_Table()
      //
      // Create the table ItemMaster
      //

      static void Create_ItemMaster_Table()
      {
         Console.WriteLine("\ttable ItemMaster...");

         try
         {
            cmd.CommandText = "CREATE TABLE itemmast (" +
                "im_itemwght INTEGER," +
                "im_itempric MONEY," +
                "im_itemnumb CHAR(5)," +
                "im_itemdesc VARCHAR(47))";
            cmd.ExecuteNonQuery();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }

         try
         {
            cmd.CommandText = "CREATE INDEX im_itemnumb_idx ON itemmast (im_itemnumb)";
            cmd.ExecuteNonQuery();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }

Manage

Manage() provides data management functionality for your application and/or process.

Below is the code for Manage():

      static void Manage()
      {
         Console.WriteLine("MANAGE");

         // populate the tables with data
         Add_CustomerMaster_Records();
         Add_CustomerOrders_Records();
         Add_OrderItems_Records();
         Add_ItemMaster_Records();

         // perform a query:
         // list customer name and total amount per order

         // name               total
         // @@@@@@@@@@@@@      $xx.xx

         // for each order in the CustomerOrders table
         //    fetch order number
         //    fetch customer number
         //    fetch name from CustomerMaster table based on customer number
         //    for each order item in OrderItems table
         //       fetch item quantity
         //       fetch item number
         //       fetch item price from ItemMaster table based on item number
         //    next
         // next

         Console.WriteLine("\n\tQuery Results...");

         try
         {
            cmd.CommandText = "SELECT cm_custname, SUM(im_itempric * oi_quantity) " +
               "FROM custmast, custordr, ordritem, itemmast " +
               "WHERE co_custnumb = cm_custnumb AND co_ordrnumb = oi_ordrnumb AND oi_itemnumb = im_itemnumb " +
               "GROUP BY cm_custnumb, cm_custname";

            // get a resultset
            rdr = (CtreeSqlDataReader)cmd.ExecuteReader();

            // read the returned resultset
            while (rdr.Read())
            {
               Console.WriteLine("\t\t{0:-20s} \t{1}", rdr.GetString(0), rdr.GetString(1));
            }

            // close the reader
            rdr.Close();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }


      //
      // Add_CustomerMaster_Records()
      //
      // This function adds records to table CustomerMaster from an
      // array of strings
      //

      static void Add_CustomerMaster_Records()
      {
         String[] data = {
            "('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')",
            "('1001','61434','CT','1','Michael Jordan','13 Main','Harford')",
            "('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')",
            "('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')"
         };

         Console.WriteLine("\tAdd records in table CustomerMaster...");

         Delete_Records("custmast");

         try
         {
            // add one record at time to table
            for (int i = 0; i < data.Length; i++)
            {
               cmd.CommandText = "INSERT INTO custmast VALUES " + data[i];
               cmd.ExecuteNonQuery();
            }
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }


      //
      // Add_CustomerOrders_Records()
      //
      // This function adds records to table CustomerOrders from an
      // array of strings
      //

      static void Add_CustomerOrders_Records()
      {
         String[] data = {
            "('09/01/2002','09/05/2002','1','1001')",
            "('09/02/2002','09/06/2002','2','1002')"
         };

         Console.WriteLine("\tAdd records in table CustomerOrders...");

         Delete_Records("custordr");

         try
         {
            // add one record at time to table
            for (int i = 0; i < data.Length; i++)
            {
               cmd.CommandText = "INSERT INTO custordr VALUES " + data[i];
               cmd.ExecuteNonQuery();
            }
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }


      //
      // Add_OrderItems_Records()
      //
      // This function adds records to table OrderItems from an
      // array of strings
      //

      static void Add_OrderItems_Records()
      {
         String[] data = {
            "(1,2,'1','1')",
            "(2,1,'1','2')",
            "(3,1,'1','3')",
            "(1,3,'2','3')"
         };

         Console.WriteLine("\tAdd records in table OrderItems...");

         Delete_Records("ordritem");

         try
         {
            // add one record at time to table
            for (int i = 0; i < data.Length; i++)
            {
               cmd.CommandText = "INSERT INTO ordritem VALUES " + data[i];
               cmd.ExecuteNonQuery();
            }
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }


      //
      // Add_ItemMaster_Records()
      //
      // This function adds records to table ItemMaster from an
      // array of strings
      //

      static void Add_ItemMaster_Records()
      {
         String[] data = {
            "(10,19.95,'1','Hammer')",
            "(3,  9.99,'2','Wrench')",
            "(4, 16.59,'3','Saw')",
            "(1,  3.98,'4','Pliers')"
         };

         Console.WriteLine("\tAdd records in table ItemMaster...");

         Delete_Records("itemmast");

         try
         {
            // add one record at time to table
            for (int i = 0; i < data.Length; i++)
            {
               cmd.CommandText = "INSERT INTO itemmast VALUES " + data[i];
               cmd.ExecuteNonQuery();
            }
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }


      //
      // Delete_Records()
      //
      // This function deletes all the records in a tables
      //

      static void Delete_Records(String Table)
      {
         Console.WriteLine("\tDelete records...");

         try
         {
            cmd.CommandText = "DELETE FROM " + Table;
            cmd.ExecuteNonQuery();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }

Done

When an application and/or process has completed operations with the database, it must release resources and close the open files by disconnecting from the database engine.

Below is the code for Done():

      //
      // Done()
      //
      // This function handles the housekeeping of closing, freeing,
      // disconnecting and logging out of the database
      //

      static void Done()
      {
         Console.WriteLine("DONE");

         try
         {
            // logout
            Console.WriteLine("\tLogout...");
            conn.Close();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }

Additional Resources

We encourage you to explore the additional resources listed here:

• Complete source code for this tutorial can be found in CSharpTutorial2.cs in your installation directory, within the csharp.sql.ado.net\tutorials directory for your platform.
   
• Additional documentation may be found on the FairCom Web site.

Record/Row Locking

drivers\csharp.sql.ado.net\tutorials\CSharpTutorial3.cs

Now we will explore row/record locks using the FairCom DB ADO.NET Data Provider using C#.

The functionality for this tutorial focuses on inserting/adding rows/records, then updating a single row/record in the customer master table under locking control. The application will pause after a LOCK is placed on a row/record. Another instance of this application should then be launched, which will block, waiting on the lock held by the first instance. Pressing the <Enter> key will enable the first instance to proceed. This will result in removing the lock thereby allowing the second instance to continue execution. Launching two processes provides a visual demonstration of the effects of locking and a basis for experimentation on your own.

As with all other examples in the c-tree tutorial series, this tutorial simplifies the creation and use of a database into four simple steps: Initialize(), Define(), Manage(), and you’re Done() !

Tutorial #3: Locking

Here we demonstrate the enforcement of data integrity by introducing record/row "locking".

• Initialize() - Connects to the FairCom Database Engine.
• Define() - Defines and creates a "customer master" (custmast) table/file.
• Manage() - Adds a few rows/records, manipulates them, and displays the results.
• Done() - Disconnects from FairCom Database Engine.

Note our simple Main() function:

using System;
using Ctree.Data.SqlClient;

namespace CSharpTutorial
{
   class Tutorial3
   {
      // declare connection, command and reader objects
      static CtreeSqlConnection conn;
      static CtreeSqlCommand cmd;
      static CtreeSqlDataReader rdr;

      //
      // main()
      //
      // The main() function implements the concept of "init, define, manage
      // and you're done..."
      //

      static void Main()
      {
         Initialize();

         Define();

         Manage();

         Done();

         Console.Write("\nPress <ENTER> key to exit . . .");
         Console.ReadLine();
      }

We suggest opening the source code with your own editor.

Continue now to review these four steps.

Init

First we need to open a connection to a database by providing the FairCom Database Engine with a user name, password and the database name.

Below is the code for Initialize():

      static void Initialize()
      {
         Console.WriteLine("INIT");

         try
         {
            // initialize connection object
            conn = new CtreeSqlConnection();
            conn.ConnectionString = "UID=ADMIN;PWD=ADMIN;Database=CtreeSql;Server=localhost;Service=6597;";

            // initialize command object
            cmd = new CtreeSqlCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Connection = conn;

            // connect to server
            Console.WriteLine("\tLogon to server...");
            conn.Open();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }

Define

Define() establishes specific data definitions. This involves defining columns/fields and creating the tables/files with optional indexes.

Below is the code for Define():

      //
      // Define()
      //
      // Create the table for containing a list of existing customers
      //

      static void Define()
      {
         Console.WriteLine("DEFINE");

         try
         {
            // create table
            Console.WriteLine("\tCreate table...");
            cmd.CommandText = "CREATE TABLE custmast (" +
               "cm_custnumb CHAR(4)," +
               "cm_custzipc CHAR(9)," +
               "cm_custstat CHAR(2)," +
               "cm_custrtng CHAR(1)," +
               "cm_custname VARCHAR(47)," +
               "cm_custaddr VARCHAR(47)," +
               "cm_custcity VARCHAR(47))";
            cmd.ExecuteNonQuery();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }

         try
         {
            cmd.CommandText = "CREATE UNIQUE INDEX cm_custnumb_idx ON custmast (cm_custnumb)";
            cmd.ExecuteNonQuery();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }

Manage

Manage() provides data management functionality for your application and/or process.

Below is the code for Manage():

      static void Manage()
      {
         Console.WriteLine("MANAGE");

         // delete any existing records
         Delete_Records();

         // populate the table with data
         Add_CustomerMaster_Records();

         // display contents of table
         Display_Records();

         // update a record under locking control
         Update_CustomerMaster_Record();

         // display again after update and effects of lock
         Display_Records();
      }



      // Delete_Records()
      //
      // This function deletes all the records in the table
      //

      static void Delete_Records()
      {
         Console.WriteLine("\tDelete records...");

         try
         {
            cmd.CommandText = "DELETE FROM custmast";
            cmd.ExecuteNonQuery();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }


      //
      // Add_CustomerMaster_Records()
      //
      // This function adds records to table CustomerMaster from an
      // array of strings
      //

      static void Add_CustomerMaster_Records()
      {
         String[] data = {
            "('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')",
            "('1001','61434','CT','1','Michael Jordan','13 Main','Harford')",
            "('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')",
            "('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')"
         };

         Console.WriteLine("\tAdd records...");

         try
         {
            // add one record at time to table
            for (int i = 0; i < data.Length; i++)
            {
               cmd.CommandText = "INSERT INTO custmast VALUES " + data[i];
               cmd.ExecuteNonQuery();
            }
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }


      //
      // Display_Records()
      //
      // This function displays the contents of a table.
      //

      static void Display_Records()
      {
         Console.Write("\tDisplay records...");

         try
         {
            cmd.CommandText = "SELECT * FROM custmast";

            // get a resultset
            rdr = (CtreeSqlDataReader)cmd.ExecuteReader();

            // read the returned resultset
            while (rdr.Read())
            {
               Console.WriteLine("\n\t\t{0}   {1}", rdr.GetString(0), rdr.GetString(4));
            }
            // close the reader
            rdr.Close();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }


      //
      // Update_CustomerMaster_Records()
      //
      // Update one record under locking control to demonstrate the effects
      // of locking
      //

      static void Update_CustomerMaster_Record()
      {
         Console.WriteLine("\tUpdate record...");

         try
         {
            cmd.Transaction = conn.BeginTransaction();
            cmd.CommandText = "UPDATE custmast SET cm_custname = 'KEYON DOOLING' where cm_custnumb = '1003'";
            cmd.ExecuteNonQuery();

            Console.WriteLine("\tPress <ENTER> key to unlock");
            Console.ReadLine();

            cmd.Transaction.Commit();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
            cmd.Transaction.Rollback();
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }

Done

When an application and/or process has completed operations with the database, it must release resources and close the open files by disconnecting from the database engine.

Below is the code for Done():

      //
      // Done()
      //
      // This function handles the housekeeping of closing, freeing,
      // disconnecting and logging out of the database
      //

      static void Done()
      {
         Console.WriteLine("DONE");

         try
         {
            // logout
            Console.WriteLine("\tLogout...");
            conn.Close();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }

Additional Resources

We encourage you to explore the additional resources listed here:

• Complete source code for this tutorial can be found in CSharpTutorial3.cs in your installation directory, within the csharp.sql.ado.net\tutorials directory for your platform.
• Additional documentation may be found on the FairCom Web site.

Transactions

drivers\csharp.sql.ado.net\tutorials\CSharpTutorial4.cs

Now we will discuss transaction processing as it relates to the FairCom DB ADO.NET Data Provider using C#.

Transaction processing provides a safe method by which multiple database operations spread across separate tables/files are guaranteed to be atomic. By atomic, we mean that, within a transaction, either all of the operations succeed or none of the operations succeed. This "either all or none" atomicity ensures that the integrity of the data in related tables/files is secure.

Like all other examples in the c-tree tutorial series, this tutorial simplifies the creation and use of a database into four simple steps: Initialize(), Define(), Manage(), and You’re Done()!

Tutorial #4: Transaction Processing

Here we demonstrate transaction control.

• Initialize() - Connects to the FairCom Database Engine.
• Define() - Defines and creates our four tables/files.
• Manage() - Adds rows/records to multiple tables/files under transaction control.
• Done() - Disconnects from FairCom Database Engine.

Note our simple Main() function:

using System;
using System.Collections;
using Ctree.Data.SqlClient;

namespace CSharpTutorial
{
   class Tutorial4
   {
      // declare connection, command and reader objects
      static CtreeSqlConnection conn;
      static CtreeSqlCommand cmd;
      static CtreeSqlDataReader rdr;

      //
      // main()
      //
      // The main() function implements the concept of "init, define, manage
      // and you're done..."
      //

      static void Main()
      {
         Initialize();

         Define();

         Manage();

         Done();

         Console.Write("\nPress <ENTER> key to exit . . .");
         Console.ReadLine();
      }

We suggest opening the source code with your own editor.

Continue now to review these four steps.

Init

First we need to open a connection to a database by providing the FairCom Database Engine with a user name, password and the database name.

Below is the code for Initialize():

      //
      // Initialize()
      //
      // Perform the minimum requirement of logging onto the c-tree Server
      //

      static void Initialize()
      {
         Console.WriteLine("INIT");

         try
         {
            // initialize connection object
            conn = new CtreeSqlConnection();
            conn.ConnectionString = "UID=ADMIN;PWD=ADMIN;Database=CtreeSql;Server=localhost;Service=6597;";

            // initialize command object
            cmd = new CtreeSqlCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Connection = conn;
            cmd.Transaction = null;

            // connect to server
            Console.WriteLine("\tLogon to server...");
            conn.Open();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }

Define

Define() establishes specific data definitions. This involves defining columns/fields and creating the tables/files with optional indexes.

Below is the code for Define():

      //
      // Define()
      //
      // Create the tables
      //

      static void Define()
      {
         Console.WriteLine("DEFINE");

         // delete tables ...
         Delete_Tables();
         // ...and re-create them with constraints
         Create_CustomerMaster_Table();
         Create_CustomerOrders_Table();
         Create_OrderItems_Table();
         Create_ItemMaster_Table();
      }
 


      //
      // Delete_Tables()
      //
      // This function removes all existing tables
      //

      static void Delete_Tables()
      {

         try
         {
            cmd.CommandText = "DROP TABLE ordritem";
            cmd.ExecuteNonQuery();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
         try
         {
            cmd.CommandText = "DROP TABLE custordr";
            cmd.ExecuteNonQuery();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
         try
         {
            cmd.CommandText = "DROP TABLE custmast";
            cmd.ExecuteNonQuery();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
         try
         {
            cmd.CommandText = "DROP TABLE itemmast";
            cmd.ExecuteNonQuery();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }


      //
      // Create_CustomerMaster_Table()
      //
      // Create the table CustomerMaster
      //

      static void Create_CustomerMaster_Table()
      {
         Console.WriteLine("\ttable CustomerMaster...");

         try
         {
            cmd.CommandText = "CREATE TABLE custmast (" +
               "cm_custnumb CHAR(4) PRIMARY KEY, " +
               "cm_custzipc CHAR(9), " +
               "cm_custstat CHAR(2), " +
               "cm_custrtng CHAR(1), " +
               "cm_custname VARCHAR(47), " +
               "cm_custaddr VARCHAR(47), " +
               "cm_custcity VARCHAR(47))";
            cmd.ExecuteNonQuery();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }


      //
      // Create_CustomerOrders_Table()
      //
      // Create the table CustomerOrders
      //

      static void Create_CustomerOrders_Table()
      {
         Console.WriteLine("\ttable CustomerOrders...");

         try
         {
            cmd.CommandText = "CREATE TABLE custordr (" +
               "co_ordrdate DATE, " +
               "co_promdate DATE, " +
               "co_ordrnumb CHAR(6) PRIMARY KEY, " +
               "co_custnumb CHAR(4), " +
               "FOREIGN KEY (co_custnumb) REFERENCES custmast)";
            cmd.ExecuteNonQuery();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }


      //
      // Create_OrderItems_Table()
      //
      // Create the table OrderItems
      //

      static void Create_OrderItems_Table()
      {
         Console.WriteLine("\ttable OrderItems...");

         try
         {
            cmd.CommandText = "CREATE TABLE ordritem (" +
               "oi_sequnumb SMALLINT, " +
               "oi_quantity SMALLINT, " +
               "oi_ordrnumb CHAR(6), " +
               "oi_itemnumb CHAR(5), " +
               "FOREIGN KEY (oi_itemnumb) REFERENCES itemmast, " +
               "FOREIGN KEY (oi_ordrnumb) REFERENCES custordr)";
            cmd.ExecuteNonQuery();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }


      //
      // Create_ItemMaster_Table()
      //
      // Create the table ItemMaster
      //

      static void Create_ItemMaster_Table()
      {
         Console.WriteLine("\ttable ItemMaster...");

         try
         {
            cmd.CommandText = "CREATE TABLE itemmast (" +
               "im_itemwght INTEGER, " +
               "im_itempric MONEY, " +
               "im_itemnumb CHAR(5) PRIMARY KEY, " +
               "im_itemdesc VARCHAR(47))";
            cmd.ExecuteNonQuery();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }

Manage

Manage() provides data management functionality for your application and/or process.

Below is the code for Manage():

      //
      // Manage()
      //
      // Populates table and perform a simple query
      //

      static void Manage()
      {
         Console.WriteLine("MANAGE");

         // populate the tables with data
         Add_CustomerMaster_Records();
         Add_ItemMaster_Records();

         Add_Transactions();

         // display the orders and their items
         Display_CustomerOrders();
         Display_OrderItems();
      }

 
      // Add_CustomerMaster_Records()
      //
      // This function adds records to table CustomerMaster from an
      // array of strings
      //

      static void Add_CustomerMaster_Records()
      {
         String[] data = {
            "('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')",
            "('1001','61434','CT','1','Michael Jordan','13 Main','Harford')",
            "('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')",
            "('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')"
         };

         Console.WriteLine("\tAdd records in table CustomerMaster...");

         try
         {
            // add one record at time to table
            for (int i = 0; i < data.Length; i++)
            {
               cmd.CommandText = "INSERT INTO custmast VALUES " + data[i];
               cmd.ExecuteNonQuery();
            }
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }


      //
      // Add_ItemMaster_Records()
      //
      // This function adds records to table ItemMaster from an
      // array of strings
      //

      static void Add_ItemMaster_Records()
      {
         String[] data = {
            "(10,19.95,'1','Hammer')",
            "(3,  9.99,'2','Wrench')",
            "(4, 16.59,'3','Saw')",
            "(1,  3.98,'4','Pliers')"
         };

         Console.WriteLine("\tAdd records in table ItemMaster...");

         try
         {
            // add one record at time to table
            for (int i = 0; i < data.Length; i++)
            {
               cmd.CommandText = "INSERT INTO itemmast VALUES " + data[i];
               cmd.ExecuteNonQuery();
            }
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }


      //
      // Add_Transactions()
      //
      // Add an Order and associated Items "as a transaction" to their
      // respective tables.  A transaction is committed or aborted if the
      // customer number on the order is confirmed valid.  Likewise each
      // item in the order is verified to be a valid item.
      //

      static void Add_Transactions()
      {
         String[,] orders = {
            { "09/01/2002", "09/05/2002", "1", "1001" },
            { "09/02/2002", "09/06/2002", "2", "9999" },  // bad customer number
            { "09/22/2002", "09/26/2002", "3", "1003" }
         };

         String[,] items = {
            { "1", "1", "2", "1" },
            { "1", "2", "1", "2" },
            { "2", "1", "1", "3" },
            { "2", "2", "3", "4" },
            { "3", "1", "2", "3" },
            { "3", "2", "2", "99"}  // bad item number
         };

         Console.WriteLine("\tAdd transaction records...");

         for (int i = 0, j = 0; i < orders.GetLength(0); i++)
         {
            try
            {
               cmd.Transaction = conn.BeginTransaction();
               cmd.CommandText = "INSERT INTO custordr VALUES (" +
                  "'" + orders[i, 0] + "', " +
                  "'" + orders[i, 1] + "', " +
                  "'" + orders[i, 2] + "', " +
                  "'" + orders[i, 3] + "')";
               cmd.ExecuteNonQuery();
            }
            catch (CtreeSqlException e)
            {
               Handle_Exception(e);
            }
            catch (Exception e)
            {
               Handle_Exception(e);
            }

            // process order items
            while (items[j, 0].CompareTo(orders[i, 2]) == 0)
            {
               try
               {
                  // add item record
                  cmd.CommandText = "INSERT INTO ordritem VALUES (" +
                     items[j, 1] + ", " +
                     items[j, 2] + ", " +
                     "'" + items[j, 0] + "', " +
                     "'" + items[j, 3] + "')";
                  cmd.ExecuteNonQuery();
               }
               catch (CtreeSqlException e)
               {
                  Handle_Exception(e);
               }
               catch (Exception e)
               {
                  Handle_Exception(e);
               }

               // bump to next item
               j++;

               // exit the while loop on last item
               if (j >= items.GetLength(0))
                  break;
            }

            try
            {
               cmd.Transaction.Commit();
            }
            catch (CtreeSqlException e)
            {
               Handle_Exception(e);
               cmd.Transaction.Rollback();
            }
            catch (Exception e)
            {
               Handle_Exception(e);
            }
         }
      }


      //
      // Display_CustomerOrders()
      //
      // This function displays the contents of CustomerOrders table.
      //

      static void Display_CustomerOrders()
      {
         Console.WriteLine("\n\tCustomerOrders Table...");

         try
         {
            // execute a query statement
            cmd.CommandText = "SELECT * FROM custordr";
            rdr = (CtreeSqlDataReader)cmd.ExecuteReader();

            // fetch and display each individual record
            while (rdr.Read())
            {
               Console.WriteLine("\t   {0}   {1}", rdr.GetString(2), rdr.GetString(3));
            }
            rdr.Close();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }


      //
      // Display_OrderItems()
      //
      // This function displays the contents of OrderItems table.
      //

      static void Display_OrderItems()
      {
         Console.WriteLine("\n\tOrderItems Table...");

         try
         {
            // execute a query statement
            cmd.CommandText = "SELECT * FROM ordritem";
            rdr = (CtreeSqlDataReader)cmd.ExecuteReader();

            // fetch and display each individual record
            while (rdr.Read())
            {
               Console.WriteLine("\t   {0}   {1}", rdr.GetString(2), rdr.GetString(3));
            }
            rdr.Close();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }

Done

When an application and/or process has completed operations with the database, it must release resources and close the open files by disconnecting from the database engine.

Below is the code for Done():

      //
      // Done()
      //
      // This function handles the housekeeping of closing, freeing,
      // disconnecting and logging out of the database
      //

      static void Done()
      {
         Console.WriteLine("DONE");

         Delete_Tables();

         try
         {
            // logout
            Console.WriteLine("\tLogout...");
            conn.Close();
         }
         catch (CtreeSqlException e)
         {
            Handle_Exception(e);
         }
         catch (Exception e)
         {
            Handle_Exception(e);
         }
      }

Additional Resources

We encourage you to explore the additional resources listed here:

• Complete source code for this tutorial can be found in CSharpTutorial4.cs in your installation directory, within the csharp.sql.ado.net\tutorials directory for your platform.
   
• Additional documentation may be found on the FairCom Web site.