Introduction to CUBRID OLE DB Support
The scope of this tutorial is to provide an introduction to CUBRID OLE DB support. We will show you where and how to get the CUBRID OLE DB libraries, how to setup a CUBRID connection and how to develop a simple .NET application using the CUBRID OLE DB data provider.
Note: This tutorial uses CUBRID 8.3.0 32-bit, Windows 7, Visual Studio 2010 Express Edition.
Table of Contents
OLE DB / .NET overview
OLE DB is a set of COM-based interfaces that expose data from a variety of sources. OLE DB interfaces provide applications with uniform access to data stored in diverse information sources, or data stores. See http://msdn.microsoft.com/en-us/library/ms722784(VS.85).aspx.
ADO.NET is a set of classes that expose data access services to the .NET developer. ADO.NET provides a rich set of components for creating distributed, data-sharing applications. It is an integral part of the .NET Framework, providing access to relational data, XML, and application data. ADO.NET provides functionality to developers writing managed code similar to the ADO functionality provided to native COM developers.
A data provider in the .NET Framework serves as a bridge between an application and a data source; a data provider is used to retrieve data from a data source and to reconcile changes to that data back to the data source.
Using the MS ADO .NET managed provider for OLE DB, it is very easy to use ADO.NET with OLE DB data providers, namely with the CUBRID OLE DB data provider.
- If you have not downloaded the OLE DB Provider, you can get it from http://www.cubrid.org/oledb_programming.
- Introduction to CUBRID OLE DB Support.pdf: This tutorial in PDF format.
- WindowsFormsApplication1.zip: the source code of the example used in this tutorial.
- MiniSQL.zip: Mini SQL preconfigured for CUBRID. Mini SQL Query is a minimalist open source SQL query tool for multiple databases (MSSQL, MSSQL CE, SQLite, OLEDB, MS Access/Excel files etc.). MiniSQL works fine with CUBRID and we recommended it as a nice example of developing a .NET application consuming an OLE DB Data provider.
CUBRID OLE DB Data Provider
The first thing we need to know in order to create a CUBRID connection from our .NET application is how to build the connection string. Looking into the CUBRID documentation, you will find that when you use the CUBRID OLE DB Provider, the connection string must be built using the following data:
|Data source||demodb||Database name|
|Location||192.168.1.10||The IP address of the CUBRID Broker Server|
|User ID||dba||Database User ID|
|Port||30000||Broker port number|
|Fetch Size||100||(Optional) Fetch size|
Provider=CUBRIDProvider.1;Data Source=demodb;Location=127.0.0.1;User ID=dba;Port=30000
And this is how it looks in the Visual Studio Properties windows, for a CUBRID connection:
Creating a simple CUBRID .NET application
- CUBRID 8.3.0
- Microsoft Visual Studio Express Edition
- .NET 4
- CUBRID OLE DB Data Provider (Cubrid.Data.dll - see previous sections)
The first thing we will do is to create a new Windows Forms Application project, using the default names/folders:
Next we will add the CUBRID OLE DB Data Provide library in our project References:
Visual Studio will automatically copy Cubrid.Data.dll to the project output folder.
Let’s add now a CUBRID connection in the Server Explorer. Expand Server Explorer window, select Data Connections, right-click and Add Connection:
Enter the CUBRID connection information: server, database, port, user, and password:
When you are done entering the connection information, click Test Connection, to make sure everything is ok and you can connect successfully to the database:
Tip: If you run into a connection error, make sure you specify the (broker) port value. By default, it is set to 0 and you will need to change it (most usually to 30000 or 33000).
Now you will be able to browse the CUBRID database from within Visual Studio Server Explorer window:
Note: Creating a CUBRID Connection in the Server Explorer is not a mandatory step; however, we recommend doing this always in your CUBRID .NET projects because it provides additional advantages during development, like validating the CUBRID connection, easy browsing the database structure, testing your queries etc.
Now let’s create a simple table data browser, for the history table in the demodb database. Select Toolbox/Data/DataGridView and drag-drop it onto your form. You will be prompted to create a new DataSet/Data Source that will be associated with the DataGridView control:
Once you have created the CUBRID DataSet, choose the proper data source for the history table:
One more step = set the EnforceConstraints property of the DataSet to False and Run (F5 or Ctrl-F5). The application window will display the data from the history table:
…And that’s all..! …you have just created your first CUBRID .NET application using the CUBRID OLE DB Data Provider!
CUBRID OLE DB project
Let’s take a closer look at the implementation of the CUBRID OLE DB Provider. Load the project in Visual Studio and open the Object Browser (Ctrl+W, J):
Besides the standard implementation of the mandatory OLE DB interfaces, you will notice that there is support for some specific CUBRID features, for example:
- OID (CubridOid, OidCommand)
- GLO data type (GloCommand, GloNewType)
When loading the project in Visual Studio, you will also notice that, along with the implementation of the data provider, a simple test application is also provided, which you can use to test the implementation but also as a quick startup coding example:
When developing applications using the CUBRID OLE DB Provider with Microsoft .NET, you should consider the followings:
- If you develop multi-thread programs using ADO.NET in the management environment, you need to change the value of the ApartmentState attribute of the Thread object to an ApartmentState.STA value because the CUBRID OLE DB Provider supports only Single Threaded Apartment (STA) attributes. Without any change of given values, the default value of the attribute in the Thread object returns Unknown value, thereby causing abnormal process or errors during multi-threads programming. Currently, the CUBRID OLE DB Provider supports only the apartment threading model among COM threading models. It does not support the free threading model. This applies to not only the .NET but all multi-threaded environment.
- Follow the recommendations and best practices regarding developing secure .NET applications; you can find more information and help here:
http://msdn.microsoft.com/en-us/library/ms723115(VS.85).aspx (OLE DB)
- For more open source examples on using ADO.NET, look here:
- You are also highly recommended to read ADO.NET Developer Guidance Map.pdf
- CUBRID ASP.NET Sample Program
- Configuring the Environment of ODBC and ASP
- OLE DB / .NET Programming with CUBRID
- CUBRID Tutorials
If you encounter any difficulties using CUBRID OLE DB Connector, post your issues to CUBRID ADO.NET forum at http://forum.cubrid.org/.
The scope of this tutorial is to show you how to work with CUBRID database from Windows PowerShell.