Adding PostgreSQL db to Asp.Net web project on Windows 7

Last night I was trying to connect an existing Asp MVC 3 app to use an PostgreSQL database.  It is using nHibernate, so I'm still going to have to determine if I need to tweak anything there, but for now just wanted to connect properly.

After installing Postgre, I needed to install the ODBC connector in order to establish a connection in my .Net application.  There was a little trick, when I used the ODBC configurator from the Start menu, I didn't see the newly installed ODBC adapter!  WTF?  Turns out, since I was running a 64 bit version of my OS, I had to run c:\windows\syswow64\odbcad32.exe in order to set my new ODBC driver.  There is also another way...

Install NpgSQL adapter, I used the latest .Net 4.0 install.  After referencing the Npg dll in your project (plus an associated Mono dll), you can simply use their wrappers in order to connect, here you can get the gist:

using NpgSQL;
var connstring = String.Format("Server=localhost;Port=5432;User Id=;Password=xxx; Database=DbName;");
var conn = new NpgsqlConnection(connstring);
conn.Open();
const string sql = "SELECT LastName FROM Users ORDER BY LastName LIMIT 10";
var da = new NpgsqlDataAdapter(sql, conn);
var ds = new DataSet();
da.Fill(ds);

Angular.js and setting up IIS to recognize JSON

Ok, so I've been messing around with Angular.js, so far, a great framework for client-side HTML manipulation.  Advantages include separation of concern (with an MVC architecture), and really good testing ease using Jasmine, taking advantage of Dependency injection, and awesome databinding features.

With regards to the Dependency injection, there is a $xhr service (it's a service used to GET data), but when I attempted to use it with IIS 7, no go.  Checking IIS under the hood, I found no MIME mapping to .json files, so after some digging on the web, here is how to set it up:

Configure IIS to recognize JSON:

IIS7 configuration to support JSON:

Using IIS manager: clock on IIS server (the main node on the left - where you see the machine name)

1. Add ‘json’ MIME Type

• Double click on ‘MIME Types’ icon
• Click ‘Add…’ link (under Actions section on the right side)
• In ‘Add MIME Type’ window type:

File name extension: .json
MIME type: application/json

• Click OK, You should see the .json MIME Type added to the list of the MIME Types list.

2. Add Script Map Handler for ‘json’ MIME Type

• Double click on ‘Handler Mappings’ icon
• Click on ‘Add Script Map…’ link (under Actions section on the right side)
• In ‘Add Script Map’ window type:

Request path: *.json
Executable: C:\Windows\System32\inetsrv\asp.dll
Name: JSON

• Click OK (Message box pops up with a message: Do you want to allow this ISAPI extension?...etc ) Click Yes, You should see the .json extension added to the list of the Handler Mappings list