Print this page.  If your browser doesn't allow JavaScript, right-click this page and choose Print from the popup-menu.        
ADO.NET
Category: SQL
Introduction

PLEASE NOTE! This article is currently not up to date. The access method described herein, using an ODBC based provider, is only recommended towards Mimer SQL database servers older than 9.2.3. For newer Mimer SQL servers there are native Mimer Data Providers available, starting with version 9.3.1. Please see the article Accessing Mimer SQL from ADO.NET using Mimer Data Provider until further notice.

ADO.NET is the interface of choice when developing database applications in the Microsoft .NET framework and it is the natural successor for ADO (ActiveX Data Objects).

The ADO.NET interface allows you to develop applications in the language best suited for the task. Whether you chose to use Visual Basic, C#, J# or C++, your application will use the same ADO.NET library and the database logic will look the same.

Description

The architecture of ADO.NET allows you to work directly connected to the database or in a disconnected manner. In the connected model a .NET framework Data Provider is used for connecting to a database, executing commands, and retrieving results. On contrary, the DataSet and DataAdapter components are the core of the disconnected model. A reflection is that the API in the connected model is quite similar to Java’s JDBC.

Below, we will initially describe how to work in the connected model and then later on we will give a short introduction to the disconnected model.



Connect to the database

Let's get started! First of all you must have a Data Provider installed for your database. The Data Provider is responsible for communicating with the underlying database. When working with Mimer SQL the Data Provider for ODBC that ships with .NET framework 1.1 can be used. The base classes for working with the Data Provider for ODBC are OdbcConnection, OdbcCommand, OdbcDataReader and OdbcTransaction. They are all found in the System.Data.Odbc namespace.

When you are programming with ADO.NET you work directly towards the Data Provider, so if you switch to another target database you must change your code and recompile. The different providers have the same functionality so the change is rather small, but it is still a major runtime limitation.

Before you can access the database you must create a connection to it. When working with the Data Provider for ODBC we use the OdbcConnection class to create a connection to the database. A connection string is used to tell the system which database to access. The string has the format:
"DSN=odbc_datasource_name;UID=database_ident;PWD=password"

You can pass the connection string through the constructor for OdbcConnection or you can set the ConnectionString property on an already created object.

When the object is created, the connection can be opened with the Open() method. When you are done you close it with the Close() method. A simple example might illustrate this (in C#):
using System;
using System.Data.Odbc;
. . .
OdbcConnection con =
new OdbcConnection("DSN=store_db;UID=BOOK_STORE;PWD=store");
con.Open();
//Do your database stuff
con.Close();

As you can see, this really doesn't do anything. It only connects to the database and then disconnects.


Executing commands

When you want to pass SQL statements to the database for execution you use the OdbcCommand class. In contrast to JDBC, ADO.NET only lets you have one type of command object. This object can be used for direct execution, prepared execution and for calling stored procedures.

Before you can use an OdbcConnection it has to be associated with a connection. This can be done either with the constructor for OdbcCommand or by using the CreateCommand() on the connection to create it. The SQL to execute can be provided in the constructor as well, or by setting the CommandText property on the command.

When the command is created we can call ExecuteNonQuery(), ExecuteScalar() or ExecuteReader():
  • ExecuteNonQuery is used to execute a statement that doesn't return any result, like an UPDATE or INSERT. The number of affected rows is returned.

  • ExecuteScalar() is used when only a simple result is expected, for example a statement like SELECT COUNT(*). It returns the first column of the first row and ignores the rest.

  • ExecuteReader() is used to execute a statement that returns a result. We will take a closer look on that later on.

A simple example that inserts a row in the CATEGORIES table of the example database illustrates how to use the ExecuteNonQuery() command:
string sql =
"INSERT INTO CATEGORIES(CATEGORY_ID, CATEGORY) VALUES(12,'A category')";
OdbcCommand command;
command = new OdbcCommand(sql, con);
//An alternative approach
//command = con.CreateCommand();
//command.CommandText = sql;
//command.Connection = con;
command.ExecuteNonQuery();

As you can see, the above statement is using static SQL in a non-prepared statement.

Using parameters via a prepared statement is a convenient way to supply data for INSERT, and it can really boost performance since it reduces the number of SQL compilations. After a statement is prepared it can be executed several times without compilation.

To set parameters for an OdbcCommand, simply add them to the Parameters collection. If you want to be able to control what datatype to use, create the OdbcParameter explicitly. Otherwise, you can simply add the value with a name identifying the parameter marker to the collection prior to Prepare(). The same example as above can look as follows:
string sql =
"INSERT INTO CATEGORIES(CATEGORY_ID, CATEGORY) VALUES(:id, :cat)";
OdbcCommand command = new OdbcCommand(sql, con);
OdbcParameter idParam = new OdbcParameter("id", OdbcType.Int);
idParam.Value = 12;
command.Parameters.Add(idParam);
command.Parameters.Add("cat", "A category");
command.Prepare();
command.ExecuteNonQuery();



Error handling

When working with databases, error handling is essential for proper application behaviour. In the above examples we have skipped that part completely. In ADO.NET, error handling is carried out using so called try-catch-statements.

Basically all database operations can throw an OdbcException that contains information about what went wrong. An error situation can cause several error messages to be generated. The simplest way to present the error is to show the OdbcException.Message, but that way we only get the first error. Besides the fact that we can have several errors in one OdbcException we can retrieve more error information if we use the OdbcException.Errors collection to get all the underlying ODBC errors.

A simple example that prints information about all errors we have in an exception is shown below:
try
{
con = new OdbcConnection("...");
con.Open();
. . .
con.Close();
}
catch(OdbcException oe)
{
foreach(OdbcError oErr in oe.Errors)
{
Console.WriteLine("Message: " + oErr.Message);
Console.WriteLine("Native error: " + oErr.NativeError);
Console.WriteLine("SQLState: " + oErr.SQLState);
Console.WriteLine("Source: " + oErr.Source);
}
}


Warning handling

To handle SQL Warnings we have to write an event handler for OdbcInfoMessageEventHandler. To do this, simply write a method with the same signature as OdbcInfoMessageEventHandler and couple it to the connection. For example, in the general error handling class ExampleErrorHelper we can write the method HandleInfoEvents as:
public class ExampleErrorHelper
{
public void HandleInfoEvents(object sender, OdbcInfoMessageEventArgs e)
{
Console.WriteLine("An event was thrown")
foreach(OdbcError oEvent in e.Errors)
{
Console.WriteLine("Errors: " + oEvent.Message);
Console.WriteLine("SQLState: " + oEvent.SQLState);
Console.WriteLine("Native: " + oEvent.NativeError);
}
}
// Place other error handling stuff here...
}

The method is then coupled to the connection as:

. . . 
ExampleErrorHelper eeh = new ExampleErrorHelper();
con = new OdbcConnection("...");
con.InfoMessage += new OdbcInfoMessageEventHandler(eeh.HandleInfoEvents);

This way, the HandleInfoEvents is called as soon as a warning is issued and the warning is printed.


Fetching the result

When we want to fetch the result of a query we use the OdbcDataReader class. OdbcDataReader is a fast, read only and forward only representation of the result and we get it by calling ExecuteReader() on a command. When we have the OdbcDataReader we can iterate through it using a while loop in conjunction with the Read() method:
. . . 
OdbcDataReader reader = command.ExecuteReader();
Console.WriteLine("ISBN\tAuthor\tTitle");
while(reader.Read())
{
Console.Write(reader.GetString(0) + "\t");
Console.Write(reader.GetString(1) + "\t");
Console.WriteLine(reader.GetString(2));
}
reader.Close();

As you can see we iterate throw the OdbcDataReader by calling reader.Read() which moves the cursor one row forward. The reader.GetString(0) is used to get the first column and so forth. There are GetXXX() methods for most datatypes, but there is no equivalent method to JDBC’s getString("columnname") - only the index can be used which is a pity. This way we depend on the column order in the SELECT statement.


Transactions

So far we haven't used explicit transaction handling and therefore auto-commit has been enabled. This means that all commands have been executed in their own transaction, and that might not be what we want. Fortunately, transaction handling is quite easy and straightforward in ADO.NET using the OdbcTransaction class.

A transaction is started by calling the OdbcConnection.BeginTransaction(isolationlevel) that returns an instance of OdbcTransaction. The different isolation levels are available in System.Data.IsolationLevel. For example, System.Data.IsolationLevel.Serializable can be used.

All SQL statements that we want to be part of the transaction have to be associated with the transaction object. This can be done by passing the transaction through the constructor of the OdbcCommand or by setting the Transaction property. When we are done with the work we simply call OdbcTransaction.Commit() or OdbcTransaction.Rollback() to commit or roll back the transaction.
try
{
OdbcConnection con = new OdbcConnection("...");
con.Open();

//Start the transaction
OdbcTransaction trans =
con.BeginTransaction(System.Data.IsolationLevel.Serializable);
OdbcCommand command = new OdbcCommand(sql, con, trans);

//An alternative but equivalent approach
OdbcCommand command2= new OdbcCommand(sql2, con);
command2.Transaction=trans;

//Execute and commit
command.ExecuteNonQuery();
command2.ExecuteNonQuery();
trans.Commit();
con.Close();
}
catch(OdbcException oe)
{
//Show errors
try
{
trans.Rollback();
}
catch(OdbcException oe2)
{
//Show errors
}
}


As you can see, we simply commit when we are done and if an OdbcException is thrown we do a roll back. Note that the trans.RollBack() method can throw an OdbcEvent itself and is therefore surrounded by its own try-catch.


DataSet and DataAdapter

As we have shown above, working with ADO.NET directly connected to the database (the connected model) is quite similar to how we work in JDBC and ODBC. The DataSet and DataAdapter in ADO.NET are used to work in an entirely different way (the disconnected model).

DataSet can be thought of as an in-memory database, which we can be populated from a database or be created directly in the program. The internal format of the DataSet is XML, which enables for openness between different parts of the system. For example, a DataSet can be sent between components, XML can be sent to a DataSet to update it and data can easily be received in XML format.

The bridge between the DataSet and database is the DataAdapter, in our case an OdbcDataAdapter. A DataAdapter is a representation of a connection to the database and the SQL statements used to get and update data. The DataAdapter is used both for filling the DataSet and to update the database.

When you use this method of programming you never open or close connections of your own. You simply create a DataAdapter, supply it with a connection context and at least a SELECT statement. The DataAdapter is then administering the connection for us. When we fill a DataSet, the connection is made, the data is fetched and then the connection is closed immediately.

DataSets are useful when we want to send data between components, when we want to get the data in XML format and when we are developing GUIs. When developing GUIs there are several controls in the .NET framework that can be directly connected to a DataSet. With this model we can show a DataGrid connected to the database with a few lines of code.

A simple example that uses a DataSet to print the data in XML format is shown below:
OdbcConnection con = new OdbcConnection("...");
string selectSql = "SELECT CATEGORY_ID, CATEGORY FROM CATEGORIES";
da = new OdbcDataAdapter(selectSql, con);
ds = new DataSet("Categories");
da.Fill(ds, "Categoryitem");
Console.WriteLine(ds.GetXml());


As you can see above, we never open the connection ourselves. The output of the example is:
<Categories>
<Categoryitem>
<CATEGORY_ID>1</CATEGORY_ID>
<CATEGORY>Music</CATEGORY>
</Categoryitem>
<Categoryitem>
<CATEGORY_ID>2</CATEGORY_ID>
<CATEGORY>Books</CATEGORY>
</Categoryitem>
<Categoryitem>
<CATEGORY_ID>3</CATEGORY_ID>
<CATEGORY>Video</CATEGORY>
</Categoryitem>
<Categoryitem>
<CATEGORY_ID>4</CATEGORY_ID>
<CATEGORY>d</CATEGORY>
</Categoryitem>
</Categories>


Conclusion

As we have shown in this article, ADO.NET programming can be really simple and yet powerful. The connected model of programming with ADO.NET is quite similar JDBC and ODBC and the disconnected model, using DataAdapters and DataSets, is a new way of thinking. Right now there is no equivalent technique available in JDBC, but the RowSet specification will bring a similar functionality to the Java world.

The great drawback with ADO.NET is that you have to state in your program which Data Provider to use and therefore have to change the code and recompile when switching database. This way it is impossible to write portable and open applications. However, this issue can be resolved by using an ADO.NET Provider Manager, like the Mimer Provider Manager (MPM), that lets you develop database independent applications, just like in JDBC.

Links

The complete example used in this article can be found in AdoExamples.zip.

The Mimer Provider Manager (MPM) home page is found at http://developer.mimer.com/mpm).

Information on ADO.NET from Microsoft can be found at http://msdn.microsoft.com/library/en-us/cpguide/html/cpconaccessingdatawithadonet.asp.

Last updated: 2009-10-12

 

Powered by Mimer SQL

Powered by Mimer SQL