Npgsql Helper class
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Npgsql;
using NpgsqlTypes;
namespace AI_Portal_Cloud.DAL
{
public class clsPSQLHelper : IDisposable
{
private NpgsqlConnection connection = null;
private String connectionString = "dbConnection";
private NpgsqlDataAdapter sqlData = new NpgsqlDataAdapter();
public NpgsqlCommand sqlCmd = new NpgsqlCommand();
// {0} : IP, {1} : DB Name, {2} : userID, {3} : userPW
private String connectionFormat = "Server={0};Database={1};User Id={2};Password={3};Port={4}";
public String ConnectionString
{
set
{
connectionString = value;
}
}
#region Constructors
public clsPSQLHelper()
{
//
// TODO: Add constructor logic here
//
string strConn = String.Format(new Connection().ConnectionString);
//string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
//string strConn = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};",
// "localhost", 5432, "postgres", "admin", "systracker_adm");
//string strConn = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};",
// "productivity-tracker.cllnqmlttyn9.us-west-1.rds.amazonaws.com", 5432, "systracker_adm", "S9T2CZgWFtgbYaZS", "systracker_adm");
{
connectionString = strConn;
}
}
public clsPSQLHelper(string pConnectionString)
{
if (!string.IsNullOrEmpty(pConnectionString.Trim()))
{
connectionString = pConnectionString;
}
}
public clsPSQLHelper(string pIP, string pDbName, string pUserID, string pUserPW, string pPort)
{
connectionString = string.Format(this.connectionFormat, pIP, pDbName, pUserID, pUserPW, pPort);
}
public clsPSQLHelper(string pIP, string pDbName, string pUserID, string pUserPW)
{
connectionString = string.Format(this.connectionFormat, pIP, pDbName, pUserID, pUserPW, "5432");
}
#endregion
/// <summary>
/// Sql Script / Stored Procedure Name
/// </summary>
public string CmdText
{
set { sqlCmd.CommandText = value; }
}
/// <summary>
/// CommandType = CommandType....
/// </summary>
public CommandType cmdType
{
set { sqlCmd.CommandType = value; }
}
public NpgsqlTransaction cmdTransaction
{
set { sqlCmd.Transaction = value; }
}
/// <summary>
/// AddParameter(iSql, "return_value", SqlDbType.Int , "", ParameterDirection.ReturnValue , 5); ---- return문
/// AddParameter(iSql, "@MSG_TEXT", SqlDbType.VarChar, "", ParameterDirection.Output, 100);
/// </summary>
/// <param name="Param"></param>
/// <param name="Db_Type"></param>
/// <param name="values"></param>
/// <param name="Param_io"></param>
/// <param name="nSize"></param>
public void AddParameter(string Param, NpgsqlDbType Db_Type, object values, ParameterDirection Param_io, int nSize)
{
NpgsqlParameter param1 = new NpgsqlParameter(Param, Db_Type);
if (Param_io == ParameterDirection.Input || Param_io == ParameterDirection.InputOutput)
param1.Value = values;
param1.Direction = Param_io;
param1.Size = nSize;
sqlCmd.Parameters.Add(param1);
}
#region Add Parameter TO Query
/// <summary>
/// Store_Param(iSql, "@prc", SqlDbType.VarChar,"C");
/// </summary>
/// <param name="Param"></param>
/// <param name="Db_Type"></param>
/// <param name="values"></param>
public void AddParameter(string Param, NpgsqlDbType Db_Type, object values)
{
NpgsqlParameter param1 = new NpgsqlParameter(Param, Db_Type);
param1.Value = values;
sqlCmd.Parameters.Add(param1);
}
/// <summary>
/// Store_Param(iSql, "@MSG_CD", SqlDbType.VarChar, "", ParameterDirection.Output);
/// </summary>
/// <param name="Param"></param>
/// <param name="Db_Type"></param>
/// <param name="values"></param>
/// <param name="Param_io"></param>
public void AddParameter(string Param, NpgsqlDbType Db_Type, object values, ParameterDirection Param_io, NpgsqlCommand sqlCmd)
{
NpgsqlParameter param1 = new NpgsqlParameter();
if (Param_io == ParameterDirection.Input || Param_io == ParameterDirection.InputOutput)
param1.ParameterName = Param;
param1.NpgsqlDbType = Db_Type;
param1.Value = values;
param1.Direction = Param_io;
sqlCmd.Parameters.Add(param1);
}
public object GetParameterValue(NpgsqlCommand command, string parameterName)
{
return command.Parameters[parameterName].Value;
}
/// <summary>
/// sp의 OUTPUT 파라미터 값 읽기
/// int(string) return = (int/string)Return_Param("@return");
/// </summary>
/// <param name="param">"@param"</param>
/// <returns>object</returns>
public object GetParameter(string param)
{
return sqlCmd.Parameters[param].Value;
}
#endregion
#region Generating SqlCommand
private NpgsqlCommand PrepareCommand(CommandType commandType, string commandText)
{
if (connection == null)
{
connection = new NpgsqlConnection(this.connectionString);
}
if (connection.State == ConnectionState.Closed || connection.State == ConnectionState.Broken)
{
connection.Open();
}
NpgsqlCommand command = new NpgsqlCommand(commandText, connection);
command.CommandType = commandType;
return command;
}
private NpgsqlCommand PrepareCommand(CommandType commandType, string commandText, NpgsqlTransaction commandTransaction)
{
if (connection == null)
{
connection = new NpgsqlConnection(this.connectionString);
}
if (connection.State == ConnectionState.Closed || connection.State == ConnectionState.Broken)
{
connection.Open();
}
NpgsqlCommand command = new NpgsqlCommand(commandText, connection);
command.CommandType = commandType;
command.Transaction = commandTransaction;
return command;
}
public NpgsqlCommand GetStoreProcedureCommand(string spname)
{
return PrepareCommand(CommandType.StoredProcedure, spname);
}
public NpgsqlCommand GetStoreProcedureCommand(string spname, NpgsqlTransaction commandTransaction)
{
return PrepareCommand(CommandType.StoredProcedure, spname, commandTransaction);
}
public NpgsqlCommand GetSqlQueryCommand(string query)
{
return PrepareCommand(CommandType.Text, query);
}
#endregion
#region Direct Quer
public int DirectNonQuery(string query)
{
NpgsqlCommand sc = GetSqlQueryCommand(query);
int iResult = ExecuteNonQuery(sc);
return iResult;
}
/// <summary>
/// 직접 쿼리결과를 가져오는 함수
/// </summary>
/// <param name="sQuery"></param>
/// <returns></returns>
public DataTable DirectQuery(string query)
{
NpgsqlCommand sc = GetSqlQueryCommand(query);
return LoadDataTable(sc, string.Empty);
}
public DataTable DirectQuery(string query, string tableName)
{
NpgsqlCommand sc = GetSqlQueryCommand(query);
return LoadDataTable(sc, tableName);
}
/// <summary>
/// 쿼리명령어(Update/Insert/Delete)등 실행
/// </summary>
/// <param name="sQuery"></param>
/// <returns>Return 1(Success) / 0(Fail)</returns>
public int StringNonQuery(string sQuery)
{
return new clsPSQLHelper().DirectNonQuery(sQuery);
}
#endregion
#region Database Related Command
public int ExecuteNonQuery(NpgsqlCommand command)
{
try
{
return command.ExecuteNonQuery();
}
finally
{
command.Connection.Close();
command.Dispose();
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public int ExcuteNonQuery()
{
return sqlExcuteNonQuery(0);
}
/// <summary>
/// Sql Excute Command
/// Transaction: 1 begin transaction
/// Transaction: 0 No transaction
/// </summary>
/// <param name="Transaction">Transaction= 1 Or 0</param>
/// <returns>Return Effective Rows</returns>
public int sqlExcuteNonQuery(int Transaction)
{
try
{
sqlData.SelectCommand = sqlCmd;
sqlCmd.Connection = new NpgsqlConnection(this.connectionString);
sqlCmd.Connection.Open();
if (Transaction > 0)
sqlCmd.Transaction = sqlCmd.Connection.BeginTransaction(IsolationLevel.ReadCommitted);
int rtn = sqlCmd.ExecuteNonQuery();
if (Transaction > 0)
sqlCmd.Transaction.Commit();
sqlCmd.Connection.Close();
return rtn;
}
catch
{
if (Transaction > 0)
sqlCmd.Transaction.Rollback();
return -1;
}
finally
{
}
}
public object ExecuteScalar(NpgsqlCommand command)
{
return command.ExecuteScalar();
}
public NpgsqlDataReader ExecuteReader(NpgsqlCommand command)
{
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
public NpgsqlDataReader ExecuteReader(NpgsqlCommand command, CommandBehavior commandBehavior)
{
return command.ExecuteReader(commandBehavior);
}
public DataTable LoadDataTable(NpgsqlCommand command, string tableName)
{
using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(command))
{
try
{
using (DataTable dt = new DataTable(tableName))
{
da.Fill(dt);
return dt;
}
}
finally
{
command.Connection.Close();
command.Dispose();
}
}
}
public DataSet LoadDataSet(NpgsqlCommand command, string[] tableNames = null)
{
using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(command))
{
try
{
using (DataSet ds = new DataSet())
{
da.Fill(ds);
return ds;
}
}
finally
{
command.Connection.Close();
command.Dispose();
}
}
}
/*----Remove PostGres Refcursor table from result set---------*/
public DataSet RemovePostGresRefCursor(DataSet objdsData)
{
DataSet dsResult = new DataSet();
for (int i = 0; i < objdsData.Tables.Count; i++)
{
var dt = objdsData.Tables[i];
DataColumnCollection columns = dt.Columns;
if (!columns.Contains("p_refcur"))/*---get table that does not have postgres refcursor---------*/
{
dsResult.Tables.Add(dt.Copy());
}
}
return dsResult;
}
/// <summary>
/// DataSet 요청
/// </summary>
/// <returns>DataSet</returns>
public DataSet LoadDataSet()
{
//
DataSet ds = new DataSet();
try
{
sqlData.SelectCommand = sqlCmd;
sqlCmd.Connection = new NpgsqlConnection(this.connectionString);
sqlData.Fill(ds);
return ds;
}
catch
{
return null;
}
finally
{
}
}
public DataTable[] ExecuteDataset(CommandType commandType, string query, NpgsqlParameter[] npgsqlParameters)
{
using (NpgsqlCommand command = GetCommand(query, npgsqlParameters, commandType))
{
try
{
DataSet myDS = new DataSet();
NpgsqlTransaction t = command.Connection.BeginTransaction();
NpgsqlDataAdapter da = new NpgsqlDataAdapter(command);
da.Fill(myDS);
t.Commit();
DataTable[] tables = new DataTable[myDS.Tables.Count];
myDS.Tables.CopyTo(tables, 0);
return tables;
}
catch (Exception Ex)
{
throw Ex;
}
finally
{
command.Connection.Close();
}
}
}
private NpgsqlCommand GetCommand(string query, NpgsqlParameter[] npgsqlParameters, CommandType commandType)
{
NpgsqlConnection conn = new NpgsqlConnection(this.connectionString);
// conn.UseSslStream = false;
conn.Open();
query = query.ToLower();
NpgsqlCommand command = new NpgsqlCommand(query, conn);
command.CommandType = commandType;
if (npgsqlParameters is NpgsqlParameter[])
{
command.Parameters.AddRange(npgsqlParameters);
}
return command;
}
private NpgsqlTransaction PrepareTransaction(IsolationLevel isolationLevel)
{
if (connection == null)
{
connection = new NpgsqlConnection(this.connectionString);
}
if (connection.State == ConnectionState.Closed || connection.State == ConnectionState.Broken)
{
connection.Open();
}
return connection.BeginTransaction(isolationLevel);
}
public NpgsqlTransaction BeginTransaction()
{
return PrepareTransaction(IsolationLevel.ReadCommitted);
}
public NpgsqlTransaction BeginTransaction(IsolationLevel isolationLevel)
{
return PrepareTransaction(isolationLevel);
}
public void Commit(NpgsqlTransaction transaction)
{
if (transaction != null)
transaction.Commit();
}
public void RollBack(NpgsqlTransaction transaction)
{
if (transaction != null)
transaction.Rollback();
}
#endregion
#region IDisposable Members
public void Dispose()
{
GC.SuppressFinalize(this);
}
#endregion
#region Destructor
~clsPSQLHelper()
{
Dispose();
}
#endregion
void IDisposable.Dispose()
{
if (connection != null)
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
connection.Dispose();
}
}
}
}
}
Comments
Post a Comment