Click here to Skip to main content
13,732,867 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

4.3K views
10 bookmarked
Posted 27 May 2018
Licenced CPOL

C# - Npgsql - PostgreSQL Handler

, 27 May 2018
Rate this:
Please Sign up or sign in to vote.
A PostgreSQL handler for C# developers for Rapid Development

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.

  1. Execute the scripts found under the scripts/functions folder.
  2. 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

City Table

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

List of Cities


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

List Of Actors

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

List Of Actors


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

List Of Inventories

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

List Of Inventories


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

Film Data


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$;
Class Name: Actor.cs (Same as above in the Execute As List: Using Function Name section)

C# Code

Output

Actor Data


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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ozesh Thapa
Software Developer Braindigit IT Solution
Nepal Nepal
I am a Software Engineer and a developer by profession.

The following things describes me:
1. I am a real time/run time developer : Basically learn and implement things on the fly.
2. Definitely not a technology racist : Love to work and explore any technology that i come across.
3. Prefer Beer over H2O : Code with passion and for fun

I am interested in Research & Development based tasks, exploring, experimenting and trying out new things.
Technologies i have been using up until now are C#, ASP.NET, Win Services, Web Services, Restful Web API, Windows Application, Windows Phone Application, Store Apps, couple of JavaScript frameworks, Xamarin Forms, NodeJS, React, ReactNative, AngularJS, SQL Server, MongoDB, Postgres etc.

You may also be interested in...

Comments and Discussions

 
SuggestionImplement a parameter builder Pin
P0110X31-May-18 6:12
memberP0110X31-May-18 6:12 
GeneralRe: Implement a parameter builder Pin
Ozesh Thapa31-May-18 10:27
professionalOzesh Thapa31-May-18 10:27 
QuestionThanks! Pin
Hakan AK29-May-18 12:43
professionalHakan AK29-May-18 12:43 
AnswerRe: Thanks! Pin
Ozesh Thapa31-May-18 10:28
professionalOzesh Thapa31-May-18 10:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web04-2016 | 2.8.180920.1 | Last Updated 27 May 2018
Article Copyright 2018 by Ozesh Thapa
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid