private void SetTextualFieldsWithNumericValues(ref DbTypeParametersCollection row)
{
DbTypeParametersCollection newRow = new DbTypeParametersCollection(row.TableName);
foreach (DbTypeParameter current in row)
{
if (current.Value is string)
{
newRow.Add(current.DbTypeName, "10");
}
}
row = newRow;
}
public void DoTestTypes(DbTypeParametersCollection row)
{
testTypesInvocations++;
exp = null;
string rowId = "43969_" + this.testTypesInvocations.ToString();
OracleDataReader rdr = null;
OracleConnection con = null;
try
{
row.ExecuteInsert(rowId);
row.ExecuteSelectReader(rowId, out rdr, out con);
while (rdr.Read())
{
//Run over all the columns in the result set row.
//For each column, try to read it as a double.
for (int i=0; i<row.Count; i++)
{
if (row[i].Value.GetType() == typeof(double) ||
row[i].Value.GetType() == typeof(decimal)) //The value in the result set should be a double.
{
try
{
BeginCase(string.Format("Calling GetDouble() on a field of dbtype {0}", row[i].DbTypeName));
double retDouble = rdr.GetDouble(i);
Compare(row[i].Value, retDouble);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
}
else //The value in the result set should NOT be double. In this case an Invalid case exception should be thrown.
{
try
{
BeginCase(string.Format("Calling GetDouble() on a field of dbtype {0}", row[i].DbTypeName));
double retDouble = rdr.GetDouble(i);
ExpectedExceptionNotCaught("InvalidCastException");
}
catch (InvalidCastException ex)
{
ExpectedExceptionCaught(ex);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
}
}
}
}
finally
{
row.ExecuteDelete(rowId);
if ( (rdr != null) && (!rdr.IsClosed) )
{
rdr.Close();
}
if ( (con != null) && (con.State != ConnectionState.Closed) )
{
con.Close();
}
}
}
public void DoTestTypes(DbTypeParametersCollection row)
{
testTypesInvocations++;
exp = null;
string rowId = "43966_" + this.testTypesInvocations.ToString();
OleDbConnection con =null;
OleDbDataReader rdr = null;
try
{
row.ExecuteInsert(rowId);
row.ExecuteSelectReader(rowId, out rdr, out con);
while (rdr.Read())
{
//Run over all the columns in the result set row.
//For each column, try to read it as a byte array.
for (int i=0; i<row.Count; i++)
{
if (row[i].Value.GetType() == typeof(byte[])) //The value in the result set should be a byte array.
{
try
{
BeginCase(string.Format("Calling GetBytes() on a field of dbtype {0}", row[i].DbTypeName));
byte[] origBytes = (byte[])row[i].Value;
byte[] retBytes = new byte[origBytes.Length];
rdr.GetBytes(i, 0, retBytes, 0, origBytes.Length);
Compare(origBytes, retBytes);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
}
else //The value in the result set should NOT be byte array. In this case an Invalid case exception should be thrown.
{
try
{
BeginCase(string.Format("Calling GetBytes() on a field of dbtype {0}", row[i].DbTypeName));
byte[] retBytes = new byte[1];
rdr.GetBytes(i, 0, retBytes, 0, 1);
ExpectedExceptionNotCaught("InvalidCastException");
}
catch (InvalidCastException ex)
{
ExpectedExceptionCaught(ex);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
}
}
}
}
finally
{
row.ExecuteDelete(rowId);
if (rdr != null && !rdr.IsClosed)
{
rdr.Close();
}
if (con != null && con.State != ConnectionState.Closed)
{
con.Close();
}
}
}
public void DoTestTextualFieldsThatContainNumbers(DbTypeParametersCollection row)
{
//Leave only textual fields in the collection, and set their value to the string "10"
SetTextualFieldsWithNumericValues(ref row);
if (row.Count < 1)
{
return;
}
testTypesInvocations++;
exp = null;
string rowId = "43968_" + this.testTypesInvocations.ToString();
OracleDataReader rdr = null;
OracleConnection con = null;
try
{
row.ExecuteInsert(rowId);
row.ExecuteSelectReader(rowId, out rdr, out con);
while (rdr.Read())
{
//Run over all the columns in the result set row.
//For each column, try to read it as a Decimal.
//Because all the fields are textual, this should throw an InvalidCastException.
for (int i=0; i<row.Count; i++)
{
try
{
BeginCase(string.Format("Calling GetDecimal() on a textual field of dbtype {0} with value '{1}'", row[i].DbTypeName, row[i].Value));
decimal retDecimal = rdr.GetDecimal(i);
ExpectedExceptionNotCaught(typeof(InvalidCastException).FullName);
}
catch (InvalidCastException ex)
{
ExpectedExceptionCaught(ex);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
}
}
}
finally
{
row.ExecuteDelete(rowId);
if ( (rdr != null) && (!rdr.IsClosed) )
{
rdr.Close();
}
if ( (con != null) && (con.State != ConnectionState.Closed) )
{
con.Close();
}
}
}
public void DoTestGUIDOnMSSQLServer()
{
if (ConnectedDataProvider.GetDbType() != DataBaseServer.SQLServer)
return;
DbTypeParametersCollection guidRow = new DbTypeParametersCollection(ConnectedDataProvider.SPECIFIC_TYPES_TABLE_NAME);
guidRow.Add("UNIQUEIDENTIFIER", new Guid(TEST_GUID_STRING));
TypesSubTests(guidRow);
}
public void TestUsingParametersArray()
{
//Only apply to MSSQL
if ( (ConnectedDataProvider.GetDbType() != DataBaseServer.SQLServer))
{
return;
}
Exception exp = null;
OracleDataReader rdr = null;
OracleConnection con = null;
DbTypeParametersCollection row = new DbTypeParametersCollection(GUID_TABLE_NAME);
string rowId = string.Empty;
try
{
BeginCase("Test using parameters array");
rowId = "43973_" + TestCaseNumber.ToString();
row.Add("UNIQUEIDENTIFIER", new Guid(TEST_GUID_STRING));
row.ExecuteInsert(rowId);
row.ExecuteSelectReader(rowId, out rdr, out con);
rdr.Read();
Guid guidValue = rdr.GetGuid (0);
Compare(guidValue, row[GUID_COLUMN_NAME].Value);
}
catch(Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
if ( (rdr != null) && (!rdr.IsClosed) )
{
rdr.Close();
}
if (rowId != String.Empty)
{
row.ExecuteDelete(rowId);
}
if ( (con != null) && (con.State != ConnectionState.Closed) )
{
con.Close();
}
}
}
private void TypesSubTests(DbTypeParametersCollection typesToTest)
{
DbTypeParametersCollection currentlyTested = new DbTypeParametersCollection(typesToTest.TableName);
int affectedRows;
string rowId = string.Empty;
foreach (DbTypeParameter currentParamType in typesToTest)
{
try
{
BeginCase("Test INSERT with parameter of type: " + currentParamType.DbTypeName);
rowId = string.Format("13282_{0}", this.TestCaseNumber);
currentlyTested.Clear();
currentlyTested.Add(currentParamType);
affectedRows = currentlyTested.ExecuteInsert(rowId);
Compare(affectedRows, 1);
}
catch(Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
currentlyTested.ExecuteDelete(rowId);
}
}
}
/// <summary>
/// Creates a DbTypeParametersCollection with default types and data for the TYPES_EXTENDED table.
/// </summary>
/// <returns>The initialized DbTypeParametersCollection</returns>
public static DbTypeParametersCollection GetExtendedDbTypesParameters() {
DbTypeParametersCollection row = new DbTypeParametersCollection(EXTENDED_TYPES_TABLE_NAME);
switch (ConnectedDataProvider.GetDbType(ConnectedDataProvider.ConnectionString)) {
#region SQLServer
case MonoTests.System.Data.Utils.DataBaseServer.SQLServer:
row.Add("text", SAMPLE_STRING, 16);
row.Add("ntext", SAMPLE_STRING, 16);
row.Add("binary", new byte[] {0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0,
0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0,
0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0,
0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0,
0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0}, 50);
row.Add("varbinary", new byte[] {0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0,
0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0,
0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0,
0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0,
0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0}, 50);
row.Add("datetime", new DateTime(2004, 8, 9, 20, 30, 15, 500), 8);
row.Add("smalldatetime", new DateTime(2004, 8, 9, 20, 30, 00), 4);
break;
#endregion
#region Sybase
case MonoTests.System.Data.Utils.DataBaseServer.Sybase:
row.Add("TEXT", SAMPLE_STRING, 16);
//There is probably a bug in the jdbc driver , we've tried to insert this string using
//sybase command tool and it gave the same result (3850)
row.Add("NTEXT", SAMPLE_STRING.Trim() , 16);
row.Add("BINARY", new byte[] {0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0,
0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0,
0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0,
0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0,
0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0}, 50);
row.Add("VARBINARY", new byte[] {0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0,
0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0,
0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0,
0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0,
0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0}, 50);
row.Add("DATETIME", new DateTime(2004, 8, 9, 20, 30, 15, 500), 8);
row.Add("SMALLDATETIME", new DateTime(2004, 8, 9, 20, 30, 00), 4);
break;
#endregion
#region ORACLE
case MonoTests.System.Data.Utils.DataBaseServer.Oracle:
row.Add("RAW", new byte[] {0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0}, 10);
row.Add("LONGRAW", new byte[] {0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0
,0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0
,0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0
,0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0
,0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0
,0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0
,0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0
,0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0
,0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0
,0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0
}, 100);
row.Add("DATE", new DateTime(2004, 8, 9, 20, 30, 15), 7);
// The .NET Framework provides support for Oracle LOBs in the OracleClient namespace, but not in the Oracle namespace.
// Since Visual MainWin does not support the OracleClient namespace, a partial support for this important feature is provided in the Oracle namespace.
// See ms-help://MS.VSCC.2003/VMW.GH.1033/ghdoc/vmwdoc_ADONET_data_access_limitations_51.htm
break;
#endregion
#region DB2
case MonoTests.System.Data.Utils.DataBaseServer.DB2:
row.Add("DATE", new DateTime(2004, 8, 9, 20, 30, 15, 500).Date);
row.Add("TIME", new TimeSpan(20, 30, 15));
row.Add("TIMESTAMP", new DateTime(2004, 8, 9, 20, 30, 15, 500));
row.Add("BLOB", new byte[] {0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0
,0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0
,0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0
,0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0
,0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0
,0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0
,0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0
,0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0
,0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0
,0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0
});
row.Add("CLOB", SAMPLE_STRING
+ SAMPLE_STRING
+ SAMPLE_STRING
+ SAMPLE_STRING
+ SAMPLE_STRING
+ SAMPLE_STRING
);
row.Add("DBCLOB", SAMPLE_STRING
+ SAMPLE_STRING
+ SAMPLE_STRING
+ SAMPLE_STRING
+ SAMPLE_STRING
+ SAMPLE_STRING
);
break;
#endregion
#region PostgreSQL
case MonoTests.System.Data.Utils.DataBaseServer.PostgreSQL:
row.Add("BYTEA", new byte[] {0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0,
0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0,
0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0,
0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0,
0x00, 0x12, 0x34, 0x56, 0x78, 0x9A, 0xBC, 0xDE, 0xFF, 0xF0}, 50);
row.Add("DATE", new DateTime(2004, 8, 9));
row.Add("TEXT", "abcdefg", 16);
row.Add("TIME", new Sys.TimeSpan(02,02,02));
row.Add("TIMESTAMP", new DateTime(2004, 8, 9, 20, 30, 15, 500), 8);
break;
#endregion
}
return row;
}
/// <summary>
/// Creates a DbTypeParametersCollection with default types and data for the TYPES_SIMPLE table.
/// </summary>
/// <returns>The initialized DbTypeParametersCollection</returns>
public static DbTypeParametersCollection GetSimpleDbTypesParameters() {
DbTypeParametersCollection row = new DbTypeParametersCollection(SIMPLE_TYPES_TABLE_NAME);
switch (ConnectedDataProvider.GetDbType(ConnectedDataProvider.ConnectionString)) {
#region SQLServer
case MonoTests.System.Data.Utils.DataBaseServer.SQLServer:
row.Add("bit", true, 1);
row.Add("tinyint", (byte)25, 1);
row.Add("smallint", (Int16)77, 2);
row.Add("int", (Int32)2525, 4);
row.Add("bigint", (Int64)25251414, 8);
row.Add("decimal", 10M, 9); //(Decimal)10
row.Add("numeric", 123123M, 9); //(Decimal)123123
row.Add("float", 17.1414257, 8);
row.Add("real", (float)0.71425, 4);
row.Add("char", "abcdefghij", 10);
row.Add("nchar", "klmnopqrst", 10);
row.Add("varchar", "qwertasdfg", 50);
row.Add("nvarchar", "qwertasdfg", 50);
break;
#endregion
#region Sybase
case MonoTests.System.Data.Utils.DataBaseServer.Sybase:
//row.Add("BIT", true, 1);
row.Add("TINYINT", (byte)25, 1);
row.Add("SMALLINT", (Int16)77, 2);
row.Add("INT", (Int32)2525, 4);
//row.Add("BIGINT", (Int64)25251414, 8);
row.Add("DECIMAL", 10M, 9); //(Decimal)10
row.Add("NUMERIC", 123123M, 9); //(Decimal)123123
row.Add("FLOAT", 17.1414257, 8);
row.Add("REAL", (float)0.71425, 4);
row.Add("CHAR", "abcdefghij", 10);
row.Add("NCHAR", "klmnopqrst", 10);
row.Add("VARCHAR", "qwertasdfg", 50);
row.Add("NVARCHAR", "qwertasdfg", 50);
break;
#endregion
#region ORACLE
case MonoTests.System.Data.Utils.DataBaseServer.Oracle:
row.Add("NUMBER", 21M, 22); //(Decimal)21
row.Add("LONG", SAMPLE_STRING, 2147483647); //Default data type in .NET is system.String.
row.Add("FLOAT", 1.234, 22);
row.Add("VARCHAR", "qwertasdfg", 10);
row.Add("NVARCHAR", "qwertasdfg", 20);
row.Add("CHAR", "abcdefghij", 10);
row.Add("NCHAR", "abcdefghij", 10);
break;
#endregion
#region DB2
case MonoTests.System.Data.Utils.DataBaseServer.DB2:
row.Add("SMALLINT", (Int16)2, 2);
row.Add("INTEGER", 7777, 4);
row.Add("BIGINT", (Int64)21767267, 8);
row.Add("DECIMAL", 123M, 9); //(decimal)123
row.Add("REAL", (float)0.7, 4);
row.Add("DOUBLE", 1.7, 8);
row.Add("CHARACTER", "abcdefghij", 10);
row.Add("VARCHAR", "qwertasdfg", 10);
row.Add("LONGVARCHAR", SAMPLE_STRING, 32000);
break;
#endregion
#region PostgreSQL
case MonoTests.System.Data.Utils.DataBaseServer.PostgreSQL:
// PostgreSQL ODBC Type BOOL returns String with value "1"
// so we don't run it on .NET
// if (!GHTEnvironment.IsJavaRunTime())
// {
// row.Add("BOOL", "1", 1);
// }
// else
// {
row.Add("BOOL", (bool)true, 1);
// }
row.Add("INT2", (Int16)21, 2);
row.Add("INT4", (Int32)30000, 4);
row.Add("INT8", (Int64)30001, 8);
row.Add("NUMERIC", (decimal)100000M, 10); //(decimal)100000
row.Add("FLOAT4", (Single)7.23157, 4);
row.Add("FLOAT8", (Double)7.123456, 8);
row.Add("VARCHAR", "qwertasdfg", 10);
row.Add("CHAR", "abcdefghij", 10);
row.Add("NCHAR", "klmnopqrst", 10);
break;
#endregion
}
return row;
}
private void TypesTests(DbTypeParametersCollection typesToTest)
{
exp = null;
DbTypeParametersCollection currentlyTested = new DbTypeParametersCollection(typesToTest.TableName);
string rowId = "13289";
object dbValue;
OleDbDataReader rdr = null;
OleDbConnection selectCon = null;
DataBaseServer testedDbServer = ConnectedDataProvider.GetDbType();
foreach (DbTypeParameter currentParamType in typesToTest)
{
BeginCase("Test value of db type: " + currentParamType.DbTypeName);
//Prepare data:
rowId = string.Format("13289_{0}", this.TestCaseNumber);
currentlyTested.Clear();
currentlyTested.Add(currentParamType);
currentlyTested.ExecuteInsert(rowId);
try
{
currentlyTested.ExecuteSelectReader(rowId, out rdr, out selectCon);
rdr.Read();
dbValue = WorkaroundOracleCharsPaddingLimitation(testedDbServer, currentParamType, rdr.GetValue(0));
if (currentParamType.Value.GetType().IsArray)
{
Compare(dbValue as Array, currentParamType.Value as Array);
}
else
{
Compare(dbValue, currentParamType.Value);
}
}
catch(Exception ex)
{
exp = ex;
}
finally
{
if (rdr != null && !rdr.IsClosed)
{
rdr.Close();
}
if (selectCon != null && selectCon.State != ConnectionState.Closed)
{
selectCon.Close();
}
currentlyTested.ExecuteDelete(rowId);
EndCase(exp);
exp = null;
}
}
}
/// <summary>
/// Builds and executes an DELETE command according to the UniqueId parameter, and the TableName property.
/// </summary>
/// <param name="a_sUniqueId">The criteria for deleting.</param>
/// <returns>The number of deleted rows.</returns>
public virtual int ExecuteDelete(string a_sUniqueId)
{
return(DbTypeParametersCollection.ExecuteDelete(this.TableName, a_sUniqueId));
}
