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.

Retrieving a DataSet

Listing 2: Retrieving a DataSet.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using Microsoft.ApplicationBlocks.Data;

namespace DaabDemo
{
  public class Database
  {
    private readonly static string connectionString = 
      "Integrated Security=SSPI;Persist Security Info=False;" + 
      "Initial Catalog=Northwind;Data Source=sci";


    public static DataSet DataSetTest()
    {
      SqlConnection connection = new SqlConnection(connectionString);
      connection.Open();
      try
      {
        return SqlHelper.ExecuteDataset(connection, 
          CommandType.Text, "SELECT * FROM Customers");
      }
      finally
      {
        connection.Close();
      }
    }

  }
}

As you can see from the listing, I only really added two lines of code: a referenced to the Microsoft.ApplicationBlocks.Data namespace and a call to the static method SqlHelper.ExecuteDataSet. That's all there is to it. Easy, right? Well, easy and reliable is what you want.

There are a couple points of interest here. The first is that the DAAB supports 9 overloaded versions of ExecuteDataset. The second is that the code is correct as-is. Many programmers will declare a temporary variable before the try block, assign the return value to the temporary, and return the temporary after the finally block, as shown incorrectly in listing 3.

The bold lines of code are just extra. Writing code like this demonstrates an imporant lapse in understanding how the try...finally construct works. The finally block is always executed. Let me repeat this: the finally block is always executed, even if there is a return statement in the try block.

Listing 3: Unnecessary, wasteful extra lines of code.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using Microsoft.ApplicationBlocks.Data;

namespace DaabDemo
{
  public class Database
  {
    private readonly static string connectionString = 
      "Integrated Security=SSPI;Persist Security Info=False;" + 
      "Initial Catalog=Northwind;Data Source=sci";


    public static DataSet DataSetTest()
    {
      DataSet data = null;
      SqlConnection connection = new SqlConnection(connectionString);
      connection.Open();
      try
      {
        // use return instead
        data = SqlHelper.ExecuteDataset(connection, 
          CommandType.Text, "SELECT * FROM Customers");
      }
      finally
      {
        connection.Close();
      }
      
      return data;
    }

  }
}

Next: ' Populating Business Objects ' >>




 
Discuss Using Application Blocks for Data Access
 
 
  
 
>>> Be the FIRST to comment on this article!
 
  
 



 
 
 
 
  
 
>>> More Using Microsoft Visual Studio Articles          >>> More By Paul Kimmel