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.

Read More

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.

For the sake of having a set of data to work with we’ll connect to the sample Northwind database provided with all installations of SQL server, and the sample application will be built using ASP.NET version 1.1 Read More

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.

Read More

ASP.NET “Mini” Master/Detail Pattern

ASP.NET “Mini” Master/Detail Pattern

In ASP.NET 2.0 the master/detail pattern has undergone massive improvements, however when working with ASP.NET 1.1 and Visual Studio .NET 2003/2002 it is a much more manual process. There are hundreds of articles regarding this subject however the most common usage is with large amounts of data; meaning long lists, with subsets of data that can fill large forms.

Sometimes the amount of data is smaller and you need to develop a much more compact version of this pattern, providing your users with a quick and easy lookup for extra information regarding your entity, whatever it may be.

In this tutorial we’ll look at a compact or “mini” version of the master/detail pattern which uses a very small list of data presented in a DataGrid component, and selecting a row in the DataGrid will display a minimal amount of extra information on another details form contained in the same page.

In addition to the master/detail pattern we’ll also implement a few tips & tricks with the DataGrid component to give us a more usable control. Below is a list of other aspects covered in this tutorial:

• Bound column concatenation: Binding more than one column in the data source to a
  single column on the DataGrid component without using code-behind
• Primary Key lookup: Binding the primary key to a hidden field on the DataGrid for easy seeking of rows on selection
• Select anywhere feature: Allowing the user to click on any cell on the DataGrid to invoke the SelectedIndexChanged event
• Manual DataSet object construction: How to create a DataSet through code including a primary key column
• Enable smart navigation to minimize page refresh visually

Although this tutorial is designed to be used in a more compact fashion the pattern itself can easily be migrated to larger implementations with larger volumes of data. This tutorial is not based on the new ASP.NET 2.0 implementation wizards for building master/detail pages; it is designed for ASP.NET 1.1.

Read More