ODBC connection - reconnect
HejJeg har denne kode som desværre ikke kan tåle at forbindelsen til Oracle database mistes.
Hvad skal der til for at program automatisk opretter ny connection når forbindelse til db igen etableres?
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using System.Windows;
using System.Runtime;
using System.Drawing.Printing;
using cs_IniHandlerDevelop;
namespace AlServ_Batch
{
public partial class Batch_MainForm : Form
{
public Batch_MainForm()
{
InitializeComponent();
}
private void Batch_MainForm_Load(object sender, EventArgs e)
{
UpdateStatusLight();
}
private void UpdateStatusLight() {
ConnectionState st = Connection.State;
if (st == ConnectionState.Open
|| st == ConnectionState.Fetching
|| st == ConnectionState.Executing
)
{
pictureBox1.Image = Properties.Resources.greenlight;
}
else if (st == ConnectionState.Closed || st == ConnectionState.Broken)
{
pictureBox1.Image = Properties.Resources.redlight;
}
else
{
pictureBox1.Image = Properties.Resources.yellowlight;
}
}
public static string[] TextTypeName = new string[] { "",
"Portvagt/I normal arbejdstid",
"TDA/I normal arbejdstid underret",
"Portvagt/Uden for normal arbejdstid",
"TDA/Uden for normal arbejdstid",
"Procedure",
"Anlæg og følerplacering",
"Særlige forhold",
"Betjeningsområde", //***//
"Anden instruktion (SOP/BPP)",
"System Alarm"}; //***//
public static string[] TextFieldName = new string[] { "",
"workcall", "workinform", "offcall", "offinform", "procedur",
"plant", "turnoff", "position", "instruction", "restart" };
private int timerLevel = 0;
private void timer1_Tick(object sender, EventArgs e)
{
if (timerLevel == 0)
{
timerLevel++;
try
{
UpdateStatusLight();
ProcessBatch();
}
catch { }
timerLevel--;
}
}
private void ProcessBatch()
{
if (Connection.State == ConnectionState.Open)
{
{
String sql = "select recid,action,parm1,parm2,parm3,parm4,targetfile from alarm.batch where recid>0 order by recid";
OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, Connection);
OleDbDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
int n = 0;
string recid_str = reader[n++].ToString();
string action = reader[n++].ToString();
string parm1 = reader[n++].ToString();
string parm2 = reader[n++].ToString();
string parm3 = reader[n++].ToString();
string parm4 = reader[n++].ToString();
string targetfile = reader[n++].ToString();
sql = "update alarm.batch set recid= -" + recid_str + " where recid=" + recid_str;
cmd = new System.Data.OleDb.OleDbCommand(sql, Connection);
cmd.ExecuteNonQuery();
if (ProcessBatchItem(action, parm1, parm2, parm3, parm4, targetfile))
{
sql = "delete from alarm.batch where recid= -" + recid_str;
cmd = new System.Data.OleDb.OleDbCommand(sql, Connection);
cmd.ExecuteNonQuery();
}
}
reader.Close();
}
}
}
private bool ProcessBatchItem(string action, string parm1, string parm2, string parm3, string parm4, string targetfile)
{
DateTime waituntil;
bool res = true;
bool liveAlarm = false;
string fname = "";
string fnameTemp;
string targetPrinters = "";
if (targetfile.ToString().Length>0) {
fname = Path.Combine(Properties.Settings.Default.htmlprintpath, targetfile);
}
TextWriter tw;
if (fname == "") fname = (new Random()).Next(int.MaxValue) + "__" + (new Random()).Next(int.MaxValue) + ".htm";
fnameTemp = fname + "_";
tw = new StreamWriter(fnameTemp);
string htmlPrefix = "<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">"
;
AlarmInstruction instruction = new AlarmInstruction();
if (action == "print")
{
liveAlarm = (parm4=="ALARM");
instruction = new AlarmInstruction(int.Parse(parm1), Connection);
targetPrinters = instruction.TargetPrinters;
WriteAlarmInstruction(tw, instruction, parm3+(targetPrinters.Length>0 ? " ("+targetPrinters+")":""), parm2.ToString()=="1", parm4, htmlPrefix+"<body charset=windows-1252>", "</body>");
tw.Close();
}
else if (action == "printmany")
{
WriteAlarmInstructions(tw, parm1,parm3,parm4,htmlPrefix+"<body charset=windows-1252>", "</body>");
liveAlarm = false; // (parm4 == "ALARM");
tw.Close();
}
System.IO.File.Move(fnameTemp, fname);
if (liveAlarm || true) {
string ts = DateTime.Now.ToString();
bool busy = true;
StreamReader reader = new StreamReader(fname);
string docText = reader.ReadToEnd();
foreach (char ch in targetPrinters)
{
try
{
int n = int.Parse(ch.ToString());
n = n == 0 ? 10 : n;
if (n > 0 && n <= 10)
{
HtmlPrint(docText, instruction.PrinterName(n));
}
}
catch
{
}
}
foreach (string mobile in instruction.SmsTarget.Split(new char[] { ';'}))
{
try
{
string smsDir = instruction.Ini.GetValue("Directory", "Sms");
string footer = instruction.Ini.GetValue("Sms", "Footer");
fname = smsDir + "\\" + DateTime.Now.Ticks.ToString();
fnameTemp = fname + ".$$$";
tw = new StreamWriter(fnameTemp);
tw.WriteLine(mobile);
tw.WriteLine();
tw.WriteLine(parm3);
tw.WriteLine(instruction.UserAdr);
tw.WriteLine(instruction.Caption);
tw.WriteLine(footer);
tw.Close();
File.Move(fnameTemp, fname + ".txt");
}
catch (Exception e)
{
}
}
}
waituntil = DateTime.Now.AddSeconds(2);
while (DateTime.Now < waituntil) Application.DoEvents();
return res;
}
private void HtmlPrint(string doctext, string printerName)
{
Batch_BrowserForm browserForm = new Batch_BrowserForm();
browserForm.Show();
browserForm.PrintHtml(doctext, printerName, this);
}
private string magic = "gkhjagdsfgkjhgkjhasdfyehecghgefuuweyuyjhweuyg1233498712vcgdqssedrrggggg";
private string InstructionTextRow(int textId, string title, string caption, bool optional)
{
string res;
NumberedText txt = new NumberedText(textId,Connection);
if (txt.Text=="" && optional) {
res = "";
} else {
res = "<tr><td valign=top>"
+ (title.ToString().Length > 0 ? "<b>" + xmlencode(title.Replace(" ", magic)).Replace(magic, " ") + "</b><br/>" : "")
+ xmlencode(caption) + "</td><td valign=top width=1%>" + (txt.Id > 0 ? "<div width=100% style=\"border: gainsboro 1px solid\">" + txt.Id.ToString() + "</div> " : "") + "</td><td valign=top borderwidth=1 style=\"border: gainsboro 1px solid\" width=80%>" + xmlencode(txt.Text.ToString()).Replace("\n", " <br/>").Replace("\r", "") + " </td></tr>";
}
return res;
}
public string xmlencode(string s)
{
XmlDocument xmldoc = new XmlDocument();
XmlNode xn = xmldoc.CreateElement("data");
xn.InnerText = s;
return xn.InnerXml;
}
private string FrameDivRight(string s)
{
return "<div halign=right style=\"border: lavender 1px solid\">" + xmlencode(s) + "</div>";
}
private string FramedText(string caption, string s)
{
return "<table width=100%><tr><td><i>" + xmlencode(caption.Replace(" ", magic)).Replace(magic, " ") + " </td><td width=100% style=\"border: gray 1px solid\">" + (s.ToString().Length > 0 ? xmlencode(s) : " ") + "</i></td></tr></table>";
}
private string CondFramedText(string caption, string s)
{
return (s.Length > 0) ? FramedText(caption, s) : " ";
}
private string rowtext(AlarmInstruction instruction, int textidx, string title, bool optional) {
int textId = instruction.textids[textidx];
return InstructionTextRow(textId,title,TextTypeName[textidx],optional);
}
private void WriteAlarmInstructions(TextWriter textwriter, string whereclause, string timestamp, string qualification, string prefix, string postfix)
{
//String sql = "select id from alarm.point"+(String.IsNullOrEmpty(whereclause) ? "": " where "+whereclause)+" order by useradr";
String sql = (new AlarmInstruction()).BasicSelect + (String.IsNullOrEmpty(whereclause) ? "" : " where " + whereclause) + " order by useradr";
OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, Connection);
OleDbDataReader reader = cmd.ExecuteReader();
bool first = true;
textwriter.Write(prefix);
while (reader.Read())
{
try
{
AlarmInstruction instruction = new AlarmInstruction();
instruction.InitFromReader(reader);
WriteAlarmInstruction(textwriter, instruction, timestamp, false, qualification, "<div " + (first ? "" : "style=\"page-break-before:always\"") + "></div>", "");
//textwriter.Write("s=" + s + "<br>");
first = false;
}
catch
{
}
}
reader.Close();
textwriter.Write(postfix);
}
private bool WriteAlarmInstruction(TextWriter textwriter, AlarmInstruction instruction, string timestamp, bool alarmTest, string qualification, string prefix, string postfix)
{
/*
textwriter.Write(DateTime.Now.ToString() + " ALARM " + id.ToString() + "<br/>");
return true
*/
if (qualification != "ALARM") timestamp = "";
string pageContents = "";
pageContents += "<style>TD,P,I,INPUT,DIV{font-family:arial,helvetica; font-size:8pt}</style>";
pageContents += "<style>B{font-family:arial,helvetica; font-size:10pt}</style>";
pageContents += "<table cellspacing=3 width=100%><tr><td align=left><h1>"
+(alarmTest ? "Alarmafprøvning" : "Alarminstruks")
+"</h1></td><td align=right>" + xmlencode(instruction.SystemId + " ").Replace(" ", " ") + xmlencode(timestamp) + "</td></tr></table>";
pageContents += "<table width=100%>";
pageContents += "<tr><td>" + FramedText("ID nr.", instruction.UserAdr.ToString()) + "</td><td valign=right>" + FramedText("Betegnelse", instruction.Caption) + "</td><td>" + FramedText("Prioritet ", instruction.Notif) + "</td></tr>";
pageContents += "<tr><td>" + CondFramedText("$ Adr.", instruction.TechAdr.ToString()) + "</td><td valign=right>" + CondFramedText("Alarmgrænse høj", instruction.AlarmLimitHigh.ToString()) + "</td><td>" + CondFramedText("Enhed", instruction.Unit.ToString()) + "</td></tr>";
pageContents += "<tr><td></td><td valign=right>" + CondFramedText("Alarmgrænse lav", instruction.AlarmLimitLow.ToString()) + "</td><td>" + CondFramedText("Tidsforsinkelse", instruction.TimeDelay.ToString()) + "</td></tr>";
pageContents += "<tr><td></td><td valign=right>" + FramedText("Alarm GMP kritisk", instruction.GMPCritical.ToString()) + "</td><td>" + FramedText("Kræver alarmen afvigelse (NDP)", instruction.Deviation.ToString()) + "</td></tr>";
pageContents += "</table>";
pageContents += "<table cellspacing=3>";
pageContents += "<tr height=2px><td colspan=3><hr></td></tr>";
pageContents += "<tr><td>"
+ FramedText("Bruger afd.",instruction.UserDept)
+"</td><td colspan=2>"
+"<table><tr><td>"
+ FramedText("Ansvarlig",instruction.Responsible)
+"</td><td>"
+ FramedText("TDA driftsgruppe",instruction.OperationsGroup)
+"</td></tr></table>"
+"</td></tr>";
pageContents += "<tr><td colspan=3 height=2px><hr></td></tr>";
pageContents += rowtext(instruction, 8, "", false);
pageContents += rowtext(instruction, 6, "", false);
pageContents += "<tr height=2px><td colspan=3><hr></td></tr>";
pageContents += rowtext(instruction, 1, "Portvagt aktion/tilkald", false);
pageContents += rowtext(instruction, 3, "", true);
pageContents += "<tr height=2px><td colspan=3><hr></td></tr>";
pageContents += rowtext(instruction, 7, "Aktion TDA", false);
pageContents += rowtext(instruction, 5, "", false);
pageContents += rowtext(instruction, 9, "", false);
pageContents += rowtext(instruction, 2, "Underret bruger", false);
pageContents += rowtext(instruction, 4, "", true);
pageContents += rowtext(instruction, 10, "", true);
if (instruction.UserUpdate.Length > 0)
{
pageContents += "<tr><td colspan=2></td><td valign=right><i>" + xmlencode("Instruks opdateret "+ instruction.UserUpdate) + "</i></td></tr>";
}
pageContents += "</table>";
textwriter.Write(prefix+"<table height=100% width=100%><tr><td valign=top>" + pageContents + "</td></tr></table>"+postfix);
return true;
}
public String ConnectionString
{
get { return Properties.Settings.Default.ConnectionString; }
}
private OleDbConnection con_;
public OleDbConnection Connection
{
get
{
if (con_ == null)
{
OleDbConnection connection = new OleDbConnection(ConnectionString);
try
{
connection.Open();
}
catch
{
}
con_ = connection;
}
return con_;
}
}
class AlarmInstruction
{
public int Id;
public string SystemId;
public string UserAdr;
public string Caption;
public string UserDept;
public string Responsible;
public string OperationsGroup;
public string Notif;
public string NotifMaster;
public string TechAdr;
public string AlarmLimitHigh;
public string AlarmLimitLow;
public string Unit;
public string TimeDelay;
public string GMPCritical;
public string Deviation;
public string UserUpdate;
public string Recipient;
public int[] textids;
public DateTime NotifDateTime = new DateTime(0);
private string _targetPrinters = null;
public string TargetPrinters
{
get {
try
{
if (_targetPrinters == null)
{
if (NotifDateTime.CompareTo(new DateTime(0))==0)
{
NotifDateTime = DateTime.Now;
}
_targetPrinters = "";
}
string sql = "select targets,weekday,hour from notifdetail where notifmaster=" + NotifMaster+" order by weekday,hour";
OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, Connection);
OleDbDataReader reader = cmd.ExecuteReader();
DateTime now = NotifDateTime;
DayOfWeek wd = now.DayOfWeek;
int weekday = (wd==DayOfWeek.Sunday ? 7 : 1+(wd-DayOfWeek.Monday));
while (reader.Read())
{
int fromweekday = int.Parse(reader["weekday"].ToString());
DateTime fromhour = reader.GetDateTime(reader.GetOrdinal("hour"));
if (fromweekday<weekday || (fromweekday==weekday && (fromhour.TimeOfDay<=now.TimeOfDay) ))
{
_targetPrinters = reader["targets"].ToString();
}
}
reader.Close();
}
catch
{
}
return _targetPrinters;
}
}
private string _smsTarget = null;
public string SmsTarget
{
get {
if (_smsTarget == null)
{
if (TargetPrinters.Contains("S") && Recipient.Length>0) {
try
{
string sql = "select mobile from recipients where id="+ (new MySqlLayer()).SqlLit(Recipient);
OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, Connection);
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
_smsTarget = reader["mobile"].ToString();
}
reader.Close();
}
catch
{
}
}
}
if (_smsTarget == null)
{
_smsTarget = "";
}
return _smsTarget.Trim();
}
}
private IniStructure _ini = null;
public IniStructure Ini
{
get {
if (_ini == null)
{
_ini = IniStructure.ReadIni("C:\\alarm\\alarm.ini");
}
return _ini;
}
}
public string PrinterName(int n)
{
string pname = "";
try
{
pname = Ini.GetValue("Printers", "printer" + n.ToString());
}
catch
{
}
if (pname.Length==0) throw new Exception("Printer selection failed. Printer #"+n.ToString()+", name \""+pname+"\"");
return pname;
}
public void InitFromReader(OleDbDataReader reader)
{
Id = int.Parse(reader["id"].ToString());
SystemId = reader["systemid"].ToString();
UserAdr = reader["useradr"].ToString();
Caption = reader["descr"].ToString();
Notif = reader["notifnam"].ToString();
NotifMaster = reader["notifmaster"].ToString();
TechAdr = reader["techadr"].ToString();
Unit = reader["unit"].ToString();
TimeDelay = reader["timedelay"].ToString();
AlarmLimitHigh = reader["alarmlimithigh"].ToString();
AlarmLimitLow = reader["alarmlimitlow"].ToString();
GMPCritical = reader["gmpcritical"].ToString() == "1" ? "Ja" : "Nej";
Deviation = reader["deviation"].ToString() == "1" ? "Ja" : "Nej";
UserUpdate = reader["user_update"].ToString();
UserDept = reader["dept"].ToString();
Responsible = reader["userid"].ToString();
OperationsGroup = reader["operationsgroup"].ToString();
Recipient = reader["recipient"].ToString();
int n = TextTypeName.GetUpperBound(0);
textids = new int[n + 1];
for (int i = 1; i <= n; i++)
{
try
{
textids[i] = int.Parse(reader[TextFieldName[i]].ToString());
}
catch
{
textids[i] = 0;
};
}
}
public String BasicSelect {
get {
return "select *,(select usernam from notifmaster where id= point.notifmaster) notifnam,(select id from alarmsystem) systemid from alarm.point";
}
}
public AlarmInstruction()
: base()
{
}
OleDbConnection Connection = null;
public AlarmInstruction(int id, OleDbConnection connection)
: base()
{
String sql = BasicSelect + " where id=" + id.ToString();
try
{
Connection = connection;
OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, connection);
OleDbDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
InitFromReader(reader);
}
reader.Close();
}
catch { }
}
}
private void button2_Click(object sender, EventArgs e)
{
SQL_performer performer = new SQL_performer();
performer.Connection = Connection;
performer.Show();
}
private void pictureBox1_Click(object sender, EventArgs e)
{
}
}
class NumberedText
{
public NumberedText(int textId, OleDbConnection connection)
: base()
{
String sql = "select txt,typ from alarm.txt where id="+textId.ToString();
OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, connection);
try
{
if (textId > 0)
{
OleDbDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
int n = 0;
_Id = textId;
_Text = reader[n++].ToString();
_Typ = int.Parse(reader[n++].ToString());
}
reader.Close();
}
}
catch
{
_Error = true;
}
if (_Text == null) _Text = "";
if (_Typ == null) _Typ = 0;
}
private DateTime _TimeStamp;
public DateTime TimeStamp
{
get { return _TimeStamp; }
set { _TimeStamp = value; }
}
private bool _Error;
public bool Error
{
get { return _Error; }
}
private int _Id;
public int Id
{
get { return _Id; }
}
private string _Text;
public string Text
{
get { return _Text; }
}
private int _Typ;
public int Typ
{
get { return _Typ; }
}
}
public class MySqlLayer
{
public virtual String SqlLit(Object obj)
{
if (obj == null)
{
return "null";
}
else if (obj is string)
{
return (String.IsNullOrEmpty(obj as string) ? "null" : ("'" + (obj as string).Replace("'", "''") + "'"));
}
else if (obj is int)
{
return (obj.ToString());
}
else if (obj is DateTime)
{
DateTime d = (DateTime)obj;
return String.Format("TO_DATE('{0:0000}-{1:00}-{2:00} {3:00}:{4:00}','YYYY-MM-DD HH24:MI')", d.Year, d.Month, d.Day, d.Hour, d.Minute);
}
else
{
return "null";
}
}
}
}
PS. Jeg har ikke lavet koden, så jeg kan ikke svare på evt. spørgsmål.
PPS. Hvordan vil jeg ellers kunne få en 'alarm' for at en sådanne program har mistet forbindelse til db og derved ikke længere fungere?
:o)
