//[Test]
public void DoTestTypes2(OracleConnection conn)
{
BeginCase("Select by full table name in the different catalog");
nonUniqueId = "48951_" + TestCaseNumber.ToString();
Exception exp = null;
string tableName = getDbObjectName("Customers", conn, "GHTDB_EX");
int expectedRowsCount = 5;
DataSet ds = new DataSet();
OracleCommand comm = new OracleCommand("", conn);
OracleDataAdapter da = new OracleDataAdapter();
da.SelectCommand = comm;
insertIntoStandatTable(conn, tableName, expectedRowsCount, "CustomerID");
comm.CommandText = "SELECT * FROM " + tableName + " where CustomerID='" + nonUniqueId + "'";
ds.Tables.Clear();
da.Fill(ds);
try
{
Compare(ds.Tables[0].Rows.Count, expectedRowsCount);
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null;
cleanStandatTable(conn, tableName, "CustomerID"); }
}
public void TestUsingSQLTextOnly()
{
//Only apply to MSSQL
if ((ConnectedDataProvider.GetDbType() != DataBaseServer.SQLServer))
{
return;
}
Exception exp = null;
OracleDataReader rdr = null;
OracleConnection con = null;
OracleCommand cmdDelete = null;
try
{
BeginCase("Test using SQL text only.");
string rowId = "43973_" + TestCaseNumber.ToString();
string insertText = string.Format("INSERT INTO {0} (ID, {1}) VALUES ('{2}', '{{{3}}}')", GUID_TABLE_NAME, GUID_COLUMN_NAME, rowId, TEST_GUID_STRING);
string selectText = string.Format("SELECT {0} FROM {1} WHERE ID='{2}'", GUID_COLUMN_NAME, GUID_TABLE_NAME, rowId);
string deleteText = string.Format("DELETE FROM {0} WHERE ID='{1}'", GUID_TABLE_NAME, rowId);
con = new OracleConnection(ConnectedDataProvider.ConnectionString);
OracleCommand cmdInsert = new OracleCommand(insertText, con);
OracleCommand cmdSelect = new OracleCommand(selectText, con);
cmdDelete = new OracleCommand(deleteText, con);
con.Open();
cmdInsert.ExecuteNonQuery();
rdr = cmdSelect.ExecuteReader();
rdr.Read();
Guid guidValue = rdr.GetGuid(0);
Guid origGuid = new Guid(TEST_GUID_STRING);
Compare(guidValue, origGuid);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
if ((rdr != null) && (!rdr.IsClosed))
{
rdr.Close();
}
if (cmdDelete != null)
{
cmdDelete.ExecuteNonQuery();
}
if ((con != null) && (con.State != ConnectionState.Closed))
{
con.Close();
}
}
}
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 ValueInColumn(string columnToTest, string valueToTest)
{
exp = null;
OracleDataReader rdr = null;
OracleConnection con = null;
DbTypeParametersCollection row = ConnectedDataProvider.GetSimpleDbTypesParameters();
BeginCase(string.Format("Use {0} as value", valueToTest));
string rowId = TEST_CASE_ID + TestCaseNumber.ToString();
try
{
foreach (DbTypeParameter param in row)
{
param.Value = DBNull.Value;
}
row[columnToTest].Value = valueToTest;
Log("rowId:" + rowId + " columnToTest:" + columnToTest + " valueToTest:" + valueToTest);
row.ExecuteInsert(rowId);
row.ExecuteSelectReader(rowId, out rdr, out con);
rdr.Read();
int columnOrdinal = rdr.GetOrdinal(columnToTest);
//this.Log(valueToTest);
Compare(valueToTest, rdr.GetValue(columnOrdinal));
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
if (rdr != null && !rdr.IsClosed)
{
rdr.Close();
}
row.ExecuteDelete(rowId);
if (con != null && con.State != ConnectionState.Closed)
{
con.Close();
}
}
}
//[Test]
public void DoTestTypes3(OracleConnection conn)
{
BeginCase("Call stored procedure in the different catalog");
nonUniqueId = "48951_" + TestCaseNumber.ToString();
Exception exp = null;
DataSet ds = new DataSet();
OracleCommand comm = new OracleCommand("", conn);
OracleDataAdapter da = new OracleDataAdapter();
da.SelectCommand = comm;
string tableName = getDbObjectName("Customers", conn, "GHTDB_EX");
int expectedRowsCount = 5;
insertIntoStandatTable(conn, tableName, expectedRowsCount, "CustomerID");
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = getDbObjectName("GH_DUMMY", conn, "GHTDB_EX");
comm.Parameters.Add(new OracleParameter("CustomerIDPrm", OracleType.Char));
comm.Parameters.Add(new OracleParameter("result", OracleType.Cursor)).Direction = ParameterDirection.Output;
comm.Parameters[0].Value = nonUniqueId;
ds.Tables.Clear();
try
{
da.Fill(ds);
Compare(ds.Tables[0].Rows.Count, expectedRowsCount);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
cleanStandatTable(conn, tableName, "CustomerID");
}
}
//[Test]
public void DoTestTypes3(OleDbConnection conn)
{
BeginCase("Call stored procedure in the different catalog");
nonUniqueId = "48951_" + TestCaseNumber.ToString();
Exception exp = null;
DataSet ds = new DataSet();
OleDbCommand comm = new OleDbCommand("", conn);
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = comm;
string tableName = getDbObjectName("Customers", conn, "GHTDB_EX");
int expectedRowsCount = 5;
insertIntoStandatTable(conn, tableName, expectedRowsCount, "CustomerID");
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = getDbObjectName("GH_DUMMY", conn, "GHTDB_EX");
switch (ConnectedDataProvider.GetDbType(conn))
{
case DataBaseServer.SQLServer:
case DataBaseServer.Sybase:
comm.Parameters.Add("@CustomerID", nonUniqueId);
break;
default:
comm.Parameters.Add(new OleDbParameter("CustomerIDPrm", OleDbType.Char));
break;
}
comm.Parameters[0].Value = nonUniqueId;
ds.Tables.Clear();
try
{
da.Fill(ds);
Compare(ds.Tables[0].Rows.Count, expectedRowsCount);
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null;
cleanStandatTable(conn, tableName, "CustomerID"); }
}
public void run()
{
Exception exp = null;
OracleCommand cmd = new OracleCommand();
string rowId = "54416_";
try
{
// clean the test table
cmd = new OracleCommand(string.Format("DELETE FROM {0} WHERE ID like '54416_%'", dateTableName));
cmd.Connection = con;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
exp = ex;
}
#region ---- testing parameterized query with a simple date ----
try
{
BeginCase("testing parameterized query with a simple date");
rowId = "54416_" + TestCaseNumber.ToString();
cmd = new OracleCommand(string.Format("insert into {0} (ID, {1}) values ('{2}', :date1)", dateTableName, dateColumnName, rowId));
cmd.Connection = con;
TestedDate1 = new DateTime(2001, 1, 13);
cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
cmd.ExecuteNonQuery();
// checking that value returned correctly;
cmd.CommandText = string.Format("select {0} from {1} where ID='{2}'", dateColumnName, dateTableName, rowId);
cmd.Parameters.Clear();
RetDate = Convert.ToDateTime(cmd.ExecuteScalar());
Compare(TestedDate1, RetDate);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
if (dr != null)
{
dr.Close();
}
if ((con != null) && (con.State == ConnectionState.Open))
{
CleanTestRow(rowId);
con.Close();
}
EndCase(exp);
exp = null;
}
#endregion
#region ---- testing parameterized query with a 1753 date min ----
try
{
BeginCase("testing parameterized query with a 1753 date min");
rowId = "54416_" + TestCaseNumber.ToString();
con.Open();
cmd = new OracleCommand(string.Format("insert into {0} (ID, {1}) values('{2}', :date1)", dateTableName, dateColumnName, rowId));
cmd.Connection = con;
TestedDate1 = new DateTime(1753, 1, 1);
cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
this.Log(cmd.CommandText);
cmd.ExecuteNonQuery();
//' checking that value returned correctly
cmd.CommandText = string.Format("select {0} from {1} where ID='{2}'", dateColumnName, dateTableName, rowId);
this.Log(cmd.CommandText);
cmd.Parameters.Clear();
RetDate = Convert.ToDateTime(cmd.ExecuteScalar());
Compare(TestedDate1.Date, RetDate.Date);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
if (dr != null)
{
dr.Close();
}
if ((con != null) && (con.State == ConnectionState.Open))
{
CleanTestRow(rowId);
con.Close();
}
EndCase(exp);
exp = null;
}
#endregion
#region ---- testing parameterized query with a future date ----
try
{
BeginCase("testing parameterized query with a future date");
rowId = "54416_" + TestCaseNumber.ToString();
con.Open();
cmd = new OracleCommand(string.Format("insert into {0} (ID, {1}) values('{2}', :date1)", dateTableName, dateColumnName, rowId));
cmd.Connection = con;
TestedDate1 = new DateTime(2500, 1, 13);
cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
cmd.ExecuteNonQuery();
//' checking that value returned correctly
cmd.CommandText = string.Format("select {0} from {1} where ID='{2}'", dateColumnName, dateTableName, rowId);
cmd.Parameters.Clear();
RetDate = Convert.ToDateTime(cmd.ExecuteScalar());
Compare(TestedDate1, RetDate);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
if (dr != null)
{
dr.Close();
}
if ((con != null) && (con.State == ConnectionState.Open))
{
CleanTestRow(rowId);
con.Close();
}
EndCase(exp);
exp = null;
}
#endregion
#region ---- testing parameterized query with a future date ----
try
{
BeginCase("testing parameterized query with a future date");
rowId = "54416_" + TestCaseNumber.ToString();
con.Open();
cmd = new OracleCommand(string.Format("insert into {0} (ID, {1}) values('{2}', :date1)", dateTableName, dateColumnName, rowId));
cmd.Connection = con;
TestedDate1 = new DateTime(2500, 1, 13);
cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
cmd.ExecuteNonQuery();
//' checking that value returned correctly
cmd.CommandText = string.Format("select {0} from {1} where ID='{2}'", dateColumnName, dateTableName, rowId);
cmd.Parameters.Clear();
RetDate = Convert.ToDateTime(cmd.ExecuteScalar());
Compare(TestedDate1, RetDate);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
if (dr != null)
{
dr.Close();
}
if ((con != null) && (con.State == ConnectionState.Open))
{
CleanTestRow(rowId);
con.Close();
}
EndCase(exp);
exp = null;
}
#endregion
#region ---- testing parameterized query with a time part ----
try
{
BeginCase("testing parameterized query with a time part");
rowId = "54416_" + TestCaseNumber.ToString();
con.Open();
cmd = new OracleCommand(string.Format("insert into {0} (ID, {1}) values('{2}', :date1)", dateTableName, dateColumnName, rowId));
cmd.Connection = con;
TestedDate1 = new DateTime(2500, 1, 13, 12, 13, 14);
cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
cmd.ExecuteNonQuery();
//' checking that value returned correctly
cmd.CommandText = string.Format("select {0} from {1} where ID='{2}'", dateColumnName, dateTableName, rowId);
cmd.Parameters.Clear();
RetDate = Convert.ToDateTime(cmd.ExecuteScalar());
Compare(TestedDate1, RetDate);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
if (dr != null)
{
dr.Close();
}
if ((con != null) && (con.State == ConnectionState.Open))
{
CleanTestRow(rowId);
con.Close();
}
EndCase(exp);
exp = null;
}
#endregion
#region ---- testing parameterized query with a time part of 00:00 ----
try
{
BeginCase("testing parameterized query with a time part of 00:00");
rowId = "54416_" + TestCaseNumber.ToString();
con.Open();
cmd = new OracleCommand(string.Format("insert into {0} (ID, {1}) values('{2}', :date1)", dateTableName, dateColumnName, rowId));
cmd.Connection = con;
TestedDate1 = new DateTime(2500, 1, 13, 0, 0, 0);
cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
cmd.ExecuteNonQuery();
//' checking that value returned correctly
cmd.CommandText = string.Format("select {0} from {1} where ID='{2}'", dateColumnName, dateTableName, rowId);
cmd.Parameters.Clear();
RetDate = Convert.ToDateTime(cmd.ExecuteScalar());
Compare(TestedDate1, RetDate);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
if (dr != null)
{
dr.Close();
}
if ((con != null) && (con.State == ConnectionState.Open))
{
CleanTestRow(rowId);
con.Close();
}
EndCase(exp);
exp = null;
}
#endregion
#region ---- testing parameterized query with a time part of 23:59:59 ----
try
{
BeginCase("testing parameterized query with a time part of 23:59:59");
rowId = "54416_" + TestCaseNumber.ToString();
con.Open();
cmd = new OracleCommand(string.Format("insert into {0} (ID, {1}) values('{2}', :date1)", dateTableName, dateColumnName, rowId));
cmd.Connection = con;
TestedDate1 = new DateTime(2500, 1, 13, 23, 59, 59);
cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
cmd.ExecuteNonQuery();
//' checking that value returned correctly
cmd.CommandText = string.Format("select {0} from {1} where ID='{2}'", dateColumnName, dateTableName, rowId);
cmd.Parameters.Clear();
RetDate = Convert.ToDateTime(cmd.ExecuteScalar());
Compare(TestedDate1, RetDate);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
if (dr != null)
{
dr.Close();
}
if ((con != null) && (con.State == ConnectionState.Open))
{
CleanTestRow(rowId);
con.Close();
}
EndCase(exp);
exp = null;
}
#endregion
#region ---- testing parameterized query with a time part of AM ----
try
{
BeginCase("testing parameterized query with a time part of AM");
string str = string.Empty; //This is an addional test ,passing GH mechnizim
rowId = "54416_" + TestCaseNumber.ToString();
con.Open();
cmd = new OracleCommand(string.Format("insert into {0} (ID, {1}) values('{2}', :date1)", dateTableName, dateColumnName, rowId));
cmd.Connection = con;
//TestedDate1 = new DateTime(2500, 1, 13, 11, 0, 0);
TestedDate1 = new DateTime(1988, 5, 31, 15, 33, 44, 00);
cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
//TODO:add also treat for other db
if (ConnectedDataProvider.GetDbType(con) == MonoTests.System.Data.Utils.DataBaseServer.SQLServer || ConnectedDataProvider.GetDbType(con) == MonoTests.System.Data.Utils.DataBaseServer.Sybase)
{
cmd.CommandText = string.Format("select CONVERT(varchar,{0},120) from {1} where ID='{2}'", dateColumnName, dateTableName, rowId);
str = cmd.ExecuteScalar().ToString();
Compare(TestedDate1, Convert.ToDateTime(str));
}
//' checking that value returned correctly
cmd.CommandText = string.Format("select {0} from {1} where ID='{2}'", dateColumnName, dateTableName, rowId);
RetDate = Convert.ToDateTime(cmd.ExecuteScalar().ToString());
Compare(TestedDate1, RetDate);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
if (dr != null)
{
dr.Close();
}
if ((con != null) && (con.State == ConnectionState.Open))
{
CleanTestRow(rowId);
con.Close();
}
EndCase(exp);
exp = null;
}
#endregion
#region ---- testing a where clause using a date ----
try
{
BeginCase("testing a where clause using a date");
rowId = "54416_" + TestCaseNumber.ToString();
con.Open();
cmd = new OracleCommand(string.Format("insert into {0} (ID, {1}) values('{2}', :date1)", dateTableName, dateColumnName, rowId));
cmd.Connection = con;
TestedDate1 = new DateTime(2500, 1, 13, 11, 0, 0);
cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
cmd.ExecuteNonQuery();
//' checking that value returned correctly
cmd = new OracleCommand(string.Format("select {0} from {1} where ID='{2}' and {0}= :date1", dateColumnName, dateTableName, rowId));
cmd.Connection = con;
cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
RetDate = Convert.ToDateTime(cmd.ExecuteScalar());
Compare(TestedDate1, RetDate);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
if (dr != null)
{
dr.Close();
}
if ((con != null) && (con.State == ConnectionState.Open))
{
CleanTestRow(rowId);
con.Close();
}
EndCase(exp);
exp = null;
}
#endregion
#region ---- testing a where clause using a rage of dates ----
try
{
BeginCase("testing a where clause using a rage of dates");
rowId = "54416_" + TestCaseNumber.ToString();
con.Open();
cmd = new OracleCommand(string.Format("insert into {0} (ID, {1}) values('{2}', :date1)", dateTableName, dateColumnName, rowId));
cmd.Connection = con;
TestedDate1 = new DateTime(2007, 12, 31, 11, 59, 59);
cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
cmd.ExecuteNonQuery();
//' checking that value returned correctly
cmd = new OracleCommand(string.Format("select {0} from {1} where ID='{2}' and {0} >:date1 and {0} <:date2", dateColumnName, dateTableName, rowId));
cmd.Connection = con;
cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1.AddSeconds(-1);
cmd.Parameters.Add(new OracleParameter("date2", OracleType.DateTime)).Value = TestedDate1.AddSeconds(+1);
RetDate = Convert.ToDateTime(cmd.ExecuteScalar());
Compare(TestedDate1, RetDate);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
if (dr != null)
{
dr.Close();
}
if ((con != null) && (con.State == ConnectionState.Open))
{
CleanTestRow(rowId);
con.Close();
}
EndCase(exp);
exp = null;
}
#endregion
#region ---- testing a set statement ----
try
{
BeginCase("testing a set statement");
rowId = "54416_" + TestCaseNumber.ToString();
con.Open();
cmd = new OracleCommand(string.Format("insert into {0} (ID) values('{1}')", dateTableName, rowId));
cmd.Connection = con;
cmd.ExecuteNonQuery();
cmd = new OracleCommand(string.Format("update {0} set {1} = :date1 where ID='{2}'", dateTableName, dateColumnName, rowId));
cmd.Connection = con;
TestedDate1 = new DateTime(2500, 1, 13, 1, 2, 3);
cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
cmd.ExecuteNonQuery();
//' checking that value returned correctly
cmd = new OracleCommand(string.Format("select {0} from {1} where ID='{2}' and {0}= :date1", dateColumnName, dateTableName, rowId));
cmd.Connection = con;
cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
RetDate = Convert.ToDateTime(cmd.ExecuteScalar());
Compare(TestedDate1, RetDate);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
if (dr != null)
{
dr.Close();
}
if ((con != null) && (con.State == ConnectionState.Open))
{
CleanTestRow(rowId);
con.Close();
}
EndCase(exp);
exp = null;
}
#endregion
}
