In this tutorial we'll start from the very beginning and create the Data Access Layer DAL , using typed DataSets, to access the information in a database. Introduction As web developers, our lives revolve around working with data. We create databases to store the data, code to retrieve and modify it, and web pages to collect and summarize it.
This is the first tutorial in a lengthy series that will explore techniques for implementing these common patterns in ASP.
NET pages that share a common page layout. Once this backend groundwork has been laid, we'll move into reporting, showing how to display, summarize, collect, and validate data from a web application.
These tutorials are geared to be concise and provide step-by-step instructions with plenty of screen shots to walk you through the process visually.
Each tutorial is available in C and Visual Basic versions and includes a download of the complete code used. This first tutorial is quite lengthy, but the rest are presented in much more digestible chunks.
These scripts can be also be downloaded directly from Microsoft , if you'd prefer. The web application was built using Visual Studio Professional Edition as a file system-based Web site project. However, all of the tutorials will work equally well with the free version of Visual Studio , Visual Web Developer.
In this tutorial we'll start from the very beginning and create the Data Access Layer DAL , followed by creating the Business Logic Layer BLL in the second tutorial, and working on page layout and navigation in the third. The tutorials after the third one will build upon the foundation laid in the first three. We've got a lot to cover in this first tutorial, so fire up Visual Studio and let's get started!
Start by creating a new file system-based ASP. With the web site created, the next step is to add a reference to the database in Visual Studio's Server Explorer. By adding a database to the Server Explorer you can add tables, stored procedures, views, and so on all from within Visual Studio.
You can also view table data or create your own queries either by hand or graphically via the Query Builder. While we can provide this connection information at that point in time, Visual Studio automatically populates a drop-down list of the databases already registered in the Server Explorer.
MDF in the Server Explorer, which you can expand and explore its tables, views, stored procedure, and so on see Figure 2. Keep in mind, however, that Access databases aren't as feature-rich as SQL Server, and aren't designed to be used in web site scenarios.
If the database server does not already have the Northwind database installed, you first must add it to database server by running the installation script included in this tutorial's download or by downloading the SQL Server version of Northwind and installation script directly from Microsoft's web site. This will bring up the Add Connection dialog box, where you can specify the server to connect to, the authentication information, and the database name. Once you have successfully configured the database connection information and clicked the OK button, the database will be added as a node underneath the Data Connections node.
You can expand the database node to explore its tables, views, stored procedures, and so on. Creating the Data Access Layer When working with data one option is to embed the data-specific logic directly into the presentation layer in a web application, the ASP. NET pages make up the presentation layer.
This may take the form of writing ADO. In either case, this approach tightly couples the data access logic with the presentation layer. The recommended approach, however, is to separate the data access logic from the presentation layer. The benefits of this layered architecture are well documented see the "Further Readings" section at the end of this tutorial for information on these advantages and is the approach we will take in this series.
The presentation layer should not contain any references to such data access code, but should instead make calls into the DAL for any and all data requests. Data Access Layers typically contain methods for accessing the underlying database data. The Northwind database, for example, has Products and Categories tables that record the products for sale and the categories to which they belong.
In our DAL we will have methods like: GetCategories , which will return information about all of the categories GetProducts , which will return information about all of the products GetProductsByCategoryID categoryID , which will return all products that belong to a specified category GetProductByProductID productID , which will return information about a particular product These methods, when invoked, will connect to the database, issue the appropriate query, and return the results.
How we return these results is important. These methods could simply return a DataSet or DataReader populated by the database query, but ideally these results should be returned using strongly-typed objects. A strongly-typed object is one whose schema is rigidly defined at compile time, whereas the opposite, a loosely-typed object, is one whose schema is not known until runtime.
For example, the DataReader and the DataSet by default are loosely-typed objects since their schema is defined by the columns returned by the database query used to populate them. To access a particular column from a loosely-typed DataTable we need to use syntax like: The DataTable's loose typing in this example is exhibited by the fact that we need to access the column name using a string or ordinal index.
A strongly-typed DataTable, on the other hand, will have each of its columns implemented as properties, resulting in code that looks like: To return strongly-typed objects, developers can either create their own custom business objects or use Typed DataSets. A business object is implemented by the developer as a class whose properties typically reflect the columns of the underlying database table the business object represents.
A Typed DataSet is a class generated for you by Visual Studio based on a database schema and whose members are strongly-typed according to this schema. We'll use strongly-typed DataSets for these tutorials' architecture.
Figure 3 illustrates the workflow between the different layers of an application that uses Typed DataSets. To accomplish this, right-click on the project node in the Solution Explorer and choose Add a New Item.
Select the DataSet option from the list of templates and name it Northwind. A Typed DataSet serves as a strongly-typed collection of data; it is composed of strongly-typed DataTable instances, each of which is in turn composed of strongly-typed DataRow instances.
We will create a strongly-typed DataTable for each of the underlying database tables that we need to work with in this tutorials series. Let's start with creating a DataTable for the Products table. Keep in mind that strongly-typed DataTables do not include any information on how to access data from their underlying database table. The DataTable's role is to serve as the strongly-typed objects used to pass data between the layers.
The TableAdapter Configuration Wizard begins by prompting you to select which database to work with. The drop-down list shows those databases in the Server Explorer. If you did not add the Northwind database to the Server Explorer, you can click the New Connection button at this time to do so.
Choose the Northwind Database from the Drop-Down List Click to view full-size image After selecting the database and clicking Next, you'll be asked if you want to save the connection string in the Web.
By saving the connection string you'll avoid having it hard coded in the TableAdapter classes, which simplifies things if the connection string information changes in the future. Save the Connection String to Web. These two steps are accomplished simultaneously by creating a query that returns the columns from the table that we want reflected in our DataTable. At the end of the wizard we'll give a method name to this query.
Once that's been accomplished, this method can be invoked from our presentation layer. The method will execute the defined query and populate a strongly-typed DataTable.
To get started defining the SQL query we must first indicate how we want the TableAdapter to issue the query. We can use an ad-hoc SQL statement, create a new stored procedure, or use an existing stored procedure. For these tutorials we'll use ad-hoc SQL statements. When creating the first method in the TableAdapter you typically want to have the query return those columns that need to be expressed in the corresponding DataTable.
We can accomplish this by creating a query that returns all columns and all rows from the Products table: Create the Query Graphically, through the Query Editor Click to view full-size image After creating the query, but before moving onto the next screen, click the Advanced Options button.
In Web Site Projects, "Generate Insert, Update, and Delete statements" is the only advanced option selected by default; if you run this wizard from a Class Library or a Windows Project the "Use optimistic concurrency" option will also be selected. Leave the "Use optimistic concurrency" option unchecked for now. We'll examine optimistic concurrency in future tutorials.
Select Only the Generate Insert, Update, and Delete statements Option Click to view full-size image After verifying the advanced options, click Next to proceed to the final screen. Here we are asked to select which methods to add to the TableAdapter. There are two patterns for populating data: Fill a DataTable with this approach a method is created that takes in a DataTable as a parameter and populates it based on the results of the query.
Return a DataTable with this approach the method creates and fills the DataTable for you and returns it as the methods return value. You can have the TableAdapter implement one or both of these patterns. You can also rename the methods provided here. Let's leave both checkboxes checked, even though we'll only be using the latter pattern throughout these tutorials.
Also, let's rename the rather generic GetData method to GetProducts. If you've unchecked the "Generate Insert, Update, and Delete statements" option from the advanced properties in Figure 9 this checkbox's setting will have no effect. Let's leave this checkbox selected. After the wizard closes we are returned to the DataSet Designer which shows the DataTable we just created.
ProductsTableAdapter with a GetProducts method. These objects can be used to access a list of all products from code like: ProductsTableAdapter Dim products as Northwind. ProductsRow In products Response. We did not have to instantiate any ADO. Instead, the TableAdapter provides the low-level data access code for us.
Each object used in this example is also strongly-typed, allowing Visual Studio to provide IntelliSense and compile-time type checking. With the ObjectDataSource we'll not have to write any code and will get paging and sorting support as well! While being able to work with all products is definitely useful, there are times when we'll want to retrieve information about a specific product, or all products that belong to a particular category. To add such functionality to our Data Access Layer we can add parameterized methods to the TableAdapter.
Right-Click on the TableAdapter and Choose Add Query We are first prompted about whether we want to access the database using an ad-hoc SQL statement or a new or existing stored procedure. Let's choose to use an ad-hoc SQL statement again.