public void TestDBAccess_SelectUsingDataAdapterMulti2()
{
TDBTransaction ReadTransaction = new TDBTransaction();
const string TestReadQuery1 =
"SELECT * from p_partner where p_partner_key_n = :APartnerKey and p_partner_short_name_c LIKE :APartnerShortName;";
DataTable TmpDT = new DataTable();
TDataAdapterCanceller TmpDac;
TDataBase db = DBAccess.Connect("TestDBAccess");
OdbcParameter[] ParametersArray;
List <object[]> ParameterValuesList = new List <object[]>();
ParametersArray = new OdbcParameter[2];
ParametersArray[0] = new OdbcParameter("APartnerKey", OdbcType.BigInt);
ParametersArray[1] = new OdbcParameter("APartnerShortName", OdbcType.Text);
ParameterValuesList.Add(new object[] { 43005001, "z%" });
ParameterValuesList.Add(new object[] { 43005002, "T%" });
db.ReadTransaction(ref ReadTransaction,
delegate
{
// Act AND Assert #1 - Prepared Parametrised Query
Assert.AreEqual(1, db.SelectUsingDataAdapterMulti(TestReadQuery1, ReadTransaction, ref TmpDT, out TmpDac,
AParameterDefinitions: ParametersArray, AParameterValues: ParameterValuesList, APrepareSelectCommand: true),
"SelectUsingDataAdapterMulti using a Prepared Command did not yield 1 records, but ought to.");
// Act AND Assert #2 - Non-Prepared Parametrised Query
Assert.AreEqual(1, db.SelectUsingDataAdapterMulti(TestReadQuery1, ReadTransaction, ref TmpDT, out TmpDac,
AParameterDefinitions: ParametersArray, AParameterValues: ParameterValuesList),
"SelectUsingDataAdapterMulti using NO Prepared Command did not yield 1 records, but ought to.");
});
}
public void TestDBAccess_SelectUsingDataAdapterMulti1()
{
TDBTransaction ReadTransaction = new TDBTransaction();
const string TestReadQuery1 = "SELECT * from p_partner where p_partner_key_n = :APartnerKey;";
DataTable TmpDT = new DataTable();
TDataAdapterCanceller TmpDac;
TDataBase db = DBAccess.Connect("TestDBAccess");
OdbcParameter[] ParametersArray = new OdbcParameter[1];
List <object[]> ParameterValuesList = new List <object[]>();
ParametersArray[0] = new OdbcParameter("APartnerKey", OdbcType.BigInt);
ParameterValuesList.Add(new object[] { 43005001 });
ParameterValuesList.Add(new object[] { 43005002 });
db.ReadTransaction(ref ReadTransaction,
delegate
{
// Act AND Asserts # 1 - Prepared Parametrised Query
Assert.AreEqual(2, db.SelectUsingDataAdapterMulti(TestReadQuery1, ReadTransaction, ref TmpDT, out TmpDac,
AParameterDefinitions : ParametersArray, AParameterValues : ParameterValuesList,
APrepareSelectCommand : true,
AProgressUpdateEveryNRecs : 1, AMultipleParamQueryProgressUpdateCallback : delegate(int AProgressUpdateCounter)
{
FProgressUpdateCounter++;
FProgressUpdateNumber = AProgressUpdateCounter;
return(false);
}),
"SelectUsingDataAdapterMulti using a Prepared Command did not yield 2 records, but ought to.");
Assert.AreEqual(2, FProgressUpdateCounter,
"SelectUsingDataAdapterMulti using a Prepared Command did not yield 2 progress updates, but ought to.");
// Act AND Asserts # 2 - Non-Prepared Parametrised Query
FProgressUpdateCounter = 0;
Assert.AreEqual(2, db.SelectUsingDataAdapterMulti(TestReadQuery1, ReadTransaction, ref TmpDT, out TmpDac,
AParameterDefinitions : ParametersArray, AParameterValues : ParameterValuesList,
AProgressUpdateEveryNRecs : 2, AMultipleParamQueryProgressUpdateCallback : delegate(int AProgressUpdateCounter)
{
FProgressUpdateCounter++;
FProgressUpdateNumber = AProgressUpdateCounter;
return(false);
}),
"SelectUsingDataAdapterMulti using NO Prepared Command did not yield 2 records, but ought to.");
Assert.AreEqual(1, FProgressUpdateCounter,
"SelectUsingDataAdapterMulti using NO Prepared Command did not yield 1 progress update, but ought to.");
});
}
/// <summary>
/// Run this Database query.
/// If FReportingQueryCancelFlag is set, this returns immediately with an empty table.
/// The query can be cancelled WHILE IT IS RUNNING. In this case the returned table may be partially filled.
/// </summary>
/// <remarks>For details on the Arguments that can be passed with <paramref name="AOptionalColumnNameMapping"/>,
/// <paramref name="ASelectCommandTimeout"/>, <paramref name="AParameterDefinitions"/>,
/// <paramref name="AParameterValues"/>, <paramref name="APrepareSelectCommand"/>,
/// <paramref name="AProgressUpdateEveryNRecs"/> and
/// <paramref name="AMultipleParamQueryProgressUpdateCallback"/> please see their respective XML Comments on
/// Method <see cref="TDataBase.SelectUsingDataAdapterMulti"/>!/</remarks>
/// <returns>DataTable. May be empty (even with no fields defined!) if cancellation happens or has happened.</returns>
public DataTable RunQueryMultiParams(String Query, String TableName, TDBTransaction Trans,
TDataBase.TOptionalColumnMappingDelegate AOptionalColumnNameMapping = null,
int ASelectCommandTimeout = -1, DbParameter[] AParameterDefinitions = null, List <object[]> AParameterValues = null,
bool APrepareSelectCommand = false, Int16 AProgressUpdateEveryNRecs = 0,
TDataBase.MultipleParamQueryProgressUpdateDelegate AMultipleParamQueryProgressUpdateCallback = null)
{
var ResultDT = new DataTable(TableName);
try
{
FPrivateDatabaseObj.SelectUsingDataAdapterMulti(Query, Trans, ref ResultDT,
AOptionalColumnNameMapping, ASelectCommandTimeout, AParameterDefinitions, AParameterValues,
APrepareSelectCommand, AProgressUpdateEveryNRecs, AMultipleParamQueryProgressUpdateCallback);
}
catch (PostgresException Exp)
{
if (Exp.SqlState == "57014") // Exception with Code 57014 is what Npgsql raises as a response to a Cancel request of a Command
{
TLogging.LogAtLevel(7, this.GetType().FullName + ".RunQuery: Query got cancelled; proper reply from Npgsql!");
}
else if (Exp.SqlState == "25P02") // Exception with Code 25P02 is what Npgsql raises as a response to a cancellation of a request of a Command when that happens in another code path (eg. on a different Thread [e.g. Partner Find
{ // screen: Cancel got pressed while Report Query ran, for instance])
TLogging.LogAtLevel(1, this.GetType().FullName +
".RunQuery: Query got cancelled (likely trought another code path [likely on another Thread]); proper reply from Npgsql!");
}
else
{
TLogging.Log(this.GetType().FullName + ".RunQuery: Query got cancelled; general PostgresException occured: " + Exp.ToString());
}
return(null);
}
catch (Exception Exc)
{
TLogging.Log("ReportingQueryWithCancelOption: Query Raised exception: " + Exc.ToString() +
"\nQuery: " + Query);
FRunQueryException = Exc;
/*
* WE MUST 'SWALLOW' ANY EXCEPTION HERE, OTHERWISE THE WHOLE
* PETRASERVER WILL GO DOWN!!! (THIS BEHAVIOUR IS NEW WITH .NET 2.0.)
*
* --> ANY EXCEPTION THAT WOULD LEAVE THIS METHOD WOULD BE SEEN AS AN <--
* --> UNHANDLED EXCEPTION IN A THREAD, AND THE .NET/MONO RUNTIME <--
* --> WOULD BRING DOWN THE WHOLE PETRASERVER PROCESS AS A CONSEQUENCE. <--
*
*/
}
return(ResultDT);
}
