Home » Projects » SQL DB Functions

SQL DB Functions

Modified:

Start Date:

1 Jul 2005

Last Update / Completion Date:

Status:

Complete

Project Types:

in

Class: SqlDbFunctions

VB
' ***************************************************************
' * Copyright © 2005 Russell Carter                             *
' *                                                             *
' * Written by Russell Carter - July 2005                       *
' *                                                             *
' * Please see AssemblyInfo for Version info. This code         *
' * may not be used or copied without prior permission          *
' * from Russell Carter.                                        *
' *                                                             *
' ***************************************************************

Imports System.Data.SqlClient
Imports System.Text

Public MustInherit Class SqlDbFunctions
    Inherits System.Collections.CollectionBase

    Protected myConn As New SqlConnection
    Protected myCmd As New SqlCommand
    Protected myTable As String
    Protected myReader As SqlDataReader
    Protected strSQL As New StringBuilder
    Protected myWhereClause As String

    Sub New()
    End Sub

    Sub New(ByVal ConnectionString As String, ByVal TableName As String)
        SetConnection(ConnectionString, TableName)
    End Sub

    Sub New(ByVal SQLServerName As String, ByVal DatabaseName As String, ByVal TableName As String)
        SetConnection(SQLServerName, DatabaseName, TableName)
    End Sub

    Sub New(ByVal SQLServerName As String, ByVal DatabaseName As String, ByVal TableName As String, ByVal Username As String, ByVal Password As String)
        SetConnection(SQLServerName, DatabaseName, TableName, Username, Password)
    End Sub

    Public Overridable WriteOnly Property WhereClause() As String
        Set(ByVal Value As String)
            myWhereClause = Value
        End Set
    End Property

    Public ReadOnly Property Item(ByVal Index As Integer) As SqlRecord
        Get
            Return CType(Me.InnerList.Item(Index), SqlRecord)
        End Get
    End Property

    Public ReadOnly Property QueryString()
        Get
            Return strSQL.ToString
        End Get
    End Property

    Public ReadOnly Property ConnString()
        Get
            'Return myConn.ConnectionString
            Dim cnStr As String = ""
            Dim s, ss As String()
            s = myConn.ConnectionString.Split(";")
            ' Split the string into sections delimited by semicolons.
            For i As Integer = 0 To s.Length - 1
                ' Find the section that contains the password and replace it.
                If s(i).StartsWith("Pass") Then
                    ss = s(i).Split("=")
                    cnStr &= ss(0) & "=" & replaceWithStars(ss(1)) & ";"
                Else
                    cnStr &= s(i) & ";"
                End If
            Next
            If cnStr.EndsWith(";") Then
                cnStr = cnStr.Substring(0, cnStr.Length - 1)
            End If
            Return cnStr
        End Get
    End Property

    Private Function replaceWithStars(ByVal s As String)
        Dim sr As String = ""
        For i As Integer = 0 To s.Length - 1
            sr &= "*"
        Next
        Return sr
    End Function

    Public Shadows Sub Clear()
        MyBase.Clear()
        strSQL.Remove(0, strSQL.Length)
    End Sub

    Public Overridable Sub Add(ByVal FieldName As String, ByVal FieldValue As String)
        Me.InnerList.Add(New SqlRecord(FieldName, FieldValue))
    End Sub

    Public Overridable Sub Add(ByVal mySQLRecord As SqlRecord)
        Me.InnerList.Add(mySQLRecord)
    End Sub

    Public Overridable Sub AddDbNull()
        ' Implemented in subclasses. 
    End Sub

    Public Overridable Sub addDbNull(ByVal FieldName As String)
        ' Implemented in subclasses. 
    End Sub

    Public Sub SetConnection(ByVal TableName As String)
        myTable = TableName
    End Sub

    Public Sub SetConnection(ByVal ConnectionString As String, ByVal TableName As String)
        SetConnectionString(ConnectionString)
        myTable = TableName
    End Sub

    Public Sub SetConnection(ByVal SQLServerName As String, ByVal DatabaseName As String, ByVal TableName As String)
        SetConnectionString("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" & DatabaseName & ";Data Source=" & SQLServerName)
        myTable = TableName
    End Sub

    Public Sub SetConnection(ByVal SQLServerName As String, ByVal DatabaseName As String, ByVal TableName As String, ByVal Username As String, ByVal Password As String)
        SetConnectionString("Persist Security Info=True;Password=" & Password & ";User ID=" & Username & ";Initial Catalog=" & DatabaseName & ";Data Source=" & SQLServerName)
        myTable = TableName
    End Sub

    Private Sub SetConnectionString(ByVal ConnString As String)
        Try
            myConn.ConnectionString = ConnString
        Catch ex As Exception
            Throw New Exception("There was a problem setting the SqlConnection's ConnectionString: " & ConnString & ": " & ex.Message)
        End Try
    End Sub

End Class
Expand

Class: SqlDbSelecter

VB
' ***************************************************************
' * Copyright © 2005 Russell Carter                             *
' *                                                             *
' * Written by Russell Carter - July 2005                       *
' *                                                             *
' * Please see AssemblyInfo for Version info. This code         *
' * may not be used or copied without prior permission          *
' * from Russell Carter.                                        *
' *                                                             *
' ***************************************************************

Imports System.Data.SqlClient
Imports System.Web.UI.WebControls

Public Class SqlDbSelecter
    Inherits SqlDbFunctions

    Sub New()
    End Sub

    Sub New(ByVal ConnectionString As String)
        MyBase.New(ConnectionString, "")
    End Sub

    Sub New(ByVal SQLServerName As String, ByVal DatabaseName As String)
        MyBase.New(SQLServerName, DatabaseName, "")
    End Sub

    Sub New(ByVal SQLServerName As String, ByVal DatabaseName As String, ByVal Username As String, ByVal Password As String)
        MyBase.new(SQLServerName, DatabaseName, "", Username, Password)
    End Sub

    Public Overloads Overrides Sub Add(ByVal FieldName As String, ByVal FieldValue As String)
        Throw New Exception("You cannot add items to an SqlDbSelecter class. Use the QueryString argument of the SqlSelect method instead.")
    End Sub

    Public Overloads Overrides Sub Add(ByVal mySqlRecord As SQLRecord)
        Throw New Exception("You cannot add items to an SqlDbSelecter class. Use the QueryString argument of the SqlSelect method instead.")
    End Sub

    Public Function SqlSelect(ByVal QueryString As String) As DataSet
        strsql.Append(QueryString)
        Try
            Dim mySqlAdaptor As New SqlDataAdapter(QueryString, myConn)
            Dim myDataSet As New DataSet
            mySqlAdaptor.Fill(myDataSet)
            Return myDataSet
        Catch ex As Exception
            Throw New Exception("A error occurred while returning the data: " & ex.Message)
        End Try
    End Function

    Public Sub loadDropdown(ByVal ddl As DropDownList, ByVal QueryString As String, ByVal firstItemBlank As Boolean)
        Try
            Dim sqla As New SqlDataAdapter(QueryString, myconn)
            Dim ds As New DataSet
            sqla.Fill(ds)
            If ds.Tables(0).Columns.Count <> 2 Then
                Throw New Exception("Your query must return two fields, an ID and text to be displayed")
            End If
            ddl.DataSource = ds
            ddl.DataValueField = ds.Tables(0).Columns(0).ColumnName
            ddl.DataTextField = ds.Tables(0).Columns(1).ColumnName
            ddl.DataBind()
            If firstItemBlank Then ddl.Items.Insert(0, New ListItem("", 0))
        Catch ex As Exception
            Throw New Exception(": " & ex.Message)
        End Try
    End Sub


End Class
Expand

Class: SqlDbInserter

VB
' ***************************************************************
' * Copyright © 2005 Russell Carter                             *
' *                                                             *
' * Written by Russell Carter - July 2005                       *
' *                                                             *
' * Please see AssemblyInfo for Version info. This code         *
' * may not be used or copied without prior permission          *
' * from Russell Carter.                                        *
' *                                                             *
' ***************************************************************

Public Class SqlDbInserter
    Inherits SqlDbFunctions

    Public ReturnIdentity As Boolean = False
    Public UseFieldNames As Boolean = True

    Sub New()
    End Sub

    Sub New(ByVal ConnectionString As String, ByVal TableName As String, Optional ByVal ReturnID As Boolean = False, Optional ByVal FieldNames As Boolean = True)
        MyBase.New(ConnectionString, TableName)
        ReturnIdentity = ReturnID
        UseFieldNames = FieldNames
    End Sub

    Sub New(ByVal SQLServerName As String, ByVal DatabaseName As String, ByVal TableName As String, Optional ByVal ReturnID As Boolean = False, Optional ByVal FieldNames As Boolean = True)
        MyBase.New(SQLServerName, DatabaseName, TableName)
        ReturnIdentity = ReturnID
        UseFieldNames = FieldNames
    End Sub

    Sub New(ByVal SQLServerName As String, ByVal DatabaseName As String, ByVal TableName As String, ByVal Username As String, ByVal Password As String, Optional ByVal ReturnID As Boolean = False, Optional ByVal FieldNames As Boolean = True)
        MyBase.new(SQLServerName, DatabaseName, TableName, Username, Password)
        ReturnIdentity = ReturnID
        UseFieldNames = FieldNames
    End Sub

    Public Overloads Sub Add(ByVal FieldValue As String)
        If Not UseFieldNames Then
            MyBase.InnerList.Add(New SqlRecord("", FieldValue))
        Else
            Throw New Exception("The 'UseFieldNames' property must be set to False if you wish to omit the field names.")
        End If
    End Sub

    Public Overloads Overrides Sub Add(ByVal FieldName As String, ByVal FieldValue As String)
        If UseFieldNames Then
            MyBase.InnerList.Add(New SqlRecord(FieldName, FieldValue))
        Else
            Throw New Exception("The 'UseFieldNames' property must be set to True if you wish to use the field names.")
        End If
    End Sub

    Public Overloads Overrides Sub Add(ByVal mySqlRecord As SqlRecord)
        If (mySqlRecord.Fieldname = "" And Not UseFieldNames) Or (mySqlRecord.Fieldname <> "" And UseFieldNames) Then
            MyBase.InnerList.Add(mySqlRecord)
        Else
            Throw New Exception("The 'UseFieldNames' property must be set to False if you wish to omit the field names, and True if you wish to use them.")
        End If
    End Sub

    Public Overloads Overrides Sub AddDbNull()
        If Not UseFieldNames Then
            MyBase.InnerList.Add(New SqlRecord("", "", True))
        Else
            Throw New Exception("The 'UseFieldNames' property must be set to False if you wish to omit the field names, and True if you wish to use them.")
        End If
    End Sub

    Public Overloads Overrides Sub AddDbNull(ByVal FieldName As String)
        If UseFieldNames Then
            MyBase.InnerList.Add(New SqlRecord(FieldName, "", True))
        Else
            Throw New Exception("The 'UseFieldNames' property must be set to False if you wish to omit the field names, and True if you wish to use them.")
        End If
    End Sub

    Public Overrides WriteOnly Property WhereClause() As String
        Set(ByVal Value As String)
            Throw New Exception("You cannot specify a where clause on a SELECT query")
        End Set
    End Property

    Public Function SqlInsert() As String
        Dim Identity As String
        strSQL.Append("INSERT INTO ")
        strSQL.Append(myTable)
        If UseFieldNames Then
            strSQL.Append(" (")
            For Each myRecord As SqlRecord In MyBase.InnerList
                strSQL.Append(myrecord.Fieldname)
                strSQL.Append(", ")
            Next
            strSQL.Remove(strSQL.Length - 2, 2)
            strSQL.Append(") VALUES(")
        Else
            strSQL.Append(" VALUES(")
        End If
        For Each myRecord As SqlRecord In MyBase.InnerList
            If myrecord.IsDatabaseNull Then
                strsql.Append("NULL, ")
            Else
                strSQL.Append("'")
                strSQL.Append(Replace(myrecord.FieldValue, "'", "''"))
                strSQL.Append("', ")
            End If
        Next
        strSQL.Remove(strSQL.Length - 2, 2)
        strSQL.Append(")")
        If ReturnIdentity Then strSQL.Append(" SELECT @@IDENTITY")
        Try
            myConn.Open()
        Catch ex As Exception
            Throw New Exception("A error occurred while opening a connection to the database: " & ex.Message)
        End Try
        myCmd.Connection = myConn
        myCmd.CommandText = strSQL.ToString
        Try
            myReader = myCmd.ExecuteReader
        Catch ex As Exception
            If myConn.State = ConnectionState.Open Then myConn.Close()
            Throw New Exception("A error occurred while inserting data: " & strSQL.ToString & ": " & ex.Message)
        End Try
        If ReturnIdentity Then
            myReader.Read()
            If IsDBNull(myReader(0)) Then
                Throw New Exception("No row identity was returned. This may be because there is no Identity field in the database.")
            Else
                Identity = myReader(0)
            End If
        Else
            Identity = ""
        End If
        myReader.Close()
        myConn.Close()
        Return Identity
    End Function

End Class
Expand

Class: SqlDbUpdater

VB
' ***************************************************************
' * Copyright © 2005 Russell Carter                             *
' *                                                             *
' * Written by Russell Carter - July 2005                       *
' *                                                             *
' * Please see AssemblyInfo for Version info. This code         *
' * may not be used or copied without prior permission          *
' * from Russell Carter.                                        *
' *                                                             *
' ***************************************************************

Public Class SqlDbUpdater
    Inherits SqlDbFunctions

    Sub New()
    End Sub

    Sub New(ByVal ConnectionString As String, ByVal TableName As String)
        MyBase.New(ConnectionString, TableName)
    End Sub

    Sub New(ByVal SQLServerName As String, ByVal DatabaseName As String, ByVal TableName As String)
        MyBase.New(SQLServerName, DatabaseName, TableName)
    End Sub

    Sub New(ByVal SQLServerName As String, ByVal DatabaseName As String, ByVal TableName As String, ByVal Username As String, ByVal Password As String)
        MyBase.new(SQLServerName, DatabaseName, TableName, Username, Password)
    End Sub

    Public Overloads Overrides Sub AddDbNull(ByVal FieldName As String)
        MyBase.InnerList.Add(New SqlRecord(FieldName, "", True))
    End Sub

    Public Function SqlUpdate() As Integer
        Dim NumRows As Integer
        If myWhereClause = "" Then Throw New Exception("There is no WHERE clause. As a safety measure, please use '1 = 1' if you want to update the whole table.")
        strSQL.Append("UPDATE ")
        strSQL.Append(myTable)
        strSQL.Append(" SET ")
        For Each myRecord As SqlRecord In MyBase.InnerList
            strSQL.Append(myRecord.Fieldname)
            If myRecord.IsDatabaseNull Then
                strsql.Append(" = NULL, ")
            Else
                strSQL.Append(" = '")
                strSQL.Append(Replace(myRecord.FieldValue, "'", "''"))
                strSQL.Append("', ")
            End If
        Next
        strSQL.Remove(strSQL.Length - 2, 2)
        strSQL.Append(" WHERE ")
        strSQL.Append(myWhereClause)
        Try
            myConn.Open()
        Catch ex As Exception
            Throw New Exception("A error occurred while opening a connection to the database: " & ex.Message)
        End Try
        myCmd.Connection = myConn
        myCmd.CommandText = strSQL.ToString
        Try
            NumRows = myCmd.ExecuteNonQuery
        Catch ex As Exception
            If myConn.State = ConnectionState.Open Then myConn.Close()
            Throw New Exception("A error occurred while updating the database: " & strSQL.ToString & ": " & ex.Message)
        End Try
        myConn.Close()
        Return NumRows
    End Function

End Class
Expand

Class: SqlDbDeleter

VB
' ***************************************************************
' * Copyright © 2005 Russell Carter                             *
' *                                                             *
' * Written by Russell Carter - July 2005                       *
' *                                                             *
' * Please see AssemblyInfo for Version info. This code         *
' * may not be used or copied without prior permission          *
' * from Russell Carter.                                        *
' *                                                             *
' ***************************************************************

Public Class SqlDbDeleter
    Inherits SqlDbFunctions

    Sub New()
    End Sub

    Sub New(ByVal ConnectionString As String, ByVal TableName As String)
        MyBase.New(ConnectionString, TableName)
    End Sub

    Sub New(ByVal SQLServerName As String, ByVal DatabaseName As String, ByVal TableName As String)
        MyBase.New(SQLServerName, DatabaseName, TableName)
    End Sub

    Sub New(ByVal SQLServerName As String, ByVal DatabaseName As String, ByVal TableName As String, ByVal Username As String, ByVal Password As String)
        MyBase.new(SQLServerName, DatabaseName, TableName, Username, Password)
    End Sub

    Public Overloads Overrides Sub Add(ByVal FieldName As String, ByVal FieldValue As String)
        Throw New Exception("You cannot add items to an SqlDbDeleter class.")
    End Sub

    Public Overloads Overrides Sub Add(ByVal mySqlRecord As SqlRecord)
        Throw New Exception("You cannot add items to an SqlDbDeleter class.")
    End Sub

    Public Function SqlDelete() As Integer
        If myWhereClause = "" Then Throw New Exception("There is no WHERE clause. As a safety measure, please use '1 = 1' if you want to update the whole table.")
        Dim NumRows As Integer
        Try
            myConn.Open()
        Catch ex As Exception
            Throw New Exception("A error occurred while opening a connection to the database: " & ex.Message)
        End Try
        myCmd.Connection = myConn
        strsql.Append("DELETE FROM ")
        strsql.Append(myTable)
        strsql.Append(" WHERE ")
        strsql.Append(myWhereClause)
        myCmd.CommandText = strsql.ToString
        Try
            NumRows = myCmd.ExecuteNonQuery
        Catch ex As Exception
            If myConn.State = ConnectionState.Open Then myConn.Close()
            Throw New Exception("A error occurred while deleting the data: " & strSQL.ToString & ": " & ex.Message)
        End Try
        myConn.Close()
        Return NumRows
    End Function

End Class
Expand

Class: SqlDbExecSP

VB
' ***************************************************************
' * Copyright © 2005 Russell Carter                             *
' *                                                             *
' * Written by Russell Carter - July 2005                       *
' *                                                             *
' * Please see AssemblyInfo for Version info. This code         *
' * may not be used or copied without prior permission          *
' * from Russell Carter.                                        *
' *                                                             *
' ***************************************************************

Imports System.Data.SqlClient

Public Class SqlDbExecSP
    Inherits SqlDbFunctions
    Public UseParams As Boolean = False

    Sub New()
    End Sub

    Sub New(ByVal ConnectionString As String, Optional ByVal Params As Boolean = False)
        MyBase.New(ConnectionString, "")
        UseParams = Params
    End Sub

    Sub New(ByVal SQLServerName As String, ByVal DatabaseName As String, Optional ByVal Params As Boolean = False)
        MyBase.New(SQLServerName, DatabaseName, "")
        UseParams = Params
    End Sub

    Sub New(ByVal SQLServerName As String, ByVal DatabaseName As String, ByVal Username As String, ByVal Password As String, Optional ByVal Params As Boolean = False)
        MyBase.new(SQLServerName, DatabaseName, "", Username, Password)
        UseParams = Params
    End Sub

    Public Overloads Sub Add(ByVal FieldValue As String)
        If UseParams Then
            MyBase.InnerList.Add(New SqlRecord("", FieldValue))
        Else
            Throw New Exception("The 'UseParams' property must be set to True if you wish to Use parameters.")
        End If
    End Sub

    Public Overloads Overrides Sub Add(ByVal FieldName As String, ByVal FieldValue As String)
        Throw New Exception("Use of this method is not valid here. Please use the Add(FieldValue As String) method, after setting the 'UseParams' property to True.")
    End Sub

    Public Overloads Overrides Sub Add(ByVal mySqlRecord As SqlRecord)
        If (mySqlRecord.Fieldname = "" And UseParams) Then
            MyBase.InnerList.Add(mySqlRecord)
        Else
            Throw New Exception("The 'UseParams' property must be set to True if you wish to use parameters, and the FieldName property of the SQLRecord must be left empty.")
        End If
    End Sub

    Public Function SqlExecSPReturn(ByVal SPCommand As String) As DataSet
        Try
            strSQL.Append("EXEC ")
            strSQL.Append(SPCommand)
            If UseParams Then
                strSQL.Append(" '")
                For Each myRecord As SqlRecord In MyBase.InnerList
                    strSQL.Append(myRecord.FieldValue)
                    strSQL.Append("', '")
                Next
                strSQL.Remove(strsql.Length - 3, 3)
            End If
            Dim mySqlAdaptor As New SqlDataAdapter(strSQL.ToString, myConn)
            Dim myDataSet As New DataSet
            mySqlAdaptor.Fill(myDataSet)
            Return myDataSet
        Catch ex As Exception
            Throw New Exception("There is a fault with the database connection. The connection string is: " & myConn.ConnectionString & ": " & ex.Message)
        End Try
    End Function

    Public Sub SqlExecSP(ByVal SPCommand As String)
        Try
            myConn.Open()
        Catch ex As Exception
            Throw New Exception("There is a fault with the database connection. The connection string is: " & myConn.ConnectionString & ": " & ex.Message)
        End Try
        myCmd.Connection = myConn
        strsql.Append("EXEC ")
        strsql.Append(SPCommand)
        If UseParams Then
            strSQL.Append(" '")
            For Each myRecord As SqlRecord In MyBase.InnerList
                strSQL.Append(myRecord.FieldValue)
                strSQL.Append("', '")
            Next
            strSQL.Remove(strsql.Length - 3, 3)
        End If
        myCmd.CommandText = strsql.ToString
        Try
            myCmd.ExecuteNonQuery()
        Catch ex As Exception
            If myConn.State = ConnectionState.Open Then myConn.Close()
            Throw New Exception("There was a problem executing the query: " & strSQL.ToString & ": " & ex.Message)
        End Try
        myConn.Close()
    End Sub

End Class
Expand

Class: SqlRecord

VB
' ***************************************************************
' * Copyright © 2005 Russell Carter                             *
' *                                                             *
' * Written by Russell Carter - July 2005                       *
' *                                                             *
' * Please see AssemblyInfo for Version info. This code         *
' * may not be used or copied without prior permission          *
' * from Russell Carter.                                        *
' *                                                             *
' ***************************************************************

Public Class SqlRecord

    Private myField As String
    Private myValue As String
    Private myDataType As SQLDataType
    Private IsNull As Boolean = False

    Sub New()
    End Sub

    Public Sub New(ByVal FieldName As String, ByVal FieldValue As String, Optional ByVal IsDatabaseNull As Boolean = False)
        IsNull = IsDatabaseNull
        myField = FieldName
        If Not (FieldValue <> "" And IsNull) Then
            myValue = FieldValue
        Else
            Throw New Exception("This SQLRecord has been set to NULL. The FieldValue should be set to an empty string.")
        End If
    End Sub

    Public Property DataType() As SQLDataType
        Get
            Return myDataType
        End Get
        Set(ByVal Value As SQLDataType)
            myDataType = Value
        End Set
    End Property

    Public Property Fieldname() As String
        Get
            Return myField
        End Get
        Set(ByVal Value As String)
            myField = Value
        End Set
    End Property

    Public Property FieldValue() As String
        Get
            Return myValue
        End Get
        Set(ByVal Value As String)
            If IsNull Then
                Throw New Exception("This SQLRecord has been set to NULL. The FieldValue should be set to an empty string.")
            Else
                myValue = Value
            End If
        End Set
    End Property

    Public Property IsDatabaseNull() As Boolean
        Get
            Return IsNull
        End Get
        Set(ByVal Value As Boolean)
            IsNull = Value
        End Set
    End Property

    Public Enum SQLDataType As Integer
        Bit
        Int
        VarChar
        Datetime
    End Enum


End Class
Expand

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *