Building a ConnectionString for Other Providers

Okay, so you need to connect to another data source and you need a ConnectionString. I don't have the bandwidth to cover every possible ConnectionString and the options associated with them, so I'll provide some typical ConnectionString settings to access the most common data sources^[17]. Note that all of these data sources require you to choose another data access provider, as SqlClient is designed to connect to SQL Server versions 7.0 and later. Remember to:

^[17] See www.connectionstrings.com for a comprehensive list of sample ConnectionStrings.

• Choose a native .NET Data Provider like OracleClient whenever one is available. These will expose the most features and offer the best performance.

• If a native .NET Data Provider is not available, try to find an ODBC driver and use the Odbc .NET Data Provider. This provider is light and fast, and is not COM-dependent.

• At last resort, use the OLE DB interface via the OleDb .NET Data Provider. This is the slowest of all providers, as it requires use of COM interop to existing OLE DB Providers. Generally, you won't have to use OleDb unless you're accessing a JET/Access database.

Regardless of the provider you choose, you'll need to make sure your project includes a reference to the corresponding .NET Framework DLL that supports it. If you have experience with .NET, you'll have trouble finding some of the old references used in previous versions of Visual Studio .NET, as they've been consolidated into System.Data.

Accessing a Flat File

When you need to read a delimited text file, you'll want to use the "ODBC" .Text driver. Actually, it turns out that this driver actually launches JET to access the file (sigh). Whenever you need to access an ODBC data source, you have at least two choices: create an ODBC DSN or hard-code the needed ConnectionString keywords (what I call a DSN-less connection). Figure 9.22 shows how to open a delimited text file (a comma-delimited file, in this case) using ADO.NET. This approach (without the data binding) is an ideal way to create a SqlDataReader to pass to the SqlBulkCopy function so that the text file data can be quickly (very quickly) imported into a SQL Server table.

Figure 9.22. Accessing a flat text file.

ADO Classic ADTG Recordset

If you use ADO classic to create a Recordset and persist the data with the Save method in ADTG format, you can read it using OleDb and the MSPersist provider. Note that the OleDb provider is also very particular about extraneous keywords passed in the ConnectionString. Figure 9.23 illustrates using the OleDb .NET data provider to read an .RS file created by the Recordset Save method. Note that the Command.CommandText is simply the path to the RS file.

Figure 9.23. Accessing ATDG persisted ADO classic Recordset (.RS) files.

JET/Access Database

Getting to a JET database is a matter of resolving the path and using the JET OLE DB provider that matches the version of the .MDB being accessedunless the database has a User ID and Password assigned. In this case, you'll need to add "Database Password=<your password> and possibly "System Database=<path to system.mdw>".

The JET 4.0 provider knows how to access the current (4.0) version of JET databases but uses "installable ISAM" drivers to access older versions (which complicate deployment). I provide this information here to help import JET data into SQL Server tables. See Figure 9.24 for an example.

Figure 9.24. Accessing a JET database.