使 C# 应用保持打开状态并检查数据库
本文关键字：状态 检查 数据库 应用 | 更新日期: 2023-09-27 18:31:54
我有这个应用程序。与一个数据库连接并向安卓设备发送通知。我想保持此应用程序始终打开并检查数据库中的新记录。我唯一的想法是放入一个无穷大循环，例如 while（true），但我在连接行中有警告。打开（）;关于内存，程序正在停止。
namespace AndroidParse
{
class Program
{
static void Main(string[] args)
{
//SqlDataReader reader;
SqlConnection conn = new SqlConnection();
string queryString = "SELECT TOP 1 device_id FROM Temp ORDER BY ID_Requests DESC";
string connectionString = "XXXX";
while (true)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
Console.WriteLine(reader[0]);
bool isPushMessageSend = false;
string postString = "";
string urlpath = "https://api.parse.com/1/push";
var httpWebRequest = (HttpWebRequest)WebRequest.Create(urlpath);
postString = "{'"data'": { '"alert'": '"Finally is working'" },'"where'": { '"device_id'": '"" + reader[0] + "'" }}";
httpWebRequest.ContentType = "application/json";
httpWebRequest.ContentLength = postString.Length;
httpWebRequest.Headers.Add("X-Parse-Application-Id", "XXXX");
httpWebRequest.Headers.Add("X-Parse-REST-API-KEY", "XXXX");
httpWebRequest.Method = "POST";
StreamWriter requestWriter = new StreamWriter(httpWebRequest.GetRequestStream());
requestWriter.Write(postString);
requestWriter.Close();
var httpResponse = (HttpWebResponse)httpWebRequest.GetResponse();
using (var streamReader = new StreamReader(httpResponse.GetResponseStream()))
{
var responseText = streamReader.ReadToEnd();
JObject jObjRes = JObject.Parse(responseText);
if (Convert.ToString(jObjRes).IndexOf("true") != -1)
{
isPushMessageSend = true;
}
}
//--------------------------------------------------
SqlConnection sqlConnection1 = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
SqlDataReader reader1;
cmd.CommandText = "delete from Temp where ID_Requests in (select top 1 ID_Requests from Temp order by ID_Requests desc)";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
reader1 = cmd.ExecuteReader();
// Data is accessible through the DataReader object here.
sqlConnection1.Close();
//--------------------------------------------------
Console.ReadLine();
}
}
finally
{
reader.Close();
}
connection.Close();
}
}
}
private static void println()
{
throw new NotImplementedException();
}
}
}
使用像Denis Reznik建议的SqlDependency对象是一个很好的解决方案。要记住的几件事：
-
SqlDependency要求 SQL Server Service Broker 服务在 SQL Server 上运行（更多详细信息：https://msdn.microsoft.com/en-us/library/ms172133（v=vs.110）.aspx） -
可以在
SqlCommand中使用的查询基本上是在服务器上连续执行的......因此，查询可以执行的操作存在一些限制（例如，没有聚合）。更多细节在 Smudge202 的 SO 答案中：SqlDependency 的限制是什么 -
我发现使用
SqlDependency来简单地通知更改，然后通过调用数据访问方法等来采取行动......比尝试实际使用查询来检索数据更容易。因此，在您的示例中，您可能希望让SqlDependency通知有更改，然后创建一个单独的数据访问方法/sp/等...检索详细信息，例如device_id。
这是一个基于上面的代码的示例...这可能需要一些调整。祝你好运！
namespace AndroidParse
{
public class DbMonitor
{
private readonly string _connectionString = ConfigurationManager.ConnectionStrings["XXXXX"].ConnectionString;
private SqlDependency _dependency;
private SqlConnection _conn;
private SqlCommand _command;
public void MonitorDatabase()
{
SqlDependency.Start(_connectionString);
// Open DB Connection
using (_conn = new SqlConnection(_connectionString))
{
// Setup SQL Command
using (_command = new SqlCommand("SELECT TOP 1 device_id FROM Temp ORDER BY ID_Requests DESC", _conn))
{
// Create a dependency and associate it with the SqlCommand. *** MAGIC ****
_dependency = new SqlDependency(_command);
// Subscribe to the SqlDependency event.
_dependency.OnChange += HandleDatabaseChange;
// Execute
_command.Connection.Open();
_command.ExecuteReader();
}
}
}
public void Stop()
{
SqlDependency.Stop(_connectionString);
}
private void HandleDatabaseChange(object sender, SqlNotificationEventArgs e)
{
if (e.Info == SqlNotificationInfo.Invalid)
{
Console.WriteLine("The above notification query is not valid.");
}
else
{
Console.WriteLine("Database Changed based on query");
Console.WriteLine("------------------------------------");
Console.WriteLine("Event Details:");
Console.WriteLine("Notification Info: " + e.Info);
Console.WriteLine("Notification source: " + e.Source);
Console.WriteLine("Notification type: " + e.Type);
}
//PushMessage logic here
bool isPushMessageSend = false;
string postString = "";
string urlpath = "https://api.parse.com/1/push";
var httpWebRequest = (HttpWebRequest)WebRequest.Create(urlpath);
// Use Query to get device_id?
postString = "{'"data'": { '"alert'": '"Finally is working'" },'"where'": { '"device_id'": '"" + "deviceID" + "'" }}";
httpWebRequest.ContentType = "application/json";
httpWebRequest.ContentLength = postString.Length;
httpWebRequest.Headers.Add("X-Parse-Application-Id", "XXXX");
httpWebRequest.Headers.Add("X-Parse-REST-API-KEY", "XXXX");
httpWebRequest.Method = "POST";
StreamWriter requestWriter = new StreamWriter(httpWebRequest.GetRequestStream());
requestWriter.Write(postString);
requestWriter.Close();
var httpResponse = (HttpWebResponse)httpWebRequest.GetResponse();
using (var streamReader = new StreamReader(httpResponse.GetResponseStream()))
{
var responseText = streamReader.ReadToEnd();
JObject jObjRes = JObject.Parse(responseText);
if (Convert.ToString(jObjRes).IndexOf("true") != -1)
{
isPushMessageSend = true;
}
}
// Resume Monitoring... Requires setting up a new connection, etc.. Reuse existing connection? Tried.
MonitorDatabase();
}
}
}
class Program
{
static void Main(string[] args)
{
try
{
// Start the cheese monitor
DbMonitor dbMonitor = new DbMonitor();
dbMonitor.MonitorDatabase();
Console.WriteLine("Monitoring....Press any key to stop.");
Console.Read();
dbMonitor.Stop();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw;
}
finally
{
SqlDependency.Stop(ConfigurationManager.ConnectionStrings["XXXXX"].ConnectionString);
}
}
}