Generate SQL Select, Insert, Update and Delete

• Download source & demo files - 121 Kb

Introduction

This will generate a SQL Select Query, Insert Query, Update Query and Delete Query by selecting SQL Database Connection and Choose a table to generate the script for using in class or DataCommand.

Background

Why I want to generate this, because there are so many field in some table that I should look at the table to remember what field was in. With this generate script, I can make a code more faster because all I need is just pass a table and it will get a Select, Insert, Update or Delete Query.

The source code for this article uses a simple function to generate table of a SQL Server database.

(Note: the code for this article will only work for SQL Server 2000 databases).

Getting Started

1. When you first run the project. It will show the SetupDBForm. Just enter the SQL connection information for the database you would like to generate scripts for.



2. After the connection is successfull. You will see a MainForm for generate SQL script.
3. Choose which table(s) you want to generate SQL Script at the bottom left.
4. If you want to generate SQL Insert scripts just click the Select option at the top left Main Menu for the data in the selected database.
5. You can also click for other option and the result would be like below sample picture.

   

6. You can save the result or copy to clipboard in main menu.

Using the code : SQLScriptNetModule.vb

The ConnectionData class is constructed to connect to SQL Server. WIth this class, it can also save and get the last setting to/from windows registry.

    Class ConnectionData
        Public ServerName As String
        Public DatabaseName As String
        Public Security As String
        Public UserID As String
        Public Password As String
        Public sProd As String = My.Application.Info.ProductName

        Public Function CheckConnection() As Boolean
            Dim oConn As New SqlConnection
            If Security = "WIN" Then
                strConn = "Persist Security Info=False;Integrated Security=SSPI;" & _
                    "Initial Catalog=" & DatabaseName & ";Server=" & ServerName
            Else
                strConn = "UID=" & UserID & ";Password=" & Password & ";Database=" & _
                    DatabaseName & ";Server=" & ServerName & ";"
            End If
            Try
                oConn.ConnectionString = strConn
                oConn.Open()
                oConn.Close()
                Return True
            Catch ex As Exception
                Return False
            End Try
        End Function

        Public Sub SaveToRegistry()
            SaveSetting(sProd, "Setup", "Server", ServerName)
            SaveSetting(sProd, "Setup", "Database", DatabaseName)
            SaveSetting(sProd, "Setup", "Security", Security)
            SaveSetting(sProd, "Setup", "UserID", UserID)
            SaveSetting(sProd, "Setup", "Password", Password)
        End Sub

        Public Sub GetFromRegistry()
            ServerName = GetSetting(sProd, "Setup", "Server", "(local)")
            DatabaseName = GetSetting(sProd, "Setup", "Database", "UNKNOWN")
            Security = GetSetting(sProd, "Setup", "Security", "WIN")
            UserID = GetSetting(sProd, "Setup", "UserID", "sa")
            Password = GetSetting(sProd, "Setup", "Password", "")
        End Sub
    End Class

    Public strConn As String
    Public cnnCom As SqlConnection
    Public cdConn As ConnectionData

    Public Function OpenConnection() As Boolean
        cnnCom = New SqlConnection
        cnnCom.ConnectionString = strConn
        Try
            cnnCom.Open()
            Return True
        Catch ex As Exception
            MsgBox("Connection Failed!", MsgBoxStyle.Critical)
            Return False
        End Try
    End Function

    Public Sub CloseConnection()
        If cnnCom Is Nothing AndAlso cnnCom.State = ConnectionState.Open Then
            cnnCom.Close()
            cnnCom = Nothing
        End If
    End Sub
    

This GetTableList() function get all the tables in connected SQL database to ArrayList object.

    Public Function GetTableList() As ArrayList
        If Not OpenConnection() Then Return Nothing
        Dim arrReturn As New ArrayList
        Dim oDA As New SqlDataAdapter("SELECT * FROM sysobjects " & _
            "WHERE (xtype = 'U') AND ([name]<>'dtproperties') ORDER BY [name]", cnnCom)
        Dim oDS As New DataSet
        oDA.Fill(oDS)
        CloseConnection()
        Dim oDR As DataRow
        If oDS.Tables(0).Rows.Count > 0 Then
            For Each oDR In oDS.Tables(0).Rows
                arrReturn.Add(oDR("name"))
            Next
            Return arrReturn
        Else
            Return Nothing
        End If
    End Function

Below other functions is constructed to generate SQL Select, Insert, Update and Delete Script.

    Public Function GetSelectTag(ByVal sTable As String, _
        Optional ByVal bFieldOnly As Boolean = False) As String
        If Not OpenConnection() Then Return Nothing
        Dim oDA As New SqlDataAdapter("SELECT TOP 1 * FROM " & sTable, cnnCom)
        Dim oDS As New DataSet
        oDA.Fill(oDS, sTable)
        CloseConnection()
        Dim oDCC As DataColumnCollection
        oDCC = oDS.Tables(sTable).Columns
        Dim sResult As String = ""
        Dim oDC As DataColumn
        If Not bFieldOnly Then sResult = "SELECT "
        For Each oDC In oDCC
            sResult = sResult & oDC.ColumnName & ", "
        Next
        sResult = Left(sResult, Len(sResult) - 2)
        If Not bFieldOnly Then sResult = sResult & " FROM " & sTable
        Return sResult
    End Function

    Public Function GetInsertTag(ByVal sTable As String) As String
        If Not OpenConnection() Then Return Nothing
        Dim oDA As New SqlDataAdapter("SELECT TOP 1 * FROM " & sTable, cnnCom)
        Dim oDS As New DataSet
        oDA.Fill(oDS, sTable)
        Dim oDCC As DataColumnCollection
        oDCC = oDS.Tables(sTable).Columns
        Dim sResult As String = "", sValue As String = ""
        Dim oDC As DataColumn
        Dim sColName As String
        sResult = "INSERT INTO " & sTable & "("
        sValue = " VALUES ("
        For Each oDC In oDCC
            sColName = oDC.ColumnName
            If Not oDC.AutoIncrement() Then
                If UCase(sColName) <> "ID" Then
                    sResult = sResult & sColName & ", "
                    sValue = sValue & "@" & sColName & ", "
                End If
            End If
        Next
        sResult = Left(sResult, Len(sResult) - 2) & ")"
        sValue = Left(sValue, Len(sValue) - 2) & ")"
        sResult = sResult & sValue
        CloseConnection()
        Return sResult
    End Function

    Public Function GetUpdateTag(ByVal sTable As String, _
        Optional ByVal nKey As Integer = 1) As String
        If Not OpenConnection() Then Return Nothing
        Dim oDA As New SqlDataAdapter("SELECT TOP 1 * FROM " & sTable, cnnCom)
        Dim oDS As New DataSet
        Dim sColName As String
        oDA.Fill(oDS, sTable)
        Dim oDCC As DataColumnCollection
        oDCC = oDS.Tables(sTable).Columns
        Dim sResult As String = "", sWhere As String = ""
        Dim oDC As DataColumn
        sResult = "UPDATE " & sTable & " SET "
        For Each oDC In oDCC
            sColName = oDC.ColumnName
            If oDC.Ordinal >= nKey Then
                If UCase(sColName) <> "ID" Then
                    sResult = sResult & sColName & " = @" & sColName & ", "
                End If
            Else
                sWhere = sWhere & sColName & " = @" & sColName & " " & _
                    IIf(oDC.Ordinal < nKey - 1, "AND ", "")
            End If
        Next
        sResult = Left(sResult, Len(sResult) - 2) & _
            IIf(Len(sWhere) > 0, " WHERE " & sWhere, "")
        CloseConnection()
        Return sResult
    End Function

    Public Function GetDeleteTag(ByVal sTable As String) As String
        If Not OpenConnection() Then Return Nothing
        Dim oDA As New SqlDataAdapter("SELECT TOP 1 * FROM " & sTable, cnnCom)
        Dim oDS As New DataSet
        oDA.Fill(oDS, sTable)
        Dim oDCC As DataColumnCollection
        oDCC = oDS.Tables(sTable).Columns
        Dim sResult As String = "", sWhere As String = ""
        Dim oDC As DataColumn
        sResult = "DELETE FROM " & sTable & " "
        oDC = oDCC(0)
        sWhere = sWhere & oDC.ColumnName & " = @" & oDC.ColumnName
        sResult &= " WHERE " & sWhere
        CloseConnection()
        Return sResult
    End Function

Conclusion

I've been a fan of CodeProject for some time now and this is my very first article. I hope it will help many of you out when it comes to deploying your code by using SQL Server databases. If you have any suggestions for improvements, please let me know.

History

Version 1. Submitted on 13th November 2006.

Version 1.1 Submitted on 19th November 2006.