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