///<summary>Updates one TimeAdjust 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(TimeAdjust timeAdjust, TimeAdjust oldTimeAdjust)
{
string command = "";
if (timeAdjust.EmployeeNum != oldTimeAdjust.EmployeeNum)
{
if (command != "")
{
command += ",";
}
command += "EmployeeNum = " + POut.Long(timeAdjust.EmployeeNum) + "";
}
if (timeAdjust.TimeEntry != oldTimeAdjust.TimeEntry)
{
if (command != "")
{
command += ",";
}
command += "TimeEntry = " + POut.DateT(timeAdjust.TimeEntry) + "";
}
if (timeAdjust.RegHours != oldTimeAdjust.RegHours)
{
if (command != "")
{
command += ",";
}
command += "RegHours = '" + POut.TSpan(timeAdjust.RegHours) + "'";
}
if (timeAdjust.OTimeHours != oldTimeAdjust.OTimeHours)
{
if (command != "")
{
command += ",";
}
command += "OTimeHours = '" + POut.TSpan(timeAdjust.OTimeHours) + "'";
}
if (timeAdjust.Note != oldTimeAdjust.Note)
{
if (command != "")
{
command += ",";
}
command += "Note = '" + POut.String(timeAdjust.Note) + "'";
}
if (timeAdjust.IsAuto != oldTimeAdjust.IsAuto)
{
if (command != "")
{
command += ",";
}
command += "IsAuto = " + POut.Bool(timeAdjust.IsAuto) + "";
}
if (command == "")
{
return;
}
command = "UPDATE timeadjust SET " + command
+ " WHERE TimeAdjustNum = " + POut.Long(timeAdjust.TimeAdjustNum);
Db.NonQ(command);
}
///<summary>Inserts one TimeAdjust into the database. Provides option to use the existing priKey.</summary>
internal static long Insert(TimeAdjust timeAdjust, bool useExistingPK)
{
if (!useExistingPK && PrefC.RandomKeys)
{
timeAdjust.TimeAdjustNum = ReplicationServers.GetKey("timeadjust", "TimeAdjustNum");
}
string command = "INSERT INTO timeadjust (";
if (useExistingPK || PrefC.RandomKeys)
{
command += "TimeAdjustNum,";
}
command += "EmployeeNum,TimeEntry,RegHours,OTimeHours,Note,IsAuto) VALUES(";
if (useExistingPK || PrefC.RandomKeys)
{
command += POut.Long(timeAdjust.TimeAdjustNum) + ",";
}
command +=
POut.Long(timeAdjust.EmployeeNum) + ","
+ POut.DateT(timeAdjust.TimeEntry) + ","
+ "'" + POut.TSpan(timeAdjust.RegHours) + "',"
+ "'" + POut.TSpan(timeAdjust.OTimeHours) + "',"
+ "'" + POut.String(timeAdjust.Note) + "',"
+ POut.Bool(timeAdjust.IsAuto) + ")";
if (useExistingPK || PrefC.RandomKeys)
{
Db.NonQ(command);
}
else
{
timeAdjust.TimeAdjustNum = Db.NonQ(command, true);
}
return(timeAdjust.TimeAdjustNum);
}
///<summary>Updates one ClockEvent in the database.</summary>
public static void Update(ClockEvent clockEvent)
{
string command = "UPDATE clockevent SET "
+ "EmployeeNum = " + POut.Long(clockEvent.EmployeeNum) + ", "
//TimeEntered1 not allowed to change
+ "TimeDisplayed1 = " + POut.DateT(clockEvent.TimeDisplayed1) + ", "
+ "ClockStatus = " + POut.Int((int)clockEvent.ClockStatus) + ", "
+ "Note = " + DbHelper.ParamChar + "paramNote, "
+ "TimeEntered2 = " + POut.DateT(clockEvent.TimeEntered2) + ", "
+ "TimeDisplayed2 = " + POut.DateT(clockEvent.TimeDisplayed2) + ", "
+ "OTimeHours = '" + POut.TSpan(clockEvent.OTimeHours) + "', "
+ "OTimeAuto = '" + POut.TSpan(clockEvent.OTimeAuto) + "', "
+ "Adjust = '" + POut.TSpan(clockEvent.Adjust) + "', "
+ "AdjustAuto = '" + POut.TSpan(clockEvent.AdjustAuto) + "', "
+ "AdjustIsOverridden= " + POut.Bool(clockEvent.AdjustIsOverridden) + ", "
+ "Rate2Hours = '" + POut.TSpan(clockEvent.Rate2Hours) + "', "
+ "Rate2Auto = '" + POut.TSpan(clockEvent.Rate2Auto) + "', "
+ "ClinicNum = " + POut.Long(clockEvent.ClinicNum) + " "
+ "WHERE ClockEventNum = " + POut.Long(clockEvent.ClockEventNum);
if (clockEvent.Note == null)
{
clockEvent.Note = "";
}
OdSqlParameter paramNote = new OdSqlParameter("paramNote", OdDbType.Text, POut.StringParam(clockEvent.Note));
Db.NonQ(command, paramNote);
}
///<summary>Updates one TimeAdjust in the database.</summary>
internal static void Update(TimeAdjust timeAdjust)
{
string command = "UPDATE timeadjust SET "
+ "EmployeeNum = " + POut.Long(timeAdjust.EmployeeNum) + ", "
+ "TimeEntry = " + POut.DateT(timeAdjust.TimeEntry) + ", "
+ "RegHours = '" + POut.TSpan(timeAdjust.RegHours) + "', "
+ "OTimeHours = '" + POut.TSpan(timeAdjust.OTimeHours) + "', "
+ "Note = '" + POut.String(timeAdjust.Note) + "', "
+ "IsAuto = " + POut.Bool(timeAdjust.IsAuto) + " "
+ "WHERE TimeAdjustNum = " + POut.Long(timeAdjust.TimeAdjustNum);
Db.NonQ(command);
}
///<summary>Inserts one ClockEvent into the database. Provides option to use the existing priKey. Doesn't use the cache.</summary>
public static long InsertNoCache(ClockEvent clockEvent, bool useExistingPK)
{
bool isRandomKeys = Prefs.GetBoolNoCache(PrefName.RandomPrimaryKeys);
string command = "INSERT INTO clockevent (";
if (!useExistingPK && isRandomKeys)
{
clockEvent.ClockEventNum = ReplicationServers.GetKeyNoCache("clockevent", "ClockEventNum");
}
if (isRandomKeys || useExistingPK)
{
command += "ClockEventNum,";
}
command += "EmployeeNum,TimeEntered1,TimeDisplayed1,ClockStatus,Note,TimeEntered2,TimeDisplayed2,OTimeHours,OTimeAuto,Adjust,AdjustAuto,AdjustIsOverridden,Rate2Hours,Rate2Auto,ClinicNum) VALUES(";
if (isRandomKeys || useExistingPK)
{
command += POut.Long(clockEvent.ClockEventNum) + ",";
}
command +=
POut.Long(clockEvent.EmployeeNum) + ","
+ DbHelper.Now() + ","
+ DbHelper.Now() + ","
+ POut.Int((int)clockEvent.ClockStatus) + ","
+ DbHelper.ParamChar + "paramNote,"
+ POut.DateT(clockEvent.TimeEntered2) + ","
+ POut.DateT(clockEvent.TimeDisplayed2) + ","
+ "'" + POut.TSpan(clockEvent.OTimeHours) + "',"
+ "'" + POut.TSpan(clockEvent.OTimeAuto) + "',"
+ "'" + POut.TSpan(clockEvent.Adjust) + "',"
+ "'" + POut.TSpan(clockEvent.AdjustAuto) + "',"
+ POut.Bool(clockEvent.AdjustIsOverridden) + ","
+ "'" + POut.TSpan(clockEvent.Rate2Hours) + "',"
+ "'" + POut.TSpan(clockEvent.Rate2Auto) + "',"
+ POut.Long(clockEvent.ClinicNum) + ")";
if (clockEvent.Note == null)
{
clockEvent.Note = "";
}
OdSqlParameter paramNote = new OdSqlParameter("paramNote", OdDbType.Text, POut.StringParam(clockEvent.Note));
if (useExistingPK || isRandomKeys)
{
Db.NonQ(command, paramNote);
}
else
{
clockEvent.ClockEventNum = Db.NonQ(command, true, "ClockEventNum", "clockEvent", paramNote);
}
return(clockEvent.ClockEventNum);
}
///<summary>Inserts one TimeAdjust into the database. Provides option to use the existing priKey. Doesn't use the cache.</summary>
public static long InsertNoCache(TimeAdjust timeAdjust, bool useExistingPK)
{
bool isRandomKeys = Prefs.GetBoolNoCache(PrefName.RandomPrimaryKeys);
string command = "INSERT INTO timeadjust (";
if (!useExistingPK && isRandomKeys)
{
timeAdjust.TimeAdjustNum = ReplicationServers.GetKeyNoCache("timeadjust", "TimeAdjustNum");
}
if (isRandomKeys || useExistingPK)
{
command += "TimeAdjustNum,";
}
command += "EmployeeNum,TimeEntry,RegHours,OTimeHours,Note,IsAuto,ClinicNum,PtoDefNum,PtoHours) VALUES(";
if (isRandomKeys || useExistingPK)
{
command += POut.Long(timeAdjust.TimeAdjustNum) + ",";
}
command +=
POut.Long(timeAdjust.EmployeeNum) + ","
+ POut.DateT(timeAdjust.TimeEntry) + ","
+ "'" + POut.TSpan(timeAdjust.RegHours) + "',"
+ "'" + POut.TSpan(timeAdjust.OTimeHours) + "',"
+ DbHelper.ParamChar + "paramNote,"
+ POut.Bool(timeAdjust.IsAuto) + ","
+ POut.Long(timeAdjust.ClinicNum) + ","
+ POut.Long(timeAdjust.PtoDefNum) + ","
+ "'" + POut.TSpan(timeAdjust.PtoHours) + "')";
if (timeAdjust.Note == null)
{
timeAdjust.Note = "";
}
OdSqlParameter paramNote = new OdSqlParameter("paramNote", OdDbType.Text, POut.StringParam(timeAdjust.Note));
if (useExistingPK || isRandomKeys)
{
Db.NonQ(command, paramNote);
}
else
{
timeAdjust.TimeAdjustNum = Db.NonQ(command, true, "TimeAdjustNum", "timeAdjust", paramNote);
}
return(timeAdjust.TimeAdjustNum);
}
///<summary>Updates one ClockEvent in the database.</summary>
internal static void Update(ClockEvent clockEvent)
{
string command = "UPDATE clockevent SET "
+ "EmployeeNum = " + POut.Long(clockEvent.EmployeeNum) + ", "
//TimeEntered1 not allowed to change
+ "TimeDisplayed1 = " + POut.DateT(clockEvent.TimeDisplayed1) + ", "
+ "ClockStatus = " + POut.Int((int)clockEvent.ClockStatus) + ", "
+ "Note = '" + POut.String(clockEvent.Note) + "', "
+ "TimeEntered2 = " + POut.DateT(clockEvent.TimeEntered2) + ", "
+ "TimeDisplayed2 = " + POut.DateT(clockEvent.TimeDisplayed2) + ", "
+ "OTimeHours = '" + POut.TSpan(clockEvent.OTimeHours) + "', "
+ "OTimeAuto = '" + POut.TSpan(clockEvent.OTimeAuto) + "', "
+ "Adjust = '" + POut.TSpan(clockEvent.Adjust) + "', "
+ "AdjustAuto = '" + POut.TSpan(clockEvent.AdjustAuto) + "', "
+ "AdjustIsOverridden= " + POut.Bool(clockEvent.AdjustIsOverridden) + " "
+ "WHERE ClockEventNum = " + POut.Long(clockEvent.ClockEventNum);
Db.NonQ(command);
}
///<summary>Inserts one ClockEvent into the database. Provides option to use the existing priKey.</summary>
public static long Insert(ClockEvent clockEvent, bool useExistingPK)
{
if (!useExistingPK && PrefC.RandomKeys)
{
clockEvent.ClockEventNum = ReplicationServers.GetKey("clockevent", "ClockEventNum");
}
string command = "INSERT INTO clockevent (";
if (useExistingPK || PrefC.RandomKeys)
{
command += "ClockEventNum,";
}
command += "EmployeeNum,TimeEntered1,TimeDisplayed1,ClockStatus,Note,TimeEntered2,TimeDisplayed2,OTimeHours,OTimeAuto,Adjust,AdjustAuto,AdjustIsOverridden,Rate2Hours,Rate2Auto) VALUES(";
if (useExistingPK || PrefC.RandomKeys)
{
command += POut.Long(clockEvent.ClockEventNum) + ",";
}
command +=
POut.Long(clockEvent.EmployeeNum) + ","
+ DbHelper.Now() + ","
+ DbHelper.Now() + ","
+ POut.Int((int)clockEvent.ClockStatus) + ","
+ "'" + POut.String(clockEvent.Note) + "',"
+ POut.DateT(clockEvent.TimeEntered2) + ","
+ POut.DateT(clockEvent.TimeDisplayed2) + ","
+ "'" + POut.TSpan(clockEvent.OTimeHours) + "',"
+ "'" + POut.TSpan(clockEvent.OTimeAuto) + "',"
+ "'" + POut.TSpan(clockEvent.Adjust) + "',"
+ "'" + POut.TSpan(clockEvent.AdjustAuto) + "',"
+ POut.Bool(clockEvent.AdjustIsOverridden) + ","
+ "'" + POut.TSpan(clockEvent.Rate2Hours) + "',"
+ "'" + POut.TSpan(clockEvent.Rate2Auto) + "')";
if (useExistingPK || PrefC.RandomKeys)
{
Db.NonQ(command);
}
else
{
clockEvent.ClockEventNum = Db.NonQ(command, true);
}
return(clockEvent.ClockEventNum);
}
///<summary>Updates one TimeAdjust in the database.</summary>
public static void Update(TimeAdjust timeAdjust)
{
string command = "UPDATE timeadjust SET "
+ "EmployeeNum = " + POut.Long(timeAdjust.EmployeeNum) + ", "
+ "TimeEntry = " + POut.DateT(timeAdjust.TimeEntry) + ", "
+ "RegHours = '" + POut.TSpan(timeAdjust.RegHours) + "', "
+ "OTimeHours = '" + POut.TSpan(timeAdjust.OTimeHours) + "', "
+ "Note = " + DbHelper.ParamChar + "paramNote, "
+ "IsAuto = " + POut.Bool(timeAdjust.IsAuto) + ", "
+ "ClinicNum = " + POut.Long(timeAdjust.ClinicNum) + " "
+ "WHERE TimeAdjustNum = " + POut.Long(timeAdjust.TimeAdjustNum);
if (timeAdjust.Note == null)
{
timeAdjust.Note = "";
}
OdSqlParameter paramNote = new OdSqlParameter("paramNote", OdDbType.Text, POut.StringParam(timeAdjust.Note));
Db.NonQ(command, paramNote);
}
/// <summary></summary>
public static string RunAll()
{
string retVal = "";
//Things that we might later add to this series of tests:
//Special column types such as timestamp
//Computer set to other region, affecting string parsing of types such dates and decimals
//Test types without casting back and forth to strings.
//Retrieval using a variety of techniques, such as getting a table, scalar, and reading a row.
//Blobs
string command = "";
DataTable table;
TimeSpan timespan;
TimeSpan timespan2;
string varchar1;
string varchar2;
//timespan(timeOfDay)----------------------------------------------------------------------------------------------
timespan = new TimeSpan(1, 2, 3); //1hr,2min,3sec
command = "INSERT INTO tempcore (TimeOfDayTest) VALUES (" + POut.Time(timespan) + ")";
DataCore.NonQ(command);
command = "SELECT TimeOfDayTest FROM tempcore";
table = DataCore.GetTable(command);
timespan2 = PIn.Time(table.Rows[0]["TimeOfDayTest"].ToString());
if (timespan != timespan2)
{
throw new Exception();
}
command = "DELETE FROM tempcore";
DataCore.NonQ(command);
retVal += "TimeSpan (time of day): Passed.\r\n";
//timespan, negative------------------------------------------------------------------------------------
timespan = new TimeSpan(0, -36, 0); //This particular timespan value was found to fail in mysql with the old connector.
//Don't know what's so special about this one value. There are probably other values failing as well, but it doesn't matter.
//Oracle does not seem to like negative values.
command = "INSERT INTO tempcore (TimeSpanTest) VALUES ('" + POut.TSpan(timespan) + "')";
DataCore.NonQ(command);
command = "SELECT TimeSpanTest FROM tempcore";
table = DataCore.GetTable(command);
string tempVal = table.Rows[0]["TimeSpanTest"].ToString();
timespan2 = PIn.TSpan(tempVal);
if (timespan != timespan2)
{
throw new Exception();
}
command = "DELETE FROM tempcore";
DataCore.NonQ(command);
retVal += "TimeSpan, negative: Passed.\r\n";
//timespan, over 24 hours-----------------------------------------------------------------------------
timespan = new TimeSpan(432, 5, 17);
command = "INSERT INTO tempcore (TimeSpanTest) VALUES ('" + POut.TSpan(timespan) + "')";
DataCore.NonQ(command);
command = "SELECT TimeSpanTest FROM tempcore";
table = DataCore.GetTable(command);
timespan2 = PIn.TSpan(table.Rows[0]["TimeSpanTest"].ToString());
if (timespan != timespan2)
{
throw new Exception();
}
command = "DELETE FROM tempcore";
DataCore.NonQ(command);
retVal += "TimeSpan, large: Passed.\r\n";
//date----------------------------------------------------------------------------------------------
DateTime date1;
DateTime date2;
date1 = new DateTime(2003, 5, 23);
command = "INSERT INTO tempcore (DateTest) VALUES (" + POut.Date(date1) + ")";
DataCore.NonQ(command);
command = "SELECT DateTest FROM tempcore";
table = DataCore.GetTable(command);
date2 = PIn.Date(table.Rows[0]["DateTest"].ToString());
if (date1 != date2)
{
throw new Exception();
}
command = "DELETE FROM tempcore";
DataCore.NonQ(command);
retVal += "Date: Passed.\r\n";
//datetime------------------------------------------------------------------------------------------
DateTime datet1;
DateTime datet2;
datet1 = new DateTime(2003, 5, 23, 10, 18, 0);
command = "INSERT INTO tempcore (DateTimeTest) VALUES (" + POut.DateT(datet1) + ")";
DataCore.NonQ(command);
command = "SELECT DateTimeTest FROM tempcore";
table = DataCore.GetTable(command);
datet2 = PIn.DateT(table.Rows[0]["DateTimeTest"].ToString());
if (datet1 != datet2)
{
throw new Exception();
}
command = "DELETE FROM tempcore";
DataCore.NonQ(command);
retVal += "Date/Time: Passed.\r\n";
//currency------------------------------------------------------------------------------------------
double double1;
double double2;
double1 = 12.34d;
command = "INSERT INTO tempcore (CurrencyTest) VALUES (" + POut.Double(double1) + ")";
DataCore.NonQ(command);
command = "SELECT CurrencyTest FROM tempcore";
table = DataCore.GetTable(command);
double2 = PIn.Double(table.Rows[0]["CurrencyTest"].ToString());
if (double1 != double2)
{
throw new Exception();
}
command = "DELETE FROM tempcore";
DataCore.NonQ(command);
retVal += "Currency: Passed.\r\n";
//group_concat------------------------------------------------------------------------------------
command = "INSERT INTO tempgroupconcat VALUES ('name1')";
DataCore.NonQ(command);
command = "INSERT INTO tempgroupconcat VALUES ('name2')";
DataCore.NonQ(command);
command = "SELECT " + DbHelper.GroupConcat("Names") + " allnames FROM tempgroupconcat";
table = DataCore.GetTable(command);
string allnames = PIn.ByteArray(table.Rows[0]["allnames"].ToString());
//if(DataConnection.DBtype==DatabaseType.Oracle) {
// allnames=allnames.TrimEnd(',');//known issue. Should already be fixed:
//Use RTRIM(REPLACE(REPLACE(XMLAgg(XMLElement("x",column_name)),'<x>'),'</x>',','))
//}
if (allnames != "name1,name2")
{
throw new Exception();
}
command = "DELETE FROM tempgroupconcat";
DataCore.NonQ(command);
retVal += "Group_concat: Passed.\r\n";
//bool,pos------------------------------------------------------------------------------------
bool bool1;
bool bool2;
bool1 = true;
command = "INSERT INTO tempcore (BoolTest) VALUES (" + POut.Bool(bool1) + ")";
DataCore.NonQ(command);
command = "SELECT BoolTest FROM tempcore";
table = DataCore.GetTable(command);
bool2 = PIn.Bool(table.Rows[0]["BoolTest"].ToString());
if (bool1 != bool2)
{
throw new Exception();
}
command = "DELETE FROM tempcore";
DataCore.NonQ(command);
retVal += "Bool, true: Passed.\r\n";
//bool,neg------------------------------------------------------------------------------------
bool1 = false;
command = "INSERT INTO tempcore (BoolTest) VALUES (" + POut.Bool(bool1) + ")";
DataCore.NonQ(command);
command = "SELECT BoolTest FROM tempcore";
table = DataCore.GetTable(command);
bool2 = PIn.Bool(table.Rows[0]["BoolTest"].ToString());
if (bool1 != bool2)
{
throw new Exception();
}
command = "DELETE FROM tempcore";
DataCore.NonQ(command);
retVal += "Bool, false: Passed.\r\n";
//varchar255 Nonstandard Characters-----------------------------------------------------------
varchar1 = @"'!@#$%^&*()-+[{]}\`~,<.>/?'"";:=_" + "\r\n\t";
varchar2 = "";
command = "INSERT INTO tempcore (TextTinyTest) VALUES ('" + POut.String(varchar1) + "')";
DataCore.NonQ(command);
command = "SELECT TextTinyTest FROM tempcore";
table = DataCore.GetTable(command);
varchar2 = PIn.String(table.Rows[0]["TextTinyTest"].ToString());
if (varchar1 != varchar2)
{
throw new Exception();
}
command = "DELETE FROM tempcore";
DataCore.NonQ(command);
retVal += "VarChar(255): Passed.\r\n";
//VARCHAR2(4000)------------------------------------------------------------------------------
varchar1 = CreateRandomAlphaNumericString(4000); //Tested 4001 and it was too large as expected.
command = "INSERT INTO tempcore (TextSmallTest) VALUES ('" + POut.String(varchar1) + "')";
DataCore.NonQ(command);
command = "SELECT TextSmallTest FROM tempcore";
table = DataCore.GetTable(command);
varchar2 = PIn.String(table.Rows[0]["TextSmallTest"].ToString());
if (varchar1 != varchar2)
{
throw new Exception();
}
command = "DELETE FROM tempcore";
DataCore.NonQ(command);
retVal += "VarChar2(4000): Passed.\r\n";
//clob:-----------------------------------------------------------------------------------------
//tested up to 20MB in oracle. (50MB however was failing: Chunk size error)
//mysql mediumtext maxes out at about 16MB.
string clobstring1 = CreateRandomAlphaNumericString(10485760); //10MB should be larger than anything we store.
string clobstring2 = "";
OdSqlParameter param = new OdSqlParameter("param1", OdDbType.Text, clobstring1);
command = "INSERT INTO tempcore (TextLargeTest) VALUES (" + DbHelper.ParamChar + "param1)";
DataCore.NonQ(command, param);
command = "SELECT TextLargeTest FROM tempcore";
table = DataCore.GetTable(command);
clobstring2 = PIn.String(table.Rows[0]["TextLargeTest"].ToString());
if (clobstring1 != clobstring2)
{
throw new Exception();
}
command = "DELETE FROM tempcore";
DataCore.NonQ(command);
retVal += "Clob, Alpha-Numeric 10MB: Passed.\r\n";
//clob:non-standard----------------------------------------------------------------------------------
clobstring1 = CreateRandomNonStandardString(8000000); //8MB is the max because many chars takes 2 bytes, and mysql maxes out at 16MB
clobstring2 = "";
param = new OdSqlParameter("param1", OdDbType.Text, clobstring1);
command = "INSERT INTO tempcore (TextLargeTest) VALUES (" + DbHelper.ParamChar + "param1)";
DataCore.NonQ(command, param);
command = "SELECT TextLargeTest FROM tempcore";
table = DataCore.GetTable(command);
clobstring2 = PIn.String(table.Rows[0]["TextLargeTest"].ToString());
if (clobstring1 != clobstring2)
{
throw new Exception();
}
command = "DELETE FROM tempcore";
DataCore.NonQ(command);
retVal += "Clob, Symbols and Chinese: Passed.\r\n";
//clob:Rick Roller----------------------------------------------------------------------------------
clobstring1 = RickRoller(10485760); //10MB should be larger than anything we store.
clobstring2 = "";
param = new OdSqlParameter("param1", OdDbType.Text, clobstring1);
command = "INSERT INTO tempcore (TextLargeTest) VALUES (" + DbHelper.ParamChar + "param1)";
DataCore.NonQ(command, param);
command = "SELECT TextLargeTest FROM tempcore";
table = DataCore.GetTable(command);
clobstring2 = PIn.String(table.Rows[0]["TextLargeTest"].ToString());
if (clobstring1 != clobstring2)
{
throw new Exception();
}
command = "DELETE FROM tempcore";
DataCore.NonQ(command);
retVal += "Clob, Rick Roller: Passed.\r\n";
//SHOW CREATE TABLE -----------------------------------------------------------------------
//This command is needed in order to perform a backup.
if (DataConnection.DBtype == DatabaseType.MySql)
{
command = "SHOW CREATE TABLE account";
table = DataCore.GetTable(command);
string createResult = PIn.ByteArray(table.Rows[0][1]);
if (!createResult.StartsWith("CREATE TABLE"))
{
throw new Exception();
}
retVal += "SHOW CREATE TABLE: Passed.\r\n";
}
else
{
retVal += "SHOW CREATE TABLE: Not applicable to Oracle.\r\n";
}
//Single Command Split-------------------------------------------------------------------------
varchar1 = "';\"";
varchar2 = ";'';;;;\"\"\"\"'asdfsadsdaf'";
command = "INSERT INTO tempcore (TextTinyTest,TextSmallTest) VALUES ('" + POut.String(varchar1) + "','" + POut.String(varchar2) + "');";
DataCore.NonQ(command); //Test the split function.
command = "SELECT TextTinyTest,TextSmallTest FROM tempcore";
table = DataCore.GetTable(command);
if (PIn.String(table.Rows[0]["TextTinyTest"].ToString()) != varchar1 || PIn.String(table.Rows[0]["TextSmallTest"].ToString()) != varchar2)
{
throw new ApplicationException();
}
command = "DELETE FROM tempcore";
DataCore.NonQ(command);
retVal += "Single Command Split: Passed.";
//Run multiple non-queries in one transaction--------------------------------------------------
varchar1 = "A";
varchar2 = "B";
command = "INSERT INTO tempcore (TextTinyTest) VALUES ('" + POut.String(varchar1) + "'); DELETE FROM tempcore; INSERT INTO tempcore (TextTinyTest) VALUES ('" + POut.String(varchar2) + "')";
DataCore.NonQ(command);
command = "SELECT TextTinyTest FROM tempcore";
table = DataCore.GetTable(command);
if (PIn.String(table.Rows[0][0].ToString()) != varchar2)
{
throw new ApplicationException();
}
command = "DELETE FROM tempcore";
DataCore.NonQ(command);
retVal += "Multi-Non-Queries: Passed.\r\n";
//Cleanup---------------------------------------------------------------------------------------
if (DataConnection.DBtype == DatabaseType.MySql)
{
command = "DROP TABLE IF EXISTS tempcore";
DataCore.NonQ(command);
command = "DROP TABLE IF EXISTS tempgroupconcat";
DataCore.NonQ(command);
}
else
{
command = "BEGIN EXECUTE IMMEDIATE 'DROP TABLE tempcore'; EXCEPTION WHEN OTHERS THEN NULL; END;";
DataCore.NonQ(command);
command = "BEGIN EXECUTE IMMEDIATE 'DROP TABLE tempgroupconcat'; EXCEPTION WHEN OTHERS THEN NULL; END;";
DataCore.NonQ(command);
}
retVal += "CoreTypes test done.\r\n";
return(retVal);
}
///<summary>Updates one TimeAdjust 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. Returns true if an update occurred.</summary>
public static bool Update(TimeAdjust timeAdjust, TimeAdjust oldTimeAdjust)
{
string command = "";
if (timeAdjust.EmployeeNum != oldTimeAdjust.EmployeeNum)
{
if (command != "")
{
command += ",";
}
command += "EmployeeNum = " + POut.Long(timeAdjust.EmployeeNum) + "";
}
if (timeAdjust.TimeEntry != oldTimeAdjust.TimeEntry)
{
if (command != "")
{
command += ",";
}
command += "TimeEntry = " + POut.DateT(timeAdjust.TimeEntry) + "";
}
if (timeAdjust.RegHours != oldTimeAdjust.RegHours)
{
if (command != "")
{
command += ",";
}
command += "RegHours = '" + POut.TSpan(timeAdjust.RegHours) + "'";
}
if (timeAdjust.OTimeHours != oldTimeAdjust.OTimeHours)
{
if (command != "")
{
command += ",";
}
command += "OTimeHours = '" + POut.TSpan(timeAdjust.OTimeHours) + "'";
}
if (timeAdjust.Note != oldTimeAdjust.Note)
{
if (command != "")
{
command += ",";
}
command += "Note = " + DbHelper.ParamChar + "paramNote";
}
if (timeAdjust.IsAuto != oldTimeAdjust.IsAuto)
{
if (command != "")
{
command += ",";
}
command += "IsAuto = " + POut.Bool(timeAdjust.IsAuto) + "";
}
if (timeAdjust.ClinicNum != oldTimeAdjust.ClinicNum)
{
if (command != "")
{
command += ",";
}
command += "ClinicNum = " + POut.Long(timeAdjust.ClinicNum) + "";
}
if (command == "")
{
return(false);
}
if (timeAdjust.Note == null)
{
timeAdjust.Note = "";
}
OdSqlParameter paramNote = new OdSqlParameter("paramNote", OdDbType.Text, POut.StringParam(timeAdjust.Note));
command = "UPDATE timeadjust SET " + command
+ " WHERE TimeAdjustNum = " + POut.Long(timeAdjust.TimeAdjustNum);
Db.NonQ(command, paramNote);
return(true);
}
///<summary>Updates one ClockEvent 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(ClockEvent clockEvent, ClockEvent oldClockEvent)
{
string command = "";
if (clockEvent.EmployeeNum != oldClockEvent.EmployeeNum)
{
if (command != "")
{
command += ",";
}
command += "EmployeeNum = " + POut.Long(clockEvent.EmployeeNum) + "";
}
//TimeEntered1 not allowed to change
if (clockEvent.TimeDisplayed1 != oldClockEvent.TimeDisplayed1)
{
if (command != "")
{
command += ",";
}
command += "TimeDisplayed1 = " + POut.DateT(clockEvent.TimeDisplayed1) + "";
}
if (clockEvent.ClockStatus != oldClockEvent.ClockStatus)
{
if (command != "")
{
command += ",";
}
command += "ClockStatus = " + POut.Int((int)clockEvent.ClockStatus) + "";
}
if (clockEvent.Note != oldClockEvent.Note)
{
if (command != "")
{
command += ",";
}
command += "Note = '" + POut.String(clockEvent.Note) + "'";
}
if (clockEvent.TimeEntered2 != oldClockEvent.TimeEntered2)
{
if (command != "")
{
command += ",";
}
command += "TimeEntered2 = " + POut.DateT(clockEvent.TimeEntered2) + "";
}
if (clockEvent.TimeDisplayed2 != oldClockEvent.TimeDisplayed2)
{
if (command != "")
{
command += ",";
}
command += "TimeDisplayed2 = " + POut.DateT(clockEvent.TimeDisplayed2) + "";
}
if (clockEvent.OTimeHours != oldClockEvent.OTimeHours)
{
if (command != "")
{
command += ",";
}
command += "OTimeHours = '" + POut.TSpan(clockEvent.OTimeHours) + "'";
}
if (clockEvent.OTimeAuto != oldClockEvent.OTimeAuto)
{
if (command != "")
{
command += ",";
}
command += "OTimeAuto = '" + POut.TSpan(clockEvent.OTimeAuto) + "'";
}
if (clockEvent.Adjust != oldClockEvent.Adjust)
{
if (command != "")
{
command += ",";
}
command += "Adjust = '" + POut.TSpan(clockEvent.Adjust) + "'";
}
if (clockEvent.AdjustAuto != oldClockEvent.AdjustAuto)
{
if (command != "")
{
command += ",";
}
command += "AdjustAuto = '" + POut.TSpan(clockEvent.AdjustAuto) + "'";
}
if (clockEvent.AdjustIsOverridden != oldClockEvent.AdjustIsOverridden)
{
if (command != "")
{
command += ",";
}
command += "AdjustIsOverridden = " + POut.Bool(clockEvent.AdjustIsOverridden) + "";
}
if (command == "")
{
return;
}
command = "UPDATE clockevent SET " + command
+ " WHERE ClockEventNum = " + POut.Long(clockEvent.ClockEventNum);
Db.NonQ(command);
}
///<summary>Updates one ClockEvent 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. Returns true if an update occurred.</summary>
public static bool Update(ClockEvent clockEvent, ClockEvent oldClockEvent)
{
string command = "";
if (clockEvent.EmployeeNum != oldClockEvent.EmployeeNum)
{
if (command != "")
{
command += ",";
}
command += "EmployeeNum = " + POut.Long(clockEvent.EmployeeNum) + "";
}
//TimeEntered1 not allowed to change
if (clockEvent.TimeDisplayed1 != oldClockEvent.TimeDisplayed1)
{
if (command != "")
{
command += ",";
}
command += "TimeDisplayed1 = " + POut.DateT(clockEvent.TimeDisplayed1) + "";
}
if (clockEvent.ClockStatus != oldClockEvent.ClockStatus)
{
if (command != "")
{
command += ",";
}
command += "ClockStatus = " + POut.Int((int)clockEvent.ClockStatus) + "";
}
if (clockEvent.Note != oldClockEvent.Note)
{
if (command != "")
{
command += ",";
}
command += "Note = " + DbHelper.ParamChar + "paramNote";
}
if (clockEvent.TimeEntered2 != oldClockEvent.TimeEntered2)
{
if (command != "")
{
command += ",";
}
command += "TimeEntered2 = " + POut.DateT(clockEvent.TimeEntered2) + "";
}
if (clockEvent.TimeDisplayed2 != oldClockEvent.TimeDisplayed2)
{
if (command != "")
{
command += ",";
}
command += "TimeDisplayed2 = " + POut.DateT(clockEvent.TimeDisplayed2) + "";
}
if (clockEvent.OTimeHours != oldClockEvent.OTimeHours)
{
if (command != "")
{
command += ",";
}
command += "OTimeHours = '" + POut.TSpan(clockEvent.OTimeHours) + "'";
}
if (clockEvent.OTimeAuto != oldClockEvent.OTimeAuto)
{
if (command != "")
{
command += ",";
}
command += "OTimeAuto = '" + POut.TSpan(clockEvent.OTimeAuto) + "'";
}
if (clockEvent.Adjust != oldClockEvent.Adjust)
{
if (command != "")
{
command += ",";
}
command += "Adjust = '" + POut.TSpan(clockEvent.Adjust) + "'";
}
if (clockEvent.AdjustAuto != oldClockEvent.AdjustAuto)
{
if (command != "")
{
command += ",";
}
command += "AdjustAuto = '" + POut.TSpan(clockEvent.AdjustAuto) + "'";
}
if (clockEvent.AdjustIsOverridden != oldClockEvent.AdjustIsOverridden)
{
if (command != "")
{
command += ",";
}
command += "AdjustIsOverridden = " + POut.Bool(clockEvent.AdjustIsOverridden) + "";
}
if (clockEvent.Rate2Hours != oldClockEvent.Rate2Hours)
{
if (command != "")
{
command += ",";
}
command += "Rate2Hours = '" + POut.TSpan(clockEvent.Rate2Hours) + "'";
}
if (clockEvent.Rate2Auto != oldClockEvent.Rate2Auto)
{
if (command != "")
{
command += ",";
}
command += "Rate2Auto = '" + POut.TSpan(clockEvent.Rate2Auto) + "'";
}
if (clockEvent.ClinicNum != oldClockEvent.ClinicNum)
{
if (command != "")
{
command += ",";
}
command += "ClinicNum = " + POut.Long(clockEvent.ClinicNum) + "";
}
if (command == "")
{
return(false);
}
if (clockEvent.Note == null)
{
clockEvent.Note = "";
}
OdSqlParameter paramNote = new OdSqlParameter("paramNote", OdDbType.Text, POut.StringParam(clockEvent.Note));
command = "UPDATE clockevent SET " + command
+ " WHERE ClockEventNum = " + POut.Long(clockEvent.ClockEventNum);
Db.NonQ(command, paramNote);
return(true);
}
