- Analyzing Design Requirements
- Choosing Our Properties
- The Constructors
- Create, Update, and Delete Methods
- Caching the Data for Better Performance
- Getting More than One Record at a Time
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Getting More than One Record at a Time
This model for a data class might be great for getting one record, but what happens when you need to get a group of records? Fortunately we can group these data objects together in an ArrayList, a structure that is often less complicated and less work for .NET to create and maintain than a DataTable.
Continuing with our example, let's say that we frequently need to look up groups of customers by their Zip code. We'll add a static (shared) method to the class that takes a single parameter, the Zip code, and searches the database for matching records. The method will return an ArrayList of Customer objects. The ArrayList class is in the System.Collections namespace, so we need to add a using statement (Imports in VB) to the top of our class file. Listing 5.11 shows our new method.
Example 5.11. The static method to get an ArrayList full of Customer objects
C#
public static ArrayList GetCustomersByZip(string Zip)
{
SqlConnection connection = new
SqlConnection("server=(local);database=test;Integrated
Security=SSPI");
connection.Open();
SqlCommand command = new SqlCommand("SELECT CustomerID, "
+ "LastName, FirstName, Address, City, State, Zip, Phone, "
+ "SignUpDate WHERE Zip = @Zip ORDER BY LastName, "
+ "FirstName ", connection);
command.Parameters.AddWithValue("@Zip", Zip);
SqlDataReader reader = command.ExecuteReader();
// create the ArrayList that the method will return
ArrayList objList = new ArrayList();
while (reader.Read())
{
// create a new customer object
Customer customer = new Customer();
// assign the database values to the object's properties
customer.CustomerID = reader.GetInt32(0);
customer.LastName = reader.GetString(1);
customer.FirstName = reader.GetString(2);
customer.Address = reader.GetString(3);
customer.City = reader.GetString(4);
customer.State = reader.GetString(5);
customer.Zip = reader.GetString(6);
customer.Phone = reader.GetString(7);
customer.SignUpDate = reader.GetDateTime(8);
// add the customer object to the ArrayList
}
objList.Add(customer);
reader.Close();
connection.Close();
// return the finished ArrayList with customer objects
return objList;
}
VB.NET
Public Shared Function GetCustomersByZip(Zip As String) As ArrayList
Dim connection As New _
SqlConnection("server=(local);database=test;Integrated Security=SSPI")
connection.Open()
Dim command As New SqlCommand("SELECT CustomerID, LastName, "_
& "FirstName, Address, City, State, Zip, Phone, SignUpDate "_
& "WHERE Zip = @Zip ORDER BY LastName, FirstName", connection)
command.Parameters.AddWithValue("@Zip", Zip)
Dim reader As SqlDataReader = command.ExecuteReader()
' create the ArrayList that the method will return
Dim objList As New ArrayList()
While reader.Read()
' create a new customer object
Dim customer As New Customer()
' assign the database values to the object's properties
customer.CustomerID = reader.GetInt32(0)
customer.LastName = reader.GetString(1)
customer.FirstName = reader.GetString(2)
customer.Address = reader.GetString(3)
customer.City = reader.GetString(4)
customer.State = reader.GetString(5)
customer.Zip = reader.GetString(6)
customer.Phone = reader.GetString(7)
customer.SignUpDate = reader.GetDateTime(8)
' add the customer object to the ArrayList
objList.Add(customer)
End While
reader.Close()
connection.Close()
' return the finished ArrayList with customer objects
Return objList
End Function
At first glance, this new method looks a lot like our constructor. The first difference is that we're using a static method that returns an ArrayList object populated with Customer objects by looping through more than one record of data. We can call static methods without instantiating the class. To look up customers in the 44114 Zip code, for example, we'd need only one line:
ArrayList objList44114 = Customer.GetCustomersByZip("44114");
The next difference is in our SQL statement. This time we're looking for records where the Zip is matched to the parameter we've passed in. Because the Zip column of the database is not our primary key and may not be unique, we may get several records.
Just after we execute the SqlDataReader, we create an ArrayList object. This will be the container for our Customer objects. Using a while loop, we go through each record returned by the database, creating a Customer object each time, assigning the database values to the object's properties, and then adding that Customer object to the ArrayList. When we're done and we've cleaned up our connection, we return the ArrayList populated with Customer objects.
There are a few other changes we need to make. First, our CustomerID property can't be read-only because we need to assign data to it when we execute these searches from static methods. We revise it to include the "set" portion of the property in Listing 5.12
Example 5.12. The revised CustomerID property
C#
public int CustomerID
{
get {return _CustomerID;}
set {_CustomerID = value;}
}
VB.NET
Public Property CustomerID() As Integer
Get
Return _CustomerID
End Get
Set
_CustomerID = value
End Set
End Property
The other change is that our static method doesn't know anything about the string _ConnectionString because the rest of the class hasn't been instantiated. We've included the string here right in the code, but a better practice is to store it elsewhere, perhaps in web.config, instead of hard-coding it.
The big surprise for many people is that your wonderful new ArrayList can be bound to a Repeater control, and you can access the properties of the Customer objects just as if you bound a SqlDataReader or DataTable. That's because the ArrayList implements the IEnumerable interface, just like SqlDataReader and DataTable. As long as your ArrayList contains all the same objects, in this case Customer objects, there's nothing more to do. Your Repeater's ItemTemplate might look something like this:
<ItemTemplate> <p><%# DataBinder.Eval(Container.DataItem,"LastName") %>, <%# DataBinder.Eval(Container.DataItem,"FirstName") %></p> </ItemTemplate>
You can cache these result sets as well by putting the finished ArrayList into the cache using a name such as "UberCustomerList44114" in this case. However, you'll have to add more plumbing to the Update() and Delete() methods, as well as the Create() method to remove any customer ArrayLists being cached if the Zip matches. Otherwise, the cached ArrayList wouldn't have a new record (or would include a deleted record) of a customer with a 44114 Zip code.