I’ve worked with many database architectures over the years, and written many apps in a variety of languages across them. Recently, I finally got the opportunity to turn my attention to PostgreSQL. This database system remains one of the most popular and is about as robust as you can get for free (save maybe for Microsoft’s SQL Server Express). One thing I’ve learned to appreciate about PostgreSQL is how lightweight it is.
While PHP or Python may be the “go to” languages working with PostgreSQL, we .NET developers can have it in our apps, too. There are a number of commercial drivers and APIs you can purchase but for the sake of simplicity and just simple trying it out—even for simpler, non-commercial apps—you can use ODBC. I know what you’re probably thinking. WHAT?! ODBC?! But this is 2015! Don’t get your panties in a bunch, just read on.
Here are the steps to get up and running quickly:
- Download and install PostgreSQL – http://postgresql.org
- Download and install the latest sql_odbc_0n_0n .msi* – http://www.postgresql.org/ftp/odbc/versions/msi/
- From the Run window, type odbcad32.exe and click OK, then click the System DSN tab. Note you have two new PostgreSQL drivers available:
- Select the ANSI(x64) driver and click Finish. The driver configuration window should appear:
- At this point, you have the option of specifying all the appropriate information in the ODBC setup. My personal preference is to save only the Data Source name here (which also acts as the DSN) and the port. Note: This is the PostgreSQL default. You can also exclude it in the setup and include in the connection string as shown below.
- Finally, create a console project in Visual Studio to test the connection. The below code demonstrates a test method.
You should see the following results if all’s well:
* As an alternative to downloading and installing the ODBC drivers manually, some PostgreSQL installations include a program called Application Stack Builder. This includes common tools and languages, as well as drivers for node, Java, and ODBC 32/64-bit. You can use Stack Builder to install the drivers, as shown.
And that’s it. You can now write .NET apps against PostgreSQL. Happy coding!