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

叁歲伎倆 2021-11-26 09:46 175阅读 0赞

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

'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

转载于:https://www.cnblogs.com/kevinGao/archive/2011/11/02/2233421.html