CHAPTER 21 ADO.NET PART I: THE CONNECTED LAYER
newPetName, id);
}
using(SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
{
cmd.ExecuteNonQuery();
}
Adding the Selection Logic
Next, you need to add a selection method. As you saw earlier in this chapter, a data provider’s data
reader object allows for a selection of records using a read-only, forward-only server-side cursor. As you
call the Read() method, you can process each record in a fitting manner. While this is all well and good,
you need to contend with the issue of how to return these records to the calling tier of your application.
One approach would be to populate and return a multidimensional array (or other such return
value, such as a generic List object) with the data obtained by the Read() method. Here is a
second way to obtain data from the Inventory table that uses the latter approach:
public List GetAllInventoryAsList()
{
// This will hold the records.
List inv = new List();
}
// Prep command object.
string sql = "Select * From Inventory";
using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
{
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
inv.Add(new NewCar
{
CarID = (int)dr["CarID"],
Color = (string)dr["Color"],
Make = (string)dr["Make"],
PetName = (string)dr["PetName"]
});
}
dr.Close();
}
return inv;
Still another approach is to return a System.Data.DataTable object, which is actually part of the
disconnected layer of ADO.NET. You will find complete coverage of the disconnected layer in the next
chapter; however, for the time being, you should understand that a DataTable is a class type that
represents a tabular block of data (e.g., a grid on a spreadsheet).
Internally, the DataTable class represents data as a collection of rows and columns. While you can
fill these collections programmatically, the DataTable type provides a method named Load() that
automatically populates these collections using a data reader object! Consider the following methods,
which return data from Inventory as a DataTable:
842