///<summary>Inserts one SigElementDef into the database. Provides option to use the existing priKey.</summary>
internal static long Insert(SigElementDef sigElementDef,bool useExistingPK)
{
if(!useExistingPK && PrefC.RandomKeys) {
sigElementDef.SigElementDefNum=ReplicationServers.GetKey("sigelementdef","SigElementDefNum");
}
string command="INSERT INTO sigelementdef (";
if(useExistingPK || PrefC.RandomKeys) {
command+="SigElementDefNum,";
}
command+="LightRow,LightColor,SigElementType,SigText,Sound,ItemOrder) VALUES(";
if(useExistingPK || PrefC.RandomKeys) {
command+=POut.Long(sigElementDef.SigElementDefNum)+",";
}
command+=
POut.Byte (sigElementDef.LightRow)+","
+ POut.Int (sigElementDef.LightColor.ToArgb())+","
+ POut.Int ((int)sigElementDef.SigElementType)+","
+"'"+POut.String(sigElementDef.SigText)+"',"
+DbHelper.ParamChar+"paramSound,"
+ POut.Int (sigElementDef.ItemOrder)+")";
if(sigElementDef.Sound==null) {
sigElementDef.Sound="";
}
OdSqlParameter paramSound=new OdSqlParameter("paramSound",OdDbType.Text,sigElementDef.Sound);
if(useExistingPK || PrefC.RandomKeys) {
Db.NonQ(command,paramSound);
}
else {
sigElementDef.SigElementDefNum=Db.NonQ(command,true,paramSound);
}
return sigElementDef.SigElementDefNum;
}
///<summary>Returns true if Update(SigElementDef,SigElementDef) would make changes to the database.
///Does not make any changes to the database and can be called before remoting role is checked.</summary>
public static bool UpdateComparison(SigElementDef sigElementDef, SigElementDef oldSigElementDef)
{
if (sigElementDef.LightRow != oldSigElementDef.LightRow)
{
return(true);
}
if (sigElementDef.LightColor != oldSigElementDef.LightColor)
{
return(true);
}
if (sigElementDef.SigElementType != oldSigElementDef.SigElementType)
{
return(true);
}
if (sigElementDef.SigText != oldSigElementDef.SigText)
{
return(true);
}
if (sigElementDef.Sound != oldSigElementDef.Sound)
{
return(true);
}
if (sigElementDef.ItemOrder != oldSigElementDef.ItemOrder)
{
return(true);
}
return(false);
}
///<summary>Inserts one SigElementDef into the database. Returns the new priKey.</summary>
internal static long Insert(SigElementDef sigElementDef)
{
if(DataConnection.DBtype==DatabaseType.Oracle) {
sigElementDef.SigElementDefNum=DbHelper.GetNextOracleKey("sigelementdef","SigElementDefNum");
int loopcount=0;
while(loopcount<100){
try {
return Insert(sigElementDef,true);
}
catch(Oracle.DataAccess.Client.OracleException ex){
if(ex.Number==1 && ex.Message.ToLower().Contains("unique constraint") && ex.Message.ToLower().Contains("violated")){
sigElementDef.SigElementDefNum++;
loopcount++;
}
else{
throw ex;
}
}
}
throw new ApplicationException("Insert failed. Could not generate primary key.");
}
else {
return Insert(sigElementDef,false);
}
}
///<summary>Used by MoveUp and MoveDown.</summary>
private static void SetOrder(int mySelNum, int myItemOrder, SigElementDef[] subList)
{
SigElementDef temp = subList[mySelNum];
temp.ItemOrder = myItemOrder;
Update(temp);
}
///<summary>Inserts one SigElementDef into the database. Returns the new priKey.</summary>
internal static long Insert(SigElementDef sigElementDef)
{
if (DataConnection.DBtype == DatabaseType.Oracle)
{
sigElementDef.SigElementDefNum = DbHelper.GetNextOracleKey("sigelementdef", "SigElementDefNum");
int loopcount = 0;
while (loopcount < 100)
{
try {
return(Insert(sigElementDef, true));
}
catch (Oracle.DataAccess.Client.OracleException ex) {
if (ex.Number == 1 && ex.Message.ToLower().Contains("unique constraint") && ex.Message.ToLower().Contains("violated"))
{
sigElementDef.SigElementDefNum++;
loopcount++;
}
else
{
throw ex;
}
}
}
throw new ApplicationException("Insert failed. Could not generate primary key.");
}
else
{
return(Insert(sigElementDef, false));
}
}
///<summary>No need to surround with try/catch, because all deletions are allowed. This routine, deletes references in the SigButDefElement table. References in the SigElement table are left hanging. The user interface needs to be able to handle missing elementdefs.</summary>
public static void Delete(SigElementDef def)
{
string command = "DELETE FROM sigbutdefelement WHERE SigElementDefNum=" + POut.PInt(def.SigElementDefNum);
General.NonQ(command);
command = "DELETE FROM sigelementdef WHERE SigElementDefNum =" + POut.PInt(def.SigElementDefNum);
General.NonQ(command);
}
///<summary></summary>
public static void Insert(SigElementDef def)
{
string command = "INSERT INTO sigelementdef (LightRow,LightColor,SigElementType,SigText,Sound,"
+ "ItemOrder) VALUES("
+ "'" + POut.PInt(def.LightRow) + "', "
+ "'" + POut.PInt(def.LightColor.ToArgb()) + "', "
+ "'" + POut.PInt((int)def.SigElementType) + "', "
+ "'" + POut.PString(def.SigText) + "', "
+ "'" + POut.PString(def.Sound) + "', "
+ "'" + POut.PInt(def.ItemOrder) + "')";
def.SigElementDefNum = General.NonQ(command, true);
}
///<summary></summary>
public static void Update(SigElementDef def)
{
string command = "UPDATE sigelementdef SET "
+ "LightRow = '" + POut.PInt(def.LightRow) + "'"
+ ",LightColor = '" + POut.PInt(def.LightColor.ToArgb()) + "'"
+ ",SigElementType = '" + POut.PInt((int)def.SigElementType) + "'"
+ ",SigText = '" + POut.PString(def.SigText) + "'"
+ ",Sound = '" + POut.PString(def.Sound) + "'"
+ ",ItemOrder = '" + POut.PInt(def.ItemOrder) + "'"
+ " WHERE SigElementDefNum ='" + POut.PInt(def.SigElementDefNum) + "'";
General.NonQ(command);
}
///<summary>Inserts one SigElementDef into the database. Returns the new priKey. Doesn't use the cache.</summary>
public static long InsertNoCache(SigElementDef sigElementDef)
{
if (DataConnection.DBtype == DatabaseType.MySql)
{
return(InsertNoCache(sigElementDef, false));
}
else
{
if (DataConnection.DBtype == DatabaseType.Oracle)
{
sigElementDef.SigElementDefNum = DbHelper.GetNextOracleKey("sigelementdef", "SigElementDefNum"); //Cacheless method
}
return(InsertNoCache(sigElementDef, true));
}
}
///<summary></summary>
public static SigElementDef[] GetSubList(SignalElementType sigElementType)
{
ArrayList AL = new ArrayList();
for (int i = 0; i < List.Length; i++)
{
if (sigElementType == List[i].SigElementType)
{
AL.Add(List[i]);
}
}
SigElementDef[] retVal = new SigElementDef[AL.Count];
AL.CopyTo(retVal);
return(retVal);
}
///<summary>Converts a DataTable to a list of objects.</summary>
public static List<SigElementDef> TableToList(DataTable table){
List<SigElementDef> retVal=new List<SigElementDef>();
SigElementDef sigElementDef;
for(int i=0;i<table.Rows.Count;i++) {
sigElementDef=new SigElementDef();
sigElementDef.SigElementDefNum= PIn.Long (table.Rows[i]["SigElementDefNum"].ToString());
sigElementDef.LightRow = PIn.Byte (table.Rows[i]["LightRow"].ToString());
sigElementDef.LightColor = Color.FromArgb(PIn.Int(table.Rows[i]["LightColor"].ToString()));
sigElementDef.SigElementType = (SignalElementType)PIn.Int(table.Rows[i]["SigElementType"].ToString());
sigElementDef.SigText = PIn.String(table.Rows[i]["SigText"].ToString());
sigElementDef.Sound = PIn.String(table.Rows[i]["Sound"].ToString());
sigElementDef.ItemOrder = PIn.Int (table.Rows[i]["ItemOrder"].ToString());
retVal.Add(sigElementDef);
}
return retVal;
}
///<summary>Gets a list of all SigElementDefs when program first opens.</summary>
public static void Refresh()
{
string command = "SELECT * FROM sigelementdef ORDER BY ItemOrder";
DataTable table = General.GetTable(command);
List = new SigElementDef[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
List[i] = new SigElementDef();
List[i].SigElementDefNum = PIn.PInt(table.Rows[i][0].ToString());
List[i].LightRow = PIn.PInt(table.Rows[i][1].ToString());
List[i].LightColor = Color.FromArgb(PIn.PInt(table.Rows[i][2].ToString()));
List[i].SigElementType = (SignalElementType)PIn.PInt(table.Rows[i][3].ToString());
List[i].SigText = PIn.PString(table.Rows[i][4].ToString());
List[i].Sound = PIn.PString(table.Rows[i][5].ToString());
List[i].ItemOrder = PIn.PInt(table.Rows[i][6].ToString());
}
}
///<summary>Converts a DataTable to a list of objects.</summary>
internal static List <SigElementDef> TableToList(DataTable table)
{
List <SigElementDef> retVal = new List <SigElementDef>();
SigElementDef sigElementDef;
for (int i = 0; i < table.Rows.Count; i++)
{
sigElementDef = new SigElementDef();
sigElementDef.SigElementDefNum = PIn.Long(table.Rows[i]["SigElementDefNum"].ToString());
sigElementDef.LightRow = PIn.Byte(table.Rows[i]["LightRow"].ToString());
sigElementDef.LightColor = Color.FromArgb(PIn.Int(table.Rows[i]["LightColor"].ToString()));
sigElementDef.SigElementType = (SignalElementType)PIn.Int(table.Rows[i]["SigElementType"].ToString());
sigElementDef.SigText = PIn.String(table.Rows[i]["SigText"].ToString());
sigElementDef.Sound = PIn.String(table.Rows[i]["Sound"].ToString());
sigElementDef.ItemOrder = PIn.Int(table.Rows[i]["ItemOrder"].ToString());
retVal.Add(sigElementDef);
}
return(retVal);
}
///<summary>Updates one SigElementDef in the database.</summary>
internal static void Update(SigElementDef sigElementDef)
{
string command = "UPDATE sigelementdef SET "
+ "LightRow = " + POut.Byte(sigElementDef.LightRow) + ", "
+ "LightColor = " + POut.Int(sigElementDef.LightColor.ToArgb()) + ", "
+ "SigElementType = " + POut.Int((int)sigElementDef.SigElementType) + ", "
+ "SigText = '" + POut.String(sigElementDef.SigText) + "', "
+ "Sound = " + DbHelper.ParamChar + "paramSound, "
+ "ItemOrder = " + POut.Int(sigElementDef.ItemOrder) + " "
+ "WHERE SigElementDefNum = " + POut.Long(sigElementDef.SigElementDefNum);
if (sigElementDef.Sound == null)
{
sigElementDef.Sound = "";
}
OdSqlParameter paramSound = new OdSqlParameter("paramSound", OdDbType.Text, sigElementDef.Sound);
Db.NonQ(command, paramSound);
}
///<summary>Converts a DataTable to a list of objects.</summary>
public static List <SigElementDef> TableToList(DataTable table)
{
List <SigElementDef> retVal = new List <SigElementDef>();
SigElementDef sigElementDef;
foreach (DataRow row in table.Rows)
{
sigElementDef = new SigElementDef();
sigElementDef.SigElementDefNum = PIn.Long(row["SigElementDefNum"].ToString());
sigElementDef.LightRow = PIn.Byte(row["LightRow"].ToString());
sigElementDef.LightColor = Color.FromArgb(PIn.Int(row["LightColor"].ToString()));
sigElementDef.SigElementType = (OpenDentBusiness.SignalElementType)PIn.Int(row["SigElementType"].ToString());
sigElementDef.SigText = PIn.String(row["SigText"].ToString());
sigElementDef.Sound = PIn.String(row["Sound"].ToString());
sigElementDef.ItemOrder = PIn.Int(row["ItemOrder"].ToString());
retVal.Add(sigElementDef);
}
return(retVal);
}
///<summary>Inserts one SigElementDef into the database. Provides option to use the existing priKey. Doesn't use the cache.</summary>
public static long InsertNoCache(SigElementDef sigElementDef, bool useExistingPK)
{
bool isRandomKeys = Prefs.GetBoolNoCache(PrefName.RandomPrimaryKeys);
string command = "INSERT INTO sigelementdef (";
if (!useExistingPK && isRandomKeys)
{
sigElementDef.SigElementDefNum = ReplicationServers.GetKeyNoCache("sigelementdef", "SigElementDefNum");
}
if (isRandomKeys || useExistingPK)
{
command += "SigElementDefNum,";
}
command += "LightRow,LightColor,SigElementType,SigText,Sound,ItemOrder) VALUES(";
if (isRandomKeys || useExistingPK)
{
command += POut.Long(sigElementDef.SigElementDefNum) + ",";
}
command +=
POut.Byte(sigElementDef.LightRow) + ","
+ POut.Int(sigElementDef.LightColor.ToArgb()) + ","
+ POut.Int((int)sigElementDef.SigElementType) + ","
+ "'" + POut.String(sigElementDef.SigText) + "',"
+ DbHelper.ParamChar + "paramSound,"
+ POut.Int(sigElementDef.ItemOrder) + ")";
if (sigElementDef.Sound == null)
{
sigElementDef.Sound = "";
}
OdSqlParameter paramSound = new OdSqlParameter("paramSound", OdDbType.Text, POut.StringParam(sigElementDef.Sound));
if (useExistingPK || isRandomKeys)
{
Db.NonQ(command, paramSound);
}
else
{
sigElementDef.SigElementDefNum = Db.NonQ(command, true, "SigElementDefNum", "sigElementDef", paramSound);
}
return(sigElementDef.SigElementDefNum);
}
///<summary>Updates one SigElementDef in the database. Uses an old object to compare to, and only alters changed fields. This prevents collisions and concurrency problems in heavily used tables.</summary>
public static void Update(SigElementDef sigElementDef,SigElementDef oldSigElementDef){
string command="";
if(sigElementDef.LightRow != oldSigElementDef.LightRow) {
if(command!=""){ command+=",";}
command+="LightRow = "+POut.Byte(sigElementDef.LightRow)+"";
}
if(sigElementDef.LightColor != oldSigElementDef.LightColor) {
if(command!=""){ command+=",";}
command+="LightColor = "+POut.Int(sigElementDef.LightColor.ToArgb())+"";
}
if(sigElementDef.SigElementType != oldSigElementDef.SigElementType) {
if(command!=""){ command+=",";}
command+="SigElementType = "+POut.Int ((int)sigElementDef.SigElementType)+"";
}
if(sigElementDef.SigText != oldSigElementDef.SigText) {
if(command!=""){ command+=",";}
command+="SigText = '"+POut.String(sigElementDef.SigText)+"'";
}
if(sigElementDef.Sound != oldSigElementDef.Sound) {
if(command!=""){ command+=",";}
command+="Sound = "+DbHelper.ParamChar+"paramSound";
}
if(sigElementDef.ItemOrder != oldSigElementDef.ItemOrder) {
if(command!=""){ command+=",";}
command+="ItemOrder = "+POut.Int(sigElementDef.ItemOrder)+"";
}
if(command==""){
return;
}
if(sigElementDef.Sound==null) {
sigElementDef.Sound="";
}
OdSqlParameter paramSound=new OdSqlParameter("paramSound",OdDbType.Text,sigElementDef.Sound);
command="UPDATE sigelementdef SET "+command
+" WHERE SigElementDefNum = "+POut.Long(sigElementDef.SigElementDefNum);
Db.NonQ(command,paramSound);
}
///<summary>Updates one SigElementDef in the database.</summary>
public static void Update(SigElementDef sigElementDef){
string command="UPDATE sigelementdef SET "
+"LightRow = "+POut.Byte (sigElementDef.LightRow)+", "
+"LightColor = "+POut.Int (sigElementDef.LightColor.ToArgb())+", "
+"SigElementType = "+POut.Int ((int)sigElementDef.SigElementType)+", "
+"SigText = '"+POut.String(sigElementDef.SigText)+"', "
+"Sound = "+DbHelper.ParamChar+"paramSound, "
+"ItemOrder = "+POut.Int (sigElementDef.ItemOrder)+" "
+"WHERE SigElementDefNum = "+POut.Long(sigElementDef.SigElementDefNum);
if(sigElementDef.Sound==null) {
sigElementDef.Sound="";
}
OdSqlParameter paramSound=new OdSqlParameter("paramSound",OdDbType.Text,sigElementDef.Sound);
Db.NonQ(command,paramSound);
}
///<summary>Updates one SigElementDef in the database. Uses an old object to compare to, and only alters changed fields. This prevents collisions and concurrency problems in heavily used tables.</summary>
internal static void Update(SigElementDef sigElementDef, SigElementDef oldSigElementDef)
{
string command = "";
if (sigElementDef.LightRow != oldSigElementDef.LightRow)
{
if (command != "")
{
command += ",";
}
command += "LightRow = " + POut.Byte(sigElementDef.LightRow) + "";
}
if (sigElementDef.LightColor != oldSigElementDef.LightColor)
{
if (command != "")
{
command += ",";
}
command += "LightColor = " + POut.Int(sigElementDef.LightColor.ToArgb()) + "";
}
if (sigElementDef.SigElementType != oldSigElementDef.SigElementType)
{
if (command != "")
{
command += ",";
}
command += "SigElementType = " + POut.Int((int)sigElementDef.SigElementType) + "";
}
if (sigElementDef.SigText != oldSigElementDef.SigText)
{
if (command != "")
{
command += ",";
}
command += "SigText = '" + POut.String(sigElementDef.SigText) + "'";
}
if (sigElementDef.Sound != oldSigElementDef.Sound)
{
if (command != "")
{
command += ",";
}
command += "Sound = " + DbHelper.ParamChar + "paramSound";
}
if (sigElementDef.ItemOrder != oldSigElementDef.ItemOrder)
{
if (command != "")
{
command += ",";
}
command += "ItemOrder = " + POut.Int(sigElementDef.ItemOrder) + "";
}
if (command == "")
{
return;
}
if (sigElementDef.Sound == null)
{
sigElementDef.Sound = "";
}
OdSqlParameter paramSound = new OdSqlParameter("paramSound", OdDbType.Text, sigElementDef.Sound);
command = "UPDATE sigelementdef SET " + command
+ " WHERE SigElementDefNum = " + POut.Long(sigElementDef.SigElementDefNum);
Db.NonQ(command, paramSound);
}
private void butAdd_Click(object sender, EventArgs e)
{
FormSigElementDefEdit FormS = new FormSigElementDefEdit();
FormS.ElementCur = new SigElementDef();
FormS.ElementCur.LightColor = Color.White;
//default is user
if (listExtras.SelectedIndex != -1)
{
FormS.ElementCur.SigElementType = SignalElementType.Extra;
}
if (listMessages.SelectedIndex != -1)
{
FormS.ElementCur.SigElementType = SignalElementType.Message;
}
FormS.IsNew = true;
FormS.ShowDialog();
if (FormS.DialogResult != DialogResult.OK)
{
return;
}
//set the order
SigElementDef element = FormS.ElementCur.Copy();
if (element.SigElementType == SignalElementType.User)
{
element.ItemOrder = ListUser.Length;
SigElementDefs.Update(element);
}
else if (element.SigElementType == SignalElementType.Extra)
{
element.ItemOrder = ListExtras.Length;
SigElementDefs.Update(element);
}
else if (element.SigElementType == SignalElementType.Message)
{
element.ItemOrder = ListMessages.Length;
SigElementDefs.Update(element);
}
FillLists();
//Select the item
for (int i = 0; i < ListUser.Length; i++)
{
if (ListUser[i].SigElementDefNum == element.SigElementDefNum)
{
listToFrom.SelectedIndex = i;
}
}
for (int i = 0; i < ListExtras.Length; i++)
{
if (ListExtras[i].SigElementDefNum == element.SigElementDefNum)
{
listExtras.SelectedIndex = i;
}
}
for (int i = 0; i < ListMessages.Length; i++)
{
if (ListMessages[i].SigElementDefNum == element.SigElementDefNum)
{
listMessages.SelectedIndex = i;
}
}
}
///<summary>Inserts one SigElementDef into the database. Returns the new priKey. Doesn't use the cache.</summary>
public static long InsertNoCache(SigElementDef sigElementDef)
{
return(InsertNoCache(sigElementDef, false));
}