Skip to content

6. Case Study with PostgreSQL 9.2.1

6.1. Installing the tools

The tools to install are as follows:

  • the DBMS: [http://www.enterprisedb.com/products-services-training/pgdownload#windows];
  • an administration tool: EMS SQL Manager for PostgreSQL Freeware [http://www.sqlmanager.net/fr/products/postgresql/manager/download].

In the following examples, the user postgres has the password postgres.

Let’s start PostgreSQL and then the [SQL Manager Lite for PostgreSQL] tool, which we will use to administer the DBMS.

  • In [1], we start the PostgreSQL DBMS from Windows Services;
  • In [2], the service is started;

We now launch the [SQL Manager Lite for MySQL] tool, which we will use to administer the DBMS [3].

  • In [4], we create a new database;
  • In [5], we specify the database name;
  • In [5], we log in as postgres / postgres;
  • In [6], we provide some information;
  • In [7], we validate the SQL statement to be executed;
  • In [8], the database has been created. It must now be registered in [EMS Manager]. The information is correct. Click [OK];
  • In [9], connect to it;
  • In [10], [EMS Manager] displays the database, which is currently empty. Note that the tables will belong to a schema called public [11].

We will now connect a VS 2012 project to this database.

6.2. Creating the database from the entities

We start by duplicating the [RdvMedecins-SqlServer-01] project folder into [RdvMedecins-PostgreSQL-01] [1]:

  • in [2], in VS 2012, we remove the [RdvMedecins-SqlServer-01] project from the solution;
  • in [3], the project has been removed;
  • in [4], we add another one. This one is taken from the [RdvMedecins-PostgreSQL-01] folder that we created earlier;
  • in [5], the loaded project is named [RdvMedecins-SqlServer-01];
  • in [6], we rename it to [RdvMedecins-PostgreSQL-01];
  • In [7], we add another project to the solution. This one is taken from the [RdvMedecins-SqlServer-01] folder of the project we previously removed from the solution;
  • in [8], the [RdvMedecins-SqlServer-01] project has been re-added to the solution.

The [RdvMedecins-PostgreSQL-01] project is identical to the [RdvMedecins-SqlServer-01] project. We need to make a few changes. In [App.config], we will modify the connection string and the [DbProviderFactory], which must be adapted to each DBMS.


<!-- database connection string -->
  <connectionStrings>
    <add name="myContext" connectionString="Server=127.0.0.1;Port=5432;Database=rdvmedecins-ef;User Id=postgres;Password=postgres;" providerName="Npgsql" />
  </connectionStrings>
  <!-- the factory provider -->
  <system.data>
    <DbProviderFactories>
      <add name="Npgsql Data Provider" invariant="Npgsql" support="FF" description=".Net Framework Data Provider for Postgresql Server" type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.11.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
    </DbProviderFactories>
  </system.data>
  • line 3: the username and password;
  • Lines 7–9: the DbProviderFactory. Line 8 references a DLL [Npgsql] that we don’t have. We can get it using NuGet [1]:
  • in [2], type the keyword postgresql in the search box;
  • in [3], select the [Npgsql] package. This is an ADO.NET connector for PostgreSQL;
  • in [4], two references have been added;
  • in [5], in [App.config], you must specify the correct version of the DLL. You can find it in its properties.

In the [Entities.cs] file, you need to adapt the schema of the tables that will be generated:


  [Table("MEDECINS", Schema = "public")]
  public class Doctor : Person
  {...}

  [Table("CLIENTS", Schema = "public")]
  public class Client : Person
  {...}

  [Table("SLOTS", Schema = "public")]
  public class Creneau
  {...}

  [Table("RVS", Schema = "public")]
  public class Rv
  {...}

We saw earlier when creating a PostgreSQL database that the tables belonged to a schema called public.

We configure the project's execution:

  • in [1], we give a different name to the assembly that will be generated;
  • in [2], we also specify a different default namespace;
  • in [3], we specify the program to be executed.

At this stage, there are no compilation errors. Let’s run the [CreateDB_01] program. We get the following exception:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
Unhandled exception: System.Data.MetadataException: The specified schema is invalid. Errors:
(11,6): error 0040: The rowversion type is not qualified with a namespace or alias. Only primitive types can be used without qualification.
(23,6): error 0040: The rowversion type is not qualified with a namespace or alias. Only primitive types can be used without qualification.
(33,6): error 0040: The rowversion type is not qualified with a namespace or alias. Only primitive types can be used without qualification.
(43,6): error 0040: The rowversion type is not qualified with a namespace or alias. Only primitive types can be used without qualification.
   to System.Data.Metadata.Edm.StoreItemCollection.Loader.ThrowOnNonWarningErrors
()
   ...
   at RdvMedecins_01.CreateDB_01.Main(String[] args) in d:\data\istia-1213\c#\d
vp\Entity Framework\RdvMedecins\RdvMedecins-Oracle-01\CreateDB_01.cs:line 15

We recall having encountered the same error with MySQL and Oracle. This is related to the type of the Timestamp field in the entities. We make the same modification as with Oracle. In the entities, we replace the three lines


    [Column("TIMESTAMP")]
    [Timestamp]
    public byte[] Timestamp { get; set; }

with the following:


    [ConcurrencyCheck]
    [Column("VERSIONING")]
    public int? Versioning { get; set; }

We change the column type from byte[] to int?. In the DBMS, we will use stored procedures to increment this integer by one each time a row is inserted or modified.

We make the above change to all four entities and then rerun the application. We then get the following error:

1
2
3
4
5
6
Unhandled exception: System.Data.DataException: An exception occurred while initializing the database. See the InnerException for details. ---> System.Data.ProviderIncompatibleException: DeleteDatabase is not supported by the provider.
   at System.Data.Common.DbProviderServices.DbDeleteDatabase(DbConnection connection, Nullable`1 commandTimeout, StoreItemCollection storeItemCollection)
   ...
   to System.Data.Entity.Internal.LazyInternalContext.InitializeDatabase()
   at RdvMedecins_01.CreateDB_01.Main(String[] args) in d:\data\istia-1213\c#\d
vp\Entity Framework\RdvMedecins\RdvMedecins-Oracle-01\CreateDB_01.cs:line 15

Line 1 indicates that the PostgreSQL ADO.NET connector is unable to delete the existing database. Just like with Oracle. We are therefore required to build the [RDVMEDECINS-EF] database manually using the [EMS Manager for PostgreSQL] tool. We will not describe every step, but only the most important ones.

The PostgreSQL database will be as follows:

The tables

  • In [1], ID is a primary key of type serial. This PostgreSQL type is an integer automatically generated by the DBMS.

The various tables have the primary and foreign keys that these same tables had in the previous examples. The foreign keys have the ON DELETE CASCADE attribute.

Sequences

As with Oracle, we have created sequences here. These are generators of consecutive numbers. There are 5 of them [1].

  • In [2], we see the properties of the [CLIENTS_ID_SEQ] sequence. It generates consecutive numbers in increments of 1, starting from 1 up to a very large value.

All sequences are built on the same model.

  • [CLIENTS_ID_seq] will be used to generate the primary key for the [CLIENTS] table;
  • [MEDECINS_ID_seq] will be used to generate the primary key for the [MEDECINS] table;
  • [SLOTS_ID_seq] will be used to generate the primary key for the [SLOTS] table;
  • [RVS_ID_seq] will be used to generate the primary key for the [RVS] table;
  • [sequence_versions] will be used to generate the values for the [VERSIONING] columns in all tables.

Triggers

A trigger is a procedure executed by the DBMS before or after an event (Insert, Update, Delete) in a table. We have 4 of them [1]:

Let’s look at the DDL code for the [CLIENTS_tr] trigger, which populates the [VERSIONING] column of the [CLIENTS] table:

1
2
3
4
CREATE TRIGGER "CLIENTS_tr"
  BEFORE INSERT OR UPDATE 
  ON public."CLIENTS" FOR EACH ROW 
EXECUTE PROCEDURE public.trigger_versions();
  • lines 1-3: before each INSERT or UPDATE operation on the [CLIENTS] table;
  • Line 4: The procedure [public.trigger_versions()] is executed.

The procedure [public.trigger_versions()] is as follows:

1
2
3
4
BEGIN
NEW."VERSIONING":=nextval('sequence_versions');
return NEW;
END
  • Line 2: NEW represents the row that is about to be inserted or modified. NEW. "VERSIONING" is the [VERSIONING] column of that row. It is assigned the next value from the "sequence_versions" number generator. Thus, the ["VERSIONING"] column changes with every INSERT or UPDATE performed on the [CLIENTS] table.

The triggers [MEDECINS_tr, CRENEAUX_tr, RVS_tr] work in the same way. The four ["VERSIONING"] columns derive their values from the same sequence.

The script for generating the tables in the PostgreSQL database [RDVMEDECINS-EF] has been placed in the folder [RdvMedecins / databases / postgreSQL]. The reader can load and run it to create the tables.

Once this is done, the various programs in the project can be run. They produce the same results as with SQL Server, except for the [ModifyDetachedEntities] program, which crashes for the same reason it crashed with Oracle. The problem is resolved in the same way. Simply copy the [ModifyDetachedEntities] program from the [RdvMedecins-Oracle-01] project into the [RdvMedecins-PostgreSQL-01] project.

The [LazyEagerLoading] program crashes with the following exception:

1
2
3
4
Unhandled exception: System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. For more details, see the inner exception. ---> Npgsql.NpgsqlException: ERROR: 42601: syntax error at or near "LEFT"
   at Npgsql.NpgsqlState.<ProcessBackendResponses_Ver_3>d__a.MoveNext()
   ...
   at RdvMedecins_01.LazyEagerLoading.Main(String[] args) in d:\data\istia-1213\c#\dvp\Entity Framework\RdvMedecins\RdvMedecins-PostgreSQL-01\LazyEagerLoading.cs:line 23

The incorrect code is as follows:


      using (var context = new RdvMedecinsContext())
      {
        // slot #0
        slot = context.Slots.Include("Doctor").Single<Slot>(c => c.Id == slotId);
        Console.WriteLine(slot.ShortIdentity());
}

Line 1 of the exception: the reported error suggests a join because LEFT is a join keyword. Since line 4 of the code above requests the immediate loading of the [Doctor] dependency for an [Appointment] entity, EF performed a join between the [APPOINTMENTS] and [DOCTORS] tables. However, it appears that the ADO.NET connector generated an incorrect SQL statement. We rewrite the code as follows:


      using (var context = new RdvMedecinsContext())
      {
        // slot #0
        slot = context.Slots.Find(slotId);
        Console.WriteLine(slot.ShortIdentity());
        // we force the loading of the associated doctor
        // this is possible because we are still in an open context
        Doctor doctor = slot.Doctor;
}
  • line 4: we retrieve the slot without a join;
  • line 8: we retrieve the missing dependency.

It works. Once again, we see that changing the DBMS has an impact on the code. In fact, it’s not the DBMS that’s the issue here, but its ADO.NET connector.

6.3. Multi-layer architecture based on EF 5

We return to our case study described in paragraph 2.

We will start by building the [DAO] data access layer. To do this, we duplicate the VS 2012 console project [RdvMedecins-SqlServer-02] into [RdvMedecins-PostgreSQL-02] [1]:

  • In [2], delete the project [RdvMedecins-SqlServer-02];
  • In [3], add an existing project to the solution. Take it from the [RdvMedecins-PostgreSQL-02] folder that was just created;
  • In [4], the new project has the same name as the one that was deleted. We will change its name;
  • In [5], we have changed the project name;
  • In [6], we modify some of its properties, such as the assembly name here;
  • in [7], the [Models] folder is deleted and replaced by the [Models] folder from the [RdvMedecins-PostgreSQL-01] project. This is because both projects share the same models.
  • in [8], the project's current references;
  • in [9], the PostgreSQL ADO.NET connector has been added using the NuGet tool.

In the [App.config] file, replace the SQL Server database information with that of the PostgreSQL database. This information can be found in the [App.config] file of the [RdvMedecins-PostgreSQL-01] project:


  <!-- database connection string -->
  <connectionStrings>
    <add name="myContext" connectionString="Server=127.0.0.1;Port=5432;Database=rdvmedecins-ef;User Id=postgres;Password=postgres;" providerName="Npgsql" />
  </connectionStrings>
  <!-- the provider factory -->
  <system.data>
    <DbProviderFactories>
      <add name="Npgsql Data Provider" invariant="Npgsql" support="FF" description=".Net Framework Data Provider for Postgresql Server" type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.11.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
    </DbProviderFactories>
</system.data>

The objects managed by Spring also change. Currently we have:


  <!-- Spring configuration -->
  <spring>
    <context>
      <resource uri="config://spring/objects" />
    </context>
    <objects xmlns="http://www.springframework.net">
      <object id="rdvmedecinsDao" type="RdvMedecins.Dao.Dao,RdvMedecins-SqlServer-02" />
    </objects>
</spring>

Line 7 references the assembly for the [RdvMedecins-SqlServer-02] project. The assembly is now [RdvMedecins-PostgreSQL-02].

With that done, we are ready to run the [DAO] layer test. First, we must ensure the database is populated (using the [Fill] program from the [RdvMedecins-PostgreSQL-01] project). The test program crashes with the following exception:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
2012/10/12 13:56:27:188 [INFO]  Spring.Context.Support.XmlApplicationContext - A
pplicationContext Refresh: Completed
List of clients:
Client [47,Mr,Jules,Martin,468]
Client [48,Ms.,Christine,German,469]
Customer [49,Mr.,Jules,Jacquard,470]
Client [50,Ms.,Brigitte,Bistrou,471]
List of doctors:
Doctor [42,Ms., Marie, Pelissier, 472]
Doctor [43,Mr., Jacques, Bromard, 497]
Doctor [44,Mr.,Philippe,Jandot,510]
Doctor [45,Ms.,Justine,Jacquemot,511]
The following error occurred: RdvMedecinsException[3,GetCreneauxMedecin,An error occurred while executing the command definition. For more details, see the internal exception.]

Line 13: The message indicates that the error occurred in the [GetCreneauxMedecin] method of the [DAO] layer. This method is as follows:


    // list of time slots for a given doctor
    public List<Creneau> GetCreneauxMedecin(int idMedecin)
    {
      // list of time slots
      try
      {
        // open persistence context
        using (var context = new RdvMedecinsContext())
        {
          // retrieve the doctor and their available slots
          Doctor doctor = context.Doctors.Include("Slots").Single(m => m.Id == doctorId);
          // return the list of the doctor's time slots
          return doctor.Slots.ToList<Slot>();
        }
      }
      catch (Exception ex)
      {
        throw new RdvMedecinsException(3, "GetCreneauxMedecin", ex);
      }
}

Line 11: We recognize the Include keyword, which previously caused a program to crash. The previous code can be replaced with the following:


    // list of time slots for a given doctor
    public List<Creneau> GetCreneauxMedecin(int idMedecin)
    {
      // list of time slots
      try
      {
        // open persistence context
        using (var context = new RdvMedecinsContext())
        {
          // return the list of the doctor's time slots
          return context.Slots.Where(c => c.DoctorId == doctorId).ToList<Slot>(); 
        }
      }
      catch (Exception ex)
      {
        throw new RdvMedecinsException(3, "GetCreneauxMedecin", ex);
      }
}

The new code even seems more consistent than the old one. In any case, this time the test program passes.

We create the project’s DLL as we did for the [RdvMedecins-SqlServer-02] project and gather all the project’s DLLs into a [lib] folder created within [RdvMedecins-PostgreSQL-02]. These will be the references for the [RdvMedecins-PostgreSQL-03] web project that follows.

  

We are now ready to build the [ASP.NET] layer of our application:

We will start with the [RdvMedecins-SqlServer-03] project. We duplicate this project’s folder into [RdvMedecins-PostgreSQL-03] [1]:

  • in [2], using VS 2012 Express for the Web, we open the solution in the [RdvMedecins-PostgreSQL-03] folder;
  • In [3], we change both the solution name and the project name;
  • in [4], the project's current references;
  • in [5], we delete them;
  • in [6], to replace them with references to the DLLs we just stored in a [lib] folder in the [RdvMedecins-PostgreSQL-02] project.

All that remains is to modify the [Web.config] file. We replace its current content with the content of the [App.config] file from the [RdvMedecins-PostgreSQL-02] project. Once this is done, we run the web project. It works. We must not forget to populate the database before running the web application.

https://tahe.developpez.com
This tutorial written by Serge Tahé is made available to the public under the terms of the Creative Commons Attribution – Non-Commercial – ShareAlike 3.0 Unported.