What is OLE DB?

OLE DB? What the hell is this? Many of you may not have heard of it before. To answer this question, we have to review the history of database connections first.

Early database connectivity is very difficult. Each database has a different format, and developers have a deep understanding of the underlying APIs for each database they develop. As a result, generic APIs that can handle a wide variety of databases have emerged. The current ODBC (Open Database Connectivity), ODBC is an early product of people creating generic APIs. There are a number of databases that comply with this standard and are called ODBC-compliant databases.
ODBC-compliant databases include access, Ms-sql Server, Oracle, Informix, and so on.

However, ODBC is not perfect, it still contains a large number of low-level calls, the development of ODBC applications is still more difficult. Developers have to devote a lot of their energy to the underlying database communications and not focus on the data they are working on. Then Microsoft proposed a solution: DAO (Data Access Objects). The DAO code looks like this:

Objitem.addnew
objItem.Name = Chair
Objitem.price = 10
Objitem.update

You may have seen the DAO code. Later DAO evolved into RDO (Remote data Objects, designed for distributed database System), and then ADO. Although they all have their own shortcomings. According to Microsoft, ODBC provides access to local SQL data, and DAO provides advanced data objects. Both DAO and RDO require data to be stored in the format of SQL (structured Query Language). In response to these flaws, Microsoft has proposed OLE DB, a COM-based data storage object that provides operations on all types of data and even accesses data offline (for example, you're using your laptop and you can easily see the data image of the last data synchronization).

OLE DB is located between an ODBC layer and an application. In your ASP page, ADO is an application that sits on top of OLE DB. Your ADO call is sent to OLE DB before it is processed by ODBC. You can connect directly to the OLE DB layer, and if you do, you'll see a performance boost for server-side cursors (the default cursors of the recordset and the most commonly used cursors). So how do we connect directly to OLE DB?

To connect directly to the OLE DB layer, you must change your connection object connection string. First, create a Connectiong object in the old fashioned:

Dim objconn
Set objconn = Server.CreateObject (ADODB. Connection)

Next, we do not use conventional similar dsn=pubs or Driver={ms sql-
Server}; Uid=sa; pwd=;D atabase=pubs; Server=mymachine connection string, using the following connection string:

objconn.connectionstring = Provider=providername; Data
Source=databasesource; Initial Catalog=databasename; User Id=userid;
Password=password

For SQL:
ProviderName = SQLOLEDB
Data Source = Server Name
Initial Catalog = Database Name

For access:
ProviderName = microsoft.jet.oledb.3.51
Data Source = Full path to. MDB file

Let's take a look at two examples, one for access and one for SQL. If your connection to the SQL Dsn-less connection string is like this:

Driver={ms Sql-server}; Uid=sa; pwd=;D atabase=pubs; Server=mymachine

So the connection string that connects directly to OLE DB should be this way:

Provider=sqloledb; Data Source=mymachine; Initial catalog=pubs; User
Id=sa; password=

Let's take a look at access if your connection string for access is:

Driver={microsoft Access DRIVER (*.mdb)};
Dbq=c:/inetpub/wwwroot/users.mdb

So the connection string that connects directly to OLE DB should be this way:

provider=microsoft.jet.oledb.3.51; Data
Source=c:/inetpub/wwwroot/users.mdb

It's so simple, it's great, right?

Is that important?
Now you may be confused about why you should learn this new database connection method, why not take the standard Dsn-less/system DSN path? Let me tell you why. According to wrox out of ADO 2.0
Programmer's Reference, the test in the book, the connection with OLE DB connections instead of DSN or dsn-less will improve the performance as follows:

Performance comparison
SQL Access
OLE DB DSN OLE DB DSN
Connection times:18 Connection times:62 99
Iterating through 1,000 Records times:2900 5400 iterating through
1,000 Records times:100 950