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.
Note: This tutorial requires Visual Studio .NET running ASP.NET 1.1 as well as a version of SQL server or access to an SQL server on your network which contains the Northwind sample database. You may also link to another SQL sample database of your choice but you will need to modify the code and SQL syntax where applicable to suit your alternative database. Both VB.NET and C# code samples are provided