C# - Npgsql - PostgreSQL Handler

• Download project from Github - 3.8 MB

Introduction

This article and the project attached to it introduces a library called DynamixPostgreSQLHandler which intends to make things easier for a .NET C# developer while working with a PostgreSQL database. The library contains a sample application which will teach a few tricks and many more for doing CRUD operations using simple C# Code.

The code snippets shown below will help developers perform the following tasks:

• ExecuteSQL
• ExecuteNonQuery
• ExecuteAsList
• ExecuteAsObject
• ExecuteAsScalar
• ExecuteAsDataSet
• ExecuteAsDataReader

Background

As a developer, I had been using MS SQL Server as my database for quite a while now. There came a requirement once, where I had to use PostgreSQL instead of my usual database. I went through a couple of blogs and articles and some more related stuff on how to get things done faster. Finally, I came across this library called Npgsql which did pretty much everything that I had been doing using ADO.NET.

Luckily, as I already had an ADO.NET SQLHandler library at my disposal which did some pretty awesome stuff, all I had to do now was hack into the code, adjust a few hundred lines here and there and test if it worked for Npgsql or not. And YES !! It did.

Note

The origianl SQLHandler class which this library is derived from belongs to SageFrame: An Open Source ASP.NET CMS and Web App Development Framework.

Setting Things Up

The samples below use the dvdrental database.

Here are the steps to set up the database:

1. Download the dvdrental.zip file from the above mentioned link.
2. Extract the zip file and obtain the dvdrental.tar file (you can also find a copy in the scripts folder).
3. Instantiate a database named dvdrental in your PostgreSQL Server.
4. Execute the following code or something similar to your environment:

pg_restore -U postgres -n public -d dvdrental dvdrental.tar

Here is a link that might help you to setup your database.

5. Execute the scripts found under the scripts/functions folder.
6. You are now ready to roll !!

Using the Code

The examples shown below are using data from the dvdrental database.

You can explore the code yourself and customize it to fit your needs.

EXECUTE SQL: BASIC

C# Code

try
{
    SQLHandler sqlHandler = new SQLHandler("ConnectionString");
    DataTable dtCountries = sqlHandler.ExecuteSQL("SELECT * FROM country;");
}
catch(System.Exception ex)
{
     // HANDLE YOUR EXCEPTION HERE
}

EXECUTE AS LIST: Using SQL Query

Let's say, you want to extract a list of cities from the database into your Info class.

The table might have some data as follows:

City Table Sample

Your Class file might look as shown below (following the Lower Case Convention for PostgreSQL).

Class Name: City.cs

public class City
{
    public int city_id { get; set; }

    public string city { get; set; }

    public int country_id { get; set; }
    
    public DateTime last_update { get; set; }
}

C# Code

try
{
    SQLHandler sqlHandler = new SQLHandler("ConnectionString");
    List<City> lstCities = sqlHandler.ExecuteAsListUsingQuery<City>("SELECT * FROM city;");
}
catch(System.Exception ex)
{
     // HANDLE YOUR EXCEPTION HERE
}

Output

EXECUTE AS LIST: Using Function Name

If you want to extract a list of records from your database using just the function name, there is a simpler way for achieving that.

Let's say you have a table with a list of actors. Your function definition might look as shown below:

Function Definition

CREATE OR REPLACE FUNCTION getactors(
	)
    RETURNS SETOF actor 
    LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
RETURN query execute 'select * from actor;';
END
$BODY$; 

Actor Table Sample

The first thing you do is create a class for the Actor table like the following:

Class Name: Actor.cs

public class Actor
{
    public int actor_id { get; set; }
    
    public string first_name { get; set; }
    
    public string last_name { get; set; }
    
    public DateTime last_update { get; set; }
}

C# Code

try
{
    SQLHandler sqlHandler = new SQLHandler("ConnectionString");
    List<Actor> lstActors = sqlHandler.ExecuteAsList<Actor>("getactors");
}
catch(System.Exception ex)
{
     // HANDLE YOUR EXCEPTION HERE
} 

Output

EXECUTE AS LIST: Using Function Name And Parameters

Extracting a list of data using parameters:

Function Definition

CREATE OR REPLACE FUNCTION getinventory_by_filmid_and_storeid(
	p_film_id integer,
	p_store_id integer
	)
    RETURNS SETOF inventory
    LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    RETURN QUERY (select * from inventory WHERE film_id = p_film_id AND store_id = p_store_id);
END
$BODY$;  

Inventory Table Sample

Class Name: Inventory.cs

public class Inventory
{
    public int inventory_id { get; set; }
    
    public int film_id { get; set; }
    
    public int store_id { get; set; }
 
    public DateTime last_update { get; set; }
}

C# Code

Output

EXECUTE AS OBJECT: Using SQL Query

If you wish to get a single object from the database, that is when you use the Execute as Object method.

Let's say, you wish to extract the details related to one specific film.

Class Name: Film.cs

 public class Film
{
    public long film_id { get; set; }
    public string title { get; set; }
    public string description { get; set; }
    public int release_year { get; set; }
    public int language_id { get; set; }
    public int rental_duration { get; set; }
    public decimal rental_rate { get; set; }
    public int length { get; set; }
    public decimal replacement_cost { get; set; }
    public string rating { get; set; }
    public DateTime last_update { get; set; }
    public string[] special_features { get; set; }
    public object fulltext { get; set; }
} 

C# Code

try
{      
    // EXECUTE THE HANDLER CODE AS SHOWN BELOW
    string query = "SELECT * FROM film WHERE film_id = 384;";
    SQLHandler sqlHandler = new SQLHandler("ConnectionString");
    Film<Film> film= sqlHandler.ExecuteAsObjectUsingQuery<Film>(query);
}
catch(System.Exception ex)
{
    // HANDLE YOUR EXCEPTION HERE
}

Output

EXECUTE AS OBJECT: Using Function And Parameters

You can even pass parameters using the same approach as for Execute As List.

Function Definition

CREATE OR REPLACE FUNCTION getactor_detail_by_id(p_actor_id integer)
      RETURNS SETOF actor LANGUAGE 'plpgsql' AS $BODY$
BEGIN
    RETURN QUERY (SELECT * FROM actor WHERE actor_id = p_actor_id );
END
$BODY$;

C# Code

Output

EXECUTE NON QUERY: Using Function And Parameters

Suppose you wish to insert a new actor in the actor table by passing in parameters to your function.

Function Definition

CREATE OR REPLACE FUNCTION add_new_actor(fname character varying(45),lname character varying(45))
    RETURNS VOID AS
$$
BEGIN
    INSERT INTO actor (first_name,last_name) values(fname, lname);
END
$$
    LANGUAGE 'plpgsql';

Class Name: Actor.cs (Same as above in the Execute As List: Using Function Name section)

C# Code

There are lots and lots of other ways to perform CRUD operations using this library.

Feel free to explore and contribute. Also, checkout the sample application attached along with it.

Leave a comment if you have any questions or suggestions.

History

• Inspired from SageFrames SQL Handler