ADO.Net Primer + Programming databases using
OLE DB with ADO.Net
By Cyril Gupta
Although I’ve been working with Visual Basic for several
years now (started with Visual Basic 3.0), and I was quick to adopt the newer
versions of Visual Basic as they came, I was a rather late entrant on the .Net
But migration to .Net had to occur eventually, so here I
am working with .Net, and to keep me company, I’ve decided to write a series of
Acessing Local Databases
Read .Net books or tutorials and they will tell you that
data access in VB.Net is pretty straightforward using Ado.Net, Microsoft’s
replacement for ADO. However, if you’ve been using DAO and ADO long enough like
I, you’ll soon be lost among the swamp of new classes and properties that ADO.Net
ADO.Net has two primary clients to establish the
connection with the data: The SQLClient and OLEDB.
The SQLClient is designed to connect only and only to
Microsoft SQL Server databases, while the OLEDB client allows you to connect to
any database that has an OLE DB server.
This is where I was lost for quite a while. Almost all of
the examples in database programming books that I saw were exclusively based on
the SQLClient class. Good, but what about the programmers who don’t use SQL
Server, or make applications that has to be deployed on PCs that don’t have SQL
As a maker of packaged applications I use Jet Databases
most of the time and my apps are used by thousands of users. I can’t force them
to install SQL Server.
You’ve got two options.
a) Stick to ADO
The .Net framework has full support for Microsoft ADO
database access. You can declare ADO classes, and use them the same way you’d
use them in your Visual Basic 6 project.
To use ADO add a reference to the ‘adodb’ .Net component
by right clicking on ‘References’ in your project, and then selecting ‘adodb’
from the Components list. This will allow you to create instances of all the
ADODB classes like Connection, Command and Recordset, and you will never notice
that you’re working with .Net.
I am not going to show you the code to do this as you
probably already know how, and if you don’t, you can look at the million or so
code examples on the Internet.
That was the easy way out, and that is what I decided to
do at first. However, when I began, something didn’t feel right. True cowboys
don’t take the easy way out, and I am not inferior in courage, or guts to any plain old cowherd.
Also there’s one more disadvantage. Microsoft.Net does not
use really compiled applications, actually the applications that we make are
compiled to MSIL (Microsoft Intermediate Language) and a Just-In-Time compiler
is used to compile then when they’re run. ADO isn’t natively supported in .Net
so it can’t be compiled to MSIL. Your code will still compile and work, but it
will use COM Interoperability, so the disadvantage is purely from a technical
point of view.
Let’s explore the second option.
b) Use OLEDB
Just about when I was about to give up on SQLClient and go
back to ADO to access my local databases, I found somewhere that OLEDB is the
interface to use to access all kinds databases with ADO.Net. This naturally
includes Jet databases.
This is what I decided to use, and this is what the rest
of this tutorial talks about.
The OLEDB does not provide the same kind of ease of use or
features like ADO or SQLClient does. The class is stricter, and unlike ADO
objects that allow you to declare and initialize the objects in a wide variety
of ways through polymorphed functions, OLEDB objects are harsher and less
Properties like Connection.Provider that you could assign
data to in ADO, are read only in OLEDB, which caused me a lot consternation
when I began using the objects.
Primary OLEDB Objects that you will need to access data:
You can call this a replacement for the ADODB.Connection
object. It behaves in a similar manner, and you can open the database the same
way you did with the ADODB.Connectionstring. However, unlike ADODB.Connection
object, most methods in OLEDBConnection object do not support polymorphic
arguments the same way that ADODB.Connection does.
How does this affect you? For example, you won’t be able
to assign values to OLEDBConnection.Provider or OLEDBConnection.Datasource like
you did with ADODB. All these properties are read only in OLEDB and you will
have to specify everything in the ConnectionString property.
Here’s some sample code.
This connectionstring opens a Microsoft Jet 4 Database
named ‘dic.mdb’ located in ‘c:\’.
Provider = The database service provider name. If you use
Jet Databases (.MDB) this will mostly be ‘Microsoft.Jet.OLEDB.4.0’ for the new
Acess 2000 databases, and ‘Microsoft.Jet.OLEDB.3.5’ for older databases. You
can open Jet 3.5 databases with Jet 4.0 but not vice-versa. So if you create a
database using the new version of Microsoft Access and not the Database wizard
in Visual Basic 6 then you will have to use the 4.0 provider.
Data Source = The database name. This can be a file, or a
database name if you use a database server like MS Sql Server. Yes, you can
open SQL Server databases using OLEDB object instead of SQLClient if you want
Don’t forget to put the semicolon ‘;’ mark after each
attribute, or the code won’t work.
The OLE DB command object allows you to execute SQL
procedures on your data tables and to retrieve data from the table.
To get the records from our data table we must first
assign the connection object to the connection property of the command object.
myCommand = myConnection;
Now set the ‘Commandtype’ property. The CommandType
property tells OLEDB what kind of command you wish to execute.
You have three options.
Text – A normal SQL Query.
StoredProcedure – The procedure name of a stored procedure
in the database.
TableDirect – The Tablename of the table to open.
Most of the time you’ll find yourself using the Text
property or the TableDirect property. However it’s a great idea to use Stored
Procedures in a database to do frequently performed operations.
I going to set my CommandType to Text for the moment.
myCommand.CommandType = CommandType.Text
Now you must tell the Command object what Query string to
myCommand.Text = “SELECT * FROM DIC WHERE WORD LIKE ‘A%’”
This will retrieve all rows of the DIC table in which the
Word column starts with the letter ‘A’.
If you used the TableDirect CommandType then you can
simply assign the name of the table to the Command.Text property. This would
look like this.
myCommand.Text = “Dic”
This will retrieve all the rows in table Dic.
More about Queries
Before we move on to learning how to use the data retrieved
from the query let’s learn a little about Executing queries using the Command
The Ole DB Command object supports three kinds of Execute
ExecuteNonQuery – Execute an SQL statement that does not
return a result.
ExecuteReader – Execute an SQL statement that returns a
DataReader object (More about it later.)
ExecuteScalar – Execute an SQL statement that returns the
first column of the resultant recordset.
In ADO you could also execute an SQL statement to return a
recordset object that was updateable and editable, this is no longer supported
with ADO.NET. Now you must use Datasets, which can be compared more favorably
with disconnected recordsets even though the comparison is not very apt. More
about DataSets later, let’s see a sample of each type of query statement first.
Examples Query Statements
myCommand.Text = “DELETE * From Dic”
Will delete everything from the table Dic.
myCommand.Text = “SELECT * From Dic”
myDataReader = myCommand.ExecuteReader
Will get everything from the table Dic and you can assign
to a DataReader object.
myCommand.Text = “SELECT * From Dic”
myVar = myCommand.ExecuteScalar
Will the first column of the first record from the table
Dic. You can assign this value to a variable.
Now let’s come to the most important changes in ADO.NET
With the DataReader object you can get a forward only,
read only set of records from the database. The DataReader object establishes a
really fast connection to the record data and you should use it whenever
Once you’ve populated a DataReader object with records you
can use the DataReader.Read property to read the column values from the
recordset. On calling Read the DataReader object will automatically move to the
DataAdapters work as a link between data sources and
DataSets. To get records from or update records to any data source you must go
through a DataAdapter in ADO.Net.
First tell DataAdapter what Command to use using the
myDataAdapter.SelectCommand = myCommand
Now you can fill a DataSet with the resulting records.
The closest comparison of an ADO.Net DataSet would be a
disconnected Recordset in ADO. The DataSet also has XML capabilities and can
read or write XML files direct.
You can browse the data inside a DataSet using the Tables
collection in the DataSet once you’ve filled it using the DataAdapter’s fill
To iterate through all the rows in a DataSet you can use
the Rows collection of the table.
Dim myDatRow as Data.DataRow ‘Declare a Datarow
For Each rsRow in MyDataSet.Tables(“myTable”).Rows
This will iterate through the entire table and write the
value of the column “MyColumn” to the Console window.
You cannot directly update using the DataSet object like
you could with a RecordSet in ADO. To update records to a database you must go
through a DataAdapter Object.
To add new records or rows to the Dataset you can call the
Add method of the Rows collection of the Table. The Add method either accepts a
new Row object as a parameter or an array filled with values for the new row.
You can change the values in the row by simply selecting
the required Row and then assigning new values to the columns (No need to call
any ‘Edit’ method)
Once you’ve finished making changes to the DataSet you can
commit the changes to the Database by calling the DataAdapter’s Update method
and passing the changed DataSet as the parameter.
This winds up the ADO.Net Primer for about now. ADO.Net is
big and this primer is by no means complete. I intend to write more episodes of
the primer and introduce the other features of ADO.Net. The next primer in this
series would most probably be based on processing XML through ADO.Net.
Adios until then