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
)
ASSELECT
@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.
