擷取資料庫表結構和表資料的小程式(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