public void TestMultipleResultsets()
{
#if !JAVA
if (ConnectedDataProvider.GetDbType(con) == DataBaseServer.Oracle)
{
//In .NET there is a bug when calling a SP with multiple REFCURSORS, the workaround is to use OracleClient and not Oracle.
//In GH we are not bug complient in this issue, because there is no workaround (We do not support the OracleClient namespace.
this.Log("Not testing multi result set Oracle on .NET");
return;
}
if (ConnectedDataProvider.GetDbType(con) == DataBaseServer.PostgreSQL)
{
// fail to work on .NET OLEDB
//reader = Microsoft.ApplicationBlocks.Data.PostgresOracleHelper.ADOExecuteReader(cmd1);
this.Log("Not testing PostgreSQL CommandType.StoredProcedure which return SETOF");
return;
}
#endif
Exception exp = null;
BeginCase("Test multi result set from stored procedure");
OracleDataReader reader = null;
OracleTransaction tr = null;
try
{
//Check SP with the structre : insert Select + update Select + delete Select
if (con.State != ConnectionState.Open)
{
con.Open();
}
// transaction use was add for PostgreSQL
tr = con.BeginTransaction();
OracleCommand cmd1 = new OracleCommand("GHSP_TYPES_SIMPLE_4", con, tr);
cmd1.CommandType = CommandType.StoredProcedure;
OracleParameter param = new OracleParameter();
param.ParameterName = "ID1";
param.Value = string.Format("13268_{0}", this.TestCaseNumber);
param.OracleType = OracleType.VarChar;
cmd1.Parameters.Add(param);
cmd1.Parameters.Add(new OracleParameter("RESULT", OracleType.Cursor)).Direction = ParameterDirection.Output;
cmd1.Parameters.Add(new OracleParameter("RESULT1", OracleType.Cursor)).Direction = ParameterDirection.Output;
cmd1.Parameters.Add(new OracleParameter("RESULT2", OracleType.Cursor)).Direction = ParameterDirection.Output;
reader = cmd1.ExecuteReader();
//Count the number of result sets.
int resultSetCount = 0;
//Count the number of the records
int recordCounter = 0;
do
{
//this.Log(string.Format("resultSetCount:{0}",resultSetCount));
while (reader.Read())
{
recordCounter++;
}
//this.Log(string.Format("recordCounter:{0}",recordCounter));
if (resultSetCount != 2)
{
Compare(recordCounter, 1); //Insert + update
}
else
{
Compare(recordCounter, 0); //Delete
}
recordCounter = 0;
resultSetCount++;
}while (reader.NextResult());
Compare(resultSetCount, 3);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
if (reader != null)
{
reader.Close();
}
tr.Commit();
con.Close();
}
}
public void run()
{
if (ConnectedDataProvider.GetDbType() != DataBaseServer.SQLServer)
{
//All tests in this class are only for MSSQLServer.
Log(string.Format("All tests in this class are only for MSSQLServer and cannot be tested on {0}", ConnectedDataProvider.GetDbType()));
return;
}
Exception exp = null;
#region ---- Bug 2716 - MSSQL - SqlCommand.Transaction ----
// testing only SQLServerr
if (ConnectedDataProvider.GetDbType(con.ConnectionString) != DataBaseServer.SQLServer)
{
try
{
BeginCase("Bug 2716 - MSSQL - SqlCommand.Transaction");
SqlCommand comm = new SqlCommand("SELECT * FROM Customers", con);
SqlTransaction trans = con.BeginTransaction("transaction");
comm.Transaction = trans;
con.Close();
Compare(con.State, ConnectionState.Closed);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
if (con != null)
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
EndCase(exp);
exp = null;
}
}
#endregion
}
public void RetrieveParameters()
{
exp = null;
try
{
BeginCase("retrieve parameters");
if (ConnectedDataProvider.GetDbType(con) == DataBaseServer.DB2)
{
this.Skip("Not Implemented on DB2.");
return;
}
switch (ConnectedDataProvider.GetDbType(con))
{
// case MonoTests.Utils.DataBaseServer.PostgreSQL:
// cmd = new OleDbCommand("GH_MULTIRECORDSETS('a','b','c')", con);
// break;
default:
cmd = new OleDbCommand("GH_MultiRecordSets", con);
break;
}
cmd.CommandType = CommandType.StoredProcedure;
OleDbCommandBuilder.DeriveParameters(cmd);
switch (ConnectedDataProvider.GetDbType(con))
{
case DataBaseServer.SQLServer:
case DataBaseServer.Sybase:
Compare(cmd.Parameters.Count, 1);
Compare(cmd.Parameters[0].Direction, ParameterDirection.ReturnValue);
Compare(cmd.Parameters[0].ParameterName, "RETURN_VALUE");
break;
case DataBaseServer.Oracle:
Compare(cmd.Parameters.Count, 3);
Compare(cmd.Parameters[0].Direction, ParameterDirection.Output);
Compare(cmd.Parameters[1].Direction, ParameterDirection.Output);
Compare(cmd.Parameters[2].Direction, ParameterDirection.Output);
Compare(cmd.Parameters[0].ParameterName, "RCT_EMPLOYEES");
break;
case DataBaseServer.PostgreSQL:
Compare(cmd.Parameters.Count, 1);
Compare(cmd.Parameters[0].Direction, ParameterDirection.ReturnValue);
Compare(cmd.Parameters[0].ParameterName, "returnValue");
break;
default:
throw new ApplicationException(string.Format("GHT: Test not implemented for DB type {0}", ConnectedDataProvider.GetDbType(con)));
}
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
}
}
public void TearDown()
{
if (ConnectedDataProvider.GetDbType() != DataBaseServer.SQLServer)
{
//All tests in this class are only for MSSQLServer.
Log(string.Format("All tests in this class are only for MSSQLServer and cannot be tested on {0}", ConnectedDataProvider.GetDbType()));
return;
}
if (con != null)
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
public void SetUp()
{
if (ConnectedDataProvider.GetDbType() != DataBaseServer.SQLServer)
{
//All tests in this class are only for MSSQLServer.
Log(string.Format("All tests in this class are only for MSSQLServer and cannot be tested on {0}", ConnectedDataProvider.GetDbType()));
return;
}
Exception exp = null;
BeginCase("Setup");
try
{
con = new SqlConnection(ConnectedDataProvider.ConnectionStringSQLClient);
con.Open();
Compare("Setup", "Setup");
}
catch (Exception ex) { exp = ex; }
finally { EndCase(exp); exp = null; }
}
public void run()
{
Exception exp = null;
int intRecordsAffected = 0;
try
{
BeginCase("Execute Insert");
cmd.CommandText = "INSERT INTO Employees (EmployeeID,FirstName, LastName) VALUES (99999,'OferXYZ', 'Testing')";
intRecordsAffected = cmd.ExecuteNonQuery();
Compare(intRecordsAffected, 1);
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
try
{
BeginCase("Check insert operation");
cmd.CommandText = "SELECT FirstName FROM Employees WHERE (EmployeeID = 99999)";
string strFirstName = cmd.ExecuteScalar().ToString();
Compare(strFirstName, "OferXYZ");
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
try
{
BeginCase("Execute Select");
cmd.CommandText = "SELECT EmployeeID FROM Employees WHERE (EmployeeID = 99999)";
intRecordsAffected = cmd.ExecuteNonQuery();
switch (ConnectedDataProvider.GetDbType())
{
case DataBaseServer.PostgreSQL:
// postgres odbc returns 1
#if !JAVA
{
Compare(intRecordsAffected, 1);
}
#else
{
Compare(intRecordsAffected, -1);
}
#endif
break;
default:
Compare(intRecordsAffected, -1);
break;
}
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
try
{
BeginCase("Execute UPDATE");
cmd.CommandText = "UPDATE Employees SET FirstName = 'OferABC', LastName = 'TestXYZ' WHERE (EmployeeID = 99999)";
intRecordsAffected = cmd.ExecuteNonQuery();
Compare(intRecordsAffected, 1);
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
try
{
BeginCase("Check Update operation");
cmd.CommandText = "SELECT FirstName FROM Employees WHERE (EmployeeID = 99999)";
string strFirstName = cmd.ExecuteScalar().ToString();
Compare(strFirstName, "OferABC");
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
try
{
BeginCase("Execute UPDATE");
cmd.CommandText = "DELETE FROM Employees WHERE (EmployeeID = 99999)";
intRecordsAffected = cmd.ExecuteNonQuery();
Compare(intRecordsAffected, 1);
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
try
{
BeginCase("Check Delete operation");
cmd.CommandText = "SELECT FirstName FROM Employees WHERE (EmployeeID = 99999)";
object obj = cmd.ExecuteScalar();
Compare(obj == null, true);
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
try
{
BeginCase("Check OleDBException - update with bad value");
cmd.CommandText = "UPDATE Employees SET BirthDate = 'bad value' WHERE (EmployeeID = 1)";
try
{
cmd.ExecuteNonQuery();
}
catch (OleDbException ex)
{
exp = ex;
}
Compare(exp.GetType().FullName, typeof(OleDbException).FullName);
exp = null;
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
try
{
BeginCase("Check OleDBException - missing EmployeeID");
cmd.CommandText = "INSERT INTO Employees (FirstName, BirthDate) VALUES ('Dado', 'Ben David')";
try
{
cmd.ExecuteNonQuery();
}
catch (OleDbException ex)
{
exp = ex;
}
Compare(exp.GetType().FullName, typeof(OleDbException).FullName);
exp = null;
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
}
public void run()
{
Exception exp = null;
//prepare data
OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
OracleCommand cmd = new OracleCommand("select ProductID,ProductName,Discontinued from Products where ProductID=1", con);
con.Open();
OracleDataReader rdr = cmd.ExecuteReader();
rdr.Read();
object obj = null;
switch (ConnectedDataProvider.GetDbType(con))
{
case DataBaseServer.DB2:
case DataBaseServer.SQLServer:
case DataBaseServer.PostgreSQL:
try
{
BeginCase("Column int - type");
obj = rdr["ProductID"];
Compare(obj.GetType().FullName, typeof(int).FullName);
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
break;
case DataBaseServer.Oracle:
case DataBaseServer.Sybase:
try
{
BeginCase("Column Decimal - type");
obj = rdr["ProductID"];
Compare(obj.GetType().FullName, typeof(decimal).FullName);
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
break;
}
try
{
BeginCase("Column int - value");
Compare(obj.ToString(), "1");
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
try
{
BeginCase("Column string - type");
obj = rdr["ProductName"];
Compare(obj.GetType().FullName, typeof(string).FullName);
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
try
{
BeginCase("Column string - value");
Compare(obj.ToString(), "Chai");
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
switch (ConnectedDataProvider.GetDbType(con))
{
case DataBaseServer.DB2:
try
{
BeginCase("Column Int16 - type");
obj = rdr["Discontinued"];
Compare(obj.GetType().FullName, typeof(Int16).FullName);
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
try
{
BeginCase("Column Int16 - value");
Compare(obj.ToString(), "0");
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
break;
case DataBaseServer.SQLServer:
try
{
BeginCase("Column bool - type");
obj = rdr["Discontinued"];
Compare(obj.GetType().FullName, typeof(bool).FullName);
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
try
{
BeginCase("Column bool - value");
Compare(obj.ToString().ToUpper(), "FALSE");
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
break;
case DataBaseServer.Oracle:
//Column type is Decimal - already tested
break;
}
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
public void run()
{
DoTestTypes(ConnectedDataProvider.GetSimpleDbTypesParameters());
DoTestTypes(ConnectedDataProvider.GetExtendedDbTypesParameters());
}
