[thelist] ASP.NET displaying data on a page

[Casey]

Norman,

Your generosity with this answer has blown me away.  Thank you!  I will read
and re-read this email until I get it right, and I'm sure I'll have a few
questions along the way as well.

!!! Thank You !!!

Casey

----- Original Message ----- 
From: "Norman Beresford" <n.beresford at anansi.co.uk>


> 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
>
>