- Using Application Blocks for Data Access
- Using the DAAB
- Retrieving a DataSet
- Using the SqlDataReader to Populate Business Objects
- Complementing the DAAB
Using the SqlDataReader to Populate Business Objects
The SqlDataReader is a read-only result set. Its one-way nature makes it an excellent choice for populating objects. In Listing 4, I simulate a lightweight business object named Customer and a strongly typed collection of Customer objects named CustomerList. The SqlDataReader populates and returns an instance of the CustomerList.
Listing 4: Using a SqlDataReader and the DAAB to populate business objects.
using System;
using System.Collections;
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 Database(){}
public class CustomerList : CollectionBase
{
public Customer this[int index]
{
get{ return (Customer)List[index]; }
set{ List[index] = value; }
}
public int Add(Customer value)
{
return List.Add(value);
}
}
public class Customer
{
private string m_name = string.Empty;
public Customer(string name)
{
m_name = name;
}
public string Name
{
get{ return m_name; }
set{ m_name = value; }
}
}
public static CustomerList DataReaderTest()
{
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
try
{
CustomerList results = new CustomerList();
SqlDataReader reader = SqlHelper.ExecuteReader(connection,
CommandType.Text, "SELECT * FROM Customers");
try
{
while(reader.Read())
{
results.Add(new Customer(reader.GetString(1)));
}
return results;
}
finally
{
reader.Close();
}
}
finally
{
connection.Close();
}
}
}
}
The method DataReaderTest opens a connection object and uses the DAAB's SqlHelper.ExecuteReader method to return a SqlDataReader. The reader populates an instance of the CustomerList class.
Suppose we wanted to show a master detail relationship between Customers and Orders using business objects. That would entail revising our strongly typed collection of Customers so that it contains a list of customer Orders. We could use very similar code to read and initialize both sets of objects, mapping relationships programmatically as the objects are constructed.
To build a more complex relationship, of course, we could always use a DataSet, multiple tables, and DataRelations to describe the table mappings. Using DataSets is initially easier, but, as you'll see in Listing 5, using non-DataSet objects can be more powerful.
Listing 5: Implementing relationships using strongly typed collections of business objects, the DAAB, and a DataReader.
using System;
using System.Collections;
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 Database(){}
public class CustomerList : CollectionBase
{
public Customer this[int index]
{
get{ return (Customer)List[index]; }
set{ List[index] = value; }
}
public Customer this[string id]
{
get{ return (Customer)this[IndexOf(id)]; }
set{ this[IndexOf(id)] = value; }
}
public int Add(Customer value)
{
return List.Add(value);
}
public bool Contains(string id)
{
return IndexOf(id) > -1;
}
public int IndexOf(string id)
{
for( int i = 0; i< this.Count; i++ )
if( this[i].Id == id )
return i;
return -1;
}
public int AddCustomerOrder(string id, Order order)
{
if( Contains(id))
return this[id].AddOrder(order);
else
return -1;
}
}
public class Customer
{
private string m_id = string.Empty;
private string m_name = string.Empty;
private OrderList orders = null;
public Customer(string id, string name)
{
m_id = id;
m_name = name;
orders = new OrderList();
}
public string Id
{
get{ return m_id; }
set{ m_id = value; }
}
public string Name
{
get{ return m_name; }
set{ m_name = value; }
}
public int AddOrder(Order value)
{
return orders.Add(value);
}
public OrderList Orders
{
get{ return orders; }
}
}
public class OrderList : CollectionBase
{
public Order this[int index]
{
get{ return (Order)List[index]; }
set{ List[index] = value; }
}
public int Add(Order value)
{
return List.Add(value);
}
}
public class Order
{
private int m_orderId;
private DateTime m_orderDate = DateTime.MinValue;
public Order(int orderId, DateTime orderDate)
{
m_orderId = orderId;
m_orderDate = orderDate;
}
public int OrderId
{
get{ return m_orderId; }
set{ m_orderId = value; }
}
public DateTime OrderDate
{
get{ return m_orderDate; }
set{ m_orderDate = value; }
}
}
public static CustomerList DataReaderTest2()
{
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
try
{
CustomerList results = new CustomerList();
SqlDataReader reader = SqlHelper.ExecuteReader(connection,
CommandType.Text,"SELECT CustomerId, CompanyName FROM Customers;" +
"SELECT OrderId, CustomerId, OrderDate FROM Orders");
try
{
while(reader.Read())
{
results.Add(new Customer(reader.GetString(0), reader.GetString(1)));
}
if( reader.NextResult())
while(reader.Read())
{
results.AddCustomerOrder(reader.GetString(1),
new Order(reader.GetInt32(0), reader.GetDateTime(2)));
}
return results;
}
finally
{
reader.Close();
}
}
finally
{
connection.Close();
}
}
}
}
Our time together and space is somewhat limited, so you will have to go through most of the new code yourself. Here are some highlights (shown in bold).
-
The CustomerList class has some additional methods: Contains, IndexOf, and AddCustomerOrder. These convenience methods make it easier to find a specific customer and add an order at the CustomerList level.
-
The Customer class now contains a read-only OrderList property named Orders, reflecting our master-detail relationship.
-
The DAAB is sent two queries together to return two result sets. This is similar to having two queries in a stored procedure, but not the same thing as using a JOIN. In effect, we are establishing an equivalent of a JOIN by organizing the orders with the correct customers in code.
-
We use SqlDataReader.NextResult to check for the presence of an additional result set. Since we wrote the SQL, we know there should be something to look for. We repeat the while…loop to read all of the Orders.
As you can see from these very lean objects, we have a lot of work to do in a business application without writing our own data access code. The DAAB does that for us.
More on the DAAB
The DAAB contains overloaded methods for using transactions, executing non-queries (such as DELETE statements), and SqlCommand objects. These easily permit protecting multi-table updates with transactions or running stored procedures. I have used the DAAB on pretty complex applications without having to write many additional low-level data access methods.