Imports System.Data
Partial Class BugTrackerByHand
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        If Not IsPostBack Then
            ' Wywołanie metody, która tworzy tabele i związki.
            Dim ds As DataSet = CreateDataSet()

            ' Ustawienie pierwszej tabeli jako źródła danych dla siatki.
            BugsGridView.DataSource = ds.Tables("Bugs")
            BugsGridView.DataBind()

            BugConstraintsGridView.DataSource = ds.Tables("Bugs").Constraints
            BugConstraintsGridView.DataBind()
        End If
    End Sub

    Private Function CreateDataSet() As DataSet
        ' Ustanowienie egzemplarza nowego obiektu DataSet, który wypełnimy tabelami i związkami.
        Dim dataSet As New DataSet()

        ' Utworzenie tabeli Bugs i jej kolumn imitujących atrybuty z bazy danych SQL.
        Dim tblBugs As DataTable = CreateBugsTable()
        dataSet.Tables.Add(tblBugs)

        ' Utworzenie tabeli Product i jej kolumn imitujących atrybuty z bazy danych SQL.
        Dim tblProduct As DataTable = CreateProductTable()
        dataSet.Tables.Add(tblProduct)

        ' Utworzenie tabeli People i jej kolumn imitujących atrybuty z bazy danych SQL.
        Dim tblPeople As DataTable = CreatePeopleTable()
        dataSet.Tables.Add(tblPeople)

        ' Tworzymy ograniczenie klucza zewnętrznego People.PersonID = Bugs.ReporterID.
        CreateForeignKeyAndDataRelation(dataSet, "BugToPerson", tblPeople, "PersonID", tblBugs, "ReporterID")

        ' Tworzymy ograniczenie klucza zewnętrznego Product.ProductID = Bugs.ProductID.
        CreateForeignKeyAndDataRelation(dataSet, "BugToProduct", tblProduct, "ProductID", tblBugs, "ProductID")

        Return dataSet
    End Function

#Region "Generic Table Methods"

    Private Sub AddNewPrimaryKeyColumn(ByVal table As DataTable, ByVal ColumnName As String)
        AddNewColumn(table, "System.Int32", ColumnName, False)
        Dim PkColumn As DataColumn = table.Columns(ColumnName)

        ' Ustawienie kolumny tak, aby pole automatycznie zwiększało wartość (autoinkrementacja).
        PkColumn.AutoIncrement = True
        ' Autoinkrementacja.
        PkColumn.AutoIncrementSeed = 1
        ' Rozpoczęcie od wartości 1.
        PkColumn.AutoIncrementStep = 1
        ' Zwiększenie o 1.
        ' Upewnienie się, że wszystkie wartości są unikalne.
        Dim constraintName As String = [String].Format("Unique_{0}", ColumnName)
        Dim constraint As New UniqueConstraint(constraintName, PkColumn)
        table.Constraints.Add(constraint)

        ' Ustawienie kolumny jako klucza podstawowego tabeli.
        Dim columnArray As DataColumn() = New DataColumn() {PkColumn}
        table.PrimaryKey = columnArray
    End Sub

    Private Sub AddNewColumn(ByVal table As DataTable, ByVal ColumnType As String, ByVal ColumnName As String, ByVal AllowNulls As Boolean, ByVal DefaultValue As Object, ByVal MaxLength As Integer)
        Dim newColumn As DataColumn = table.Columns.Add(ColumnName, Type.[GetType](ColumnType))
        newColumn.AllowDBNull = AllowNulls
        newColumn.MaxLength = MaxLength
        newColumn.DefaultValue = DefaultValue
    End Sub

    Private Sub AddNewColumn(ByVal table As DataTable, ByVal ColumnType As String, ByVal ColumnName As String, ByVal AllowNulls As Boolean, ByVal DefaultValue As Object)
        AddNewColumn(table, ColumnType, ColumnName, AllowNulls, DefaultValue, -1)
    End Sub

    Private Sub AddNewColumn(ByVal table As DataTable, ByVal ColumnType As String, ByVal ColumnName As String, ByVal AllowNulls As Boolean)
        AddNewColumn(table, ColumnType, ColumnName, AllowNulls, Nothing, -1)
    End Sub

    Private Sub CreateForeignKeyAndDataRelation(ByVal dataSet As DataSet, ByVal relationName As String, ByVal parentTable As DataTable, ByVal primaryKeyColumnName As String, ByVal childTable As DataTable, ByVal foreignKeyColumnName As String)
        ' Pobranie odniesień do powiązanych kolumn.
        Dim primaryKeyColumn As DataColumn = parentTable.Columns(primaryKeyColumnName)
        Dim foreignKeyColumn As DataColumn = childTable.Columns(foreignKeyColumnName)
        Dim foreignKeyConstraintName As String = [String].Format("FK_{0}", relationName)

        ' Tworzymy ograniczenie klucza zewnętrznego.
        Dim fk As New ForeignKeyConstraint(foreignKeyConstraintName, primaryKeyColumn, foreignKeyColumn)
        fk.DeleteRule = Rule.Cascade
        ' Jeżeli rekord nadrzędny zostanie usunięty, wtedy wszystkie powiązane z nim rekordy
        ' w tabeli potomnej również zostaną usunięte.
        fk.UpdateRule = Rule.Cascade
        childTable.Constraints.Add(fk)
        ' Dodanie nowego ograniczenia.
        ' Dodanie DataRelation przedstawiającego FKConstraint względem DataSet.
        Dim relation As New DataRelation(relationName, primaryKeyColumn, foreignKeyColumn)
        dataSet.Relations.Add(relation)
    End Sub

#End Region

#Region "Bugs Table"

    Private Function CreateBugsTable() As DataTable
        Dim tblBugs As New DataTable("Bugs")

        ' Dodanie kolumn.
        AddNewPrimaryKeyColumn(tblBugs, "BugID")
        AddNewColumn(tblBugs, "System.Int32", "ProduktID", False, 1)
        AddNewColumn(tblBugs, "System.String", "Wersja", False, "0.1", 50)
        AddNewColumn(tblBugs, "System.String", "Opis", False, "", 8000)
        AddNewColumn(tblBugs, "System.Int32", "ZgłaszającyID", False)

        ' Dodanie rekordów do tabeli.
        AddNewBug(tblBugs, 1, "0.1", "Awaria podczas wczytywania", 5)
        AddNewBug(tblBugs, 1, "0.1", "Nie pokazuje poprawnie osoby zgłaszającej błąd", 5)
        AddNewBug(tblBugs, 1, "0.1", "Nie wyświetla historii poprzednich działań", 6)
        AddNewBug(tblBugs, 1, "0.1", "Próba ponownego wczytania kończy się niepowodzeniem", 5)
        AddNewBug(tblBugs, 2, "0.1", "Utrata danych przez noc", 5)
        AddNewBug(tblBugs, 2, "0.1", "Kod HTML nie jest prawidłowo wyświetlany", 6)
        Return tblBugs
    End Function

    Private Sub AddNewBug(ByVal bugTable As DataTable, ByVal product As Integer, ByVal version As String, ByVal description As String, ByVal reporter As Integer)
        Dim newRow As DataRow = bugTable.NewRow()
        newRow("ProductID") = product
        newRow("Version") = version
        newRow("Description") = description
        newRow("ReporterID") = reporter
        bugTable.Rows.Add(newRow)
    End Sub


#End Region

#Region "Product Table"

    Private Function CreateProductTable() As DataTable
        Dim tblProduct As New DataTable("lkProduct")

        ' Dodanie kolumn.
        AddNewPrimaryKeyColumn(tblProduct, "ProductID")
        AddNewColumn(tblProduct, "System.String", "ProductDescription", False, "", 8000)

        ' Dodanie rekordów do tabeli.
        AddNewProduct(tblProduct, "BugX Bug Tracking")
        AddNewProduct(tblProduct, "PIM - My Personal Information Manager")
        Return tblProduct
    End Function

    Private Sub AddNewProduct(ByVal productTable As DataTable, ByVal description As String)
        Dim newRow As DataRow = productTable.NewRow()
        newRow("ProductDescription") = description
        productTable.Rows.Add(newRow)
    End Sub

#End Region

#Region "People Table"

    Private Function CreatePeopleTable() As DataTable
        Dim tblPeople As New DataTable("People")

        ' Dodanie kolumn.
        AddNewPrimaryKeyColumn(tblPeople, "PersonID")
        AddNewColumn(tblPeople, "System.String", "FullName", False, "", 8000)
        AddNewColumn(tblPeople, "System.String", "Email", False, "", 100)
        AddNewColumn(tblPeople, "System.String", "Phone", False, "", 20)
        AddNewColumn(tblPeople, "System.Int32", "Role", False, 0)

        ' Dodanie rekordów do tabeli.
        AddNewPerson(tblPeople, "Dan Maharry", "danm@hmobius.com", "212-555-0285", 1)
        AddNewPerson(tblPeople, "Jesse Liberty", "jliberty@libertyassociates.com", "617-555-7301", 1)
        AddNewPerson(tblPeople, "Dan Hurwitz", "dhurwitz@stersol.com", "781-555-3375", 1)
        AddNewPerson(tblPeople, "John Galt", "jGalt@franconia.com", "617-555-9876", 1)
        AddNewPerson(tblPeople, "John Osborn", "jOsborn@oreilly.com", "617-555-3232", 3)
        AddNewPerson(tblPeople, "Ron Petrusha", "ron@oreilly.com", "707-555-0515", 2)
        AddNewPerson(tblPeople, "Tatiana Diaz", "tatiana@oreilly.com", "617-555-1234", 2)
        Return tblPeople
    End Function

    Private Sub AddNewPerson(ByVal table As DataTable, ByVal name As String, ByVal email As String, ByVal phone As String, ByVal role As Integer)
        Dim newRow As DataRow = table.NewRow()
        newRow("FullName") = name
        newRow("email") = email
        newRow("Phone") = phone
        newRow("Role") = role
        table.Rows.Add(newRow)
    End Sub

#End Region


End Class
