Validating data with Flat File Checker

Imports System.Data.OleDb
Imports System.Threading
Imports System.Xml.XPath
Imports System.Xml
Public Enum CardinalityType
    NotExists = 0
    Exists = 1
    Condition = 2
End Enum

Public MustInherit Class QueryChecker
    Inherits GeneralChecker

    Private _connection As Connection
    Private _cmd As OleDbCommand
    Private _cardinality_type As CardinalityType
    Private _table As String
    Private _fields As List(Of FieldCondition)
    Private _query_template As CustomQueryTemplate
    Private _template As Template
    Protected Sub New(ByVal tableName As String)
        _fields = New List(Of FieldCondition)
        _table = tableName
        _fields = New List(Of FieldCondition)
    End Sub

    Friend Property TableName() As String
        Get
            Return _table
        End Get
        Set(ByVal value As String)
            _table = value
        End Set
    End Property
    Public Sub New(ByVal connectionName As String, ByVal templateName As String, ByVal queryName As String, ByVal parent As GeneralChecker, ByVal column As TableColumn)
        MyBase.New(column, Parent)
        _fields = New List(Of FieldCondition)
        _connection = column.Table.Datasources.Connections.Connection(connectionName)
        _template = column.Table.Datasources.Templates.Template(templateName)
        _query_template = New CustomQueryTemplate(queryName, Me)

    End Sub
 
    Public Sub AddField(ByVal queryName As String, ByVal attribute As String, ByVal value As String, ByVal cond As EnumOperator)
        _query_template.AddField(queryName, attribute, value, cond)
    End Sub

    Public ReadOnly Property Template() As Template
        Get
            Return Me._template
        End Get
    End Property
    Public Sub New(ByVal definition As IXPathNavigable, ByVal parent As GeneralChecker, ByVal column As DataColumn)
        MyBase.New(column, parent)
        _fields = New List(Of FieldCondition)
        Dim navigator As XPathNavigator = definition.CreateNavigator
        Dim connection_name As String = navigator.GetAttribute("Connection", "")
        _connection = Me.Column.Datasource.Datasources.Connections.Connection(connection_name)
        Dim template_name As String = navigator.GetAttribute("Template", "")
        If String.IsNullOrEmpty(template_name) Then
            Dim fromNavigator As XPathNavigator = navigator.CreateNavigator
            fromNavigator.MoveToChild("From", "")
            Me.TableName = fromNavigator.InnerXml
            ' Go through all fields declarations and add fields to the rule
            Dim fieldsIterator As XPathNodeIterator = navigator.Select("WhereFields/*")
            Dim fieldNavigator As XPathNavigator
            For Each fieldNavigator In fieldsIterator
                Me.AddField(fieldNavigator)
            Next
        Else ' there is a template
            Me._template = parent.Column.Datasource.Datasources.Templates(template_name)
            navigator.MoveToFirstChild()
            Me._query_template = New CustomQueryTemplate(navigator, Me)
        End If
        ' Process Field declarations here:

    End Sub
    Public Sub AddField(ByVal field As FieldCondition)

        _fields.Add(field)
    End Sub
    Friend Sub AddField(ByVal xNavigator As XPathNavigator)
        Dim navigator As XPathNavigator = xNavigator.CreateNavigator
        Select Case navigator.LocalName
            Case "ValueField"
                Me.AddField(New ValueFieldCondition(navigator, Me))
            Case "MapField"
                Me.AddField(New MappedFieldCondition(navigator, Me, Me.Column))
            Case Else ' skip this for now.
                '  Throw New NotImplementedException()
        End Select
    End Sub

    Public Overrides Function RunChecks(ByVal wait As AutoResetEvent) As Boolean
        MyBase.RunChecks(wait)

        Return Me.Evaluate()
        ' Need to close db connection(s) when application is terminated.

    End Function
    ''' <summary>
    ''' Initiates OleDb command
    ''' </summary>
    ''' <returns>Returns true if command was successfully created.</returns>
    ''' <remarks></remarks>
    Private Function InitiateCommand() As Boolean
        _cmd = New OleDbCommand
        Dim sql As String

        sql = Me.SelectClause & " FROM " & _table

        Dim is_first As Boolean = True
        Dim field As FieldCondition
        For Each field In _fields
            With field
                If is_first Then
                    sql &= " WHERE "
                    is_first = False
                Else
                    sql &= " AND "
                End If
                sql &= .ConditionText
                .AddParameters(_cmd)
            End With
        Next
        If Me.Cardinality <> CardinalityType.Condition Then
            sql &= ")"
        End If
        _cmd.CommandText = sql
        _cmd.CommandType = CommandType.Text
        _cmd.Connection = Me.Connection.Connection
    End Function

    Public Overrides Function Evaluate(ByVal row As Integer) As Boolean

        Dim now_function As String = "SYSDATE"
        Dim parameter As OleDbParameter
        Dim i As Integer = 0
        Me.CurrentValue = Me.Column(row)
        For Each parameter In _cmd.Parameters
            If _fields(i).Type = FieldConditionType.Mapping Then
                If String.IsNullOrEmpty(CurrentValue) Then
                    If Me.Column.AllowBlankValues Then
                        ' Return True
                        ' Else
                        Return False
                        ' Throw New FileFormatException("Value should be provided for column " & Me.Column.FullName & " as it is linked to the external data source.", Me.Column.File, row)
                    End If
                End If
                parameter.Value = Me.CurrentValue
            End If
            i += 1
        Next

        Return Me.CommandResult

    End Function
    Public MustOverride Property Cardinality() As CardinalityType
       
    Protected MustOverride ReadOnly Property CommandResult() As Boolean

    Public Overrides Function Clone() As Object
        Dim QueryClone As QueryChecker = CType(Me.MemberwiseClone, QueryChecker)
        Return QueryClone
    End Function

    Public Overrides Function Initiate() As Boolean
        InitiateCommand() ' Create OleDb Command
        Me.Connection.Open() ' Open connection once before processing records
        If Not Where Is Nothing AndAlso Not Where.Initiate() Then
            Return False
        End If
        Return True
    End Function
    Protected ReadOnly Property Command() As OleDbCommand
        Get
            Return Me._cmd
        End Get
    End Property
    Public ReadOnly Property Connection() As Connection
        Get
            Return _connection
        End Get
    End Property
    Public Shared Function CreateQueryCheck(ByVal xmlDefinition As XPathNavigator, ByVal parent As IDataRule, ByVal column As DataColumn) As GeneralChecker
        Dim navigator As XPathNavigator = xmlDefinition.CreateNavigator
        ' Base Query Validation
        Dim exists As String = navigator.GetAttribute("Exists", "")
        If String.IsNullOrEmpty(exists) Then
            Return New CardinalityQueryChecker(xmlDefinition, parent, column)
        Else
            Return New ExistsQueryChecker(xmlDefinition, parent, column)
        End If
    End Function
    Public Overrides ReadOnly Property Type() As FlatFileLibrary.DataRuleType
        Get
            Return DataRuleType.Query
        End Get
    End Property

    Public Overrides ReadOnly Property DataRuleMessage() As String
        Get
            If _query_template Is Nothing Then
                Return "Query rule."
            Else
                Return _query_template.DataRuleMessage
            End If
        End Get
    End Property

    ''' <summary>
    ''' Gets or sets the template of the query
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property QueryTemplate() As CustomQueryTemplate
        Get
            Return _query_template
        End Get
        Set(ByVal value As CustomQueryTemplate)
            _query_template = value
        End Set
    End Property
    Friend Overrides Function GetNode() As System.Xml.XmlNode
        Dim chkXml As XmlElement
        chkXml = Document.CreateElement("Query", "")
        Dim attrConnection As XmlAttribute = Document.CreateAttribute("Connection")
        attrConnection.Value = _connection.Name
        chkXml.Attributes.Append(attrConnection)
        AppendAttributes(chkXml)

        If _query_template Is Nothing Then
            Dim xmlFrom As XmlNode = Me.Document.CreateElement("From")

            xmlFrom.InnerText = Me.TableName

            chkXml.AppendChild(xmlFrom)
            Dim xmlWhereFields As XmlNode = Me.Document.CreateElement("WhereFields")
            chkXml.AppendChild(xmlWhereFields)
            Dim field As FieldCondition
            For Each field In Me._fields
                xmlWhereFields.AppendChild(field.GetNode())
            Next
            Me.AppendActionXml(chkXml)
            Me.AppendWhereXml(chkXml)
        Else ' Tempalte based query
            Dim attrTemplate As XmlAttribute = Me.Document.CreateAttribute("Template")
            attrTemplate.Value = _query_template.Template.Name
            chkXml.Attributes.Append(attrTemplate)
            chkXml.AppendChild(_query_template.GetNode)
        End If

        Me.AppendWhereXml(chkXml)
        Me.AppendActionXml(chkXml)

        Return chkXml
    End Function
    Public MustOverride ReadOnly Property SelectClause() As String

    Protected MustOverride Sub AppendAttributes(ByVal node As XmlNode)

    Public Overrides ReadOnly Property RuleName() As String
        Get
            Return "Query"
        End Get
    End Property

    Protected Overrides Sub Finalize()
        MyBase.Finalize()
    End Sub
End Class