Using the DAAB
The DAAB is comprised of two public classes: SqlHelper and SqlHelperParameterCache. These classes are in the Microsoft.ApplicationBlocks.Data namespace. For the most part, you use the helper functions in the SqlHelper class. In the sub-sections that follow, I'll demonstrate how to perform common operations on a SQL Server database using the DAAB.
Connecting to SQL Server
For the most part, the DAAB has methods for returning SqlDataReader, SqlCommand, and DataSet objects and returning scalar values from queries sent to the database server. A connection and optionally a transaction object (if you want to use transactions) are initialized and sent to the DAAB methods as parameters.
Connecting to a SQL Server database requires the System.Data and System.Data.SqlClient namespaces, an instance of the SqlConnection class, and a connection string. There are several techniques for defining a connection string, including using the OLEDB32.dll Data Link Properties editor, and copying the connections string from the .udl file, or copying the connection string from the properties window.
To create a connection string using the Data Link Properties editor follow these steps:
- Open Windows Explorer.
- Select File|New|Text Document in Windows Explorer.
- Rename the text file something like connection.udl.
- Double-click on the .udl file. The Data Link Properties editor (see Figure 1) is associated with .udl text files and will run, opening the .udl file.
- Follow the .udl file wizard, and click OK when finished.
- Next, open the .udl file and copy the text on the line beginning with Provider, for example,
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False; Initial Catalog=Northwind;Data Source=sci
)
Figure
1. The OLEDB32.dll Data Link Properties applet can create OLEDB connection
strings.
The Data Link Properties editor is for OLEDB providers. However, because we are using the SQL provider, we do not need the Provider clause. In our example connection string to the Northwind database, the revised connection string, without the Provider clause, is Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=sci. (No user and password is needed in this string, as I am using integrated Windows security.)
To obtain a connection string in VS.NET, follow these steps:
- In Visual Studio .NET, select View|Server Explorer.
- Expand the Data Connections tab (see figure 2).
- Drag and drop a connection from the Server Explorer onto a convenient form.
- This adds a connection component to the component window. The connection component's ConnectionString (see figure 3) property contains a usable connection string.
)
Figure
2: Drag a data connection from the server explorer onto a form;
the connection string is a property of the SqlConnection control created by
the IDE.
)
Figure
3: The SqlConnection.ConnectionString property contains
a valid connection string, too.
The connection we created in the second example can be used as a valid connection for any programming task. The one drawback is that you need a UserControl, Web Page, or Windows Form onto which to drag and drop a connection component; many experienced programmers simply write the extra line or two of code to create the connection programmatically.
Another important reason experienced programmers don't use connection components is that connection strings contain information, such as user names and passwords, that you may not want accessible to anyone with a copy of the decompiler's Anakrino or Reflector. To prevent this information from being accessible to customers, connection strings are often encrypted and stored in application and Web .config files. Storing the connection string separately negates almost any value of a connection component.
Listing 1 demonstrates how easy it is to create a connection to a database programmatically.
Listing 1: Connection to a SQL Server database.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
namespace DaabDemo
{
public class Database
{
private readonly static string connectionString =
"Integrated Security=SSPI;Persist Security Info=False;" +
"Initial Catalog=Northwind;Data Source=sci";
public Database(){}
public static void ConnectionTest()
{
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
try
{
Debug.WriteLine(connection.State);
}
finally
{
connection.Close();
}
}
}
}
The code of interest is in bold case font. We need a connection string, an instance of the SqlConnection class initialized with the connection string, and we need to open, use, and close the connection. In the example, we use a resource protection block—the try...finally—construct to ensure the connection is closed. (ADO.NET uses a disconnected model, so using the connection means we perform our query—like retrieving a DataSet—and then close the connection. We do not need to and should not maintain an open connection in ADO.NET to use database data.)
Using the connection, in this example, consists of writing the connection state to the Debug Output window in VS.NET. In an ordinary, application, it is more likely that you want to perform a query, return a result set or update the database. However, the code in the example would be a useful NUnit test.