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

Public Class DisconnectedData3
    Private _dStudentAdapter As SqlDataAdapter
    Private _dModuleAdapter As SqlDataAdapter
    Private _dStudentModuleAdapter As SqlDataAdapter
    Private _dSet As DataSet

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

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

        ' Store all the in-memory objects in a session
        ' so that they are remembered between page posts
        ' back to the web server
        '
        context = HttpContext.Current
        _dSet = context.Session("StudentDataSet3")
        _dStudentAdapter = context.Session("StudentDataAdapter3")
        _dModuleAdapter = context.Session("ModuleDataAdapter3")
        _dStudentModuleAdapter = context.Session("StudentModuleDataAdapter3")

        If _dStudentAdapter Is Nothing OrElse _dModuleAdapter Is Nothing OrElse _dStudentModuleAdapter Is Nothing Then
            Dim params As SqlParameterCollection

            ' Create three adapters to read from each table
            ' and to allow individual commands to be executed
            ' on each of them
            '
            _dStudentAdapter = New SqlDataAdapter()
            _dModuleAdapter = New SqlDataAdapter()
            _dStudentModuleAdapter = New SqlDataAdapter()

            ' Configure the commands for the StudentAdapter
            '++++++++++++++++++++++++++++++++++++++++++++++++++
            _dStudentAdapter.SelectCommand = New SqlCommand("SELECT * FROM Student;", conn)

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

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

            ' Configure the commands for the ModuleAdapter
            '++++++++++++++++++++++++++++++++++++++++++++++++++
            _dModuleAdapter.SelectCommand = New SqlCommand("SELECT * FROM Module;", conn)

            ' Configure the commands for the StudentModuleAdapter
            '++++++++++++++++++++++++++++++++++++++++++++++++++
            _dStudentModuleAdapter.SelectCommand = New SqlCommand("SELECT * FROM StudentModule", conn)

            _dStudentModuleAdapter.InsertCommand = New SqlCommand("INSERT StudentModule(StudentId,ModuleId) Values(@StudentId,@ModuleId)", conn)
            params = _dStudentModuleAdapter.InsertCommand.Parameters
            params.Add("@StudentId", SqlDbType.Int, 0, "StudentId")
            params.Add("@ModuleId", SqlDbType.Int, 0, "ModuleId")

            _dStudentModuleAdapter.UpdateCommand = New SqlCommand("UPDATE StudentModule SET ModuleId=@NewModuleId WHERE StudentId=@StudentId AND ModuleId=@OldModuleId", conn)
            params = _dStudentModuleAdapter.UpdateCommand.Parameters
            params.Add("@StudentId", SqlDbType.Int, 0, "StudentId")
            params.Add("@NewModuleId", SqlDbType.Int, 0, "ModuleId")
            params.Add("@OldModuleId", SqlDbType.Int, 0, "OldModuleId")

            ' Can also add DeleteCommand parameters

            ' Store these new adapters in the Session
            '
            context.Session("StudentDataAdapter3") = _dStudentAdapter
            context.Session("ModuleDataAdapter3") = _dModuleAdapter
            context.Session("StudentModuleDataAdapter3") = _dStudentModuleAdapter
        End If

        If _dSet Is Nothing Then
            Dim dStudentTable As DataTable
            Dim dModuleTable As DataTable
            Dim dStudentModuleTable As DataTable
            Dim dTableMap As DataTableMapping

            ' Create and store the DataSet
            '
            _dSet = New DataSet
            context.Session("StudentDataSet3") = _dSet

            ' Pick the database tables we want to use.
            ' The recordset are numbered Table, Table1, Table2, etc
            ' Note: To form a join, rename the duplicate column names
            '
            _dStudentAdapter.TableMappings.Add("Table", "Student")
            dTableMap = _dModuleAdapter.TableMappings.Add("Table", "Module")
            dTableMap.ColumnMappings.Add("Name", "ModuleName")
            _dStudentModuleAdapter.TableMappings.Add("Table", "StudentModule")

            ' Fill all the database tables using the adapter
            ' SelectCommands we create earlier.
            '
            _dStudentAdapter.Fill(_dSet)
            _dModuleAdapter.Fill(_dSet)
            _dStudentModuleAdapter.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")}

            dStudentModuleTable = _dSet.Tables("StudentModule")
            dStudentModuleTable.PrimaryKey = New DataColumn() {dStudentModuleTable.Columns("StudentId"), dStudentModuleTable.Columns("ModuleId")}

            ' Create the DataRelations
            ' Setup a relationship between the primary and foreign key.
            ' The parent table contains the primary key.
            '
            _dSet.Relations.Add("StudentRelation", dStudentTable.Columns("Id"), dStudentModuleTable.Columns("StudentId"))
            _dSet.Relations.Add("ModuleRelation", dModuleTable.Columns("Id"), dStudentModuleTable.Columns("ModuleId"))

            ' Create a join between the Student, Module and StudentModule tables .
            ' Add new columns to the StudentModule "link table" joining the Student
            ' and Module tables together.
            ' Copy data from the previously create relation, using an expression 
            ' based data column. Make sure the fields in the tables have different 
            ' names, and if necessary use ColumnMappings to rename them.
            '
            dStudentModuleTable.Columns.Add("Name", Type.GetType("System.String"), "Parent(StudentRelation).Name")
            dStudentModuleTable.Columns.Add("Age", Type.GetType("System.Int32"), "Parent(StudentRelation).Age")
            dStudentModuleTable.Columns.Add("Course", Type.GetType("System.String"), "Parent(StudentRelation).Course")
            dStudentModuleTable.Columns.Add("ModuleName", Type.GetType("System.String"), "Parent(ModuleRelation).ModuleName")

            ' We need to add two more columns, one to allow the DropDownList to
            ' pick a NewModuleId and the other to remember the OldModuleId.
            '
            dStudentModuleTable.Columns.Add("NewModuleId", Type.GetType("System.Int32"), "ModuleId")
            dStudentModuleTable.Columns.Add("OldModuleId", Type.GetType("System.Int32"))

        End If

    End Sub

    Public Function SelectData() As DataTable
        Return _dSet.Tables("StudentModule")
    End Function

    Public Function SelectModuleData() As DataTable
        Return _dSet.Tables("Module")
    End Function

    '++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    ' We can't provide an InsertData subprocedure because we
    ' would need the primary key from the new student record
    ' being inserted. Since this is an autonumber, created
    ' by the database then we would need to add this record
    ' recover the ID and then insert a record in the
    ' StudentModule table. This would have to be have be done
    ' connected mode.
    '++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

    Public Sub UpdateData(ByVal Name As String, ByVal Age As Integer, ByVal Course As String, ByVal NewModuleId As Integer, ByVal ModuleId As Integer, ByVal StudentId As Integer)
        Dim dTable As DataTable
        Dim keyVal(1) As Object
        Dim dRow As DataRow

        dTable = _dSet.Tables("Student")
        dRow = dTable.Rows.Find(StudentId)
        dRow("Name") = Name
        dRow("Age") = Age
        dRow("Course") = Course

        dTable = _dSet.Tables("StudentModule")
        keyVal(0) = StudentId
        keyVal(1) = ModuleId
        dRow = dTable.Rows.Find(keyVal)
        dRow("OldModuleId") = ModuleId
        dRow("ModuleId") = NewModuleId

    End Sub

    Public Sub UpdateComplete()
        Dim dTable As DataTable

        dTable = _dSet.Tables("Student")
        _dStudentAdapter.Update(dTable)
        dTable.AcceptChanges()

        dTable = _dSet.Tables("StudentModule")
        _dStudentModuleAdapter.Update(dTable)
        dTable.AcceptChanges()


    End Sub

End Class