Question

vb.net 连接sql数据库模块,如何使用？

vb.net 新手，请教各位： 在网上查了一个vb连接sql数据的类，如何调用呢？
类代码如下：
Module Module1
Public txtSQL As String '存放SQL语句
Public DBSet As DataSet '查询得到的记录集
Public ErrorMsg As String '存放错误信息
Public Function ExecuteSQL(ByVal strSQL As String, ByRef errMsg As String) As DataSet
Dim cnn As SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand()
Dim adpt As SqlClient.SqlDataAdapter
Dim rst As New DataSet()
Dim SplitSQL() As String
errMsg = ""
Try
SplitSQL = Split(strSQL)
cnn = New SqlClient.SqlConnection("data source=192.168.0.11;initial catalog=remote2;user id=sa;pwd=2013")

If InStr("INSERT,DELETE,UPDATE", UCase$(SplitSQL(0))) Then
cmd.Connection = cnn
cmd.Connection.Open()
cmd.CommandText = strSQL
cmd.ExecuteNonQuery()
Else
adpt = New SqlClient.SqlDataAdapter(strSQL, cnn)
adpt.Fill(rst)
ExecuteSQL = rst
End If
Catch ex As Exception
errMsg = ex.Message
Finally
rst = Nothing
cnn = Nothing
End Try
End Function
End Module
程序代码如下：
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
txtSQL = "SELECT * from tbinfopart where partcode='DDF1231-D'"
DBSet = ExecuteSQL(txtSQL, ErrorMsg)
TextBox1.Text = txtSQL ‘这种写法不对，得到的答案是sql语句，应该怎么写？
这种方式如何获取 记录集条数？ 如何像vb一样写循环 do while not.eof ?

Answer 1

比如有个asp.net(vb.net)连接sql server 2000数据库的连接模块(module)：

Module MdlCommon
Public txtSQL As String '存放SQL语句
Public DBSet As DataSet '查询得到的记录集
Public ErrorMsg As String '存放错误信息
Public Function ExecuteSQL(ByVal strSQL As String, ByRef errMsg As String) As DataSet
Dim cnn As SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand()
Dim adpt As SqlClient.SqlDataAdapter
Dim rst As New DataSet()
Dim SplitSQL() As String
errMsg = ""
Try
SplitSQL = Split(strSQL)
cnn = New SqlClient.SqlConnection("data source=(local);initial catalog=urp;user id=sa;pwd=1234")
If InStr("INSERT,DELETE,UPDATE", UCase$(SplitSQL(0))) Then
cmd.Connection = cnn
cmd.Connection.Open()
cmd.CommandText = strSQL
cmd.ExecuteNonQuery()
Else
adpt = New SqlClient.SqlDataAdapter(strSQL, cnn)
adpt.Fill(rst)
ExecuteSQL = rst
End If
Catch ex As Exception
errMsg = ex.Message
Finally
rst = Nothing
cnn = Nothing
End Try
End Function End Module

调用时在asp.net后台写上txtSQL="SELECT * FROM user";
DBSet=ExecuteSQL(txtSQL,ErrorMsg)就可以查询db返回结果了。

Answer 2

首先是SQL查询语句
txtSQL = "SELECT * from tbinfopart where partcode='DDF1231-D'"
然后是定义出错提示
ErrorMsg='出错拉'
再后来是将查询结果赋值给DBSet
DBSet = ExecuteSQL(txtSQL, ErrorMsg)
这个时候如果查询出错DBSet就是出错信息，如果查询成功 DBSet就是一个数据集
TextBox1.Text = DBSet.rows.count就是查询到数据的条数

Answer 3

adpt.tables("rst").rows.count 可以获取记录数。