Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

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

Other Wiki Sections

Software

PoweredBy

SqlBuilder Class

RSS
Modified on Fri, Nov 20, 2009, 3:53 PM by Administrator Categorized as (drafts), Oracle, SQL Server
This page is a Draft. Its content is not complete and might contain errors.

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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.