Defining a Database Connection

As a general practice, I employ a style of programming that relies heavily on a technique referred to as scaffolding and commonly understood to mean a way of unit testing. This technique simply means that a little code is written that performs a complete job, and some test code is written to ensure the code works. As the body of code grows, existing code is regressively tested as each new chunk is added and tested . The promoted means of testing code is to use NUnit. The result of using NUnit or scaffolding is approximately the same except that using NUnit will result in your being able to use existing testing tools that will run your test suite automatically, accumulating and reporting on results.


NUnit (see for details) is modeled after JUnit, both of which are testing frameworks. The basic principle is that you implement a predetermined interface. Your test code implements this predetermined interface, and a test application can exercise your code by tapping into this interface. The test application uses the interface to test your code, accumulate results, and report on these results. The NUnit tools are getting better and becoming more popular all the time. It is worth doing some independent investigation on NUnit.

In accordance with the idea of building software that conceptually looks like an onion, where layers of test code are wrapped around successively growing complexity, I typically start with a connection to my database. (I start with the connection when building the database layer and start with other kinds of entities when building a graphical user interface or business object layer.) A connection is comprised of the OleDbConnection (or SqlConnection ) class, a connection string, and a database. The connection can be dragged from the Toolbox or Server Explorer or created programmatically with code. The Server Explorer version is easiest because it manages the connection string automatically; however, below we will create the connection programmatically because this provides us with the greatest flexibility.

Defining a Connection String

Connection strings have the distinct honor of being difficult to remember. The basic syntax is to provide a provider name, a data source, a password, and a user name . Provider names tend to be cryptic and relatively easy to forget. The data source name is often a physical file path or a reference to an OleDb alias. The password and user name are self-explanatory.

Due in part to the cryptic provider names, we can borrow a foolproof means of defining a connection string. The technique involves using the Data Link Properties applet to create the connection string on our behalf . To define a connection string to the Northwind sample database, follow these steps.

  1. In Windows Explorer, create a new text file form the FileNewText Document menu. The default name will be New Text Document.txt . Rename this file to have a meaningful name and a .udl extension, for example, connection.udl .

  2. The .udl extension is associated with a Microsoft Data Link file. Double-click this file to open the Data Link Properties applet (Figure 11.1).

    Figure 11.1. Using the Data Link Properties applet to manage connections.


  3. Select the provider you want to use (in our example, we'll use the Microsoft Jet 4.0 OLE DB Provider) and click the Next button (Figure 11.1). The Data Link Properties dialog behaves like a wizard.

  4. On the Connection tab, browse to the database location using the Browse button with the ellipsis. Enter a user name and password and click Allow saving password. Click Test Connection to ensure that your settings will work. (A default user name for the Northwind.mdb sample database is Admin; no password is required.)

  5. You can modify values on the Advanced and All tabs, but we don't need to do that for our example. (You will benefit from exploring these independently; keep in mind that the specific values are provider dependent)

  6. Assuming the test connection succeeded, click the OK button. This step will write the values you indicated as a connection string to the .udl file

The .udl file is just text. We can open it directly in VS .NET and extricate the connection string from the file. This technique has worked reliably regardless of the OLE DB provider used, so I keep it on hand.

Storing a Connection String

To store a connection string, you can copy the last line of text in the .udl file following the comment "Everything after this line is an OLE DB initstring." A .config file is a good place to store the connection string information. (You can store the connection string in an App.config file or a Web.config file for Web applications.) Storing the connection string information externally ensures that every chunk of code that uses the connection string will use it consistently.

Information like connection strings can be stored as key and value pairs in the <appSettings> section of a .config file. For simplicity you can use the key "ConnectionString" , and the value will be the literal connection string. Listing 11.1 shows an excerpt of the Web.config file for ADODOTNETDemo.sln , with the <appSettings> element defined properly.

Listing 11.1 Using <appSettings> to Define a ConnectionString Key and Value
[View full width]
 <?xml version="1.0" encoding="utf-8" ?> <configuration>   <appSettings>     <add key="ConnectionString"  value="Provider=Microsoft.Jet.OLEDB.4.0;Password="";Data Source=C:\Program Files\ graphics/ccc.gif Microsoft Visual Studio\VB98\NWIND.MDB;Persist   Security Info=True" />  </appSettings>   <system.web>     <!--  DYNAMIC DEBUG COMPILATION 

The <appSettings> values are incorporated with the <add> tag, and the value must be on a single line of text. (It is shown wrapped in Listing 11.1see the bold textdue to the limitations of the printed page.) The remainder of the Web.config file (at least for our example) contains default information created when the Web application solution was created by Visual Studio .NET. You can open a Web.config file and explore this information.


Ensure that you remove embedded strings in the connection string. For example, the Data Link Properties applet uses double quotes ("") to indicate an empty password; simply remove the double quotes to indicate the password is blank. You will get an error indicating that the debugger couldn't start on the Web server and asking whether or not you want to disable debugging. Answer No to this prompt.

If you accidentally enter Yes in response to the error caused by the invalid Web.config file, you can reenable ASP.NET debugging on the Debugging page of the project's Property Pages (Figure 11.2).

Figure 11.2. You can reenable ASP.NET debugging from the Debugging Property Page if it becomes accidentally disabled.


Reading a Connection String

Values added to the <appSettings> element can be read using the shared, indexable property System.Configuration.ConfigurationSettings. AppSettings by key. For example, we could read ConnectionString with the statement System.Configuration.ConfigurationSettings.AppSettings("ConnectionString") . Rather than litter our application with this statement, we could opt to create a Connection class with a ConnectionString property that concealed the AppSettings statement, making the code simpler for everyone to use. Defining our own Connection class also provides us with a convenient location to write our test code.

Testing the Connection

A convenient way to encapsulate solutions is to define your own classes. You can build on existing abstractions to create a contextual abstraction unique to your solution. This contextual abstraction will provide you with a convenient location to layer in things you need like connection strings and test code. Such a class to test our connection string might be implemented as demonstrated in Listing 11.2.

Listing 11.2 Creating a Contextual Wrapper for a Connection String
 Imports System.Configuration Imports System.Data.OleDb Imports System.Diagnostics Public Class Database   Private Shared Function GetConnectionString() As String     Return ConfigurationSettings.AppSettings("ConnectionString")   End Function   Public Shared ReadOnly Property ConnectionString() As String   Get     Return GetConnectionString()   End Get   End Property   Public Shared Sub Test()     Dim C As OleDbConnection = New OleDbConnection(ConnectionString)     Try       C.Open()       Debug.WriteLine(C.State)     Catch       Debug.WriteLine("Connection failed to open")     Finally       C.Close()     End Try   End Sub End Class 

The Database class provides a wrapper for the connection string and a convenient place for some test code.

Note that the code in Listing 11.2 represents my personal convention. It is entirely up to you whether you employ this strategy or not; however, Listing 11.2 does demonstrate the basic operations necessary for using an OleDbConnection object. You must have a connection string and an OleDbConnection object initialized with this string, and it is helpful to test each of these elements separately as you begin building the database part of your solution.

Finally, it is worth noting that with the notion of connection pooling, .NET optimizes the number of physical connections created. When we are using OleDbConnection objects, pooling is managed automatically; SqlConnection objects handle connection pooling implicitly. A key aspect of how connections are pooled is the connection string. An identical connection string is central to determining whether a connection in the pool can satisfy the request for a new connection. For this reason using a strategy for ensuring that connection strings don't unnecessarily diverge works advantageously.

Visual Basic. NET Power Coding
Visual Basic(R) .NET Power Coding
ISBN: 0672324075
EAN: 2147483647
Year: 2005
Pages: 215
Authors: Paul Kimmel © 2008-2017.
If you may any questions please contact us: