Creating a Data Tier in ASP.NET

Every application starts with an idea, a thought or a need of some kind and the software of course becomes the solution. Once requirements have been gathered and the project vision is established the architects usually begin designing a logical framework for the application based on several factors.

It’s usually recommended no matter what the size or projected complexity of the application that there is some form of separation between interface logic, business rules and database logic. Creating this separation not only maintains a clean easier to read application but it also creates physically separate components which can then be reused in other areas or even deployed separately on other dedicated servers and invoked remotely.

A middle of the road approach is what most mid-sized applications will implement. Where most of the business logic will remain tied into the front end code and the database logic separated into another component and invoked as needed from the front end. This is still a 2-tier approach but it is definitely more favourable than having the database logic and SQL code embedded into the front end.

In this tutorial will we look at a fairly basic example of how to implement a database connection layer into an ASP.NET project which can then be invoked from your aspx pages in the code-behind. This style of approach bypasses all GUI wizards and connection/adapter controls which would normally be configured on the code-behind file. Instead we will push this logic into a separate component.

Display SubTotals in ASP.NET DataGrid

The DataGrid is of course a natural fit for display tabular sets of data in a web application and it does its job quite nicely with support for paging, sorting and even editing in place. As you develop your applications however you will inevitably come across a requirement which is not covered by the built in functionality.

Using Stored Procedures in ASP.NET

The architecture and implementation of an application’s data tier is always a strong primary focus for any developer. Without a data tier and connection to a backend your application would of course do nothing and all functionality in your application of course has to flow through the backend data store. Whether it be pulling data out or pushing it in the design of the data tier can make or break a good user experience from the front end.

While this principle is all well and good there are of course many developers who are not database designers or data tier architects and don’t have the expertise or skills to design proper n-tier componentized objects to support their data store.

Fortunately for these developers working on smaller scale applications there is a solution provided in Visual Studio .NET to allow more of a WYSIWYG database design interface that still allows you to leverage some of the more powerful aspects of database interaction without having to write code for it.

One of these more powerful database features is stored procedures. Stored procedures are normally supported by any large scale database server and provide a number of performance and design benefits. The #1 benefit is that your SQL code is encapsulated outside of your application. Meaning you can manage database changes and fixes without having to recompile and redeploy your application which is a great advantage.

While smaller scale database applications such as Microsoft Access support queries which are similar in principle to stored procedures, they usually require a separate data tier component and will not be covered in this tutorial.

This tutorial will focus on implementing stored procedures within SQL Server. Trial versions of SQL server can be freely downloaded and Visual Studio .NET also comes with a smaller scale MSDE for localized database installation and development testing on your local PC.

We’ll create a sample ASP.NET application which uses stored procedures to pull data out of the sample Northwind database that comes with SQL server, and we’ll also implement an update stored procedure and use parameters to push data back into the database, all of which is accomplished through a minimal amount of code and minimal database programming knowledge.