Navigation

	Home Page
	Index
	All Pages

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

SqlBuilder Class

Imports System.Collections.Generic

Public Class SqlBuilder

    Private _dict As Dictionary(Of String, String)
    Private _tableName As String
    Private _pkField As String

    Public Sub New(ByVal tableName As String, ByVal pkField As String)

        _dict = New Dictionary(Of String, String)()
        _tableName = tableName
        _pkField = pkField

    End Sub
    Public Sub Add(ByVal fieldName As String, ByVal fieldValue As Object)

        Dim s As String = fieldValue.ToString()

        If TypeOf fieldValue Is String Then
            s = "'" & s.Replace("'", "''") & "'"
        End If

        _dict.Add(fieldName, s)

    End Sub
    Public Function FieldNames(Optional ByVal includePk As Boolean = False) As String

        Dim result As String = ""
        For Each fieldName As String In _dict.Keys
            If fieldName <> _pkField Or includePk Then
                If result.Length > 0 Then
                    result &= ", "
                End If
                result &= fieldName
            End If
        Next

        Return result

    End Function
    Public Function FieldValues(Optional ByVal includePk As Boolean = False) As String

        Dim result As String = ""

        For Each fieldName As String In _dict.Keys
            If fieldName <> _pkField Or includePk Then
                If result.Length > 0 Then
                    result &= ", "
                End If
                result &= fieldName
                result &= " = "
                result &= _dict(fieldName)
            End If
        Next

        Return result

    End Function

    Public ReadOnly Property ExistsSql() As String
        Get

            Dim sb As StringBuilder = New StringBuilder()
            sb.Append("select 2 from ")
            sb.Append(_tableName)
            sb.Append(" where ")
            sb.Append(_pkField)
            sb.Append(" = ")
            sb.Append(_dict(_pkField))
            Return sb.ToString()

        End Get
    End Property

    Public Function InsertSql(ByVal includePk As Boolean)

        Dim sb As StringBuilder = New StringBuilder()

        sb.Append("insert into ")
        sb.Append(_tableName)
        sb.Append(" (")
        sb.Append(FieldNames(includePk))
        sb.Append(") select ")
        sb.Append(FieldValues(includePk))

        Return sb.ToString()

    End Function

    Public ReadOnly Property UpdateSql() As String
        Get

            Dim sb As StringBuilder = New StringBuilder()

            sb.Append("update ")
            sb.Append(_tableName)
            sb.Append(" set ")
            sb.Append(FieldValues)
            sb.Append(" where ")
            sb.Append(_pkField)
            sb.Append(" = ")
            sb.Append(_dict(_pkField))

            Return sb.ToString()

        End Get
    End Property

    Public Function UpsertSql(ByVal insertPk As Boolean) As String

        Dim sb As StringBuilder = New StringBuilder()
        sb.Append("if exists (")
        sb.Append(ExistsSql)
        sb.Append(") ")
        sb.Append(UpdateSql)
        sb.Append(" else ")
        sb.Append(InsertSql(insertPk))
        Return sb.ToString()

    End Function

End Class