The Provider keyword is the exception to this rule. If a ‘ keyword=value’ pair occurs more than once in the connection string, then the value assigned by the last occurrence is used. The keyword is not case-sensitive whereas the value may be. The connection string is actually a list of keyword/attribute pairs. The user would see dialog boxes that prompted for such information as passwords, user IDs, or the name of a file. An ODBC function called SQLBrowseConnect could be used iteratively by applications to build up a connection string, passing back lists of alternatives, or question marks in places where it needed further input, until a connection could be made.
When the idea of the connection string was developed, the designers realised that one needed to browse for data. This wasn’t originally intended for human consumption. In ODBC and ole DB, all the specifics of a connection are held in a connection string. However, once you have one that works, it is worth taking time to puzzle out why and how it works. It pays to look around before cutting your own from scratch. When looking for a connection string, the web holds a rich store of connection strings that work. In debugging the previous illustration, for example, I spent ages scratching my head wondering why it couldn’t find the table in the HTML page until I discovered that the square-brackets delimiting the table name were obligatory.
The errors from the drivers are often arcane, often irrelevant, and not nearly explicit enough to indicate where the problems lie. Nobody will try to pretend that it is easy to know what to do when a connection string doesn’t work. Connection strings are still at the heart of things with ADO.NET, used to store all the provider-specific information. With ADO.NET, the MDAC data providers are accessed via a ‘Data Provider Factory’ all you need to do is to choose the Provider (normally OleDB, ODBC, OracleClient, SQLClent, or SQLServerCE) and pass it the connection string and you can, if you choose, do the rest of your coding in a Database-independent way through a set of abstractions that include the datasource, session, command and rowsets. The connection string contains all the vendor-specific information to allow a connection to be made. ODBC even supplied a minimum SQL Grammar based on X/Open (1992) that allowed it to be used with any vendor’s data store. ODBC, JDBC and OLE DB all provide an interface to the application that is reasonably generic.
The general idea is for the application code to be as independent of the actual data source as possible. The Application perspectiveĬertainly, although you have other options such as Linq to SQL when connecting to SQL Server, the use of ADO.NET with a connection string will solve a lot of problems when connecting to a database, application, or wide variety of data stores. Neat eh? You cannot write to the website, mercifully, with this provider, although if your internet site supports WebDAV, (Frontpage or Sharepoint, for example), then you can use the MSDAIPP provider to do that.
Connection Strings, an Overview - Simple Talk