.NET connecting to different databases…a request

Your wish is my command :) A request was made for a quick primer on how to get .NET connecting to different databases.

Whilst there are a multitude of different databases almost all will allow you to connect through one of the following methods supported by .NET: ODBC and OLE DB. OLE DB can often provide better performance because it has less work to do to get your requests to and from your database and offers higher level data access interfaces, making it more suitable to non-SQL and unstructured data sources.

Many database manufacturers (and 3rd parties) also produce native ADO.NET interfaces to the database, which again, can increase performance.

So first see if your Database provides native ADO.NET support, if not try OLE DB support, and if all else fails, fall back to using ODBC :)

All ADO.NET data providers conform to a common set of interfaces providing a Connector, a DataReader, and a DataAdapter. There are more components, but let’s concentrate on these for the time being.

Let’s take MySQL as an example. There are 3rd party data providers, which you can use however, I’ll show how to connect to the database through OLE DB:

The connection string to MySQL through OLE DB takes the following form:

string connectionString = "Provider=MySQLProv;Data Source=mydb;User Id=UserName;Password=password;";

Simply fill in your database information to create your own connection string which you can use with the OleDbConnection class to create a connection to your database:


OleDbConnection conn = new OleDbConnection(connectionString);

We can now either choose to use a DataSet (executes a SQL query to your database, retrieves all results before returning, and disconnects from the database), or a DataReader (executes a SQL query, and if results are available, retrieves on your command).

I’ll outline the code for both:

Using a DataSet:


OleDbDataAdapter da = new OleDbDataAdapter("select id from foo",conn);
DataSet ds = new DataSet();
int i = adapter.Fill(ds); // i will contain the number of results

foreach (DataRow dr in ds.Tables[0].Rows)
{
Console.WriteLine("id: "+dr[0].ToString());
}

You can now use the DataSet to query your results.

Using a DataReader:


OleDbCommand comm = new OleDbCommand("select id from foo",conn);
conn.Open();
OleDbDataReader reader;
reader = comm.ExecuteReader();

while (myReader.Read()) {
Console.WriteLine("id: "+reader.GetInt32(0));
}
// Close when done reading.
reader.Close();
// Close the connection.
conn.Close();
}

As you can see, it is only the connection string which selects your database. Connectionstrings.com is a great resource for finding the connection string for your particular database.

For ODBC, simply replace any references to “OleDb” in the above example to “Odbc”. All share the same interfaces :) You might also need to download the Odbc ADO.NET provider from Microsoft depending on your .NET and VS.NET edition (you can find this here).

Well, I hope this clears matters up somewhat. Give it a try, and as always, if you have anything to add, just add a comment :)