using HotelPms.Share.IO;
|
using Npgsql;
|
using System.Data;
|
using System.Data.Common;
|
|
namespace HotelPms.Share.Data;
|
|
public class PostgreSqlNet : DataAccess
|
{
|
#region ★★★★★ Declartions ★★★★★
|
|
public string ConnectionString { get; set; } = string.Empty;
|
|
/// <summary>ユーザー</summary>
|
public override string UseID { get; set; } = string.Empty;
|
|
/// <summary>パスワード</summary>
|
public override string PassWord { get; set; } = string.Empty;
|
|
/// <summary>データベース所在のパソコン名</summary>
|
public override string DataSource { get; set; } = string.Empty;
|
|
/// <summary>データベース名</summary>
|
public override string Catalog { get; set; } = string.Empty;
|
|
public override string Schema { get; set; } = "public";
|
|
public override int Port { get; set; } = 5432;
|
|
/// <summary>
|
/// デッドロック関連： 1205,3635,5231,5252,17888,22840,23424
|
/// </summary>
|
public int Message_id { get; set; } = 0;
|
|
public string SqlState { get; set; } = string.Empty;
|
|
/// <summary>エラー情報</summary>
|
public override string ErrInfo { get; set; } = string.Empty;
|
|
/// <summary>エラーコード</summary>
|
public override int ErrNo { get; set; } = 0;
|
|
public bool PoolEnabled { get; set; } = false;
|
|
public int MaxPoolSize { get; set; } = 1000;
|
|
public int RetryCount { get; set; } = 12;
|
|
public int RetryInterval { get; set; } = 5;
|
|
/// <summary>
|
/// タイマー単位のSQL
|
/// </summary>
|
public bool IsTimerAccess { get; set; } = false;
|
|
public bool FireInfoMessageEventOnUserErrors { get; set; } = false;
|
|
public bool MessageEventEnabled { get; set; } = false;
|
|
#endregion
|
|
|
public PostgreSqlNet(string connectionString)
|
{
|
ConnectionString = connectionString;
|
NpgsqlConnectionStringBuilder ssb = new NpgsqlConnectionStringBuilder(connectionString);
|
DataSource = ssb.Host;
|
Port = ssb.Port;
|
UseID = ssb.Username;
|
PassWord = ssb.Password;
|
Catalog = ssb.Database;
|
MaxPoolSize = ssb.MaxPoolSize;
|
}
|
|
public PostgreSqlNet(DBConnectItem connecitem) : this(connecitem.HostName, connecitem.UserID, connecitem.Password, connecitem.Port, connecitem.DBName)
|
{ }
|
|
public PostgreSqlNet(string dataSource, string userID, string passWord) : this(dataSource, userID, passWord, 5432, "postgres")
|
{
|
}
|
|
public PostgreSqlNet(string dataSource, string userID, string passWord, int port) : this(dataSource, userID, passWord, port, "postgres")
|
{ }
|
|
public PostgreSqlNet(string dataSource, string userID, string passWord, int port, string catalog)
|
{
|
DataSource = dataSource;
|
Port = port;
|
UseID = userID;
|
PassWord = passWord;
|
Catalog = catalog;
|
ResetConnectionString();
|
}
|
|
public override void Dispose()
|
{
|
|
}
|
|
public void ResetConnectionString()
|
{
|
NpgsqlConnectionStringBuilder ssb = new NpgsqlConnectionStringBuilder();
|
ssb.Host = DataSource;
|
ssb.Port = Port;
|
ssb.Username = UseID;
|
ssb.Password = PassWord;
|
ssb.Database = Catalog;
|
ssb.Enlist = true;
|
//ssb.MaxPoolSize = MaxPoolSize;
|
ssb.ApplicationName = System.IO.Path.GetFileNameWithoutExtension(System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName);
|
ssb.Encoding = "UTF8";
|
//ssb.ApplicationName = System.IO.Path.GetFileNameWithoutExtension(Application.ExecutablePath);
|
ConnectionString = ssb.ToString();
|
}
|
|
public static bool CreateDataBase(DBConnectItem item)
|
{
|
using (PostgreSqlNet dbAccess = new PostgreSqlNet(item.HostName, item.UserID, item.Password, item.Port))
|
{
|
if (dbAccess.ErrNo != 0) { return false; }
|
return (dbAccess.ExecuteNonQuery($"CREATE DATABASE \"{item.DBName}\" ENCODING 'UTF8'") != -1);
|
}
|
}
|
|
/// 可変戻り値
|
/// </summary>
|
/// <typeparam name="T"></typeparam>
|
/// <param name="sql"></param>
|
/// <param name="fillReaderHandler"></param>
|
/// <returns></returns>
|
public override T GetDataReader<T>(string sql, Action<DbDataReader, T> Fetch)
|
{
|
OperationLog.Instance.WriteLog(string.Format("MsSqlNet.GetDataReader({0})", sql), GetLogLevel());
|
for (int i = 0; i < RetryCount; i++)
|
{
|
if (i > 0) { OperationLog.Instance.WriteLog(string.Format("{1}⇒MsSqlNet.GetDataReader({0})", sql, i), OperationLog.LogLevelType.Normal); }
|
try
|
{
|
ErrClear();
|
T t = new T();
|
using (NpgsqlConnection dbConnect = Open())
|
{
|
using (NpgsqlCommand command = new NpgsqlCommand(sql, dbConnect))
|
{
|
command.CommandTimeout = 6000;
|
using (NpgsqlDataReader reader = command.ExecuteReader(CommandBehavior.Default))
|
{
|
|
Fetch(reader, t);
|
} //自動クロス
|
}
|
}
|
return t;
|
}
|
catch (Exception ex)
|
{
|
if (!IsTransient(ex, sql)) { return default; }
|
}
|
Thread.Sleep(RetryInterval * 1000);
|
}
|
return default;
|
}
|
|
public override DataSet GetDataSet(string sql)
|
{
|
throw new NotImplementedException();
|
}
|
|
public override DataSet GetDataSetWithTran(string sql)
|
{
|
throw new NotImplementedException();
|
}
|
|
public override DataTable GetDataTable(string sql)
|
{
|
OperationLog.Instance.WriteLog(string.Format("GetDataTable({0})", sql), GetLogLevel());
|
for (int i = 0; i < RetryCount; i++)
|
{
|
if (i > 0) { OperationLog.Instance.WriteLog(string.Format("{1}⇒GetDataTable({0})", sql, i), OperationLog.LogLevelType.Normal); }
|
try
|
{
|
ErrClear();
|
using (NpgsqlConnection dbConnect = Open())
|
{
|
using (NpgsqlCommand command = new NpgsqlCommand(sql, dbConnect))
|
{
|
command.CommandTimeout = 60000;
|
DataTable dataTable = new DataTable();
|
using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter())
|
{
|
adapter.SelectCommand = command;
|
adapter.Fill(dataTable);
|
}
|
return dataTable;
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
if (!IsTransient(ex, sql)) { return null; }
|
}
|
Thread.Sleep(RetryInterval * 1000);
|
}
|
return null;
|
}
|
|
public override object ExecuteScalar(string sql)
|
{
|
OperationLog.Instance.WriteLog($"ExecuteScalar({sql})", GetLogLevel());
|
|
for (int i = 0; i < RetryCount; i++)
|
{
|
if (i > 0) { OperationLog.Instance.WriteLog($"{i}⇒ExecuteScalar({sql})", OperationLog.LogLevelType.Normal); }
|
try
|
{
|
ErrClear();
|
using (NpgsqlConnection dbConnect = Open())
|
{
|
using (NpgsqlCommand command = new NpgsqlCommand(sql, dbConnect))
|
{
|
return command.ExecuteReader();
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
if (!IsTransient(ex, sql)) { return null; }
|
}
|
Thread.Sleep(RetryInterval * 1000);
|
}
|
return null;
|
}
|
|
public override int ExecuteGetID(string sql)
|
{
|
throw new NotImplementedException();
|
}
|
|
public override int ExecuteNonQuery(string sql)
|
{
|
OperationLog.Instance.WriteLog($"ExecuteNonQuery({sql})", GetLogLevel());
|
|
for (int i = 0; i < RetryCount; i++)
|
{
|
if (i > 0) { OperationLog.Instance.WriteLog($"{i}⇒ExecuteNonQuery({sql})", OperationLog.LogLevelType.Normal); }
|
try
|
{
|
ErrClear();
|
using (NpgsqlConnection dbConnect = Open())
|
{
|
using (NpgsqlCommand command = new NpgsqlCommand(sql, dbConnect))
|
{
|
int row = command.ExecuteNonQuery();
|
return (row == -1 ? 0 : row);
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
if (!IsTransient(ex, sql)) { return -1; }
|
}
|
Thread.Sleep(RetryInterval * 1000);
|
}
|
return -1;
|
}
|
|
public override bool ExecuteNonQueryWithTran(string sql)
|
{
|
OperationLog.Instance.WriteLog($"ExecuteNonQuery({sql})", GetLogLevel());
|
|
for (int i = 0; i < RetryCount; i++)
|
{
|
if (i > 0) { OperationLog.Instance.WriteLog($"{i}⇒ExecuteNonQuery({sql})", OperationLog.LogLevelType.Normal); }
|
NpgsqlTransaction tran = null;
|
try
|
{
|
ErrClear();
|
using (NpgsqlConnection dbConnect = Open())
|
{
|
tran = dbConnect.BeginTransaction();
|
using (NpgsqlCommand command = new NpgsqlCommand(sql, dbConnect))
|
{
|
int row = command.ExecuteNonQuery();
|
if (row == -1)
|
{
|
tran.Rollback();
|
return false;
|
}
|
else
|
{
|
tran.Commit();
|
return true;
|
}
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
tran?.Rollback();
|
if (!IsTransient(ex, sql)) { return false; }
|
}
|
Thread.Sleep(RetryInterval * 1000);
|
}
|
return false;
|
}
|
|
public NpgsqlConnection Open()
|
{
|
NpgsqlConnection dbConnect = new NpgsqlConnection();
|
dbConnect.ConnectionString = ConnectionString;
|
dbConnect.Open();
|
return dbConnect;
|
}
|
|
/// <summary>
|
/// 一時的なエラー
|
/// </summary>
|
/// <param name="ex"></param>
|
/// <returns></returns>
|
private bool IsTransient(Exception ex, string sql)
|
{
|
NpgsqlException exSql = null;
|
if (ex is NpgsqlException)
|
{
|
exSql = ex as NpgsqlException;
|
Message_id = exSql.HResult;
|
ErrNo = exSql.ErrorCode;
|
SqlState = exSql.SqlState;
|
}
|
else
|
{
|
ErrNo = -1;
|
}
|
ErrInfo = ex.Message;
|
OperationLog.Instance.WriteLog(string.Format("IsTransient({0},{1},{2},{3})", Message_id, ErrNo, ErrInfo, sql), OperationLog.LogLevelType.Normal);
|
|
if (exSql != null)
|
{
|
//Specifies whether the exception is considered transient, that is, whether retrying the operation could succeed (e.g. a network error or a timeout).
|
if (exSql.IsTransient) { return true; }
|
if (exSql.SqlState == "40P01") { return true; } //デッドロックの検出
|
}
|
else if (ex is TimeoutException)
|
{
|
return true;
|
}
|
return false;
|
}
|
|
private void ErrClear()
|
{
|
ErrNo = 0;
|
ErrInfo = string.Empty;
|
SqlState = string.Empty;
|
Message_id = 0;
|
}
|
|
private OperationLog.LogLevelType GetLogLevel()
|
{
|
return IsTimerAccess ? OperationLog.LogLevelType.Level8 : OperationLog.LogLevelType.Level7;
|
}
|
|
/// <summary>
|
/// テーブル一覧を取得
|
/// ※一般的にschema = publicですが
|
/// </summary>
|
/// <returns></returns>
|
public override DataTable GetTableList()
|
{
|
return GetDataTable($"SELECT \"table_name\" AS \"Name\" FROM \"information_schema\".\"tables\" WHERE \"table_schema\"='{Schema}';");
|
}
|
|
/// <summary>
|
/// テーブル一覧を取得
|
/// </summary>
|
/// <returns></returns>
|
public override DataTable GetTableList(string whereAdding)
|
{
|
return GetDataTable($"SELECT \"table_name\" AS \"Name\" FROM \"information_schema\".\"tables\" WHERE \"table_schema\"='{Schema}' AND {whereAdding};");
|
}
|
}
|
