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 a high stakes poker game. 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.
Contents
ReBlogger Contents
Email TopXML