Difference between revisions of "CSharp:Tips esempi di accesso a DB PostgreSQL"

Latest revision as of 14:15, 13 May 2016

PostgreSQL

Supponendo di usare la libreria NpgSQL installabile via NuGet (MainMenu VisualStudio\NuGet Package Manager\ Manage NuGet Packages for Solutions...).
Un esempio da cui partire è: codeprojectsito ufficiale: npgsql.org
Segue lo stato dell'arte, quindi provvisorio, di alcuni miei test.

Classe Helper

using System;
using Npgsql;
using System.Data;
using System.Reflection;
using NLog;
using System.Collections.Generic;
 
namespace MultiPrepareKTUpdater.DAL
{
    public class DBHelper_PostgreSQL
    {
        public static Logger Logger4DAO;
 
        public static NpgsqlConnection OpenConnection(string connectionString)
        {
            NpgsqlConnection cnn = new NpgsqlConnection(connectionString);
            cnn.Open();
            return cnn;
        }
 
        public static bool CloseConnection(NpgsqlConnection cnn)
        {
            bool ok = true;
            if (cnn != null
                && cnn.State == System.Data.ConnectionState.Open)
            {
                cnn.Close();
            }
            return ok;
        }
 
        public static T GetEntity<T>(DataRow dr)
        {
            bool columnCanNotCorrespond = true;
            return GetEntity<T>(dr, columnCanNotCorrespond);
        }
 
        /// <summary>
        /// Riempie una Entità con i valori presenti in un DataRow automaticamente. Se un valore è NULLO inserirà il valore di default.
        /// L'automatismo funzione solo se i nomi delle colonne (campi del DataBase) corrispondono ai nomi 
        /// delle properties se una Property non ha colonna del DataRow semplicemente è valorizzata al Default
        /// (come dopo una istanzizione dell'oggetto mediante "new"). Se una colonna esiste ma non c'è la 
        /// corrispondente proprietà la segnalazione dell'eccezione dipenderà dal flag: columnCanNotCorrespond
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dr"></param>
        /// <param name="columnCanNotCorrespond">Se true, non scatta eccezione se c'è una colonna che non è corrispondente ad una Property</param>
        /// <returns></returns>
        public static T GetEntity<T>(DataRow dr, bool columnCanNotCorrespond)
        {
            Type entityType = typeof(T);
            T entity = (T)entityType.Assembly.CreateInstance(entityType.FullName);
 
            if (columnCanNotCorrespond)
            {
                foreach (DataColumn dc in dr.Table.Columns)
                {
                    object columnValue = dr[dc.ColumnName];
                    //La Property Esiste?
                    if (entity.GetType().GetProperty(dc.ColumnName, BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase) != null)
                    {
                        //Assegnazione condizionata affinchè rimanga il valore di Default nel caso il valore da inserire sia nullo 
                        if (columnValue != DBNull.Value)
                        {
                            entity.GetType().GetProperty(dc.ColumnName, BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase).SetValue(entity, columnValue, null);
                        }
                    }
                }
            }
            else //Scatterà eccezione se la Property non corrisponde alla colonna!
            {
                foreach (DataColumn dc in dr.Table.Columns)
                {
                    object columnValue = dr[dc.ColumnName];
                    //Assegnazione condizionata affinchè rimanga il valore di Default nel caso il valore da inserire sia nullo 
                    if (columnValue != DBNull.Value)
                    {
                        entity.GetType().GetProperty(dc.ColumnName, BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase).SetValue(entity, columnValue, null);
                    }
                }
            }
            return (T)entity;
        }
 
        /// <summary>
        /// Data una DataTable, restituisce una collezione di entità
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static List<T> GetEntity<T>(DataTable dt) where T : new()
        {
            List<T> tmp_list = new List<T>();
            if (dt != null)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    T toAdd = GetEntity<T>(dr);
                    if (toAdd != null)
                    {
                        tmp_list.Add(toAdd);
                    }
                }
            }
            return tmp_list;
        }
    }
}

Classe DAO

using System;
using System.Collections.Generic;
using Npgsql;
using System.Data;
using MultiPrepareKTUpdater.Model;
using NLog;
 
namespace MultiPrepareKTUpdater.DAL
{
    public class MetaServerDAO
    {
        public static Logger Logger4DAO;
 
        public static string CheckServerActive(string cnnStringSrv_First, string cnnStringSrv_Second)
        {
            string strCnnStringDBActive = string.Empty;
 
            try
            {
 
            }
            catch (Exception ex)
            {
                if (Logger4DAO != null)
                {
                    Logger4DAO.Error("Ex.: {0}\r\n\r\n{1}", ex.Message, ex.StackTrace);
                }
                throw;
            }
            return strCnnStringDBActive;
        }
 
        public static string GetVersionForShipFromServer(string cnnStringSrv_First, string cnnStringSrv_Second, 
                                                        string shipCode, string companyCode)
        {
            string strVersionFromDB = string.Empty;
            NpgsqlConnection cnn = new NpgsqlConnection();
            string qrySQL = string.Empty;
            DataTable dt = new DataTable();
            int rowsAffected = 0;
 
            try
            {
                cnn = DBHelper_PostgreSQL.OpenConnection(cnnStringSrv_First);
                qrySQL = string.Format(
@"SELECT 
  ""Ships"".""Id"", 
  ""Ships"".""CompanyCode"", 
  ""Ships"".""ShipCode"", 
  ""Ships"".""Disabled"", 
  ""Ships"".""LastUpdate"", 
  ""Ships"".""UpdaterVersion"", 
  ""Ships"".""ConfigurationVersion"", 
  ""Ships"".""DatabaseVersion"", 
  ""Ships"".""SoftwareVersion""
FROM 
  dbo.""Ships""
WHERE 
  ""Ships"".""ShipCode"" = '{0}'
  AND ""Ships"".""CompanyCode"" = '{1}';", shipCode, companyCode);
 
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(qrySQL, cnn);
                rowsAffected = da.Fill(dt);
 
                if (rowsAffected == 0
                    || dt == null
                    || dt.Rows.Count == 0)
                {
                    cnn = DBHelper_PostgreSQL.OpenConnection(cnnStringSrv_Second);
 
                    da = new NpgsqlDataAdapter(qrySQL, cnn);
                    rowsAffected = da.Fill(dt);
                }
 
                if (rowsAffected > 0)
                {
                    strVersionFromDB = dt.Rows[0]["UpdaterVersion"].ToString();
                }
            }
            catch (Exception ex)
            {
                if (Logger4DAO != null)
                {
                    Logger4DAO.Error("shipCode = '{0}',companyCode = '{1}'\t\r\nEx.: {2}\r\n\r\n{3}", 
                        shipCode, companyCode, ex.Message, ex.StackTrace);
                }
                throw;
            }
            finally
            {
                DBHelper_PostgreSQL.CloseConnection(cnn);
            }
            return strVersionFromDB;
        }
 
        public static DB_InfoForShip GetInfoForShip(string cnnStringSrv_First, string cnnStringSrv_Second,
                                                 string shipCode, string companyCode)
        {
            string strVersionFromDB = string.Empty;
            NpgsqlConnection cnn = new NpgsqlConnection();
            string qrySQL = string.Empty;
            DataTable dt = new DataTable();
            int rowsAffected = 0;
            DB_InfoForShip iShip = null;
 
            try
            {
                cnn = DBHelper_PostgreSQL.OpenConnection(cnnStringSrv_First);
                qrySQL = string.Format(
@"SELECT 
  ""Ships"".""Id"", 
  ""Ships"".""CompanyCode"", 
  ""Ships"".""ShipCode"", 
  ""Ships"".""Disabled"", 
  ""Ships"".""LastUpdate"", 
  ""Ships"".""UpdaterVersion"", 
  ""Ships"".""ConfigurationVersion"", 
  ""Ships"".""DatabaseVersion"", 
  ""Ships"".""SoftwareVersion""
FROM 
  dbo.""Ships""
WHERE 
  ""Ships"".""ShipCode"" = '{0}'
  AND ""Ships"".""CompanyCode"" = '{1}';", shipCode, companyCode);
 
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(qrySQL, cnn);
                rowsAffected = da.Fill(dt);
 
                if (rowsAffected == 0
                    || dt == null
                    || dt.Rows.Count == 0)
                {
                    cnn = DBHelper_PostgreSQL.OpenConnection(cnnStringSrv_Second);
 
                    da = new NpgsqlDataAdapter(qrySQL, cnn);
                    rowsAffected = da.Fill(dt);
                }
 
                if (rowsAffected > 0)
                {
                    iShip = DBHelper_PostgreSQL.GetEntity<DB_InfoForShip>(dt.Rows[0]);
                }
 
            }
            catch (Exception ex)
            {
                if (Logger4DAO != null)
                {
                    Logger4DAO.Error("shipCode = '{0}',companyCode = '{1}'\t\r\nEx.: {2}\r\n\r\n{3}",
                        shipCode, companyCode, ex.Message, ex.StackTrace);
                }
                throw;
            }
            finally
            {
                DBHelper_PostgreSQL.CloseConnection(cnn);
            }
            return iShip;
        }
 
        // DA testare !!!
        public static List<DB_InfoForShip> ListInfoForShip(string cnnStringSrv_First, string cnnStringSrv_Second)
        {
            string strVersionFromDB = string.Empty;
            NpgsqlConnection cnn = new NpgsqlConnection();
            string qrySQL = string.Empty;
            DataTable dt = new DataTable();
            int rowsAffected = 0;
            List<DB_InfoForShip> lstInfoShip = null;
 
            try
            {
                cnn = DBHelper_PostgreSQL.OpenConnection(cnnStringSrv_First);
                qrySQL = string.Format(
@"SELECT 
  ""Ships"".""Id"", 
  ""Ships"".""CompanyCode"", 
  ""Ships"".""ShipCode"", 
  ""Ships"".""Disabled"", 
  ""Ships"".""LastUpdate"", 
  ""Ships"".""UpdaterVersion"", 
  ""Ships"".""ConfigurationVersion"", 
  ""Ships"".""DatabaseVersion"", 
  ""Ships"".""SoftwareVersion""
FROM 
  dbo.""Ships"";");
 
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(qrySQL, cnn);
                rowsAffected = da.Fill(dt);
 
                if (rowsAffected == 0
                    || dt == null
                    || dt.Rows.Count == 0)
                {
                    cnn = DBHelper_PostgreSQL.OpenConnection(cnnStringSrv_Second);
 
                    da = new NpgsqlDataAdapter(qrySQL, cnn);
                    rowsAffected = da.Fill(dt);
                }
 
                if (rowsAffected > 0)
                {
                    lstInfoShip = DBHelper_PostgreSQL.GetEntity<DB_InfoForShip>(dt);
                }
 
            }
            catch (Exception ex)
            {
                if (Logger4DAO != null)
                {
                    Logger4DAO.Error("Ex.: {0}\r\n\r\n{1}", ex.Message, ex.StackTrace);
                }
                throw;
            }
            finally
            {
                DBHelper_PostgreSQL.CloseConnection(cnn);
            }
            return lstInfoShip;
        }
    }
}

Es. Uso classi e metodi accesso al DB

ExecuteScalar

Segue esempio che sceglie tra due DB quello che da le info più aggiornate di un campo in una tabella

        /// <summary>
        /// Restituisce la stringa di connessione del DB attualmente attivo dopo aver valutato le due 
        /// possibili: *Firs e *_Second
        /// </summary>
        /// <param name="cnnStringSrv_First"></param>
        /// <param name="cnnStringSrv_Second"></param>
        /// <param name="minForValidrange"></param>
        /// <returns></returns>
        public static string CheckServerActive(string cnnStringSrv_First, string cnnStringSrv_Second, int minForValidrange)
        {
            string strCnnStringDBActive = string.Empty;
            string qrySQL = string.Empty;
            NpgsqlConnection cnn = new NpgsqlConnection();
            DateTime lastDBTableUpdateUTC = DateTime.MinValue;
            string str4Log = string.Empty;
 
            #region Controlli preliminari
            if (string.IsNullOrWhiteSpace(cnnStringSrv_First)
                || string.IsNullOrWhiteSpace(cnnStringSrv_Second))
            {
                throw new Exception(string.Format("At least one of the two query string is empty so no check is possible!\r\n1° = '{0}'\r\n2° = '{1}'",
                    cnnStringSrv_First, cnnStringSrv_Second));
            }
            if (minForValidrange <= 0)
            {
                throw new Exception(string.Format("Minutes to check for a valid datime of update has unaxpected value (='{0}').", minForValidrange));
            }
            #endregion
 
            try
            {
                #region Query SQL
                qrySQL = string.Format(
@"SELECT 
  ""Ships"".""LastUpdate""
FROM dbo.""Ships""
ORDER BY ""Ships"".""LastUpdate"" DESC;");
                #endregion
 
                #region PRIMO tentativo
                cnn = DBHelper_PostgreSQL.OpenConnection(cnnStringSrv_First);
 
                NpgsqlCommand cmd = new NpgsqlCommand(qrySQL, cnn);
                object objCmdResult = cmd.ExecuteScalar();
                if (objCmdResult != null)
                {
                    if (DateTime.TryParse(objCmdResult.ToString(), out lastDBTableUpdateUTC))
                    {
                        // Verifica se la data è sufficientemente recente
                        if (DateTime.UtcNow.AddMinutes(-minForValidrange) < lastDBTableUpdateUTC)
                        {
                            strCnnStringDBActive = cnnStringSrv_First;
                        }
                    }
                    else
                    {
                        Logger4DAO.Error("DateTime '{0}', last update is incorrect!\r\nstrConn{1}\r\n{1}",
                                        cnnStringSrv_First, objCmdResult.ToString(), qrySQL);
                    }
                }
                else
                {
                    Logger4DAO.Error("Query result is null on strConn '{0}'!\r\n{0}", cnnStringSrv_First, qrySQL);
                }
                #endregion
 
                #region SECONDO tentativo
                if (string.IsNullOrWhiteSpace(strCnnStringDBActive))
                {
                    // SI CHIUDE LA PRECEDENTE CONNESSIONE!!!!!
                    DBHelper_PostgreSQL.CloseConnection(cnn);
 
                    cnn = DBHelper_PostgreSQL.OpenConnection(cnnStringSrv_Second);
 
                    cmd = new NpgsqlCommand(qrySQL, cnn);
                    objCmdResult = cmd.ExecuteScalar();
                    if (objCmdResult != null)
                    {
                        if (DateTime.TryParse(objCmdResult.ToString(), out lastDBTableUpdateUTC))
                        {
                            // Verifica se la data è sufficientemente recente
                            if (DateTime.UtcNow.AddMinutes(-minForValidrange) < lastDBTableUpdateUTC)
                            {
                                strCnnStringDBActive = cnnStringSrv_Second;
                            }
                        }
                        else
                        {
                            Logger4DAO.Error("DateTime '{0}', last update is incorrect!\r\nstrConn{1}\r\n{1}",
                                            cnnStringSrv_First, objCmdResult.ToString(), qrySQL);
                        }
                    }
                    else
                    {
                        Logger4DAO.Error("Query result is null on strConn '{0}'!\r\n{0}", cnnStringSrv_Second, qrySQL);
                    }
                }
                #endregion
 
                // CASO in cui son fallite entrambe le stringhe! ==> ERRORE
                if (string.IsNullOrWhiteSpace(strCnnStringDBActive))
                {
                    str4Log = string.Format("No update data from the two DB!\r\nStrConn1='{0}'\r\nStrConn1='{0}'", 
                                    cnnStringSrv_First, cnnStringSrv_Second);
                    Logger4DAO.Error(str4Log);
                    throw new Exception(str4Log);
                }
            }
            catch (Exception ex)
            {
                if (Logger4DAO != null)
                {
                    Logger4DAO.Error("Qry SQL:\r\n{0}\r\n\r\nEx.: {1}\r\n\r\n{2}", qrySQL, ex.Message, ex.StackTrace);
                }
                throw;
            }
            finally
            {
                DBHelper_PostgreSQL.CloseConnection(cnn);
            }
            Logger4DAO.Debug("Connection string selected = '{0}'", strCnnStringDBActive);
            return strCnnStringDBActive;
        }

ExecuteNoQuery

Mappa e Link

C# | Helper DB

Visual Studio

Parole chiave:

Navigation

Help

Tools

WIDGETS

Widgets

Recent changes

Wanted articles

Who is online?

Article tools