vbnet 獲取資料庫表結構和表資料

參考引用來源:獲取資料庫表結構和表資料的小程式(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