获取数据库表结构和表数据的小程序(VB.NET版本)
来源:互联网 发布:淘宝怎么自己做链接 编辑:程序博客网 时间:2024/06/04 18:10
以前在项目实施过程中经常要到客户方去进行调查。由于客户的生产网络环境是封闭的,而且不能去查看真实的数据库,因此做了个小程序来采集数据库中指定表的数据和指定表的结构。代码如下:
'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
- 获取数据库表结构和表数据的小程序(VB.NET版本)
- 获取数据库表结构和表数据的小程序(VB.NET版本)
- vb.net 获取当前程序的版本(软件自己的版本)
- 将数据库表结构转化为实体类(适用于vb.net和C#.net)
- VB.net 简单程序的结构 (注意和VB过程的区别)
- ADO.NET的结构,提供程序和数据连接,执行数据库命令Command对象
- 通过文件结构直接生成xls文件的VB.Net和Java两个版本
- vb.net获取文件版本
- 数据库表结构的获取
- vb.net的关键字和结构体
- [VB.NET]如何在vb。net中打开一个excel表 并获取数据阿
- [VB.NET]数据库表的初始化问题
- listview绑定数据库的表VB.NET
- mysql 获取已知数据库的表名和表结构
- 分析数据库版本不一致的小程序
- vb.net获取程序图标
- 获取表结构的小技巧
- VB.net版本的数据库访问类DataBaseAccess
- iOS运行回路(RunLoop)总结
- WebLogic 10.3.5.0 集群环境配置(二)
- Solaris下如何编译生成动态库
- C#通讯编程
- linux系统 samba服务器文件配置
- 获取数据库表结构和表数据的小程序(VB.NET版本)
- memory pool资料
- 互换两个变量 i,j的值
- CAML语法-Query写法
- [记录]编程语言翻译
- gleffect
- C#中的事件代理
- Flash的事件机制
- 给动态数组增加元素