Sample Code for ASP C# QODBC Connection

Steps to Creating Connection in C#

Try adding a new data source to your project by selecting Data -> Add New Data Source -> Selecting Database -> Next Clicking on the New Connection... button -> Selecting Data Source: Microsoft ODBC Data Source -> clicking Continue -> Then selecting Use user or system data source name: QuickBooks Data -> Then press Test Connection.

Possible Error

But you might get an error message from Microsoft Visual Studio that reads:

Note: You can connect to the ODBC data source using the OdbcConnection class (http://msdn2.microsoft.com/en-us/library/system.data.odbc.odbcconnection.aspx).

Minimal Code Required

The following code is the minimal code required to create a DataSet from the SQL statement and attaches the dataset to a DataGrid:

Dim cnQODBC As System.Data.Odbc.OdbcConnection
Dim daQODBC As System.Data.Odbc.OdbcDataAdapter
Dim dsQODBC As System.Data.DataSet

cnQODBC = New System.Data.Odbc.OdbcConnection("DSN=QuickBooks Data")
cnQODBC.Open()
daQODBC = New System.Data.Odbc.OdbcDataAdapter("SELECT ListID, FullName, CompanyName FROM Customer", cnQODBC)
dsQODBC = New System.Data.DataSet

daQODBC.Fill(dsQODBC)
dgDataGrid.DataSource = dsQODBC
dgDataGrid.DataBind() 'May or may not be required depending on where you put this code.

Related Link

See also:
Sample Code for VB.NET with QODBC

See also the msdn .NET Framework Developer Center: http://msdn2.microsoft.com/en-us/library/system.stathreadattribute.aspx.

Note: ADODB works when you loop through the result and 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.

Comparison between ADODB and ODBC Method

Note: 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.

ADODB Method

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

ODBC Method

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

Keywords : C #, c%23 , c#.net, .net, c%23.net