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
|
}
|
