/// <summary>
/// Заполение DataTable в зависимости от введённого SQL запроса
/// </summary>
/// <param name="SQL_Select_Query">Переменная запроса SQL</param>
public Table_Class(string SQL_Select_Query)
{
command.Notification = null; //Отключение оповещений у команды
command.CommandText = SQL_Select_Query; //Присвение SQL запроса SQLCommand
Dependency.AddCommandDependency(command); //Присвоение команды в связку
//прослушивания
try
{
//Запуск прослушивания
SqlDependency.Start(Configuration_class.connection.ConnectionString);
//Открытие подключения
Configuration_class.connection.Open();
//Записть данных в табличном виде в виртулальную таблицу
table.Load(command.ExecuteReader());
}
catch (Exception ex)
{
//Вывод сообщения об ошибке
if (ex.Message != "Подключение не было закрыто. Подключение открыто." & ex.Message != "Недопустимая попытка вызвать MetaData при закрытом устройстве чтения.")
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
}
finally
{
//Закрытие подключения
Configuration_class.connection.Close();
}
}
/// <summary>
/// Заполнение DataTable в зависимости от введеного SQL запроса
/// </summary>
/// <param name="SQL_Select_Query"></param>
public Table_Class(String SQL_Select_Query)
{
command.Notification = null; //Отключение уведомлений
command.CommandText = SQL_Select_Query; //Присвоение SQL запроса SqlCommand
Dependency.AddCommandDependency(command); //Присвоение команд в связку
//прослушивание
try
{
//Запуск прослушиания
SqlDependency.Start(Configuration_class.connection.ConnectionString);
//Открытия подключения
Configuration_class.connection.Open();
//Запись данных в табличном виде в вирт. табл.
table.Load(command.ExecuteReader());
}
catch (Exception ex)
{
//Сообщение об ошибке
System.Windows.Forms.MessageBox.Show(ex.Message);
}
finally
{
//Закрытие подключения
Configuration_class.connection.Close();
}
}
public void AddCommandDependency()
{
SqlDependency sqlDependency = new SqlDependency();
SqlCommand sqlCommand = new SqlCommand("command");
sqlDependency.AddCommandDependency(sqlCommand);
}
/// <summary>
/// 设置具有SqlChangeMonitor策略的CacheItemPolicy（绝对或相对过期时间为分钟）
/// </summary>
/// <param name="connStr"></param>
/// <param name="sql"></param>
/// <param name="cacheTime"></param>
/// <param name="slidingExpiration"></param>
/// <returns></returns>
private CacheItemPolicy SetSqlPolicy(string connStr, string sql, int?cacheTime = null, int?slidingExpiration = null)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
command.Notification = null;
conn.Open();
var dependency = new SqlDependency(command);
dependency.AddCommandDependency(command);
var monitor = new SqlChangeMonitor(dependency);
var policy = new CacheItemPolicy();
policy.ChangeMonitors.Add(monitor);
command.ExecuteScalar();
if (cacheTime.HasValue)
{
policy.AbsoluteExpiration = DateTime.Now.AddMinutes(cacheTime.Value);
}
if (slidingExpiration.HasValue)
{
policy.SlidingExpiration = TimeSpan.FromMinutes(slidingExpiration.Value);
}
return(policy);
}
}
}
public void Set(string forUser, IEnumerable <Order> orders)
{
var policy = new CacheItemPolicy();
string connStr = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
SqlDependency.Start(connStr);
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand command = new SqlCommand("Select * From Orders", conn))
{
command.Notification = null;
SqlDependency dep = new SqlDependency();
dep.AddCommandDependency(command);
conn.Open();
SqlChangeMonitor monitor = new SqlChangeMonitor(dep);
policy.ChangeMonitors.Add(monitor);
}
cache.Set(prefix + forUser, orders, policy);
}
}
protected override DbCommand CreateDbCommand()
{
var command = _sqlConnection.CreateCommand();
_sqlDependency.AddCommandDependency(command);
return(command);
}
/// <summary>
/// Зполнение DataTAble в зависимосьти от введенного SQL запроса
/// </summary>
/// <param name="SQL_Select_Query"></param>
public Table_Class(string SQL_Select_Query)
{
command.Notification = null; //отключение оповещений у команды
command.CommandText = SQL_Select_Query; //присвоение SQL запроса
Dependency.AddCommandDependency(command); //присвоение команды в связку
//прослушивание
try
{
//запуск прослушивания
SqlDependency.Start(Configuration_class.connection.ConnectionString);
//открытия подключения
Configuration_class.connection.Open();
// записать данные в табличном виде в виртуальной таблице
table.Load(command.ExecuteReader());
}
catch (Exception ex)
{
//Вывод сообщения от ошибке
System.Windows.Forms.MessageBox.Show(ex.Message);
}
finally
{
//Закрытие подключения
Configuration_class.connection.Close();
}
}
/// <summary>
/// 监视表的数据
/// </summary>
private static void MonitorItem(string tableName)
{
var conn = new SqlConnection(SqlServerOnChangeNotice.connectionString);
SqlDataReader dr = null;
SqlCommand cmd = null;
try
{
conn.Open();
cmd = conn.CreateCommand();
cmd.CommandText = @"select c.name from sys.columns c,sys.objects o
where
c.object_id=o.object_id and
o.name='" + tableName + "'";
List <string> columns = new List <string>();
using (SqlDataReader colsdr = cmd.ExecuteReader())
{
while (colsdr.Read())
{
columns.Add(colsdr["name"].ToString());
}
}
string cols = string.Join(",", columns.ToArray());
cols = cols.TrimEnd(",".ToCharArray());
cmd.CommandText = "SELECT " + cols + " FROM dbo." + tableName;
SqlDependency dependency = new SqlDependency();
dependency.AddCommandDependency(cmd);
dic.Add(dependency.Id, tableName);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
dr = cmd.ExecuteReader();
}
finally
{
if (dr != null)
{
dr.Close();
dr.Dispose();
dr = null;
}
if (cmd != null)
{
cmd.Dispose();
cmd = null;
}
if (conn != null)
{
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
conn.Dispose();
conn = null;
}
}
}
//public void dbEnter(string login, string password)
//{
// command.CommandText = "SELECT count(*) FROM [dbo].[Employee] " +
// "where [Employee_Login] = '" + login + "' and [Employee_Password] = '" +
// password + "'";
// connection.Open();
// IDuser = Convert.ToInt32(command.ExecuteScalar().ToString());
// connection.Close();
//}
// Функция на вывод данных из таблицы БД
private void dtFill(DataTable table, string query)
{
command.Notification = null;
Dependency.AddCommandDependency(command);
SqlDependency.Start(connection.ConnectionString);
command.CommandText = query; // Кладем запрос в переменную
connection.Open(); // Открываем подключение
table.Load(command.ExecuteReader()); // Считывание таблицы баз данных
connection.Close(); // ЗАкрываем подключение
}
private void DtFill(DataTable table, string query)
{
command.Notification = null;
Dependency.AddCommandDependency(command);
SqlDependency.Start(Connection.ConnectionString);
command.CommandText = query;
Connection.Open();
table.Load(command.ExecuteReader());
Connection.Close();
}
public void AddCommandDependencyHasChanges()
{
SqlDependency dep = new SqlDependency();
SqlCommand cmd = new SqlCommand("command");
Type sqlDependencyType = typeof(SqlDependency);
FieldInfo dependencyFiredField = sqlDependencyType.GetField("_dependencyFired", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance);
dependencyFiredField.SetValue(dep, true);
dep.AddCommandDependency(cmd);
}
private void dtFill(DataTable table, string query)
{
command.CommandText = query;
//Технология "Real Time"
command.Notification = null;
Dependency.AddCommandDependency(command);
SqlDependency.Start(Configuration_Class.connection.ConnectionString);
Configuration_Class.connection.Open();
table.Load(command.ExecuteReader());
Configuration_Class.connection.Close();
}
public DataTable GetSQLContents()
{
string strDBCon = ConfigurationManager.ConnectionStrings["SQLServer"].ToString();
string sqlFilePath = ConfigurationManager.AppSettings["SQLFile"].ToString();
string timeout = ConfigurationManager.AppSettings["CacheTimeOut"].ToString();
ObjectCache cache = MemoryCache.Default;
DataTable sqlContents = cache[KEY_SQL_CONTENTS] as DataTable;
if (sqlContents == null)
{
CacheItemPolicy policy = new CacheItemPolicy();
policy.AbsoluteExpiration = DateTimeOffset.Now.AddSeconds(Convert.ToDouble(timeout));
try
{
SqlDependency.Start(strDBCon);
SqlDependency dep = new SqlDependency();
using (SqlConnection con = new SqlConnection(strDBCon))
{
string sql = ReadFile(sqlFilePath);
using (SqlCommand command = new SqlCommand(sql, con))
{
con.Open();
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(sqlContents);
dep.AddCommandDependency(command);
policy.ChangeMonitors.Add(new SqlChangeMonitor(dep));
}
}
}
catch (Exception ex)
{
throw ex;
}
// Fetch the SQL contents.
cache.Set(KEY_SQL_CONTENTS, sqlContents, policy);
}
return(sqlContents);
}
private void dtFill(string query, DataTable table)
{
//try
//{
command.Notification = null;
command.CommandText = query;
dependency.AddCommandDependency(command);
SqlDependency.Start(sqlConnection.ConnectionString);
sqlConnection.Open();
table.Load(command.ExecuteReader());
//}
//catch (Exception ex)
//{
//}
//finally
//{
sqlConnection.Close();
//}
}
private void dtvFill(DataTable table, string query)
{
command.CommandText = query;
command.Notification = null;
dependency.AddCommandDependency(command);
try
{
SqlDependency.Start(Registry_Class.sqlConnection.ConnectionString);
Registry_Class.sqlConnection.Open();
table.Load(command.ExecuteReader());
}
catch (SqlException ex)
{
Registry_Class.error_message += "\n" + DateTime.Now.ToLongDateString() + " " + ex.Message;
}
finally
{
Registry_Class.sqlConnection.Close();
}
}
static public bool IsInMaintenanceMode()
{
bool inMaintenanceMode;
if (MemoryCache.Default["MaintenanceMode"] == null)
{
CacheItemPolicy policy = new CacheItemPolicy();
string connStr = "DB CONNECTION STRING";
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);
}
}
MemoryCache.Default.Add("MaintenanceMode", inMaintenanceMode, policy);
}
else
{
inMaintenanceMode = (bool)MemoryCache.Default.Get("MaintenanceMode");
}
return(inMaintenanceMode);
}
private void DataTableFill(DataTable table, string query)
{
try
{
table.Clear();
command.Notification = null;
command.CommandText = query;
dependency.AddCommandDependency(command);
SqlDependency.Start(RegistryData.DBConnectionString.ConnectionString);
RegistryData.DBConnectionString.Open();
table.Load(command.ExecuteReader());
}
catch (Exception ex)
{
RegistryData.ErrorMessage += "\n" + DateTime.Now.ToLongDateString() + ex.Message;
}
finally
{
RegistryData.DBConnectionString.Close();
}
}
public void DataTableFill(DataTable table, string query)
{
try
{
table.Clear();
command.Notification = null;
command.CommandText = query;
dependency.AddCommandDependency(command);
SqlDependency.Start(Registry_Class.sqlConnection.ConnectionString);
Registry_Class.sqlConnection.Open();
table.Load(command.ExecuteReader());
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
Registry_Class.sqlConnection.Close();
}
}
static void UpdateCache()
{
var policy = new CacheItemPolicy();
policy.RemovedCallback = (s) => UpdateCache();
var connectionString =
ConfigurationManager.ConnectionStrings["Default"]
.ConnectionString;
SqlDependency.Start(connectionString);
using (var conn = new SqlConnection(connectionString))
{
using (SqlCommand command =
new SqlCommand("SELECT PersonType FROM Person.Person WHERE PersonType = 'EM'",
conn))
{
SqlDependency sqlDependency = new SqlDependency();
sqlDependency.AddCommandDependency(command);
//sqlDependency.OnChange += (s, x) => UpdateCache();
ChangeMonitor sqlMonitor = new SqlChangeMonitor(sqlDependency);
conn.Open();
command.ExecuteScalar();
var empCount = GetCurrentEmployeeCount();
policy.ChangeMonitors.Add(sqlMonitor);
cache.Add("employee_count", empCount, policy);
Console.WriteLine(string.Format("Updated Employee count: {0}", empCount));
}
}
}
public bool IsCacheValidForProducts()
{
bool isValid;
if (MemoryCache.Default["OwnMonitoring"] == null)
{
string connectionString = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
CacheItemPolicy policy = new CacheItemPolicy();
SqlDependency.Start(connectionString);
using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand("select count(*) from [Northwind].[dbo].[Products]", connection))
{
command.Notification = null;
SqlDependency dependency = new SqlDependency();
dependency.AddCommandDependency(command);
connection.Open();
isValid = ((int)command.ExecuteScalar() == rowCount);
SqlChangeMonitor monitor = new SqlChangeMonitor(dependency);
policy.ChangeMonitors.Add(monitor);
connection.Close();
}
MemoryCache.Default.Add("OwnMonitoring", isValid, policy);
}
else
{
isValid = (bool)MemoryCache.Default.Get("OwnMonitoring");
}
return(isValid);
}
///<summary>
///Заполнение таблицы в зависимости от запроса
///</summary>
///<param_name="SQL_Select_Querry">Переменная запроса</param_name>
public Table_Class(string SQL_Select_Querry)
{
command.Notification = null; //Отключение оповещений для прослушивания
command.CommandText = SQL_Select_Querry; //Перевод строки запроса в комманду
Dependency.AddCommandDependency(command); //Прослушивание БД по команде
try
{
//Запуск прослушивания
SqlDependency.Start(Configuration_class.connection.ConnectionString);
//Открытие подключения
Configuration_class.connection.Open();
//Запись данных в таблицу
table.Load(command.ExecuteReader());
}
catch (Exception ex)
{
//Вывод сообщения об ошибке
System.Windows.Forms.MessageBox.Show(ex.Message);
}
finally
{
Configuration_class.connection.Close();
}
}
public void AddCommandDependencyNull_Throws()
{
SqlDependency sqlDependency = new SqlDependency();
ArgumentNullException ex = Assert.Throws <ArgumentNullException>(() => sqlDependency.AddCommandDependency(null));
Assert.Contains("null", ex.Message, StringComparison.OrdinalIgnoreCase);
}
