获取数据库表结构和表数据的小程序(VB.NET版本)

          以前在项目实施过程中经常要到客户方去进行调查。由于客户的生产网络环境是封闭的，而且不能去查看真实的数据库，因此做了个小程序来采集数据库中指定表的数据和指定表的结构。代码如下：

          'GetTableStuct  获取表的结构
    Private Function GetDataTableSchame(ByVal strTableName As String) As DataSet

        On Error GoTo theError

        InitialConnectionString()
        Dim dba As New SqlDataProvider.DataBaseAccess(con_SPEC_DB)


        Dim dsTableSchame As New DataSet
        Dim strSql As String
        'Get Table Schame
        strSql = "SELECT syscolumns.name as columnname,systypes.name as columntype,syscolumns.isnullable," & _
                 "syscolumns.length FROM syscolumns, systypes  " & _
                 "WHERE(syscolumns.xusertype = systypes.xusertype) " & _
                 " AND syscolumns.id = object_id('" & strTableName & "') "

        dsTableSchame = dba.FillDataset(strSql, CommandType.Text)

        Return dsTableSchame
        Exit Function
theError:
        MessageBox.Show("获取表" & strTableName & "的结构失败！")
        Return Nothing
        Exit Function

    End Function


    'GetTableData  获取表的数据
    Private Function GetTableData(ByVal strTableName As String) As DataSet

        On Error GoTo theError

        InitialConnectionString()
        Dim dba As New SqlDataProvider.DataBaseAccess(con_SPEC_DB)


        Dim dsTableData As New DataSet
        Dim strSql As String
        'Get Table Schame
        strSql = "select  * from  " & strTableName

        dsTableData = dba.FillDataset(strSql, CommandType.Text)

        Return dsTableData
        Exit Function
theError:
        MessageBox.Show("获取表" & strTableName & "的数据失败！")
        Return Nothing
        Exit Function

    End Function

'将表的结构写入到文件中

 Private Function WriteTableStruct(ByVal strTableName As String, ByVal fs As FileStream, ByVal sw As StreamWriter, ByVal ds As DataSet) As Boolean

        If ds Is Nothing Then
        Else
            sw.WriteLine(ds.Tables(0).Columns(0).ColumnName & "," & ds.Tables(0).Columns(1).ColumnName & "," & _
                ds.Tables(0).Columns(2).ColumnName & "," & ds.Tables(0).Columns(3).ColumnName)
            Dim i As Int16
            For i = 0 To ds.Tables(0).Rows.Count - 1
                sw.WriteLine(ds.Tables(0).Rows(i)(0).ToString() & "," & ds.Tables(0).Rows(i)(1).ToString() & "," & _
                ds.Tables(0).Rows(i)(2).ToString() & "," & ds.Tables(0).Rows(i)(3).ToString())
            Next
            sw.WriteLine("--------------------------------------")
            sw.WriteLine()
        End If

    End Function

'将表的数据写入到文件中
    Private Function WriteTableData(ByVal strTableName As String, ByVal fs As FileStream, ByVal sw As StreamWriter, ByVal ds As DataSet) As Boolean

        If ds Is Nothing Then
        Else
            Dim j As Int16
            For j = 0 To ds.Tables(0).Columns.Count - 1
                If j = ds.Tables(0).Columns.Count - 1 Then
                    sw.Write(ds.Tables(0).Columns(j).ColumnName)
                    sw.WriteLine()
                Else
                    sw.Write(ds.Tables(0).Columns(j).ColumnName & ",")
                End If
            Next

            Dim i As Int16
            For i = 0 To ds.Tables(0).Rows.Count - 1
                For j = 0 To ds.Tables(0).Columns.Count - 1
                    If j = ds.Tables(0).Columns.Count - 1 Then
                        sw.Write(ds.Tables(0).Rows(i)(j).ToString())
                        sw.WriteLine()
                    Else
                        sw.Write(ds.Tables(0).Rows(i)(j).ToString() & ",")
                    End If
                Next
            Next
            sw.WriteLine("--------------------------------------")
            sw.WriteLine()
        End If

    End Function

‘调用上面程序的Demo代码如下：

’获取表结构的代码

Private Sub btnGetTableStruct_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetTableStruct.Click

        On Error GoTo theError

        Dim strPath As String
        strPath = System.Windows.Forms.Application.StartupPath

        'Create New File
        Dim strOutputFileName As String
        strOutputFileName = "TableStruct-" + DateTime.Now.ToString("yyyyMMddhhmmss")
        strOutputFileName = strPath & "\" & strOutputFileName
        Dim fs As System.IO.FileStream = New System.IO.FileStream(strOutputFileName & ".csv", IO.FileMode.CreateNew, IO.FileAccess.Write)
        Dim sw As System.IO.StreamWriter = New System.IO.StreamWriter(fs)

        Dim ds As New DataSet
        ds = Nothing
        Dim strTableName As String
        strTableName = "Test"
        sw.WriteLine(strTableName & "表结构如下：")
        ds = GetDataTableSchame(strTableName)
        WriteTableStruct(strTableName, fs, sw, ds)
        ds.Clear()

        sw.Close()
        fs.Close()

        MessageBox.Show("生成表结构文件成功！")
        Exit Sub

theError:
        MessageBox.Show("生成表结构文件失败！")
        sw.Close()
        fs.Close()
        Exit Sub


    End Sub

'获取表数据的代码

Private Sub btnGetTableData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click

        On Error GoTo theError

        Dim strPath As String
        strPath = System.Windows.Forms.Application.StartupPath

        'Create New File
        Dim strOutputFileName As String
        strOutputFileName = "TableData-" + DateTime.Now.ToString("yyyyMMddhhmmss")
        strOutputFileName = strPath & "\" & strOutputFileName
        Dim fs As System.IO.FileStream = New System.IO.FileStream(strOutputFileName & ".csv", IO.FileMode.CreateNew, IO.FileAccess.Write)
        Dim sw As System.IO.StreamWriter = New System.IO.StreamWriter(fs)

        Dim ds As New DataSet
        ds = Nothing
        Dim strTableName As String
        strTableName = "Test"
        sw.WriteLine(strTableName & "表数据如下：")
        ds = GetTableData(strTableName)
        WriteTableData(strTableName, fs, sw, ds)
        ds.Clear()

         sw.Close()
        fs.Close()

        MessageBox.Show("生成表数据文件成功！")
        Exit Sub

theError:
        MessageBox.Show("生成表数据文件失败！")
        sw.Close()
        fs.Close()
        Exit Sub

    End Sub