CHAPTER 21 ADO.NET PART I: THE CONNECTED LAYER
Adding the Insertion Logic
Inserting a new record into the Inventory table is as simple as formatting the SQL Insert statement
(based on user input) and calling the ExecuteNonQuery() using your command object. You can see this in
action by adding a public method to your InventoryDAL type named InsertAuto() that takes four
parameters that map to the four columns of the Inventory table (CarID, Color, Make, and PetName). You
use these arguments to format a string type to insert the new record. Finally, use your SqlConnection
object to execute the SQL statement.
public void InsertAuto(int id, string color, string make, string petName)
{
// Format and execute SQL statement.
string sql = string.Format("Insert Into Inventory" +
"(CarID, Make, Color, PetName) Values" +
"('{0}', '{1}', '{2}', '{3}')", id, make, color, petName);
}
// Execute using our connection.
using(SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
{
cmd.ExecuteNonQuery();
}
This method is syntactically fine, but you could supply an overloaded version that allows the caller
to pass in a strongly typed class that represents the data for the new row. Define the following new
NewCar class, which represents a new row in the Inventory table:
public class NewCar
{
public int CarID { get; set; }
public string Color { get; set; }
public string Make { get; set; }
public string PetName { get; set; }
}
Now add the following version of InsertAuto() to your InventoryDAL class:
public void InsertAuto(NewCar car)
{
// Format and execute SQL statement.
string sql = string.Format("Insert Into Inventory" +
"(CarID, Make, Color, PetName) Values" +
"('{0}', '{1}', '{2}', '{3}')", car.CarID, car.Make, car.Color, car.PetName);
// Execute using our connection.
using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
{
cmd.ExecuteNonQuery();
}
}
Defining classes that represent records in a relational database is a common way to build a data
access library. In fact, as you will see in Chapter 23, the ADO.NET Entity Framework automatically
generates strongly typed classes that allow you to interact with database data. On a related note, the
840