ExecuteResultSet using vb.net and sql server2008

[kdejan87]

This is writen using vb.net and sqlCe. I'm interested how this could be written in SQL server 2008 and vb.net.

Dim rs As SqlCeResultSet = cmd.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)

[Begginnerdev]

First you will need to connect to the database. (See this for connection strings.)

Note that for the following code I will be using OleDB.

I, personally, like to wrap my connection code in clean class.

'This code uses integrated security.
Public Class Connection
    Private con As New OleDb.OleDbConnection("Provider=SQLOLEDB;" & _ 
                                             "Server=MyServer;" & _
                                             "Database=MyDatabase;" & _
                                             "Integrated Security=SSPI")

    Protected Friend ReadOnly Property Open As OleDb.OleDbConnection
        Get
            If con.State = ConnectionState.Closed Then con.Open()
            Return con
        End Get
    End Property

    Protected Friend Sub Close()
        Try
            con.Close()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection_Close")
        End Try
    End Sub

    Protected Friend Sub Dispose()
        con.Dispose()
    End Sub
End Class

Second, you will need to create the dataset.

Private Function GetData() As DataTable
    Dim db As New Connection
    Try
        Dim da As New OleDBDataAdapter("SELECT * FROM MyTable",db.Open)
        Dim ds As New DataSet

        da.Fill(ds,"Data")

        If Not IsNothing(ds.Tables("Data")) Then
            If ds.Tables("Data").Rows.Count > 0 Then
                Return ds.Tables("Data")
            Else
                MsgBox("No records were returned from table...") 'Remove
                Return New DataTable
            End If
        Else
            MsgBox("No table returned...") 'Remove/Add Fault Tolerance
        End If
    Catch ex As Exception
        MsgBox(ex.Message,MsgBoxStyle.Critical,"Error!")
        Return New DataTable
    Finally
        db.Close
        db.Dispose
        db = Nothing
    End Try
End Function

Finally, you will need to issue updates to the table.

Private Function UpdateTable(ByVal dt As DataTable) As Boolean
    Dim db As New Connection
    Try
        Dim da As New OleDbDataAdapter("SELECT * FROM MyTable WHERE 1=2", db.Open)
        da.UpdateCommand = New OleDb.OleDbCommandBuilder(da).GetUpdateCommand
        da.Update(dt)

        Return True
    Catch ex As Exception
        MsgBox(ex.Message,MsgBoxStyle.Critical,"Error!")
        Return False
    Finally
        db.Close
        db.Dispose
        db = Nothing
    End Try
End Function