获取数据库表结构和表数据的小程序(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