Basically my code waits for database table a to have an XML string inserted into it. The SQL query parses the XML string and stores it into separate columns (this has to occur this way because the insert is actually happening from elsewhere, I just rewrote if for testing). The service broker listens to database a and when it sees a new record inserted, it creates a new XML string to send out. I am not adept at threading and this is my very first time ever using the service broker in SQL.
Are there any tips/suggestions that could be offered up? Are there any places which code is going to, for lack of better terms, explode and fail? I just want to make sure this is at least an acceptable way of completing the operations.
This code works perfectly, but does it make sense and can it be optimized?
Interface
Code
#region Declarations
private bool isPermitted()
{
try
{
SqlClientPermission clientPermission = new SqlClientPermission(PermissionState.Unrestricted);
clientPermission.Demand();
return true;
}
catch
{
return false;
lbxSystemAvailabilityReply.Items.Add("Failed permissions test.");
}
}
string db = Connect.GetDBString("DatabaseName");
static int RecordsProcessed = 0;
static decimal MaxRecordsRetained = 10;
const string SYSTEM_AVAILABILITY_QUERY = "SELECT [Columns] FROM [dbo].[System];";
const string SYSTEM_AVAILABILITY_REPLY_QUERY = "SELECT [Columns] FROM [dbo].[SystemReply];";
const string SYSTEM_AVAILABILITY_AWAIT = "SELECT [Columns] FROM [dbo].[System] WHERE [Columns] = 0;";
#endregion
public SystemAvailabilityLog()
{
InitializeComponent();
}
private void dbTransactionLog_Load(object sender, System.EventArgs e)
{
SqlDependency.Stop(db);
SqlDependency.Start(db);
Thread sysAvail = new Thread(SystemAvailabilityThread);
Thread sysAvailRep = new Thread(SystemAvailabilityReplyThread);
sysAvail.Start();
sysAvailRep.Start();
txtLastRestart.Text = DateTime.Now.ToString();
}
private void dbTransactionLog_FormClosing(object sender, FormClosingEventArgs e)
{
SqlDependency.Stop(db);
}
#region Methods and Functions
private SqlCommand CreateCommandWithDependency(string queryText, SqlConnection con, OnChangeEventHandler e)
{
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = queryText;
cmd.CommandType = CommandType.Text;
cmd.Notification = null;
SqlDependency sqlDep = new SqlDependency(cmd);
sqlDep.OnChange += new OnChangeEventHandler(e);
return cmd;
}
}
private void UpdateRecordCount()
{
RecordsProcessed++;
if (txtProcessed.InvokeRequired)
{
txtProcessed.Invoke(
new ThreadStart(delegate
{
txtProcessed.Text = RecordsProcessed.ToString();
}));
}
else
{
txtProcessed.Text = RecordsProcessed.ToString();
}
}
private void SendSystemAvailabilityPing()
{
using (SqlConnection con = new SqlConnection(db))
{
SystemAvailability oPing = new SystemAvailability();
oPing.PingDate = DateTime.Today.Date.ToString("MMddyyyy");
oPing.PingTime = DateTime.Now.TimeOfDay.ToString().Replace(":", "").Remove(6);
string xmlSend = oPing.GetXMLString;
using (SqlDataReader dr = Connect.ExecuteReader("[db_InsertSystemAvailability]", con,
new SqlParameter("@XmlSend", xmlSend))) { }
}
}
private void SystemAvailabilityReply()
{
try
{
if (isPermitted())
{
using (SqlConnection con = new SqlConnection(db))
{
using (SqlCommand cmd = CreateCommandWithDependency(SYSTEM_AVAILABILITY_AWAIT, con, SystemAvailabilityReply_OnChange))
{
con.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
while (dr.Read())
{
if (lbxSystemAvailabilityReply.Items.Count >= MaxRecordsRetained)
{
lbxSystemAvailabilityReply.Items.RemoveAt(0);
}
SystemAvailabilityReply sarPing = new SystemAvailabilityReply();
sarPing.PingID = dr["PingID"].ToString();
using (SqlConnection conInsert = new SqlConnection(db))
{
using (SqlDataReader drInsert = Connect.ExecuteReader("[db_InsertSystemAvailabilityReply]", conInsert,
new SqlParameter("@Param1", sarPing.PingID)))
{ }
}
string transactionDetail = string.Format("PingID: {0} PingDate: {1} PingTime: {2}",
sarPing.PingID, sarPing.PingDate, sarPing.PingTime);
lbxSystemAvailabilityReply.Items.Add(transactionDetail);
UpdateRecordCount();
}
}
}
}
}
}
}
catch (Exception ex)
{
}
}
private void SystemAvailability()
{
lbxSystemAvailability.Items.Clear();
if (isPermitted())
{
string pingDate = DateTime.Today.ToShortDateString();
string pingTime = DateTime.Now.TimeOfDay.ToString();
string pingMessage = string.Format("Ping Received: {0} @ {1}", pingDate, pingTime);
lbxSystemAvailability.Items.Add(pingMessage);
}
}
private void SystemAvailabilityThread()
{
using (SqlConnection con = new SqlConnection(db))
{
con.Open();
using (SqlCommand cmd = CreateCommandWithDependency(SYSTEM_AVAILABILITY_QUERY, con, SystemAvailability_OnChange))
{
using (SqlDataReader dr = cmd.ExecuteReader()) { }
}
}
}
private void SystemAvailabilityReplyThread()
{
using (SqlConnection con = new SqlConnection(db))
{
con.Open();
using (SqlCommand cmd = CreateCommandWithDependency(SYSTEM_AVAILABILITY_REPLY_QUERY, con, SystemAvailabilityReply_OnChange))
{
using (SqlDataReader dr = cmd.ExecuteReader()) { }
}
}
}
#endregion
#region Object Handles
private void btnSystemAvailabilityForm_Click(object sender, EventArgs e)
{
Thread sysPing = new Thread(SendSystemAvailabilityPing);
sysPing.Start();
}
private void nudRecentTransactionCount_ValueChanged(object sender, EventArgs e)
{
MaxRecordsRetained = nudRecentTransactionCount.Value;
while (lbxSystemAvailabilityReply.Items.Count > MaxRecordsRetained)
{
lbxSystemAvailabilityReply.Items.RemoveAt(0);
}
}
private void SystemAvailability_OnChange(object sender, SqlNotificationEventArgs e)
{
if (this.InvokeRequired)
{
lbxSystemAvailabilityReply.BeginInvoke(new MethodInvoker(SystemAvailabilityReply));
}
else
{
SystemAvailabilityReply();
}
Thread sysAvail = new Thread(SystemAvailabilityThread);
sysAvail.Start();
}
private void SystemAvailabilityReply_OnChange(object sender, SqlNotificationEventArgs e)
{
if (this.InvokeRequired)
{
lbxSystemAvailability.BeginInvoke(new MethodInvoker(SystemAvailability));
}
else
{
SystemAvailability();
}
Thread sysAvailRep = new Thread(SystemAvailabilityReplyThread);
sysAvailRep.Start();
}
#endregion
