Development guide with
OleDBProNet
1. Introduction.
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
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
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.
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");
void OpenWithDataLinks();
void OpenWithDataLinks(enumDataLinkDialogOption
DataLinkDialogOption);
CDataSource DataSource = new
CDataSource();
//open
a data source through MS datalink dialog.
DataSource.OpenWithDataLinks();
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.
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);
}
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
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)