Class Databse Postgresql c#

ini adalah class database untuk postgresql untuk project dengan microsoft visual studio 2008 (tidak cocok dengan ms visual studio 2005). Class ini membutuhkan driver NPGSql. Silahkan download disini (Pg Foundry NPGsql) dan add reference-nya pada project kamu.

 Jika digunakan pada form:

a. tambahkan definisi:

using Npgsql;

b. deklarasikan class pada form:

Classes.DatabasePg cDatabasePg = new Classes.DatabasePg();

c. pada event form.closing, tambahkan baris kode :

this.cDatabasePg.DisConnect();

ini adalah badan dari class tersebut. Class ini disimpan dalam Folder Classes. Sebelum kamu terapkan class ini pada project c# kamu, edit terlebih dahulu semua yang ada di bracket.

[nama projectnya disini]
[host ip address server postgress disini]
[user postgres disini]
[password si user postgress disini]
[nama database-nya disini]

ini badan dari class DatabasePg:

using System;
using System.Collections.Generic;
using System.Text;
using Npgsql;

namespace [nama projectnya disini].Classes
{
    class DatabasePg
    {
        public NpgsqlConnection NCONN { get; set; }
        public NpgsqlCommand NCMD1 { get; set; }
        public NpgsqlCommand NCMD2 { get; set; }
        public NpgsqlDataReader NDR1 { get; set; }
        public NpgsqlDataReader NDR2 { get; set; }
        public NpgsqlTransaction NTR { get; set; }

        public bool DB_DEBUG_MODE = false;
        private string className = "Classes.DatabasePg";
       
        private string createConnectionString(string keyString) {
            if (keyString == "") return "";
            string[] keys = keyString.Split('|');
            if (keys.Length != 4) return "";
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("Server={0};User Id={1};Password={2};Database={3};", keys);
            return sb.ToString();
        }

        public void ShowNException(string className, NpgsqlException nex) {
            System.Windows.Forms.MessageBox.Show(
                "ERROR ON POSTGRES\nClass Name: " + className +
                "\nError Message: " + nex.Message +
                "\nError Number: " + nex.Code +
                "\nStack Trace: \n" + nex.StackTrace,
                "Postgres Error",
                System.Windows.Forms.MessageBoxButtons.OK,
                System.Windows.Forms.MessageBoxIcon.Error);
        }

        public void ShowException(string className, Exception ex) {
            System.Windows.Forms.MessageBox.Show(
                "ERROR ON APPLICATION\nClass Name: " + className +
                "\nError Message: " + ex.Message +
                "\nStack Trace: \n" + ex.StackTrace,
                "Application Error",
                System.Windows.Forms.MessageBoxButtons.OK,
                System.Windows.Forms.MessageBoxIcon.Error);
        }

        public bool Connect()
        {
            if (this.NCONN != null) return true;
            try
            {
                this.NCONN = new NpgsqlConnection(this.createConnectionString("[host ip address server postgress disini]|[user postgres disini]|[password si user postgress disini]|[nama database-nya disini]"));
                this.NCONN.Open();
                return true;
            }
            catch (NpgsqlException nex)
            {
                this.ShowNException(this.className, nex);
                return false;
            }
            catch (Exception ex)
            {
                this.ShowException(this.className, ex);
                return false;
            }
        }

        public void DisConnect()
        {
            if (this.NCONN == null) return;
            if (this.NTR != null)
            {
                try
                {
                    this.NTR.Dispose();
                }
                catch { }
                finally
                {
                    this.NTR = null;
                }
            }
            this.CloseNDR2();
            this.CloseNDR1();
            this.CloseNCMD2();
            this.CloseNCMD1();
            try
            {
                this.NCONN.Close();
                this.NCONN.Dispose();
            }
            catch { }
            finally
            {
                this.NCONN = null;
            }
        }

        public void CloseNDR1()
        {
            if (this.NDR1 != null)
            {
                try
                {
                    this.NDR1.Close();
                    this.NDR1.Dispose();
                }
                catch { }
                finally
                {
                    this.NDR1 = null;
                }
            }
        }

        public void CloseNDR2()
        {
            if (this.NCMD2 != null)
            {
                try
                {
                    this.NCMD2.Cancel();
                    this.NCMD2.Dispose();
                }
                catch { }
                finally
                {
                    this.NCMD2 = null;
                }
            }
        }

        public void CloseNCMD1()
        {
            if (this.NCMD1 != null)
            {
                try
                {
                    this.NCMD1.Cancel();
                    this.NCMD1.Dispose();
                }
                catch { }
                finally
                {
                    this.NCMD1 = null;
                }
            }
        }

        public void CloseNCMD2()
        {
            if (this.NCMD2 != null)
            {
                try
                {
                    this.NCMD2.Cancel();
                    this.NCMD2.Dispose();
                }
                catch { }
                finally
                {
                    this.NCMD2 = null;
                }
            }
        }

        public DateTime GetCurrentSeverTime()
        {
            DateTime result = DateTime.MinValue;
            if (!this.Connect()) return result;
            try
            {
                this.NCMD1 = new NpgsqlCommand("SELECT NOW()", this.NCONN);
                result = (DateTime)this.NCMD1.ExecuteScalar();
            }
            catch (NpgsqlException nex)
            {
                this.ShowNException(this.className, nex);
            }
            catch (Exception ex)
            {
                this.ShowException(this.className, ex);
            }
            finally
            {
                this.CloseNCMD1();
            }
            return result;
        }
       
    }
}

Contoh penggunaan:

1. mengambil tanggal server :

DateTime serverTime = this.cDatabasePg.GetCurrentServerTime();

2. mengambil data:

        private List loadBagian()
        {
            List result = new List();
            if (!this.Connect()) return result;
            try
            {
                this.NCMD1 = new NpgsqlCommand("SELECT BAG_ID, NAMA_BAGIAN, DESC_BAGIAN, KP_ALAMAT, KP_KABKOTA, " +
                    "KP_NOTELP, KP_NOFAX, KP_WEBSITE, KP_EMAIL, KP_URLLOGO, KP_DIREKTUR FROM BAGIAN ORDER BY 1",
                    this.NCONN);
                this.NDR1 = this.NCMD1.ExecuteReader();
                if (this.NDR1.HasRows)
                {
                    while (this.NDR1.Read())
                    {
                        Objects.Bagian itemBagian = new Imelda2012.Objects.Bagian();
                        itemBagian.BAG_ID = this.NDR1.GetInt32(0);
                        itemBagian.NAMA_BAGIAN = this.NDR1.GetString(1);
                        itemBagian.DESC_BAGIAN = this.NDR1.GetString(2);
                        itemBagian.KP_ALAMAT = this.NDR1.GetString(3);
                        itemBagian.KP_KABKOTA = this.NDR1.GetString(4);
                        itemBagian.KP_NOTELP = (!this.NDR1.IsDBNull(5)) ? this.NDR1.GetString(5) : "-";
                        itemBagian.KP_NOFAX = (!this.NDR1.IsDBNull(6)) ? this.NDR1.GetString(6) : "-";
                        itemBagian.KP_WEBSITE = (!this.NDR1.IsDBNull(6)) ? this.NDR1.GetString(7) : "-";
                        itemBagian.KP_EMAIL = (!this.NDR1.IsDBNull(8)) ? this.NDR1.GetString(8) : "-";
                        itemBagian.KP_URLLOGO = (!this.NDR1.IsDBNull(9)) ? this.NDR1.GetString(9) : "-";
                        itemBagian.KP_DIREKTUR = (!this.NDR1.IsDBNull(10)) ? this.NDR1.GetString(10) : "-";
                        result.Add(itemBagian);
                    }
                    this.NDR1.Close();
                }
            }
            catch (NpgsqlException nex)
            {
                if (DB_DEBUG_MODE) this.ShowNException(this.className, nex);
            }
            catch (Exception ex)
            {
                if (Program.DEBUG_MODE) this.ShowException(this.className, ex);
            }
            return result;
        }

3. menggunakan parameter :

        public string GenSeqeunce(string header, string master, int len)
        {
            string result = "";
            if (!this.Connect()) return result;
            try
            {
                if (this.NTR != null)
                    this.NCMD1 = new NpgsqlCommand("SELECT GENERATE_SEQUENCE(:HEADER, :MASTER, :LEN)", this.NCONN, this.NTR);
                else
                    this.NCMD1 = new NpgsqlCommand("SELECT GENERATE_SEQUENCE(:HEADER, :MASTER, :LEN", this.NCONN);
                this.NCMD1.Parameters.Add(new NpgsqlParameter(":HEADER", NpgsqlDbType.Text) { NpgsqlValue = header, Direction = System.Data.ParameterDirection.Input });
                this.NCMD1.Parameters.Add(new NpgsqlParameter(":MASTER", NpgsqlDbType.Text) { NpgsqlValue = master, Direction = System.Data.ParameterDirection.Input });
                this.NCMD1.Parameters.Add(new NpgsqlParameter(":LEN", NpgsqlDbType.Integer) { NpgsqlValue = len, Direction = System.Data.ParameterDirection.Input });
                this.NDR1 = this.NCMD1.ExecuteReader();
                if (this.NDR1.HasRows && this.NDR1.Read())
                    result = this.NDR1.GetString(0);
            }
            catch (NpgsqlException nex)
            {
                if (DB_DEBUG_MODE) this.ShowNException(this.className, nex);
            }
            catch (Exception ex)
            {
                if (Program.DEBUG_MODE) this.ShowException(this.className, ex);
            }
            finally
            {
                this.CloseNDR1();
                this.CloseNCMD1();
            }
            return result;
        }