Using Microsoft .NET with the PostgreSQL Database Server via ODBC

Introduction and getting it running

The Microsoft .net CLR (Common Language Runtime) provides several ways of connecting to databases. This article deals exclusively with using ODBC to connect with PostgreSQL using .net languages.
[Note: For those that don't know, ODBC stands for 'Open Database Connectivity'.]

Before you read all of this article you may want to have a look at the 'known bugs' section at the end of this document.

The first important point is that, in the current version of the .net Framework SDK (Version 1.0.3617.0), there is no in built ODBC support. You need to download and install an extension to .net called, appropriately, odbc.net. This can be downloaded from msdn.microsoft.com

The downloaded file is called 'odbc_net.msi'.

NOTE: A .msi file is a MicroSoft Installer file, these are runnable if your system has Microsoft Installer installed (this comes preinstalled with Win 2000 / XP and most recent Windows versions and can be downloaded from Microsoft if you have an older OS).

'odbc_net.msi' will provide you with 2 important files:

• Microsoft.Data.Odbc.dll - This is a dll that will need to be linked to your programs at compile time.
  
• Odbcref.chm - This is the documentation for the classes in the dll.

The next file you will need to download and install, if you don't have it already, is the PostgreSQL ODBC driver. The following site has all the information you could possibly want about the PostgreSQL ODBC driver.

https://odbc.postgresql.org/

NOTE (possibly obsolete): if you have problems with described above driver you may download this PostgreSQL ODBC driver it updates frequently, latest version is from 15/05/2015. Supports Windows, Linux, Mac OS x, both 32-bit and 64-bit.

Once you've installed the ODBC driver you will need to add a new user data source. This is achieved by going to 'Control Panel', 'Admin. Tools', 'Data Sources (ODBC)'. Then selecting 'Add User DSN'.
[NOTE: These names and locations may vary slightly with different Win OS's.]

Select the PostgreSQL driver, and fill in your server and database details. You must also specify a unique DSN name; in Windows 2000 this field is, confusingly, labelled 'Data Source' in the data entry dialog box and not 'Data Source Name' which would be more appropriate. It is this name that you will later use in your programs to specify which database connection you want to use. Of course you can have as many User DSN entries as you want for different databases, servers and users.

You are now ready to write .net programs that connect to PostgreSQL.

odbc.net provides you with a suite of classes such as OdbcConnection, OdbcCommand and OdbcDataReader; which will allow you to communicate with the PostgreSQL server. Read the documentation, lots of classes are available.

Here is a small code sample, written in C#, that shows you how to open an ODBC connection and query a table:

// Create the ODBC connection using the unique name you specified when 
// creating your DSN. If desired you may input less information at the
// DSN entry stage and put more in the "DSN=" line below.
OdbcConnection connection = new OdbcConnection("DSN=PostgreSQL");
// "DSN=MyDSN;UID=Admin;PWD=Test" (UID = User name, PWD = password.)


// Open the ODBC connection to the PostgreSQL database and display
// the connection state (status).
connection.Open();
System.Console.WriteLine("State: " + connection.State.ToString());


// Create an ODBC SQL command that will be executed below. Any SQL 
// command that is valid with PostgreSQL is valid here (I think, 
// but am not 100 percent sure. Every SQL command I've tried works).
string query = "SELECT * FROM TESTTABLE";
OdbcCommand command = new OdbcCommand(query, connection);


// Execute the SQL command and return a reader for navigating the results.
OdbcDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);


// This loop will output the entire contents of the results, iterating
// through each row and through each field of the row.
while(reader.Read() == true) 
{
    Console.WriteLine("New Row:");
    for (int i = 0; i < reader.FieldCount; i++)
    {
        Console.WriteLine(reader.GetString(i));
    }
}

// Close the reader and connection (commands are not closed).
reader.Close();
connection.Close();

You will need to specify the use of the odbc.net dll in your program in the usual way, in C# this is done with the following line:

using Microsoft.Data.Odbc;

At compile time you will need to reference the odbc.net dll. A typical C# compile line would be something like:

csc /out:run.exe /r:Microsoft.Data.Odbc.dll TestDB.cs

Make sure Microsoft.Data.Odbc.dll is in your 'path', referenced with it's entire path, or in the 'compiling' directory.

My apologies for the examples being exclusively is C#, I have not used any other .net languages. But I am sure the usage of the classes is the same in other languages even if the notation is not. Known Bugs in odbc.net. Users of odbc.net have experienced problems when using various odbc drivers. The following bugs have been found when using odbc.net with the PostgreSQL ODBC driver.

1) When querying a table and the results include an empty string in one of the columns, if you try and read the value of the empty string you may get the following unhandled exception:


Unhandled Exception: Microsoft.Data.Odbc.OdbcException: NO_DATA
2) Similarly if a column contains the NULL value then the unhandled exception is this:


Unhandled Exception: System.InvalidCastException: Specified cast is not valid."

The problem lies in Microsoft's Data Access Components which are an installation requirement for .net and odbc.net. Microsoft have announced a fix that can be obtained from Microsoft Product Support Services but you need a support contract to obtain it. The fix will be included in the Data Access Components 2.7 SP1 (Service Pack 1), which will be freely available at some point (no date for release is available).

More information is available here; Question ID: Q319243

Post any questions you may have, that this article does not cover, to the pgsql-general mailing list or to the newsgroup. You may email me directly <mattstan@NOSPAM-blueyonder.co.uk> but I can't guarantee a quick response.