ADO and ODBC - confused
-
I improved on an application that was found on this site (OQuery). I made it so that it could access different types of DBMS such as MySQL, Oracle (which is originally did), MS-SQL 2000. I had two people ask me if I used ODBC which I replied that I used ADO. I'm a little confused because now I'm having trouble with the differences between ADO and ODBC. I thought ADO ends up using ODBC....right? I've also heard that an ODBC connection is 10 slower than that of ADO....can someone confirm this. And should I be using ODBC? Thanks
Tom Wright tawright915@gmail.com
-
I improved on an application that was found on this site (OQuery). I made it so that it could access different types of DBMS such as MySQL, Oracle (which is originally did), MS-SQL 2000. I had two people ask me if I used ODBC which I replied that I used ADO. I'm a little confused because now I'm having trouble with the differences between ADO and ODBC. I thought ADO ends up using ODBC....right? I've also heard that an ODBC connection is 10 slower than that of ADO....can someone confirm this. And should I be using ODBC? Thanks
Tom Wright tawright915@gmail.com
Yes, ADO is a wrapper to ODBC. But I think you really mean ADO.Net?
-
Yes, ADO is a wrapper to ODBC. But I think you really mean ADO.Net?
Yes...ADO.NET. So why am I being asked if I used ODBC. Is there some ODBC secret that's not being shared that I should know about? Thanks
Tom Wright tawright915@gmail.com
-
I improved on an application that was found on this site (OQuery). I made it so that it could access different types of DBMS such as MySQL, Oracle (which is originally did), MS-SQL 2000. I had two people ask me if I used ODBC which I replied that I used ADO. I'm a little confused because now I'm having trouble with the differences between ADO and ODBC. I thought ADO ends up using ODBC....right? I've also heard that an ODBC connection is 10 slower than that of ADO....can someone confirm this. And should I be using ODBC? Thanks
Tom Wright tawright915@gmail.com
ADO is a wrapper for a technology called OLE DB, although it has little to do with OLE (Object Linking and Embedding). The database-specific layer (called a Provider) is a set of COM objects which implement interfaces designed by Microsoft. The client talks directly to the OLE DB provider with no intermediary. ADO is a classic VB-friendly wrapper for OLE DB. ODBC (Open Database Connectivity) is based around a manager/driver model. Applications talk to the ODBC manager, which talks to the drivers. The extra overhead is generally minimal. ODBC is the ANSI standard interface defined in the SQL standard. To enable transition to ADO/OLE DB from ODBC, i.e. so that client application developers could use the new interface before the database developers released OLE DB providers, Microsoft made a special provider called the OLE DB Provider For ODBC available (its ProgID, which you specify in the Provider connection string parameter, is MSDASQL). You should only use this where there is no native OLE DB provider. This provider adds extra overhead and implements a reduced feature set compared to native OLE DB providers. Unfortunately it's also the default provider - it's what you get if you don't specify a Provider in the connection string. The relative speed of ODBC and ADO is all down to the provider and driver, but in general, all else being equal, ADO should be slightly faster than ODBC if you're using a native OLE DB provider. ADO.NET is slightly misnamed in the .NET Framework. It's really a general umbrella name for 'how .NET does data access'. The desktop Framework ships with four Providers (the name is the same but the concepts have changed) - two generic (OLE DB, ODBC) and two specific (SQL Server, Oracle [since 1.1]). You can also obtain third-party Providers. The cost of calling a COM object is slightly higher than calling a 'flat' API like ODBC in the .NET Framework, so OLE DB's small performance advantage is mostly negated. You can access a third-party database like MySQL either through a native .NET provider or via an OLE DB provider (using OleDbConnection) or ODBC driver (using OdbcConnection). Generally you should prefer a native .NET provider as it's likely to have more features and less translation.
Stability. What an interesting concept. -- Chris Maunder
-
ADO is a wrapper for a technology called OLE DB, although it has little to do with OLE (Object Linking and Embedding). The database-specific layer (called a Provider) is a set of COM objects which implement interfaces designed by Microsoft. The client talks directly to the OLE DB provider with no intermediary. ADO is a classic VB-friendly wrapper for OLE DB. ODBC (Open Database Connectivity) is based around a manager/driver model. Applications talk to the ODBC manager, which talks to the drivers. The extra overhead is generally minimal. ODBC is the ANSI standard interface defined in the SQL standard. To enable transition to ADO/OLE DB from ODBC, i.e. so that client application developers could use the new interface before the database developers released OLE DB providers, Microsoft made a special provider called the OLE DB Provider For ODBC available (its ProgID, which you specify in the Provider connection string parameter, is MSDASQL). You should only use this where there is no native OLE DB provider. This provider adds extra overhead and implements a reduced feature set compared to native OLE DB providers. Unfortunately it's also the default provider - it's what you get if you don't specify a Provider in the connection string. The relative speed of ODBC and ADO is all down to the provider and driver, but in general, all else being equal, ADO should be slightly faster than ODBC if you're using a native OLE DB provider. ADO.NET is slightly misnamed in the .NET Framework. It's really a general umbrella name for 'how .NET does data access'. The desktop Framework ships with four Providers (the name is the same but the concepts have changed) - two generic (OLE DB, ODBC) and two specific (SQL Server, Oracle [since 1.1]). You can also obtain third-party Providers. The cost of calling a COM object is slightly higher than calling a 'flat' API like ODBC in the .NET Framework, so OLE DB's small performance advantage is mostly negated. You can access a third-party database like MySQL either through a native .NET provider or via an OLE DB provider (using OleDbConnection) or ODBC driver (using OdbcConnection). Generally you should prefer a native .NET provider as it's likely to have more features and less translation.
Stability. What an interesting concept. -- Chris Maunder
Wow.....thanks for the great explination. Okay so if the database creator....for better use of the word....provides a .NET provider then it's best to use it. And if I wanted to add an "Other Database" option to my program...I can always use ODBC to connect to the "Other" database and run a query. I guess the great thing about ODBC is that I would not have to install all the providers for each of the different types of DB's out there. So now I understand why I keep getting asked if I'm using ODBC. DING....the light came on
Tom Wright tawright915@gmail.com
-
Yes...ADO.NET. So why am I being asked if I used ODBC. Is there some ODBC secret that's not being shared that I should know about? Thanks
Tom Wright tawright915@gmail.com
lol, not at all. ODBC is very old and low level. ADO.net, however, is a new(er) implementation for data access and pretty fast. For one, it allows for disconnected datasources which, in itself, is much more efficient for the server (server doesn't have to manage connections). Perfect for web apps. ODBC was used more for the old client/server connected model. It almost sounds like you're being asked about ODBC because the datasource they want to use doesn't have an ADO.NET connector available?
-
lol, not at all. ODBC is very old and low level. ADO.net, however, is a new(er) implementation for data access and pretty fast. For one, it allows for disconnected datasources which, in itself, is much more efficient for the server (server doesn't have to manage connections). Perfect for web apps. ODBC was used more for the old client/server connected model. It almost sounds like you're being asked about ODBC because the datasource they want to use doesn't have an ADO.NET connector available?
That's possible...although all of the options that I've added to my app have a .NET connector. I'm also wondering is they are asking that because have a different provider for each type of database is not really important. Between you an Mike, you'ver really cleared this up....now I look and sound smarter. Thanks
Tom Wright tawright915@gmail.com
-
Wow.....thanks for the great explination. Okay so if the database creator....for better use of the word....provides a .NET provider then it's best to use it. And if I wanted to add an "Other Database" option to my program...I can always use ODBC to connect to the "Other" database and run a query. I guess the great thing about ODBC is that I would not have to install all the providers for each of the different types of DB's out there. So now I understand why I keep getting asked if I'm using ODBC. DING....the light came on
Tom Wright tawright915@gmail.com
It's worth knowing that the native providers, e.g. the SQL Server providers, generally use the databases native API, so you get the best speed because they aren't dealing with the plumbing that technologies that ODBC put in place.
Please visit http://www.readytogiveup.com/ and do something special today. Deja View - the feeling that you've seen this post before.