Writing Code to Create Connections

Okay, I'm finally ready to start talking about the code needed to open a connection. A big part of this discussion involves protecting the logon credentials and other ConnectionString details you use to gain access to the server. Microsoft seems very concerned with this aspect of security, but I'm not so frightened. That's because you must trust your developers and those that can review your codeif you can't, you're in trouble. In any respect, your DBA can protect your data and servers by setting up appropriate and specifically restricted Login accounts that can't do anything but what the application is intended to do. Visual Studio also has several new features that let you move your ConnectionString to the XML configuration file and encrypt it, if necessary. On the other hand, Peter has a good point:

I'm probably petrified. I trust the "authorized" developers and testers, but do I trust the users? Should I trust the users? Never, ever! Given, a user with a bit of technical background can determine the credentials being used in an database application and then use these credentials to get access to things they potentially should not is pretty scary. While your DBA might be happy for you to connect to the Accounting Database using the Accounting Client Application, they probably would be fired if they let you take a development tool like Visual Studio (or Access) and connected it to the Accounting Database. Yes, there are ways of limiting what a particular login account can do, but if the user doesn't know the credentials the application is using or the server that is being addressed by the application, they are at a greater and more controlled distance.

What Is a ConnectionString?

Okay, let's stop a second. I noticed there are a few newbies out there who might need a few terms defined before I go on. First, if you've worked with ADO classic before (or ADO.NET), you know that in order to open a connection, you need to point to a specific server instance and that SQL Server needs to know who you are (or who you're pretending to be). ADO.NET uses a ConnectionString to pass the answers to these questions to SQL Server. It's a bit early to talk about the specific contents of the ConnectionString, but I'll get to that later in this chapter. The ConnectionString is easy to code, but if you make a mistake coding it, your code can crash at runtimenot good. Consider using a class like the SqlConnectionStringBuilder to build the ConnectionString at design time. I'll show you how next.

Generally, your ConnectionString should not change during the life of your application. Each time it does, ADO.NET creates a new connection pool to manage it and all other connections with the same ConnectionStringthis can be very expensive. However, creating a different ConnectionString can help performance if you need a separate connection pool for some operations.

Building a ConnectionString with the SqlConnectionStringBuilder Class

If you need to create a ConnectionString programmatically, you can use the SqlConnectionStringBuilder class. It's certainly a better choice than concatenating key/value pair strings together and hoping the resulting string is a valid ConnectionString. That's because as you address the SqlConnectionStringBuilder object's strongly typed properties (or their synonyms), the class validates the entry on-the-flywell, for the most part. For example, (as shown in Figure 9.13), you can build up a valid ConnectionString to access SQL Server by selecting from a list of valid ConnectionString arguments.

Figure 9.13. Choose valid ConnectionString arguments from the SqlConnectionStringBuilder properties.

I'm not using "late binding" to address the properties of the SqlConnectionStringBuilder object in the example shown in Figure 9.13. Although the documentation suggests this approach, it's problematic, as it delays resolution of the selected property until runtime and makes it easy to specify invalid keywords.

When you work with other .NET Data Providers (like System.Data.Odbc), you'll discover that several of the more important OdbcConnectionStringBuilder properties are not listed as properties. This might be an oversight or an intentional omission. In any case, you'll need to refer to these ConnectionString keywords using late binding. I illustrate that a bit later when discussing how to connect to flat files.

Tip

Yes, there are "ConnectionStringBuilder" classes for each ADO.NET provider. Each has its own properties that map to the provider-specific ConnectionString they build.

Figure 9.14 shows how to populate the SqlConnectionStringBuilder with property settings. As you enter each property, Visual Studio's Intellisense makes sure you choose from valid entries (at least, for most settings). Unlike other providers, SqlClient does not permit any "extraneous" entries in the ConnectionString, so be careful as you type late-bound entries that refer to keywords using Item("String of keyword") referencing.

Figure 9.14. Populating the SqlConnectionStringBuilder object with values.

Protecting the ConnectionString

When you include sensitive credential information in a ConnectionString, it's important to protect those credentials from prying eyes. I'm of the opinion that it makes more sense to use credentials that permit access to just those parts of the database that the application is programmed to seeand no more. Sure, there are applications that need access to more sensitive information, and those will need to be granted access to those areas. In virtually all cases, it's important to protect the credentials. Fortunately, there are several strategies implemented in Visual Studio to help. First, you can set the BrowsableConnectionString property of the SqlConnectionStringBuilder object to false. This prevents the Visual Studio IDE from making the ConnectionString visible.

Persisting the ConnectionString

Visual Studio makes it easier than ever to save application or user "settings", which is a useful place to store a ConnectionString or any other value used to configure an application that you used to store in .INI files or the Windows Registry. These name/value pairs are stored behind the scenes in XML files, but fortunately, you won't have to manipulate these files directly. In a Windows Forms application, settings are placed in the app.config file. ASP.NET (and ASP Web Service) applications use the web.config file. There are two basic types of "settings":

• Application settings: These are read-only settings that you initialize using the Solution Properties page, Settings tab (as shown in Figure 9.15). The name must conform to namespace requirement. The type when defining a ConnectionString should be "(Connection String)". The value (in this case) is set by clicking on the ellipsis (...) and filling in the Connection Properties dialog (as explained later in this chapter).

  Figure 9.15. Using the Solution Properties page to change the app.config file.

  

• User settings: These are read-write settings (as shown in Figure 9.15) that can be initialized like application settings or in your code.

It's easy to address either type of settingbut don't bother trying to set application settings in code. Using the My.Settings.Save() method seems to work, but the values are not changed. For example, to fetch a saved ConnectionString from the app.config settings, you can use strongly typed referencing that exposes each of the settings as My.Settings propertiesthis means they'll show up in the Intellisense list of valid properties, as shown in Figure 9.16.

Figure 9.16. Getting and setting application and user settings in code using My.Settings.

Building a Connection Object

I'm now ready to build an ADO.NET Connection object. For those of you new to .NET, remember that I'll use class New "constructors", which are a special class function whose job it is to initialize or "construct" an instance of a class. When the class is built using Visual Basic .NET or C#, you can include one or more "New" functions that help developers set up the instances of the class in the Visual Studio IDE. Data is passed to the New constructor as arguments, and these are used however the New function sees fit, but generally to set properties of the new class instance.

The SqlConnection object has two constructors, one that takes no arguments and simply returns an uninitialized SqlConnection object, and a second that accepts a properly constructed ConnectionString. That's the trickbuilding a string that has all of the required name/value pairs associated with a ConnectionString correctly spelled and with correctly scaled values. That is, if you provide a value for a named ConnectionString argument, it must conform to one of the valid keyword names, along with the correct datatype and range of permissible values for that keyword. That's where the table of acceptable values comes inunless you've used the SqlConnectionStringBuilder class to construct the ConnectionString (as shown in Figure 9.14).

Table 9.1 lists the valid names and aliases for keyword values within the ConnectionString. I've organized the keywords by function. Note that acceptable keywords must be spelled as shown (including the spaces) but are not case-sensitive. Do not confuse the SqlConnectionStringBuilder properties from the keywords they setthe properties are shown in (parenthesis).

Table 9.1. Valid ConnectionString Keywords and Aliases

	Keyword/(Property)	Default	Description
Specifying Server, SQL Server Instance, and Database
	AttachDBFilename Initial File Name Extended Properties (AttachDBFilename)		Attachable database (.MDF) file path (see discussion). Database file must be used with and match "Database" argument, which names the database being attached.
	Data Source Server Address Addr Network Address (DataSource)		Addresses server either by NetBIOS name or by TCP address and port. Server=tcp:servername, portnumber. The default port is 1433 (see discussion).
	User Instance (User Instance)	false	(SQLExpress only.) Creates a new private instance of SQL Server for the user. Copies the MDF file to the user's data area. Used with AttachDBFilename (see discussion).
	Initial Catalog Database (InitialCatalog)		The name of the default database (see discussion).
Specifying Security
	Integrated Security Trusted_Connection (Integrated Security)	false	If false, SQL Server User ID and Password must be supplied. If true (or SSPI), Windows credentials are used for authentication (see discussion).
	Password Pwd (Password)		Recognized only when Integrated Security/Trusted_Connection is not used or is false. Indicates password for specified SQL Server Login ID.
	User ID (UserID)		Recognized only when Integrated Security/Trusted_Connection is not used or is false. Indicates SQL Server Login ID. Requires matching Password argument (see discussion).
	Encrypt (Encrypt)	false	If true and SQL Server is properly configured, SSL is used to transport all data sent to and from server.
	Persist Security Info (PersistSecurityInfo)	false	When false, security information is not exposed on the Connection object (see discussion).
Specifying Options
	Async[10] (AsynchronousProcessing)	false	If true, enables asynchronous operation support.
	Application Name (ApplicationName)	.NET SqlClient Data Provider	Used to identify your application when using Profiler traces. Can be set to any string value.
	Connect Timeout Connection Timeout (ConnectTimeout)	15	Number of seconds to wait for the connection process to complete. Not to be confused with CommandTimeout (see discussion).
	Current Language (CurrentLanguage)	Language set on database	The SQL Server language to use.
	Failover Partner | (FailoverPartner)		Names mirrored partner SQL Server. This keyword uses the same addressing scheme as Data Source (2.0 or later; see discussion).
	MultipleActiveResultSets (MultipleActiveResultSets)	false	Enables multiple operations on a single connection (see discussion).
	Network Library Net (Network Library)	dbmssocn	Network protocol. Can be dbnmpntw (Named Pipes), dbmsrpcn (Multiprotocol), dbmsadsn (Apple Talk), dbmsgnet (Via), dbmslpcn (shared memory), dbmsspxn (IPX/SPX), or dbmssocn (TCP/IP).
	Packet Size (Packet Size)	8192	Network packet size in bytes.
	Workstation ID (WorkstationID)	(local computer name)	Identifies workstation name. Can be used to filter Profiler traces. Can be set to any string value.

^[10] This is documented as "asynch" (which does not work).

Before I move on, I need to pause a moment and more fully discuss a few of these keywords, as they're pretty important to your connection strategy. First, ADO.NET has tightened up their parser, so leading or trailing spaces and case are now ignored. This means "Server = Fred; Database = George" is the same as "Server=fred; Database=GeorGe". I don't recommend using values that contain a semicolon (;), but if that's necessary, you can frame the value in double quotes. Next, most Boolean values can be set to a number of alternative values. For example, true = yes and false=no in most cases. I suggest sticking with true or false. The only important exception is where Microsoft encourages us to set the Integrated Security keyword to "SSPI". While in parts of Texas and in the White House some versions of ADO.NET still accept "yup" in place of "yes", sadly, Microsoft dropped support of "you betcha" as a value setting some time ago.

Using AttachDBFilename

This keyword appeared in earlier versions of ADO.NET, but it's gained a great deal of flexibility with the innovations associated with (just the) SQL Server Express edition. Used alone, AttachDBFilename points to a SQL Server .MDF database file that's ready^[11] to be attached to the master database. The AttachDBFilename value is set to the name of the primary database file, including the full path name of an attachable database. The path may be absolute or relative by using the DataDirectory substitution string. If DataDirectory is used, the database file must exist within a subdirectory of the directory pointed to by the substitution string. The DataDirectory is set by the application installer and is visible via My.Application.Deployment.DataDirectorybut not during debug. No, you can't put the .MDF on a remote server or provide an HTTP or UNC pathname to the .MDF file.

^[11] By "ready," I mean it's been properly detached from another SQL Server instancenot just ripped out, leaving the wires hanging down.

When you use the AttachDBFilename keyword, ADO.NET opens the connection and checks to see if the named target database (as determined by the Database or Initial Catalog keyword value settings) has already been attached. If it's there, ADO.NET is done and you're granted access to the database (assuming you have permission). However, if the database has not been previously attached, ADO.NET permanently attaches the file to the target SQL Server instance. In this case, the "Database" or "Initial Catalog" keywords must name the database being attached. If it does not match, ADO.NET throws an exception and falls to the floor kicking and screaming.

Using AttachDBFilename with SQLExpress Instances

In the 2.0 Framework, ADO.NET dramatically expands the AttachDBFilename functionality. If you use AttachDBFilename, the keyword "User Instance" is set to true, and you're connecting to a SQL Server Express instance; the MDF database file and the SQL Server Express system database files (master, model, and tempdb) are copied to the user disk area under "Documents and Settings". Subsequently, this new private instance of SQL Server is started and a new process is spun up as the user who opened the connection and the MDF file is attached to the copy of master. Simple? Well, this approach means the user (based on the current Windows credentials) can have full use of the user database as well as the entire set of system databases without fear of impacting the "root" system database or any other user databases on the system.

Because the user has full access to a User Instance .MDF, it's possible to "safely" open the database connection as "SA" with full rightsbut since the user has full rights, there is no need to enable mixed-mode security. This AttachDBFilename approach was implemented because Microsoft wanted users to be able to run applications as usersand not have to have elevated administrator accounts simply because they were attaching a database deployed with their application. Sure, the same rules apply to the application itself as with other applicationsthey'll still have to be installed by the administrator.

Does this make sense for ASP.NET applications? Hardly. Remember, all ASP.NET applications run under the same process account by default; all of these applications would attach their local databases to the same shared instance of SQL Express. This also means the assurance that your data is safe because you're the only one using it is no longer possible. And no, you can't create a User Instance using any other version of SQL Serverit's a SQL Server Express feature.

Setting the Server Name Keyword

One of the challenges you'll face if you're unfamiliar with SQL Server instance addressing is how to code the "Server" ("Data Source" or "Address") ConnectionString keyword value. Consider that there are a number of ways to point to the SQL Server you wantjust remember to include the instance name to address. Yes, you can install SQL Server as the "default" instance, which is how preSQL Server 2000 installations were installed (there was only one instance). As discussed in Chapter 2, SQL Server 2000 introduced the concept of multiple instances, so when you install SQL Server 2000 on a system with an existing SQL Server 7.0, the 7.0 instance became the "default" instance (so existing code would not break). While it's possible to install SQL Server 2000 or 2005 as the default instance, it's not a particularly good ideaI recommend that you give each instance a unique name. Peter is a bit more flexible in this regard. He suggests that there are situations where installing default instances make sense. I'm not fully convinced, but he has more customers than I do.

To help visualize the addressing requirements, take a look at Figure 9.17. Here, I show two servers: machine "George" with a single "default" instance of SQL Server installed, and machine "Fred" with two SQL Server instances installed: "Test" and "Production". All of these machines are visible on the "Acme" domain. When creating a ConnectionString that must address machines across domains, include the domain name in the address you use in the "Server" keyword value.

Figure 9.17. Addressing servers and instances using the Data Source ConnectionString keyword.

Let's review some sample Server/Data Source keyword value settings:

• "<server name>" addresses the default SQL Server instance on the specified machine.

• "." or "(local)" addresses the default SQL Server instance on the local machine.

• ".\<instance>", as in ".\SS2K5", refers to the named SQL Server instance on the local machine.

• "<domain>\<server>" addresses the default SQL Server instance on the named machine in a specific domain.

• "<server name>\<instance>" addresses the named SQL Server instance on the specified current domain machine, as in "Fred\Test".

• "<domain>\<server>\<instance>" addresses the named SQL Server instance on the named machine in the named domain, as in "Acme\Fred\Production".

• "<IP>,<Port>" as in "209.20.233.22,1433" addresses default SQL Server instance on a machine-visible specific IP/Port.

Yes, you can also use the other aliases for the "Server" keyword (there are several), but I recommend use of the "Data Source" keyword and "DataSource" SqlConnectionStringBuilder property.

Establishing an Alias to Your Instance

All of the example applications included on the book's DVD reference the target SQL Server via an Alias of "DemoServer". An Alias permits you to address any named instance of SQL Server in the ConnectionString by using another namethus, the term "Alias". Creating an alias is very simplejust follow along:

1.	Start Microsoft SQL Server 2005 | Configuration Tools | SQL Server Configuration Manager. This MMC console application was discussed in detail in Chapter 2. It's used to manage the installed instances of SQL Server 2005, as well as establish aliases to any SQL Server instance.
2.	Click the "+" sign next to "SQL Server Native Client Configuration," as shown in Figure 9.18, to expose the Aliases tree. Right-click on Aliases to expose the operations menu and choose "New Alias..." to create a new Alias. Figure 9.18. Opening the Aliases tree in SQL Server Configuration Manager.
3.	This opens the dialog shown in Figure 9.19, which prompts you for the Alias Name (in this case, set this to "DemoServer"; the Port No, which I left blank to use an automatically generated port number; the Protocol, which defaults to TCP/IP (which you can set as requiredjust make sure the SQL Server has the selected protocol enabled); and, finally, the Server entry. The Server entry requires that you name the server as you would in the ConnectionString. This can refer to a "local" instance of SQL Server or a remote server hosted on another system on your network. Figure 9.19. Configuring a new Alias. When you click "OK" in this dialog, the Alias is registered with the local system, so any references to the alias are translated to access the "Server" entry.

Revisiting Other Keywords

Let's take a closer look at some of the ConnectionString keywords to get a better understanding of how they interact with each other and tune how ADO.NET manages the Connection.

• Connection Timeout: Generally, if things are working normally, SQL Server should be able to establish a connection in a couple of seconds or less. Of course, other factors can add time to that delay. For example, the first time you connect, it can take extra time to initialize the DLLS, create the pool, register your new connection in the pool, establish the connection, get the server-side agents started, and return status to your application. If SQL Server does not respond immediately to the data provider with a new connection handle (but does indicate that it's alive), the provider starts to wait Connection Timeout seconds. Setting the Connection Timeout does not always work as expected. When the network interface card (NIC) cannot see the Ethernet (as when the cable is disconnected or piece of the network infrastructure has melted down after that last lightening strike), the NIC timeout can add a great deal of time to the total time you'll wait before a timeout. Connection timeout exceptions are also very common when working with the connection pool, so I'll take up the discussion of that reason for timeout exceptions in a few pages.

• Failover Partner: This keyword tells ADO.NET that, should the connection fail to open, it should try to connect to an explicitly named (failover) SQL Server setup to mirror the primary server. Of course, this means you'll need to have your DBA setup the mirrored failover SQL Server. Yes, it's possible to set up a failover partner SQL Server that's automatically referenced when the primary server fails. The Connection.DataSource property always reflects which server is actually in useassuming your connection is open.

• Initial Catalog: This keyword names the "default" database used by T-SQL to resolve unspecific database object names. For example, if you code "SELECT Author FROM Pubs.dbo.Authors", the default database is not needed (it's stated explicitly), but if you coded "SELECT Author FROM dbo.Authors", the default database is assumed by the query compiler. The default database (or initial catalog) might point to "Biblio" or some other database, so the query might (very well) work differently. The default database is one of the few settings that can be changed after you open the connection by using the Connection.ChangeDatabase method. However, since the connection is reset each time it's opened (unless you disable this feature), the default database is changed back to its original setting when it's reopened. No, it's not a good idea to try to manage connection state (like current database) with the TSQL USE operatoralthough you can. That's because ADO.NET can't keep track of those settings.

  If you don't include the "Initial Catalog" or "Database" keyword, SQL Server uses the default database associated with the Login account named in the security credentials you supply. Of course, if the DBA changes this default value at some later time, applications that worked at one point in time might very well fail once the change is made. It's always a good idea to specify a default database (or Initial Catalog) keyword/value in your ConnectionString.

• Integrated Security (or Trusted_Connection): This keyword tells ADO.NET to use the Windows login credentials used to launch your application as SQL Server credentials. That is, if a user logs into Windows as "Fred" in the "Acme" domain and runs an ADO.NET application that uses Integrated Security=SSPI^[12], the application needs a Login account on SQL Server for Acme\Fred with rights to the default database. Yes, this is a lot of trouble, so you'll probably want to create a SQL Server Login that maps to a domain group that includes those individuals who need SQL Server access. As new people are added to the organization, you must remember to add them to the domain group so that they can get access to the data. Of course, as people leave, you need to remove them from the domain group.

  ^[12] Or Trusted_Connection=Yes, but Integrated Security=SSPI is preferred.

  Yes, if you're a member of the Administrators group, you're automatically granted rights to log on to SQL Server with SA (administrator) rights. No, don't assume that everyone logging on is a member of the Administrators groupexcept when using User Instances with SQL Server Express.

  In an ASP.NET application, IIS "owns" the connection identity, so when you use Integrated Security, ADO.NET uses the ASPNET (IIS 5.x) or IIS_WPG (IIS 6.x) Windows account (created when IIS is installed). In this case, you'll need to grant rights to the appropriate IIS accountgiving it permission to log on to SQL Server and grant appropriate read/read-write access to the initial catalog database.

• MultipleActiveResultSets (MARS): This keyword tells ADO.NET to permit up to nine simultaneous SqlDataReader objects to share the same connection. That seems simple enough, but there are a number of restrictions that you should be aware of before arbitrarily enabling this feature. Note that "MARS" works only with SQL Server 2005 (or later) instances and has a number of other restrictions and side effects. See "Understanding MARS and its Implications," in this chapter.

• Persist Security Info: If you need to prevent your code (or code you call) from seeing the security credentials in your ConnectionString, you need to set the "Persist Security Info" keyword value to true. Once set, you can fetch the ConnectionString from the SqlConnection object, but the security settings are stripped off the resulting string.

• User ID and Password: If you don't set the "Integrated Security" keyword to SSPI or True, you'll need to provide a valid SQL Server Login ID and Password to gain access to a SQL Server instanceassuming (and this is a big assumption) that the instance has been configured to support mixed-mode security. This Login account must be granted rights to the default database, as well for the connection to be opened. By default, SQL Server is configured to accept (only) SSPI (and just SSPI) security credentials.

Configuring the Connection Pool Keyword Values

The next set of keywords, as shown in Table 9.2, is used to set up and manage the connection pool. Note that there is only one change in these arguments since ADO.NET 1.1: the "Connection Lifetime" keyword has a new alias, "Load Balance Timeout" (that Microsoft wants developers to use), that does the same thingwhich is nothing for ordinary (non-clustered) connection pools. These settings have corresponding properties in the SqlConnectionStringBuilder, as shown in (parenthesis).

Table 9.2. Connection Pool Keywords

Keyword	Default	Description
Connection Lifetime Load Balance Timeout (LoadBalanceTimeout)	0	(Clustered SQL Servers only.) Sets the number of minutes a closed connection remains in the pool. Has no effect on non-clustered connection pools. 0 = use default timeout (48 minutes).
Connection Reset (ConnectionReset)	true	When true, the pooler resets the live connection to its initial state when the connection is reopened (see discussion).
Enlist (Enlist)	true	When true, the pooler automatically enlists the connection in the creation thread's current transaction context. Since this incurs more overhead, I suggest setting this to false except when you're managing client-side transactions. See the BeginTransaction method.
Max Pool Size (MaxPoolSize)	100	The maximum number of connections allowed in a pool (see discussion).
Min Pool Size (MinPoolSize)	0	The minimum number of connections maintained in the pool (see discussion).
Pooling (Pooling)	True	Enables or disables connection pooling.

Don't place unvalidated strings into the ConnectionString (or any SQL), as this can lead to SQL injection attacks.