ASP 101 - Active Server Pages 101 - Web06
The Place ASP Developers Go!

Please visit our partners


Windows Technology Windows Technology
15 Seconds
4GuysFromRolla.com
ASP 101
ASP Wire
VB Forums
VB Wire
WinDrivers.com
internet.commerce internet.commerce
Partners & Affiliates














ASP 101 is an
internet.com site
ASP 101 is an internet.com site
IT
Developer
Internet News
Small Business
Personal Technology

Search internet.com
Advertise
Corporate Info
Newsletters
Tech Jobs
E-mail Offers

ASP 101 News Flash ASP 101 News Flash



 Top ASP 101 Stories Top ASP 101 Stories
Getting Scripts to Run on a Schedule
The Top 10 ASP Links @ Microsoft.com
What is Adovbs.inc and Why Do I Need It?

QUICK TIP:
How To List The Tables In A Database
Show All Tips >>
ASP 101 RSS Feed ASP 101 Updates


Building an ASP.NET App: John's DVD Catalog - Part 4: Finally... Some Code

by John Peterson

Before We Begin... Some Comments on the DB Design Phase

Well, if you didn't know it before we started this project, by now you've probably figured out that DB design is not my strong suit. It's a couple weeks and a ton of design changes later and, while I continue to get lots of good ideas for improvement, it's getting pretty tiresome and it's time to move on. Please note that in real life you shouldn't do this... you should really get things settled before you move on to writing code that depends on the DB design, but since this is supposed to be a sample app, I figured I should start getting to the sample code.

So... the DB is staying as it is... that is until I decide I need to change it.  ;)   Don't feel bad if your suggestion wasn't followed. I got a lot of requests that were very appropriate and some that I know I really should make, but we are talking about a relatively small amount of data and to be honest I'm being lazy about some of them. In particular, there are a number of other fields which should be changed to lookup tables, but I'm leaving them as is and will deal with the risk of bad data in an effort to keep the SQL we'll be writing at least somewhat human readable. There's also the problem of actors playing multiple roles in the same movie (like Eddie Murphy in "Coming to America" or Mike Meyers in the Austin Powers movies) which my keying in the movie-actor table makes troublesome, but again I'm willing to deal with the problem since I'm really more interested in the fact that an actor is in a particular movie then I am in the role that they play... plus I don't think don't I have any movies that it'll affect! I'm half joking, but that's the half that always seems to win these arguments.  ;)

Introduction

Now that I've put an end (as premature as it probably was) to all the database changes, I'm going to walk you through the basic steps of getting data from the database to a web page using a simple example. In the coming weeks we'll finish filling in the steps with more and more details and a lot more code, but before I dive into completing any one section I thought a nice overview would be helpful so you could see where I'm going.

When you think about this ASP.NET application, it's entire job is to provide a web page interface to the data sitting in our database. Between the database and the web are a number of layers and today I'm going to go through them one at a time (starting at the DB) until we reach the web.

The Next Level... Stored Procs

The next layer we need to get through in order to get our little data traveller out onto the information superhighway is the stored procedures layer. Stored procedures (Stored Procs) are really nothing more then SQL commands that have been prepared ahead of time so that the database knows how to handle them. They're more efficient then just sending standard SQL strings to the database and can really help centralize all your SQL into one location. For those of you who have been using Access, this might be a rude awakening and require a little bit of a change in your thinking, but once you get used to it it's really for the best.

The sample I'm going to use simply takes a DVD ID and retreives the title of the movie that the DVD contains. I am going to do something a little different in that instead of getting the result back as a data set, I'm passing it back out as an output parameter (mainly to avoid the overhead and code associted with returning a data reader for just 1 value), but, besides that, it should be pretty straight forward.

Create Procedure DvdTitle
(
	@dvdid	int,
	@title	varchar(255) OUTPUT
)
AS
SELECT
	@title = movie.title
FROM
	dvd
	INNER JOIN movie ON dvd.movie_id = movie.id
WHERE
	dvd.id = @dvdid

As you can see it's pretty simple... the first line indicates the name of the procedure. After that you place a comma delimited list of the the parameters that the stored procedure is going to require along with their data types. Note that I've also noted that the title parameter is an output paramter. After that you simply insert your SQL statement. You can obviously type it by hand or, if you prefer, you can certainly build it using whatever tool you like using constants for your parameter and then simply cut and paste it and finish by simply plugging in the parameters. (I'll talk more about how I do this when we start building some of the more complex SQL queries in the coming weeks.)

Now that we've got the stored proc built, the next step is to write some code to call it.

Code at Last

DvdDB.vb

The first file I'm gonna discuss is the DvdDB.vb file. It's written in VB.NET and is acting as a combo Business Layer / DB Layer component for this sample. It defines two different classes: DvdDetails and DvdDB.

The DvdDetails class is insanely simple. It's basically just a made up structure that represents a DVD. For illustration I've only given it one property called Title which will hold the title of the movie which that particular instance of the object represents.

The other class is DvdDB and it contains the code that is needed to query the database about DVDs. So far I've only added one function named GetDvdDetails. It takes a DVD ID integer and creates an instance of the DvdDetails class to represent the movie that integer identifies. It then retreives the data associated with that movie and populates the new DvdDetails object with the appropriate values. The object is then returned to the routine that requested it.

Option Explicit
Option Strict
Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Namespace asp101dvd
    '*******************************************************
    ' DvdDetails Class
    '*******************************************************
    Public Class DvdDetails
        Public Title As String
    End Class
    '*******************************************************
    ' DvdDB Class
    '*******************************************************
    Public Class DvdDB
        Public Function GetDvdDetails(ByVal DvdId As Integer) As DvdDetails
            Dim cnnDvdDetails As SqlConnection
            Dim cmdDvdDetails As SqlCommand
            Dim parDvdId As SqlParameter
            Dim parTitle As SqlParameter
            Dim myDvdDetails As DvdDetails
            ' Create connection and command objects.
            ' Note that we're not opening them yet.
            cnnDvdDetails = New SqlConnection(_
                ConfigurationSettings.AppSettings("ConnectionString"))
            cmdDvdDetails = New SqlCommand("DvdTitle", cnnDvdDetails)
            ' Identify the command as a stored procedure
            cmdDvdDetails.CommandType = CommandType.StoredProcedure
            ' Add parameters to the stored procedure
            ' Our first is an input parameter of type integer which we set
            ' to the id that was passed to this function.
            parDvdID = New SqlParameter("@dvdid", SqlDbType.Int, 4)
            parDvdID.Value = CInt(DvdId)
            cmdDvdDetails.Parameters.Add(parDvdId)
            ' Our second is an output parameter of type varchar.
            ' Since I'm just getting one value back, I'm retreiving it as
            ' an output parameter to avoid all the overhead of returning
            ' a dataset or data reader.
            parTitle = New SqlParameter("@title", SqlDbType.VarChar, 255)
            parTitle.Direction = ParameterDirection.Output
            cmdDvdDetails.Parameters.Add(parTitle)
            ' Open our connection, execute the command telling it
            ' we're not expecting an return object, and immediately
            ' close the connection.
            cnnDvdDetails.Open()
            cmdDvdDetails.ExecuteNonQuery()
            cnnDvdDetails.Close()
            ' Create an instance of our DVD data structure class.
            myDvdDetails = New DvdDetails()
            ' Set it's single value using the data we just retreived
            ' from the return value of our stored procedure.
            myDvdDetails.Title = CStr(parTitle.Value)
            ' Take the resulting object and return it to the
            ' calling routine.
            Return myDvdDetails
        End Function
    End Class
End Namespace

Oh and since I needed a Namespace to put all this in I've simply called it asp101dvd. To use this code, you need to compile it and place it in the /bin directory of your application. I've included both a compiled version and a basic make.bat file to make recompiling easy. (If I'm not mistaken, back in the Beta 1 timeframe there was talk of auto-compiling of components from the source vb file, but I couldn't figure out if this is supported or the syntax for it so if you find it let me know.)

GetTitle.aspx

This page is really pretty simple. It imports the namespace that we just created in our component so that is has access to it. It then creates an instance of the the DvdDB object discussed above, calls the GetDvdDetails method and receives the DvdDetails object it returns. From there it's simply a matter of using the values contained in that object to populate the different parts of the resulting HTML page.

For illustration I've hard-coded a DvdId of 1 into the code, but when we get to the real code this value would be read from the QueryString or Form collection depending on how this page was called.

<%@ Page Language="VB" %>
<%@ Import Namespace="asp101dvd" %>
<script runat="server">
    Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
		Dim objDvdDB As DvdDB
		Dim objDvdDetails As DvdDetails
		objDvdDB = New DvdDB
		objDvdDetails = objDvdDB.GetDvdDetails(1)
		lblTitle.Text = objDvdDetails.Title
    End Sub
</script>
<html>
<head>
<title>Sample ASP.NET Title Page</title>
</head>
<body>
<asp:Label id="lblTitle" runat="server" />
</body>
</html>

That's All Folks...

That's really all there is to it... wheh... wait a minute... all that work and all it does is display the title of a movie! That just doesn't seem right, but trust me... we've basically just done all that the heart of this app is really going to do!

Get The Code

You can download a zip containing DvdDB.vb, GetTitle.aspx, the make.bat file, a compiled asp101dvd.dll, and a sample web.config file from here. The stored proc can easily be cut and pasted from above and the DB creation scripts can be found back at the end of part 3. All that's missing is the data in the database, and, to be honest, I haven't entered that myself yet so just type in something to test with.

Oh and when it doesn't work... check the connection string in the web.config file.

Project Links

Building an ASP.NET App: John's DVD Catalog
Home |  News |  Samples |  Articles |  Lessons |  Resources |  Forum |  Links |  Search |  Feedback

Internet.com
The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers