ODBC connection with C# and ProvideX

[cosmoh2o]

I am new to using C# and ODBC connections and was wondering what is the easiest way to connect to a FACTS database (uses ProvideX ODBC driver) that is not on the localhost but is inside our LAN?
The .NET application is a Web Application running at 10.0.3.79 (on a Windows 2000 box) and the database (FACTS) running on a UNIX AIX box at 10.0.3.99.
I have downloaded the ProvideX 32-Bit ODBC driver and installed it on the localhost where the Web Application will be running and have made sure that the connection works. I have been all over the web looking for anyone that has dealt with the FACTS database (I believe it is a Sage product) and can't find much info on it.
Thanks to anyone with some ideas.

 

[cosmoh2o]

Never mind, I figured it out.
Turns out that in order to connect to an ODBC driver with .NET you need to download Microsoft.Data.ODBC.dll and install it into the .NET framework. Once I did this and called the "System DSN" (where the ProvideX 32-Bit ODBC driver was set up) using the connection methods provided in the new class, everything worked great.

 

[obislavu]

For ODBC access you have only in 1.1 NET Framework the following classes:
OdbcConnection, OdbcCommand, OdbcCommandBuilder, OdbcDataAdapter etc ... that will accomplish the same tasks as Sqldb* and Oledb* classes are doing for SQL and other Ole database providers.
This should compile and work with the right ConnectionString
public void ConnectToData()
{
System.Data.Odbc.OdbcConnection conn = null;
try
{
conn = new System.Data.Odbc.OdbcConnection ();
// TODO: Modify the connection string and include any
// additional required properties for your database.
conn.ConnectionString = "DSN=valid data source name;FIL=";

conn.Open();
// Process data .
}
catch (Exception ex)
{
MessageBox.Show("Failed to connect to data source"
}
finally
{
conn.Close();
}
}
-obislavu-

 

[cosmoh2o]

I have found another problem with ODBC .NET and was wondering if anyone else has found this problem. I can connect with .NET to the database with ODBC and everything works just fine, the only issue is "speed". It seems very slow. Is this normal for an ODBC .NET connection? I am using System DSN - will this cause a slow-down and if so is there a way to connect to the remote database without using DSN?

Thanks for any help.

 

[obislavu]

cosmoh2o - I have experience with .NET using MSSQL, Oracle, DB2, Access databases and I didn't see any "speed" problem due to the ODBC coonection.
I think you should look in each of the following directions:
1. network configuration
2. how you query the database.
2.1 Maybe there is a need to create more indexes.
2.2 Use stored procedures to accomplish the most of tasks in the databse.
3. use cache memory for the web application.
4. Implement web services and base the web application on those services
-obislavu-

 

[cosmoh2o]

I figured the "speed" problem out. It was my code. I was opening and closing connections unneccessarily to the database. After I refactored the class I built to access the database the program worked at a much improved pace.
Thanks for the quick response though.

 

[nthpvx]

If you're worried about speed using the ProvideX ODBC driver, are you using the 'Local' Driver, or the 'Client-Server' driver? In your setup, the Client-Server driver should give you better performance, but you'll have to configure the serverside on the AIX box.

FYI: FACTs is not a Sage product, it's written by Aperum. ProvideX is owned by Best Software Canada (which is owned by Sage UK).

Chris Nolan

http://nthdimension.net/

- ProvideX Experts