c-treeace sql ado.net data provider user's guide · pdf filethis tutorial will take you...

c-treeACE

ADO .NET Data Provider User's Guide

Copyright Notice

Copyright © 1992-2017 FairCom Corporation. All rights reserved. No part of this publication may be stored in a retrieval

system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording or otherwise without

the prior written permission of FairCom Corporation. Printed in the United States of America.

Information in this document is subject to change without notice.

Trademarks

c-treeACE, c-treeRTG, c-treeAMS, c-tree Plus, c-tree, r-tree, FairCom and FairCom’s circular disc logo are trademarks of

FairCom, registered in the United States and other countries.

The following are third-party trademarks: AMD and AMD Opteron are trademarks of Advanced Micro Devices, Inc.

Macintosh, Mac, Mac OS, and Xcode are trademarks of Apple Inc., registered in the U.S. and other countries.

Embarcadero, the Embarcadero Technologies logos and all other Embarcadero Technologies product or service names

are trademarks, service marks, and/or registered trademarks of Embarcadero Technologies, Inc. and are protected by the

laws of the United States and other countries. Business Objects and the Business Objects logo, BusinessObjects, Crystal

Reports, Crystal Decisions, Web Intelligence, Xcelsius, and other Business Objects products and services mentioned

herein as well as their respective logos are trademarks or registered trademarks of Business Objects Software Ltd.

Business Objects is an SAP company. HP and HP-UX are registered trademarks of the Hewlett-Packard Company. AIX,

IBM, POWER6, POWER7, and pSeries are trademarks or registered trademarks of International Business Machines

Corporation in the United States, other countries, or both. Intel, Intel Core, Itanium, Pentium and Xeon are trademarks or

registered trademarks of Intel Corporation or its subsidiaries in the United States and other countries. Microsoft, the .NET

logo, the Windows logo, Access, Excel, SQL Server, Visual Basic, Visual C++, Visual C#, Visual Studio, Windows,

Windows Server, and Windows Vista are either registered trademarks or trademarks of Microsoft Corporation in the

United States and/or other countries. Novell and SUSE are registered trademarks of Novell, Inc. in the United States and

other countries. Oracle and Java are registered trademarks of Oracle and/or its affiliates. QNX and Neutrino are

registered trademarks of QNX Software Systems Ltd. in certain jurisdictions. CentOS, Red Hat, and the Shadow Man logo

are registered trademarks of Red Hat, Inc. in the United States and other countries, used with permission. UNIX and

UnixWare are registered trademarks of The Open Group in the United States and other countries. Linux is a trademark of

Linus Torvalds in the United States, other countries, or both. Python and PyCon are trademarks or registered trademarks

of the Python Software Foundation. OpenServer is a trademark or registered trademark of Xinuos, Inc. in the U.S.A. and

other countries. Unicode and the Unicode Logo are registered trademarks of Unicode, Inc. in the United States and other

countries.

Btrieve is a registered trademark of Actian Corporation.

ACUCOBOL-GT, MICRO FOCUS, RM/COBOL, and Visual COBOL are trademarks or registered trademarks of Micro

Focus (IP) Limited or its subsidiaries in the United Kingdom, United States and other countries.

isCOBOL and Veryant are trademarks or registered trademarks of Veryant in the United States and other countries.

All other trademarks, trade names, company names, product names, and registered trademarks are the property of their

respective holders.

Portions Copyright © 1991-2016 Unicode, Inc. All rights reserved.

Portions Copyright © 1998-2016 The OpenSSL Project. All rights reserved. This product includes software developed by

the OpenSSL Project for use in the OpenSSL Toolkit (http://www.openssl.org/).

Portions Copyright © 1995-1998 Eric Young ([email protected]). All rights reserved. This product includes cryptographic

software written by Eric Young ([email protected]). This product includes software written by Tim Hudson

([email protected]).

Portions © 1987-2017 Dharma Systems, Inc. All rights reserved. This software or web site utilizes or contains material

that is © 1994-2007 DUNDAS DATA VISUALIZATION, INC. and its licensors, all rights reserved.

Portions Copyright © 1995-2013 Jean-loup Gailly and Mark Adler.

5/17/2018

All Rights Reserved iii www.faircom.com

Contents

1. Introduction ........................................................................................................ 1

2. QuickStart........................................................................................................... 2

2.1 Introductory Tutorial ............................................................................................. 3

Init 6

Define .................................................................................................................................. 7

Manage ............................................................................................................................... 8

Done .................................................................................................................................. 11

Additional Resources ........................................................................................................ 12

2.2 Relationships ..................................................................................................... 13

Init 15

Define ................................................................................................................................ 16

Manage ............................................................................................................................. 20

Done .................................................................................................................................. 25


2.3 Record/Row Locking .......................................................................................... 27

Init 29

Define ................................................................................................................................ 30

Manage ............................................................................................................................. 32

Done .................................................................................................................................. 35


2.4 Transaction Processing...................................................................................... 37

Init 39

Define ................................................................................................................................ 40

Manage ............................................................................................................................. 44

Done .................................................................................................................................. 49


3. c-treeACE SQL ADO.NET Data Provider ........................................................ 51

3.1 Features ............................................................................................................ 51

3.2 Architecture ........................................................................................................ 51

3.3 c-treeACE SQL ADO.NET Connection String .................................................... 52

3.4 ADO.NET support for TLS ................................................................................. 54

3.5 connectionidletimeout connection option for ADO.NET Provider ........................ 55

3.6 Data Type Mapping - C# Edition ........................................................................ 55

3.7 ADO.NET Entity Framework .............................................................................. 56

3.8 Entity Framework 6 Support ............................................................................... 56

Introduction

All Rights Reserved iv www.faircom.com

Visual Studio DDEX Provider for FairCom c-treeACE ...................................................... 58

4. Developing ADO.NET Applications with c-treeACE SQL .............................. 60

4.1 Ctree.Data.SqlClient Namespace ...................................................................... 60

4.2 Connecting to a c-treeACE SQL Database ........................................................ 60

4.3 Executing c-treeACE SQL Commands ............................................................... 62

4.4 Command Type ................................................................................................. 63

4.5 Reading Data ..................................................................................................... 63

4.6 Reading Blob Data ............................................................................................. 65

4.7 Transactions and Isolation Levels ...................................................................... 66

4.8 Populating a DataSet ......................................................................................... 67

4.9 Parameterized Commands ................................................................................ 69

5. Index ................................................................................................................. 72

FairCom Typographical Conventions

Before you begin using this guide, be sure to review the relevant terms and typographical

conventions used in the documentation.

The following formatted items identify special information.

Formatting convention Type of Information

Bold Used to emphasize a point or for variable expressions such as parameters

CAPITALS Names of keys on the keyboard. For example, SHIFT, CTRL, or ALT+F4

FairCom Terminology FairCom technology term

FunctionName() c-treeACE Function name

Parameter c-treeACE Function Parameter

Code Example Code example or Command line usage

utility c-treeACE executable or utility

filename c-treeACE file or path name

CONFIGURATION KEYWORD c-treeACE Configuration Keyword

CTREE_ERR c-treeACE Error Code

All Rights Reserved 1 www.faircom.com

1. Introduction

The .NET Framework is a computing platform greatly simplifying application development in the

highly distributed environment of the Internet. ADO.NET provides a rich set of components to

create distributed, disconnected and data-sharing applications. ADO.NET is an integral part of the

.NET Framework, providing relational data access to systems such as FairCom’s c-treeACE SQL.

A .NET Data Provider is a bridge used to connect ADO.NET applications to a database, execute

commands and retrieve results. The c-treeACE SQL .NET Data Provider gives you access to

your c-treeACE SQL data from a .NET application. The .NET Data Provider is lightweight,

creating a thin layer between the data source and your code, thus increasing performance without

sacrificing functionality. A .NET Data Provider consists of a set of classes implementing interfaces

specified in Microsoft's specification for .NET Data Providers.

The c-treeACE SQL ADO.NET Data Provider gives you access to your c-treeACE SQL data

through this easy to use interface. With integrated support for Visual Studio, your application

development could never be easier. Drop your components onto a Windows form and

immediately begin accessing the power of c-treeACE SQL with your data.


2. QuickStart

Like 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 c-treeACE 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.

QuickStart


2.1 Introductory Tutorial

..\sdk\sql.ado.net\tutorials\CSharpTutorial1.cs

If you are using c-treeRTG, adjust the path to match your version, e.g., V2.2.0.RTG, and replace

sdk with Driver.

This tutorial will take you through the basic use of the c-treeACE SQL ADO.NET Data Provider

using C#.

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 #1: Introductory - Simple Single Table

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

Initialize() - Connects to the c-treeACE 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 c-treeACE Database Engine.

The best way to following along is to use your debugger. If you are on a Linux/Unix platform, to

go to the directory where the "Makefile" is located and run the "make" or equivalent utility. If you

are on a Windows platform, 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.

The solution file can be found in the following directory (note: use your installed path in lieu of

'C:\faircom.vX.X.X'):

C:\FairCom\VX.X.X\win32\sdk\sql.ado.net\tutorials\IDEProjects\Microsoft Visual Studio

2012\CSharpTutorials.sln

C:\FairCom\VX.X.X\win32\sdk\sql.ado.net\tutorials\IDEProjects\Microsoft Visual Studio

2010\CSharpTutorials.sln

(If you are using c-treeRTG, adjust the path to match your version, e.g., V2.2.0.RTG, and replace

sdk with Driver.)

QuickStart


From within the Microsoft Visual Studio IDE, open the Solutions Explorer to see the four tutorial

projects. Be sure to set the 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 mainline:

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();

QuickStart


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.

QuickStart


Init

First we need to open a connection to a database by providing the c-treeACE 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)

{


}

}

QuickStart


Define

The define step is where specific data definitions are established by your application and/or

process. This involves defining columns/fields and creating the tables/files with optional indices.

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 (Exception e)

{


}

}

QuickStart


Manage

The manage step 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";


}


{


}

catch (Exception e)

{


}

}

//

// Add_Records()

QuickStart


//

// 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];


}

}


{


}

catch (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();

}

QuickStart



{


}

catch (Exception e)

{


}

}

QuickStart


Done

When an application and/or process has completed operations with the database, it must release

resources 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 (Exception e)

{


}

}

QuickStart


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 sdk\sql.ado.net\tutorials directory for your platform.

Example for the Windows platform:

C:\FairCom\V*\win32\sdk\sql.ado.net\tutorials\CSharpTutorial1.cs

(If you are using c-treeRTG, adjust the path to match your version, e.g., V2.2.0.RTG, and

replace sdk with Driver.)

Additional documentation may be found on the FairCom Web site at: www.faircom.com

QuickStart


2.2 Relationships



sdk with Driver.

Now we will build some table/file relationships using the c-treeACE SQL 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 indices for each table to form a

relational model database.



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:


Define() - Defines and creates the "custmast", "custordr", "ordritem" and the

"itemmast" tables/files with related indices.

Manage() - Adds some related rows/records to all tables/files. Then queries the

database.


using System;

using System.Collections;



QuickStart



{

class Tutorial2

{





//

// main()

//



//

static void Main()

{

Initialize();

Define();

Manage();

Done();


Console.ReadLine();

}



QuickStart


Init




//

// Initialize()

//


//


{


try

{




"UID=ADMIN;PWD=ADMIN;Database=CtreeSql;Server=localhost;Service=6597;";








conn.Open();

}


{


}

catch (Exception e)

{


}

}

QuickStart


Define




//

// Define()

//

// Create the tables

//


{


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

{










}


{


}

catch (Exception e)

{


}

QuickStart


try

{

cmd.CommandText = "CREATE UNIQUE INDEX cm_custnumb_idx ON custmast (cm_custnumb)";


}


{


}

catch (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))";


}


{


}

catch (Exception e)

{


}

try

{

cmd.CommandText = "CREATE UNIQUE INDEX co_ordrnumb_idx ON custordr (co_ordrnumb)";


cmd.CommandText = "CREATE INDEX co_custnumb_idx ON custordr (co_custnumb)";


}


{


}

catch (Exception e)

{


}

}

QuickStart


//

// 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))";


}


{


}

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.CommandText = "CREATE INDEX oi_itemnumb_idx ON ordritem (oi_itemnumb)";


}


{


}

catch (Exception e)

{


}

}

//

// Create_ItemMaster_Table()

//

// Create the table ItemMaster

//

static void Create_ItemMaster_Table()

{

Console.WriteLine("\ttable ItemMaster...");

try

{

QuickStart


cmd.CommandText = "CREATE TABLE itemmast (" +

"im_itemwght INTEGER," +

"im_itempric MONEY," +

"im_itemnumb CHAR(5)," +

"im_itemdesc VARCHAR(47))";


}


{


}

catch (Exception e)

{


}

try

{

cmd.CommandText = "CREATE INDEX im_itemnumb_idx ON itemmast (im_itemnumb)";


}


{


}

catch (Exception e)

{


}

}

QuickStart


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



while (rdr.Read())

{

Console.WriteLine("\t\t{0:-20s} \t{1}", rdr.GetString(0), rdr.GetString(1));

}

// close the reader

rdr.Close();

QuickStart


}


{


}

catch (Exception e)

{


}

}

//

// Add_CustomerMaster_Records()

//

// This function adds records to table CustomerMaster from an

// array of strings

//

static void Add_CustomerMaster_Records()

{

String[] data = {





};

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

Delete_Records("custmast");

try

{



{



}

}


{


}

catch (Exception e)

{


}

}

//

// Add_CustomerOrders_Records()

//

// This function adds records to table CustomerOrders from an

// array of strings

//

static void Add_CustomerOrders_Records()

{

QuickStart


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

{



{

cmd.CommandText = "INSERT INTO custordr VALUES " + data[i];


}

}


{


}

catch (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

{



{

cmd.CommandText = "INSERT INTO ordritem VALUES " + data[i];


}

}


{


QuickStart


}

catch (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

{



{

cmd.CommandText = "INSERT INTO itemmast VALUES " + data[i];


}

}


{


}

catch (Exception e)

{


}

}

//

// Delete_Records()

//

// This function deletes all the records in a tables

//

static void Delete_Records(String Table)

{


try

{

cmd.CommandText = "DELETE FROM " + Table;

QuickStart



}


{


}

catch (Exception e)

{


}

}

QuickStart


Done




//

// Done()

//



//

static void Done()

{


try

{

// logout


conn.Close();

}


{


}

catch (Exception e)

{


}

}

QuickStart











QuickStart


2.3 Record/Row Locking



sdk with Driver.

Now we will explore row/record locks using the c-treeACE SQL 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.

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 #3: Locking

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


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. Then demonstrates an update operation under locking

control, and a scenario that shows a locking conflict.



using System;



{

class Tutorial3

{





//

// main()

//



//

QuickStart


static void Main()

{

Initialize();

Define();

Manage();

Done();


Console.ReadLine();

}



QuickStart


Init





{


try

{











conn.Open();

}


{


}

catch (Exception e)

{


}

}

QuickStart


Define




//

// Define()

//

// Create the table for containing a list of existing customers

//


{


try

{

// create table

Console.WriteLine("\tCreate table...");










}


{


}

catch (Exception e)

{


}

try

{

cmd.CommandText = "CREATE UNIQUE INDEX cm_custnumb_idx ON custmast (cm_custnumb)";


}


{


}

catch (Exception e)

{


}

}

QuickStart


QuickStart


Manage




{


// delete any existing records

Delete_Records();

// populate the table with data


// 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()

{


try

{

cmd.CommandText = "DELETE FROM custmast";


}


{


}

catch (Exception e)

{


}

}

//

QuickStart



//


// array of strings

//


{

String[] data = {





};

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

try

{



{



}

}


{


}

catch (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



while (rdr.Read())

{

Console.WriteLine("\n\t\t{0} {1}", rdr.GetString(0), rdr.GetString(4));

}

// close the reader

rdr.Close();

QuickStart


}


{


}

catch (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'";


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

Console.ReadLine();

cmd.Transaction.Commit();

}


{


cmd.Transaction.Rollback();

}

catch (Exception e)

{


}

}

QuickStart


Done




//

// Done()

//



//

static void Done()

{


try

{

// logout


conn.Close();

}


{


}

catch (Exception e)

{


}

}

QuickStart











QuickStart


2.4 Transaction Processing



sdk with Driver.

Now we will discuss transaction processing as it relates to the c-treeACE SQL 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 insures that the integrity of the data in related tables/files is secure.



Tutorial #4: Transaction Processing

Here we demonstrate transaction control.


Define() - Defines and creates our four tables/files.

Manage() - Adds rows/records to multiple tables/files under transaction control.



using System;

using System.Collections;



{

class Tutorial4

{





//

// main()

//



//

static void Main()

{

Initialize();

Define();

QuickStart


Manage();

Done();


Console.ReadLine();

}



QuickStart


Init




//

// Initialize()

//


//


{


try

{












conn.Open();

}


{


}

catch (Exception e)

{


}

}

QuickStart


Define




//

// Define()

//

// Create the tables

//


{


// 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";


}


{


}

catch (Exception e)

{


}

try

{

cmd.CommandText = "DROP TABLE custordr";


QuickStart


}


{


}

catch (Exception e)

{


}

try

{

cmd.CommandText = "DROP TABLE custmast";


}


{


}

catch (Exception e)

{


}

try

{

cmd.CommandText = "DROP TABLE itemmast";


}


{


}

catch (Exception e)

{


}

}

//

// Create_CustomerMaster_Table()

//

// Create the table CustomerMaster

//

static void Create_CustomerMaster_Table()

{

Console.WriteLine("\ttable CustomerMaster...");

try

{


"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), " +



}


QuickStart


{


}

catch (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)";


}


{


}

catch (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)";


QuickStart


}


{


}

catch (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))";


}


{


}

catch (Exception e)

{


}

}

QuickStart


Manage



//

// Manage()

//

// Populates table and perform a simple query

//


{


// populate the tables with data


Add_ItemMaster_Records();

Add_Transactions();

// display the orders and their items

Display_CustomerOrders();

Display_OrderItems();

}


//


// array of strings

//


{

String[] data = {





};

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

try

{



{



}

}

QuickStart



{


}

catch (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

{



{

cmd.CommandText = "INSERT INTO itemmast VALUES " + data[i];


}

}


{


}

catch (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 = {

QuickStart


{ "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] + "')";


}


{


}

catch (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] + "')";


}


{


}

catch (Exception e)

{


}

// bump to next item

j++;

QuickStart


// exit the while loop on last item

if (j >= items.GetLength(0))

break;

}

try

{

cmd.Transaction.Commit();

}


{


cmd.Transaction.Rollback();

}

catch (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";


// fetch and display each individual record

while (rdr.Read())

{

Console.WriteLine("\t {0} {1}", rdr.GetString(2), rdr.GetString(3));

}

rdr.Close();

}


{


}

catch (Exception e)

{


}

}

//

// Display_OrderItems()

//

// This function displays the contents of OrderItems table.

QuickStart


//

static void Display_OrderItems()

{

Console.WriteLine("\n\tOrderItems Table...");

try

{

// execute a query statement

cmd.CommandText = "SELECT * FROM ordritem";


// fetch and display each individual record

while (rdr.Read())

{

Console.WriteLine("\t {0} {1}", rdr.GetString(2), rdr.GetString(3));

}

rdr.Close();

}


{


}

catch (Exception e)

{


}

}

QuickStart


Done




//

// Done()

//



//

static void Done()

{


Delete_Tables();

try

{

// logout


conn.Close();

}


{


}

catch (Exception e)

{


}

}

QuickStart












3. c-treeACE SQL ADO.NET Data Provider

This section provides details about using the c-treeACE SQL ADO.NET Data Provider. Here you

will find information on the required format of the connection string, supported data types and

brief examples to quickly connect and use c-treeACE SQL databases.

Support for Entity Framework 6.x (EF6) is implemented in a DLL named

Ctree.Data.EntityFramework.dll. See Entity Framework 6 Support (page 56).

Support for EF2 through EF4 is available as the Ctree.Data.SqlClient.Entity namespace

inside the ADO.NET provider DLL named Ctree.Data.SqlClient.dll. See ADO.NET Entity

Framework V2 - V4 Support (page 56).

3.1 Features

The following are key features of the c-treeACE SQL ADO.NET Data Provider:

Advanced design-time wizards for Microsoft Visual Studio providing complete Entity

Framework 6 support

Supports all current c-treeACE data types, including LVARCHAR and LVARBINARY

Supports stored procedure calls

High performance

Composed of pure managed code and is CLS-compliant

Supports Embarcadero CodeGear

All Exception() derived classes in the ADO.NET provider have the [SerializableAttribute] to make

them serializable in V11 and later.

3.2 Architecture

The c-treeACE ADO.NET Data Provider is referenced with the Ctree.Data.SqlClient.dll assembly.

There are core classes composing the c-treeACE ADO.NET Data Provider. The following table

describes these classes and their methods.

c-treeACE ADO.NET Data Provider Object

c-treeACE ADO.NET Data Provider Object Function

CtreeSqlConnection Establish a connection to c-treeACE database server and can begin transactions.

CtreeSqlCommand Execute SQL statements at c-treeACE database server and exposes SQL parameters.

c-treeACE SQL ADO.NET Data Provider


c-treeACE ADO.NET Data Provider Object

c-treeACE ADO.NET Data Provider Object Function

CtreeSqlDataReader Read a forward only stream of data from the c-treeACE database.

CtreeSqlDataAdapter Populate a DataSet and resolves updates with the c-treeACE database

CtreeSqlTransaction Allow transactions to be committed or aborted and enable the transaction isolation level used.

CtreeSqlParameter Define parameters for c-treeACE commands.

CtreeSqlParameterCollection

Represents a list of parameters relevant to CtreeSqlCommand as well as their respective mappings to columns in a DataSet.

CtreeSqlException Throws exceptions when an error is encountered while handling c-treeACE database operations and data.

3.3 c-treeACE SQL ADO.NET Connection String

The connection string to be passed into the CtreeSqlConnection Object is a set of semicolon

separated Name-Value pairs.

The following table lists Name-Value pairs used in connection strings for the c-treeACE SQL

ADO.NET Data Provider.

You can use any of the synonyms as a replacement for the Name portion of the pair to remain

cross-compatible with usage of other database products. Name is not case sensitive.

The minimum required are User ID and Password. If other values are not specified, default values

will be assigned as noted.

Name Default Value Description

User ID

Synonyms

• user id

• userid

• uid

• user

• user name

• username

None The user name for login.

Password

Synonyms

• password

• pwd

• user password

• userpassword

None The password for the user.

Initial Catalog

Synonyms

• database

• initial catalog

ctreeSQL The name of the c-treeACE SQL database.



Data Source

Synonyms

• data source

• datasource

• server

• host

localhost The TCP/IP address of the c-treeACE SQL installation.

Port Number

Synonyms

• port

• service

• port number

6597 The TCP/IP port number used by c-treeACE SQL.

Pooling true User client side connection pooling.

Max Pool Size

Synonyms

• max pool size

• maxpoolsize

100 Maximum number of pooled connections.

Connection Lifetime

Synonyms

• connection lifetime

• connectionlifetime

0

Connection Timeout

Synonyms

• timeout

• connection timeout

• connectiontimeout

15 Number of seconds to wait before the connection times out with no response from c-treeACE SQL.

Isolation Level

Synonyms

• isolation level

• isolationlevel

ReadCommitted The transaction isolation level for the connection.

Example

A typical connection string for the c-treeACE ADO.NET Data Provider would be composed of:

"User ID=ADMIN;Password=ADMIN;database=ctreeSQL;server=localhost;port=6597”

A minimal connection string can be:

"User=ADMIN;Password=ADMIN"

Timeout

The following connection string sets the timeout to never expire. In this example,

"Server=faircom2" is a bad server name:


"ConnectionTimeout=0;UID=ADMIN;PWD=ADMIN;Database=ctreeSQL;Datasource=db1.company.com;Service

=6597";

You might expect your ADO.NET application to wait indefinitely before it returns. However,

several errors are not affected by the timeout setting in this string, such as:

the server name is a bad name

the server is down



the server is unreachable

The actual error returned by the timeout setting in this string is generated only if the server can

establish the connection, but, it is very busy or the network is very slow to respond.

In the case above, with a bad server name, an error is returned in 5 seconds. (This value is not

configurable in the connect string.)

3.4 ADO.NET support for TLS

In V11.5 and later, the c-treeACE ADO.NET provider supports TLS/SSL connections per

Microsoft specifications.

The ADO.NET provider requires ctsrvr.pem to be added to the trusted root certificate store on the

client machine for the .NET framework's certificate authentication to succeed:

CertMgr.exe /add ctsrvr.pem /c /s /r localMachine root

Note that the Common Name specified in the server certificate is the name that the application

must specify in the ADO.NET connection string for the TLS option.

For this certificate, we used support.faircom.com as the Common Name, and so the ADO.NET

connection string must specify sslcert=support.faircom.com for the TLS 1.2 authentication to

succeed.

Connection String

The ADO.NET connection string is similar to the JDBC string. The connection string accepts a

new property:

ssl=<value>

which can have two values:

basic - Basic SSL setting, no peer certificate authentication, only communication encryption

as requested by server certificate

peerAuthentication - Server certificate authentication.

In the case of peerAuthentication the server certificate Common Name must be provided by the

new property:

sslcert=<value>

If this property is not specified, the value of the Server setting is used to match the certificate.

Examples: "UID=ADMIN;PWD=ADMIN;Database=CtreeSQL;Server=localhost;Service=6597;ssl=basic";

"UID=ADMIN;PWD=ADMIN;Database=CtreeSQL;Server=localhost;Service=6597;ssl=peerAuthentication;s

slcert=support.faircom.com";



3.5 connectionidletimeout connection option for ADO.NET

Provider

In V11.5 and later, a connectionidletimeout connection-string option is available to indicate

how long the connection may stay idle in the pool before getting closed and removed from the

pool. This option is separate from the existing connection timeout option, which has a different

meaning in ADO connection pool.

3.6 Data Type Mapping - C# Edition

The following table lists the mapping between c-treeACE SQL data types and the .NET

Framework types. It also lists DbTypes and .NET Framework typed accessors:

c-treeACE SQL Data Type

DbType .NET Framework Type

.NET Framework Typed Accessor

Bigint Int64 Int64 GetInt64()

Binary Binary Byte[] GetBytes()

Bit Boolean Boolean GetBoolean()

Character AnsiString FixedLength

String GetString()

GetBytes()

Date DateTime1 DateTime1 GetDateTime()

Float Double Double GetDouble()

Integer Int32 Int32 GetInt32()

LVarChar Byte[] Byte[] GetBytes()

LVarBinary Byte[] Byte[] GetBytes()

Money Decimal Decimal GetDecimal()

Numeric Decimal Decimal GetDecimal()

Real Single Single GetSingle()

SmallInt Int16 Int16 GetInt16()

Time DateTime2 DateTime2 GetDateTime()

TimeStamp DateTime2 DateTime2 GetDateTime()

TinyInt SByte SByte GetInt16()

Varchar String String GetString()

GetBytes()

VarBinary Byte[] Byte[] GetBytes()

1The time portion of DateTime is set to hour = 0, minute = 0, second = 0 and milliseconds = 0.

2The date portion of DateTime is set to year = 1, month = 1 and day = 1.



3.7 ADO.NET Entity Framework

The c-treeACE SQL ADO.NET Data Provider has support for Entity Framework up through V7.

System Requirements

The minimum development system requirements for c-treeACE SQL ADO.NET Entity Framework

support are listed below. Note that they require the complete version (e.g., the complete version,

not just the "client" version):

Visual Studio 2008 Service Pack 1 or greater

c-treeACE V11.5 requires Microsoft .NET 4.0 Framework.



c-treeACE V10 requires at least Framework Version 3.5 SP1

Auto Incrementing Field Type Restriction

Entity Framework Models allow Int16, Int32 or Int64 field types to be specified as Auto

Incrementing in model design. Auto Incrementing fields are also known as Identity fields in some

schemas.

c-treeACE SQL allows one user Auto Incrementing field type. Note that c-treeACE already

supported a serial segment field, currently used by default as the ROWID value. As there is a

limitation of one SRLSEG field type per data file (table), this precluded the addition of a

user-defined field. An IDENTITY attribute is now available for this purpose.

Other Known Limitations

The following are other known c-treeACE SQL limitations that can be encountered when using

Entity Framework support. These are in various stages of development. Contact your nearest

FairCom office for the latest information concerning specific support for these items.

The SKIP operator is not currently supported. The SKIP operator is commonly used with the

TOP operator for “paging” purposes.

The EXCEPT operator is not currently supported.

Parameters are not currently supported in functions and in the TOP operator.

BIT (Boolean) columns can currently only be tested against 1 or 0 (that is, if ( bitColumn ==

1 ). Entity Framework requires a test against true/false (for example, if ( bitColumn == true )

or more simply if ( bitColumn )

3.8 Entity Framework 6 Support

Support for Entity Framework 6.x (EF6) is implemented in a separate DLL named

Ctree.Data.EntityFramework.dll.

Support for previous versions, up to EF4, is available as the Ctree.Data.SqlClient.Entity

namespace inside the ADO.NET provider DLL (Ctree.Data.SqlClient.dll). See ADO.NET Entity

Framework V2 - V4 Support (page 56).



To test or use the EF6 support, the following steps must be taken:

Note: This support has been tested with Visual Studio 2012 and 2013.

Be sure your c-treeACE package is installed. If you used the .zip installer, be sure you have

executed \FairCom\v*\win*\Setup\FairComConfig.exe to register the ADO.NET driver.

1. Start Visual Studio 2012 (or newer).

2. Create a new Console Application project.

3. Right-click the project node and select Manage NuGet Packages.

4. From the packages list, select Entity Framework. This step is required to install the latest EF6 package in Visual Studio 2012 (Visual Studio 2013 should already have this package built-in).

5. Open the app.config file and replace the following line:

<provider invariantName="System.Data.SqlClient"

type="System.Data.Entity.SqlServer.SqlProviderServices,

EntityFramework.SqlServer" />

with:

<provider invariantName="Ctree.Data.SqlClient"

type="Ctree.Data.EntityFramework.CtreeSqlProviderServices,

Ctree.Data.EntityFramework" />

6. In Solution Explorer add a reference to the Ctree.Data.EntityFramework.dll located by default in the \FairCom\V10.4.0\win*\bin\sql.ado.net directory (you may also remove the

reference to EntityFramework.SqlServer).

7. Click Build > Rebuild Solution.

8. Right-click the project node and select Add > New Item.

9. From the Data category select ADO.NET Entity Data Model.

10. Select Generate from Database.

11. If no previous connection is present, click New Connection.

12. The FairCom c-treeACE SQL datasource should appear. If it does not:

a. Click Change.

b. Select FairCom c-treeACE SQL from the list of available datasources.

Note: If the FairCom c-treeACE SQL datasource is not listed among the available datasources, you did not properly register the datasource for the version of Visual Studio you are using. Please refer to ADO.NET installation documentation for information on how to register the datasource. For a quick registration in Visual Studio 2012, locate the FairComDDEXProvider_v11_Config.reg file in the win*\bin\sql.ado.net directory, double-click it, and click OK on all confirmation dialogs.

13. Check the values in the Connection Properties dialog and adjust them if needed (you can click Advanced for additional connection string options).

14. Click Test Connection to verify that the provider can successfully connect to the c-treeACE Server.

15. Click Next and you should be able to select one or more tables from the list of available ones and click Finish.

Note: Remember that, to properly work in the EF6 environment, the tables should have a "primary key" defined.

If everything works as expected, a model (.edmx file) will be generated.



Visual Studio DDEX Provider for FairCom c-treeACE

Note: This topic explains how the FairCom installer integrates the ADO provider inside Visual Studio. You should not need to perform these procedures by hand. If you have problems, you can refer to this topic to check that things are properly set up.

The DDEX Provider for FairCom ACE provides integration of FairCom .NET Data Adapter into

Visual Studio.

Before you can use it, the following steps must be completed:

1. The FairCom .NET Data Adapter DLL (Ctree.Data.SqlClient.dll) must be put in the GAC (simply copy it to the C:\Windows\Assembly folder or use the Microsoft .NET Configuration tool).

2. The DLL in Step 1 must be registered in the machine.config file. The file should be located in the following directory:

C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\CONFIG

To register the provider, add the following to the machine.config file:

<configuration>

<configSections>

...

<section name="ctree.data.sqlclient"

type="System.Data.Common.DbProviderConfigurationHandler, System.Data,

Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />

...

<configSections>

<system.data>

<DbProviderFactories>

...

<add name="c-treeACE SQL Data Provider"

invariant="Ctree.Data.SqlClient" description=".Net Framework Data

Provider for FairCom c-treeACE SQL"

type="Ctree.Data.SqlClient.CtreeSqlClientFactory,

Ctree.Data.SqlClient, Version=10.5.0.0, Culture=neutral,

PublicKeyToken=0ce73727dc1039a8" />

...

</DbProviderFactories>

</system.data>

....

</configuration>

3. The information of the FairComDDEXProvider_v11.reg file should be added to the Windows registry.

If needed, the FairComDDEXProvider.reg file must be modified to set the correct CodeBase for

the DDEX provider files (replace the current DLL path in the file with the correct path).

Attention: The path separator must be a double backslash ( \\ )


4. Developing ADO.NET Applications with

c-treeACE SQL

Creating applications with c-treeACE databases is easy with the c-treeACE 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 c-treeACE 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.

4.1 Ctree.Data.SqlClient Namespace

When developing applications with the c-treeACE 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;


.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;

4.2 Connecting to a c-treeACE SQL Database

A CtreeSqlConnection object represents a unique session to a c-treeACE 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

Developing ADO.NET Applications with c-treeACE SQL


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 c-treeACE SQL database using the

c-treeACE 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();


hConnection.Close();

}

.NET C++ Example void Connect()

{

String* conString = S“User=ADMIN;Password=ADMIN;Database=ctreeSQL”;

CtreeSqlConnection* hConnection = new CtreeSqlConnection(conString);

hConnection->Open();


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();



end;



4.3 Executing c-treeACE SQL Commands

After establishing a connection to a c-treeACE 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 c-treeACE 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 c-treeACE 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 c-treeACE 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 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()

{



CtreeSqlCommand hCommand = new CtreeSqlCommand(hConnection);

hConnection.Open();

hCommand.CommandText = “create table tab1 (name (char(20), age integer)”;

hCommand.ExecuteNonQuery();


}



.NET C++ Example void CreateTable()

{



CtreeSQLCommand* hCommand = new CtreeSQLCommand(hConnection);


hCommand->CommandText = “create table tab1 (name (char(20), age integer)”;

hCommand->ExecuteNonQuery();


}

.NET Delphi Example procedure CreateTable

var

conString : String;


hCommand : CtreeSqlCommand;

begin



hCommand := CtreeSqlCommand(hConnection);

hConnection.Open();

hCommand.CommandRText := 'create table tab1 (name (char(20), age integer)';



end;

4.4 Command Type

CtreeSqlCommand.CommandType property indicates how the c-treeACE 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 c-treeACE SQL command.

CommandType.StoredProcedure is used to call a c-treeACE 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.

4.5 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 c-treeACE 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

c-treeACE 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 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()

{




CtreeSqlDataReader reader;

hConnection.Open();

hCommand.CommandText = “select name from tab1”;

reader = hCommand.ExecuteReader();

while (reader.Read())

Console.WriteLine(reader[“name”]);

reader.Close();


}


{



CtreeSqlCommand* hCommand = new CtreeSqlCommand(hConnection);

CtreeSqlDataReader* reader;


hCommand->CommandText = “select name from tab1”;



reader = hCommand->ExecuteReader();

while (reader->Read())

Console::WriteLine(reader[“name”]);

reader->Close();


}

.NET Delphi Example procedure DisplayNames

var

conString : String;



reader : CtreeSqlDataReader;

begin




hConnection.Open();

hCommand.CommandText := 'select name from tab1';

reader := hCommand.ExecuteReader();

while reader.Read() do

Console.WriteLine(reader['name']);

reader.Close();


end;

4.6 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 c-treeACE 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 c-treeACE SQL database.



4.7 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 hTransaction As CtreeSqlTransaction

hConnection.Open()

hTransaction = hConnection.BeginTransaction(IsolationLevel.ReadCommitted)

hCommand.Transaction = hTransaction



hTransaction.Commit()

hConnection.Close()

End Sub


{




CtreeSqlTransaction hTransaction;

hConnection.Open();

hTransaction = hConnection.BeginTransaction(IsolationLevel.ReadCommitted);

hCommand.Transaction = hTransaction;

hCommand.CommandText = “create table tab1 (name (char(20), age integer)”;


hTransaction.Commit();


}


{




CtreeSqlTransaction* hTransaction;


hTransaction = hConnection->BeginTransaction(IsolationLevel::ReadCommitted);

hCommand->Transaction = hTransaction;

hCommand->CommandText = “create table tab1 (name (char(20), age integer)”;


hTransaction->Commit();


}



.NET Delphi Example procedure CreateTable

var

conString : String;



hTransaction : CtreeSqlTransaction;

begin




hCommand.Transaction = hTransaction;

hConnection.Open();

hTransaction = hConnection.BeginTransaction(IsolationLevel.ReadCommitted);

hCommand.CommandRText := 'create table tab1 (name (char(20), age integer)';


hTransaction.Commit();


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 c-treeACE 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 c-treeACE SQL)

4.8 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 c-treeACE SQL

database.

.NET VB Example procedure DisplayNames

var

conString : String;



reader : CtreeSqlDataReader;

begin




hConnection.Open();

hCommand.CommandText := 'select name from tab1';

reader := hCommand.ExecuteReader();

while reader.Read() do

Console.WriteLine(reader['name']);

reader.Close();


end;

Sub DisplayNames()




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()

{




DataSet dset = new DataSet();

CtreeSqlDataAdapter adapter;

hConnection.Open();

hCommand.CommandText = “select name from tab1”;



adapter = new CtreeSqlDataAdapter(hCommand);

adapter.Fill(dset);


}


{




DataSet* dset = new DataSet();

CtreeSqlDataAdapter* adapter;


hCommand->CommandText = “select name from tab1”;

adapter = new CtreeSqlDataAdapter(hCommand);

adapter->Fill(dset);


}

.NET Delphi Example procedure DisplayNames

var

conString : String;



adapter : CtreeSqlDataAdapter;

dset : DataSet

begin




hConnection.Open();

hCommand.CommandText := 'select * from tab1';

dset := DataSet.Create();

adapter := new CtreeSqlDataAdapter(hCommand);

adapter.Fill(dset);


end;

4.9 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.

c-treeACE SQL supports both named and unnamed parameters, however, not in mixed usage.

c-treeACE 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 c-treeACE

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 (?)



or

insert into tab2 values (:name)

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

.NET VB Example Sub AddName()




hConnection.Open()



hCommand.Parameters.Add(“name”, CtreeSqlType.Char)

hCommand.Parameters.Add(“age”, CtreeSqlType.Integer)

hCommand.CommandText = “insert into tab2 values (?, ?)”

hCommand.Parameters[“name”].Value = “Mary”

hCommand.Parameters[“age”].Value = 1


hConnection.Close()

End Sub


{




hConnection.Open();

hCommand.CommandText = “create table tab1 (name char(20), age integer)”;


hCommand.Parameters.Add(“name”, CtreeSqlType.Char);

hCommand.Parameters.Add(“age”, CtreeSqlType.Integer);

hCommand.CommandText = “insert into tab2 values (?, ?)”;

hCommand.Parameters[“name”].Value = “Mary”;

hCommand.Parameters[“age”].Value = 1;



}


{

String* conString = “User=ADMIN;Password=ADMIN;Database=ctreeSQL”;




hCommand->CommandText = “create table tab1 (name char(20), age integer)”;


hCommand->Parameters->Add(“name”, CtreeSqlType::Char);

hCommand->Parameters->Add(“age”, CtreeSqlType::Integer);

hCommand->CommandText = “insert into tab2 values (?, ?)”;

hCommand->Parameters[“name”]->Value = “Mary”;

hCommand->Parameters[“age”]->Value = 1;





.NET Delphi Example procedure AddName

var

conString : String;



begin




hConnection.Open();

hCommand.CommandRText := 'create table tab2 (name char(20), age integer);


hCommand.Parameters.Add('name', CtreeSqlType.Char);

hCommand.Parameters.Add('age', CtreeSqlType.Integer);

hCommand.CommandText := 'insert into tab2 values (?, ?)';

hCommand.Parameters['name'].Value := 'Mary';

hCommand.Parameters['age'].Value := 1;



end;


5. Index

A Additional Resources ............................12, 26, 36, 50 ADO.NET Entity Framework ............................. 51, 56 ADO.NET support for TLS ...................................... 54 Architecture ............................................................. 51

C classes .................................................................... 51 Command Type ...................................................... 63 Connecting to a c-treeACE SQL Database ............ 60 connection string ..................................................... 52 connectionidletimeout connection option for

ADO.NET Provider ............................................. 55 Copyright Notice ....................................................... ii Ctree.Data.EntityFramework.dll ........................ 51, 56 Ctree.Data.SqlClient Namespace ........................... 60 Ctree.Data.SqlClient.dll .................................... 51, 56 Ctree.Data.SqlClient.Entity ..................................... 51 c-treeACE SQL ADO.NET Connection String ........ 52 c-treeACE SQL ADO.NET Data Provider ............... 51

D Data Type Mapping - C# Edition ............................ 55 DDEX Provider ....................................................... 58 Define ..................................................... 7, 16, 30, 40 Developing ADO.NET Applications with

c-treeACE SQL ................................................... 60 DLL ................................................................... 51, 56 Done .....................................................11, 25, 35, 49

E EF2 - EF4 ............................................................... 56 EF6 ......................................................................... 56 Entity Framework 6 Support ............................. 51, 56 Executing c-treeACE SQL Commands................... 62

F FairCom Typographical Conventions ....................... v Features .................................................................. 51

I Init ........................................................... 6, 15, 29, 39 Introduction ............................................................... 1 Introductory Tutorial .................................................. 3

M machine.config ........................................................ 58 Manage ................................................... 8, 20, 32, 44

P Parameterized Commands ..................................... 69 Populating a DataSet .............................................. 67 primary key ............................................................. 56

Q QuickStart .................................................................2

R Reading Blob Data ................................................. 65 Reading Data ......................................................... 63 Record/Row Locking .............................................. 27 Relationships ......................................................... 13

T Transaction Processing ......................................... 37 Transactions and Isolation Levels ......................... 66 Tutorials ....................................................................2

Introductory Tutorial ..............................................3 Relational Model and Indexing .......................... 13 Row/Record Locking .......................................... 27 Transaction Processing ..................................... 37

V Visual Studio DDEX Provider for FairCom

c-treeACE........................................................... 58

c-treeace sql ado.net data provider user's guide · pdf filethis tutorial will take you...

Documents