Postgres Succinctly^®
by Peter Shaw

CHAPTER 9

Using Postgres with .NET

So now we get to the fun part: using Postgres in a .NET application.

I'm not going to use any of the special functionality that Postgres has available in this example; it's going to be a quick and easy editing application that will edit MyTable that we created previously.

We'll start first with the ADO.NET version. Once that's working, we'll convert it to use Entity Framework version 5.

Both sample applications are available for download at https://bitbucket.org/syncfusion/postgres_succinctly as two separate projects, one for the plain ADO version, and the other for the Entity Framework version.

Note that I’ll be using Visual Studio 2010 for my examples here, but feel free to use whatever compiler or IDE you wish. The code I present should work under any C# compiler, including Mono. Npgsql (the .NET data interface for Postgres) is known to work well under Mono, but I can’t guarantee that the Entity Framework version will. Remember, I'm using a Windows 7 platform for this example.

Create a simple Windows Forms application

We'll start by firing up Visual Studio 2010, starting a new project from the File menu, and selecting a Windows Forms project that targets .NET 4.

Windows Forms project

Once you've set your path, project name, etc., click OK to create your project in the IDE.

Once your project is created, resize your main window to approximately 650 × 530 pixels, and then add a tab control and two normal buttons to the main form.

Your two buttons should be Cancel and Edit.

On the first tab control page, place a regular DataGridView and a third button marked Load Data. Name the first tab View Data. When you’re finished, your form should look something like this:

Application window

I've left the data grid at its defaults, (automatic columns, widths, etc.). We'll be populating this in code soon.

Once you've completed first tab, switch to the second and place a Table Layout Panel on it. Edit the layouts columns and rows to the following:

Column1 15%

Column2 85%

Row1 30 pixels

Row2 30 pixels

Row3 30 pixels

Row4 30 pixels

Row5 Autosize

Next, add a label to every left-hand cell in the table control and set the Dock property to Fill. Set the text alignment of each label to middle-right except for the bottom one, which should be top-right, and then give each label the following text:

Gid :

Name :

EMail :

Webpage :

Biography :

Continue by adding another label in the top right cell of the table layout (same row as Gid) and text boxes in the remaining cells.

The bottom cell's text box should be set to Multiline. Set the Dock property on all of them to Fill.

Once you've finished the table layout, add six buttons across the bottom of the tab page and label them from left to right as follows:

Create New

Insert

Update

Delete

Once you’re finished, your form should look something like the following:

Completed form

Once the interface is complete, we need to start adding code. First though, we need to add the NpgSql assembly using NuGet.

Right-click References, and then select Manage NuGet Packages. Ensure you’re searching online. Then in Search, type Npgsql. You should see the following:

Adding the Npgsql assembly

Click the Install button next to Npgsql to install it in your project.

Next, right-click on your project root and add two new folders, one called classes and one called entities. Note: You don't have to organize your project this way, but I always do as it makes for easier management. If you're at all curious about my approach, I have a 90-minute video about the subject available free on the LIDNUG YouTube page at http://www.youtube.com/user/lidnug.

In the Classes folder, create two new classes called ConnectionStrings.cs and Database.cs. In the Entities folder, create a class called MyTable.cs.

ConnectionStrings.cs is required to get the connection string from the app.config file as Npgsql does not have the ability to take a connection string name and load the real string from it. We use the ConnectionStrings class to get the string, and then pass that to the methods in DataBase.cs where all the functionality to talk to Postgres is.

The entity class should model your database table.

In our case the three classes appear as follows:

ConnectionStrings.cs

using System;

using System.Configuration;

namespace PostgresExample.Classes

{

public static class ConnectionStrings

{

public static string PgConnection

{

get

{

const string connectionStringName = "pgconnection";

if (string.IsNullOrEmpty(ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString))

{

throw new Exception("Connection string (" + connectionStringName + ") not defined in app.config!");

}

return ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;

}

Database.cs

using System.Collections.Generic;

using Npgsql;

using NpgsqlTypes;

using PostgresExample.Entities;

namespace PostgresExample.Classes

{

public class Database

{

public List<MyTable> GetAll()

{

List<MyTable> results = new List<MyTable>();

using (NpgsqlConnection connection = new NpgsqlConnection(ConnectionStrings.PgConnection))

{

connection.Open();

const string sql = "select * from mytable";

using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))

{

using (NpgsqlDataReader reader = command.ExecuteReader())

{

if (!reader.HasRows) return results;

while (reader.Read())

{

results.Add(new MyTable

{

Gid = (int)reader["gid"],

Name = (string)reader["name"],

Email = (string)reader["email"],

Webpage = (string)reader["webpage"],

Bio = (string)reader["bio"]

});

}

return results;

}

public MyTable GetByGid(int gid)

{

MyTable result;

using (NpgsqlConnection connection = new NpgsqlConnection(ConnectionStrings.PgConnection))

{

connection.Open();

const string sql = "select * from mytable where gid = :gid";

using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))

{

command.Parameters.Add(new NpgsqlParameter("gid", NpgsqlDbType.Integer));

command.Parameters["gid"].Value = gid;

using (NpgsqlDataReader reader = command.ExecuteReader())

{

if (!reader.HasRows) return null;

reader.Read();

result = new MyTable

{

Gid = (int)reader["gid"],

Name = (string)reader["name"],

Email = (string)reader["email"],

Webpage = (string)reader["webpage"],

Bio = (string)reader["bio"]

};

}

return result;

}

public void AddNew(MyTable entity)

{

using (NpgsqlConnection connection = new NpgsqlConnection(ConnectionStrings.PgConnection))

{

connection.Open();

const string sql = "insert into mytable(name, email, webpage, bio) values(:name,:email,:webpage,:bio)";

using(NpgsqlCommand command = new NpgsqlCommand(sql, connection))

{

command.Parameters.Add(new NpgsqlParameter("name", NpgsqlDbType.Varchar));

command.Parameters.Add(new NpgsqlParameter("email", NpgsqlDbType.Varchar));

command.Parameters.Add(new NpgsqlParameter("webpage", NpgsqlDbType.Varchar));

command.Parameters.Add(new NpgsqlParameter("bio", NpgsqlDbType.Varchar));

command.Parameters["name"].Value = entity.Name;

command.Parameters["email"].Value = entity.Email;

command.Parameters["webpage"].Value = entity.Webpage;

command.Parameters["bio"].Value = entity.Bio;

command.ExecuteNonQuery();

}

public void DeleteByGid(int gid)

{

using (NpgsqlConnection connection = new NpgsqlConnection(ConnectionStrings.PgConnection))

{

connection.Open();

const string sql = "delete from mytable where gid = :gid";

using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))

{

command.Parameters.Add(new NpgsqlParameter("gid", NpgsqlDbType.Integer));

command.Parameters["gid"].Value = gid;

command.ExecuteNonQuery();

}

public void Update(MyTable entity)

{

using (NpgsqlConnection connection = new NpgsqlConnection(ConnectionStrings.PgConnection))

{

connection.Open();

const string sql = "update mytable set name = :name, email = :email, webpage = :webpage, bio = :bio WHERE gid = :gid";

using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))

{

command.Parameters.Add(new NpgsqlParameter("name", NpgsqlDbType.Varchar));

command.Parameters.Add(new NpgsqlParameter("email", NpgsqlDbType.Varchar));

command.Parameters.Add(new NpgsqlParameter("webpage", NpgsqlDbType.Varchar));

command.Parameters.Add(new NpgsqlParameter("bio", NpgsqlDbType.Varchar));

command.Parameters.Add(new NpgsqlParameter("gid", NpgsqlDbType.Integer));

command.Parameters["name"].Value = entity.Name;

command.Parameters["email"].Value = entity.Email;

command.Parameters["webpage"].Value = entity.Webpage;

command.Parameters["bio"].Value = entity.Bio;

command.Parameters["gid"].Value = entity.Gid;

command.ExecuteNonQuery();

}

MyTable.cs

namespace PostgresExample.Entities

{

public class MyTable

{

public int Gid { get; set; }

public string Name { get; set; }

public string Email { get; set; }

public string Webpage { get; set; }

public string Bio { get; set; }

}

As you can see, the code is straightforward and relatively easy to understand.

If you get any missing reference errors when trying to compile, you'll need to use Add Reference Tools in Visual Studio to add a reference to System.Configuration. This is required to read the configuration string in ConnectionStrings.cs.

The key to opening a connection to Postgres lies with the using statement, which ensures the object is disposed of correctly.

using (NpgsqlConnection connection = new NpgsqlConnection(ConnectionStrings.PgConnection))

{

connection.Open();

}

Once you have the connection, you can create an Npgsql command object to allow you to execute standard SQL statements.

const string sql = "select * from mytable";

using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))

{

}

Again, we employ the using statement (since the object derives from IDisposable) to ensure that the object is disposed of correctly.

If you need to pass parameters into your SQL string, then you should use the parameterized functionality that exists in ADO.NET to protect against things like XSS and SQL injection attacks.

You can also use string.format or some other form of concatenation, but it is highly unadvisable to do so unless you’re completely sure your input data is clean.

command.Parameters.Add(new NpgsqlParameter("gid", NpgsqlDbType.Integer));

command.Parameters["gid"].Value = entity.Gid;

Where you specify the parameter name in the previous code sample, you would place : followed by the name in your SQL string (in this case :gid).

After all of that is set up, it's simply a case of calling the following if your SQL is not expected to return any data:

command.ExecuteNonQuery();

If your SQL is expected to return data, then call:

command.ExecuteReader();

The rest, such as working with data readers, is all standard ADO.NET code.

Once you have those three classes in place, you need to add the connection string to the app.config file in the application. The connection string should look something like the following:

<?xml version="1.0"?>

</startup>

<add name="pgconnection"

connectionString="Server=virtw7-lucinda;Port=5432;Database=mydatabase;User Id=myuser;Password=myuser;"

providerName="Npgsql" />

</connectionStrings>

</configuration>

If you’re running this application on the same machine you installed Postgres, then the previous code sample will work as is. If not, you'll need to change the parameters as required to point to the correct server.

Once we get to this point, all that remains to be done is to wire up the UI buttons and elements to the various methods in the Database class.

Please refer to the download for the full code. I'm not going to paste the full form code here, as it's quite long. I've also added some extra functionality to make the UI more friendly, but these extras are not necessary to demonstrate how to use Postgres.

The key thing here is the Database class and the way it acts as a proxy between the classes representing your table data and your program code.

If you’re using raw Npgsql, then you should definitely take a look at the manual page available at http://npgsql.projects.pgfoundry.org/docs/manual/UserManual.html.

There is so much more that is available, especially in release 2. Things like prepared statements, stored procedure support, and reference cursors are now available, along with bulk table imports and exports, and many other features.

Once you have the app running, on the first tab you should see this:

View Data tab

And on the second tab:

Edit Data tab

Converting the App to Use Entity Framework

For this final part, we'll take our ADO.NET-based app, and only change what we need to make it work with Entity Framework 5 Code First.

You won't need to make any changes to the GUI code since we'll use the existing DataBase.cs class to provide the same interface to our app as the ADO.NET version.

For this part you can delete the ConnectionStrings.cs class in your classes folder, and create a new class called EFDataBase.cs.

You also need to use NuGet as you did previously, and add Entity Framework to your project. (At the time of writing this book, v5 was the current version.)

EFDatabase.cs should contain the following code:

using System.Data.Entity;

using PostgresExample.Entities;

namespace PostgresExample.Classes

{

public class EFDatabase : DbContext

{

public EFDatabase()

: base("pgconnection") // Name of connection string to look for.

{ }

public DbSet<MyTable> MyTables { get; set; }

}

This is the main interface between our code and Entity Framework. Any of the many tutorials available on the Internet will explain what's going on here.

Once we have the Entity Framework interface ready, we need to adapt our Database.cs class so that it looks as follows:

using System.Collections.Generic;

using System.Data;

using System.Data.Entity.Infrastructure;

using System.Linq;

using PostgresExample.Entities;

namespace PostgresExample.Classes

{

public class Database

{

public List<MyTable> GetAll()

{

List<MyTable> results;

using(EFDatabase myDb = new EFDatabase())

{

results = myDb.Set<MyTable>().AsQueryable().ToList();

}

return results;

}

public void AddNew(MyTable entity)

{

using (EFDatabase myDb = new EFDatabase())

{

myDb.Set<MyTable>().Add(entity);

myDb.SaveChanges();

}

public void DeleteByGid(int gid)

{

using(EFDatabase myDb = new EFDatabase())

{

MyTable entityToRemove = myDb.Set<MyTable>().FirstOrDefault(x => x.Gid == gid);

if (entityToRemove == null) return;

myDb.Set<MyTable>().Remove(entityToRemove);

myDb.SaveChanges();

}

public void Update(MyTable entity)

{

using(EFDatabase myDb = new EFDatabase())

{

DbEntityEntry entry = myDb.Entry(entity);

if (entry != null)

{

switch (entry.State)

{

case EntityState.Detached:

myDb.Set<MyTable>().Attach(entity);

myDb.Entry(entity).State = EntityState.Modified;

break;

case EntityState.Deleted:

entry.CurrentValues.SetValues(entity);

entry.State = EntityState.Modified;

break;

default:

entry.State = EntityState.Modified;

break;

}

else

{

myDb.Set<MyTable>().Attach(entity);

myDb.Entry(entity).State = EntityState.Modified;

}

myDb.ChangeTracker.DetectChanges();

myDb.SaveChanges();

}

There is much less code than in the ADO.NET database class, but this does come at a price. Entity Framework is much slower than plain ADO.NET. It's worth noting that many of the micro ORMs such as Simple.Data, Massive, and ServiceStack all support Postgres. I can highly recommend Simple.Data, as I've used it for many projects where Postgres has been the main store.

Once the data classes are set, you need to make some changes to your MyTable entity class.

These changes involve adding Entity Framework data attributes to the class to set the default schema (which is always public under Postgres) and making sure the column names are lowercase.

Note also that when using CodeFirst in Entity Framework with NpgSql, you must create your tables by hand. Entity Framework with Postgres will not currently create non-existing tables for you.

After you make the changes to your entity, it should look something like the following:

using System.ComponentModel.DataAnnotations;

using System.ComponentModel.DataAnnotations.Schema;

namespace PostgresExample.Entities

{

// Note: I'm using column attributes to enforce lowercase names and set the default schema which is 'public'

[Table("mytable", Schema = "public")]

public class MyTable

{

[Key]

[Column("gid")]

public int Gid { get; set; }

[Column("name")]

public string Name { get; set; }

[Column("email")]

public string Email { get; set; }

[Column("webpage")]

public string Webpage { get; set; }

[Column("bio")]

public string Bio { get; set; }

}

Once the code changes are finished, the last thing to do is to register Npgsql in your app config as a data provider by using the following addition:

<system.data>

<add name="Npgsql Data Provider"

invariant="Npgsql"

description="Data Provider for PostgreSQL"

type="Npgsql.NpgsqlFactory, Npgsql" />

</DbProviderFactories>

</system.data>

Without this section in your application config file, you'll get an exception when you try to connect stating that the registered data provider was not found.

If everything has worked as planned, then pressing F5 and running your app should give you the same output as the previous ADO.NET example.

Connect and visualize PostgreSQL data

Seamless integration with PostgreSQL databases
Real-time data visualization for actionable insights
Customizable dashboards to meet your specific needs
Leverage PostgreSQL's scalability and performance

Get Your Free Trial Now

Using Postgres with .NET

Create a simple Windows Forms application

ConnectionStrings.cs

MyTable.cs

Converting the App to Use Entity Framework

DISCLAIMER: Web reader is currently in beta. Please report any issues through our support system. PDF and Kindle format files are also available for download.