Imports System.Security.Cryptography
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Text
Imports System.IO
Imports System.Text.RegularExpressions
Imports System.Transactions
Imports EntitiesLayer
Imports DataAccess

Public Class DataAccessDataManager

    'The connection path to the database
    Private recipeDatabaseConnection As String = My.Settings.RecipeHolderDatabaseConnectionString
    Private _accountId As Integer


    Public Sub setAccountID(ByVal accountId As Integer)
        _accountId = accountId
    End Sub

    Public Function getAccountID() As Integer
        Return _accountId
    End Function

    ''' <summary>
    ''' Authenticates a user based on user name and password.
    ''' </summary>
    ''' <param name="userName">User inputs the username to authenticate</param>
    ''' <param name="password">Password to check for authentication</param>
    ''' <returns>Returns (True) for user credentials are good, and (False) for the user credentials failed.</returns>
    ''' <remarks></remarks>
    Public Function authenticateUser(ByVal userName As String, ByVal password As String) As Boolean
        Dim sqlConn As New SqlConnection(recipeDatabaseConnection)
        Dim verifyPassCommand As New SqlCommand()
        Dim utf As New UTF32Encoding
        Dim userHashedPass As String
        Try
            'validate in entities layer
            If EntitiesLayer.EntitiesValidation.validUser(userName, password) Then

                'passwordParam.Value = utf.GetBytes(userPasswordHashed)
                Dim passwordParam As New SqlParameter("@password", SqlDbType.VarBinary)
                passwordParam.Direction = ParameterDirection.Output
                passwordParam.Size = 32

                verifyPassCommand.CommandType = CommandType.StoredProcedure
                verifyPassCommand.CommandText = "dbo.VerifyUser"
                verifyPassCommand.Parameters.AddWithValue("@userName", userName)
                verifyPassCommand.Parameters.Add(passwordParam)
                verifyPassCommand.Connection = sqlConn

                Dim accountIdParam As New SqlParameter("@AccountId", SqlDbType.Int)
                passwordParam.Direction = ParameterDirection.Output
                verifyPassCommand.Parameters.Add(passwordParam)

                Dim retrievedPassword As String
                Dim userPassword As String = getPassword(password)

                If sqlConn.State = ConnectionState.Closed Then
                    sqlConn.Open()
                End If

                verifyPassCommand.ExecuteNonQuery()
                sqlConn.Close()
                If CType(verifyPassCommand.Parameters("@password").Value, Byte()).Length > 0 Then
                    retrievedPassword = utf.GetString(CType(verifyPassCommand.Parameters("@password").Value, Byte()))
                    userHashedPass = userPassword

                    setAccountID(CType(verifyPassCommand.Parameters("@AccountId").Value, Integer))

                    Return verifyPassword(getPassword(userHashedPass), retrievedPassword)

                Else
                    Return False
                End If
            Else
                Return False
            End If

        Catch ex As ArgumentNullException
            Console.WriteLine(ex.ToString)
        Catch ex As ArgumentException
            Console.WriteLine(ex.ToString)
        Catch ex As RankException
            Console.WriteLine(ex.ToString)
        Catch ex As InvalidCastException
            Console.WriteLine(ex.ToString)
        Catch ex As SqlException
            Console.WriteLine(ex.ToString)
        Catch ex As Exception
            Console.WriteLine(ex.ToString)
        Finally
            If Not sqlConn Is Nothing Then
                sqlConn.Dispose()
            End If

            If Not verifyPassCommand Is Nothing Then
                verifyPassCommand.Dispose()
            End If
        End Try
    End Function

    ''' <summary>
    ''' Saves recipe, nutrition details, and recipe ingredients to the database by typed table adapters. 
    ''' </summary>
    ''' <param name="recipes">The typed dataset that holds the new recipe</param>
    ''' <returns>Returns a boolean whether the recipe got saved (True) or not (False)</returns>
    ''' <remarks></remarks>
    Public Function saveRecipe(ByRef recipes As EntitiesLayer.RecipeHolderDatabaseDataSet) As Boolean

        Dim saved = False
        Dim manager As New DataAccess.RecipeHolderDatabaseDataSetTableAdapters.TableAdapterManager

        Try
            Using tScope As New TransactionScope()

                If recipes.HasChanges AndAlso Not recipes.HasErrors Then


                    manager.RecipesTableAdapter = New DataAccess.RecipeHolderDatabaseDataSetTableAdapters.RecipesTableAdapter
                    manager.BackupDataSetBeforeUpdate = True
                    saved = (manager.RecipesTableAdapter.Update(recipes) > 0)

                    manager.NutritionDetailsTableAdapter = New DataAccess.RecipeHolderDatabaseDataSetTableAdapters.NutritionDetailsTableAdapter
                    manager.BackupDataSetBeforeUpdate = True
                    saved = (manager.NutritionDetailsTableAdapter.Update(recipes) > 0)

                    manager.RecipeIngredientTableAdapter = New DataAccess.RecipeHolderDatabaseDataSetTableAdapters.RecipeIngredientTableAdapter
                    manager.BackupDataSetBeforeUpdate = True
                    saved = (manager.RecipeIngredientTableAdapter.Update(recipes) > 0)

                Else
                    'send error message to UI
                End If
            End Using

        Catch ex As Transactions.TransactionAbortedException
            Throw ex
        Catch ex As Transactions.TransactionException
            Throw ex
        Catch ex As InvalidOperationException
            Throw ex
        Catch ex As SqlException
            Throw ex
        Catch ex As Exception
            Throw ex
        Finally
            If Not manager Is Nothing Then
                manager.Dispose()
            End If

        End Try

        'return whether the recipe saved
        Return saved
    End Function

    ''' <summary>
    ''' Sends account info to stored procedure InsertAccount to insert a new record.
    ''' </summary>
    ''' <param name="firstName">First name of the account</param>
    ''' <param name="lastName">Last Name of the account</param>
    ''' <param name="userName">User name of the account</param>
    ''' <param name="password">Password used to access the account</param>
    ''' <param name="email">The e-mail given to the account</param>
    ''' <param name="isProtected">Boolean whether the account should be protected if true, and not if false.</param>
    ''' <returns>Return the inserted account number of the user</returns>
    ''' <remarks></remarks>
    Public Function saveAccount(ByVal firstName As String, ByVal lastName As String, ByVal userName As String, _
                   ByVal password As String, ByVal email As String, ByVal isProtected As Boolean) As Integer
        'Connection, Command and encoding variables
        Dim sqlCommand As New SqlCommand()
        Dim sqlConn As New SqlConnection(My.Settings.RecipeHolderDatabaseConnectionString)
        Dim utf As New UTF32Encoding

        Try
            sqlCommand.CommandType = CommandType.StoredProcedure
            sqlCommand.CommandText = "InsertAccount"
            sqlCommand.Connection = sqlConn

            'setup parameters for the insert of an account
            Dim parmFirst As New SqlParameter("@FirstName", SqlDbType.VarChar)
            parmFirst.Value = Trim(firstName)
            parmFirst.Direction = ParameterDirection.Input

            Dim parmLast As New SqlParameter("@LastName", SqlDbType.VarChar)
            parmLast.Value = Trim(lastName)
            parmLast.Direction = ParameterDirection.Input

            Dim parmUserName As New SqlParameter("@UserName", SqlDbType.VarChar)
            parmUserName.Value = userName
            parmUserName.Direction = ParameterDirection.Input

            Dim parmPass As New SqlParameter("@Password", SqlDbType.VarBinary)
            Dim hashedPassword As String = getPassword(password)
            parmPass.Value = utf.GetBytes(hashedPassword)
            parmPass.Direction = ParameterDirection.Input

            Dim parmEMail As New SqlParameter("@Email", SqlDbType.VarChar)
            parmEMail.Value = email
            parmEMail.Direction = ParameterDirection.Input


            Dim parmProtected As New SqlParameter("@isProtected", SqlDbType.Bit)
            parmProtected.Value = isProtected
            parmProtected.Direction = ParameterDirection.Input


            Dim accountIdProtected As New SqlParameter("@AccountId", SqlDbType.Int)
            accountIdProtected.Direction = ParameterDirection.Output

            'add parameters, Open Connection and execute procedure
            sqlCommand.Parameters.Add(parmFirst)
            sqlCommand.Parameters.Add(parmLast)
            sqlCommand.Parameters.Add(parmUserName)
            sqlCommand.Parameters.Add(parmPass)
            sqlCommand.Parameters.Add(parmProtected)
            sqlCommand.Parameters.Add(parmEMail)
            sqlCommand.Parameters.Add(accountIdProtected)

            sqlConn.Open()
            sqlCommand.ExecuteNonQuery()

            'return the inserted account number
            Return CInt(sqlCommand.Parameters("@AccountId").Value)

        Catch ex As InvalidOperationException
            Throw ex
        Catch ex As SqlException
            Console.WriteLine(ex.ToString)
        Catch ex As Exception
            Console.WriteLine(ex.ToString)
        Finally
            If Not sqlConn Is Nothing Then
                sqlConn.Dispose()
            End If

            If Not sqlCommand Is Nothing Then
                sqlCommand.Dispose()
            End If
        End Try
    End Function

    Public Function updateAccount(ByVal firstName As String, ByVal lastName As String, ByVal userName As String, ByVal newPassword As String, ByVal isProtected As Boolean, ByVal eMail As String, ByVal accountId As Integer) As Boolean
        Dim sqlAccountAdapt As New RecipeHolderDatabaseDataSetTableAdapters.AccountsTableAdapter
        Dim rowsUpdated As Integer = 0
        Dim utf As New Text.UTF32Encoding
        Dim hashedPassword As String = getPassword(newPassword)

        Try

            rowsUpdated = sqlAccountAdapt.Update(firstName, lastName, userName, utf.GetBytes(hashedPassword), isProtected, accountId, eMail)

            If rowsUpdated > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As InvalidOperationException
            Throw ex
        Catch ex As SqlException
            Throw ex
        Catch ex As Exception
            Throw ex
        Finally
            If Not sqlAccountAdapt Is Nothing Then
                sqlAccountAdapt.Dispose()
            End If
        End Try
    End Function

    ''' <summary>
    ''' Retrieves profiles that are either protected or not protected based on the boolean isProtected
    ''' </summary>
    ''' <param name="isProtected">Tells the stored procedure to return protected accounts if true, unprotected if false </param>
    ''' <returns>returns a typed account datatable</returns>
    ''' <remarks></remarks>
    Public Function getAccountProfiles(ByVal isProtected As Boolean) As EntitiesLayer.RecipeHolderDatabaseDataSet.AccountsDataTable
        Dim profiles As New EntitiesLayer.RecipeHolderDatabaseDataSet.AccountsDataTable
        Dim sqlConn As New SqlConnection(recipeDatabaseConnection)
        Dim profileCommand As New SqlCommand()
        Dim sqlAccountAdapter As New RecipeHolderDatabaseDataSetTableAdapters.AccountsTableAdapter


        Try
            'check the connection to see if its open. Open it if its not
            If sqlConn.State = ConnectionState.Closed Then
                sqlConn.Open()
            End If

            'Fill the typed datatable 
            sqlAccountAdapter.Fill(profiles, isProtected)

        Catch ex As InvalidOperationException
            Throw ex
        Catch ex As SqlException
            Throw ex
        Catch ex As Exception
            Throw ex
        Finally
            If Not sqlConn Is Nothing Then
                sqlConn.Dispose()
            End If

            If Not sqlAccountAdapter Is Nothing Then
                sqlAccountAdapter.Dispose()
            End If

            If Not profileCommand Is Nothing Then
                profileCommand.Dispose()
            End If
        End Try

        'return typed datatable no matter what
        Return profiles
    End Function

    ''' <summary>
    ''' Retrieves the current user profile.
    ''' </summary>
    ''' <returns>returns the current profile in a typed datatable</returns>
    ''' <remarks></remarks>
    Public Function searchAccountProfile(ByVal accountId As Integer, ByVal firstName As String, ByVal lastName As String, _
                                         ByVal eMail As String, ByVal dateCreated As Nullable(Of Date), ByVal isProtected As Nullable(Of Boolean)) As EntitiesLayer.RecipeHolderDatabaseDataSet.AccountsDataTable
        Dim sqlComm As New SqlCommand
        Dim sqlconn As New SqlConnection(recipeDatabaseConnection)
        Dim dt As New RecipeHolderDatabaseDataSet.AccountsDataTable
        Dim sqlParamAccountId As SqlParameter
        Dim sqlParamFirstName As SqlParameter
        Dim sqlParamLastName As SqlParameter
        Dim sqlParamEMail As SqlParameter
        Dim sqlParamDateCreated As SqlParameter
        Dim sqlParamIsProtected As SqlParameter


        Try

            'Check for NULL values. If arguements are null, set it to a database NULL

            If accountId = -1 Then
                sqlParamAccountId = New SqlParameter("@accountId", DBNull.Value)
            Else
                sqlParamAccountId = New SqlParameter("@accountId", accountId)
            End If

            If firstName.Length <= 0 Then
                sqlParamFirstName = New SqlParameter("@firstName", DBNull.Value)
            Else
                sqlParamFirstName = New SqlParameter("@firstName", firstName)
            End If

            If lastName.Length <= 0 Then
                sqlParamLastName = New SqlParameter("@lastName", DBNull.Value)
            Else
                sqlParamLastName = New SqlParameter("@lastName", lastName)
            End If

            If eMail.Length <= 0 Then
                sqlParamEMail = New SqlParameter("@eMail", DBNull.Value)
            Else
                sqlParamEMail = New SqlParameter("@eMail", eMail)
            End If

            If dateCreated.HasValue = Nothing Then
                sqlParamDateCreated = New SqlParameter("@dateCreated", DBNull.Value)
            Else
                sqlParamDateCreated = New SqlParameter("@dateCreated", dateCreated)
            End If


            If isProtected.HasValue = Nothing Then
                sqlParamIsProtected = New SqlParameter("@isProtected", DBNull.Value)
            Else
                sqlParamIsProtected = New SqlParameter("@isProtected", isProtected)
            End If
           
            ''Add the parameters to the sqlcommand
            sqlComm.Parameters.Add(sqlParamAccountId)
            sqlComm.Parameters.Add(sqlParamFirstName)
            sqlComm.Parameters.Add(sqlParamLastName)
            sqlComm.Parameters.Add(sqlParamEMail)
            sqlComm.Parameters.Add(sqlParamDateCreated)
            sqlComm.Parameters.Add(sqlParamIsProtected)

            With sqlComm
                .Connection = sqlconn
                .CommandType = CommandType.StoredProcedure
                .CommandText = "dbo.SearchForProfile"
            End With


            'Check to see if connection is open
            'Open sql connection if not open and execute the reader, load into the typed datatable
            If sqlconn.State = ConnectionState.Closed Then
                sqlconn.Open()
            End If

            dt.Load(sqlComm.ExecuteReader())

            Return dt
        Catch ex As InvalidOperationException
            Throw ex
        Catch ex As SqlException
            Throw ex
        Catch ex As Exception
            Throw ex
        Finally
            If Not sqlComm Is Nothing Then
                sqlComm.Dispose()
            End If

            If Not sqlconn Is Nothing Then
                sqlconn.Dispose()
            End If
        End Try

    End Function

    ''' <summary>
    ''' Searches the database for recipes based on criteria input 
    ''' </summary>
    ''' <param name="recipeName">Can be zero length. The recipe name to search</param>
    ''' <param name="prepTimeMin">Can be null, pass -1 for null. Mininium preparation value to input</param>
    ''' <param name="prepTimeMax">Can be null, pass -1 for null. The max value for preparation time</param>
    ''' <param name="courseTypeId">Can be null, pass -1 for null.The course id to search for.</param>
    ''' <param name="cuisinetypeId">Can be null, pass -1 for null. The cuisine id to search for.</param>
    ''' <param name="accountId">The current user account id used to pick users recipes out.Can not be null</param>
    ''' <remarks></remarks>
    Public Sub searchRecipes(ByRef recipeData As RecipeHolderDatabaseDataSet, ByVal recipeName As String, ByVal prepTimeMin As Integer, _
        ByVal prepTimeMax As Integer, ByVal courseTypeId As Integer, _
        ByVal cuisinetypeId As Integer, ByVal accountId As Integer)

        'Declare the query objects, and typed recipe table adapter
        Dim sqlconn As New SqlConnection(recipeDatabaseConnection)
        Dim sqlcomm As New SqlCommand
        Dim sqlCommIngredient As New SqlCommand
        'Declare the parameters for the sqlcommand object
        Dim sqlParamAcountId As SqlParameter
        Dim sqlParamRecipeName As SqlParameter
        Dim sqlParamPrepTimeMin As SqlParameter
        Dim sqlParamPrepTimeMax As SqlParameter
        Dim sqlParamCoursetypeId As SqlParameter
        Dim sqlParamCuisineTypeId As SqlParameter
        Dim recipeDt As New RecipeHolderDatabaseDataSet.RecipesDataTable

        Try

            '*********************************Get Recipe table data*********************************
            'This parameter cant be set to null
            sqlParamAcountId = New SqlParameter("@accountId", accountId)

            'Setup the sqlcommand
            With sqlcomm
                .CommandType = CommandType.StoredProcedure
                .Connection = sqlconn
                .CommandText = "dbo.SearchForRecipes"
            End With

            'Check for NULL values. If arguements are null, set it to a database NULL
            If recipeName.Length <= 0 Then
                sqlParamRecipeName = New SqlParameter("@recipeName", DBNull.Value)
            Else
                sqlParamRecipeName = New SqlParameter("@recipeName", recipeName)
            End If

            If prepTimeMin = -1 Then
                sqlParamPrepTimeMin = New SqlParameter("@preparationTimeMin", DBNull.Value)
            Else
                sqlParamPrepTimeMin = New SqlParameter("@preparationTimeMin", prepTimeMin)
            End If

            If prepTimeMax = -1 Then
                sqlParamPrepTimeMax = New SqlParameter("@preparationTimeMax", DBNull.Value)
            Else
                sqlParamPrepTimeMax = New SqlParameter("@preparationTimeMax", prepTimeMax)
            End If

            If courseTypeId = -1 Then
                sqlParamCoursetypeId = New SqlParameter("@courseTypeID", DBNull.Value)
            Else
                sqlParamCoursetypeId = New SqlParameter("@courseTypeID", courseTypeId)
            End If

            If cuisinetypeId = -1 Then
                sqlParamCuisineTypeId = New SqlParameter("@cuisineTypeID", DBNull.Value)
            Else
                sqlParamCuisineTypeId = New SqlParameter("@cuisineTypeID", cuisinetypeId)
            End If


            'Add the parameters to the sqlcommand
            sqlcomm.Parameters.Add(sqlParamAcountId)
            sqlcomm.Parameters.Add(sqlParamRecipeName)
            sqlcomm.Parameters.Add(sqlParamPrepTimeMin)
            sqlcomm.Parameters.Add(sqlParamPrepTimeMax)
            sqlcomm.Parameters.Add(sqlParamCoursetypeId)
            sqlcomm.Parameters.Add(sqlParamCuisineTypeId)


            'Open sql connection if not open and execute the reader, load into the typed datatable

            'check to see if connection is already open
            If Not sqlconn.State = ConnectionState.Open Then
                sqlconn.Open()
            End If

            'load into typed dataset
            recipeData.Recipes.Load(sqlcomm.ExecuteReader())

            'do new query
            sqlcomm = New SqlCommand(recipeDatabaseConnection)

            'setup new query
            With sqlcomm
                .CommandText = "dbo.RetrieveNutritionDetailsById"
                .Connection = sqlconn
                .CommandType = CommandType.StoredProcedure
            End With

            'setup ingredient query
            sqlCommIngredient = New SqlCommand(recipeDatabaseConnection)

            With sqlCommIngredient
                .CommandText = "dbo.RetrieveRecipeIngredientById"
                .Connection = sqlconn
                .CommandType = CommandType.StoredProcedure
            End With

            'Recipes are already loaded so then load the ingredients, and nutrition details
            'that go with the recipes
            For Each row As RecipeHolderDatabaseDataSet.RecipesRow In recipeData.Recipes
                sqlcomm.Parameters.Clear()
                sqlCommIngredient.Parameters.Clear()

                Dim recipeId As Integer = CInt(row.Item("RecipeID"))
                sqlcomm.Parameters.AddWithValue("@recipeId", recipeId)
                sqlCommIngredient.Parameters.AddWithValue("@recipeId", recipeId)
                recipeData.NutritionDetails.Load(sqlcomm.ExecuteReader)
                recipeData.RecipeIngredient.Load(sqlCommIngredient.ExecuteReader)

            Next


        Catch ex As InvalidOperationException
            Throw ex
        Catch ex As SqlException
            Throw ex
        Catch ex As Exception
            Throw ex
        Finally
            If Not sqlcomm Is Nothing Then
                sqlcomm.Dispose()
            End If

            If Not sqlconn Is Nothing Then
                sqlconn.Dispose()
            End If

            If Not sqlCommIngredient Is Nothing Then
                sqlCommIngredient.Dispose()
            End If

        End Try


    End Sub

    ''' <summary>
    ''' Retrieves a list of courses from a stored procedure and sends the data back in a typed courses datatable
    ''' </summary>
    ''' <returns>Typed courses datatable with all of the couses in the database.</returns>
    ''' <remarks></remarks>
    Public Function retrieveCourses() As RecipeHolderDatabaseDataSet.CoursesDataTable

        Dim coursesAdaptor As New DataAccess.RecipeHolderDatabaseDataSetTableAdapters.CoursesTableAdapter
        Dim dt As New EntitiesLayer.RecipeHolderDatabaseDataSet.CoursesDataTable

        Try
            'fill and return the typed datatable
            coursesAdaptor.Fill(dt)
            Return dt

        Catch ex As InvalidOperationException
            Throw ex
        Catch ex As SqlException
            Throw ex
        Catch ex As Exception
            Throw ex
        Finally
            If Not coursesAdaptor Is Nothing Then
                coursesAdaptor.Dispose()
            End If
        End Try

    End Function

    ''' <summary>
    ''' Retrieves a list of cuisines from a stored procedure and sends the data back in a typed cuisine datatable
    ''' </summary>
    ''' <returns>Typed cuisine datatable with all of the couses in the database.</returns>
    ''' <remarks></remarks>
    Public Function retrieveCuisines() As RecipeHolderDatabaseDataSet.CuisineTypesDataTable

        Dim cuisineAdator As New DataAccess.RecipeHolderDatabaseDataSetTableAdapters.CuisineTypesTableAdapter
        Dim dt As New EntitiesLayer.RecipeHolderDatabaseDataSet.CuisineTypesDataTable

        Try
            'fill and return typed datatable
            cuisineAdator.Fill(dt)


        Catch ex As InvalidOperationException
            Throw ex
        Catch ex As SqlException
            Throw ex
        Catch ex As Exception
            Throw ex

        Finally
            If Not cuisineAdator Is Nothing Then
                cuisineAdator.Dispose()
            End If
        End Try


        Return dt
    End Function

    ''' <summary>
    ''' Retrieves a list of Unit Measurements using sqlcommand and sqladapter
    ''' </summary>
    ''' <returns>Datatable with a list of Unit Measurements</returns>
    ''' <remarks></remarks>
    Public Function retrieveUnitMeasurements() As EntitiesLayer.RecipeHolderDatabaseDataSet.UnitMeasurementsDataTable
        Dim sqlconn As New SqlConnection(recipeDatabaseConnection)
        Dim sqlComm As New SqlCommand()
        Dim sqlAdapt As New SqlDataAdapter()
        Dim unitMeasurementDT As New RecipeHolderDatabaseDataSet.UnitMeasurementsDataTable

        Try

       
            'open connection, fill Unit Measurement table, then close connection, dispose objects 
            'and return datatable 
            With sqlComm
                .CommandText = "dbo.RetrieveAllUnitMeasurements"
                .CommandType = CommandType.StoredProcedure
                .Connection = sqlconn
            End With
            'set adapter's select command, also could use typed datadapter here
            sqlAdapt.SelectCommand = sqlComm


            'check to see if connection is already open
            If Not sqlconn.State = ConnectionState.Open Then
                sqlconn.Open()
            End If

            'fill the unit measurement table
            sqlAdapt.Fill(unitMeasurementDT)

        Catch ex As InvalidOperationException
            Throw ex
        Catch ex As SqlException
            Throw ex
        Catch ex As Exception
            Throw ex

        Finally
            If Not sqlconn Is Nothing Then
                sqlconn.Close()
            End If

            If Not sqlComm Is Nothing Then
                sqlComm.Dispose()
            End If

            If Not sqlAdapt Is Nothing Then
                sqlAdapt.Dispose()
            End If
        End Try

        'Return the table
        Return unitMeasurementDT
    End Function

    ''' <summary>
    ''' Retrieves a list of Ingredients
    ''' </summary>
    ''' <returns>Datatable with a list of Ingredients</returns>
    ''' <remarks></remarks>
    Public Function retrieveIngredients() As EntitiesLayer.RecipeHolderDatabaseDataSet.IngredientsDataTable
        Dim sqlconn As New SqlConnection(recipeDatabaseConnection)
        Dim sqlComm As New SqlCommand()
        Dim sqlAdapt As New SqlDataAdapter()
        Dim ingredientsDT As New EntitiesLayer.RecipeHolderDatabaseDataSet.IngredientsDataTable

        Try
            'open connection, fill ingredients table, then close connection, dispose objects and return datatable 
            With sqlComm
                .CommandText = "dbo.RetrieveAllIngredients"
                .CommandType = CommandType.StoredProcedure
                .Connection = sqlconn
            End With
            'set adapter's select command, also could use typed datadapter here
            sqlAdapt.SelectCommand = sqlComm

            'check to see if connection is already open
            If Not sqlconn.State = ConnectionState.Open Then
                sqlconn.Open()
            End If

            sqlAdapt.Fill(ingredientsDT)

        Catch ex As InvalidOperationException
            Throw ex
        Catch ex As SqlException
            Throw ex
        Catch ex As Exception
            Throw ex

        Finally
            If Not sqlconn Is Nothing Then
                sqlconn.Close()
            End If

            If Not sqlComm Is Nothing Then
                sqlComm.Dispose()
            End If

            If Not sqlAdapt Is Nothing Then
                sqlAdapt.Dispose()
            End If
        End Try
       
        'return Ingredient Table
        Return ingredientsDT
    End Function

    ''' <summary>
    ''' Retrieves a list of food types
    ''' </summary>
    ''' <returns>Datatable with a list of food types</returns>
    ''' <remarks></remarks>
    Public Function retrieveFoodTypes() As EntitiesLayer.RecipeHolderDatabaseDataSet.FoodTypesDataTable
        Dim sqlconn As New SqlConnection(recipeDatabaseConnection)
        Dim sqlComm As New SqlCommand()
        Dim sqlAdapt As New SqlDataAdapter()
        Dim ingredientTypeDT As New EntitiesLayer.RecipeHolderDatabaseDataSet.FoodTypesDataTable

        Try
            'open connection, fill ingredient type table, then close connection, dispose objects and return datatable
            With sqlComm
                .CommandText = "dbo.RetrieveAllFoodTypes"
                .CommandType = CommandType.StoredProcedure
                .Connection = sqlconn
            End With

            'set adapter's select command, also could use typed datadapter here = less code:)
            sqlAdapt.SelectCommand = sqlComm

            'check to see if connection is already open
            If Not sqlconn.State = ConnectionState.Open Then
                sqlconn.Open()
            End If

            'Fill the table
            sqlAdapt.Fill(ingredientTypeDT)

        Catch ex As InvalidOperationException
            Throw ex
        Catch ex As SqlException
            Throw ex
        Catch ex As Exception
            Throw ex
        Finally
            If Not sqlconn Is Nothing Then
                sqlconn.Close()
            End If

            If Not sqlComm Is Nothing Then
                sqlComm.Dispose()
            End If

            If Not sqlAdapt Is Nothing Then
                sqlAdapt.Dispose()
            End If
            sqlAdapt.Dispose()
        End Try

        'return the ingredienttype table
        Return ingredientTypeDT
    End Function

    ''' <summary>
    ''' Loads the recipeIngredientTable by the account number given
    ''' </summary>
    ''' <param name="recipeData">referenced typed dataset</param>
    ''' <param name="accountId">account id to get the recipeIngredients for</param>
    ''' <remarks></remarks>
    Public Sub retrieveRecipeIngredientTableByAccount(ByRef recipeData As EntitiesLayer.RecipeHolderDatabaseDataSet, ByVal accountId As Integer)

        Dim recipeAdapt As New DataAccess.RecipeHolderDatabaseDataSetTableAdapters.RetrieveRecipeTableTableAdapter
        Dim recipeIngredientAdapt As New DataAccess.RecipeHolderDatabaseDataSetTableAdapters.RetrieveRecipeIngredientTableTableAdapter
        Dim NutritionAdapt As New DataAccess.RecipeHolderDatabaseDataSetTableAdapters.NutritionDetailsTableAdapter

        Try
            'Fill the recipes, then recipeIngredients, then the nutrition details
            recipeAdapt.Fill(recipeData.RetrieveRecipeTable, accountId)
            recipeIngredientAdapt.Fill(recipeData.RetrieveRecipeIngredientTable)
            NutritionAdapt.FillBy(recipeData.NutritionDetails)

        Catch ex As InvalidOperationException
            Throw ex
        Catch ex As SqlException
            Throw ex
        Catch ex As Exception
            Throw ex
        Finally
            If Not recipeAdapt Is Nothing Then
                recipeAdapt.Dispose()
            End If

            If Not recipeIngredientAdapt Is Nothing Then
                recipeIngredientAdapt.Dispose()
            End If

            If Not NutritionAdapt Is Nothing Then
                NutritionAdapt.Dispose()
            End If

        End Try
    End Sub

    ''' <summary>
    ''' Searches for records in the recipeIngredientTable
    ''' </summary>
    ''' <param name="recipedata">Typed dataset by reference</param>
    ''' <param name="recipeName">The recipe name to search for</param>
    ''' <param name="ingredientName">ingredient name to search for</param>
    ''' <param name="courseName">course name to search for</param>
    ''' <param name="cuisineName">cuisine name to search for</param>
    ''' <param name="prepMin">mininum preparation limit</param>
    ''' <param name="prepMax">maximum preparation limit</param>
    ''' <remarks></remarks>
    Public Sub searchRecipeIngredientTable(ByRef recipedata As EntitiesLayer.RecipeHolderDatabaseDataSet, _
                                           ByVal recipeName As String, ByVal ingredientName As String, _
                                           ByVal courseName As String, ByVal cuisineName As String, _
                                           ByVal prepMin As Nullable(Of Integer), ByVal prepMax As Nullable(Of Integer))


        Dim recipeAdapt As New DataAccess.RecipeHolderDatabaseDataSetTableAdapters.RetrieveRecipeTableTableAdapter
        Dim recipeIngredientAdapt As New DataAccess.RecipeHolderDatabaseDataSetTableAdapters.RetrieveRecipeIngredientTableTableAdapter
        Dim NutritionAdapt As New DataAccess.RecipeHolderDatabaseDataSetTableAdapters.NutritionDetailsTableAdapter

        Try
            'Set the nulls
            If ingredientName = "" Then
                ingredientName = Nothing
            End If

            If recipeName = "" Then
                recipeName = Nothing
            End If

            'Fill the recipes, recipeIngredients, nutrion details
            recipeAdapt.FillBySearch(recipedata.RetrieveRecipeTable, recipeName, _
                                     courseName, cuisineName, prepMax, prepMin)
            recipeIngredientAdapt.FillBySearch(recipedata.RetrieveRecipeIngredientTable, ingredientName)

            NutritionAdapt.FillByRetrieveRecipeTable(recipedata.NutritionDetails)


        Catch ex As InvalidOperationException
            Throw ex
        Catch ex As SqlException
            Throw ex
        Catch ex As Exception
            Throw ex
        Finally
            If Not recipeAdapt Is Nothing Then
                recipeAdapt.Dispose()
            End If

            If Not recipeIngredientAdapt Is Nothing Then
                recipeIngredientAdapt.Dispose()
            End If

            If Not NutritionAdapt Is Nothing Then
                NutritionAdapt.Dispose()
            End If

        End Try
    End Sub

    Public Function checkIngredientExist(ByVal ingredientName As String) As Boolean
        Dim sqlconn As New SqlConnection(recipeDatabaseConnection)
        Dim sqlComm As New SqlCommand

        Try
            'Create a new sqlcommand and set up the sqlcommand to check if foodType id exists

            With sqlComm
                .CommandType = CommandType.StoredProcedure
                .CommandText = "dbo.CheckIngredientExists"
                .Connection = sqlconn
            End With

            'Create exists parameter
            Dim existsParam As New SqlParameter("@Exists", SqlDbType.Bit)
            existsParam.Direction = ParameterDirection.Output

            'Create Name Parameter 
            Dim nameParam As New SqlParameter("@Name", SqlDbType.VarChar)
            nameParam.Direction = ParameterDirection.Input
            nameParam.Value = ingredientName

            'Add parameter
            sqlComm.Parameters.Add(nameParam)
            sqlComm.Parameters.Add(existsParam)

            'Open the connection and execute the CheckInsertIngredientExists stored procedure
            If Not sqlconn.State = ConnectionState.Open Then
                sqlconn.Open()
            End If

            sqlComm.ExecuteNonQuery()
            Return CBool(sqlComm.Parameters("@Exists").Value)

        Catch ex As InvalidOperationException
            Throw ex
        Catch ex As SqlException
            Throw ex
        Catch ex As Exception
            Throw ex
        Finally
            If Not sqlconn Is Nothing Then
                sqlconn.Close()
            End If

            If Not sqlComm Is Nothing Then
                sqlComm.Dispose()
            End If

        End Try

    End Function

    Public Function insertIngredient(ByVal ingredientName As String, ByVal foodTypeId As Integer) As Integer
        Dim sqlAdpatIngredients As New RecipeHolderDatabaseDataSetTableAdapters.IngredientsTableAdapter

        Try
            Return sqlAdpatIngredients.Insert(ingredientName, foodTypeId)

        Catch ex As InvalidOperationException
            Throw ex
        Catch ex As SqlException
            Throw ex
        Catch ex As Exception
            Throw ex
        Finally
            If Not sqlAdpatIngredients Is Nothing Then
                sqlAdpatIngredients.Dispose()
            End If

        End Try
    End Function

    Public Function viewIngredientsFoodTypes() As RecipeHolderDatabaseDataSet.ViewIngredientsFoodTypesDataTable
        Dim sqlIngredientFoodTypeAdapt As New RecipeHolderDatabaseDataSetTableAdapters.ViewIngredientsFoodTypesTableAdapter
        Dim dt As New RecipeHolderDatabaseDataSet.ViewIngredientsFoodTypesDataTable
        
        Try

            sqlIngredientFoodTypeAdapt.Fill(dt)

            Return dt
        Catch ex As InvalidOperationException
            Throw ex
        Catch ex As SqlException
            Throw ex
        Catch ex As Exception
            Throw ex
        Finally
            If Not sqlIngredientFoodTypeAdapt Is Nothing Then
                sqlIngredientFoodTypeAdapt.Dispose()
            End If

        End Try
    End Function


    Public Function updateIngredients(ByVal recipData As RecipeHolderDatabaseDataSet) As Boolean
        Dim ingredientAdapt As New RecipeHolderDatabaseDataSetTableAdapters.IngredientsTableAdapter
        Dim rowsUpdated As Integer = 0

        Try
            'Update the deleted rows first, next the updated rows, then the new rows
            rowsUpdated += ingredientAdapt.Update(recipData.Ingredients.Select("", "", DataViewRowState.Deleted))
            rowsUpdated += ingredientAdapt.Update(recipData.Ingredients.Select("", "", DataViewRowState.ModifiedCurrent))
            rowsUpdated += ingredientAdapt.Update(recipData.Ingredients.Select("", "", DataViewRowState.Added))

            'return true if rows were updated
            Return rowsUpdated > 0

        Catch ex As SqlException
            Throw ex
        Catch ex As Exception
            Throw ex

        Finally
            If Not ingredientAdapt Is Nothing Then
                ingredientAdapt.Dispose()
            End If
        End Try

    End Function

    Public Function updateCourses(ByVal recipData As EntitiesLayer.RecipeHolderDatabaseDataSet) As Boolean
        Dim coursesAdapt As New RecipeHolderDatabaseDataSetTableAdapters.CoursesTableAdapter
        Dim rowsUpdated As Integer = 0

        Try
            'Update the deleted rows first, next the updated rows, then the new rows
            rowsUpdated += coursesAdapt.Update(recipData.Courses.Select("", "", DataViewRowState.Deleted))
            rowsUpdated += coursesAdapt.Update(recipData.Courses.Select("", "", DataViewRowState.ModifiedCurrent))
            rowsUpdated += coursesAdapt.Update(recipData.Courses.Select("", "", DataViewRowState.Added))

            'return true if rows were updated
            Return rowsUpdated > 0

        Catch ex As SqlException
            Throw ex
        Catch ex As Exception
            Throw ex
        Finally
            If Not coursesAdapt Is Nothing Then
                coursesAdapt.Dispose()
            End If
        End Try


    End Function

    Public Function updateCuisines(ByVal recipData As RecipeHolderDatabaseDataSet) As Boolean
        Dim cuisineAdapt As New RecipeHolderDatabaseDataSetTableAdapters.CuisineTypesTableAdapter
        Dim rowsUpdated As Integer = 0

        Try
            'Update the deleted rows first, next the updated rows, then the new rows
            rowsUpdated += cuisineAdapt.Update(recipData.CuisineTypes.Select("", "", DataViewRowState.Deleted))
            rowsUpdated += cuisineAdapt.Update(recipData.CuisineTypes.Select("", "", DataViewRowState.ModifiedCurrent))
            rowsUpdated += cuisineAdapt.Update(recipData.CuisineTypes.Select("", "", DataViewRowState.Added))
            'return true if rows were updated
            Return rowsUpdated > 0

        Catch ex As SqlException
            Throw ex
        Catch ex As Exception
            Throw ex

        Finally
            If Not cuisineAdapt Is Nothing Then
                cuisineAdapt.Dispose()
            End If
        End Try

    End Function

    Public Function updateFoodTypes(ByVal recipData As RecipeHolderDatabaseDataSet) As Boolean
        Dim foodTypeAdapt As New RecipeHolderDatabaseDataSetTableAdapters.FoodTypesTableAdapter
        Dim rowsUpdated As Integer = 0

        Try
            'Update the deleted rows first, next the updated rows, then the new rows
            rowsUpdated += foodTypeAdapt.Update(recipData.FoodTypes.Select("", "", DataViewRowState.Deleted))
            rowsUpdated += foodTypeAdapt.Update(recipData.FoodTypes.Select("", "", DataViewRowState.ModifiedCurrent))
            rowsUpdated += foodTypeAdapt.Update(recipData.FoodTypes.Select("", "", DataViewRowState.Added))
            'return true if rows were updated
            Return rowsUpdated > 0

        Catch ex As SqlException
            Throw ex
        Catch ex As Exception
            Throw ex
        Finally
            If Not foodTypeAdapt Is Nothing Then
                foodTypeAdapt.Dispose()
            End If
        End Try
    End Function

    Public Function updateUnitMeasurements(ByVal recipData As RecipeHolderDatabaseDataSet) As Boolean
        Dim unitMeasurementAdapt As New RecipeHolderDatabaseDataSetTableAdapters.UnitMeasurementsTableAdapter
        Dim rowsUpdated As Integer = 0

        Try
            'Update the deleted rows first, next the updated rows, then the new rows
            rowsUpdated += unitMeasurementAdapt.Update(recipData.UnitMeasurements.Select("", "", DataViewRowState.Deleted))
            rowsUpdated += unitMeasurementAdapt.Update(recipData.UnitMeasurements.Select("", "", DataViewRowState.ModifiedCurrent))
            rowsUpdated += unitMeasurementAdapt.Update(recipData.UnitMeasurements.Select("", "", DataViewRowState.Added))
            'return true if rows were updated
            Return rowsUpdated > 0

        Catch ex As SqlException
            Throw ex
        Catch ex As Exception
            Throw ex
        Finally
            If Not unitMeasurementAdapt Is Nothing Then
                unitMeasurementAdapt.Dispose()
            End If
        End Try
    End Function

#Region "Authenticate User Functions"

    ''' <summary>
    ''' Creates a random number between 8 and 24. Uses the RNGCryptoServiceProvider to randomize a number
    ''' </summary>
    ''' <returns>The random number in integer form.</returns>
    ''' <remarks></remarks>
    Private Function createRandomNumber() As Integer
        Dim randomBytes(4) As Byte
        Dim seed As Integer = 0

        ' Generate 4 random bytes.    
        Dim randNumGenerator As New RNGCryptoServiceProvider()
        randNumGenerator.GetBytes(randomBytes)

        ' Convert 4 bytes into a 32-bit integer value. 
        seed = (randomBytes(0) And &H7F) << 24 Or randomBytes(1) << 16 Or randomBytes(2) << 8 Or randomBytes(3)

        Dim rand As New Random(seed)

        ' return a random number between 8 and 24    
        Return rand.Next(8, 24)
    End Function

    ''' <summary>
    ''' Generates random salt using variable length. Use Rfc2898DeriveBytes against brute force attacks.
    ''' </summary>
    ''' <param name="input">password user enters</param>
    ''' <param name="saltSize">salt size </param>
    ''' <returns>salt bytes</returns>
    ''' <remarks></remarks>
    Private Shared Function getSaltBytes(ByVal input As String, ByVal saltSize As Integer) As Byte()
        Dim saltBytes As Byte() = Nothing
        Dim rfc2 As Rfc2898DeriveBytes
        Try
            rfc2 = New Rfc2898DeriveBytes(input, saltSize, 1000)

            saltBytes = rfc2.Salt

        Catch ex As Exception
            Throw
        Finally
            rfc2 = Nothing
        End Try

        Return saltBytes
    End Function

    ''' <summary>
    ''' Encrypts a password using a variable length random salt bytes. SHA512 is used.
    ''' </summary>
    ''' <param name="input">The password to hash</param>
    ''' <param name="saltBytes">The salt bytes to hash with</param>
    ''' <returns>The hash</returns>
    ''' <remarks></remarks>
    Private Shared Function encrypt(ByVal input As String, ByVal saltBytes As Byte()) As String
        Dim inputBytes As Byte() = Encoding.UTF8.GetBytes(input)
        Dim saltAndInput As Byte() = New Byte(saltBytes.Length + (inputBytes.Length - 1)) {}
        Buffer.BlockCopy(saltBytes, 0, saltAndInput, 0, saltBytes.Length)
        Buffer.BlockCopy(inputBytes, 0, saltAndInput, saltBytes.Length, inputBytes.Length)
        Dim data As Byte() = getHashInternal(saltAndInput)
        Dim ss As Byte = Convert.ToByte(saltBytes.Length)

        Dim finalBytes As Byte() = New Byte(1 + saltBytes.Length + (data.Length - 1)) {}
        finalBytes(0) = ss
        Buffer.BlockCopy(saltBytes, 0, finalBytes, 1, saltBytes.Length)
        Buffer.BlockCopy(data, 0, finalBytes, saltBytes.Length + 1, data.Length)
        Dim hash As String = Convert.ToBase64String(finalBytes)
        Return hash
    End Function

    ''' <summary>
    ''' Iterates over the hashed output 1000 times for better security. Convert input string into a byte array
    ''' </summary>
    ''' <param name="input">Password or password and salt together to hash.</param>
    ''' <returns>returns the computed byte array</returns>
    ''' <remarks></remarks>
    Friend Shared Function getHashInternal(ByVal input As Byte()) As Byte()
        Using ha As HashAlgorithm = New SHA512CryptoServiceProvider()
            Dim data As Byte() = New Byte(input.Length - 1) {}
            Array.Copy(input, data, input.Length)
            For i As Integer = 0 To 999
                data = ha.ComputeHash(data)
            Next
            Return data
        End Using
    End Function

    ''' <summary>
    ''' Creates a random salt size with CreateRandomNumber()
    ''' Next, using the random salt size, GetSaltBytes() creates a random salt 
    ''' Finally the password is hashed with Encrypt()
    ''' </summary>
    ''' <param name="input">The password to hash</param>
    ''' <returns>The hash is returned</returns>
    ''' <remarks></remarks>
    Private Function getPassword(ByVal input As String) As String
        'create a random salt size
        Dim saltSize As Integer = createRandomNumber()
        'generate a random salt
        Dim saltBytes As Byte() = getSaltBytes(input, saltSize)
        'encrypt the password
        Dim hash As String = encrypt(input, saltBytes)
        'return the hash
        Return hash
    End Function

    ''' <summary>
    ''' Verifies the password that the user enters against the hash in the database. 
    ''' The user password has to be hashed in order to test it against the database hashed password.
    ''' </summary>
    ''' <param name="input">user inputs password</param>
    ''' <param name="encPassword">the encrypted password in the database</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function verifyPassword(ByVal input As String, ByVal encPassword As String) As Boolean
        ' convert encrypted password to bytes
        Dim finalBytes As Byte() = Convert.FromBase64String(encPassword)
        Dim match As Boolean
        ' get salt size
        Dim saltSize As Integer = Convert.ToInt32(finalBytes(0))
        Try


            'get raw salt 
            Dim saltBytes As Byte() = New Byte(saltSize - 1) {} '-1

            Array.Copy(finalBytes, 1, saltBytes, 0, saltSize)

            ' using this recovered salt
            ' generate a crypto hash of the input string
            Dim hash As String = encrypt(input, saltBytes)

            ' check for match
            match = ([String].Compare(encPassword, hash, False) = 0)
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try

        Return match
    End Function

#End Region



End Class
