/// <summary>
///
/// </summary>
/// <param name="domain"></param>
/// <returns></returns>
public static WebInfo GetCacheWebInfoByDomain(string domain)
{
string cacheKey = string.Format(WEBINFO_DOMAIN_CACHEKEY, domain);
WebInfo _webinfo = new WebInfo();
_webinfo = (WebInfo)viviapi.Cache.WebCache.GetCacheService().RetrieveObject(cacheKey);
if (_webinfo == null)
{
IDictionary <string, object> sqldepparms = new Dictionary <string, object>();
sqldepparms.Add("domain", domain);
SqlDependency sqlDep = DataBase.AddSqlDependency(cacheKey, SQL_TABLE, SQL_TABLE_FIELD, "[domain]=@domain", sqldepparms);
_webinfo = GetWebInfoByDomain(domain);
if (_webinfo == null)
{
return(null);
}
viviapi.Cache.WebCache.GetCacheService().AddObject(cacheKey, _webinfo);
}
return(_webinfo);
}
private void RegisterForChanges()
{
//Remove old dependency object
this.Command.Notification = null;
//Create new dependency object
SqlDependency dep = new SqlDependency(this.Command);
dep.OnChange += new OnChangeEventHandler(Handle_OnChange);
//Save data
Result = new DataSet();
Adapter.Fill(Result);
//Notify client of change to DataSet
switch (NotificationType)
{
case SqlWatcherNotificationType.Blocking:
OnChange(Result);
break;
case SqlWatcherNotificationType.Threaded:
ThreadPool.QueueUserWorkItem(ChangeEventWrapper, Result);
break;
}
}
private void Subscribe2()
{
using (var conn = new SqlConnection(configuration.GetConnectionString("DefaultConnection")))
{
conn.Open();
using (var command = conn.CreateCommand())
{
command.CommandText = "select Id, Value from dbo.Message2s";
command.Notification = null;
var dependency = new SqlDependency(command);
dependency.OnChange += Dependency_OnChange2;
try
{
command.ExecuteNonQuery();
}
catch (SqlException ex) when(ex.Errors[0].Number == 9202)
{
// http://answers.flyppdevportal.com/MVC/Post/Thread/4c367faa-4723-4994-a28b-6b7d2d2e441b?category=sqlservicebroker
// Error 9202 は無視しても大丈夫
}
}
}
}
public IEnumerable <Category> GetCategories()
{
Console.WriteLine("Get Categories");
var user = Thread.CurrentPrincipal.Identity.Name;
var categories = _cache.Get(user);
if (categories == null)
{
Console.WriteLine("From DB");
string connectionString;
using (var dbContext = new Northwind())
{
dbContext.Configuration.LazyLoadingEnabled = false;
dbContext.Configuration.ProxyCreationEnabled = false;
categories = dbContext.Categories.ToList();
connectionString = dbContext.Database.Connection.ConnectionString;
}
SqlDependency.Start(connectionString);
_cache.Set(user, categories, GetMonitor(_command, connectionString));
}
return(categories);
}
public void Dispose()
{
lock (_stopLocker)
{
_disposing = true;
}
#if NET451
if (_notificationState != NotificationState.Disabled)
{
try
{
SqlDependency.Stop(ConnectionString);
}
catch (Exception) { }
}
#endif
if (Interlocked.Read(ref _notificationState) == NotificationState.ProcessingUpdates)
{
_stopHandle.Wait();
}
_stopHandle.Dispose();
}
private static void RegisterDependency()
{
//We have selected the entire table as the command, so SQL Server executes this script and sees if there is a change in the result, raise the event
string commandText = string.Format("Select LogEventID, LogCreateDate FROM dbo.EventLog WHERE LogPortalID = {0}", PortalId);
//Start the SQL Dependency
SqlDependency.Start(connectionString);
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(commandText, connection))
{
connection.Open();
var sqlDependency = new SqlDependency(command);
sqlDependency.OnChange += new OnChangeEventHandler(sqlDependency_OnChange);
// NOTE: You have to execute the command, or the notification will never fire.
using (SqlDataReader reader = command.ExecuteReader())
{
}
}
}
}
///// <summary>
///// 得到一个对象实体，从缓存中
///// </summary>
public viviapi.Model.Supplier.SupplierAccount GetCacheModelByDomain(int code, string domain)
{
var model = new Model.Supplier.SupplierAccount();
string cacheKey = string.Format(CacheKey, code, domain);
model = (Model.Supplier.SupplierAccount)Cache.WebCache.GetCacheService().RetrieveObject(cacheKey);
if (model == null)
{
IDictionary <string, object> sqldepparms = new Dictionary <string, object>();
sqldepparms.Add("code", code);
sqldepparms.Add("domain", domain);
SqlDependency sqlDep = DataBase.AddSqlDependency(cacheKey, SqlTable, SqlTableFields, "[code]=@code AND domain=@domain", sqldepparms);
model = GetModelByDomain(code, domain);
viviapi.Cache.WebCache.GetCacheService().AddObject(cacheKey, model);
}
return(model);
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
//MessageBox.Show("modification Occurred");
ISynchronizeInvoke i = (ISynchronizeInvoke)this;
if (i.InvokeRequired)
{
OnChangeEventHandler tempDelegate = new OnChangeEventHandler(dependency_OnChange);
object[] args = { sender, e };
i.BeginInvoke(tempDelegate, args);
return;
}
SqlDependency dependency = (SqlDependency)sender;
dependency.OnChange -= dependency_OnChange;
//++changeCount;
//lblChanges.Text = String.Format(statusMessage, changeCount);
GetData();
SearchAndPaginationFingerprint();
}
public void Set(string forUser, IEnumerable <NorthwindLibrary.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);
}
}
public IEnumerable <T> GetEntities()
{
Console.WriteLine("Get Entities");
var user = Thread.CurrentPrincipal.Identity.Name;
var entities = _cache.Get(user);
if (entities == null)
{
Console.WriteLine("From no cache storage");
string connectionString;
using (var dbContext = new Northwind())
{
dbContext.Configuration.LazyLoadingEnabled = false;
dbContext.Configuration.ProxyCreationEnabled = false;
entities = dbContext.Set <T>().ToList();
connectionString = dbContext.Database.Connection.ConnectionString;
}
SqlDependency.Start(connectionString);
_cache.Set(user, entities, GetCachePolicy(_monitorCommand, connectionString));
}
return(entities);
}
public ActionResult Get()
{
using (var sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDbContext"].ConnectionString))
{
using (SqlCommand command = new SqlCommand("SELECT * FROM PRODUCTS", sqlConnection))
{
command.Notification = null;
SqlDependency sqlDependency = new SqlDependency(command);
sqlDependency.OnChange += SqlDependency_OnChange;
sqlConnection.Open();
SqlDataReader reader = command.ExecuteReader();
var list = reader.Cast <IDataRecord>().Select(x => new
{
Id = (int)x["Id"],
Name = (string)x["Name"],
Buying = (decimal)x["BuyingPrice"],
Selling = (decimal)x["SellingPrice"]
}).ToList();
return(Json(list, JsonRequestBehavior.AllowGet));
}
}
}
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();
CommandOpenKey.ExecuteNonQuery();
table.Load(command.ExecuteReader());
CommandCloseKey.ExecuteNonQuery();
}
catch (Exception ex)
{
RegistryData.ErrorMessage += "\n" + DateTime.Now.ToLongDateString() + ex.Message;
}
finally
{
RegistryData.DBConnectionString.Close();
}
}
public bool EnsureInitialized()
{
lock (_synLock) {
if (!_initialized)
{
try {
// call only once per connectionstring when appdomain starts up
Logger.Information("Starting SqlDependency.");
SqlDependency.Start(_shellSettings.DataConnectionString);
_worker = _workerFactory();
_worker.Work();
_initialized = true;
}
catch (Exception e) {
Logger.Error(e, "The application doesn't have the permission to request notifications.");
}
}
return(_initialized);
}
}
private void Unprime()
{
lock (_lock)
{
if (!_primed)
{
return;
}
if (_cmd == null)
{
return;
}
if (_dependency == null)
{
return;
}
_dependency.OnChange -= Handle;
_dependency = null;
_cmd.Dispose();
_cmd = null;
_primed = false;
}
}
protected void Application_Start()
{
SqlConnectionString = ConfigurationManager.ConnectionStrings[FingerprintsData.connection.ConnectionString].ConnectionString;
AreaRegistration.RegisterAllAreas();
WebApiConfig.Register(GlobalConfiguration.Configuration);
FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
RouteConfig.RegisterRoutes(RouteTable.Routes);
BundleConfig.RegisterBundles(BundleTable.Bundles);
// RouteTable.Routes.MapConnection<ExecutiveDashboardTickerConnection>("executiveDashboardTicker", "\executiveDashboardTicker");
if (!String.IsNullOrEmpty(SqlConnectionString))
{
try
{
SqlDependency.Start(SqlConnectionString);
}
catch (Exception ex)
{
FingerprintsModel.clsError.WriteException(ex);
}
}
}
/// <summary>
/// Initializes the sql dependency for the current user
/// </summary>
/// <param name="username"></param>
private void InitializeUsersSqlDependency()
{
if (this._connectedUserName == null)
{// return if no user connected
return;
}
// create sql dependency for users table
using (SqlConnection con = new SqlConnection(this.ConnectionString))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(@"select username
from dbo.users where username<>@connectedUser",
con))
{
cmd.Notification = null;
cmd.Parameters.Add(new SqlParameter("connectedUser", _connectedUserName));
SqlDependency dependency = new SqlDependency(cmd);
dependency.OnChange += new OnChangeEventHandler(dependency_user_OnChange);
cmd.ExecuteReader();
}
}
}
/// <summary>
/// Obtener todos los cuestionarios
/// </summary>
/// <returns></returns>
public IEnumerable <Cuestionario2> Cuestionarios()
{
var messages = new List <Cuestionario2>();
using (var connection = new SqlConnection(conString))
{
connection.Open();
using (var command = new SqlCommand(@"SELECT [IdCuestionario],
[Nombre], [Valor], [Estatus] FROM [dbo].[REM_CUESTIONARIO]", connection))
{
command.Notification = null;
var dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
var reader = command.ExecuteReader();
while (reader.Read())
{
messages.Add(item: new Cuestionario2
{
IdCuestionario = (int)reader["IdCuestionario"],
Nombre = (string)reader["Nombre"],
Valor = (int)reader["Valor"],
Estatus = (bool)reader["Estatus"]
});
}
}
}
return(messages);
}
public static List <Scope> GetAllCompletedScopes()
{
using (
var connection =
new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
connection.Open();
using (
SqlCommand command =
new SqlCommand(@"SELECT Id,ScopeTypeId,ScopeStatusId,UpdatedAt,UserId,ScopeTypeId
FROM [Foundation].[Scopes] WHERE ScopeStatusId = 8 AND (IsNotified =0 OR IsNotified is NULL)", connection)
)
{
command.Notification = null;
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
using (var reader = command.ExecuteReader())
return(reader.Cast <IDataRecord>()
.Select(x => new Scope()
{
Id = x.GetString(0),
ScopeTypeId = x.GetInt32(1),
ScopeStatusId = x.GetInt32(2),
UpdatedAt = DateTime.Now,
UserId = x.GetInt32(4)
}).ToList());
}
}
}
public void AutoRefresh(Type dbSetType)
{
var oldCookie = CallContext.GetData(SqlDependencyCookie);
try
{
SqlDependency.Start(this.Database.Connection.ConnectionString);
var autoRefreshArgs = this.AutoRefreshArgsDictionary.GetOrAdd(dbSetType, type => new AutoRefreshArgs(dbSetType, this.GetSqlCommandForAutoRefrest(dbSetType)));
if (autoRefreshArgs.SqlConnection != null)
{
return; //duplicit call for refresh
}
var sqlNotificationCommand = autoRefreshArgs.SqlCommand;
sqlNotificationCommand.CommandType = CommandType.Text;
sqlNotificationCommand.Notification = null;
var sqlConnection = new SqlConnection(this.Database.Connection.ConnectionString); //cannot be in using because it would close channel for backwards notification
autoRefreshArgs.SqlConnection = sqlConnection;
sqlConnection.Open();
sqlNotificationCommand.Connection = sqlConnection;
var dependency = new SqlDependency(sqlNotificationCommand);
this.Connections.Add(dependency.Id, autoRefreshArgs);
CallContext.SetData(SqlDependencyCookie, dependency.Id);
dependency.OnChange += this.Dependency_OnChange;
var reader = sqlNotificationCommand.ExecuteReaderAsync().Result;
Debugger.Log(0, "Debug", "AUTOREFRESH COLLECTION " + dbSetType.BaseType);
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
finally { CallContext.SetData(SqlDependencyCookie, oldCookie); }
}
public List <Training> GetTrainingDetails()
{
List <Training> trainings = new List <Training>();
String query = "select [dbo].[Training].[TrainingName],[dbo].[Training].[Lvl], [dbo].[Training].[CourseID], [dbo].[Training].[TrainingID], [dbo].[Training].[StartDate], [dbo].[Training].[EndDate],[dbo].[Training].[Credits] from [dbo].[Training] ";
using (var command = new SqlCommand(query, con))
{
var dependency = new SqlDependency(command);
dependency.OnChange -= new OnChangeEventHandler(dependency_OnChange);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable dTable = ds.Tables[0];
foreach (DataRow row in dTable.Rows)
{
Training tr = new Training();
tr.TrainingID = int.Parse(row["TrainingID"].ToString());
tr.StartDate = DateTime.Parse(row["StartDate"].ToString());
tr.EndDate = DateTime.Parse(row["EndDate"].ToString());
tr.Credits = int.Parse(row["Credits"].ToString());
tr.courseID = int.Parse(row["CourseID"].ToString());
tr.Name = row["TrainingName"].ToString();
tr.Level = int.Parse(row["Lvl"].ToString());
trainings.Add(tr);
}
}
}
return(trainings);
}
public IEnumerable <Message> GetAllMessages()
{
var messages = new List <Message>();
using (var connection = new SqlConnection(_connString))
{
connection.Open();
using (var command = new SqlCommand(@"SELECT [Id],
[Rfid], [Cf], [Date], [Payment] FROM [dbo].[Messages] WHERE readed = 0", connection))
{
command.Notification = null;
var dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
var reader = command.ExecuteReader();
while (reader.Read())
{
messages.Add(item: new Message
{
Id = (int)reader["Id"],
Rfid = reader["Rfid"] != DBNull.Value ? (string)reader["Rfid"] : "",
Cf = reader["Cf"] != DBNull.Value ? (string)reader["Cf"] : "",
Payment = reader["Payment"] != DBNull.Value ? (string)reader["Payment"] : "",
Date = Convert.ToDateTime(reader["Date"])
});
}
}
}
return(messages);
}
public List <Shops> GetAllShops()
{
var shops = new List <Shops>();
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlDependency.Start(connectionString);
string commandText = "select * from dbo.Shops";
SqlCommand cmd = new SqlCommand(commandText, conn);
SqlDependency dependency = new SqlDependency(cmd);
dependency.OnChange += new OnChangeEventHandler(dbChangeNotification);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var shop = new Shops
{
id = Convert.ToInt32(reader["id"]),
//Name = reader["Name"].ToString(),
//Age = Convert.ToInt32(reader["Age"])
};
shops.Add(shop);
}
conn.Close();
}
return(shops);
}
public static IEnumerable <NotificationDTO> GetMessages(string username)
{
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(@"SELECT [MessageId],[ToUserName],[FromUserName],[FromUserNameImage],[Subject],[Text],[DateSent],[IsRead]
FROM [dbo].[Messages] WHERE [ToUserName] = '" + username + "' ORDER BY [DateSent] DESC", connection))
{
// Make sure the command object does not already have
// a notification object associated with it.
command.Notification = null;
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
using (var reader = command.ExecuteReader())
return(reader.Cast <IDataRecord>()
.Select(x => new NotificationDTO()
{
MessageId = x.GetInt32(0),
ToUserName = x.GetString(1),
FromUserName = x.GetString(2),
FromImage = x.GetString(3),
Subject = x.GetString(4),
Text = x.GetString(5),
DateSent = x.GetDateTime(6),
IsRead = x.GetBoolean(7)
}).ToList());
}
}
}
private void SqlDependencyWatch()
{
string sSQL = "SELECT [Id],[FactoryID],[EquipmentID],[Type],[Message],[State],[CreatorTime],[CreatorUserId],[ReceiveTime],[DTUID] FROM[dbo].[Task_Request] where [state] = 0";
SqlConnection connection = null;
SqlCommand command = null;
try
{
using (connection = new SqlConnection(ConnTaskModel))
{
using (command = new SqlCommand(sSQL, connection))
{
connection.Open();
command.CommandType = CommandType.Text;
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(SqlTableOnChange);
SqlDataReader sdr = command.ExecuteReader();
if (sdr.Read())
{
this.TextReport(sdr["DTUID"].ToString(), sdr["Message"].ToString());
}
}
}
}
catch (Exception)
{
throw;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
public IEnumerable <MessagesView> GetAllMessages()
{
var messagesView = new List <MessagesView>();
using (var connection = new SqlConnection(_connString))
{
connection.Open();
using (var command = new SqlCommand(@"SELECT obsTimeUtc,humidityHigh,humidityLow,humidityAvg FROM [dbo].[WeatherSummaries]", connection))
{
command.Notification = null;
var dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
var reader = command.ExecuteReader();
while (reader.Read())
{
messagesView.Add(item: new MessagesView
{
obsTimeUtc = reader["obsTimeUtc"].ToString(),
humidityAvg = Int32.Parse(reader["humidityAvg"].ToString()),
humidityHigh = Int32.Parse(reader["humidityHigh"].ToString()),
humidityLow = Int32.Parse(reader["humidityLow"].ToString()),
});
}
}
}
var newList = messagesView.OrderByDescending(c => c.obsTimeUtc).ToList();
return(newList);
}
private void Initialize()
{
try
{
SqlDependency.Start(_connString, _queue);
}
catch (Exception ex)
{
if (ex is KeyNotFoundException)
{
LogErrors(ex, "Attempting service queue cleanup");
string _sqlcmdstring = "CleanupQueue";
try
{
using (_conn = new SqlConnection(_connString))
{
_conn.Open();
SqlCommand cmd = new SqlCommand(_sqlcmdstring, _conn);
cmd.ExecuteNonQuery();
}
SqlDependency.Start(_connString, _queue);
LastUpdated = DateTime.Now;
_logger.LogInformation("Cleanup successful");
}
catch (Exception e2)
{
LogErrors(e2);
LastUpdated = DateTime.MinValue;
}
}
else
{
LogErrors(ex);
LastUpdated = DateTime.MinValue;
}
}
}
private void LoadRollInfoData()
{
try
{
dsRollInfo.Clear();
cmdRollInfo.Notification = null;
SqlDependency cmdRollInfoDependency = new SqlDependency(cmdRollInfo);
cmdRollInfoDependency.OnChange += new OnChangeEventHandler(cmdRollInfoDependency_OnChange);
using (SqlDataAdapter adapter = new SqlDataAdapter(cmdRollInfo))
adapter.Fill(dsRollInfo);
foreach (DataRow dr in dsRollInfo.Tables[0].Rows)
{
switch (dr["Status"].ToString())
{
case "C":
RollInfo.CurrentRoll = dr["RollNumber"].ToString();
RollInfo.CurrentRollID = Convert.ToInt32(dr["RollID"]);
lblCurrentRoll.Text = RollInfo.CurrentRoll;
ClearScreen();
break;
case "N":
RollInfo.NextRoll = dr["RollNumber"].ToString();
RollInfo.NextRollID = Convert.ToInt32(dr["RollID"]);
lblNextRoll.Text = RollInfo.NextRoll;
break;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public List <temperature> GetAllTemperatures()
{
var luminanceList = new List <temperature>();
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlDependency.Start(connectionString);
string cmdText = "select TOP (10)[name],[time],[sensor_number],[sensor_type],[value] from dbo.temperature";
SqlCommand cmd = new SqlCommand(cmdText, conn);
SqlDependency dependency = new SqlDependency(cmd);
dependency.OnChange += dbChangeNotification;
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var temperatureInfo = new temperature
{
name = reader["name"].ToString(),
time = Convert.ToInt64(reader["time"]),
sensor_number = reader["sensor_number"].ToString(),
sensor_type = reader["sensor_type"].ToString(),
value = (float)Convert.ToDouble(reader["value"])
};
luminanceList.Add(temperatureInfo);
}
}
return(luminanceList);
}
//History
public JsonResult GetHistory(int?id)
{
HttpCookie cookie = Request.Cookies["admin_id"];
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DataShareCode"].ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(@"SELECT [his_id]
,[user_id]
,[his_datecreate]
,[his_content]
FROM [dbo].[Historys]", connection))
{
// Make sure the command object does not already have
// a notification object associated with it.
command.Notification = null;
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
SqlDataReader reader = command.ExecuteReader();
var list = db.Historys.OrderByDescending(n => n.his_datecreate).Select(n => new {
content = n.his_content,
img = n.User.user_img,
date = n.his_datecreate.Value.ToString()
}).ToList();
return(Json(new { list = list }, JsonRequestBehavior.AllowGet));
}
}
}
/// <summary>
/// 获得数据列表
/// </summary>
public static DataTable GetList(bool iscache)
{
try
{
string cacheKey = PAYMODETYPE_CACHEKEY;
DataSet ds = new DataSet();
if (iscache)
{
ds = (DataSet)viviapi.Cache.WebCache.GetCacheService().RetrieveObject(cacheKey);
}
if (ds == null || !iscache)
{
SqlDependency sqlDep = DataBase.AddSqlDependency(cacheKey, SQL_TABLE, SQL_TABLE_FIELD, "", null);
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,type,modetypename,payrateid,isOpen,addtime,sort,release ");
strSql.Append(" FROM paymodetype Where release = 1 order by sort");
ds = DataBase.ExecuteDataset(CommandType.Text, strSql.ToString());
if (iscache)
{
viviapi.Cache.WebCache.GetCacheService().AddObject(cacheKey, ds);
}
}
return(ds.Tables[0]);
}
catch (Exception ex)
{
ExceptionHandler.HandleException(ex);
return(null);
}
}
private void StartNotifying()
{
string query = "SELECT dbo.movies.Id, dbo.movies.name, dbo.movies.producerID, dbo.movies.releaseDate FROM dbo.movies";
SqlCommand cmd = new SqlCommand(query, _sqlConn)
{
CommandType = System.Data.CommandType.Text,
Connection = _sqlConn
};
SqlDependency dependency = new SqlDependency(cmd);
dependency.OnChange += OnChange;
cmd.ExecuteNonQuery();
}
protected void SendNotifications()
{
string todayCount = string.Empty;
string allCount = string.Empty;
string[] statusUpdate = new string[4];
string[] datetime = new string[4];
string[] batteryData = new string[5];
using (SqlConnection connection = new SqlConnection(conStr))
{
string query = @"SELECT [rightalertmaster].[Detection Distance], [activity].[Status] FROM [dbo].[rightalertmaster], [dbo].[activity]";
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Notification = null;
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
//message = reader[0].ToString();
}
/// QUERY 1:
SqlConnection conn0 = new SqlConnection(conStr);
conn0.Open();
// cmd2 RETRIEVE DATA
SqlCommand cmd0 = new SqlCommand();
cmd0.Connection = conn0;
cmd0.CommandText = @"SELECT COUNT([Detection Distance]) FROM [dbo].[rightalertmaster] WHERE Date = convert(date,dateadd(hh,-4,getdate()));";
cmd0.Prepare();
SqlDataReader rdr0 = cmd0.ExecuteReader();
//rdr0 = command.ExecuteReader();
if (rdr0.HasRows)
{
while (rdr0.Read())
{
todayCount = rdr0[0].ToString();
}
}
rdr0.Close();
conn0.Close();
///QUERY 2:
SqlConnection conn1 = new SqlConnection(conStr);
conn1.Open();
// cmd2 RETRIEVE DATA
SqlCommand cmd1 = new SqlCommand();
cmd1.Connection = conn1;
cmd1.CommandText = @"SELECT COUNT([ID]) FROM [dbo].[rightalertmaster];";
cmd1.Prepare();
SqlDataReader rdr1 = cmd1.ExecuteReader();
if (rdr1.HasRows)
{
while (rdr1.Read())
{
allCount = rdr1[0].ToString();
}
}
rdr1.Close();
conn1.Close();
/// End Query 2
// Query 3: FOR LIVE ACTIVITY FEED:
int i = 0;
SqlConnection conn2 = new SqlConnection(conStr);
conn2.Open();
// cmd2 RETRIEVE DATA
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = conn2;
cmd2.CommandText = @"SELECT TOP 4 Sender, Status, Date, Time FROM [dbo].[activity] ORDER BY ID DESC;";
cmd2.Prepare();
SqlDataReader rdr2 = cmd2.ExecuteReader();
if (rdr2.HasRows)
{
while (rdr2.Read())
{
statusUpdate[i] = "<b>" + rdr2[0].ToString() + ": </b>" + rdr2[1].ToString();
datetime[i] = rdr2[3].ToString().Substring(0, rdr2[3].ToString().IndexOf(".")) + " - " + Regex.Match(rdr2[2].ToString(), "^[^ ]+").Value;
i++;
}
}
rdr2.Close();
conn2.Close();
// END LIVE ACTIVITY FEED SECTION
///QUERY 4:
//i = 0;
SqlConnection conn3 = new SqlConnection(conStr);
conn3.Open();
// cmd4 RETRIEVE DATA
SqlCommand cmd3 = new SqlCommand();
cmd3.Connection = conn3;
cmd3.CommandText = @"SELECT TOP 1 [Bus Voltage], [Shunt Voltage], [Load Voltage], [Current], [Watts] FROM [dbo].[powerdata] ORDER BY ID DESC;";
cmd3.Prepare();
SqlDataReader rdr3 = cmd3.ExecuteReader();
if (rdr3.HasRows)
{
while (rdr3.Read())
{
batteryData[0] = rdr3[0].ToString();
batteryData[1] = rdr3[1].ToString();
batteryData[2] = rdr3[2].ToString();
batteryData[3] = rdr3[3].ToString();
batteryData[4] = rdr3[4].ToString();
//i++;
}
}
rdr3.Close();
conn3.Close();
/// End Query 4
NotificationHub nHub = new NotificationHub();
nHub.NotifyAllClients(todayCount, allCount, statusUpdate, datetime, batteryData);
}
}
}
