Imports Microsoft.VisualBasic
Imports System.Data                     ' DataTable
Imports System.Data.SqlClient           ' SqlDataAdapter
Imports System.web.Configuration        ' WebConfigurationManager

Public Class DisconnectedData
    Private _dAdapter As SqlDataAdapter
    Private _dSet As DataSet

    Public Sub New()
        Dim context As HttpContext
        Dim conn As SqlConnection

        conn = New SqlConnection(WebConfigurationManager.ConnectionStrings("studentdbconnstring").ConnectionString)

        context = HttpContext.Current
        _dSet = context.Session("StudentDataSet")
        _dAdapter = context.Session("StudentDataAdapter")

        If _dAdapter Is Nothing Then
            Dim builder As SqlCommandBuilder
            _dAdapter = New SqlDataAdapter("SELECT * FROM Student", conn)
            builder = New SqlCommandBuilder(_dAdapter)
            context.Session("StudentDataAdapter") = _dAdapter
        End If

        If _dSet Is Nothing Then
            Dim dTable As DataTable

            _dSet = New DataSet
            context.Session("StudentDataSet") = _dSet

            _dAdapter.TableMappings.Add("Table", "Student")
            _dAdapter.Fill(_dSet)

            ' Configure the auto-numbered primary key record field.
            ' The IDs we are adding won't be written to the database, we use
            ' negative values when we add new rows to the DataTable, so that 
            ' we don't duplicate values created by the database itself.
            '
            dTable = _dSet.Tables("Student")
            With dTable.Columns("Id")
                .AutoIncrement = True
                .AutoIncrementSeed = -1
                .AutoIncrementStep = -1
            End With
            dTable.PrimaryKey = New DataColumn() {dTable.Columns("Id")}

        End If

    End Sub

    Public Function SelectData(ByVal filterstring As String) As DataView
        Dim dView As DataView

        dView = New DataView(_dSet.Tables("Student"))

        If filterstring Is Nothing Then ' Null is Nothing in VB.Net
            Return dView
        Else
            Dim sb As New StringBuilder
            sb.Append("Name LIKE")
            sb.Append("'")
            sb.Append(filterstring)
            sb.Append("%'")
            dView.RowFilter = sb.ToString

            Return dView
        End If
    End Function

    Public Sub InsertData(ByVal Name As String, ByVal Age As Integer, ByVal Course As String)
        Dim dTable As DataTable
        Dim dRow As DataRow

        dTable = _dSet.Tables("Student")
        dRow = dTable.NewRow()
        dRow("Name") = Name
        dRow("Age") = Age
        dRow("Course") = Course
        dTable.Rows.Add(dRow)
    End Sub

    Public Sub UpdateData(ByVal Name As String, ByVal Age As Integer, ByVal Course As String, ByVal Id As Integer)
        Dim dTable As DataTable
        Dim dRow As DataRow

        dTable = _dSet.Tables("Student")
        dRow = dTable.Rows.Find(Id)
        dRow("Name") = Name
        dRow("Age") = Age
        dRow("Course") = Course
    End Sub

    Public Sub UpdateComplete()
        Dim dTable As DataTable

        dTable = _dSet.Tables("Student")

        ' Write all uncommited changes in the DataTable to the database
        '
        _dAdapter.Update(dTable)

        ' Commit all the changes to the DataTable
        '
        dTable.AcceptChanges()
    End Sub

End Class