CHAPTER 21 ADO.NET PART I: THE CONNECTED LAYER
Source Code You can find the AutoLotDataReader project under the Chapter 21 subdirectory.
Building a Reusable Data Access Library
The ExecuteReader() method extracts a data reader object that allows you to examine the results of a
SQL Select statement using a forward-only, read-only flow of information. However, when you want to
submit SQL statements that result in the modification of a given table (or any other non-query SQL
statement, such as creating tables or granting permissions), you call the ExecuteNonQuery() method of
your command object. This single method performs inserts, updates, and deletes based on the format of
your command text.
Note Technically speaking, a nonquery is a SQL statement that does not return a result set. Thus, Select
statements are queries, while Insert, Update, and Delete statements are not. Given this, ExecuteNonQuery()
returns an int that represents the number of rows affected, not a new set of records.
Next, you will learn how to modify an existing database using nothing more than a call to
ExecuteNonQuery(); your next goal is to build a custom data access library that can encapsulate the
process of operating upon the AutoLot database. In a production-level environment, your ADO.NET
logic will almost always be isolated to a .NET *.dll assembly for one simple reason: code reuse! The first
examples of this chapter have not done so, simply so you can keep focused on the task at hand; however,
it would be a waste of time to author the same connection logic, the same data reading logic, and the
same command logic for every application that needs to interact with the AutoLot database.
Isolating data access logic to a .NET code library means that multiple applications using any sort of
front end (e.g., console based, desktop based, or web based) can reference the library at hand in a
language-independent manner. Thus, if you author your data library using C#, other developers can
build a UI in the .NET language of their choice.
In this chapter, your data library (AutoLotDAL.dll) will contain a single namespace
(AutoLotConnectedLayer) that interacts with AutoLot using the connected types of ADO.NET. In the next
chapter, you will add a new namespace (AutoLotDisconnectionLayer) to this same *.dll that contains
types to communicate with AutoLot using the disconnected layer. Multiple applications will take
advantage of this library throughout the remainder of this book.
Begin by creating a new C# Class Library project named AutoLotDAL (short for AutoLot Data Access
Layer) and renaming your initial C# code file to AutoLotConnDAL.cs. Next, rename your namespace scope
to AutoLotConnectedLayer and change the name of your initial class to InventoryDAL; this class will define
various members to interact with the Inventory table of the AutoLot database. Finally, import the
following .NET namespaces:
using System;
...
// You will use the SQL server
// provider; however, it would also be
// permissible to use the ADO.NET
838