生成 SQL Select, Insert, Update 和 Delete

• 下载源代码和演示文件 - 121 Kb

引言

通过选择 SQL 数据库连接并选择一个表，生成 SQL Select 查询、Insert 查询、Update 查询和 Delete 查询，以便在类或 DataCommand 中使用。

背景

我想要生成这个的原因是，某些表中有很多字段，我需要查看表来记住有哪些字段。有了这个生成的脚本，我可以使代码更快，因为我只需要传递一个表，它就会获得 Select、Insert、Update 或 Delete 查询。

这篇文章的源代码使用一个简单的函数来生成 SQL Server 数据库的表。

(注意：这篇文章的代码只适用于 SQL Server 2000 数据库)。

入门

1. 当你第一次运行该项目时。它将显示 SetupDBForm。只需输入您想要为其生成脚本的数据库的 SQL 连接信息。



2. 连接成功后。您将看到一个 MainForm 用于生成 SQL 脚本。
3. 在左下角选择您想要生成 SQL 脚本的表。
4. 如果您想生成 SQL Insert 脚本，只需单击主菜单左上角的 Select 选项即可获取所选数据库中的数据。
5. 您也可以单击其他选项，结果将如下面的示例图片所示。

   

6. 您可以在主菜单中保存结果或复制到剪贴板。

使用代码：SQLScriptNetModule.vb

ConnectionData 类用于连接到 SQL Server。使用这个类，它还可以将上次设置保存到/从 Windows 注册表中。

    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
    

此GetTableList()函数将连接的 SQL 数据库中的所有表获取到 ArrayList 对象中。

    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

下面其他函数用于生成 SQL Select、Insert、Update 和 Delete 脚本。

    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

结论

我一直是 CodeProject 的粉丝，这是我的第一篇文章。我希望它能在您使用 SQL Server 数据库部署代码时帮助到你们。如果您有任何改进建议，请告诉我。

历史

版本 1. 提交于 2006 年 11 月 13 日。

版本 1.1 提交于 2006 年 11 月 19 日。