PostgreSQL-ADO.NET Framework

• Download solution - 2.5 MB

Introduction

The simple application that shows how to connect the ADO.NET Framework to the PostgreSQL. The source code is based on the N-Tier pattern and uses EntityFramework6.Npgsql and Npgsql NuGet packages.

Background

PostgreSQL, unlike MS SQL Server, is completely free. It also has the ability to connect .NET applications via Entity Framework and some other packages. It also gives a good performance. So, it seems like a good free alternative for a SQL Server in applications.

Using the Code

As I described above, the source code is based on the N-Tier design and has 4 layers:

1. Repository
2. Model
3. Service
4. GUI

The solution also has a generic project that names XMen.A query that is a generic Query creator. Generic query generators have this ability that makes the independent query. The independent queries do not have any dependency on the type of database. They will be translated into the correct Query in the Repository layer.

In the Model layer, all classes are inherited from a superclass named Entity.

namespace SMS.Model
{
    public class Entity
    {
        public Guid Id { get; set; }
    }
}

Actually, it looks very simple but all the code in the application is based on this pattern. Model, Repository, and Service. It helps to have a hierarchical design in our source code. For example, you can see the structure of the Course class here:

namespace SMS.Model
{
    public class Cours:
        Entity
    {
        public string Name { get; set; }

    }
}

So as you can see, it's inherited from the Entity class. Also, this Model has its own repository and services in both Service and Repository layer.

For connecting the ADO.NET Entity Framework to the PostgreSQL, you should add two NuGet packages plus original ADO.NET Entity Framework. I have to pay attention that you should use Entity Framework 6.0 in your application and Entity Framework 5.0 is not compatible with PostgreSQL. Two NuGet packages are:

1. Npgsql
2. EntityFramework6.Npgsql

For installing each of them, you can use Visual Studio NuGet GUI or NuGet Command. But you don't have enough told yet. If you want to create a new entity schema and connect it to the PostgreSQL, you should install a simple Visual Studio extension that adds the PostgreSQL connection setting to your Visual Studio. The extension name is Npgsql PostgreSQL Integration. After you install it, you will have a new option in the Data source list box:

It lets you choose that your data source is a PostgreSQL. The connection properties for PostgreSQL are like that:

As a default, you can use 127.0.0.1 for the database server that is hosted on your computer. And after you filled all the necessary information, you can see the normal windows of Entity Framework and make your schema.

When you are working with the Entity Framework, you have two options for model and context. First, using the models and context file that the Visual Studio created automatically, second, using your own model and context. If you choose the second way, you should be aware that after each time you save your schema, the Visual Studio creates such files and these are conflicting with your context and model files.

In the photo, you can see samples of the generated files. You should delete them manually each time after you save the schema.

Points of Interest

I think that PostgreSQL has a good future. It's free but it has several features especially that can work with .NET solutions. It's really better than Microsoft SQL Server Express and you can replace it very easy. So, I've started to work with the PostgreSQL.