///<summary>This gets all new fields which are found in the table definition but not in the database. Result will be empty if the table itself is not in the database.</summary>
public static List <FieldInfo> GetNewFields(FieldInfo[] fields, Type typeClass, string dbName)
{
string tablename = GetTableName(typeClass);
string command = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = '" + dbName + "' AND table_name = '" + tablename + "'";
if (DataCore.GetScalar(command) != "1")
{
return(new List <FieldInfo>());
}
command = "SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS "
+ "WHERE table_name = '" + tablename + "' AND table_schema = '" + dbName + "'";
DataTable table = DataCore.GetTable(command);
List <FieldInfo> retVal = new List <FieldInfo>();
for (int i = 0; i < fields.Length; i++)
{
if (IsNotDbColumn(fields[i]))
{
continue;
}
bool found = false;;
for (int t = 0; t < table.Rows.Count; t++)
{
if (table.Rows[t]["COLUMN_NAME"].ToString().ToLower() == fields[i].Name.ToLower())
{
found = true;
}
}
if (!found)
{
retVal.Add(fields[i]);
}
}
return(retVal);
}
///<summary>Makes sure the tablename is valid. Goes through each column and makes sure that the column is present and that the type in the database is a supported type for this C# data type. Throws exception if it fails.</summary>
public static void ValidateTypes(Type typeClass, string dbName)
{
string tablename = GetTableName(typeClass);
string command = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = '" + dbName + "' AND table_name = '" + tablename + "'";
if (DataCore.GetScalar(command) != "1")
{
return; //can't validate
}
command = "SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS "
+ "WHERE table_name = '" + tablename + "' AND table_schema = '" + dbName + "'";
DataTable table = DataCore.GetTable(command);
//We also are going to check to make sure there are not any extra columns in the database that are not in the code.
HashSet <string> setDbColumns = new HashSet <string>(table.Select().Select(x => x["COLUMN_NAME"].ToString()));
FieldInfo[] fields = typeClass.GetFields();
for (int i = 0; i < fields.Length; i++)
{
if (IsNotDbColumn(fields[i]))
{
continue;
}
ValidateColumn(dbName, tablename, fields[i], table);
setDbColumns.Remove(fields[i].Name);
}
if (setDbColumns.Count > 0)
{
throw new Exception("Table " + tablename + " has columns that are not a part of its corresponding TableType class: "
+ string.Join(", ", setDbColumns));
}
}
///<summary>Gets a list of WebForms_Sheet objects from the database using a query.</summary>
public static List <WebForms_Sheet> SelectMany(string command)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
throw new ApplicationException("Not allowed to send sql directly. Rewrite the calling class to not use this query:\r\n" + command);
}
List <WebForms_Sheet> list = TableToList(DataCore.GetTable(command));
return(list);
}
///<summary>Gets one WebForms_Sheet object from the database using the primary key. Returns null if not found.</summary>
public static WebForms_Sheet SelectOne(long sheetID)
{
string command = "SELECT * FROM webforms_sheet "
+ "WHERE SheetID = " + POut.Long(sheetID);
List <WebForms_Sheet> list = TableToList(DataCore.GetTable(command));
if (list.Count == 0)
{
return(null);
}
return(list[0]);
}
///<summary>Gets one WebForms_SheetFieldDef object from the database using the primary key. Returns null if not found.</summary>
public static WebForms_SheetFieldDef SelectOne(long webSheetFieldDefID)
{
string command = "SELECT * FROM webforms_sheetfielddef "
+ "WHERE WebSheetFieldDefID = " + POut.Long(webSheetFieldDefID);
List <WebForms_SheetFieldDef> list = TableToList(DataCore.GetTable(command));
if (list.Count == 0)
{
return(null);
}
return(list[0]);
}
///<summary>Gets one WebForms_Preference object from the database using the primary key. Returns null if not found.</summary>
public static WebForms_Preference SelectOne(long dentalOfficeID)
{
string command = "SELECT * FROM webforms_preference "
+ "WHERE DentalOfficeID = " + POut.Long(dentalOfficeID);
List <WebForms_Preference> list = TableToList(DataCore.GetTable(command));
if (list.Count == 0)
{
return(null);
}
return(list[0]);
}
///<summary>Executes the createTableStatement passed in if the table does not exists in the current database context.</summary>
public static void CreateTableIfNeeded(string tableName, string createTableStatement)
{
string command = $@"SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = '{POut.String(tableName)}'
LIMIT 1" ;
DataTable table = DataCore.GetTable(command);
if (table.Rows.Count == 0)
{
DataCore.NonQ(createTableStatement);
}
}
///<summary>Unlike in the main program, this conversion script runs on every startup.
///Throws exceptions in order to indicate to the user that the plug-in has failed to load.</summary>
public static void Begin()
{
//Any methods that directly run queries should be public static methods that can be invoked by the middle tier layer.
//This is because workstations that are connected via the middle tier do not have a direct connection to the database possible (most likely).
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
Meth.GetVoid(MethodBase.GetCurrentMethod());
return;
}
Version MainAppVersion = new Version(System.Windows.Forms.Application.ProductVersion); //eg. 6.8.0.0
Version MainAppMajMin = new Version(MainAppVersion.Major, MainAppVersion.Minor); //eg. 6.8
Version ThisAssemblyVersion = System.Reflection.Assembly.GetExecutingAssembly().GetName().Version; //eg. 6.7.0.0
Version ThisAssMajMin = new Version(ThisAssemblyVersion.Major, ThisAssemblyVersion.Minor); //eg. 6.7
if (ThisAssMajMin > MainAppMajMin)
{
//Prevent the use of a new plugin with an old version of OD. User should upgrade OD.
throw new ApplicationException("Version of plug-in may not be newer than version of Open Dental.");
}
//In case the developer of a plug-in gets hit by a bus etc, there should be no restriction on using an old plugin with a new version of OD.
//If a plug-in is distributed, there should be a separate plugin available for each minor version.
string command = "SELECT ValueString FROM preference WHERE PrefName='Plugin_JSS_DataBaseVersion'"; //notice the very unique name.
DataTable table = DataCore.GetTable(command);
if (table.Rows.Count == 0) //This plugin has never run before.
{
command = "INSERT INTO preference (PrefName,ValueString) VALUES('Plugin_JSS_DataBaseVersion','1.0.0.0')";
DataCore.NonQ(command);
FromVersion = new Version(1, 0);
}
else
{
FromVersion = new Version(table.Rows[0][0].ToString());
}
if (FromVersion < new Version("6.8.0.0")) //6.8.1.0
//remember to NEVER use "0" versions (head), which are still in development.
{
command = "DROP TABLE IF EXISTS jss_dev_myveryuniquetable"; //best practice to drop first in case file is present from an old backup.
DataCore.NonQ(command);
command = @"CREATE TABLE jss_dev_myveryuniquetable (
PriKeyNum INT AUTO_INCREMENT,
ForeignKeyNum INT,
Descript VARCHAR(255),
PRIMARY KEY(PriKeyNum)
)" ;
DataCore.NonQ(command);
command = "UPDATE preference SET ValueString = '6.8.0.0' WHERE PrefName = 'Plugin_JSS_DataBaseVersion'";
DataCore.NonQ(command);
}
To7_1_0();
}
///<summary>Gets one WebForms_SheetField object from the database using a query.</summary>
public static WebForms_SheetField SelectOne(string command)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
throw new ApplicationException("Not allowed to send sql directly. Rewrite the calling class to not use this query:\r\n" + command);
}
List <WebForms_SheetField> list = TableToList(DataCore.GetTable(command));
if (list.Count == 0)
{
return(null);
}
return(list[0]);
}
///<summary>Creates a database and a preference table so that DataConnection can pass our arbitrary test query.</summary>
public static void CreateDatabaseIfNeeded(string databaseName)
{
string command = $"SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '{POut.String(databaseName)}'";
DataTable table = DataCore.GetTable(command);
//Create a custom database if needed.
if (table.Rows.Count == 0)
{
DataCore.NonQ($"CREATE DATABASE `{POut.String(databaseName)}` CHARACTER SET utf8");
DataCore.NonQ($@"CREATE TABLE `{POut.String(databaseName)}`.`preference` (
`PrefName` varchar(255) NOT NULL DEFAULT '',
`ValueString` text NOT NULL,
`PrefNum` bigint(20) NOT NULL AUTO_INCREMENT,
`Comments` text,
PRIMARY KEY (`PrefNum`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8" );
}
}
///<summary>Gets all entries from the Signalod table.</summary>
public static List <Signalod> GetAllSignalods()
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
return(Meth.GetObject <List <Signalod> >(MethodBase.GetCurrentMethod()));
}
List <Signalod> listSignals = null;
try {
string command = "";
if (DataConnection.DBtype == DatabaseType.MySql)
{
command = "SELECT * FROM signalod";
listSignals = OpenDentBusiness.Crud.SignalodCrud.TableToList(DataCore.GetTable(command));
}
}
catch {
listSignals = new List <Signalod>();
}
return(listSignals);
}
public void Reports_GetTable_MySqlUserLow()
{
Exception ex = null;
//Spawn a new thread so that we don't manipulate any global DataConnection settings.
ODThread thread = new ODThread(o => {
//Prepare some simple queries to verify how both user and user low react.
string tempTableName = "tmpreportsunittesttable";
string tempTableDropQuery = $"DROP TABLE IF EXISTS {tempTableName}";
string tempTableCreateQuery = $"CREATE TABLE {tempTableName} (Col1 VARCHAR(1))";
string tempTableShowQuery = $"SHOW TABLES LIKE '{tempTableName}'";
//Make sure that we can create and drop tables as the normal user but cannot do the same thing via Reports.GetTable().
//First, make sure that the regular user works as intended.
DataCore.NonQ(tempTableDropQuery);
DataCore.NonQ(tempTableCreateQuery);
Assert.AreEqual(1, DataCore.GetTable(tempTableShowQuery).Rows.Count, "Root user was not able to create a new table.");
DataCore.NonQ(tempTableDropQuery);
//Next, make sure that user low cannot create the new table. Required to use the Middle Tier otherwise user low is ignored.
DataAction.RunMiddleTierMock(() => {
//User low should be able to run SELECT and SHOW commands.
if (Reports.GetTable(tempTableShowQuery).Rows.Count != 0) //Should have been dropped via root user above.
{
throw new ApplicationException("Temporary table was not dropped correctly.");
}
//Reports.GetTable() should throw an exception due to the lack of the CREATE permission. Swallow it.
ODException.SwallowAnyException(() => Reports.GetTable(tempTableCreateQuery));
//User low should not have been able to create the table.
if (Reports.GetTable(tempTableShowQuery).Rows.Count != 0)
{
throw new ApplicationException("User low was able to create a table.");
}
});
});
thread.AddExceptionHandler(e => { ex = e; }); //This will cause the unit test to fail.
thread.Name = "thread" + MethodBase.GetCurrentMethod().Name;
thread.Start();
thread.Join(Timeout.Infinite);
Assert.IsNull(ex, ex?.Message);
}
///<summary>Makes sure the tablename is valid. Goes through each column and makes sure that the column is present and that the type in the database is a supported type for this C# data type. Throws exception if it fails.</summary>
public static void ValidateTypes(Type typeClass, string dbName)
{
string tablename = GetTableName(typeClass);
string command = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = '" + dbName + "' AND table_name = '" + tablename + "'";
if (DataCore.GetScalar(command) != "1")
{
return; //can't validate
}
command = "SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS "
+ "WHERE table_name = '" + tablename + "' AND table_schema = '" + dbName + "'";
DataTable table = DataCore.GetTable(command);
FieldInfo[] fields = typeClass.GetFields();
for (int i = 0; i < fields.Length; i++)
{
if (IsNotDbColumn(fields[i]))
{
continue;
}
ValidateColumn(dbName, tablename, fields[i], table);
}
}
/// <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);
}