Imports Microsoft.VisualBasic

Imports Org.Mentalis.Files

Namespace dbUtilities

    Public Class QueryStringBuilder

        '/// <summary>
        '/// ToDo: need to document all code
        '/// </summary>
        Public Sub New()
            _iniFile = New IniReader(_rootDir + "App_Data\ini\queries.ini")
        End Sub

        Public Function buildString(ByVal queryType As String, ByVal queryName As String) As String
            buildString = _iniFile.ReadString(queryType, queryName)
        End Function

        Public Function buildString(ByVal queryType As String, ByVal queryName As String, _
                                    ByVal FirstParam As String, ByVal ParamArray NextParams() As String) As String
            Dim returnStr As StringBuilder = New StringBuilder("")
            Dim qryStr As String = _iniFile.ReadString(queryType, queryName)

            Try
                returnStr.Append(qryStr.Substring(0, qryStr.IndexOf("@"))) ' Add the string prefix (first set of logic)
                returnStr.Append(FirstParam) ' Add the first parameter to the string
            Catch e As ArgumentOutOfRangeException
                Return "Error -> Problem with query  """ & queryName & """ in the ""queries.ini"" file!"
            End Try


            Dim i As Int16 = getNextIndex(qryStr) ' get index at end of parameter placeholder
            Dim j As Int16 = 0

            If i <> -1 Then
                qryStr = qryStr.Substring(i, qryStr.Length - i) ' remove prefix and parameter placeholder from string
                If qryStr.IndexOf("@") = -1 Then
                    returnStr.Append(qryStr) ' add final stage of logic to the string
                End If
            Else
                qryStr = "" ' there is no logic at the end of the string so remove the entire string
            End If

            Try

                While qryStr.IndexOf("@") <> -1
                    returnStr.Append(qryStr.Substring(0, qryStr.IndexOf("@"))) ' add the next stage of logic
                    returnStr.Append(NextParams(j))                          ' add the next parameter

                    i = getNextIndex(qryStr)                           ' get index at end of the next parameter placeholder
                    If i <> -1 Then
                        qryStr = qryStr.Substring(i, qryStr.Length - i)      ' remove next set of logic and paramter placeholder from the string
                        If qryStr.IndexOf("@") = -1 Then
                            returnStr.Append(qryStr)
                        End If
                        j += 1
                    Else
                        qryStr = "" ' there is no logic at the end of the string so remove the entire string
                        Exit While
                    End If
                End While
            Catch e As IndexOutOfRangeException
                Return "Error -> Problem parameters of the query  """ & queryName & """ in the ""queries.ini"" file!"
            End Try


            Return returnStr.ToString()
        End Function

        Public Function buildString() As String ' Test Function
            Dim returnStr As StringBuilder = New StringBuilder("")
            Dim qryStr As String = _iniFile.ReadString("select", "projectNameIdPair")
            returnStr.Append(qryStr)
            Return returnStr.ToString()
        End Function


        Private Shared _iniFile As IniReader
        ' Can change to shared for smaller memory footprint
        ' if parameter constructor is commented out
        'Public Sub New(ByVal iniFile As String)
        '    _iniFile = New IniReader(_rootDir + "App_Data\" + iniFile)
        'End Sub
        Private Shared _rootDir As String = AppDomain.CurrentDomain.SetupInformation.ApplicationBase()

        Private i As Int16
        Private j As Int16
        Private k As Int16
        Private Function getNextIndex(ByVal Str As String) As Int16
            i = j = k = -1
            i = Str.IndexOf(" ", Str.IndexOf("@") + 1)
            j = Str.IndexOf("'", Str.IndexOf("@") + 1)
            k = Str.IndexOf(",", Str.IndexOf("@") + 1)

            If i <> -1 And (i < j Or j = -1) And (i < k Or k = -1) Then
                Return i
            ElseIf j <> -1 And (j < k Or k = -1) Then
                Return j
            ElseIf k <> -1 Then
                Return k
            End If
            Return i
        End Function
    End Class

End Namespace

Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.IO
Imports Microsoft.VisualBasic.ApplicationServices
Imports System.Web.Configuration

Namespace dbUtilities


    Public Class DBAccess

        Private Shared _rootDir As String = AppDomain.CurrentDomain.SetupInformation.ApplicationBase()


        Public Function GetSingleDBValue(ByVal conStr As String, ByVal qryStr As String) As Object
            Dim sqlCon As New SqlConnection(conStr)
            Dim sqlCmd As New SqlCommand
            Dim retVal As Object

            sqlCmd.CommandText = qryStr
            sqlCmd.CommandType = Data.CommandType.Text
            sqlCmd.Connection = sqlCon

            sqlCon.Open()
            retVal = sqlCmd.ExecuteScalar()
            If IsDBNull(retVal) Then
                retVal = Nothing
            End If
            sqlCon.Close()

            Return retVal
        End Function


        Public Function GetDataReader(ByVal conStr As String, ByVal qryStr As String) As SqlDataReader
            Dim sqlCon As New SqlConnection(conStr)
            Dim sqlCmd As New SqlCommand(qryStr)

            sqlCmd.CommandType = Data.CommandType.Text
            sqlCmd.Connection = sqlCon

            sqlCon.Open()

            Return sqlCmd.ExecuteReader()
        End Function

        Public Sub ExecuteNoReturnQuery(ByVal conStr As String, ByVal qryStr As String)
            Dim sqlCon As New SqlConnection(conStr)
            Dim sqlCmd As New SqlCommand(qryStr)

            sqlCmd.CommandType = Data.CommandType.Text
            sqlCmd.Connection = sqlCon

            sqlCon.Open()
            sqlCmd.ExecuteNonQuery()

            sqlCon.Close()

        End Sub

        Public Function GetOleDataReader(ByVal conStr As String, ByVal qryStr As String) As OleDbDataReader
            Dim oleCon As OleDbConnection = New OleDbConnection(conStr)
            oleCon.Open()
            Dim oleDbCmd As OleDbCommand = New OleDbCommand(qryStr, oleCon)

            Return oleDbCmd.ExecuteReader()
        End Function


        Public Function BuildOleDbfConnectionString(ByVal dir As String) As String

            Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                  _rootDir & "App_Data\dbfFiles\" & dir & "\" & _
                 ";Extended Properties=dBASE IV;User ID=Admin;Password=;"
        End Function


        Public Function BuildOleExcelConnectionString(ByVal dir As String, ByRef fileName As String) As String
            Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                   _rootDir & "App_Data\dbfFiles\" & dir & "\" & fileName & ".xls" & _
                   ";Jet OLEDB:Engine Type=23;Extended Properties=Excel 8.0"
        End Function


        Public Function BuildOleAccessConnectionString(ByVal dir As String, ByRef fileName As String) As String
            Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                   _rootDir & "App_Data\dbfFiles\" & dir & "\" & fileName & ".mdb"
        End Function



        Public Sub CopyDbfFile(ByVal sourceFile As String, ByVal destFile As String)
            Dim dir As String = _rootDir & "App_Data\dbfFiles\"
            File.Delete(dir & destFile)
            If File.Exists(dir & sourceFile) = True Then
                File.Copy(dir & sourceFile, dir & destFile)
            End If
        End Sub


        Public Function IsUserAuthorized( _
                ByVal UserID As String, _
                ByVal pid As String, _
                ByVal PhaseID As String, _
                ByVal conStr As String) As Boolean
            Dim qsb As New QueryStringBuilder

            If Roles.IsUserInRole("staff") Or Roles.IsUserInRole("admin") Then
                Return True
            ElseIf pid = 3 _
                And GetSingleDBValue(conStr, qsb.buildString("select", "isDatasetUserAuthorized", _
                                                            UserID, pid)) > 0 Then
                Return True
            ElseIf GetSingleDBValue(conStr, qsb.buildString("select", "isUserAuthorized", _
                                                            UserID, pid, PhaseID)) > 0 Then
                Return True
            End If
            Return False

        End Function

    End Class


End Namespace

  ...
  [select]
  selectFitReachStorageList=SELECT reachIDCombo, reachIDName FROM dbo.vwFITSelectBox WHERE ProjectID = @pid
  ...

        ...
        <asp:CheckBoxList ID="ReachCheckBoxList" runat="server">
        </asp:CheckBoxList>
        ...

  Imports System.Web.Configuration
  Imports System.Data.SqlClient

  Imports dbUtilities
  ...
Partial Class projects_phase1_fit_store
    Inherits System.Web.UI.Page

    Private _conStr As String
    Private _ds As SqlDataSource
    Private _dataReader As SqlDataReader
    Private _chbl As CheckBoxList

    Private _qsb As QueryStringBuilder
    Private _dba As DBAccess
    

    Protected pid As String
    ...
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        _qsb = New QueryStringBuilder()
        _dba = New DBAccess       'data access class used to get project name and sgat version  
        _conStr = WebConfigurationManager.ConnectionStrings("RiversDMSConStr").ConnectionString
        ...
        pid = Request.QueryString("pid")
        ...
        _chbl = ReachCheckBoxList
        ' Fill the checkbox list
        _dataReader = _dba.GetDataReader(_conStr, _qsb.buildString("select", "selectFitReachStorageList", pid))

               If Not Page.IsPostBack Then
           While _dataReader.Read
            _chbl.Items.Add(New ListItem(_dataReader("reachIDName"), _dataReader("reachIDCombo")))
           End While
               End If

    End Sub

End Class

        <asp:LinkButton ID="lbAll" runat="server">Select All</asp:LinkButton>
        &nbsp;&nbsp;&nbsp;&nbsp;
        <asp:LinkButton ID="lbNone" runat="server">Select None</asp:LinkButton>
        
        <asp:CheckBoxList ID="ReachCheckBoxList" runat="server">
        </asp:CheckBoxList>

    Protected Sub lbAll_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lbAll.Click
        For Each li In _chbl.Items
            li.Selected = True
        Next
    End Sub

    Protected Sub lbNone_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lbNone.Click
        For Each li In _chbl.Items
            li.Selected = False
        Next
    End Sub