[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

creating 3 tier web  application

Posted on 2005-04-24
3
Medium Priority
?
288 Views
Last Modified: 2010-04-07
Hi,
I know its a sunday morning and I should be in bed!!! However, I have been trying to teach myself 3 tier app in vb.net.

Its kinda of hard to follow books!!

What I have so far:--- A class library as my DataAccessLayer called DataAccess
                                 "   "       "       "   "    BusinessAccessLayer  called  Business

and the client application called orbits.

In the Business layer i added a direct reference    to the Access Class Lib
In client application "orbits"  has a direct reference  to the Business Class Lib
I believe this step is correct.

Now my problem how do i write the a common data access class in the Access Class lib, so that I can communicate with the
stored procedures in the database??



I was using example from my Ibuyspyportal book, but i can't get around it!!

can anyone please help!!

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

Namespace Wrox.Intranet.DataTools

    Public Class Database
        Implements IDisposable

#Region "Private Fields"
        'connection to data source
        Private con As SqlConnection
        'Error Message field
        Private _errorMessage As String
#End Region

#Region "Public Properties"

        Public ReadOnly Property ErrorMessage() As String
            Get
                Return _errorMessage
            End Get
        End Property

        Public Shared ReadOnly Property ConnectionString() As String
            Get
                'Return System.Configuration.ConfigurationSettings.AppSettings("Database.DEFAULT_CONNECTION_STRING")
                Return System.Configuration.ConfigurationSettings.AppSettings _
                                                            ("ConnectionString")
            End Get
        End Property

#End Region

#Region "Public Method Implementations"

        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'This function takes a stored proc name and returns an integer '
        'by invoking ExecuteScalar() on the SqlCommand type instance   '
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Public Function RunProcedure(ByVal procName As String) As Integer
            Dim result As Integer
            Dim cmd As SqlCommand

            Try
                cmd = CreateCommand(procName, Nothing)
                result = CType(cmd.ExecuteScalar(), Integer)

            Catch e As Exception
                result = -1
                _errorMessage = e.Message

            Finally
                Me.Close()

            End Try

            Return result

        End Function

        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'This function takes a stored proc name, sql parameters returns an integer '
        'by invoking ExecuteScalar() on the SqlCommand type instance               '
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Public Function RunProcedure(ByVal procName As String, ByVal prams As SqlParameter()) As Integer
            Dim result As Integer
            Dim cmd As SqlCommand

            Try

                cmd = CreateCommand(procName, prams)
                result = cmd.ExecuteScalar()

            Catch e As Exception
                result = -1
                _errorMessage = e.Message

            Finally
                Me.Close()

            End Try

            Return result

        End Function

        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'This method takes a stored proc name and a SqlDataReader (BY REF) and returns the results    '
        'in the same DataReader that you pass in as ref. This invokes ExecuteReader on SqlCommand type'
        'instance                                                                                     '
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Public Sub RunProcedure(ByVal procName As String, ByRef dataReader As SqlDataReader)
            Dim cmd As SqlCommand
            Try
                cmd = CreateCommand(procName, Nothing)
                dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

            Catch e As Exception
                dataReader = Nothing
                _errorMessage = e.Message
                Me.Close()

            End Try
        End Sub

        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'This method takes a stored proc name, Sql Parameters and a SqlDataReader (BY REF) and         '
        'returns the results in the same DataReader that you pass in as ref. This invokes ExecuteReader'
        'on SqlCommand type instance                                                                   '
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Public Sub RunProcedure(ByVal procName As String, ByVal prams As SqlParameter(), ByRef dataReader As SqlDataReader)
            Dim cmd As SqlCommand
            Try
                cmd = CreateCommand(procName, prams)
                dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

            Catch e As Exception
                dataReader = Nothing
                _errorMessage = e.Message
                Me.Close()
            End Try

        End Sub

        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'This method takes a stored proc name, Sql Parameters and a DataSet ByRef      '
        'In case of an exception returns a Nothing and ErrorMessage                    '
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Public Sub RunProcedure(ByVal procName As String, ByVal prams As SqlParameter(), ByRef ds As DataSet)
            Dim cmd As SqlCommand
            Dim adapter As SqlDataAdapter
            Try
                cmd = CreateCommand(procName, prams)
                adapter = New SqlDataAdapter(cmd)
                adapter.Fill(ds)
            Catch e As Exception
                _errorMessage = e.Message
                ds = Nothing
            Finally
                adapter = Nothing
                Me.Close()
            End Try

        End Sub


        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'This method takes a stored proc name and a Dataset by Ref     '
        'In case of an exception returns a Nothing and ErrorMessage    '
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Public Sub RunProcedure(ByVal procName As String, ByRef ds As DataSet)

            Dim cmd As SqlCommand
            Dim adapter As SqlDataAdapter

            Try
                cmd = CreateCommand(procName, Nothing)
                adapter = New SqlDataAdapter(cmd)
                adapter.Fill(ds)
            Catch e As Exception
                _errorMessage = e.Message
                ds = Nothing
            Finally
                adapter = Nothing
                Me.Close()
            End Try

        End Sub

        Public Function MakeParameter(ByVal ParamName As String, ByVal Value As Object) As SqlParameter
            Dim param As SqlParameter

            param = New SqlParameter(ParamName, Value)
            param.Direction = ParameterDirection.Input

            Return param

        End Function

        Public Function MakeParameter(ByVal ParamName As String, ByVal Direction As ParameterDirection, ByVal Value As Object) As SqlParameter
            Dim param As SqlParameter

            param = New SqlParameter(ParamName, Value)
            param.Direction = Direction

            Return param

        End Function


        Public Sub Close()
            If (con Is Nothing) Then
            Else
                con.Close()
            End If
        End Sub

        Public Sub Dispose() Implements IDisposable.Dispose

            If (con Is Nothing) Then
            Else
                con.Dispose()
                con = Nothing
            End If

        End Sub

#End Region

#Region "Private Method Implementations"

        Private Function CreateCommand(ByVal procName As String, ByVal prams As SqlParameter()) As SqlCommand

            Dim cmd As SqlCommand
            Dim parameter As SqlParameter

            Call Open()

            cmd = New SqlCommand(procName, con)

            cmd.CommandType = CommandType.StoredProcedure

            If (prams Is Nothing) Then
            Else
                For Each parameter In prams
                    cmd.Parameters.Add(parameter)
                Next

            End If

            Return cmd

        End Function


        Private Sub Open()
            If (con Is Nothing) Then
                con = New SqlConnection(Me.ConnectionString())
                con.Open()
            Else
                If ((con.State = ConnectionState.Closed) Or (con.State = ConnectionState.Broken)) Then
                    con.Open()
                End If
            End If
        End Sub
#End Region


    End Class

End Namespace


this
 
0
Comment
Question by:SirReadAlot
  • 2
3 Comments
 
LVL 33

Accepted Solution

by:
raterus earned 2000 total points
ID: 13853061
As long as you make it to church, code all you want on Sunday morning!

I know what you want, but I think you are trying to hard in your case.  I'd suggest coding it so the Access lib makes direct calls to the DB.  To add another layer of abstraction there would be a waste of time and resources.  Unless this is a majorly huge project, I'd skip this step.  If you can keep to the layers you've already defined you should be more than good to go!

--Michael
0
 

Author Comment

by:SirReadAlot
ID: 13853134
yep mate.

I guess I try too hard.  
























0
 

Author Comment

by:SirReadAlot
ID: 13853226
am just curious and what to know how to build it from scratch using the 3 tiers which i described above.

0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month19 days, 12 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question