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

首先，需引用

Imports System.Data.SqlClient.SqlExceptionImports 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, ByVal strSql As String, 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 DataSetIf pubMyExecuteQuery(pubSqlCommand, strSQL, Err, dsTable) = False ThenMsgBox("读取数据失败!" & vbCrLf & Err, MsgBoxStyle.Exclamation, "提示")Exit SubEnd IfFor Each pRow As DataRow In dsTable.Tables(0).Rows'用pRow("id").ToString 展示出每一个数据集内容Next

注：使用前需要调用一次pubInit这个过程。