Chapter 2 explained how to create a DbConnection object and communicate to the data store as quickly and easily as possible. This chapter explores some of the more advanced connectivity options and best practices by looking at connection pooling, clustered connections, and asynchronous access. But first we examine the ConnectionStringBuilder object, which we use throughout this chapter when implementing these advanced options.
Building Accurate Connection Strings
You'll often want to prompt the user for information that will be used to build a connection string. For example, when the user starts your application for the first time or before you grant the user the ability to export data from one database server to another, you might want to prompt the user for a SQL Server computer name, the database name, a user name, and password. How can you possibly ensure that the user has typed everything correctly? Also, how can you prevent a user from breaching security by injecting other settings into that information?
After a user enters information into your application, you normally build a connection string using code that looks something like the following snippet (which builds a connection string based on the data source name that is typed into the txtDataSource text box).
Visual Basic
Public Function GetConnectionString() As String Return String.Format( _ "AttachDbFilename=|DataDirectory|PUBS.MDF;" _ & "integrated security=true;User Instance=true;" _ & "Data Source={0};", _ txtDataSource.Text) End Function
One of the biggest problems with this code is that the user can insert a keyword separator (the semicolon) into the password field and then type additional keywords and values. For example, if the user types ".\SQLEXPRESS;Database=Joe", the database file that is being attached is mounted as Joe on the server while the application is running; if the user types ".\SQLEXPRESS;Database=Joe;User Instance=false;", the database file is permanently mounted on the database server as Joe. We can easily create an example of this by placing a button and a text box on a Windows form and adding the following code to the button click event handler.
Visual Basic
Private Sub btnConnectionStringTest_Click( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnConnectionStringTest.Click MessageBox.Show(GetConnectionString()) Dim cn As SqlConnection = New SqlConnection(GetConnectionString()) Try cn.Open() Dim cmd As SqlCommand = New SqlCommand("Select DB_NAME()", cn) MessageBox.Show(cmd.ExecuteScalar().ToString()) Catch xcp As Exception MessageBox.Show(xcp.Message) Finally cn.Close() End Try End Sub
In this example, if the user types ".\SQLEXPRESS" in the text box and clicks the button, a message box showing the resulting connection string is displayed. The button can be clicked several times without any problem. If the user types ".\SQLEXPRESS;Database=Joe;User Instance=false;" in the text box and clicks the button, the pubs database file is mounted as a permanent database called Joe. If the user types ".\SQLEXPRESS" in the text box and clicks the button again, an exception is thrown that indicates that the pubs database is already in use.
I always try to set up Microsoft SQL Server to use integrated Windows authentication. If you set up SQL Server to use SQL Server authentication, a user can potentially bypass a user name and password in the connection string by adding ";Integrated Security=true". If this succeeds, the user will have the permissions that the process login has.
Another problem with this approach to building the connection string is that there is no checking of this string. For example, if a user incorrectly types one of the keywords, he won't realize it until it's time to open the connection. For example, if he types "Integrated_Security=true" in the connection string, he won't find out that the underscore should be a space until the code attempts to open the connection.
The solution to these problems is to use a ConnectionStringBuilder object. The ConnectionStringBuilder properly adds quotation marks around each value to keep someone from typing the semicolon command separator and adding new keywords and values. The ConnectionStringBuilder has a constructor that accepts a connection string through which you can initialize the ConnectionStringBuilder object. After the object has been initialized, the properties are available for reading and writing. The following code shows the use of the SqlConnectionStringBuilder object.
Visual Basic
Private Sub button1__Click( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles btnConnectionStringBuilderTest.Click Dim bld as SqlConnectionStringBuilder = new SqlConnectionStringBuilder( _ "AttachDbFilename=|DataDirectory|PUBS.MDF;" _ + "integrated security=true;User Instance=true;") bld.DataSource = txtDataSource.Text MessageBox.Show(bld.ConnectionString) Dim cn as SqlConnection = new SqlConnection(bld.ConnectionString) try cn.Open() Dim cmd as SqlCommand = new SqlCommand("Select DB_NAME()", cn) MessageBox.Show(cmd.ExecuteScalar().ToString()) catch xcp as Exception MessageBox.Show(xcp.Message) finally cn.Close() end try End Sub
In our example, if the user types ".\SQLEXPRESS;Database=Joe;User Instance=false;" in the text box, here is the resulting connection string:
Data Source=".\SQLEXPRESS;Database=Joe;User Instance=false;" ;AttachDbFilename=|DataDirectory|PUBS.MDF;Integrated Security=True; User Instance=True
Notice the quotation marks around ".\SQLEXPRESS;Database=Joe;User Instance=false;". They indicate that the process uses this string as the DataSource when locating the server. The end result is that this code generates a timeout exception because the process cannot find a server named ".\SQLEXPRESS;Database=Joe;User Instance=false;".
The ConnectionStringBuilder also validates connection string information for proper key names. If the connection string contains an invalid key, such as abc=def, an ArgumentException is thrown. The ConnectionStringBuilder also contains an Add method, which you can use to pass key and value pairs as needed. The Add method also validates the keys that are added.
Provider-Independent Data Access
The ConnectionStringBuilder also plays an important role in the creation of database platform-independent code. For example, if you save a proper connection string in the application's configuration file, you can create and validate a provider-specific ConnectionStringBuilder object at run time. Notice that even though the following code example does not use any provider-specific classes, the provider-specific connection string is validated by the DbConnectionStringBuilder when pubs.ConnectionString is assigned to bld.ConnectionString.
Private Sub btnGenericDatabaseAccess_Click( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles btnGenericDatabaseAccess.Click Dim pubs as ConnectionStringSettings = _ ConfigurationManager.ConnectionStrings("PubsData") Dim factory as DbProviderFactory = _ DbProviderFactories.GetFactory(pubs.ProviderName) Dim bld as DbConnectionStringBuilder = _ factory.CreateConnectionStringBuilder() bld.ConnectionString=pubs.ConnectionString Dim cn as DbConnection = factory.CreateConnection() cn.ConnectionString = bld.ConnectionString Dim da as DbDataAdapter = factory.CreateDataAdapter() Dim cmd as DbCommand = factory.CreateCommand() cmd.CommandText= "Select * from authors" cmd.CommandType = CommandType.Text cmd.Connection = cn da.SelectCommand = cmd Dim cmdBld as DbCommandBuilder = factory.CreateCommandBuilder() cmdBld.DataAdapter = da Dim ds as DataSet = new DataSet() da.Fill(ds, "authors") dataGridView1.DataSource = ds dataGridView1.DataMember = "authors" End Sub
