///<summary>Converts a DataTable to a list of objects.</summary>
public static List <ApptView> TableToList(DataTable table)
{
List <ApptView> retVal = new List <ApptView>();
ApptView apptView;
foreach (DataRow row in table.Rows)
{
apptView = new ApptView();
apptView.ApptViewNum = PIn.Long(row["ApptViewNum"].ToString());
apptView.Description = PIn.String(row["Description"].ToString());
apptView.ItemOrder = PIn.Int(row["ItemOrder"].ToString());
apptView.RowsPerIncr = PIn.Byte(row["RowsPerIncr"].ToString());
apptView.OnlyScheduledProvs = PIn.Bool(row["OnlyScheduledProvs"].ToString());
apptView.OnlySchedBeforeTime = PIn.Time(row["OnlySchedBeforeTime"].ToString());
apptView.OnlySchedAfterTime = PIn.Time(row["OnlySchedAfterTime"].ToString());
apptView.StackBehavUR = (OpenDentBusiness.ApptViewStackBehavior)PIn.Int(row["StackBehavUR"].ToString());
apptView.StackBehavLR = (OpenDentBusiness.ApptViewStackBehavior)PIn.Int(row["StackBehavLR"].ToString());
apptView.ClinicNum = PIn.Long(row["ClinicNum"].ToString());
apptView.ApptTimeScrollStart = PIn.Time(row["ApptTimeScrollStart"].ToString());
apptView.IsScrollStartDynamic = PIn.Bool(row["IsScrollStartDynamic"].ToString());
apptView.IsApptBubblesDisabled = PIn.Bool(row["IsApptBubblesDisabled"].ToString());
apptView.WidthOpMinimum = PIn.Int(row["WidthOpMinimum"].ToString());
retVal.Add(apptView);
}
return(retVal);
}
///<summary>Converts a DataTable to a list of objects.</summary>
internal static List <Procedure> TableToList(DataTable table)
{
List <Procedure> retVal = new List <Procedure>();
Procedure procedure;
for (int i = 0; i < table.Rows.Count; i++)
{
procedure = new Procedure();
procedure.ProcNum = PIn.Long(table.Rows[i]["ProcNum"].ToString());
procedure.PatNum = PIn.Long(table.Rows[i]["PatNum"].ToString());
procedure.AptNum = PIn.Long(table.Rows[i]["AptNum"].ToString());
procedure.OldCode = PIn.String(table.Rows[i]["OldCode"].ToString());
procedure.ProcDate = PIn.Date(table.Rows[i]["ProcDate"].ToString());
procedure.ProcFee = PIn.Double(table.Rows[i]["ProcFee"].ToString());
procedure.Surf = PIn.String(table.Rows[i]["Surf"].ToString());
procedure.ToothNum = PIn.String(table.Rows[i]["ToothNum"].ToString());
procedure.ToothRange = PIn.String(table.Rows[i]["ToothRange"].ToString());
procedure.Priority = PIn.Long(table.Rows[i]["Priority"].ToString());
procedure.ProcStatus = (ProcStat)PIn.Int(table.Rows[i]["ProcStatus"].ToString());
procedure.ProvNum = PIn.Long(table.Rows[i]["ProvNum"].ToString());
procedure.Dx = PIn.Long(table.Rows[i]["Dx"].ToString());
procedure.PlannedAptNum = PIn.Long(table.Rows[i]["PlannedAptNum"].ToString());
procedure.PlaceService = (PlaceOfService)PIn.Int(table.Rows[i]["PlaceService"].ToString());
procedure.Prosthesis = PIn.String(table.Rows[i]["Prosthesis"].ToString());
procedure.DateOriginalProsth = PIn.Date(table.Rows[i]["DateOriginalProsth"].ToString());
procedure.ClaimNote = PIn.String(table.Rows[i]["ClaimNote"].ToString());
procedure.DateEntryC = PIn.Date(table.Rows[i]["DateEntryC"].ToString());
procedure.ClinicNum = PIn.Long(table.Rows[i]["ClinicNum"].ToString());
procedure.MedicalCode = PIn.String(table.Rows[i]["MedicalCode"].ToString());
procedure.DiagnosticCode = PIn.String(table.Rows[i]["DiagnosticCode"].ToString());
procedure.IsPrincDiag = PIn.Bool(table.Rows[i]["IsPrincDiag"].ToString());
procedure.ProcNumLab = PIn.Long(table.Rows[i]["ProcNumLab"].ToString());
procedure.BillingTypeOne = PIn.Long(table.Rows[i]["BillingTypeOne"].ToString());
procedure.BillingTypeTwo = PIn.Long(table.Rows[i]["BillingTypeTwo"].ToString());
procedure.CodeNum = PIn.Long(table.Rows[i]["CodeNum"].ToString());
procedure.CodeMod1 = PIn.String(table.Rows[i]["CodeMod1"].ToString());
procedure.CodeMod2 = PIn.String(table.Rows[i]["CodeMod2"].ToString());
procedure.CodeMod3 = PIn.String(table.Rows[i]["CodeMod3"].ToString());
procedure.CodeMod4 = PIn.String(table.Rows[i]["CodeMod4"].ToString());
procedure.RevCode = PIn.String(table.Rows[i]["RevCode"].ToString());
procedure.UnitQty = PIn.Int(table.Rows[i]["UnitQty"].ToString());
procedure.BaseUnits = PIn.Int(table.Rows[i]["BaseUnits"].ToString());
procedure.StartTime = PIn.Int(table.Rows[i]["StartTime"].ToString());
procedure.StopTime = PIn.Int(table.Rows[i]["StopTime"].ToString());
procedure.DateTP = PIn.Date(table.Rows[i]["DateTP"].ToString());
procedure.SiteNum = PIn.Long(table.Rows[i]["SiteNum"].ToString());
procedure.HideGraphics = PIn.Bool(table.Rows[i]["HideGraphics"].ToString());
procedure.CanadianTypeCodes = PIn.String(table.Rows[i]["CanadianTypeCodes"].ToString());
procedure.ProcTime = PIn.Time(table.Rows[i]["ProcTime"].ToString());
procedure.ProcTimeEnd = PIn.Time(table.Rows[i]["ProcTimeEnd"].ToString());
procedure.DateTStamp = PIn.DateT(table.Rows[i]["DateTStamp"].ToString());
procedure.Prognosis = PIn.Long(table.Rows[i]["Prognosis"].ToString());
procedure.DrugUnit = (EnumProcDrugUnit)PIn.Int(table.Rows[i]["DrugUnit"].ToString());
procedure.DrugQty = PIn.Float(table.Rows[i]["DrugQty"].ToString());
retVal.Add(procedure);
}
return(retVal);
}
///<summary>Converts a DataTable to a list of objects.</summary>
internal static List <TimeCardRule> TableToList(DataTable table)
{
List <TimeCardRule> retVal = new List <TimeCardRule>();
TimeCardRule timeCardRule;
for (int i = 0; i < table.Rows.Count; i++)
{
timeCardRule = new TimeCardRule();
timeCardRule.TimeCardRuleNum = PIn.Long(table.Rows[i]["TimeCardRuleNum"].ToString());
timeCardRule.EmployeeNum = PIn.Long(table.Rows[i]["EmployeeNum"].ToString());
timeCardRule.OverHoursPerDay = PIn.Time(table.Rows[i]["OverHoursPerDay"].ToString());
timeCardRule.AfterTimeOfDay = PIn.Time(table.Rows[i]["AfterTimeOfDay"].ToString());
retVal.Add(timeCardRule);
}
return(retVal);
}
///<summary>Converts a DataTable to a list of objects.</summary>
public static List <TimeCardRule> TableToList(DataTable table)
{
List <TimeCardRule> retVal = new List <TimeCardRule>();
TimeCardRule timeCardRule;
foreach (DataRow row in table.Rows)
{
timeCardRule = new TimeCardRule();
timeCardRule.TimeCardRuleNum = PIn.Long(row["TimeCardRuleNum"].ToString());
timeCardRule.EmployeeNum = PIn.Long(row["EmployeeNum"].ToString());
timeCardRule.OverHoursPerDay = PIn.Time(row["OverHoursPerDay"].ToString());
timeCardRule.AfterTimeOfDay = PIn.Time(row["AfterTimeOfDay"].ToString());
timeCardRule.BeforeTimeOfDay = PIn.Time(row["BeforeTimeOfDay"].ToString());
timeCardRule.IsOvertimeExempt = PIn.Bool(row["IsOvertimeExempt"].ToString());
retVal.Add(timeCardRule);
}
return(retVal);
}
///<summary>Converts a DataTable to a list of objects.</summary>
public static List <ApptView> TableToList(DataTable table)
{
List <ApptView> retVal = new List <ApptView>();
ApptView apptView;
for (int i = 0; i < table.Rows.Count; i++)
{
apptView = new ApptView();
apptView.ApptViewNum = PIn.Long(table.Rows[i]["ApptViewNum"].ToString());
apptView.Description = PIn.String(table.Rows[i]["Description"].ToString());
apptView.ItemOrder = PIn.Int(table.Rows[i]["ItemOrder"].ToString());
apptView.RowsPerIncr = PIn.Byte(table.Rows[i]["RowsPerIncr"].ToString());
apptView.OnlyScheduledProvs = PIn.Bool(table.Rows[i]["OnlyScheduledProvs"].ToString());
apptView.OnlySchedBeforeTime = PIn.Time(table.Rows[i]["OnlySchedBeforeTime"].ToString());
apptView.OnlySchedAfterTime = PIn.Time(table.Rows[i]["OnlySchedAfterTime"].ToString());
apptView.StackBehavUR = (ApptViewStackBehavior)PIn.Int(table.Rows[i]["StackBehavUR"].ToString());
apptView.StackBehavLR = (ApptViewStackBehavior)PIn.Int(table.Rows[i]["StackBehavLR"].ToString());
apptView.ClinicNum = PIn.Long(table.Rows[i]["ClinicNum"].ToString());
retVal.Add(apptView);
}
return(retVal);
}
///<summary>Converts a DataTable to a list of objects.</summary>
internal static List <Schedule> TableToList(DataTable table)
{
List <Schedule> retVal = new List <Schedule>();
Schedule schedule;
for (int i = 0; i < table.Rows.Count; i++)
{
schedule = new Schedule();
schedule.ScheduleNum = PIn.Long(table.Rows[i]["ScheduleNum"].ToString());
schedule.SchedDate = PIn.Date(table.Rows[i]["SchedDate"].ToString());
schedule.StartTime = PIn.Time(table.Rows[i]["StartTime"].ToString());
schedule.StopTime = PIn.Time(table.Rows[i]["StopTime"].ToString());
schedule.SchedType = (ScheduleType)PIn.Int(table.Rows[i]["SchedType"].ToString());
schedule.ProvNum = PIn.Long(table.Rows[i]["ProvNum"].ToString());
schedule.BlockoutType = PIn.Long(table.Rows[i]["BlockoutType"].ToString());
schedule.Note = PIn.String(table.Rows[i]["Note"].ToString());
schedule.Status = (SchedStatus)PIn.Int(table.Rows[i]["Status"].ToString());
schedule.EmployeeNum = PIn.Long(table.Rows[i]["EmployeeNum"].ToString());
schedule.DateTStamp = PIn.DateT(table.Rows[i]["DateTStamp"].ToString());
retVal.Add(schedule);
}
return(retVal);
}
///<summary>Converts a DataTable to a list of objects.</summary>
public static List <Schedule> TableToList(DataTable table)
{
List <Schedule> retVal = new List <Schedule>();
Schedule schedule;
foreach (DataRow row in table.Rows)
{
schedule = new Schedule();
schedule.ScheduleNum = PIn.Long(row["ScheduleNum"].ToString());
schedule.SchedDate = PIn.Date(row["SchedDate"].ToString());
schedule.StartTime = PIn.Time(row["StartTime"].ToString());
schedule.StopTime = PIn.Time(row["StopTime"].ToString());
schedule.SchedType = (OpenDentBusiness.ScheduleType)PIn.Int(row["SchedType"].ToString());
schedule.ProvNum = PIn.Long(row["ProvNum"].ToString());
schedule.BlockoutType = PIn.Long(row["BlockoutType"].ToString());
schedule.Note = PIn.String(row["Note"].ToString());
schedule.Status = (OpenDentBusiness.SchedStatus)PIn.Int(row["Status"].ToString());
schedule.EmployeeNum = PIn.Long(row["EmployeeNum"].ToString());
schedule.DateTStamp = PIn.DateT(row["DateTStamp"].ToString());
schedule.ClinicNum = PIn.Long(row["ClinicNum"].ToString());
retVal.Add(schedule);
}
return(retVal);
}
///<summary>Converts a DataTable to a list of objects.</summary>
internal static List <Patient> TableToList(DataTable table)
{
List <Patient> retVal = new List <Patient>();
Patient patient;
for (int i = 0; i < table.Rows.Count; i++)
{
patient = new Patient();
patient.PatNum = PIn.Long(table.Rows[i]["PatNum"].ToString());
patient.LName = PIn.String(table.Rows[i]["LName"].ToString());
patient.FName = PIn.String(table.Rows[i]["FName"].ToString());
patient.MiddleI = PIn.String(table.Rows[i]["MiddleI"].ToString());
patient.Preferred = PIn.String(table.Rows[i]["Preferred"].ToString());
patient.PatStatus = (PatientStatus)PIn.Int(table.Rows[i]["PatStatus"].ToString());
patient.Gender = (PatientGender)PIn.Int(table.Rows[i]["Gender"].ToString());
patient.Position = (PatientPosition)PIn.Int(table.Rows[i]["Position"].ToString());
patient.Birthdate = PIn.Date(table.Rows[i]["Birthdate"].ToString());
patient.SSN = PIn.String(table.Rows[i]["SSN"].ToString());
patient.Address = PIn.String(table.Rows[i]["Address"].ToString());
patient.Address2 = PIn.String(table.Rows[i]["Address2"].ToString());
patient.City = PIn.String(table.Rows[i]["City"].ToString());
patient.State = PIn.String(table.Rows[i]["State"].ToString());
patient.Zip = PIn.String(table.Rows[i]["Zip"].ToString());
patient.HmPhone = PIn.String(table.Rows[i]["HmPhone"].ToString());
patient.WkPhone = PIn.String(table.Rows[i]["WkPhone"].ToString());
patient.WirelessPhone = PIn.String(table.Rows[i]["WirelessPhone"].ToString());
patient.Guarantor = PIn.Long(table.Rows[i]["Guarantor"].ToString());
patient.CreditType = PIn.String(table.Rows[i]["CreditType"].ToString());
patient.Email = PIn.String(table.Rows[i]["Email"].ToString());
patient.Salutation = PIn.String(table.Rows[i]["Salutation"].ToString());
patient.EstBalance = PIn.Double(table.Rows[i]["EstBalance"].ToString());
patient.PriProv = PIn.Long(table.Rows[i]["PriProv"].ToString());
patient.SecProv = PIn.Long(table.Rows[i]["SecProv"].ToString());
patient.FeeSched = PIn.Long(table.Rows[i]["FeeSched"].ToString());
patient.BillingType = PIn.Long(table.Rows[i]["BillingType"].ToString());
patient.ImageFolder = PIn.String(table.Rows[i]["ImageFolder"].ToString());
patient.AddrNote = PIn.String(table.Rows[i]["AddrNote"].ToString());
patient.FamFinUrgNote = PIn.String(table.Rows[i]["FamFinUrgNote"].ToString());
patient.MedUrgNote = PIn.String(table.Rows[i]["MedUrgNote"].ToString());
patient.ApptModNote = PIn.String(table.Rows[i]["ApptModNote"].ToString());
patient.StudentStatus = PIn.String(table.Rows[i]["StudentStatus"].ToString());
patient.SchoolName = PIn.String(table.Rows[i]["SchoolName"].ToString());
patient.ChartNumber = PIn.String(table.Rows[i]["ChartNumber"].ToString());
patient.MedicaidID = PIn.String(table.Rows[i]["MedicaidID"].ToString());
patient.Bal_0_30 = PIn.Double(table.Rows[i]["Bal_0_30"].ToString());
patient.Bal_31_60 = PIn.Double(table.Rows[i]["Bal_31_60"].ToString());
patient.Bal_61_90 = PIn.Double(table.Rows[i]["Bal_61_90"].ToString());
patient.BalOver90 = PIn.Double(table.Rows[i]["BalOver90"].ToString());
patient.InsEst = PIn.Double(table.Rows[i]["InsEst"].ToString());
patient.BalTotal = PIn.Double(table.Rows[i]["BalTotal"].ToString());
patient.EmployerNum = PIn.Long(table.Rows[i]["EmployerNum"].ToString());
patient.EmploymentNote = PIn.String(table.Rows[i]["EmploymentNote"].ToString());
patient.Race = (PatientRace)PIn.Int(table.Rows[i]["Race"].ToString());
patient.County = PIn.String(table.Rows[i]["County"].ToString());
patient.GradeLevel = (PatientGrade)PIn.Int(table.Rows[i]["GradeLevel"].ToString());
patient.Urgency = (TreatmentUrgency)PIn.Int(table.Rows[i]["Urgency"].ToString());
patient.DateFirstVisit = PIn.Date(table.Rows[i]["DateFirstVisit"].ToString());
patient.ClinicNum = PIn.Long(table.Rows[i]["ClinicNum"].ToString());
patient.HasIns = PIn.String(table.Rows[i]["HasIns"].ToString());
patient.TrophyFolder = PIn.String(table.Rows[i]["TrophyFolder"].ToString());
patient.PlannedIsDone = PIn.Bool(table.Rows[i]["PlannedIsDone"].ToString());
patient.Premed = PIn.Bool(table.Rows[i]["Premed"].ToString());
patient.Ward = PIn.String(table.Rows[i]["Ward"].ToString());
patient.PreferConfirmMethod = (ContactMethod)PIn.Int(table.Rows[i]["PreferConfirmMethod"].ToString());
patient.PreferContactMethod = (ContactMethod)PIn.Int(table.Rows[i]["PreferContactMethod"].ToString());
patient.PreferRecallMethod = (ContactMethod)PIn.Int(table.Rows[i]["PreferRecallMethod"].ToString());
patient.SchedBeforeTime = PIn.Time(table.Rows[i]["SchedBeforeTime"].ToString());
patient.SchedAfterTime = PIn.Time(table.Rows[i]["SchedAfterTime"].ToString());
patient.SchedDayOfWeek = PIn.Byte(table.Rows[i]["SchedDayOfWeek"].ToString());
patient.Language = PIn.String(table.Rows[i]["Language"].ToString());
patient.AdmitDate = PIn.Date(table.Rows[i]["AdmitDate"].ToString());
patient.Title = PIn.String(table.Rows[i]["Title"].ToString());
patient.PayPlanDue = PIn.Double(table.Rows[i]["PayPlanDue"].ToString());
patient.SiteNum = PIn.Long(table.Rows[i]["SiteNum"].ToString());
patient.DateTStamp = PIn.DateT(table.Rows[i]["DateTStamp"].ToString());
patient.ResponsParty = PIn.Long(table.Rows[i]["ResponsParty"].ToString());
patient.CanadianEligibilityCode = PIn.Byte(table.Rows[i]["CanadianEligibilityCode"].ToString());
patient.AskToArriveEarly = PIn.Int(table.Rows[i]["AskToArriveEarly"].ToString());
patient.OnlinePassword = PIn.String(table.Rows[i]["OnlinePassword"].ToString());
patient.SmokeStatus = (SmokingStatus)PIn.Int(table.Rows[i]["SmokeStatus"].ToString());
patient.PreferContactConfidential = (ContactMethod)PIn.Int(table.Rows[i]["PreferContactConfidential"].ToString());
patient.SuperFamily = PIn.Long(table.Rows[i]["SuperFamily"].ToString());
retVal.Add(patient);
}
return(retVal);
}
/// <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);
}