public static bool IsInMaintenanceMode()
{
bool inMaintenanceMode;
string connStr = "Data Source=KIM-MSI\\KIMSSQLSERVER;Initial Catalog=MVWDataBase;User ID=sa;Password=mis123;MultipleActiveResultSets=True";
if (MemoryCache.Default["MaintenanceMode"] == null)
{
Console.WriteLine("Hitting the database...");
CacheItemPolicy policy = new CacheItemPolicy();
SqlDependency.Start(connStr);
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand command = new SqlCommand("Select MaintenanceMode From dbo.MaintenanceMode", conn))
{
command.Notification = null;
SqlDependency dep = new SqlDependency();
dep.AddCommandDependency(command);
conn.Open();
inMaintenanceMode = (bool)command.ExecuteScalar();
SqlChangeMonitor monitor = new SqlChangeMonitor(dep);
policy.ChangeMonitors.Add(monitor);
dep.OnChange += Dep_OnChange;
}
}
MemoryCache.Default.Add("MaintenanceMode", inMaintenanceMode, policy);
}
else
{
inMaintenanceMode = (bool)MemoryCache.Default.Get("MaintenanceMode");
}
return inMaintenanceMode;
}
public DataTable SomeMethod(Action<object, SqlNotificationEventArgs> handler)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
// notificationas bus iskvieciamas visuomet, kai pasikeis bent vienas duomuo, is sio SELECTO grazinamu duomenu set`o (bus atliktas UPDATE, DELETE, INSERT)
using (SqlCommand command = new SqlCommand("SELECT StudentasID, Vardas, Pavarde, IBAN, AK FROM dbo.tblIseiviaiStudentai", connection))
{
dependency = new SqlDependency();
command.Notification = null;
dependency.AddCommandDependency(command);
dependency.OnChange += new OnChangeEventHandler(handler);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
// kad veiktu notificationas butini sie conection`o nustatymai, kitaip eventas bus kvieciamas nuolatos
var ON_PARAMETERS = @"SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ARITHABORT ON";
var OFF_PARAMETERS = @"SET NUMERIC_ROUNDABORT OFF";
var cmd_ON_PARAMETERS = new SqlCommand(ON_PARAMETERS, connection);
var cmd_OFF_PARAMETERS = new SqlCommand(OFF_PARAMETERS, connection);
cmd_ON_PARAMETERS.ExecuteNonQuery();
cmd_OFF_PARAMETERS.ExecuteNonQuery();
dataTable.Clear();
dataTable.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
dependency = null;
return dataTable;
}
}
}
public static void SetMonitor()
{
string connStr = ConfigurationManager.ConnectionStrings["lab"].ConnectionString;
CacheItemPolicy policy = new CacheItemPolicy();
SqlDependency.Start(connStr);
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand command = new SqlCommand("Select MaintenanceMode From dbo.MaintenanceMode", conn))
{
command.Notification = null;
SqlDependency dep = new SqlDependency();
dep.AddCommandDependency(command);
conn.Open();
bool inMaintenanceMode = (bool)command.ExecuteScalar();
SqlChangeMonitor monitor = new SqlChangeMonitor(dep);
policy.ChangeMonitors.Add(monitor);
dep.OnChange += Dep_OnChange;
MemoryCache.Default.Add("MaintenanceMode", inMaintenanceMode, policy);
}
}
}
void CreateSqlDep(SqlCommand sqlCmd) {
_sqlYukonDep = new SqlDependency();
// Note: sqlCmd is null in output cache case.
if (sqlCmd != null) {
Debug.Trace("SqlCacheDependency", "SqlCmd added to SqlDependency object");
_sqlYukonDep.AddCommandDependency(sqlCmd);
}
_sqlYukonDep.OnChange += new OnChangeEventHandler(OnSQL9SqlDependencyChanged);
Debug.Trace("SqlCacheDependency", "SQL9 dependency created: depId=" + _sqlYukonDep.Id);
}
void ISqlDependency.AddCommandDependency(SqlCommand command)
{
_dependency.AddCommandDependency(command);
}