From 1a1c8e71fcd14858f595029f089b2d4a00202b32 Mon Sep 17 00:00:00 2001
From: ogi <Administrator@S-OGI-PC>
Date: Fri, 05 Dec 2025 09:24:16 +0900
Subject: [PATCH] プロジェクトファイルを追加。
---
HotelPms.Share/Data/PostgreSqlNet.cs | 381 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 381 insertions(+), 0 deletions(-)
diff --git a/HotelPms.Share/Data/PostgreSqlNet.cs b/HotelPms.Share/Data/PostgreSqlNet.cs
new file mode 100644
index 0000000..16374d5
--- /dev/null
+++ b/HotelPms.Share/Data/PostgreSqlNet.cs
@@ -0,0 +1,381 @@
+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};");
+ }
+}
--
Gitblit v1.10.0