FairCom ADO.NET Driver - Developer's Guide

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:

1. Instantiate and initialize a new CtreeSqlConnection object.
2. Instantiate a new CtreeSqlCommand object and set its Connection property.
3. Set the CtreeSqlCommand CommandText property with the the FairCom DB SQL command to be executed.
4. Optionally, call the Prepare() method.
5. 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;