首先,需引用
Imports System.Data.sqlClient.sqlException Imports System.Data.sqlClient
定义全局变量
Public pubConnection As New sqlConnection Public privConDbOther As New sqlConnection Public pubsqlCommand As sqlCommand = New sqlCommand Public ServerIP As String = "." '服务器地址 Public ServerName As String = "" '服务器用户名 Public ServerPassword As String = "" '服务器密码 Public DatabaseName As String = "" '数据库服务器地址、服务器用户名、服务器密码、数据库 根据你实际情况进行付值
Public Function pubSetConnect(ByVal strHostIp As String,ByVal strDatabaseName As String,ByVal strUserName As String,ByVal strUserPassword As String) As String '生成数据库连接字符串 Return "Data Source=" & strHostIp _ & ";Database=" & strDatabaseName _ & ";Initial Catalog=" & strDatabaseName _ & ";User ID=" & strUserName _ & " ;Password =" & strUserPassword End Function
连接数据库主子程
Public Function pubInit() As Boolean '连接数据库主子程 pubConnection.Close() pubConnection.ConnectionString = pubSetConnect(ServerIP,DatabaseName,ServerName,ServerPassword) Try If pubConnection.State = ConnectionState.Closed Then pubConnection.Open() End If Catch ex As sqlClient.sqlException MsgBox(ex.Message) Exit Function Catch ex As Exception MsgBox(ex.Message) Exit Function End Try pubsqlCommand.Connection = pubConnection Return True End Function
'执行无返回值的sql语句 Public Function pubMyExecuteNonQuery(ByRef myCommand As sqlCommand,ByVal strsql As String,ByRef errMsg As String) As Boolean myCommand.Parameters.Clear() myCommand.CommandType = CommandType.Text Try myCommand.CommandText = strsql If myCommand.Connection.State = ConnectionState.Closed Then myCommand.Connection.Open() End If myCommand.ExecuteNonQuery() Catch ex As sqlException errMsg = "sql_err=" & CStr(ex.ErrorCode) & "|" & ex.Message Return False Catch ex As Exception errMsg = "other|" & ex.Message Return False End Try Return True End Function '根据传入的sql语句得到数据集 Public Function pubMyExecuteQuery(ByRef myCommand As sqlCommand,ByRef errMsg As String,ByRef dsReturn As DataSet) As Boolean myCommand.Parameters.Clear() myCommand.CommandType = CommandType.Text dsReturn.Clear() Try myCommand.CommandText = strsql If myCommand.Connection.State = ConnectionState.Closed Then myCommand.Connection.Open() End If Dim adapter As sqlDataAdapter = New sqlDataAdapter adapter.SelectCommand = myCommand adapter.Fill(dsReturn,"tmpTable") Catch ex As sqlException errMsg = "sql_err=" & CStr(ex.ErrorCode) & "|" & ex.Message Return False Catch ex As Exception errMsg = "other|" & ex.Message Return False End Try Return True End Function
使用案例如下:
Dim strsql As String = "SELECT * From Test" Dim dsTable As New DataSet If pubMyExecuteQuery(pubsqlCommand,strsql,Err,dsTable) = False Then MsgBox("读取数据失败!" & vbCrLf & Err,MsgBoxStyle.Exclamation,"提示") Exit Sub End If For Each pRow As DataRow In dsTable.Tables(0).Rows '用pRow("id").ToString 展示出每一个数据集内容 Next
注:使用前需要调用一次pubInit这个过程。