article

Repost: ADO.Net Primer + Programming databases using OLE DB with ADO.Net

Email
Submitted on: 1/3/2015 12:05:00 AM
By: TeknikForce (from psc cd)  
Level: Beginner
User Rating: By 6 Users
Compatibility: VB.NET
Views: 1447
 
     Moving from ADO to ADO.Net? Planning to access a Non SQL Server database? This article talks about databases on your hard disk, and discusses almost all of the important ADO.Net classes.

 
				

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 scene.

 

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 articles.

 

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 exposes.

 

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 Server installed?

 

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.

 

Programming OLEDB

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 adaptable.

 

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:

 

·         OLEDBConnection

·         OLEDBCommand

·         OLEDBDataAdapter

 

OLEDBConnection

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.

 

cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\dic.mdb;"

 

This connectionstring opens a Microsoft Jet 4 Database named ‘dic.mdb’ located in ‘c:\’.

 

Here-

 

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 to.

 

Don’t forget to put the semicolon ‘;’ mark after each attribute, or the code won’t work.

 

OLEDBCommand

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.

 

CommandType

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 use.

 

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 object.

 

The Ole DB Command object supports three kinds of Execute statements.

 

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”

myCommand.ExecuteNonQuery

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

 

DataReader, DataAdapters & DataSet

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 possible.

 

Using DataReaders

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 next record.

 

DataAdapters

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 ‘SelectCommand’ property.

 

myDataAdapter.SelectCommand = myCommand

 

Now you can fill a DataSet with the resulting records.

 

MyDataAdapter.Fill(myDataSet,”myTable”)

 

DataSets

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 method.

 

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

Console.WriteLine(“MyColumn”)

Next

 

This will iterate through the entire table and write the value of the column “MyColumn” to the Console window.

 

Updating Databases

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.

 

Example:

MyDataSet.Tables(“MyTable”).Rows.Add(myNewRow)

 

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.

 

Example.

myDataAdapter.Update(myDataSet)

 

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

 

Cyril Gupta


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Beginner category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments


 There are no comments on this submission.
 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)
 

To post feedback, first please login.