[Sorry for the re-post. The formatted code is messing with the overall formatting of this page.]
(Ok, I'm going to try to recapture the post as best as I can, but in less time... sorry it's going to seem a little more rushed than I would like).
(Also, Chris be sure to read carefully... you may find an answer to your question.)
Ok, so here's the “reader's digest version” of what n-Tier development is. Originally this concept was called “3 tier development” The 3 tiers are Presentation (your GUI), Data (getting data from the database), and Business (the actual rules associated with your data...) In time their were wars about whether 3 was too many or not enough. People made good cases for both ways (I happen to embed much of my business logic in either the Stored Procs or the data later itself as validation rules... I guess some of the validation logic also makes it's way to the font end GUI too). The idea was that you separate these pieces in an application. A practical way to do this is to put it into a separate folder in your project or create a new project entirely.
Why do this?
Here's a simple scenario (but it's not hard to find instances in the past if you've done this for any length of time). Let's say you have a client that has techs in the field that they want to update tickets. Since the Internet is all the rage they decide that an asp.net web app would fit the bill, so you work hard and create the site from nothing (and you don't use an N-Tier methodology). A few months later the client comes back and says that the laptops are kind of expensive and they are wondering if you could give them some kind of Phone app... at this point your scratching your head because even though you've already written a big chunk of the code you will probably need to rewrite everything... And you need to be competitive or else you won't get the job.
With N-Tier, you can easily identify what parts of your app are data-related and yank them right out of the web project and dump them into a smart device app, or (better yet) a web service.
So how do I do it?
What I do (and by no means does this represent an absolute best practice... but this works for me... those more experienced please correct me) is I build what I call “SQL” classes. Let's take a simple “MOCK” authentication scenario (NOTE: by no means am I suggesting that this is the only code you would want to use for custom Authentication). So I start out with a simple base class that all my SQL classes derive from (I simply have some standard functions that I use):
1Option Explicit On
2Option Strict On
3
4Imports System.data
5Imports System.Data.SqlClient
6
7Public Class sqlBase
8 Protected _Database As {Some Namespace}.Database
9
10 ' Common Field Sizes
11 Protected Const USERID_PARAM_SIZE_IN_CHARS As Integer = 100
12
13 ' Common Parameters (I can more or less templat-ize my stored procs in one place...
' this one is used everywhere in the app)
14 Protected Shared ParamUser As SqlParameter = _
New SqlParameter("@USER", SqlDbType.VarChar, USERID_PARAM_SIZE_IN_CHARS)
15
16 ' ReturnCode Param
17 Protected Shared ParamReturnCode As SqlParameter =
New SqlParameter("@RETURN_CODE", SqlDbType.Int)
18
19 Public Sub New()
20 _Database = New {someNamespace}.Database
21 ParamReturnCode.Direction = ParameterDirection.ReturnValue
22 End Sub
23
24' With these 2 functions I can clone one of my shared params
25 Protected Function CreateParamFromTemplate(ByRef paramTemplate As SqlParameter, _
ByVal value As Object) As SqlParameter
26
27 Try
28 Dim param1 As SqlParameter =
DirectCast(DirectCast(paramTemplate, ICloneable).Clone(), SqlParameter)
29 param1.Value = value
30 Return param1
31 Catch ex As Exception
32 Throw New DataException("Error in sqlBase:CreateParamFromTemplate", ex)
' I have my own exception class
33 End Try
34 End Function
35
36 Protected Function CreateParamFromTemplate _
(ByRef paramTemplate As SqlParameter) As SqlParameter
37 Try
38 Dim param1 As SqlParameter = _
DirectCast(DirectCast(paramTemplate, ICloneable).Clone(), SqlParameter)
39 Return param1
40 Catch ex As Exception
41 Throw New DataException("Error in sqlBase:CreateParamFromTemplate", ex)
' I have my own exception class (check out the Exception App Block)
42 End Try
43 End Function
44
45End Class
There is a Database class mentioned in this definition; I'll let you dream it up; all it does is we get the connection string.
Alright now we mentioned that we want to do authentication, so a simple Security class would look like this:
6
7Option Explicit On
8Option Strict On
9
10Imports System.Data.SqlClient
11Imports Microsoft.ApplicationBlocks.Data
12
13Public Class sqlSecurity
14 Inherits sqlBase
15
16' Stored Proc names
17 Private Const AUTH As String = "SP_Auth"
18
19 ' Field Sizes
20 Private Const PASSWORD_PARAM_SIZE_IN_CHARS As Integer = 1000
21
22 ' Parameters
23 Private Shared ParamPWD As SqlParameter = _
New SqlParameter("@PWD", SqlDbType.VarChar, PASSWORD_PARAM_SIZE_IN_CHARS)
24
25' A simple Authentication function... not meant to illustrate a great example of security)
26 Public Function Authenticate(ByVal UserID As String, ByVal Password As String) As Boolean
27 Dim __DR as SQLDataReader
28 Try
29 __DR = SqlHelper.ExecuteReader( _
30 _Database.ConnectionString, _
31 CommandType.StoredProcedure, _
32 AUTH, _
33 CreateParamFromTemplate(ParamUser, Left(UserID, USERID_PARAM_SIZE_IN_CHARS)) _
34 CreateParamFromTemplate(ParamPWD, Left(PWD, PASSWORD_PARAM_SIZE_IN_CHARS) )
35 return __dr.read() ' There is a record if the user authenticates (otherwise no record returns
36 Catch ex As Exception
37 Throw New SecurityRelatedException("Error in Get User Permissions", ex)
' Once again I have derived my own exceptions (see the MS Exception Block for more info)
38 Finally
39 if Not (__DR is Nothing) andalso __DR.IsClosed = False Then __DR.Close()
40 End Try
41 End Function
42
43End Class
And finally how you would call this
1Dim __SQL as sqlSecurity
2Try
3 __SQL = New sqlSecurity()
4 If __SQL.Authenticate(UserID.Text, Password.Text) Then
5 ' User is logged in do get them into the app
6 Else
7 ' User is not logged in.. show them the door
8 End If
9Catch Ex as Exception
10 ' Do something with the error
11End Try
The important thing is that my web (or win) code does not touch the database at all. That means that the database code can “live” anywhere (in the app, from another DLL, from a web service, etc.). I'm much more flexible this way.
I will probably followup on this and explain the code more if necessary. Let me know via comments if I need to (BTW, Chris, my guess is that you should keep the code in the web service...).
Print | posted on Thursday, June 03, 2004 7:46 PM