[thelist] ASP.NET displaying data on a page

[Norman Beresford]

Hi Casey

This might end up a bit of a long email, but I hope we cover lots of
things.  A lot of this is hard won experience.

First up, NEVER store your connection string within your code.  What
will happen is someone will change the server it's on, or  the password
will change, and suddenly your application will be dead.  And you will
have lost the source code so you won't be able to recompile it with the
new connection string.

So our first little lesson is using web.config and the
ConfigurationSettings class.

If you look in your web project you'll see a file called web.config.  If
you open this up you'll find it's a simple XML file, with LOTS of stuff
in it.  It's actually part of a hierarchy, but you'll not bother with
any of the parent files (machine.config etc).

What you need to do is add a new section within it called appSettings.
Within this section you can add new entries, in the form of <add key=""
value="" />.  Any entries within this area you can reference from the
rest of the application using
System.Configuration.ConfigurationSettings.AppSettings("key").  So with
your connection string you're going to end up with the following at the
top of your web.config file:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>       
	 <add key="MyConnectionString" value="server='123.abc.com'; user
id='xyz'; password='xxx'; database='zzz'" />
    </appSettings>
    <system.web>

And your code will now look like this:

Dim conDocs as SqlConnection = New
SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("My
ConnectionString"))

This means that if you wish to change the connection string you only
have to do so in one place, in the web.config file, which is a text file
so you don't need to recompile for changes to have an effect.  You might
want to look into encrypting the connection string, but I'm not going to
go into that here :)

The next thing to look at is your use of the DataSet.  The DataSet is
easy to use, it's easy to populate etc, and very flexible once you've
got it filled, however it carries a substantial performance overhead.  I
don't know how far into ASP you got but you might have discovered that
you could use different cursors.  Most of the time you'd use a forward
only 'firehose' cursor.  This meant that you could pull out a single
record at a time, and once you'd taken it out you no longer had access
to the previous record.  Well ASP.Net has it's own object that's similar
to that, the DataReader.  This gives you forward only access to data,
and with a far lower performance hit then the DataSet.

Converting your code to using a DataReader we'd end up with something
like:

Sub Page_Load(Src As Object, E As EventArgs)

dim getDoc as string = request.querystring("id")

Dim conDocs as SqlConnection = New
SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("My
ConnectionString"))
Dim cmdDocs As SqlCommand = New SqlCommand
cmdDocs.Connection = conDocs
cmdDocs.CommandText = "SELECT fname FROM doctors WHERE doc_id= " &
getDoc
cmdDocs.CommandType = CommandType.Text

Dim drDocs As SqlDataReader
conDocs.Open()
drDocs = cmdDocs.ExecuteReader
	If drDocs.Read()
		lblDocfname.Text= drDocs("fname")
	End If
drDocs.Close()
conDocs.Close()

End Sub

However we can do a lot better then that :)

The thing about this code is that we can't reuse it easily.  There's no
real structure to it.  So what we need to do is think about what we're
actually doing.  We're modelling a doctor, and our doctor has various
attributes.  So we're going to create a doctor class, and wrap all our
functionality up within it.  That means if we want to change how it
behaves we only have to make the change in one place.

Now I'm not certain how well WebMatrix lets you do this, so you might
want to look at an alternative IDE..VS.net being the best, but you could
look at Sharp Develop which is a open source programme.  Assuming
WebMatrix does let you do it:

We'll create a new class called Doctor.vb.  Each doctor has an ID and a
first name, so we want to create properties exposing them.  We also want
to be able to tell the class to load based on an ID, so we'll add a
method to do that.  So our code is going to look like:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class Doctor
	Private _ID as integer
	Private _Fname as string

	Public Property Id() As Integer
        Get
            Return _ID
        End Get
        Set(ByVal Value As Integer)
            _ID = Value
        End Set
	End Property

	Public Property Fname() As string
        Get
            Return _Fname
        End Get
        Set(ByVal Value As string)
            _Fname= Value
        End Set
	End Property

	Public Sub Load(connectionString as string)
		_Load(connectionString)
	End Sub
	
	Private Sub _Load(connectionString as string)
		Dim conDocs as SqlConnection = New
SqlConnection(connectionString)
		Dim cmdDocs As SqlCommand = New SqlCommand
		cmdDocs.Connection = conDocs
		cmdDocs.CommandText = "SELECT fname FROM doctors WHERE
doc_id= " & _ID
		cmdDocs.CommandType = CommandType.Text

		Dim drDocs As SqlDataReader
		conDocs.Open()
		drDocs = cmdDocs.ExecuteReader
		If drDocs.Read()
			_Fname = drDocs("fname")
		End If
		drDocs.Close()
		conDocs.Close()
	End Sub
End Class

Now we change the presentation code.  What we want to do is create a
doctor object, assigns it's ID, and tell it to load it's details using
the connection string we pass to it.  So our code will look like:

Sub Page_Load(Src As Object, E As EventArgs)
	Dim thisDoctor as new Doctor
	thisDoctor.Id = request.querystring("id")
	
thisDoctor.Load(System.Configuration.ConfigurationSettings.AppSettings("
MyConnectionString"))

	lblDocfname.Text= thisDoctor.Fname

	thisDoctor = nothing
End Sub

So you can see this means our presentation code is now a lot smaller.
And we can reuse our doctor object on other pages.

Now there's one final very important change we need to make to the code.
At the moment you're passing dirty values to the sql you're executing
against the database.  This leaves you wide open to sql injection
attacks.  All someone has to do is embed some SQL into the querystring
and you're going to find yourself with a corrupted/deleted
database/server.  You're using SQL Server so we should take advantage of
stored procedures.

So we need to create our stored procedure.  It's not very different to
the sql you're using at the moment, but we want to define a parameter
for it:

CREATE PROCEDURE usp_Doctor_Retrieve
(
		@doc_id int
	)
AS
SELECT fname FROM doctors WHERE doc_id = @doc_id

We then need to change our doctor object to execute the stored
procedure, and to pass the appropriate parameter to it.  As you can see
if we had our doctor code scattered all over the project we'd have to
track down every place it was used, but instead now we can just alter
the code in the single object.  All we need to do is change the command
text to the name of the stored procedure, change the CommandType, and
create a parameter object and add it to the command.  So within the
_Load method we change the code so that the appropriate bit looks like:

Dim conDocs as SqlConnection = New SqlConnection(connectionString)
Dim cmdDocs As SqlCommand = New SqlCommand
cmdDocs.Connection = conDocs
cmdDocs.CommandText = "usp_Doctor_Retrieve"
cmdDocs.CommandType = CommandType.StoredProcedure

Dim paramClient As SqlClient.SqlParameter = New SqlClient.SqlParameter
paramClient = cmdClient.Parameters.Add("@doc_id", SqlDbType.Int)
paramClient.Direction = ParameterDirection.Input
paramClient.Value = _ID

Everything else stays the same.  What this means is that if we try to
use anything other then an integer for the doc_id in the query we're
going to get an exception when we try to set the Id property, when we
add the parameter, and when we try to pass it to the actual stored
procedure.

I hope that gives you so ideas about how to go forward.  If you adopt
the approach I've advocated, using a separate object to model the
doctor, using a SqlDataReader instead of a DataSet, and using
paramatised stored procedures instead of dynamic SQL you should find
your code is far more flexible.  You'll quickly find yourself spending
less time actually writing code and more time designing what it's going
to do.

For your next question we'll talk about strongly typed collections, but
I'll let you ask it first (assuming you're going to ask 
About how to handle multiple doctors rather then single ones).  Then we
can move on to how using objects like our doctor object allows us to use
TDD :)

Norman