我有一个推送服务,它将传入的数据插入到SQL表中,我需要制作一个应用程序来监听此表以查看新的传入数据。
是否有一种方法可以监听此表而无需不断地选择数据库以获取新更改?如果没有使用这些数据,是否应该在读取后删除行,还是最好定期清理?
这是我的实际代码,它没有在表中插入任何通知,我做错了什么吗?
感谢您的信件,
是否有一种方法可以监听此表而无需不断地选择数据库以获取新更改?如果没有使用这些数据,是否应该在读取后删除行,还是最好定期清理?
更新2
这是我的实际代码,它没有在表中插入任何通知,我做错了什么吗?
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Threading.Tasks;
namespace Navman_SPI_Service
{
public partial class Service1 : ServiceBase
{
SqlConnection con = new SqlConnection();
String passAvl = Properties.Settings.Default.AVLPass;
String usuarioAvl = Properties.Settings.Default.AVLuser.ToString();
String servidor = Properties.Settings.Default.Server.ToString();
String DataB = Properties.Settings.Default.DB.ToString();
String SQLuser = Properties.Settings.Default.DBuser.ToString();
String SQLpass = Properties.Settings.Default.DBpassword.ToString();
SqlCommand dependencyCommand = new SqlCommand();
String connectionString = "";
public Service1()
{
InitializeComponent();
}
public String getQuery()
{
return "SELECT MessageID FROM dbo.navman_ic_api_message WHERE Proccessed IS NULL";
}
public void OnDebug()
{
OnStart(null);
}
protected override void OnStart(string[] args)
{
dependencyCommand.CommandText = getQuery();
connectionString = "Data Source=";
connectionString += servidor + ";Initial Catalog=FOO;Persist Security Info=True;User ID=";
connectionString += SQLuser + ";Password=";
connectionString += SQLpass + ";Initial Catalog=" + DataB;
con = new SqlConnection(connectionString);
try
{
con.Open();
}
catch (Exception f)
{
var logging = new StringBuilder();
var filePath = @"c:\temp\log.txt";
String timestamp = DateTime.Now.ToString("[yyyy:MM:dd][HH:mm:ss]");
logging.Append(timestamp + Environment.NewLine + Environment.NewLine + connectionString + Environment.NewLine + Environment.NewLine + f.ToString() + Environment.NewLine + Environment.NewLine);
File.AppendAllText(filePath, logging.ToString());
}
if (con.State == ConnectionState.Open)
{
Initialization();
dependencyStarter();
var logging = new StringBuilder();
var filePath = @"c:\temp\test.txt";
String timestamp = DateTime.Now.ToString("[yyyy:MM:dd][HH:mm:ss]");
logging.Append(timestamp + Environment.NewLine + Environment.NewLine + "SUCCESS" + Environment.NewLine + Environment.NewLine);
File.AppendAllText(filePath, logging.ToString());
}
}
protected override void OnStop()
{
Termination();
}
void dependencyStarter()
{
// Assume connection is an open SqlConnection.
// Create a new SqlCommand object.
using (SqlCommand command = new SqlCommand(getQuery(),con))
{
// Create a dependency and associate it with the SqlCommand.
SqlDependency dependency = new SqlDependency(dependencyCommand);
// Maintain the refence in a class member.
// Subscribe to the SqlDependency event.
dependency.OnChange += new
OnChangeEventHandler(OnDependencyChange);
// Execute the command.
using (SqlDataReader reader = command.ExecuteReader())
{
// Process the DataReader.
}
}
}
void OnDependencyChange(object sender, SqlNotificationEventArgs e)
{
// Handle the event (for example, invalidate this cache entry).
var logging = new StringBuilder();
var filePath = @"c:\temp\test.txt";
String timestamp = DateTime.Now.ToString("[yyyy:MM:dd][HH:mm:ss]");
logging.Append(timestamp + Environment.NewLine + Environment.NewLine + sender.ToString() + Environment.NewLine + Environment.NewLine);
File.AppendAllText(filePath, logging.ToString());
}
void Termination()
{
// Release the dependency.
SqlDependency.Stop(connectionString);
}
void Initialization()
{
// Create a dependency connection.
SqlDependency.Stop(connectionString);
SqlDependency.Start(connectionString);
}
}
}
感谢您的信件,