Large (Binary) Fields using ODBC in C#

Introduction

Background

This article is about storing large values inside BLOB, Text and Memo fields using the C# programming language. The code provided should work under Mono but is also applicable to the toolchain provided by other vendors like Microsoft's Visual Studio .NET.

When storing data in databases using standard SQL statements using the ODBC classes, you will notice you can't actually store string, character or binary values which are larger than 255 bytes. This seemingly arbitrarily one byte limit, just below 256 characters can be alleviated using an OdbcParameterCollection instance.

I'll try to guide you through the process of writing correct code for handling large field values. First, we'll look at some general code required to set up an ODBC connection and after that I'll provide some code for both storing and retrieving values from the database.

Prerequisites

Be sure to have at least one usable ODBC data provider which is accessible using a known DSN, short for "data source name".

Making the Connection

Importing required classes

To start the ODBC adventure, you'll need to add some imports at the top of your class file. The ones you'll definitely want to have in there are:

System.Data
System.Data.Odbc

The first one provides generic classes for database access and utilization, the second one is specific for the ODBC method of accessing databases. To add these imports, write the following two lines at the top of your class source file:

using System.Data; using System.Data.Odbc;

That's all regarding the importing of classes. Let's look at the classes we'll be using.

Who's who and what do they do?

The relevant ODBC classes for this article are (I'm assuming you are familiar with a lot of the C# / CLR base classes):

OdbcConnection, for creating the ODBC connection and maintaining it once set up
OdbcConnectionStringBuilder, for creating usable ODBC connection strings which point to a database
OdbcCommand, a model of a (SQL) statement that can be executed on an OdbcConnection
OdbcParameterCollection, a collection of parameters which are used for determining field values in an OdbcCommand
OdbcDataReader, for retrieving field values from an executed query
OdbcType, wrapper class for ODBC data types

The first thing to do when making a connection is getting a usable instance of OdbcConnection. If you do not already have a DSN which points to a database, create one now. The following example code initializes an ODBC connection for a given DSN:

using System.Data;
using System.Data.Odbc;

namespace OdbcExample {
    public class OdbcExample {
        private static OdbcConnection Connection;

        public static void Main() {
            string dsn = "MyDSNName";

            InitializeConnection(dsn);
        }

        private static void InitializeConnection(string dsn) {
            // build the required connection string
            OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder();
            builder.Dsn = dsn;

            // create the connection and open it
            Connection = new OdbcConnection(builder.ConnectionString);
            Connection.Open();
        }
    }
}

Save this file as "OdbcExample.cs" or something to your liking.

What's happening?

The Main method simply calls InitializeConnection, which in turn builds an ODBC connection string for connecting to the given DSN and opens the connection. At this point, we're set to go store in and retrieve values from the database.

Storing Large Values

Creating a simple table

First of all, create a very basic table in which we'll be able to story large binary objects (BLOBs), you could use a TEXT or MEMO field for this once, since it often doesn't matter whether or not you actually story binary information in them.

So, create a table called "blobtest" and add the following two fields:

id, an auto increasing integer field (SERIAL in PostgreSQL)
value, using datatype BLOB, TEXT or MEMO

Commit these changes to the database, and now we'll write a procedure that will store both long text and binary data in the database.

Storing long text

The following method will store a long text in the database, add this method to your source file and add a call to it in the Main function:

private static void StoreLongText() {
    // single line of text to concatenate
    string singleLine = "This will become a long text in the database. ";

    // build a long string using the single line
    string longText = singleLine;
    for(int i = 0; i < 1024; i++) {
        longText += singleLine;
    }

    // use question-marks as placeholders for parameters
    string insertQuery = "INSERT INTO blobtest(value) VALUES(?)";
    
    // create an OdbcCommand and get its parameters
    OdbcCommand command = new OdbcCommand(insertQuery);
    OdbcParameterCollection parameters = command.Parameters;

    // add "value" column to parameters, store the long string in it
    parameters.Add("value", OdbcType.Text);
    parameters["value"].Value = longText;

    // set the command's connection and execute the query
    command.Connection = Connection;
    command.ExecuteNonQuery();
}

Explanation

First of all, we create a long text string which will definitely span across the 255 character limit of normal SQL statements, this long string is stored in the variable longText. After that a simple INSERT statement is created using the SQL language, using a question-mark as a placeholder for the long text value.

Using the SQL statement, an OdbcCommand instance is created for it. Because we need to fill in the gap of the question-mark, we use the OdbcCommand's OdbcParameterCollection to specify a column called "value" of the OdbcType "Text". Then we set the value of this column to the longText variable.

The last two lines set the connection for the command and execute it as a query without a result (ExecuteNonQuery).

Storing a large binary value

Storing a large binary value is almost the same as the example above, but we'll now use a byte-array filled with zeroes to store in the database. Take a look at the following method, place it in your source file and add a call to it in the Main method of your class:

private static void StoreLongBinary() {
    // large 16K empty byte array to store
    byte[] bytes = new byte[16384];

    // use question-marks as placeholders for parameters
    string insertQuery = "INSERT INTO blobtest(value) VALUES(?)";
    
    // create an OdbcCommand and get its parameters
    OdbcCommand command = new OdbcCommand(insertQuery);
    OdbcParameterCollection parameters = command.Parameters;

    // add "value" column to parameters, store the long string in it
    parameters.Add("value", OdbcType.Image);
    parameters["value"].Value = bytes;

    // set the command's connection and execute the query
    command.Connection = Connection;
    command.ExecuteNonQuery();
}

So, what is different? The method name and the value to store (the variable called "bytes"), obviously. The only real difference is the OdbcType used: for binary data, use OdbcType.Image, even if your binary data doesn't resemble an actual image.

I hope you see that storing binary data isn't really that different from storing long text data. Now let's look at how to retrieve the data we've just stored.

Retrieving Large Values

Retrieving large text values

Try the following method for retrieving a large text value from the database, I'll explain it below:

private static void RetrieveLargeText() {
    // SQL query to retrieve the value
    string query = "SELECT value FROM blobtest WHERE id = 1";

    // create ODBC command, execute the query and get the reader for it
    OdbcCommand command = new OdbcCommand(query);
    command.Connection = Connection;
    OdbcDataReader reader = command.ExecuteReader();

    // check whether there is at least one record
    if(reader.Read()) {
        // matching record found, read first column as string instance
        string value = reader.GetString(0);
    }

    reader.Close();
}

That's pretty much all you'll need to do to read a large string from a field:

Create an SQL query to retrieve the desired records
Open an OdbcDataReader instance for the SQL query
Read through the records if there are any
Retrieve the desired field values using GetString

To retrieve large binary values, or BLOBs, you'll need to modify the code only slightly.

Retrieving large binary BLOB values

The following method looks a lot like the one above, but differs slightly at the bottom:

private static void RetrieveLargeText() {
    // SQL query to retrieve the value
    string query = "SELECT value FROM blobtest WHERE id = 2";

    // create ODBC command, execute the query and get the reader for it
    OdbcCommand command = new OdbcCommand(query);
    command.Connection = Connection;
    OdbcDataReader reader = command.ExecuteReader();

    // check whether there is at least one record
    if(reader.Read()) {
        // matching record found, read first column as string instance
        byte[] value = reader.GetValue(0);
    }

    reader.Close();
}

The difference is in the return type and the method used to retrieve the value from the OdbcDataReader, for binary data you should use GetValue which returns a byte array. That's all there really is to it.

Conclusion

Not much of a conclusion

In this article we looked at connection via ODBC, storing and retrieving large binary values in a database. The sample code should provide you with a good starting point for looking up how to code using ODBC in Mono / C#, I hope this information was helpful to you.

About this article

This article was written on both the 20th and 22nd of April, 2006 and added to the site on the 22nd.

Forever For Now

Menu

Search