Imports Microsoft.VisualBasic
Imports System.Data                     ' DataTable
Imports System.Data.Common              ' DataTableMapping
Imports System.Data.SqlClient           ' SqlDataAdapter
Imports System.web.Configuration        ' WebConfigurationManager
'#Const HARD_WAY = 1
Public Class DisconnectedData2
    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("StudentDataSet2")
        _dAdapter = context.Session("StudentDataAdapter2")

        If _dAdapter Is Nothing Then
            Dim params As SqlParameterCollection

            _dAdapter = New SqlDataAdapter()

            _dAdapter.SelectCommand = New SqlCommand("SELECT * FROM Student; SELECT * FROM Module", conn)

            _dAdapter.InsertCommand = New SqlCommand("INSERT Student(Name,Age,Course) VALUES(@Name,@Age,@Course)", conn)
            params = _dAdapter.InsertCommand.Parameters
            params.Add("@Name", SqlDbType.NVarChar, 0, "Name")
            params.Add("@Age", SqlDbType.Int, 0, "Age")
            params.Add("@Course", SqlDbType.NVarChar, 0, "Course")

            _dAdapter.UpdateCommand = New SqlCommand("UPDATE Student SET Name=@Name,Age=@Age,Course=@Course WHERE Id=@Id", conn)
            params = _dAdapter.UpdateCommand.Parameters
            params.Add("@Name", SqlDbType.NVarChar, 0, "Name")
            params.Add("@Age", SqlDbType.Int, 0, "Age")
            params.Add("@Course", SqlDbType.NVarChar, 0, "Course")
            params.Add("@Id", SqlDbType.Int, 0, "Id")

            ' Can also add DeleteCommand parameters

            context.Session("StudentDataAdapter2") = _dAdapter
        End If

        If _dSet Is Nothing Then
            Dim dStudentTable As DataTable
            Dim dModuleTable As DataTable

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

            ' Pick the database tables we want to use.
            ' The recordset are numbered Table, Table1, Table2, etc
            '
            _dAdapter.TableMappings.Add("Table", "Student")
#If HARD_WAY Then
            _dAdapter.TableMappings.Add("Table1", "Module")
#Else
            Dim dTableMap As DataTableMapping

            dTableMap = _dAdapter.TableMappings.Add("Table1", "Module")

            ' The Student.Name and Module.Name fields have the same name
            ' so we add a new column to the Module table so we can
            ' rename it to ModuleName
            '
            dTableMap.ColumnMappings.Add("Name", "ModuleName")
#End If
            _dAdapter.Fill(_dSet)

            ' Configure the auto-numbered primary key record fields.
            ' 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.
            '
            dStudentTable = _dSet.Tables("Student")
            With dStudentTable.Columns("Id")
                .AutoIncrement = True
                .AutoIncrementSeed = -1
                .AutoIncrementStep = -1
            End With
            dStudentTable.PrimaryKey = New DataColumn() {dStudentTable.Columns("Id")}

            dModuleTable = _dSet.Tables("Module")
            With dModuleTable.Columns("Id")
                .AutoIncrement = True
                .AutoIncrementSeed = -1
                .AutoIncrementStep = -1
            End With
            dModuleTable.PrimaryKey = New DataColumn() {dModuleTable.Columns("Id")}

            ' Setup a relationship between the primary and foreign key.
            ' The parent table contains the primary key.
            '
            _dSet.Relations.Add("StudentModuleRelation", dModuleTable.Columns("Id"), dStudentTable.Columns("ModuleId"))

            ' One way to create a join between the Student and Module tables is to code a
            ' third table and copy the record fields required into it.
            ' This is a bit long winded, so you might be tempted to use dTable.Merge() call
            ' but this just copies the contents of one row to another where the primary
            ' keys match (or just adds a new row).
            ' However, there is an alternative approach which is to use an expression based
            ' data column. But to get this to work, you must make sure all the fields
            ' have different names.
            '
#If HARD_WAY Then
            Dim dJoinTable As DataTable
            Dim dStudent As DataRow
            Dim dRow As DataRow
            Dim dView As DataView

            dJoinTable = New DataTable("StudentModule")

            dJoinTable.Columns.Add("Id", Type.GetType("System.Int32"))
            dJoinTable.Columns.Add("Name", Type.GetType("System.String"))
            dJoinTable.Columns.Add("Age", Type.GetType("System.Int32"))
            dJoinTable.Columns.Add("Course", Type.GetType("System.String"))
            dJoinTable.Columns.Add("Module", Type.GetType("System.String"))

            With dJoinTable.Columns("Id")
                .AutoIncrement = True
                .AutoIncrementSeed = -1
                .AutoIncrementStep = -1
            End With
            dJoinTable.PrimaryKey = New DataColumn() {dJoinTable.Columns("Id")}

            For Each dStudent In dStudentTable.Rows
                If IsDBNull(dStudent("ModuleId")) = True Then
                    dRow = dJoinTable.NewRow()
                    dRow("Id") = dStudent("Id")
                    dRow("Name") = dStudent("Name")
                    dRow("Age") = dStudent("Age")
                    dRow("Course") = dStudent("Course")
                    dRow("Module") = ""
                    dJoinTable.Rows.Add(dRow)
                End If
            Next

            Dim dModule As DataRow
            For Each dModule In dModuleTable.Rows
                Dim dStudents() As DataRow = dModule.GetChildRows("StudentModuleRelation")
                For Each dStudent In dStudents
                    dRow = dJoinTable.NewRow()
                    dRow("Id") = dStudent("Id")
                    dRow("Name") = dStudent("Name")
                    dRow("Age") = dStudent("Age")
                    dRow("Course") = dStudent("Course")
                    dRow("Module") = dModule("Name")
                    dJoinTable.Rows.Add(dRow)
                Next
            Next

            _dSet.Tables.Add(dJoinTable)

            dView = dJoinTable.DefaultView
            dView.Sort = "Name"
#Else
            ' Add a new column to the student table and copy data from the previously
            ' create relation, using an expression based data column. Make sure
            ' the fields in both tables have different names, changes the names
            ' if necessary using ColumnMappings.
            '
            dStudentTable.Columns.Add("ModuleName", Type.GetType("System.String"), "Parent(StudentModuleRelation).ModuleName")
#End If
        End If

    End Sub

#If HARD_WAY Then
    Public Function SelectData() As DataView
        Return _dSet.Tables("StudentModule").DefaultView
    End Function
#Else
    Public Function SelectData() As DataTable
        Return _dSet.Tables("Student")
    End Function
#End If

    Public Sub InsertData(ByVal Name As String, ByVal Age As Integer, ByVal Course As String)
        Dim dTable As DataTable
        Dim dRow As DataRow
#If HARD_WAY Then
        dTable = _dSet.Tables("StudentModule")
#Else
        dTable = _dSet.Tables("Student")
#End If
        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
#If HARD_WAY Then
        dTable = _dSet.Tables("StudentModule")
#Else
        dTable = _dSet.Tables("Student")
#End If
        dRow = dTable.Rows.Find(Id)
        dRow("Name") = Name
        dRow("Age") = Age
        dRow("Course") = Course
    End Sub

    Public Sub UpdateComplete()
        Dim dTable As DataTable

#If HARD_WAY Then
        dTable = _dSet.Tables("StudentModule")
#Else
        dTable = _dSet.Tables("Student")
#End If
        ' 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