C# OdbcConnection

SQL (Structured Query Language)

OdbcConnection is used with MySQL. It often leads to syntax errors. The connection string does not allow quotes around the values. With some care this SQL database object is effective.

This C# tutorial uses the OdbcConnection type. It shows OdbcCommand and OdbcDataReader.

Introduction

Here we explore the OdbcConnection objects in the C# language and .NET Framework. After I introduce the basics, I will break down other details, such as how to add the code and actually use the OdbcConnection.

Note

The acronym's definition: The acronym ODBC stands for open database connectivity. The "open" refers to the platform status, not the connection state. It took me a long time to figure out what all those letters stand for.

When to use ODBC: These connections work with more than one SQL server, so you can use ODBC in many different scenarios. The scenario I will outline in this article is using ODBC connections in an ASP.NET application that accesses a MySQL database.

Performance of the connection: Because of the compiled nature of C#, and the performance of MySQL, the applications you build with this combination have the potential to be very fast and responsive.

Connection string

Usually, if you are using a third-party web host, they will provide you with a sample connection string. This is useful, but you need to be careful. There are some tricks. On my web apps, I have connection strings that look like the next block.

Connection string DRIVER={MySQL ODBC 3.51 Driver}; SERVER=p50mysq5555.secureserver.net; PORT=3306; DATABASE=OkieData; USER=SamAllen; PASSWORD=CutiePie; OPTION=0;

Driver Specify the driver as MySQL ODBC 3.51 Driver in curly brackets.

Quotes around driver section Don't put quotes around your driver.

Database server Put your server URL in there. You will have to get this from your web host.

Attribute values The database's name is OkieData. The user name is SamAllen. The password is CutiePie.

All quotes If you read nothing else here read this: You can't put quotes around any of the values.

My experience. I spent a hectic half-hour or more trying to log in to the MySQL database with this configuration with quotes around my password. It didn't work. So don't use those quotes.

ASP.NET web programming framework

Web.config

It is best practice, although not required, to put your connection string in Web.config in your ASP.NET project. Here are the lines I used. You will have to find the appropriate blocks in the XML config file yourself. In this next XML element example, I use the connection string name of WhateverName.

Web.config [XML]

<!-- This XML should be put in Web.config -->

<connectionstrings>
    <add name="WhateverName" connectionString="Exact string shown above"/>
</connectionstrings>

Using statements

Your page will have a code-behind file. At the top are your using statements. Add these two using statements at the top. The first adds the database stuff, and the second allows you to access your connection string.

Using statements [C#]

using System.Data.Odbc;
using System.Web.Configuration;

Create OdbcConnection

Here we see that you can create an OdbcConnection by combining it with the using statement. The next code block gets our special connection string from the Web.config file. The contents of that string are shown near the start of this article. In C#, you want to use the "using" blocks.

Fragment that uses ConnectionStrings [C#]

// Try to connect to the database based on our stored connection string.
string conString = WebConfigurationManager.
    ConnectionStrings["WhateverName"].ConnectionString;
using (OdbcConnection con = new OdbcConnection(conString))
{
    con.Open();
    // We are now connected. Now we can use OdbcCommand objects
    // to actually accomplish things.
}

Description. The OdbcConnection with the identifier "con" is a new connection to the database. These are automatically pooled and shared. That code was written by Microsoft programmers very skilled in these things. So I won't try to improve upon that work.

Read MySQL database

In this next code block, I will declare a new OdbcCommand object, then add a parameter to the command object, and then read in data from the database. Note the question mark in the command text.

Fragment that uses OdbcDataReader [C#]

using (OdbcCommand com = new OdbcCommand(
    "SELECT ColumnWord FROM OkieTable WHERE MagicKey = ?", con))
{
    com.Parameters.AddWithValue("@var", paramWord);

    using (OdbcDataReader reader = com.ExecuteReader())
    {
	while (reader.Read())
	{
	    string word = reader.GetString(0);
	    // Word is from the database. Do something with it.
	}
    }
}
Question and answer

Description. The OdbcCommand takes the connection object as the second parameter. The paramWord string is added to the command. It is named @var but that doesn't matter. The parameter is added in the first question mark (?). It looks through the table and find the rows where the MagicKey column is equal to paramWord.

Summary

.NET Framework information

Here we saw ways you can fix your bugs with OdbcConnection. This can be applied to any ADO.NET provider, from SqlConnection objects to SqlCeConnections and SQLiteConnections. Use the material here as a launching point for learning more about ADO.NET.

Data
.NET