Geeks With Blogs
Paul Chapman .Net Musings

In the years since the .Net runtime was first released I've seen a number of ways of accessing a SQL database from within VB.Net (or C#). One person I worked with had a nifty utility which would take any database and generate all the code to access it. It has been on my list of jobs to do to implement my own such tool but like other jobs, such as winning the National Lottery or winning a high stakes poker game I have never got around to it. OK the poker game is something my wife will have to do - she is better at poker than me.

What I do have is a class which encapsulates the functions I need to execute stored procedures on the database. All my database access is executed by inheriting from the following class;

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient

Imports System
Imports System.Xml
Imports System.Xml.Linq

Public MustInherit Class DataEngine
    Protected _Connection As SqlConnection
    Protected _Command As SqlCommand

    Sub New()
        _Connection = New SqlConnection()
        _Command = New SqlCommand()
    End
Sub

    Sub New(ByVal ConnectionString As String)
        _Connection = New SqlConnection(ConnectionString)
        _Command = New SqlCommand()
    End
Sub

    Protected Property ConnectionString As
String
       
Get
            Return _Connection.ConnectionString
        End
Get
        Set(ByVal value As String)
            _Connection.ConnectionString = value   
        End
Set
    End
Property

    Protected Function ExecuteStoredProcedure(ProcedureName As String, Table As String) As DataTable
        Dim _DataTable      As DataTable
        Dim _DataAdapter    As SqlDataAdapter
        Dim _DataSet        As DataSet = New DataSet()

       
Try

            If _Connection.State = ConnectionState.Closed Then
                _Connection.Open()
            End
If

            With _Command
                .CommandText = ProcedureName
                .CommandType = CommandType.StoredProcedure
                .Connection = _Connection
            End
With

            _DataAdapter = New SqlDataAdapter(_Command)

            _DataAdapter.Fill(_DataSet,Table)
            _DataTable = _DataSet.Tables(Table)
        Catch ex As Exception
            Throw ex
        End
Try

        Return _DataTable
    End
Function

    Protected Sub ExecuteStoredProcedure(ProcedureName As String)
       
Try
           
            If _Connection.State = ConnectionState.Closed Then
                _Connection.Open()
            End
If

            With _Command
                .CommandText = ProcedureName
                .CommandType = CommandType.StoredProcedure
                .Connection = _Connection   
     
                .ExecuteNonQuery()
            End With 

        Catch ex As Exception
            Throw ex
        End
Try
    End Sub


    Protected Property Command As SqlCommand
       
Get
            Return _Command
        End
Get
        Set(ByVal value As SqlCommand)
            _Command = value
        End
Set
    End
Property

End
Class

First thing to notice about this class is that it is marked MustInherit you cannot directly create an instance of this class. What you do is create a class which inherits from this class. In this way the common tasks required; which summarised is open a connection to the database and execute stored procedures. There are two types essentially; a stored procedure which returns a table, and one that does not.

The class has two constructors. One that creates the basic database access objects; the connection and the command, and another which allows us to pass a connection string.

Next is a property which exposes the connection string to any child of this class. This is the other means for setting the connection string, although to be honest as a general rule I prefer to do that at the when an instance is created.

The next two procedures take advantage of overloading, allowing us to pass an optional table name for the results.

Finally the last property allows access to the command object.

We can use this class to access the database created earlier with the following class;

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration.ConfigurationManager

Public Class SimplicitaDatabase
        Inherits DataEngine

    Public Sub
New

        ConnectionString = ConnectionStrings("SimplicitaDB").ConnectionString

    End
Sub

    Public Function Register(Name As String,Address1 As String, Address2 As String, Town As String, County As String, Postcode As String, Tel As String,Fax As String, Email As String, WebAddress As String,Password As String) As DataTable
        Dim dt As DataTable = New DataTable()


        Dim SqlParamName        As SqlParameter = New SqlParameter("Name",SqlDbType.VarChar)
        Dim SqlParamAddress1    As SqlParameter = New SqlParameter("Address1",SqlDbType.VarChar)
        Dim SqlParamAddress2    As SqlParameter = New SqlParameter("Address2",SqlDbType.VarChar)
        Dim SqlParamTown        As SqlParameter = New SqlParameter("Town",SqlDbType.VarChar)
        Dim SqlParamCounty      As SqlParameter = New SqlParameter("County",SqlDbType.VarChar)
        Dim SqlParamPostcode    As SqlParameter = New SqlParameter("Postcode",SqlDbType.VarChar)
        Dim SqlParamTel         As SqlParameter = New SqlParameter("Telephone",SqlDbType.VarChar)
        Dim SqlParamFax         As SqlParameter = New SqlParameter("Fax",SqlDbType.VarChar)
        Dim SqlParamEmail       As SqlParameter = New SqlParameter("Email",SqlDbType.VarChar)
        Dim SqlParamWebAddr     As SqlParameter = New SqlParameter("WebAddress",SqlDbType.VarChar)
        Dim SqlParamPassword    As SqlParameter = New SqlParameter("Password",SqlDbType.VarChar)
       
       
Try

            SqlParamName.Value      = Name
            SqlParamAddress1.Value  = Address1
            SqlParamAddress2.Value  = Address2
            SqlParamTown.Value      = Town
            SqlParamCounty.Value    = County
            SqlParamPostcode.Value  = Postcode
            SqlParamTel.Value       = Tel
            SqlParamFax.Value       = Fax
            SqlParamEmail.Value     = Email
            SqlParamWebAddr.Value   = WebAddress
            SqlParamPassword.Value  = Password

            With Command.Parameters
                .Clear()
                .Add(SqlParamName)
                .Add(SqlParamAddress1)
                .Add(SqlParamAddress2)
                .Add(SqlParamTown)
                .Add(SqlParamCounty)
                .Add(SqlParamPostcode)
                .Add(SqlParamTel)
                .Add(SqlParamFax)
                .Add(SqlParamEmail)
                .Add(SqlParamWebAddr)
                .Add(SqlParamPassword)
            End
With

            dt = ExecuteStoredProcedure("RegisteredUser.Register","Result")

        Catch ex As Exception
            Throw
        End
Try
       
        Return dt
    End
Function

    Public Function RetrieveNewsfeeds As DataTable
        Dim dt As DataTable = New DataTable

        With Command.Parameters
            .Clear()
        End
With

        dt = ExecuteStoredProcedure("NewsAggregator.RetrieveNewsFeeds","NewsFeeds")

        Return dt
    End
Function

    Public Function RetrieveLatestStories As DataTable
        Dim dt As DataTable

        With Command.Parameters
            .Clear()
        End
With

        dt = ExecuteStoredProcedure("NewsAggregator.RetrieveLatestStories","NewsStories")

        Return dt
    End
Function

    Public Sub WriteNewsStory ( Title As String, Link As String, Desc As String, Supplier As String)
        Dim SqlParamTitle       As SqlParameter = New SqlParameter("Title",SqlDbType.VarChar)
        Dim SqlParamLink        As SqlParameter = New SqlParameter("Link",SqlDbType.VarChar)
        Dim SqlParamDesc        As SqlParameter = New SqlParameter("Desc",SqlDbType.VarChar)
        Dim SqlParamSupplier    As SqlParameter = New SqlParameter("Supplier",SqlDbType.VarChar)

        SqlParamTitle.Value     = Title
        SqlParamLink.Value      = Link
        SqlParamDesc.Value      = Desc
        SqlParamSupplier.Value  = Supplier

        With Command.Parameters
            .Clear()
            .Add(SqlParamTitle)
            .Add(SqlParamLink)
            .Add(SqlParamDesc)
            .Add(SqlParamSupplier)
           
        End
With

        ExecuteStoredProcedure("NewsAggregator.WriteNewsStory")
       
    End
Sub
End
Class

And that is the code for accessing the database created in part one.

Posted on Thursday, September 4, 2008 10:57 PM Silverlight , VB.Net | Back to top


Comments on this post: Implementing a News Aggregator in Silverlight and VB.Net #2 Accessing our database from VB.Net

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © paulschapman | Powered by: GeeksWithBlogs.net