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)

{

Console.WriteLine(err.Message + ", error code = " + err.HResult);

}

string strCreateProcedure = "create Procedure OrderInfoEx @dtOrderDate datetime, @strCustomerID nchar(5), @strRegion nvarchar(15), @nSumEmployeeID int out, @strInfo nchar(255) out " +

"as " +

"select * from Orders where ShipRegion <> @strRegion and OrderDate <> @dtOrderDate and CustomerID<>@strCustomerID and EmployeeID<@nSumEmployeeID " +

"select @nSumEmployeeID=sum(EmployeeID) from Orders " +

"select @strInfo='This is a test from a procedure ' + @strCustomerID";

Session.ExecuteSQL(strCreateProcedure);

CParamInfo[] aParamInfo = new CParamInfo[5];

aParamInfo[0] = new CParamInfo();

aParamInfo[1] = new CParamInfo();

aParamInfo[2] = new CParamInfo();

aParamInfo[3] = new CParamInfo();

aParamInfo[4] = new CParamInfo();

aParamInfo[0].m_nDBType = enumDataType.dtDateTime;

aParamInfo[1].m_nDBType = enumDataType.dtStringW;

aParamInfo[1].m_nLen = (5 + 1) * 2; //in bytes !!!!

aParamInfo[2].m_nDBType = enumDataType.dtStringW;

aParamInfo[2].m_nLen = (15 + 1) * 2;

aParamInfo[3].m_nDBType = enumDataType.dtInt32;

aParamInfo[3].m_nDBParamIO = enumParamIO.InputOutput;

aParamInfo[4].m_nDBType = enumDataType.dtStringW;

aParamInfo[4].m_nLen = (1024 + 1) * 2;

aParamInfo[4].m_nDBParamIO = enumParamIO.Output;

Command.OpenWithParameters(Session.IOpenRowset, "{CALL OrderInfoEx(?, ?, ?, ?, ?)};Select Count(*) from Orders", aParamInfo);

object[] aData = new object[5];

aData[0] = DateTime.Now;

aData[1] = "YYYY";

aData[2] = "RG";

aData[3] = (int)3;

aData[4] = null;

Rowset = Command.OpenRowset(aData, true, enumCursorType.ctStatic, true, false, false, false);

while (Rowset != null)

{

if (Rowset.IsOpen)

{

ShowRowset(Rowset);

//must close the current rowset to prepare for querying the next rowset

Rowset.Close();

}

Rowset = Command.GetNextRowset();

}

Open a stream using a parameterized stored procedure.

string strStatement = "CREATE PROCEDURE OrdersXML @nOrderID int AS select * from Orders where Orders.OrderID < @nOrderID FOR XML AUTO";

try

{

Session.ExecuteSQL(strStatement);

}

catch (COleDBProException err)

{

Console.WriteLine(err.Message + ", error code = " + err.HResult);

}

strStatement = "{ ? = CALL OrdersXML(?) }";

CParamInfo[] ParamInfo = new CParamInfo[2];

ParamInfo[0] = new CParamInfo();

ParamInfo[1] = new CParamInfo();

ParamInfo[0].m_nDBType = enumDataType.dtInt32;

ParamInfo[0].m_nDBParamIO = enumParamIO.Output;

ParamInfo[1].m_nDBType = enumDataType.dtInt32;

ParamInfo[1].m_nDBParamIO = enumParamIO.Input;

Console.WriteLine();

Command.OpenWithParameters(Session.IOpenRowset, strStatement, ParamInfo);

object[] aData = new object[2];

aData[1] = (int)10279; //input parameter

Stream = Command.OpenStream(aData);

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

5. How many features can a CRowset have?

In OLEDB world, a rowset object may expose a lot of interfaces with many different methods, which indicates that a rowset may have many features available to you. In fact, OLEDB has much more features than all of other data access technologies. This is one of huge advantages of OLEDB technology over others. OLEDB technology also supports many more data types because it wraps data accessing for all of type data sources. In addition, OLEDB is extremely extensible and flexible. Therefore, OLEDB can be complex to use, especially for managing a rowset, and it is not strange for many programmers to complain complexity of OLEDB. Fortunately, OleDBProNet wraps all of OLEDB rowset features very nicely, and gets rid of all of pitfalls of OLEDB programming. It is extremely simple to use without losing OLEDB performance and flexibility much.

a). Get data from a rowset.

It is simple to get data from a rowset after opening a rowset successfully. See the following code:

static public void ShowRowset(CRowset Rowset)

{

if (Rowset != null && Rowset.IsOpen)

{

uint nCol;

object obData;

uint nCols = Rowset.Cols;

Rowset.MoveFirst();

while (!Rowset.IsEOF)

{

for (nCol = 1; nCol <= nCols; nCol++)

{

obData = Rowset.GetData(nCol);

if (obData == null)

Console.Write("(null)");

else

Console.Write(obData.ToString());

if (nCol != nCols)

Console.Write(", ");

}

Console.WriteLine();

Rowset.MoveNext();

}

The above code can be used to get any data from any rowset opened from any methods in the above sections, no matter how many BLOBs the rowset has. In order to simplify your coding, OleDBProNet automatically maps all of complicate data types like VARNUMERIC and NUMERIC into ones recognized by .Net. If you worry the data precision loss, you can set data type into a string or others by calling the method CRowset::SetDataType after a rowset is opened but before calling method CRowset::MoveFirst. OleDBProNet internally manages BLOBs on the fly so that you will never have trouble in managing BLOBs.

b). Update database through an updateable cursor.

Contrary to ADO.NET, OleDBProNet fully supports various client and server updateable cursors. You can use these cursors to add, update and delete records flexibly and friendly. This feature makes you coding fun. See the following code from the sample project UpdateRowset.

//add

Rowset = Command.OpenRowset("Select * from Shippers Where 1 = 0");

Rowset.BeginUpdate();

if (Rowset.Writable(1))

{

Rowset.SetData(1, (int)4);

}

Rowset.SetData(2, "OleDBProNet");

Rowset.SetData(3, "(111) 222-3333");

//add a record into table

Rowset.Add();

Rowset.Close();

//update

Rowset = Command.OpenRowset("Select * from Shippers", false, enumCursorType.ctStatic, false, false, true, false);

//position to the last record

Rowset.MoveLast();

Rowset.BeginUpdate();

Rowset.SetData(3, "(222) 333-4444");

Rowset.SetData(2, "Microsoft");

//update a record back into database

Rowset.Update();

//delete

Rowset = Command.OpenRowset("Select * from Shippers", false, enumCursorType.ctStatic, false, false, true, false);

//position to the last record

Rowset.MoveLast();

//delete the last record

Rowset.Delete();

Note that it is very simple and flexible to update database through a rowset for a few records. However, if you want to update a lot of records, it is recommend to use a parameterized statement or stored procedure for better performance and scalability, because the latter method requires much less resources in the database backend and does not need expensive cursors. Cursors make programming simpler and more intuitive as shown in the above and below, but they are expensive. It is important for you to know when and how to use them.

c). Navigate records fast and efficiently in many ways.

Contrary to ADO.NET model, OLEDB provides many ways to efficiently and friendly navigate a rowset using a proper cursor on either client or server side. In order to experience navigating a rowset in OLEDB technology, you’d better play with the provided sample project ScrollRowset.

Skip a given number of records: As shown in the below code, you can skip a number of records so that you don’t have to fetch records one by one sequentially.

//Move forward 25 records

Rowset.MoveNext(24);

obData = Rowset.GetData(1);

Move back and forth records: As long as you enable the property scrollable to true, you can easily scroll rowset back and forth as following.

Cmd.SetProperty("IRowsetScroll", true);

Rowset = Cmd.OpenRowset("Select * from Orders Order by OrderID",

false, //multiple rowset

enumCursorType.ctStatic,

true, //readonly

false, //batch update

true, //scrollable

true //bookmark

);

Rowset.MoveLast();

obData = Rowset.GetData(1);

Rowset.MoveNext(-568);

obData = Rowset.GetData(1);

Rowset.MovePrev(); //equal to Rowset.MoveNext(-2);

obData = Rowset.GetData(1);

Rowset.MoveFirst();

obData = Rowset.GetData(1);

Use bookmark: You can pin-point a record by use of bookmark. To use bookmark, first we open a rowset with bookmark as the above code, and bookmark a specific record. At last we can directly navigate to a record. See the following code.

if (obBookmarkData == null && Rowset.Bookmarkable)

{

obBookmarkData = Rowset.BookmarkValue;

}

//navigate some records here

Rowset.GetRowsAt((byte[])obBookmarkData);

obData = Rowset.GetData(1);

Navigate to records with a given ratio: After enabling the interface IRowsetScroll as shown above, you can directly navigate to records with a given ratio. Also, you can estimate an approximate position to a book-marked record.

//get rows based on a given ratio

Rowset.GetRowsAtRatio(40, 100);

obData = Rowset.GetData(1);

Rowset.GetApproximatePosition((byte[])obBookmarkData, ref nPosition, ref nRows);

obData = Rowset.GetData(1);

Find records according to a given field value: In addition to the above ways for navigating records, you can find records with a given field value directly on server side. For details, see the below code and the sample project RowsetFind.

Rowset = Command.OpenRowset("Select * from Orders order by orderid");

CFindInfoData FindInfoData = new CFindInfoData();

FindInfoData.m_nCol = 1;

FindInfoData.m_FindData = 10356;

FindInfoData.m_nOp = CFindInfoData.CompareOperation.DBCOMPAREOPS_EQ;

Rowset.FindFirst(FindInfoData);

ShowRow(Rowset);

Rowset.MoveNext(100); //skip 100 records

ShowRow(Rowset);

FindInfoData.m_FindData = 6;

FindInfoData.m_nCol = 3; //search on the 3^rd column

FindInfoData.m_nOp = CFindInfoData.CompareOperation.DBCOMPAREOPS_EQ; //equal to

Rowset.FindLast(FindInfoData);

ShowRow(Rowset);

Rowset.MoveNext(-100); //skip 100 records

ShowRow(Rowset);

Rowset.FindLast(FindInfoData);

ShowRow(Rowset);

Rowset.FindPrev(FindInfoData);

ShowRow(Rowset);

To enable finding records, an OLEDB provider must support the interface IRowsetFind. For example, ORACLE Oracle and MS SQL native OLEDB providers natively support the interface. Therefore, you can use the above method to quickly find a specific record against a giant rowset. This is also a very important feature, specific to OLEDB technology. Also, MS client cursor engine supports the interface, you can use MS client cursor engine for any OLEDB providers.

Seek records with given values of an index: OLEDB technology supports seeking records according to given values for an index. Although most of OLEDB providers don’t support the method, MS jet engine OLEDB provider support the method as shown in the below code from the sample project Seek.

//enable the interface IRowsetIndex

PropSet.AddProperty(RowsetPropertyID.DBPROP_IRowsetIndex, true);

//open a table with an index

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

//set the range of rowset records, equal to the statement

//select * from EmpInfo where lastname between 'Davolio' and 'Peacock'

CKeyInfoData []StartKey = new CKeyInfoData[1];

StartKey[0] = new CKeyInfoData();

StartKey[0].m_KeyData = "Davolio";

StartKey[0].m_nCol = 3;

CKeyInfoData []EndKey = new CKeyInfoData[1];

EndKey[0] = new CKeyInfoData();

EndKey[0].m_nCol = 3;

EndKey[0].m_KeyData = "Peacock";

Rowset.SetRange(StartKey, EndKey);

CKeyInfoData[] KeyInfo = new CKeyInfoData[1];

KeyInfo[0] = new CKeyInfoData();

KeyInfo[0].m_KeyData = "Leverling";

KeyInfo[0].m_nCol = 3;

//directly position to the record containing lastname = Leverling

Rowset.Seek(KeyInfo);

Note that you don’t have setting range of records as shown in the above

d.) Create DataTable from a rowset.

If you want to create a DataTable from a rowset, you simply call the CRowset::GetDataTable as shown in the below. Internally, CRowset will fetch all of records from a database for you and populate all of records into a DataTable instance.

DataTable dt = Rowset.GetDataTable("Customers");

e.) Open enumerator rowset.

It is very simple to open an enumerator rowset for installed OLEDB providers, created ODBC data sources, SQL servers or others. See the below code from the sample project OleRead.

//open OLEDB enumerator

Rowset.OpenEnumeratorRowset(CommonEnumeratorGuid.OLEDBEnum);

ShowRowset(Rowset);

//open ODBC enumerator

Rowset.OpenEnumeratorRowset(CommonEnumeratorGuid.ODBCEnum);

ShowRowset(Rowset);

//open SQL server enumerator

Rowset.OpenEnumeratorRowset(CommonEnumeratorGuid.SQLOLEDBEnum);

ShowRowset(Rowset);

f.) Use rowset activity notifications.

If you need to monitor various rowset activities, you can implement it by setting delegates DRowChange, DRowsetChange and DFieldChange. By default, these delegates (events) are not enabled. To enable these delegates, set the property IConnectionPointContainer to true before opening a rowset, create delegates and set them to CRowset, and turn on the property RowsetNotify as shown in the below code.

Command.SetProperty(CommonPropertySet.DBPROPSET_ROWSET, RowsetPropertyID.DBPROP_IConnectionPointContainer, true);

Rowset = Command.OpenRowset("Select * from Shippers", false, enumCursorType.ctStatic, false, false, true, false);

Rowset.m_OnFieldChange = new DFieldChange(FieldChange);

Rowset.RowsetNotify = true;

6. Open data source and rowset asynchronously.

Asynchronous processing enables methods to return immediately without blocking on the calling thread. It offers unique advantages over common synchronous processing. Although most of OLEDB providers don’t support asynchronous processing, the latest MS SQL 2005 native client OLEDB provider supports asynchrony computation well. Therefore, OleDBProNet includes this feature. To show you how to use it, please see the sample project AsynParallel.

a.) Build connection to a database asynchronously.

See the following code. To establish a connection asynchronously, you must call CdataSource::SetInitAsynch(true).

DataSource.SetDatabase("Northwind");

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

DataSource.SetIntegrated("SSPI");

//establish connection asynchronously

DataSource.SetInitAsynch(true);

DataSource.OpenDirectly(CommonProviderClassID.SQLNCLI);

//do your work here

DataSource.WaitForAsynchCompletion(10000);

In addition to use of the method WaitForAsynchCompletion, you can use the method CDataSource::GetAsynStatus to poll connection status. If you want to stop connecting before a connection is completed, you call the method CDataSource::AbortAsyn.

b.) Open a rowset asynchronously.

Similarly, you can also open a rowset asynchronously. Here is code snippet from the sample project AsynParallel.

//set property DBPROP_ROWSET_ASYNCH to 1

Command.SetProperty(CommonPropertySet.DBPROPSET_ROWSET, RowsetPropertyID.DBPROP_ROWSET_ASYNCH, (int)1);

Rowset = Command.OpenRowset("Select * from Orders order by orderid");

SSRowset.OpenFrom(Rowset);

if (!SSRowset.Initialized)

{

//do your work here

SSRowset.WaitForAsynchCompletion(10000);

//you need to make a call CRowset::PostProcess after generating a rowset asynchronously

SSRowset.PostProcess();

}

To open a rowset asynchronously, we must set the property DBPROP_ROWSET_ASYNCH to 1 first. Afterwards, if a rowset is not initialized, we call CRowset::WaitForAsynchCompletion. At last, we need to call the method CRowset::PostProcess for completing the asynchronous processing. Similarly, you can use the method CRowset::GetAsynStatus to poll the opening status. If you want to stop opening before a connection is fully initialized, simply call the method CRowset::AbortAsyn.

7. Bind a cursor with data grid view control

Starting from version 1.5.0.1, OleDBProNet supports binding a database cursor with .NET data grid view control. Here are simple steps to bind a data grid view control with an instance of CRowset. First of all, you should use CUGridView instead of DataGridView as below.

private CUGridView gvCursor; //System.Windows.Forms.DataGridView;

gvCursor = new OleDBProNet.CUGridView();

Second, bind an instance of CRowset after it is opened.

cmd = m_Session.OpenCommand();

//cmd.SetProperty(CommonPropertySet.DBPROPSET_ROWSET, RowsetPropertyID.DBPROP_IRowsetFind, true);

//cmd.SetProperty(CommonPropertySet.DBPROPSET_ROWSET, RowsetPropertyID.DBPROP_SERVERDATAONINSERT, true);

cmd.SetProperty(CommonPropertySet.DBPROPSET_ROWSET, RowsetPropertyID.DBPROP_REMOVEDELETED, true);

cmd.SetProperty(CommonPropertySet.DBPROPSET_ROWSET, RowsetPropertyID.DBPROP_OTHERUPDATEDELETE, true);

m_Rowset = cmd.OpenRowset("Select * from Orders", false, enumCursorType.ctStatic, false, false, true, true);

m_Rowset.BatchSize = 15; //or page size

//attach rowset with grid view

m_Rowset.Attach(gvCursor, "Orders");

//set cells to whatever type cell you like.

//Otherwise, OleDBProNet will use default types of cells.

//postion to the very first record

m_Rowset.MoveFirst();

Once a rowset or cursor is bound with a data grid view control, keys Home, Page Down, and arrow down will function like moving a cursor to the very first batch of records, next batch of records, and next record, respectively. If a cursor is scrollable, you can use keys End, Page Up and arrow up to position the cursor to the last batch of records, previous batch of records, and a previous record, respectively. If you want to update, add and delete records into and from a database flexibly through a rowset, open a non-readonly rowset. Afterwards, you can edit cells. By clicking left row header, you can delete a record. To update and delete records, you are not required to code. Also, you can add record as shown in the attached sample CursorDataGridView.

Once a rowset is attached with a data grid view control, any changes in the rowset will be synchronized onto the data grid view control. Because CRowset supports many OLEDB features like bookmark, backward fetching and scrolling, and finding records either on a bookmark value or column data value, you can easily and fast navigate a rowset though a server cursor back and forth without fetching the whole rowset from the backend database to a client. This works truly great for a large set of records. You can’t get these features through ADO.NET because of its limitations.

Here is the sample picture from the project CursorDataGridView.

8. Write a high performance application.

No programmers are like to write a sluggish code. In some cases, we have to focus on performance tuning for meeting our business requirements and make sure the quality of our applications are competitive to our competitors. Here are a few simple guides to better performance.

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

OLEDB rowset can support many features through cursors. However, you don’t have to need all of these features and should use the features you just want, no more and no less. The more features you require, the slower you fetch records. For example, if you just need a read-only cursor through executing a SQL statement using a command, you should use a forward-only cursor, that will accelerate fetching records from a backend database server to a client.

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

Adding, updating and deleting records are usually much slower than reading records from a backend database, but in many cases we do need to update a great number of records into a database. Therefore, we need a way to speed up adding, updating and deleting bulk records. In most cases, the performance is determined by a number of factors such as table design, database management system, network bandwidth, machine hardware, and our code. Let’s focus on our code. As programmers, we should keep on it in mind that our codes should always support adding, updating and delete records in batch and never in the way one by one.

If an OLEDB provider provides a specific interface for bulk updating, we should use it. For example, we could use bulk copy interface IRowsetFastLoad to add a large number of records into MS SQL server. If an OLEDB provider doesn’t have a specific interface to for bulk updating, you should consider using CCommand::DoBatch through a parameterized SQL statement or stored procedure. The method is very simple to use as shown in the sample project BatchParams. For example, all of Oracle OLEDB providers support batching updating records very nicely. Use of this feature will significantly improve the performance for updating records usually. Although you can use a writable server cursor to accomplish these tasks, it is not recommended for updating lots of records. Note that a writable server cursor is still great for updating a few records for its simplicity and flexibility.

c.) Use manual transaction instead of automatic transaction properly.

Use of manual transaction will improve performance, but one transaction can’t cover too many records because a large transaction will harm other transactions from applications. In reality, you need to make a test for finding a proper number of records.

Contents