VB.NET 连接SQL数据库读取/处理数据

 Imports System

'连接数据库并查询/处理数据
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Sql

Public ErrorMsg As String

    '连接数据库并查询/处理数据
    '(strSql:SQL语句SELECT/INSERT/DELETE/UPDATE,strTableName:查询后表名，errmsg:返回错误信息)
    Public Function ExecuteSQL(ByVal strSql As String, ByVal strTableName As String, ByRef errmsg As String) As System.Data.DataSet
        On Error GoTo Err

        ExecuteSQL = Nothing
        errmsg = ""
        Dim SplitSQL As String = Mid(strSql, 1, InStr(strSql, " ") - 1)
        Dim Rst As New DataSet
        Dim oCn As SqlClient.SqlConnection
        Dim MyComm As SqlClient.SqlCommand
        Dim MyAdapter As SqlClient.SqlDataAdapter
        Dim MyDataset As New DataSet

        Dim sCnStr As String = "data source=192.168.2.7;initial catalog=DBData;user id=sa;password=111111;"
        oCn = New System.Data.SqlClient.SqlConnection(sCnStr)
        oCn.Open()              '打开数据库
        If InStr("INSERT,DELETE,UPDATE", UCase$(SplitSQL)) Then
            MyComm = New SqlCommand(strSql, oCn)
            MyComm.ExecuteNonQuery()
        Else
            MyAdapter = New SqlClient.SqlDataAdapter(strSql, oCn)
            MyAdapter.Fill(MyDataset, strTableName)
            MyAdapter = Nothing
            ExecuteSQL = MyDataset
        End If
        oCn.Close()              '关闭连接，释放资源

 

        Exit Function
Err:
        errmsg = "ExecuteSQL:" & "错误编号：" & Err.Number & "错误停息：" & Err.Description
        ExecuteSQL = Nothing
    End Function

 

 

 

'应用

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim MyDataset As New DataSet
        Dim inti As Double

        '查询数据库里的代码
        MyDataset = ExecuteSQL("Select  * From Remittances_List_Kr Order By sDate", "ABC", ErrorMsg)
        For inti = 0 To MyDataset.Tables("ABC").Rows.Count - 1

             msgbox(Trim(MyDataset.Tables.Item("ABC").Rows(inti).Item("ID")) )

        Next
        MyDataset = Nothing

    End Sub