Connecting to MS SQL Server "pubs" database
1. Create new project.
2. Add path to $(OLEDBDirect)\Dcu (where $(OLEDBDirect) is directory where it is installed).
3. Drop OLEDBDataSource to main form.
OLEDB Direct components works directly with OLEDB interfaces where there is no connection string. So, when you set Initialization string, OLEDB Direct connects to database using Client Cursor Engine (CCE), then looks connection properties and stores them. Any way, connection properties are stored as properties, but not as connection string. There may be errors converting connection string into properties. InitializationString should be used as informational property.
The preferred way to specify connection is to set ProviderName property and Properties. So, I set ProviderName to "Microsoft OLEDB provider for SQL Server" (there may be a pause while providers list is initialized), and you see "SQLOLEDB.1" in this property (that is right, list shows provider descriptions, but property shows a string similar to Provider= in ADO connection string, this string can be converted to provider GUID using ProgIDToClassID function from ComObj unit). Also you can see InitializationString (that is only information, properties now are not set).
Now, press button "..." in "Properties" property. In a dialog set properties you need. All properties supported by provider are shown (this information goes from provider and should be correct for any provider, now you can see DBPROPSET_DBINIT and DBPROPSET_SQLSERVERDBINIT properties group). For this example purpose set following properties (all in DBPROPSET_DBINIT group):
NB. All properties have description field where you can see a little description.
1. You should specify login information. You can do this two ways:
a) Set DBPROP_AUTH_INTEGRATED to empty string (edit Value field and you see, that Set and Req field are marked, Set means that property should be set when opening connection, Req that property is required - in OLEDB properties can be optional, but this case provider will decide will it set this property or not). Property DBPROP_AUTH_INTEGRATED means connecting to MS SQL Server using Windows Authentification, this may be not available on all SQL Server versions - if so, use
b) Set DBPROP_AUTH_USERID to login - "sa" and DBPROP_AUTH_PASSWORD to password - "your password here" (all without quotes).
2. Specify db you will use and set DBPROP_INIT_CATALOG to "pubs".
All properties are set and you should press OK.
Now you should decide will you use Client Cursor Engine (CCE) or not. "Client Cursor Engine enables OLE DB consumers to scroll or find over the rowsets of providers that do not natively support such functionality, as well as performance and scaling features such as resource pooling and automatic transaction enlistment within a Microsoft® Windows NT®/Windows® 2000 Component Services (or Microsoft Transaction Server, if you are using Windows NT) environment." - citation from OLEDB SDK. ADO always uses CCE. Setting "UseCCE" to False let you work faster, setting UseCCE to True let you use more properties (for example, some provider do not support ClientCursor). But MS SQL Server supports most properties so leave UseCCE set to False.
Optionally:
You can also set DBPROP_INIT_PROMPT to DBPROMPT_COMPLETE (= 2) and provider will prompt the user for information only if more information is needed.
You can set DBPROP_INIT_HWND to valid window handle for this dialog. Of course, you must not set it from IDE. Set it programmatically to Form1.Handle. On my machine, without this handle ODBC driver fails to show its prompt.
Now, set "Connected" property to true. Datasets and commands do not open connection automatically, so this property should be set manually.
Now you can examine database by looking "Schemas". Setting these properties does nothing, this is only for information and it is as well as "Tables" property is absolutely useless at runtime. There is one other property - "Tables" that let you look at tables structure and modify tables, but for MS SQL Server needed interfaces are not supported (you can modify tables by executing corresponding command). "Tables" property correspond ADOX Tables collection.
4. Drop OLEDBCommandDataSet to form and set its OLEDBDataSource property to OLEDBDataSource1. Set CommandText property to "Select * from authors". As an alternative you can drop OLEDBDataSet and set it TableName to "authors".
Now you should set properties. OLEDB Direct is developped for extremely fast data access and has OLEDBRowset and OLEDBCommand components, but they are not TDataset descendant and can not be used with data controls. But this means one other thing - OLEDBCommandDataSet (and OLEDBDataSet) by default has no properties set and, most cases will be open as forward-only rowsets with very limited capabilities (this depends on provider, but changing data, inserting/deleting rows and even bookmarks could be not supported). This mode may correspond to open ADO dataset with server cursor and all properties disabled. This mode is fastest for read data. However, of cause, you can set properties to have all functionallity you need. This functionallity may be slightly different depending on "UseCCE" property of OLEDBDataSource a dataset is connected to (for example, client cursor may be not supported if UseCCE is False). You can press "..." button near "Properties" property and look which properties are available. But you can look for helper Flags property. Flags set some common properties. If a property is not supported by OLEDB provider you will can not set corresponding flag.
For this example purpose you should set - rfBookmarks, rfLocate, rfScroll, rfCanScrollBackwards, rfCanFetchBackwards, rfCanHoldRows. This will let you navigate through rowset as you need, not only forward-only rowset, this will let you use bookmarks.
If you need modify data, you should set also - rfChange, rfDelete, rfInsert flags. rfUpdate flag corresponds to batch updates, do not set it now.
Now you can set cursor type you need. The following table shows how OLEDB properties map to ADO cursor types:
- adOpenKeyset: DBPROP_OWNUPDATEDELETE, DBPROP_OWNINSERT, DBPROP_OTHERUPDATEDELETE, DBPROP_IRowsetLocate, DBPROP_CANSCROLLBACKWARDS, DBPROP_CANFETCHBACKWARDS, DBPROP_CANHOLDROWS, DBPROP_IRowsetRefresh, DBPROP_REMOVEDELETED.
- adOpenStatic: DBPROP_OWNUPDATEDELETE, DBPROP_OWNINSERT, DBPROP_IRowsetLocate, DBPROP_CANSCROLLBACKWARDS, DBPROP_CANFETCHBACKWARDS, DBPROP_CANHOLDROWS, DBPROP_IRowsetRefresh.
- adOpenDynamic: DBPROP_OWNUPDATEDELETE, DBPROP_OWNINSERT, DBPROP_OTHERUPDATEDELETE, DBPROP_OTHERINSERT, DBPROP_CANSCROLLBACKWARDS, DBPROP_CANFETCHBACKWARDS, DBPROP_REMOVEDELETED.
adOpenForwardOnly All cursor types: DBPROP_IRowset, DBPROP_ISequentialStream, DBPROP_IColumnsRowset.
For this example, set flags rfOwnInsert, rfOwnUpdateDelete flags. This let you see your own inserts/updates/deletes.
You can see which flags are set afer you will open dataset - provider may add functionality, but all flags you set to True should stay set.
Now you need choose PrimaryNavigator - the model of OLEDB Direct uses to navigate records.
- rnStandard - fastest for moving without bookmarks (MoveNext, MovePrevious, Move some records next/previous and so).
- rnBulk - same as rnStandard but has row handles cache (rowset will fetch some rows and while moving inside cache you will not see others changes).
- rnLocate - fastest to move by bookmarks, but is slower while using MoveNext, MovePrevious methods. Also has row handles cache.
- rnCustom - this navigator fetchs all rows and stores their bookmarks. It is slowest but enables filtering and sorting even on server-side cursors.
Leave rnCustom navigator.
Set Active property to True.
From Data Access and Data Controls panel drop DataSource, DBGrid and DBNavigator, connect them as usual and you can see the table Authors from pubs db.
You can save a project and build it.
Really, this may be a difficult fist times but OLEDB Direct let use only properties you need and have fastest access as possible. OLEDB is native for SQL Server and for Jet provider. ADO uses it but has some overhead (converting all data to variants, connecting only using service components, like CCE and so), VCL ADO components will slow your access to data even more. Using OLEDB directly let you also use advanced capabilities of you data provider. For example, on my machine (PII - 300, 192 MB RAM) I created a database with file size 30 MB and transaction file size 300 MB, and filled a simple table (three integer columns) by 1 000 000 rows in less than 1 minute 5 seconds (I used FastLoad interface, its use is shown in FileList demo).
P.S. Some properties may conflict with others. Sometimes you may even need to delete a component and drop to form new one.
Description of all common properties are in MDAC SDK that you can download from http://www.microsoft.com/data or in your provider documentation. Short description of properties is also available in properties dialog.