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); }