Development guide with OleDBProNet

3. Use CSession for different jobs.
Manual transaction
Execute a simple SQL statement
Open a command object
Open a table with a given table name (and a given index name) directly
Open a schema rowset with a given schema Guid

4.   What can CCommand do for you?
      Open a rowset from a simple given SQL statement or store procedure
      Open a stream with a given XML query
      Update a data source with a parameterized stored procedure or statement by sending multiple sets of parameter data in one single call
      Open a rowset or XML stream with a parameterized stored procedure or statement by sending one set of parameter data

5.   How many features can a CRowset have?
      Get data from a rowset
      Update database through an updateable cursor
      Navigate records fast and efficiently in many ways
      Create DataTable from a rowset
      Open enumerator rowset
      Use rowset activity notifications

6.   Open data source and rowset asynchronously.
      Build connection to a database asynchronously
      Open a rowset asynchronously

7. Bind a cursor with data grid view control

8. Write a high performance application.

Open a rowset with properties just wanted, no more and no less

Find a way to add, update or delete records in batch

Properly use manual transaction instead of automatic transaction

1. Introduction.

With the advent of the .NET environment, Microsoft introduces a very new data accessing model, ADO.NET, to replace the previous COM ADO/OLEDB model. ADO.NET is designed to focus the needs of web applications with a completely disconnected model, which requires all of tables data are loaded into an "in-memory" database (a DataSet) before accessing these data. This model works well for web applications, but it does have a number of short comings that make it awkward to use for traditional desktop applications and middle tier components in many cases. In comparison to classical COM ADO/OLEDB technology, ADO.NET has following main short comings listed below:

a). It is very slow to access a large data table. ADO.NET can manage a small number of data records in disconnected model efficiently. However, it works very slow if the number of data records becomes large because ADO.NET requires loading these records into a DataSet object first and setting relationships among tables. The loading time becomes larger and larger when the number of records becomes larger and larger. It is this reason that leads to a large memory footprint in many cases.

b). ADO.NET supports sequential forward and read-only server cursor only. It works fine with web applications, but it becomes very awkward with developments for classical desktop and middle tier applications because these applications typically require data accessing mode is able to provide scrollable and updateable server cursors with bookmarks at least. Currently, ADO.NET is not able to provide these features. ADO.NET lacks many fundamental features that the previous COM ADO/OLEDB model provides. This is a huge problem especially for desktop application development.

c). Creating and managing ADO.NET classes is more complex than COM ADO/OLEDB model. With the classical COM ADO/OLEDB, you will not be required to set relationships and schema data among tables. Also, classical COM ADO/OLEDB model has fewer objects to manage, and each of these objects has much less properties and methods that you need to understand, which leads to simple development.

Therefore, we have created the module named as OleDBProNet for .Net development with direct use of raw OLEDB interfaces using the latest Visual C++ 2005/CLI in order to eliminating the above ADO.NET shortcomings. With OleDBProNet, you will use the previous COM ADO/OLEDB model to access all of your data sources with very little data marshaling between native code and managed code with two layers less, which leads to much better performance. OleDBProNet provides following advantages:

a). Super simple. OleDBProNet hides all of raw OLEDB interface complexity. It is as simple as classical ADO. You will never meet any problems in managing various complicate OLEDB data types, multiple BLOBs, and texts for all of SQL statements. This is absolutely guaranteed.

b). Lots of features. OleDBProNet has much more features than ADO.NET as shown in attached samples. It also has more features than the classical ADO. It offers much more fundamental features than any .Net data providers can provide today or in the future.

c). Super performance. OleDBProNet is written with extensive use of batching model. By default, you fetch records in batch, and update records in batch also. OleDBProNet is usually about 15% faster than MS OleDB .Net provider. As expected, OleDBProNet is much faster than any ADO.NET providers in managing large rowsets or record sets.

d). One copy of data for all of data sources. It is very simple for you to write one copy of code for accessing all of data sources because OLEDB specification has more requirements for inter-exchange among different data sources.

To ease your development, OleDBProNet comes with fifteen real and advanced samples to cover all of OLEDB relational database programming aspects. This guide is written for helping you develop .NET applications quickly and smoothly using OleDBProNet.

2. Work with CDataSource.

The class CDataSource corresponds to OLEDB COM object TDataSource. You can use an instance of the class to open a data source with one of the following methods. The most important methods for the class CDataSource are various versions of open methods as described below.

a). OLEDB connection string

void Open(String ^strConnection);

void Open(String ^strConnection, bool bEnableCCE, bool bEnablePooling);

Here is a sample to open a datasource (Oracle) with a given OLEDB connection string through MS MDAC core services:

CDataSource DataSource = new CDataSource();

DataSource.Open("Provider=OraOLEDB.Oracle;Data Source=Ora102;User ID=scott;Password=tiger");

b). MS datalink dialog

void OpenWithDataLinks();

void OpenWithDataLinks(enumDataLinkDialogOption DataLinkDialogOption);

For example, see the below code for opening a data source using OLEDB data link dialog:

CDataSource DataSource = new CDataSource();

//open a data source through MS datalink dialog.

DataSource.OpenWithDataLinks();

c). Class ID or ProgID

void OpenDirectly(Guid clsidProvider, enumDBPromptOption DBPromptOption);
void OpenDirectly(String ^strProgID, enumDBPromptOption DBPromptOption);
void OpenDirectly(Guid clsidProvider);
void OpenDirectly(String ^strProgID);

To directly open a data source object without use MS MDAC core services, you can use the below code:

CDataSource DataSource = new CDataSource();

DataSource.SetServer("localhost\\SQLExpress");

DataSource.SetDatabase("Northwind");

DataSource.SetIntegrated("SSPI");

DataSource.OpenDirectly(CommonProviderClassID.SQLNCLI);

If you use the method to open a data source, you can’t use MS client cursor engine or OLEDB pooling services from MS OLEDB core services because MS MDAC core services are not integrated with the opened data source.

d). An existing interface

void OpenFrom(IntPtr pIUnknownToDataSource);

If you have an interface to an OLEDB TDataSource object, you can open a CDataSource through this method.

Once having opened a CDataSource object, you can query all of sets of supported properties through the property AllPropSets, including a provider specific properties. You can also see the detailed information about each of these properties by looking at the property AllPropSetInfos. To set a property, you can use the method SetProperty. To know details about those standard properties, see the Section Appendix C: OLE DB Properties inside MS OLEDB documentation. To know details about those provider-specific properties.

Of course, you can call its method Close to explicitly close a data source when it is no longer needed.

3. Use CSession for different jobs!

You can use an instance of CSession to complete five different tasks:

a). Manual transaction.

See the below sample code inside the sample project MakeTable:

try

{

//start a manual transaction

Session.BeginTrans();

Command.ExecuteSQL("Create Table Dept(DeptNo short not null constraint Dept_PK PRIMARY KEY, Name varchar(50))");

Command.ExecuteSQL("CREATE TABLE EmpInfo (EmpID Integer NOT NULL CONSTRAINT EmpInfo_PK PRIMARY KEY, DeptNo SHORT NOT NULL CONSTRAINT DEPT_FK REFERENCES DEPT(DeptNo), LastName VARCHAR(25) NOT NULL, HireDate DATETIME, Photo LONGBINARY, Notes Memo)");

Command.ExecuteSQL("CREATE INDEX LastName_Index ON EmpInfo(LastName)");

Command.ExecuteSQL("Insert into Dept values(1, 'Software Development')");

Command.ExecuteSQL("Insert into Dept values(2, 'Software Test')");

Command.ExecuteSQL("Insert into Dept values(3, 'Sales Department')");

//commit transaction

Session.Commit();

}

catch (COleDBProException tErr)

{

Session.Rollback();

Console.WriteLine(tErr.Message);

}

Also, you can use its method EnlistDistributedTransaction to join a distributed transaction.

b). Execute a simple SQL statement.

For example, see the below code:

try

{

//start a manual transaction

Session.BeginTrans();

Session.ExecuteSQL("Create Table Dept(DeptNo short not null constraint Dept_PK PRIMARY KEY, Name varchar(50))");

Session.ExecuteSQL("CREATE TABLE EmpInfo (EmpID Integer NOT NULL CONSTRAINT EmpInfo_PK PRIMARY KEY, DeptNo SHORT NOT NULL CONSTRAINT DEPT_FK REFERENCES DEPT(DeptNo), LastName VARCHAR(25) NOT NULL, HireDate DATETIME, Photo LONGBINARY, Notes Memo)");

Session.ExecuteSQL("CREATE INDEX LastName_Index ON EmpInfo(LastName)");

Session.ExecuteSQL("Insert into Dept values(1, 'Software Development')");

Session.ExecuteSQL("Insert into Dept values(2, 'Software Test')");

Session.ExecuteSQL("Insert into Dept values(3, 'Sales Department')");

//commit transaction

Session.Commit();

}

catch (COleDBProException tErr)

{

Session.Rollback();

Console.WriteLine(tErr.Message);

}

c). Open a command object.

See the below sample code.

CCommand Command = Session.OpenCommand();

d). Open a table with a given table name (and a given index name) directly.

See the following code.

CRowset Rowset = Session.OpenTable("EmpInfo", "LastName_Index", enumCursorType.ctStatic, true, false, true, true, PropSet);

e). Open a schema rowset with a given schema Guid.

Following sample code demonstrates how to retrieve schema data about table Shippers, which is except from the sample project Schema.

object[] obRestrictions = new object[4];

obRestrictions[2] = "Shippers";

Rowset = Session.OpenSchema(SchemaGuid.DBSCHEMA_COLUMNS, obRestrictions);

4. What can CCommand do for you?

The class CCommand echoes OLEDB object TCommand, which exposes a set of interfaces for different tasks. You can use an instance of CCommand to execute a simple SQL statement or a stored procedure, and open a rowset or XML stream with a given SQL statement. In addition, you can use a CCommand with a parameterized stored procedure or SQL statement to access a data source in multiple sets of parameter data by a single call. For many OLEDB providers, it is the fastest way to update a database. For example, you can use this way to load, update or delete bulk records into an Oracle database. Let’s see what CCommand is able to do for you.

a). Open a rowset from a simple given SQL statement or store procedure.

See the following code.

//open a command

Command = Session.OpenCommand();

//open a rowset through a command object

Rowset = Command.OpenRowset("Select * from Employees");

b). Open a stream with a given XML query.

See the sample code sniffet.

CCommand Command = Session.OpenCommand();

string strXMLQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'> <sql:query>SELECT PRODUCTID, PRODUCTNAME FROM PRODUCTS WHERE PRODUCTNAME LIKE 'C%' FOR XML AUTO </sql:query> </ROOT>";

CStream Stream = Command.OpenStream(strXMLQuery);

string strXML = Stream.ReadStringA(2000); //read ASCII XML

c). Update a data source in batch by sending multiple sets of parameter data in one single call with a parameterized stored procedure or statement.

See the code below from the sample project BatchParam:

CCommand Command = new CCommand();

CParamInfo[] aParamInfo = new CParamInfo[2];

aParamInfo[0] = new CParamInfo();

aParamInfo[1] = new CParamInfo();

aParamInfo[0].m_nDBType = enumDataType.dtInt16;

aParamInfo[1].m_nDBType = enumDataType.dtStringW;

//for ASCII string, UNICODE string and Bytes, you'd better set a max buffer size in bytes

aParamInfo[1].m_nLen = (50 + 1) * 2; //in bytes plus a null-terminated wchar

string strInsert = "Insert into Dept values(?, ?)";

Command.OpenWithParameters(Session.IOpenRowset, strInsert, aParamInfo);

object[] aData = new object[6];

aData[0] = (short)1;

aData[1] = "Sales Dept";

aData[2] = (short)2;

//key eyes on the difference between null and ""

aData[3] = "";

aData[4] = (short)3;

aData[5] = null;

//Insert 3 sets of data in one call

Command.DoBatch(aData);

d). Open a rowset or XML stream with a parameterized stored procedure or statement by sending one set of parameter data with a parameterized stored procedure or statement by sending one set of parameter data.

Here are two sets of sample codes. One set is for opening a rowset from the sample project MultiRowsets, and the other for a XML stream from XMLQuery.

Open a rowset from a set of parameter data with a parameterized stored procedure.

CDataSource DataSource = new CDataSource();

CSession Session = null;

CCommand Command = new CCommand();

CRowset Rowset;

DataSource.SetServer("localhost\\SQLExpress");

DataSource.SetDatabase("Northwind");

DataSource.SetIntegrated("SSPI");

DataSource.OpenDirectly(CommonProviderClassID.SQLNCLI);

Session = DataSource.OpenSession();

try

{

Session.ExecuteSQL("Drop Procedure OrderInfoEx");

}

catch (COleDBProException err)

Contents