Imports System
Imports System.ComponentModel
Imports System.Collections
Imports System.Diagnostics
Imports System.Data
Imports System.Data.OleDb

Imports C1.Data
Imports DataLibrary.DataObjects

' C1SchemaDef container class: main class of the data library.
' Contains the C1SchemaDef component managing the data schema. 
Public Class DataClass
    Inherits System.ComponentModel.Component

#Region " Component Designer generated code "

    ' C1SchemaDef component managing the data schema.
    ' Needs to be public in order to allow access to the library.
    Public WithEvents SchemaDef1 As C1.Data.C1SchemaDef

    Public Sub New(Container As System.ComponentModel.IContainer)
        MyClass.New()

        'Required for Windows.Forms Class Composition Designer support
        Container.Add(me)
    End Sub

    Public Sub New()
        MyBase.New()
        'This call is required by the Component Designer.
        InitializeComponent()
    End Sub

    'Component overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub
    Friend WithEvents table_SQLCityCountry As C1.Data.C1TableLogic
    Friend WithEvents table_SQLCustomerInfo As C1.Data.C1TableLogic
    Friend WithEvents table_SQLFilterByCountry As C1.Data.C1TableLogic
    Friend WithEvents table_SQLCountries As C1.Data.C1TableLogic
    Friend WithEvents table_SQLBestSellingProducts As C1.Data.C1TableLogic
    Friend WithEvents table_SQLSuppliers As C1.Data.C1TableLogic
    Friend WithEvents dataset_DataSet1 As C1.Data.C1DataSetLogic
    Friend WithEvents dataset_DataSet2 As C1.Data.C1DataSetLogic
    Friend WithEvents dataset_DataSet3 As C1.Data.C1DataSetLogic
    Friend WithEvents dataset_DataSet4_1 As C1.Data.C1DataSetLogic
    Friend WithEvents dataset_DataSet4_2 As C1.Data.C1DataSetLogic
    Friend WithEvents dataset_DataSet5 As C1.Data.C1DataSetLogic

    'Required by the Component Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Component Designer
    'It can be modified using the Component Designer.
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Dim resources As System.ComponentModel.ComponentResourceManager = New System.ComponentModel.ComponentResourceManager(GetType(DataClass))
        Me.SchemaDef1 = New C1.Data.C1SchemaDef
        Me.table_SQLCityCountry = New C1.Data.C1TableLogic
        Me.table_SQLCustomerInfo = New C1.Data.C1TableLogic
        Me.table_SQLFilterByCountry = New C1.Data.C1TableLogic
        Me.table_SQLCountries = New C1.Data.C1TableLogic
        Me.table_SQLBestSellingProducts = New C1.Data.C1TableLogic
        Me.table_SQLSuppliers = New C1.Data.C1TableLogic
        Me.dataset_DataSet1 = New C1.Data.C1DataSetLogic
        Me.dataset_DataSet2 = New C1.Data.C1DataSetLogic
        Me.dataset_DataSet3 = New C1.Data.C1DataSetLogic
        Me.dataset_DataSet4_1 = New C1.Data.C1DataSetLogic
        Me.dataset_DataSet4_2 = New C1.Data.C1DataSetLogic
        Me.dataset_DataSet5 = New C1.Data.C1DataSetLogic
        CType(Me.SchemaDef1, System.ComponentModel.ISupportInitialize).BeginInit()
        '
        'SchemaDef1
        '
        Me.SchemaDef1.DummyImage = CType(resources.GetObject("SchemaDef1.DummyImage"), System.Drawing.Bitmap)
        Me.SchemaDef1.SerializedSchemaXML = Me.SchemaDef1.GetSchemaFromResources(resources, "SchemaDef1", Me)
        Me.SchemaDef1.SerializedDesignTimeOptionsXML = Me.SchemaDef1.GetDesignTimeOptionsFromResources(resources, "SchemaDef1", Me)
        '
        'table_SQLCityCountry
        '
        Me.table_SQLCityCountry.SchemaComponent = Me.SchemaDef1
        Me.table_SQLCityCountry.SchemaPersist = Me.SchemaDef1
        Me.table_SQLCityCountry.Table = "SQLCityCountry"
        '
        'table_SQLCustomerInfo
        '
        Me.table_SQLCustomerInfo.SchemaComponent = Me.SchemaDef1
        Me.table_SQLCustomerInfo.SchemaPersist = Me.SchemaDef1
        Me.table_SQLCustomerInfo.Table = "SQLCustomerInfo"
        '
        'table_SQLFilterByCountry
        '
        Me.table_SQLFilterByCountry.SchemaComponent = Me.SchemaDef1
        Me.table_SQLFilterByCountry.SchemaPersist = Me.SchemaDef1
        Me.table_SQLFilterByCountry.Table = "SQLFilterByCountry"
        '
        'table_SQLCountries
        '
        Me.table_SQLCountries.SchemaComponent = Me.SchemaDef1
        Me.table_SQLCountries.SchemaPersist = Me.SchemaDef1
        Me.table_SQLCountries.Table = "SQLCountries"
        '
        'table_SQLBestSellingProducts
        '
        Me.table_SQLBestSellingProducts.SchemaComponent = Me.SchemaDef1
        Me.table_SQLBestSellingProducts.SchemaPersist = Me.SchemaDef1
        Me.table_SQLBestSellingProducts.Table = "SQLBestSellingProducts"
        '
        'table_SQLSuppliers
        '
        Me.table_SQLSuppliers.SchemaComponent = Me.SchemaDef1
        Me.table_SQLSuppliers.SchemaPersist = Me.SchemaDef1
        Me.table_SQLSuppliers.Table = "SQLSuppliers"
        '
        'dataset_DataSet1
        '
        Me.dataset_DataSet1.DataSetDef = "DataSet1"
        Me.dataset_DataSet1.SchemaComponent = Me.SchemaDef1
        Me.dataset_DataSet1.SchemaPersist = Me.SchemaDef1
        '
        'dataset_DataSet2
        '
        Me.dataset_DataSet2.DataSetDef = "DataSet2"
        Me.dataset_DataSet2.SchemaComponent = Me.SchemaDef1
        Me.dataset_DataSet2.SchemaPersist = Me.SchemaDef1
        '
        'dataset_DataSet3
        '
        Me.dataset_DataSet3.DataSetDef = "DataSet3"
        Me.dataset_DataSet3.SchemaComponent = Me.SchemaDef1
        Me.dataset_DataSet3.SchemaPersist = Me.SchemaDef1
        '
        'dataset_DataSet4_1
        '
        Me.dataset_DataSet4_1.DataSetDef = "DataSet4_1"
        Me.dataset_DataSet4_1.SchemaComponent = Me.SchemaDef1
        Me.dataset_DataSet4_1.SchemaPersist = Me.SchemaDef1
        '
        'dataset_DataSet4_2
        '
        Me.dataset_DataSet4_2.DataSetDef = "DataSet4_2"
        Me.dataset_DataSet4_2.SchemaComponent = Me.SchemaDef1
        Me.dataset_DataSet4_2.SchemaPersist = Me.SchemaDef1
        '
        'dataset_DataSet5
        '
        Me.dataset_DataSet5.DataSetDef = "DataSet5"
        Me.dataset_DataSet5.SchemaComponent = Me.SchemaDef1
        Me.dataset_DataSet5.SchemaPersist = Me.SchemaDef1
        CType(Me.SchemaDef1, System.ComponentModel.ISupportInitialize).EndInit()

    End Sub

#End Region

    Private Sub dataset_DataSet1_BeforeGenerateSql(ByVal sender As Object, ByVal e As C1.Data.GenerateSqlEventArgs) Handles dataset_DataSet1.BeforeGenerateSql
        If e.TableView.Name = "SQLCityCountry" Then
            e.Command = New OleDbCommand( _
             "SELECT DISTINCT City, Country " + _
             "FROM Customers " + _
             "ORDER BY 1, 2", _
             CType(e.TableView.Diagram.Schema.Connections("Connection").DbConnection, System.Data.OleDb.OleDbConnection))
            e.Status = DataSetEventStatusEnum.Skip
        End If
    End Sub

    Private Sub dataset_DataSet2_BeforeGenerateSql(ByVal sender As Object, ByVal e As C1.Data.GenerateSqlEventArgs) Handles dataset_DataSet2.BeforeGenerateSql
        If e.TableView.Name = "SQLCustomerInfo" Then
            e.Command = New OleDbCommand( _
             "SELECT Customers.CompanyName, Customers.Country, Customers.ContactName, " + _
             "   COUNT(Orders.OrderID) As [Order Count], " + _
             "   SUM(CCur(OD.UnitPrice*OD.Quantity*(1-OD.Discount)/100)*100) As [Order Sum] " + _
             "FROM Customers, Orders, [Order Details] OD " + _
             "WHERE Customers.CustomerID = Orders.CustomerID AND " + _
             "   OD.OrderID = Orders.OrderID " + _
             "GROUP BY Customers.CompanyName, Customers.Country, Customers.ContactName " + _
             "ORDER BY 4 DESC, 5 DESC", _
             CType(e.TableView.Diagram.Schema.Connections("Connection").DbConnection, System.Data.OleDb.OleDbConnection))
            e.Status = DataSetEventStatusEnum.Skip
        End If
    End Sub

    Private Sub dataset_DataSet3_BeforeGenerateSql(ByVal sender As Object, ByVal e As C1.Data.GenerateSqlEventArgs) Handles dataset_DataSet3.BeforeGenerateSql
        If e.TableView.Name = "SQLBestSellingProducts" Then
            e.Command = New OleDbCommand( _
             "SELECT A.CompanyName, B.ProductName, " + _
             "   SUM(CCur(C.UnitPrice*C.Quantity*(1-C.Discount)/100)*100) AS [Order Sum] " + _
             "FROM  Suppliers A, Products B, [Order Details] C " + _
             "WHERE A.SupplierID = B.SupplierID AND B.ProductID = C.ProductID AND " + _
             "NOT EXISTS (SELECT SUM(CCur(E.UnitPrice*E.Quantity*(1-E.Discount)/100)*100) " + _
             "   FROM  [Order Details] E " + _
             "   WHERE E.ProductID = B.ProductID " + _
             "   GROUP BY E.ProductID " + _
             "   HAVING SUM(CCur(E.UnitPrice*E.Quantity*(1-E.Discount)/100)*100) < ANY " + _
             "     (SELECT SUM(CCur(F.UnitPrice*F.Quantity*(1-F.Discount)/100)*100) " + _
             "      FROM  Products U, [Order Details] F " + _
             "      WHERE U.SupplierID = A.SupplierID AND U.ProductID = F.ProductID " + _
             "      GROUP BY U.ProductID)) " + _
             "GROUP BY A.CompanyName, B.ProductName " + _
             "ORDER BY 1", _
            CType(e.TableView.Diagram.Schema.Connections("Connection").DbConnection, System.Data.OleDb.OleDbConnection))
            e.Status = DataSetEventStatusEnum.Skip
        End If
    End Sub

    Private Sub dataset_DataSet4_1_BeforeGenerateSql(ByVal sender As Object, ByVal e As C1.Data.GenerateSqlEventArgs) Handles dataset_DataSet4_1.BeforeGenerateSql
        If e.TableView.Name = "SQLCountries" Then
            e.Command = New OleDbCommand( _
             "SELECT Country " + _
             "FROM Suppliers " + _
             "UNION " + _
             "SELECT Country " + _
             "FROM Customers " + _
             "ORDER BY 1", _
             CType(e.TableView.Diagram.Schema.Connections("Connection").DbConnection, System.Data.OleDb.OleDbConnection))
            e.Status = DataSetEventStatusEnum.Skip
        End If
    End Sub

    Private Sub dataset_DataSet4_2_BeforeGenerateSql(ByVal sender As Object, ByVal e As C1.Data.GenerateSqlEventArgs) Handles dataset_DataSet4_2.BeforeGenerateSql
        If e.TableView.Name = "SQLFilterByCountry" Then
            Dim command As OleDbCommand = New OleDbCommand( _
             "SELECT City, CompanyName, ContactName, 'Supplier' AS [Relationship] " + _
             "FROM Suppliers " + _
             "WHERE Suppliers.Country='" + e.Filter(0).ConditionText + "' " + _
             "UNION " + _
             "SELECT City, CompanyName, ContactName, 'Customer' " + _
             "FROM Customers " + _
             "WHERE Customers.Country='" + e.Filter(0).ConditionText + "' " + _
             "ORDER BY 1, 2, 3", _
             CType(e.TableView.Diagram.Schema.Connections("Connection").DbConnection, System.Data.OleDb.OleDbConnection))
            e.Command = command
            e.Status = DataSetEventStatusEnum.Skip
        End If
    End Sub

    Private Sub dataset_DataSet5_BeforeGenerateSql(ByVal sender As Object, ByVal e As C1.Data.GenerateSqlEventArgs) Handles dataset_DataSet5.BeforeGenerateSql
        If e.TableView.Name = "SQLSuppliers" Then
            e.Command = New OleDbCommand( _
             "SELECT CompanyName, ContactName, City, Country, Phone, SupplierID " + _
             "FROM Suppliers ORDER BY CompanyName", _
             CType(e.TableView.Diagram.Schema.Connections("Connection").DbConnection, System.Data.OleDb.OleDbConnection))
            e.Status = DataSetEventStatusEnum.Skip
        End If
    End Sub

    Private Sub table_SQLSuppliers_BeforeUpdateRow(ByVal sender As Object, ByVal e As C1.Data.RowUpdateEventArgs) Handles table_SQLSuppliers.BeforeUpdateRow
        Select Case e.Row.RowState
            Case C1.Data.DataRowStateEnum.Deleted
                Dim conn As C1.Data.SchemaObjects.Connection = SchemaDef1.Schema.Connections("Connection")
                Dim check1Command As OleDbCommand = _
                 New OleDbCommand("SELECT COUNT(*) As [Product Count] " + _
                 "FROM Products WHERE SupplierID=?", CType(conn.DbConnection, OleDbConnection))
                check1Command.Transaction = conn.DbTransaction
                check1Command.Parameters.AddWithValue("SupplierID_OriginalValue", e.Row("SupplierID", _
                 DataRowVersionEnum.Original))
                Dim ProductCount As Int32
                Dim reader As OleDbDataReader = check1Command.ExecuteReader()
                Try
                    reader.Read()
                    ProductCount = CType(reader("Product Count"), Int32)
                Finally
                    reader.Close()
                End Try
                If ProductCount > 0 Then
                    Dim check2Command As OleDbCommand = _
                     New OleDbCommand("SELECT COUNT([Order Details].ProductID) As [Item Count] " + _
                     "FROM Products, [Order Details] " + _
                     "WHERE Products.SupplierID=? AND [Order Details].ProductID=Products.ProductID", _
                     CType(conn.DbConnection, OleDbConnection))
                    check2Command.Transaction = CType(conn.DbTransaction, OleDbTransaction)
                    check2Command.Parameters.AddWithValue("SupplierID_OriginalValue", e.Row("SupplierID", _
                     DataRowVersionEnum.Original))
                    Dim ItemCount As Int32
                    reader = check2Command.ExecuteReader()
                    Try
                        reader.Read()
                        ItemCount = CType(reader("Item Count"), Int32)
                    Finally
                        reader.Close()
                    End Try
                    If ItemCount > 0 Then
                        e.Status = C1.Data.RowEventStatusEnum.SkipCurrentRow
                        Return
                    End If
                    Dim cascade_deleteCommand As OleDbCommand = _
                     New OleDbCommand("DELETE FROM Products WHERE SupplierID=?", _
                     CType(conn.DbConnection, OleDbConnection))
                    cascade_deleteCommand.Transaction = CType(conn.DbTransaction, OleDbTransaction)
                    cascade_deleteCommand.Parameters.AddWithValue("SupplierID_OriginalValue", _
                     e.Row("SupplierID", DataRowVersionEnum.Original))
                    ' Cascade delete of all supplier's products
                    cascade_deleteCommand.ExecuteNonQuery()
                End If
                Dim deleteCommand As OleDbCommand = _
                 New OleDbCommand("DELETE FROM Suppliers WHERE SupplierID=?", _
                 CType(conn.DbConnection, OleDbConnection))
                e.DeleteCommand = deleteCommand
                deleteCommand.Parameters.AddWithValue("SupplierID_OriginalValue", e.Row("SupplierID", _
                 DataRowVersionEnum.Original))
            Case C1.Data.DataRowStateEnum.Added
                Dim conn As System.Data.OleDb.OleDbConnection = _
                    CType(SchemaDef1.Schema.Connections("Connection").DbConnection, System.Data.OleDb.OleDbConnection)
                Dim insertCommand As OleDbCommand = _
                 New OleDbCommand("INSERT INTO Suppliers (CompanyName, ContactName, " + _
                 "City, Country, Phone) VALUES (?, ?, ?, ?, ?)", conn)
                e.InsertCommand = insertCommand
                insertCommand.Parameters.AddWithValue("CompanyName_NewValue", e.Row("CompanyName"))
                insertCommand.Parameters.AddWithValue("ContactName_NewValue", e.Row("ContactName"))
                insertCommand.Parameters.AddWithValue("City_NewValue", e.Row("City"))
                insertCommand.Parameters.AddWithValue("Country_NewValue", e.Row("Country"))
                insertCommand.Parameters.AddWithValue("Phone_NewValue", e.Row("Phone"))
                Dim refreshSQLText As String = _
                 "SELECT CompanyName, ContactName, City, Country, Phone, SupplierID " + _
                 "FROM Suppliers " + _
                 "WHERE CompanyName=?"
                Dim rw As DataLibrary.DataObjects.SQLSuppliersRow = DataLibrary.DataObjects. _
                 SQLSuppliersRow.Obj(e.Row)
                If Not rw.IsContactNameNull() Then
                    refreshSQLText += " AND ContactName=?"
                End If
                If Not rw.IsCityNull() Then
                    refreshSQLText += " AND City=?"
                End If
                If Not rw.IsCountryNull() Then
                    refreshSQLText += " AND Country=?"
                End If
                If Not rw.IsPhoneNull() Then
                    refreshSQLText += " AND Phone=?"
                End If
                Dim refreshCommand As OleDbCommand = New OleDbCommand(refreshSQLText, conn)
                e.SelectCommand = refreshCommand
                refreshCommand.Parameters.AddWithValue("CompanyName_NewValue", e.Row("CompanyName"))
                If Not rw.IsContactNameNull() Then
                    refreshCommand.Parameters.AddWithValue("ContactName_NewValue", e.Row("ContactName"))
                End If
                If Not rw.IsCityNull() Then
                    refreshCommand.Parameters.AddWithValue("City_NewValue", e.Row("City"))
                End If
                If Not rw.IsCountryNull() Then
                    refreshCommand.Parameters.AddWithValue("Country_NewValue", e.Row("Country"))
                End If
                If Not rw.IsPhoneNull() Then
                    refreshCommand.Parameters.AddWithValue("Phone_NewValue", e.Row("Phone"))
                End If
            Case C1.Data.DataRowStateEnum.Modified
                Dim conn As System.Data.OleDb.OleDbConnection = _
                 CType(SchemaDef1.Schema.Connections("Connection").DbConnection, System.Data.OleDb.OleDbConnection)
                Dim updateCommand As OleDbCommand = _
                    New OleDbCommand("UPDATE Suppliers SET CompanyName=?, ContactName=?, " + _
                    "City=?, Country=?, Phone=? WHERE SupplierID=?", conn)
                e.UpdateCommand = updateCommand
                updateCommand.Parameters.AddWithValue("CompanyName_NewValue", e.Row("CompanyName"))
                updateCommand.Parameters.AddWithValue("ContactName_NewValue", e.Row("ContactName"))
                updateCommand.Parameters.AddWithValue("City_NewValue", e.Row("City"))
                updateCommand.Parameters.AddWithValue("Country_NewValue", e.Row("Country"))
                updateCommand.Parameters.AddWithValue("Phone_NewValue", e.Row("Phone"))
                updateCommand.Parameters.AddWithValue("SupplierID_OriginalValue", e.Row("SupplierID", _
                    DataRowVersionEnum.Original))
                Dim refreshCommand As OleDbCommand = New OleDbCommand( _
                    "SELECT CompanyName, ContactName, City, Country, Phone, SupplierID " + _
                    "FROM Suppliers WHERE SupplierID=?", conn)
                e.SelectCommand = refreshCommand
                refreshCommand.Parameters.AddWithValue("SupplierID_NewValue", e.Row("SupplierID"))
        End Select
    End Sub

#Region " Switch to C1NWind.mdb database "

    Private Sub SchemaDef1_CreateSchema(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SchemaDef1.CreateSchema
        Dim dbPath As String = Environment.GetFolderPath(Environment.SpecialFolder.Personal) + _
            "\ComponentOne Samples\Common\C1NWind.mdb"
        If System.IO.File.Exists(dbPath) Then
            Dim conn As C1.Data.SchemaObjects.Connection = SchemaDef1.Schema.Connections(0)
            conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbPath
        End If
    End Sub

#End Region

End Class
