Your .NET Windows Console application needs to use a STA (single-threaded-apartment) threading model. Multi-threading actually slows QODBC down as we need to re-establish a connection to QuickBooks every time the Thread ID changes.
See the msdn .NET Framework Developer Center: http://msdn2.microsoft.com/en-us/library/system.stathreadattribute.aspx
Note: ADODB works, but when you loop through the resultant recordset via Recordset.MoveNext(), an additional query is made through QODBC for each record. These additional queries caused an additional 50 minutes of runtime for a 7000 invoice query.
The first function below demonstrates the ADODB method that took ~1 hour. The second does the same thing with ODBC and takes less than a minute.
[STAThread] static void test1() { ADODB.Connection con = new ADODB.Connection(); con.Open("DSN=SOQB;OLE DB Services=-2", "", "", -1); string invoiceSQL = "SELECT CustomerRefFullName, RefNumber, TxnDate, BalanceRemaining, AppliedAmount, Memo " + "FROM Invoice " + "WHERE TxnDate>{d'2006-04-02'}"; ADODB.Recordset invoiceResult = new ADODB.Recordset(); invoiceResult.Open(invoiceSQL, con, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, 0); while(!invoiceResult.EOF) { Console.WriteLine("Invoice #" + invoiceResult.Fields["RefNumber"].Value.ToString()); invoiceResult.MoveNext(); } con.Close(); }
[STAThread] static void test2() { OdbcConnection con = new OdbcConnection("DSN=SOQB"); con.Open(); OdbcDataAdapter dAdapter = new OdbcDataAdapter( "SELECT CustomerRefFullName, RefNumber, TxnDate, BalanceRemaining, AppliedAmount, Memo " + "FROM Invoice " + "WHERE TxnDate>{d'2006-04-02'}", con); DataTable result = new DataTable(); dAdapter.Fill(result); DataTableReader reader = new DataTableReader(result); while(reader.Read()){ Console.WriteLine("Invoice #: " + reader.GetString(1)); } con.Close(); }
|