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

深藏阁楼爱情的钟 2022-01-05 04:17 180阅读 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