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

最新推荐文章于 2024-06-02 07:30:00 发布

骷髅会-北极熊

最新推荐文章于 2024-06-02 07:30:00 发布

阅读量1.5k

点赞数 1

分类专栏：数据库文章标签： sql server vb.net 数据库服务器数据库

本文链接：https://blog.csdn.net/wang19850219/article/details/41440521

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

首先，需引用

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