Dot Net Perls

Problem

Learn more about using OdbcConnection in our website or any C# project. We may have a problem with the syntax of our OdbcConnection. We might have a problem with our connection string, or a parameter to the SQL query in C#. Our goal is to learn some small tricks with this ADO.NET object and related objects.

Solution

Here we explore the OdbcConnection objects in C# and .NET. Let me review some important points about Odbc database connections in C# .NET. After I introduce the basics, I will break down other details, such as how to add the code and actually use the OdbcConnection.

• What it stands for
  The acronym Odbc stands for open database connection. 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
  Odbc 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
  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.

What parts are in the ConnectionString?

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. After I show the connection string, I will emphasize some points.

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

As you might guess, I spent a hectic half-hour or more (probably more like 1-2 hours) 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.

How do I use 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.

<!-- 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--it can be named anything, but will end in .aspx.cs. At the top are your using statements. Let's add a couple using directives at the top. The first one adds the open database connection database stuff, and the second allows you to access your Web.config connection string in the recommended way.

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

How do I make a OdbcConnection object?

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.

// 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.
}

OdbcConnection con is a new connection to the database. These are automatically pooled and shared by the .NET runtime. The code that does that was written by programmers far more skilled in these things than I am. So I won't try to improve upon that work--it is fast and about as good as it gets with this design.

How do I read data from the 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. After the code block, I will list some important factors.

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

• The above code makes a new command
  The OdbcCommand takes the connection object as the second parameter.
• Parameter is added
  The paramWord string is added to the command. It is named @var but that doesn't matter. The parameter is added in the first ?.
• Data is read in
  It looks through the table and find the rows where the MagicKey column is equal to paramWord.

Conclusion

Mastering OdbcCommand and family has utility far greater than this one scenario--it can be applied to any ADO.NET object, from SqlConnection objects to SqlCeConnection objects and SQLiteConnection objects. Use the material here as a launching point for learning more about ADO.NET.

Ads by The Lounge