FairCom — Developing ADO.NET Applications with FairCom DB SQL

Creating applications with FairCom DB databases is easy with the FairCom DB Data Provider. Simply connect to the database, create your commands and execute them. Data sets and data grids are easy to populate with data, and the advanced transaction handling of FairCom DB can be used to secure your operations. Parameterized commands make it easy to reuse SQL statements. The following sections describe each of these abilities and more with examples.

Ctree.Data.SqlClient Namespace

When developing applications with the FairCom DB SQL ADO.NET Data Provider you may have to specify at least the following namespaces in your source code modules:

System
System.Data
Ctree.Data.SqlClient

The examples below shows how to specify the namespaces with various .NET languages.

.NET VB Example

Imports System
Imports System.Data
Imports Ctree.Data.SqlClient

.NET C# Example

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

.NET C++ Example

using namespace System;
using namespace System::Data;
using namespace Ctree::Data::SqlClient;

.NET Delphi Example

Uses
   System, System.Data, Ctree.Data.SqlClient;

Connecting to a FairCom DB SQL Database

A CtreeSqlConnection object represents a unique session to a FairCom DB SQL database. When you create an instance of CtreeSqlConnection, all properties are set to their initial values: the database name is set to “c-treeSQL”, the username and password are set to empty strings, the database server address is set to “localhost” and the service is set to “6597”. If the CtreeSqlConnection goes out of scope, it is not closed. Therefore, you must explicitly close the connection by calling the methods Close() or Dispose().

The following examples shows how to connect to a FairCom DB SQL database using the FairCom DB Data Provider and when the connection is no longer needed, close the connection.

.NET VB Example

Sub Connect()
   Dim conString As String = “User=ADMIN;Password=ADMIN;Database=ctreeSQL”
   Dim hConnection As New CtreeSqlConnection(conString)
 
   hConnection.Open()
   ... perform some operations ...
   hConnection.Close()
End Sub

.NET C# Example

void Connect()
{
   String conString = “User=ADMIN;Password=ADMIN;Database=ctreeSQL”;
   CtreeSqlConnection hConnection = new CtreeSqlConnection(conString);
 
   hConnection.Open();
   ... perform some operations ...
   hConnection.Close();
}

.NET C++ Example

void Connect()
{
   String* conString = S“User=ADMIN;Password=ADMIN;Database=ctreeSQL”;
   CtreeSqlConnection* hConnection = new CtreeSqlConnection(conString);
 
   hConnection->Open();
   ... perform some operations ...
   hConnection->Close();
}

.NET Delphi Example

procedure Connect()
var
    conString : String;
    hConnection : CtreeSqlConnection;
 
begin
   conString := 'User=ADMIN;Password=ADMIN;Database=ctreeSQL';
   hConnection := CtreeSqlConnection.Create(conString);
   hConnection.Open();
   ... perform some operations ...
   hConnection.Close();
end;

Executing FairCom DB SQL Commands

After establishing a connection to a FairCom DB SQL database, you can execute commands and return results from a data source using a CtreeSqlCommand object. The following steps are usually necessary to execute commands:

Instantiate and initialize a new CtreeSqlConnection object.
Instantiate a new CtreeSqlCommand object and set its Connection property.
Set the CtreeSqlCommand CommandText property with the the FairCom DB SQL command to be executed.
Optionally, call the Prepare() method.
Call one of the CtreeSqlCommand object methods to execute the command: ExecuteNonQuery(), ExecuteReader() or ExecuteScalar().

A CtreeSqlCommand object can be constructed with a default constructor with no arguments or using optional arguments of SQL language command text and/or a CtreeSqlConnection. The command text can be queried or modified using the CommandText property.

CtreeSqlCommand object exposes several Execute() methods that can be used to perform the intended SQL action:

ExecuteNonQuery() executes a SQL statement against a FairCom DB SQL database and returns the number of rows affected.
ExecuteScalar() executes the query, and returns the first column of the first row in the result set returned by the query. Extra columns or rows are ignored.
ExecuteDataReader() sends the CommandText to the FairCom DB SQL database and builds a CtreeSqlDataReader object with the resulting row set.

Below are examples demonstrating command execution in various .NET implementations.

.NET VB Example

Sub CreateTable()
   Dim conString As String = “User=ADMIN;Password=ADMIN;Database=ctreeSQL”
   Dim hConnection As New CtreeSqlConnection(conString)
   Dim hCommand As New CtreeSqlCommand(hConnection)
 
   hConnection.Open()
   hCommand.CommandText = “create table tab1 (name char(20), age integer)”
   hCommand.ExecuteNonQuery()
   hConnection.Close()
End Sub

.NET C# Example

void CreateTable()
{
   String conString = “User=ADMIN;Password=ADMIN;Database=ctreeSQL”;
   CtreeSqlConnection hConnection = new CtreeSqlConnection(conString);
   CtreeSqlCommand hCommand = new CtreeSqlCommand(hConnection);
 
   hConnection.Open();
   hCommand.CommandText = “create table tab1 (name (char(20), age integer)”;
   hCommand.ExecuteNonQuery();
   hConnection.Close();
}

.NET C++ Example

void CreateTable()
{
   String* conString = S“User=ADMIN;Password=ADMIN;Database=ctreeSQL”;
   CtreeSqlConnection* hConnection = new CtreeSqlConnection(conString);
   CtreeSQLCommand* hCommand = new CtreeSQLCommand(hConnection);
 
   hConnection->Open();
   hCommand->CommandText = “create table tab1 (name (char(20), age integer)”;
   hCommand->ExecuteNonQuery();
   hConnection->Close();
}

.NET Delphi Example

procedure CreateTable
var
   conString : String;
   hConnection : CtreeSqlConnection;
   hCommand : CtreeSqlCommand;
 
begin
   conString := 'User=ADMIN;Password=ADMIN;Database=ctreeSQL';
   hConnection := CtreeSqlConnection.Create(conString);
   hCommand := CtreeSqlCommand(hConnection);
   hConnection.Open();
   hCommand.CommandRText := 'create table tab1 (name (char(20), age integer)';
   hCommand.ExecuteNonQuery();
   hConnection.Close();   
end;

Command Type

CtreeSqlCommand.CommandType property indicates how the FairCom DB SQL database command is to be interpreted. CommandType.Text and CommandType.StoredProcedure are supported. By default, CtreeSqlCommand.CommandType is set to CommandType.Text. If you try to set the CommandType property of CtreeSqlCommand object with any other value, an UnsupportedException is thrown. The other possible value for CommandType is TableDirect. (According to Microsoft documentation, TableDirect is only supported by an OleDB .NET data provider.)

CommandType.Text. is used to specify any FairCom DB SQL command.
CommandType.StoredProcedure is used to call a FairCom DB Stored Procedure. Set CtreeSqlCommand.CommandText to the name of the stored procedure, without parenthesis () and without parameters. Then set CtreeSqlCommand.CommandType to CommandType.StoredProcedure.
Create and add CtreeSqlParameters as needed.
Call CtreeSqlCommand.ExecuteReader() to execute the command or stored procedure.

Reading Data

ADO.NET provides a basic object to read data from a database. This object is the DataReader, or CtreeSqlDataReader in the particular case of our data provider. CtreeSqlDataReader is a fast forward-only and read-only representation of the data returned by a FairCom DB SQL query. A DataReader object is obtained by calling the ExecuteReader() method of the CtreeSqlCommand object, after setting the CommandText property of a CtreeSqlCommand object to the appropriate FairCom DB SQL query.

You should always call the Close() method when you have finished using the CtreeSqlDataReader object.

The examples below show how to use a DataReader with various .NET languages.

.NET VB Example

Sub DisplayNames()
   Dim conString As String = “User=ADMIN;Password=ADMIN;Database=ctreeSQL”
   Dim hConnection As New CtreeSqlConnection(conString)
   Dim hCommand As New CtreeSqlCommand(hConnection)
   Dim reader As CtreeSqlDataReader
 
   hConnection.Open()
   hCommand.CommandText = “select name from tab1”
   reader = hCommand.ExecuteReader()
 
   While reader.Read()
      Console.WriteLine(reader[“name”])
   End While
 
   reader.Close()
   hConnection.Close()
End Sub

.NET C# Example

void DisplayNames()
{
   String conString = “User=ADMIN;Password=ADMIN;Database=ctreeSQL”;
   CtreeSqlConnection hConnection = new CtreeSqlConnection(conString);
   CtreeSqlCommand hCommand = new CtreeSqlCommand(hConnection);
   CtreeSqlDataReader reader;
 
   hConnection.Open();
   hCommand.CommandText = “select name from tab1”;
   reader = hCommand.ExecuteReader();
 
   while (reader.Read())
        Console.WriteLine(reader[“name”]);
 
   reader.Close();
   hConnection.Close();
}

.NET C++ Example

void CreateTable()
{
   String* conString = S“User=ADMIN;Password=ADMIN;Database=ctreeSQL”;
   CtreeSqlConnection* hConnection = new CtreeSqlConnection(conString);
   CtreeSqlCommand* hCommand = new CtreeSqlCommand(hConnection);
   CtreeSqlDataReader* reader;
 
   hConnection->Open();
   hCommand->CommandText = “select name from tab1”;
   reader = hCommand->ExecuteReader();
 
   while (reader->Read())
      Console::WriteLine(reader[“name”]);
 
   reader->Close();
   hConnection->Close();
}

.NET Delphi Example

procedure DisplayNames
var
   conString : String;
   hConnection : CtreeSqlConnection;
   hCommand : CtreeSqlCommand;
   reader : CtreeSqlDataReader;
 
begin
   conString := 'User=ADMIN;Password=ADMIN;Database=ctreeSQL';
   hConnection := CtreeSqlConnection.Create(conString);
   hCommand := CtreeSqlCommand(hConnection);
   hConnection.Open();
   hCommand.CommandText := 'select name from tab1';
   reader := hCommand.ExecuteReader();
 
   while reader.Read() do
      Console.WriteLine(reader['name']);
 
   reader.Close();
   hConnection.Close();   
end;

Reading Blob Data

By default, the CtreeSqlDataReader loads incoming data as a row as soon as an entire row of data is available. Binary large objects (blobs) need different treatment, however, because they can contain gigabytes of data that cannot be contained in a single row.

When accessing the data in the blob field, use the GetBytes() or GetChars() typed accessors of the CtreeSqlDataReader object, which fill an array with data. You can also use GetString() for character data. However, to conserve system resources you might not want to load an entire blob value into a single string variable. You can instead specify a specific buffer size of data to be returned, and a starting location for the first byte or character to be read from the returned data. GetBytes() and GetChars() will return a long value, which represents the number of bytes or characters returned. If you pass a null array to GetBytes() or GetChars(), the long value returned will be the total number of bytes or characters in the blob. You can optionally specify an index in the array as a starting position for the data being read.

The FairCom DB SQL database has two different types of blob fields: LVARCHAR, that store long variable character data and LVARBINARY used to store an arbitrary number of binary bytes. You should consider using either CtreeSqlDataReader GetBytes() or GetChars() methods when reading LVARCHAR or LVARBINARY columns from a FairCom DB SQL database.

Transactions and Isolation Levels

Database transaction operations are an important element of many data-driven applications. To start a transaction you must call method CtreeSqlConnection.BeginTransaction() to obtain a CtreeSqlTransaction object. Once you instantiated a CtreeSqlTransaction object, you can use it to commit or rollback transactions.

.NET VB Example

Sub CreateTable()
   Dim conString As String = “User=ADMIN;Password=ADMIN;Database=ctreeSQL”
   Dim hConnection As New CtreeSqlConnection(conString)
   Dim hCommand As New CtreeSqlCommand(hConnection)
   Dim hTransaction As CtreeSqlTransaction

   hConnection.Open()
   hTransaction = hConnection.BeginTransaction(IsolationLevel.ReadCommitted)
   hCommand.Transaction = hTransaction
   hCommand.CommandText = “create table tab1 (name char(20), age integer)”
   hCommand.ExecuteNonQuery()
   hTransaction.Commit()
   hConnection.Close()
End Sub

.NET C# Example

void CreateTable()
{
   String conString = “User=ADMIN;Password=ADMIN;Database=ctreeSQL”;
   CtreeSqlConnection hConnection = new CtreeSqlConnection(conString);
   CtreeSqlCommand hCommand = new CtreeSqlCommand(hConnection);
   CtreeSqlTransaction hTransaction;

   hConnection.Open();
   hTransaction = hConnection.BeginTransaction(IsolationLevel.ReadCommitted);
   hCommand.Transaction = hTransaction;
   hCommand.CommandText = “create table tab1 (name (char(20), age integer)”;
   hCommand.ExecuteNonQuery();
   hTransaction.Commit();
   hConnection.Close();
}

.NET C++ Example

void CreateTable()
{
   String* conString = S“User=ADMIN;Password=ADMIN;Database=ctreeSQL”;
   CtreeSqlConnection* hConnection = new CtreeSqlConnection(conString);
   CtreeSqlCommand* hCommand = new CtreeSqlCommand(hConnection);
   CtreeSqlTransaction* hTransaction;

   hConnection->Open();
   hTransaction = hConnection->BeginTransaction(IsolationLevel::ReadCommitted);
   hCommand->Transaction = hTransaction;
   hCommand->CommandText = “create table tab1 (name (char(20), age integer)”;
   hCommand->ExecuteNonQuery();
   hTransaction->Commit();
   hConnection->Close();
}

.NET Delphi Example

procedure CreateTable
var
   conString : String;
   hConnection : CtreeSqlConnection;
   hCommand : CtreeSqlCommand;
   hTransaction : CtreeSqlTransaction;

begin
   conString := 'User=ADMIN;Password=ADMIN;Database=ctreeSQL';
   hConnection := CtreeSqlConnection.Create(conString);
   hCommand := CtreeSqlCommand(hConnection);
   hCommand.Transaction = hTransaction;
   hConnection.Open();
   hTransaction = hConnection.BeginTransaction(IsolationLevel.ReadCommitted);
   hCommand.CommandRText := 'create table tab1 (name (char(20), age integer)';
   hCommand.ExecuteNonQuery();
   hTransaction.Commit();
   hConnection.Close();   
end;

When the CtreeSqlTransaction object is created, by calling the BeginTransaction() method of CtreeSqlConnection object, you can optionally specify the transaction isolation level. The standard transaction isolation levels as defined by current SQL standards are as follows:

Transaction Isolation level 0 - Read Uncommitted

A dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored. (Not supported with FairCom DB SQL)
Transaction Isolation level 1 - Read Committed

Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data.
Transaction Isolation level 2 - Repeatable Read

Locks are placed on all data that is used in a query, preventing other users from updating the data. Prevents non-repeatable reads but phantom rows are still possible.
Transaction Isolation level 3 - Serializable

A range lock is placed on the DataSet preventing other users from updating or inserting rows into the dataset until the transaction is complete. (Not supported with FairCom DB SQL)

Populating a DataSet

CtreeSqlDataReader objects provide stream-based access to result sets of database queries. While streaming access is fast and efficient, it is also read-only and forward-only. You can’t, for example, back up and re-read the previous record with a CtreeSqlDataReader object or change the results and update the changes back to the database. Set-based access on the other hand, captures an entire query in memory and supports backward and forward traversal through the result set. It also lets you edit the data obtained through database queries and propagate these changes back to the data source.

Set-based data access revolves around two classes, DataSet, which is the equivalent of an in-memory database and CtreeSqlDataAdapter, which serves as a bridge between DataSets and the data source.

The following .NET examples below show how to create a DataSet from a FairCom DB SQL database.

.NET VB Example

procedure DisplayNames
var
   conString : String;
   hConnection : CtreeSqlConnection;
   hCommand : CtreeSqlCommand;
   reader : CtreeSqlDataReader;
 
begin
   conString := 'User=ADMIN;Password=ADMIN;Database=ctreeSQL';
   hConnection := CtreeSqlConnection.Create(conString);
   hCommand := CtreeSqlCommand(hConnection);
   hConnection.Open();
   hCommand.CommandText := 'select name from tab1';
   reader := hCommand.ExecuteReader();
 
   while reader.Read() do
      Console.WriteLine(reader['name']);
 
   reader.Close();
   hConnection.Close();   
end;
 
Sub DisplayNames()
   Dim conString As String = “User=ADMIN;Password=ADMIN;Database=ctreeSQL”
   Dim hConnection As New CtreeSqlConnection(conString)
   Dim hCommand As New CtreeSqlCommand(hConnection)
   Dim adapter As CtreeSqlDataAdapter
   Dim dset As New DataSet();
 
   hConnection.Open()
   hCommand.CommandText = “select * from tab1”
   adapter = new CtreeSqlDataAdapter(hCommand)
   adapter.Fill(dset);
   hConnection.Close()
End Sub

.NET C# Example

void DisplayNames()
{
   String conString = “User=ADMIN;Password=ADMIN;Database=ctreeSQL”;
   CtreeSqlConnection hConnection = new CtreeSqlConnection(conString);
   CtreeSqlCommand hCommand = new CtreeSqlCommand(hConnection);
   DataSet dset = new DataSet();
   CtreeSqlDataAdapter adapter;

   hConnection.Open();
   hCommand.CommandText = “select name from tab1”;
   adapter = new CtreeSqlDataAdapter(hCommand);
   adapter.Fill(dset);
   hConnection.Close();
}

.NET C++ Example

void CreateTable()
{
   String* conString = S“User=ADMIN;Password=ADMIN;Database=ctreeSQL”;
   CtreeSqlConnection* hConnection = new CtreeSqlConnection(conString);
   CtreeSqlCommand* hCommand = new CtreeSqlCommand(hConnection);
   DataSet* dset = new DataSet();
   CtreeSqlDataAdapter* adapter;
 
   hConnection->Open();
   hCommand->CommandText = “select name from tab1”;
   adapter = new CtreeSqlDataAdapter(hCommand);
   adapter->Fill(dset);
   hConnection->Close();
}

.NET Delphi Example

procedure DisplayNames
var
   conString : String;
   hConnection : CtreeSqlConnection;
   hCommand : CtreeSqlCommand;
   adapter : CtreeSqlDataAdapter;
   dset : DataSet

begin
   conString := 'User=ADMIN;Password=ADMIN;Database=ctreeSQL';
   hConnection := CtreeSqlConnection.Create(conString);
   hCommand := CtreeSqlCommand(hConnection);
   hConnection.Open();
   hCommand.CommandText := 'select * from tab1';
   dset := DataSet.Create();
   adapter := new CtreeSqlDataAdapter(hCommand);
   adapter.Fill(dset);
   hConnection.Close();
end;

Parameterized Commands

It is not unusual for an application to execute the same command on a database repeatedly, varying only the value or values used in the command. SQL database developers often use parameterized commands to code redundant commands, typically, commands whose input values come from user input. Parameterized commands may be the only way long data such as LVARCHAR and LVARBINARY can be queried, inserted or updated.

FairCom DB SQL supports both named and unnamed parameters, however, not in mixed usage. FairCom DB SQL accepts as parameter marks a question mark (?) or a name starting with a colon (:name). With unnamed parameters, the order in which parameters are used in the FairCom DB SQL command must be respected when setting parameter values and properties. With named parameters, you are free to use any order. The following are examples of valid parameters:

insert into tab2 values (?)

insert into tab2 values (:name)

The following examples show how to add data to a table using parameters.

.NET VB Example

Sub AddName()
   Dim conString As String = “User=ADMIN;Password=ADMIN;Database=ctreeSQL”
   Dim hConnection As New CtreeSqlConnection(conString)
   Dim hCommand As New CtreeSqlCommand(hConnection)
 
   hConnection.Open()
   hCommand.CommandText = “create table tab2 (name char(20), age integer)”
   hCommand.ExecuteNonQuery()
   hCommand.Parameters.Add(“name”, CtreeSqlType.Char)
   hCommand.Parameters.Add(“age”, CtreeSqlType.Integer)
   hCommand.CommandText = “insert into tab2 values (:name, :age)”
   hCommand.Parameters[“name”].Value = “Mary”
   hCommand.Parameters[“age”].Value = 1
   hCommand.ExecuteNonQuery()
   hConnection.Close()
End Sub

.NET C# Example

void CreateTable()
{
   String conString = “User=ADMIN;Password=ADMIN;Database=ctreeSQL”;
   CtreeSqlConnection hConnection = new CtreeSqlConnection(conString);
   CtreeSqlCommand hCommand = new CtreeSqlCommand(hConnection);
 
   hConnection.Open();
   hCommand.CommandText = “create table tab1 (name char(20), age integer)”;
   hCommand.ExecuteNonQuery();
   hCommand.Parameters.Add(“name”, CtreeSqlType.Char);
   hCommand.Parameters.Add(“age”, CtreeSqlType.Integer);
   hCommand.CommandText = “insert into tab2 values (:name, :age)”;
   hCommand.Parameters[“name”].Value = “Mary”;
   hCommand.Parameters[“age”].Value = 1;
   hCommand.ExecuteNonQuery();
   hConnection.Close();
}

.NET C++ Example

void CreateTable()
{
   String* conString = “User=ADMIN;Password=ADMIN;Database=ctreeSQL”;
   CtreeSqlConnection* hConnection = new CtreeSqlConnection(conString);
   CtreeSqlCommand* hCommand = new CtreeSqlCommand(hConnection);
 
   hConnection->Open();
   hCommand->CommandText = “create table tab1 (name char(20), age integer)”;
   hCommand->ExecuteNonQuery();
   hCommand->Parameters->Add(“name”, CtreeSqlType::Char);
   hCommand->Parameters->Add(“age”, CtreeSqlType::Integer);
   hCommand->CommandText = “insert into tab2 values (:name, :age)”;
   hCommand->Parameters[“name”]->Value = “Mary”;
   hCommand->Parameters[“age”]->Value = 1;
   hCommand->ExecuteNonQuery();
   hConnection->Close();

.NET Delphi Example

procedure AddName
var
   conString : String;
   hConnection : CtreeSqlConnection;
   hCommand : CtreeSqlCommand;
 
begin
   conString := 'User=ADMIN;Password=ADMIN;Database=ctreeSQL';
   hConnection := CtreeSqlConnection.Create(conString);
   hCommand := CtreeSqlCommand(hConnection);
   hConnection.Open();
   hCommand.CommandRText := 'create table tab2 (name char(20), age integer);
   hCommand.ExecuteNonQuery();
   hCommand.Parameters.Add('name', CtreeSqlType.Char);
   hCommand.Parameters.Add('age', CtreeSqlType.Integer);
   hCommand.CommandText := 'insert into tab2 values (:name, :age)';
   hCommand.Parameters['name'].Value := 'Mary';
   hCommand.Parameters['age'].Value := 1;
   hCommand.ExecuteNonQuery();
   hConnection.Close();   
end;

Developing ADO.NET Applications with FairCom DB SQL

Ctree.Data.SqlClient Namespace

Connecting to a FairCom DB SQL Database

Executing FairCom DB SQL Commands

Command Type

Reading Data

Reading Blob Data

Transactions and Isolation Levels

Populating a DataSet

Parameterized Commands

Was this article helpful?

Related Articles

FairCom DB API .NET Error and Return Values

FairCom DB Error Code Reference

Configuring FairCom DB SQL Data Sources