VB.net 对MSSQL操作 查、删、改 三个常规操作

首先，需引用

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这个过程。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!