private static void FindDonorKeysByBankAccount(BankImportTDS AMainDS) { if (AMainDS.PBankingDetails.Rows.Count == 0) { // First collect all IBANs in this statement List <string> IBANs = new List <string>(); foreach (BankImportTDSAEpTransactionRow transaction in AMainDS.AEpTransaction.Rows) { if (transaction.Iban.Length > 0) { IBANs.Add(transaction.Iban); } } // load all banking details by IBAN into AMainDS if (IBANs.Count > 0) { string sql = "SELECT " + PPartnerBankingDetailsTable.GetPartnerKeyDBName() + " as PartnerKey, " + "bd.* " + "FROM " + PBankingDetailsTable.GetTableDBName() + " bd, " + PPartnerBankingDetailsTable.GetTableDBName() + " pbd " + "WHERE pbd." + PPartnerBankingDetailsTable.GetBankingDetailsKeyDBName() + " = bd." + PBankingDetailsTable.GetBankingDetailsKeyDBName() + " " + "AND " + BankImportTDSPBankingDetailsTable.GetIbanDBName() + " IN ("; List <OdbcParameter> parameters = new List <OdbcParameter>(); foreach (string iban in IBANs) { if (parameters.Count > 0) { sql += ","; } sql += "?"; OdbcParameter p = new OdbcParameter("IBAN" + parameters.Count.ToString(), OdbcType.VarChar); p.Value = iban; parameters.Add(p); } sql += ")"; TDataBase db = DBAccess.Connect("FindDonorKeysByBankAccount"); TDBTransaction transaction = db.BeginTransaction(IsolationLevel.ReadUncommitted); db.SelectDT(AMainDS.PBankingDetails, sql, transaction, parameters.ToArray()); transaction.Rollback(); db.CloseDBConnection(); } } AMainDS.PBankingDetails.DefaultView.Sort = BankImportTDSPBankingDetailsTable.GetIbanDBName(); foreach (BankImportTDSAEpTransactionRow transaction in AMainDS.AEpTransaction.Rows) { // find the donor for this transaction, by his IBAN number Int64 DonorKey = GetDonorByIBAN(AMainDS, transaction.Iban); if (transaction.Iban.Length == 0) { // useful for NUnit testing for csv import: partnerkey in description try { DonorKey = Convert.ToInt64(transaction.Description); } catch (Exception) { DonorKey = -1; } } transaction.DonorKey = DonorKey; } }