Retrieving a DataSet
To return a result set, you can insert a call to the DAAB in between opening and closing the SQL Server connection. In the second example (see Listing 2), the work is a returned DataSet containing all of the Northwind Customers data.
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;
}
}
}
Not really understanding exception handling is a huge oversight. During the course of development, saving a few extra lines per method and using exception handlers correctly means that the programmer writing code like listing 2 is writing code much faster and less error-prone than the programmer who generates listing 3. Programmer 2 is writing 20% fewer lines of database code in just this one example. Spread over months or years, we are talking about hundreds of hours saved in writing and testing lines of code.