Database Search Solution
(New Version) Search Control
Free ASP.NET Controls
ASP.NET Media Player Control
Flash Video Player Control
ASP.NET Telecommute Jobs
Free IP Location Lookup
Test .Net Regular Expressions
CSS/Table/DIV Page Layouts
Article Sites Master List
.NET Windows Forms
General .NET Framework
Accepting Credit Cards
Using ADO.NET to Develop Data Access Logic (DAL)
Dynamic Web sites usually use one or more data sources like a database or xml files. In ASP.NET 2.0 you can make use of ADO.NET to easily communicate with your data sources. This tutorial will explain you more about the data layer. There are several other tutorials for creating the business and the presentation layer on this site.
3 Tier application
Quite often you hear developers speak about 3 tier (or n tier) applications. This is a technique to develop your application in several layers to keep different things nicely separated. The basic idea of a 3 tier application is to separate your data, business logics and the presentation.
In the data layer you have CRUD (Create, Update, Delete) methods for your tables (or other data source). You might add some minor business logics in combination with those tables (e.g. max, count, ...) and usually a singleton class is used to provide connection to the business layer.
In the business layer you have all the domain classes with their variables, properties and methods. The variables contain the status
of your business entity and are hidden to the outer world by using data hiding (private, protected). Public properties
provide the data of these variables to client applications while methods implement the business logics.
The third layer of a 3 tier application is the presentation layer. This can be any type of user interface, for ASP.NET is this a Web application.
ADO.NET object model
ADO.NET has both connected and disconnected classes to handle data. The connected classes are located in a .NET data provider and
are used for communication with a database and can be placed in two groups. At one side you have all the classes to set up a
Connection, to handle a Command and to read the returned data (DataReader).
The disconnected classes can retrieve a DataSet from the connected classes or from an xml file. The DataTable implements a DataRowCollection, DataColumnCollection and a ConstrainCollection and acts like a database table (but then disconnected from the database). All sql commands can be executed on the disconnected classes. For more info about disconnected data, check ADO.NET - Working With Disconnected Data tutorial.
The .NET Framework has multiple data providers, mainly one for each common used type:
Setting up a connection with the database
Connecting with a database is done with the Connection class. Every Data Provider has his own version implemented.
To setup the connection there is a connection string needed which mostly contains info about the server containing the database,
name of the database and authentication data. Making a connection with the database costs loads of time. This is why most providers
implement a connection pool. Every time you use Open() with the same connection string, a connection from the pool is
returned. After a Close() the connection returns to the pool.
Do not forget to close database connections yourself since they are not automatically closed when leaving the scope.
This is best done in a finally clause to be sure it's always executed, even in case of an exception. Close connections as soon as
possible, since they are expensive sources.
Executing a sql command
Executing a sql command is done with the Command class. This class needs at least his CommandText (query) and his Connection property set to be able to execute the command. This can be done by setting the properties one by one, or by adding both in the constructor. Parameters in the query (or stored procedure) have to be set by using the Parameters property which is a collection.
There are three possible ways to execute a command with the Command class. The first method is ExecuteReader, this method creates a DataReader with the result of the sql command. A DataReader is a forward-only and read-only stream data stream of database records and it's the fastest way to retrieve records. Don't forget to close the DataReader since a onnection object can only have one DataReader open. It's common used to implement 'mapper' methods to map your record to a business logic object. This is the method to use for your select statements .
The second method to execute a sql command is ExecuteNonQuery. This method executes the command and returns the number of rows affected. It should be used for insert, update and delete statements.
The last method is ExecuteScalar. This method is used for select statements that return only one value (max(), count(), ...) and allows you to retrieve the value in one single step. Note that you can also use the ExecuteReader method, but you will have to open and close the DataReader to get the value. Note that you can use ExecuteScalar instead of ExecuteNonQuery to retrieve the primary key from your last insert by adding select SCOPE_IDENTITY() to your command string.
You can add parameters to your sql command at runtime. This is be done with either placing the parameter in the command, or adding it to the Parameters collection property. Use an @ in front of your parameter name to minimalise chances on sql injection. Extra (regular expression) validation and MaxLength on your controls are extra safety checks. You don't want a malevolent person add this to your sql string if you ask a single number: "5; delete * from users".
int InsertBrouwer(Brouwer brouwer)
Concurrency in ADO.NET
There are several ways to handle concurrency (concurrency is when multiple users changing same records). The first way is to use
'Last-in-wins' updating. This is the fastest way but only have to be used if the chances on a collision are very small.
Another way to handle concurrency is Timestamp-based updating (optimistic locking). This requires an extra (timestamp) column with the
time of the last update. If the timestamp did change since the last time you read this record, your update will fail.
Setting up a data access logic (DAL) class file
It's a nice habit to make a separate data access logic class for each different business entity. This DAL class will use ADO.NET and sql to access a database. Implementation for data in xml or other data sources are analogue. The DAL class will have to set up a connection, be able to execute commands and map the results to objects for the business layer. Have a look at the code supplied with this tutorial for an implementation of everything your learned in this tutorial. Also note that the business entities (domain classes) aren't necessarily an exact copy of a database table.
To conclude this tutorial here's a small code snippet that shows you how to bind the retrieved data to a control. For more info about data binding and the controls, see Introduction To Server Controls tutorial.
You can downloadExample Data Access Logic Implementation Project, used in this tutorial.
This tutorial is written by Assesino