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.