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/SqlBrokerListener.cs | 245 +++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 245 insertions(+), 0 deletions(-)
diff --git a/HotelPms.Share/Data/SqlBrokerListener.cs b/HotelPms.Share/Data/SqlBrokerListener.cs
new file mode 100644
index 0000000..b9d0cec
--- /dev/null
+++ b/HotelPms.Share/Data/SqlBrokerListener.cs
@@ -0,0 +1,245 @@
+using HotelPms.Share.Data.Script;
+using HotelPms.Share.Util;
+using System.Data;
+using System.Data.SqlClient;
+
+namespace HotelPms.Share.Data;
+
+/// <summary>
+/// ****************************** Description *******************************
+/// ◇システム名称
+/// @CoreCom
+/// ◇概要
+/// 施設設定画面
+/// ◇履歴
+/// 2007/11/02 小木 勝龍 新規作成
+/// ****************************** Declarations ******************************
+/// </summary>
+public class SqlBrokerListener : IDisposable
+{
+ #region ★★★★★ Declartions ★★★★★
+
+#if false
+CREATE TABLE [dbo].[S_BrokerMsg](
+ [ID] [int] NOT NULL,
+ [SeqNo] int null,
+ [ActionID] TINYINT NULL,
+ [ActionData] [nvarchar](500) NULL,
+ [PcName] [nvarchar](50) NULL,
+ [UserName] [nvarchar](50) NULL,
+ UpdateDate DATETIME,
+CONSTRAINT [PK_S_BrokerMsg] PRIMARY KEY CLUSTERED
+(
+ [ID] ASC
+))
+GO
+
+CREATE TABLE [dbo].[H_BrokerMsg](
+[ID] [int] IDENTITY(1,1) NOT NULL,
+ [BrokerID] [int] NULL,
+ [SeqNo] int null,
+ [ActionID] TINYINT NULL,
+ [ActionData] [nvarchar](500) NULL,
+ [PcName] [nvarchar](50) NULL,
+ [UserName] [nvarchar](50) NULL,
+ UpdateDate DATETIME,
+CONSTRAINT [PK_H_BrokerMsg] PRIMARY KEY CLUSTERED
+(
+ [ID] ASC
+))
+GO
+
+CREATE TRIGGER [dbo].[TR_AddBrokerLog]
+ON [dbo].[S_BrokerMsg]
+FOR INSERT, UPDATE
+AS
+BEGIN
+ IF @@ROWCOUNT = 0 RETURN
+ SET NOCOUNT ON
+ DELETE FROM [H_BrokerMsg] WHERE UpdateDate < DATEADD(DD, -7, GETDATE());
+ INSERT INTO [H_BrokerMsg]([PcName],[UserName],[SeqNo],[ActionID],[ActionData],[UpdateDate]) SELECT [PcName],[UserName],[SeqNo],[ActionID],[ActionData],[UpdateDate] FROM inserted;
+END
+GO
+
+ALTER TABLE [dbo].[S_BrokerMsg] ENABLE TRIGGER [TR_AddBrokerLog]
+GO
+#endif
+
+
+ /// <summary>
+ /// 非同期のため、使う時Invoke必要
+ /// </summary>
+ public event DataChangeEventHandler? DataChange;
+ private bool m_Disposed = false;
+ private string m_ConnectString = string.Empty;
+
+ #endregion
+
+ #region ★★★★★ Property ★★★★★
+
+ /// <summary>
+ /// メッセージデータのID
+ /// </summary>
+ public int ID { get; set; } = 1;
+
+ #endregion
+
+ #region ★★★★★ Class Event ★★★★★
+
+ public SqlBrokerListener(string hostName, string useID, string password, string dbName, int id)
+ {
+ ID = id;
+ m_ConnectString = string.Format("Data Source={0};User ID={1};Password={2};Initial Catalog={3};Application Name=SqlBrokerListener;", hostName, useID, password, dbName);
+ }
+
+ ~SqlBrokerListener()
+ {
+ Dispose(false);
+ }
+
+ protected virtual void Dispose(bool disposing)
+ {
+ if (!m_Disposed) //一回だけ
+ {
+ if (disposing)
+ {
+ //Managed Resources
+ SqlDependency.Stop(m_ConnectString);
+ }
+
+ //Unmanaged resources
+ m_Disposed = true;
+ }
+ }
+
+ public void Dispose()
+ {
+ Dispose(true);
+ GC.SuppressFinalize(this);
+ }
+
+ #endregion
+
+ #region ★★★★★ Private Function ★★★★★
+
+ private void Update(bool isFirst)
+ {
+ using (SqlConnection connection = new SqlConnection(m_ConnectString))
+ {
+ //依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[]
+ //using (SqlCommand command = new SqlCommand("select [Message] From [dbo].[Messages] WHERE ID = 1", connection))
+ using (SqlCommand command = new SqlCommand(string.Format("select [SeqNo] From [dbo].[S_BrokerMsg] WHERE ID = {0}", ID), connection))
+ {
+ //command.NotificationAutoEnlist = true;
+ command.Notification = null;
+ command.CommandType = CommandType.Text;
+ connection.Open();
+ SqlDependency dependency = new SqlDependency(command);
+ dependency.OnChange += dependency_OnChange;
+
+ using (DataTable dataTable = new DataTable())
+ {
+ using (SqlDataAdapter adapter = new SqlDataAdapter())
+ {
+ adapter.SelectCommand = command;
+ adapter.Fill(dataTable);
+ adapter.Dispose();
+ }
+
+ if (isFirst) { return; }
+ if (DataChange != null) { DataChange(this, new SqlDataChangeEventArgs(isFirst ? 0 : 1, false, dataTable)); }
+ }
+ }
+ }
+ }
+
+ private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
+ {
+ SqlDependency dependency = (SqlDependency)sender;
+ dependency.OnChange -= dependency_OnChange;
+
+ //if (m_OwnerForm.InvokeRequired)
+ //{
+ // m_OwnerForm.Invoke(new OnChangeEventHandler(dependency_OnChange), new object[] { sender, e });
+ //}
+ //else
+ //{
+ Update(false);
+ //}
+ }
+
+ #endregion
+
+ #region ★★★★★ Public Function ★★★★★
+
+ public void Execute()
+ {
+ SqlDependency.Start(m_ConnectString);
+ Update(true);
+ }
+
+ public static bool BrokerEnabled(string hostName, string useID, string password, string dbName)
+ {
+ try
+ {
+ using (MsSqlNet msSqlNet = new MsSqlNet(hostName, useID, password, "master"))
+ {
+ object ret = msSqlNet.ExecuteScalar(string.Format("SELECT is_broker_enabled FROM sys.databases WHERE name ='{0}'", dbName));
+ return CConvert.ToBool(ret);
+ }
+ }
+ catch
+ {
+ return false;
+ }
+ }
+
+ /// <summary>
+ /// インストール時、is_broker_enabledよりfalseの場合、初期化必要
+ /// </summary>
+ /// <param name="hostName"></param>
+ /// <param name="useID"></param>
+ /// <param name="password"></param>
+ /// <param name="dbName"></param>
+ /// <returns></returns>
+ public static bool InitBroker(string hostName, string useID, string password, string dbName)
+ {
+ try
+ {
+ string sql = string.Empty;
+
+ //MASTER登録
+ using (MsSqlNet msSqlNet = new MsSqlNet(hostName, useID, password, "master"))
+ {
+ if (!msSqlNet.ExistsProctrue("SP_CloseDBConnect"))
+ {
+ sql = ResourceFile.GetScript(ResourceFile.ResType.SP_CloseDBConnect);
+ if (msSqlNet.ExecuteNonQuery(sql) == -1) { return false; }
+ }
+
+ //ＤＢをクロス
+ //if (msSqlNet.ExecuteNonQuery(string.Format("EXECUTE SP_CloseDBConnect '{0}'", dbName)) == -1) { return false; }
+ msSqlNet.ExecuteNonQuery(string.Format("EXECUTE SP_CloseDBConnect '{0}'", dbName));
+
+ //Broker Enable
+ sql = string.Format(@"ALTER DATABASE {0} SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
+ALTER DATABASE {0} SET ENABLE_BROKER; ", dbName);
+ if (msSqlNet.ExecuteNonQuery(sql) == -1) { return false; }
+
+ //sa権限⇒ＤＢへ
+ if (msSqlNet.ExecuteNonQuery(string.Format("ALTER AUTHORIZATION ON DATABASE::{0} TO [sa];", dbName)) == -1) { return false; }
+
+ //CLR??
+
+ //成功チェック
+ return CConvert.ToBool(msSqlNet.ExecuteScalar(string.Format("SELECT is_broker_enabled FROM sys.databases WHERE name ='{0}'", dbName)));
+ }
+ }
+ catch
+ {
+ return false;
+ }
+ }
+
+ #endregion
+}
--
Gitblit v1.10.0