Basic CRUD using C# and PostgreSQL using InLine Queries

 Blog author: Niladri.Biswas | Posted on: 7/7/2012 | Category: Others Blogs | Views: 1437 | Status: [Member] | Points: 75 | Alert Moderator   Tweet  Download source file

Submit Blog | Blogs Home |

Basic CRUD using C# and PostgreSQL using InLine Queries

Introduction

PostgreSQL is a powerful RDBMS and is there for a long time and it is free also which indicates that we will not have any licensing issue if we use it in our commercial applications.In this article we will look into how to interface C# application with PostgreSQL(9.1.0-1) and PgAdmin III(1.14.0).

Where to download it?

First we need to download the PostgreSQL(9.1.4) software from here. and NpgSql(2.0.11.94) from here.I have download Npgsql2.0.11.91-bin-ms.net4.0.zip since we will use .Net Framework 4 for this application.

Once the Npgsql2.0.11.94-bin-ms.net4.0.zip is downloaded, we need to unzip that inside which we will find

1. Mono.Security.dll
2. Npgsql.dll

These two dlls will participate while interfacing the C# application with PostgreSQL

Let's start the experiment

Now let us create a database(say TestDB) and have a Player table(tblPlayers) as under.

CREATE TABLE tblPlayers (
        PlayerName Varchar(50)
		,Salary Int
		,BelongsTo Varchar(50)
		,Age Int
);

Now, let us create a simple windows form application as under

Next add reference to Npgsql.dll and Mono.Security.dll in our project

We will now prepare our DAL layer

using System;
using System.Data;
using Npgsql;

namespace App1
{
    public class Dal
    {
        static string serverName = "127.0.0.1"; //localhost
        static string port = "5432"; // default port 
        static string userName = "postgres"; //admin name
        static string password = "niladri_1234"; //admin password
        static string databaseName = "TestDB"; //database name

        string connString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};",
                                           serverName, port, userName, password, databaseName);

        //Get all records
        public DataTable GetAllRecords()
        { 

            DataTable dtRecord = new DataTable();

            try
            {
                using (NpgsqlConnection pgsqlConnection = new NpgsqlConnection(connString))
                {
                    // Open the PgSQL Connection.                
                    pgsqlConnection.Open();
                    string selectCommand = "Select * from tblPlayers";

                    using (NpgsqlDataAdapter Adpt = new NpgsqlDataAdapter(selectCommand, pgsqlConnection))
                    {
                        Adpt.Fill(dtRecord);
                    }                   
                }
            }
            catch (NpgsqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dtRecord;
        }

        //Insert records
        public void InsertRecord(string PlayerName, int Age, string BelongsTo, int Salary)
        {
           
            try
            {
                using (NpgsqlConnection pgsqlConnection = new NpgsqlConnection(connString))
                {
                    // Open the PgSQL Connection.                  
                    pgsqlConnection.Open();

                    string insertCommand = String.Format(
                                                            "Insert Into tblPlayers(playername,age,belongsto,salary) values('{0}',{1},'{2}',{3})",
                                                            PlayerName,Age,BelongsTo,Salary
                                                        );

                    using (NpgsqlCommand pgsqlcommand = new NpgsqlCommand(insertCommand, pgsqlConnection))
                    {
                        pgsqlcommand.ExecuteNonQuery();
                    }                    
                }
            }
            catch (NpgsqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Update records
        public void UpdateRecord(string PlayerName, int Salary)
        {
            try
            {
                using (NpgsqlConnection pgsqlConnection = new NpgsqlConnection(connString))
                {
                    // Open the PgSQL Connection.                  
                    pgsqlConnection.Open();

                    string updateCommand = String.Format(
                                                            "Update tblPlayers Set salary  = salary + {0} Where playername = '{1}'",
                                                            Salary, PlayerName
                                                        );

                    using (NpgsqlCommand pgsqlcommand = new NpgsqlCommand(updateCommand, pgsqlConnection))
                    {
                        pgsqlcommand.ExecuteNonQuery();
                    }
                }
            }
            catch (NpgsqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        //Delete records
        public void DeleteRecord(string PlayerName)
        {
            try
            {
                using (NpgsqlConnection pgsqlConnection = new NpgsqlConnection(connString))
                {
                    // Open the PgSQL Connection.                  
                    pgsqlConnection.Open();

                    string insertCommand = String.Format(
                                                            "Delete From tblPlayers Where playername = '{0}'",
                                                            PlayerName
                                                        );

                    using (NpgsqlCommand pgsqlcommand = new NpgsqlCommand(insertCommand, pgsqlConnection))
                    {
                        pgsqlcommand.ExecuteNonQuery();
                    }
                }
            }
            catch (NpgsqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
    }
}

This program is quite easy to understand. First we are making the connection string which is a keyvalue pair combination. The GetAllRecords is use for fetching the records.We are first establishing the connection to the PostgreSQL by using the NpgsqlConnection class where we are specifying the connection string.Then by using the NpgsqlDataAdapter class, we are filling the datatable and we are returning it back to our presentation layer for binding to the grid

The InsertRecord,UpdateRecord and DeleteRecord also follow the same pattern.By the help of ExecuteNonQuery method of NpgsqlCommand class, we are performing the DML operations

Conclusion

So, in this article we have seen as how to interface C# and Postgre SQL using inline queries.Hope this will be helpful. The zip file for the sample application is attached.

Thanks for reading