Specifying Connection Strings

for RuBoard

As mentioned previously, the connection string contains attributes. The attributes are specified in a semicolon-delimited list of name -value pairs that are used to identify all aspects of a connection, including the security information, the context (location of the data store and the particular database within the data store, if appropriate), and even information that specifies how the connection should behave in particular situations. The particular attributes are dependent on the provider. This section is a discussion of the attributes you can use with the SqlClient and OleDb providers.

Regardless of the provider, the values in a connection string may be delimited either with single or double quotation marks. You need to use quotation marks only if the value contains a space. Any spaces in the string will be ignored, the string is not case-sensitive, and the string is parsed immediately when the property is set, so you should be prepared to handle exceptions if you're building the string programmatically.

Tip

If you do build a connection string programmatically, make sure that users can't add their own attributes to the connection by appending a semicolon followed by the name-value pair in the user ID or password text boxes. In other words, parse the connection string before opening the connection to make sure that it contains only the attributes that you require to make your connection.

Specifying Connection Strings with SqlClient

The ConnectionString property of the SqlConnection object supports a number of attributes, many of which are mapped to read-only properties after the connection string is set. In most cases, the attribute has several aliases that also can be used. Table 9.1 shows the primary attributes. There is also a set of attributes used with connection pooling, which we'll address later today.

Table 9.1. SqlClient connection attributes. This table lists the attributes you can use in the connection string when using the SqlConnection object.

Attribute	Description
Application Name	The name of the application connecting to the server or .Net SqlClient Data Provider if not set. Can be used to identify the application using the SQL Profiler utility.
AttachDBFileName	Aliased to Initial File Name and used to specify the primary file of an attachable database.
Connect Timeout	Aliased to Connection Timeout; defaults to 15 seconds and maps to the read-only property ConnectionTimeout .
Current Language	The SQL Server Language to be used.
Data Source	Aliased to Server, Address, Addr, and Network Address. Specifies the name or network address of the SQL Server. Can use "(local)" or "." to specify the default instance of SQL Server on the same machine. Maps to the read-only DataSource property.
Enlist	Defaults to true and automatically enlists the connection in the current thread's transaction context. Useful in distributed transaction scenarios.
Initial Catalog	Aliased to Database and specifies the name of the database. Maps to the read-only property Database . If the catalog isn't specified, the current database will be the default database assigned to the login.
Integrated Security	Aliased to Trusted_Connection and defaults to false. When true, it attempts to use the current thread's identity to authenticate against SQL Server. Valid values are SSPI, true, and yes. Supported on all network libraries.
Network Library	Aliased to Net and defaults to dbmssocn. Specifies the network library to use when connecting to SQL Server. You change this from the default of TCP/IP to a different library, such as IPX/SPX (dbmsspxn), Apple Talk (dbmsadsn), Named Pipes (dbnmpntw), Multiprotocol (dbmsrpcn), or Shared Memory (dbmsipcn), if the application is running on a non-TCP/IP network or must support special features such as encryption.
Packet Size	Mapped to the read-only property PacketSize and used to specify the size in bytes of the network packets used to communicate with the server. Defaults to 8192.
Password	Aliased to Pwd and specifies the password for the SQL Server login account to use. Not needed when using integrated security.
Persist Security Info	Defaults to false; when set to true, removes security information from the connection string when it is returned through the ConnectionString property if the connection has been successfully opened.
Use Procedure For Prepare	Determines whether SQL Server creates temporary stored procedures when SqlCommand objects are prepared using the Prepare property. Defaults to 1 (true).
User ID	The SQL Server login account to use. It doesn't need to be set when using integrated security.
Workstation ID	Defaults to the local computer name and can be viewed in the SQL Profiler to assist in debugging and tracing. Maps to the read-only WorkstationId property.

Using the attributes shown in Table 9.1, a typical connection string for SQL Server might look like the following:

 String _connect = "server=ssosa;Initial Catalog=ComputeBooks;Enlist=false;" _connect += "Integrated Security=yes;Application Name='ComputeBooks Web';" 

In this case, the database server is identified as the server called ssosa and the database to use is called ComputeBooks. Enlist has been set to false as a small performance enhancement because, in this case, the connection string won't be used for connections that participate in distributed transactions (transactions across data sources). The connection will use integrated security, which means that SQL Server will try to match the identity information for the current thread with Windows accounts (users and groups) defined as valid SQL Server logins. Figure 9.1 shows a screenshot of the SQL Profiler after a stored procedure has been executed with this connection.

Figure 9.1. SQL Profiler. This utility is useful for debugging code written with the SqlClient provider.

You'll notice in Figure 9.1 that the connection was made using the Named Pipes network library and then the sp_reset_connection followed by the usp_GetTitles stored procedures were executed. Even though TCP/IP is the default network library, in this instance, the server supported connections only over Named Pipes, so SqlClient used Named Pipes. Note also that the Application Name is viewable in the Profiler as is the LoginName that in this case maps to a Windows account.

Note

Even if the Network Library attribute is set explicitly, the SqlClient provider can still connect using one of the other network libraries if the server isn't listening using the one specified.

Note that if standard SQL Server security were used, the User Id and Password attributes (unless the password was blank) would have had to be specified in the connection string. In addition, by default the Password wouldn't have been returned in the ConnectionString property after the connection had been successfully opened. Setting the Persist Security Info property to true would persist the Password in the connection string.

Specifying Connection Strings with OleDb

The connection string used with the OleDb provider is similar in many respects, although only the Provider attribute is required (unless a UDL file is used, as I'll discuss later). This attribute points to the OLE DB provider that will be used to connect to the data store.

Note

In this initial release, the provider doesn't support OLE DB 2.5 interfaces. Unfortunately, this means that OLE DB providers such as the Microsoft Exchange provider (ExOLEDB) can't be used with the OLE DB .NET Data Provider. However, you can use any OLE DB provider that doesn't use those interfaces that you have installed on your system with the exception of the OLE DB Provider for ODBC drivers (MSDASQL). To connect to ODBC drivers, you must use the ODBC .NET Data Provider that is downloadable from MSDN. The provider also requires MDAC 2.6 or higher, which is installed along with Visual Studio .NET.

If the provider attribute is not set, an ArgumentException will be thrown immediately after setting the ConnectionString property as it is parsed. All other attributes are determined by the provider itself.

Tip

You can, of course, determine what attributes a particular provider needs by creating a new data connection using the server explorer. As you learned on Day 2, "Getting Started," a dialog will be presented, allowing you to fill in the properties. You can then inspect the completed ConnectString property by clicking on the connection and viewing the Properties window.

So, for example, the following code snippet would successfully open a connection against SQL Server using the SQL Server OLE DB provider ( SQLOLEDB ):

 OleDbConnection con = new OleDbConnection( "Provider=sqloledb;server=ssosa;database=computebooks;trusted_connection=yes"); con.Open(); 

Typically, providers at least support the Data Source property, so connection strings like the following shown in the online documentation could be used to connect to a Jet or Oracle database:

 Provider=MSDAORA; Data Source=ORACLE8i7; User ID=scott; Password=tiger Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\bin\LocalAccess40.mdb 

As with SqlClient, you could also use the Persist Security Info attribute (initially set to false) to persist the password in the ConnectionString property. And as with SqlClient, the ConnectionTimeout , Database , DataSource , and Provider properties are all read-only properties that can be initially set in the connection string.

Finally, the ConnectionString property can alternatively point to a Microsoft Data Link file, also called a UDL file because of its .udl extension. Simply put, a UDL file is set up like an INI file that contains the connection string. UDL files can be easily created by creating a file with a .udl extension and then double-clicking it to invoke the Data Link Properties dialog. After it is built, the file can be referenced simply with the File Name attribute like so:

 OleDbConnection con = new OleDbConnection(   "File Name=c:\bin\myconnection.udl"); 

Unfortunately, when you use a UDL file like this, the file must be accessed and parsed each time the connection is opened, resulting in less than optimal performance. As a result, although UDL files provide an easy means of managing connection strings, they aren't recommended.

for RuBoard