Tuesday, February 19, 2013

PostgreSQL Sample Web Application

The PosgreSQL open-source database management system offers several sample databases. One of these samples is Pagila, a conversion of the sakila sample database from MySQL. Let’s use Code On Time generator to create a web app straight from this database.

Installing PostgreSQL and Pagila

First, download the correct version of PostgreSQL for your computer. Run the installer and follow the instructions to complete the installation.

Next, download the Pagila sample database. Follow the README file instructions to install the database.

Registering NpgSQL .NET Data Provider in GAC

We will be using NpgSQL .NET Data Provider to connect to the PostgreSQL database engine. This data provider is designed by the vender to be deployed alongside the binary files of a .NET web application to eliminate any need to install client software. Code On Time generator and produced web apps use a provider-independent model when interacting with the application database. This model of database access requires registration of native data providers in the Global Assembly Cache (GAC). Follow the instructions below to register the Npgsql data provider in GAC.

Download the NpgSQL .NET 4.0 Provider for PostgreSQL. Extract the contents of the zip file.

Run cmd.exe as administrator.

Running the command prompt as administrator.

Use the following command to navigate to the location of “gacutil.exe” tool. The tool will be present if Visual Studio is installed. Otherwise, install the Microsoft SDK for .NET Framework 4.0. The location and version of the SDK may be different on your computer.

cd C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools

Next, run the following line to register the data provider in the Global Assembly Cache. Make sure to use the correct path to the extracted “Npgsql.dll” file.

gacutil.exe /i "[Path]\Npgsql.dll"

If the installation was successful, you will see the text “Assembly successfully added to the cache”.

Installation of Npqsql to the global assembly cache was successful.

Repeat the process to install the second required DLL using the following line.

gacutil.exe /i "[Path]\Mono.Security.dll"

The next step will be to add a reference in the machine.config file for both 32-bit and 64-bit versions of Microsoft.NET.

You must update both configuration files on 64-bit machines.

Run Visual Studio as an administrator.

Running Visual Studio as administrator.

On the toolbar, press File | Open | File.

Opening a file in Visual Studio 2012.

Open the 32-bit machine.config file, located at C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\machine.config.

Within the XML file, locate the configuration/system.data/DbProviderFactories section. Add the following line inside the section. Note that your data provider version may be different.

<add name="Npgsql Data Provider" invariant="Npgsql"  support="FF" description=".Net Framework Data Provider for Postgresql Server" type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.12.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
Save the file. 

Open the 64-bit machine.config file, located at C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config. Add the same configuration element inside configuration/system.data/DbProviderFactories.

Save the file. The provider is now configured.

Creating a Web Application

Start Code On Time web application generator. Click on Create new web application. Select Web Site Factory from the list.

Creating a Web Site Factory project.

Assign a name of “PagilaDemo”, select the programming language of your choice, and press Create.

Assigning a name to the project.

Preserve the default Namespace and Framework, and press Next.

On the Database Connection page, select “.Net Framework Data Provider for Posgresql Server” for Data Provider. Then, click on the “…” button next to Connection String field.

Specifying Npgsql Provider as the data provider and activating the connection string configuration screen.

Enter the connection string properties and press Test to confirm.

Successful test of PostgreSQL connection string.

Press OK to use the configured connection string. Press Next until you reach the Reports screen. Check the box to enable reporting.

Enabling reports in the web application.

Press Next until you reach the Theme page. Select “Yoshi” from the list.

Selecting 'Yoshi' theme on the Theme page of the Project Wizard.

Hold down Shift key and press Next to skip to the Summary page. Press Generate and wait for the web app to load in the default browser.

Pagila web app running in Internet Explorer 9.

No comments:

You can find more about Code OnTime Generator, Data Aquarium Framework, and other great products here.


© 2010 Code OnTime LLC. Intelligent code generation software for ASP.NET. Visit us at http://codeontime.com