The Overall Idea
Because the .NET Framework does not fulfill all these requirements out of the box, you need to create your own data access architecture. Of course, you do not want to reinvent the wheel, so use all the data access components the .NET Framework provides and simply wrap your functionality around it.
Fundamentally, what you need is a single place through which all our data access will pass. No developer will ever be allowed to create a connection or send a command to the database that does not pass through the infrastructure. Sometimes people say "
but I have so many different data access scenarios and needs, how could I possibly build infrastructure that allows for all of those?"
|We need to build a data access infrastructure that routes all data operations through a single class. This does not limit us in any way, as the .NET Framework already takes a similar approach.|
The answer is simple. The implementation needs to be flexible and pass all kinds of different commands through without applying any special restrictions. Can something like that be implemented? Of course! It can be done without too much trouble. In fact, the .NET Framework itself applies this very concept by routing all data access through a few simple objects in ADO.NET. This approach builds on that and does not limit the feature-set provided.
Any client code that needs to execute a database command uses a data service object (often also referred to as a data portal object) to do so. The client can specify the desired database operation using a standard SqlCommand object (or in more generic scenarios, an IDbCommand object, as you will see later). Once the command is passed to the data service, it is completely up to the service to figure out how to execute the command, achieve the desired operation, and return the requested result set. One possible implementation can have the data service open a standard connection to SQL Server and execute the command, but there are many other options, as you will see later in this article.
The data service object features a number of methods that can be used to interact with the data. The exact list of methods varies with your exact needs, but at a bare minimum, there will be methods such as ExecuteQuery()
, or ExecuteScalar()
, and the like. To make our scenario as generic as possible, first create an interface that defines the methods you want. For now, keep things simple. Here is a first version of that interface (create a new project such as a C# Windows application to put this code into):
public interface IDataService
DataSet ExecuteQuery(IDbCommand command,
void ExecuteNonQuery(IDbCommand command);
object ExecuteScalar(IDbCommand command);
This interface serves as the contract to which every data service class needs to adhere. It is simpler than a real-life version would be, but it will suffice for the first example.
The interface exposes three methods that are specific to data operations: ExecuteQuery()
executes a query and returns a result set; ExecuteNonQuery()
executes a database command that does not query data (such as INSERT
); and ExecuteScalar()
is similar to ExecuteQuery()
, except that it returns a single value instead of a set of rows.
method is somewhat different. It tells you whether the data service is valid and can be used in its current state. For instance, a standard SQL Server data service would be valid on a LAN, but not over the Internet, where it could not connect to the database. In essence, this method tells you whether or not the service managed to connect to the database.
Now that you have defined a simple interface, you can create the first class that implements those ideas. Listing 1
shows a simple data service that connects to SQL Server on a local area network. It takes care of all the details of talking to the database, such as creating a connection based on configuration settings defined for the application. To use this class, you need to add an application configuration file to your project that contains the following information:
<?xml version="1.0" encoding="utf-8" ?>
<add key="UserId" value="devuser"/>
<add key="Password" value="devuser"/>
<add key="Server" value="(local)"/>
<add key="Database" value="Northwind"/>
It should be pointed out here that this is not a particularly good way to store configuration information for database access. In a production implementation, information such as user names and passwords should be stored in a more secure way. It could also be discussed whether Windows or SQL authentication is the better way to go. These issues are not the focus of this article.
Now that you have a data service class as well as appropriate configuration settings, you can use the service to query data:
IDataService svc = new SqlServerDataService();
IDbCommand cmd =
new SqlCommand("SELECT * FROM Customers");
DataSet dsCustomers =
This is the code you would expect the developers using the data service infrastructure to write. It is already pretty nice, because it is much easier to use the data service object to execute queries than it is to create connections and adapters and all the other things that are now abstracted into the service class. However, there are a number of improvements you can make to make it even easier to use these classes. Also, you have not quite achieved the goals defined above.