Chapter 4 Advanced Connectivity to the Data Store

 

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).

image from book

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 
image from book

image from book

C#

public string GetConnectionString() {    return string.Format(       "AttachDbFilename=|DataDirectory|PUBS.MDF;"       + "integrated security=true;User Instance=true;"       + "Data Source={0};",       txtDataSource.Text); } 
image from book

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.

image from book

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 
image from book

image from book

C#

private void btnConnectionStringTest_Click(object sender, EventArgs e) {    MessageBox.Show(GetConnectionString());    SqlConnection cn = new SqlConnection(GetConnectionString());    try    {       cn.Open();       SqlCommand cmd = new SqlCommand("Select DB_NAME()",cn);       MessageBox.Show(cmd.ExecuteScalar().ToString());    }    catch (Exception xcp)    {       MessageBox.Show(xcp.Message);    }    finally    {       cn.Close();    } } 
image from book

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.

image from book

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 
image from book

image from book

C#

private void button1_Click(object sender, EventArgs e) {    SqlConnectionStringBuilder bld = new SqlConnectionStringBuilder(       "AttachDbFilename=|DataDirectory|PUBS.MDF;"       + "integrated security=true;User Instance=true;");    bld.DataSource = txtDataSource.Text;    MessageBox.Show(bld.ConnectionString);    SqlConnection cn = new SqlConnection(bld.ConnectionString);    try    {       cn.Open();       SqlCommand cmd = new SqlCommand("Select DB_NAME()", cn);       MessageBox.Show(cmd.ExecuteScalar().ToString());    }    catch (Exception xcp)    {       MessageBox.Show(xcp.Message);    }    finally    {       cn.Close();    } } 
image from book

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.

image from book

App.Config File

<?xml version="1.0" encoding="utf-8" ?> <configuration>    <connectionStrings>       <clear />       <add name="PubsData"          connectionString="Data Source=.\SQLEXPRESS;          AttachDbFilename=|DataDirectory|PUBS.MDF;          Integrated Security=True;          User Instance=True"          providerName="System.Data.SqlClient" />    </connectionStrings> </configuration> 
image from book

image from book

Visual Basic

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 
image from book

image from book

C#

private void btnGenericDatabaseAccess_Click(object sender, EventArgs e) {    ConnectionStringSettings pubs =       ConfigurationManager.ConnectionStrings["PubsData"];    DbProviderFactory factory =       DbProviderFactories.GetFactory(pubs.ProviderName);    DbConnectionStringBuilder bld =       factory.CreateConnectionStringBuilder();    bld.ConnectionString=pubs.ConnectionString;    DbConnection cn = factory.CreateConnection();    cn.ConnectionString = bld.ConnectionString;    DbDataAdapter da = factory.CreateDataAdapter();    DbCommand cmd =  factory.CreateCommand();    cmd.CommandText= "Select * from authors";    cmd.CommandType = CommandType.Text;    cmd.Connection = cn;    da.SelectCommand = cmd;    DbCommandBuilder cmdBld = factory.CreateCommandBuilder();    cmdBld.DataAdapter = da;    DataSet ds = new DataSet();    da.Fill(ds, "authors");    dataGridView1.DataSource = ds;    dataGridView1.DataMember = "authors"; } 
image from book

 


Programming Microsoft ADO. NET 2.0 Applications. Advanced Topics
Linux Application Development (2nd Edition)
ISBN: 735621411
EAN: 2147483647
Year: 2004
Pages: 85
Authors: Michael K. Johnson, Erik W. Troan
BUY ON AMAZON

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net