Code Fun

RequestFindVoter.cs

using System.Collections.Generic;
using ExpressPoll.Core.CBE.Pdm.Model;

namespace ExpressPoll.Core.CBE.Services.Business.Voter
{
    public class RequestFindVoter : RequestBase
    {
        public SearchByEnum SearchBy { get; private set; }
        public SearchAreaEnum SearchArea { get; private set; }

        public string LastName { get; private set; }
        public string FirstName { get; private set; }
        public string MiddleInitial { get; private set; }
        public string Zipcode { get; private set; }
        public string BirthDate { get; private set; }

        public string VoterId { get; private set; }
        public string DriversLicense { get; private set; }
        public string AffidavitNumber { get; private set; }

        public string StreetName { get; private set; }
        public string HouseNumber { get; private set; }
        public string AptNumber { get; private set; }

        public int ConsolidationId = 0;
        public int CountyId = 0;
        public string PrecinctIdsCsv { get; private set; }

        public RequestFindVoter()
        {
            SearchBy = SearchByEnum.Name;
            SearchArea = SearchAreaEnum.State;
            LastName = "";
            FirstName = "";
            MiddleInitial = "";
            Zipcode = "";
            BirthDate = "";

            VoterId = "";
            DriversLicense = "";
            AffidavitNumber = "";

            StreetName = "";
            HouseNumber = "";
            AptNumber = "";
            ConsolidationId = 0;
        }

        public RequestFindVoter ByName(string lastName, string firstName, string middleInitial, string zipCode, string birthDate)
        {
            SearchBy = SearchByEnum.Name;

            LastName = lastName;
            FirstName = firstName;
            MiddleInitial = middleInitial;
            Zipcode = zipCode;
            BirthDate = birthDate;
            return this;
        }

        public RequestFindVoter ByAddress(string streetName, string houseNumber, string aptNumber, string zipCode)
        {
            SearchBy = SearchByEnum.Address;

            StreetName = streetName;
            HouseNumber = houseNumber;
            AptNumber = aptNumber;
            Zipcode = zipCode;
            return this;
        }

        public RequestFindVoter ByIds(string voterId, string driversLicense, string affidavitNumber)
        {
            SearchBy = SearchByEnum.Ids;

            VoterId = voterId;
            DriversLicense = driversLicense;
            AffidavitNumber = affidavitNumber;
            return this;
        }

        public RequestFindVoter InPoll(int consolidationId)
        {
            SearchArea = SearchAreaEnum.Poll;
            ConsolidationId = consolidationId;
            return this;
        }

        public RequestFindVoter InPrecinct(List<PrecinctModel> precincts)
        {
            SearchArea = SearchAreaEnum.Precinct;
            string precinctIds = "";
            bool first = true;
            foreach (var precinct in precincts)
            {
                if (first)
                {
                    precinctIds = precinct.PrecinctId + "";
                }
                else
                {
                    precinctIds += ", " + precinct.PrecinctId;
                }

                first = false;
            }
            PrecinctIdsCsv = precinctIds;

            return this;
        }

        public RequestFindVoter InCounty(int countyId)
        {
            CountyId = countyId;
            SearchArea = SearchAreaEnum.County;
            return this;
        }

        public RequestFindVoter InState()
        {
            SearchArea = SearchAreaEnum.State;
            return this;
        }
    }

    
}

Voter.cs

using System;
using System.Collections.Generic;
using ExpressPoll.Core.CBE;
using ExpressPoll.Core.CBE.Pdm.Dao;
using ExpressPoll.Core.CBE.Pdm.Model;
using ExpressPoll.Core.CBE.Resources;
using ExpressPoll.Core.CBE.Services.Business.Options;
using ExpressPoll.Core.CBE.Services.Business.Voter;

namespace ExpressPoll.Droid.Services.Business.Voter
{
    public class Voter : BusinessServiceBase, IVoter
    {
        private readonly IVoterDao _voterDao;
        private readonly IPrecinctDao _precinctDao;
        private readonly IPartyDao _partyDao;
        private readonly ICountyDao _countyDao;
        private readonly IOptions _options;
        private readonly int _maxRows;

        public Voter(
            IVoterDao voterDao
            , IPrecinctDao precinctDao
            , IPartyDao partyDao
            , ICountyDao countyDao
            , IOptions options
            )
        {
            _voterDao = voterDao;
            _precinctDao = precinctDao;
            _partyDao = partyDao;
            _countyDao = countyDao;
            _options = options;

            string maxRowsOption = options.GetOption(OptionNames.MaxVoterSearchCount);
            if (!Int32.TryParse(maxRowsOption, out _maxRows))
            {
                _maxRows = 100;
            }
        }

        public ResponseEditVoter EditVoter(RequestEditVoter request)
        {
            throw new NotImplementedException();
        }

        public ResponseFindVoter FindVoter(RequestFindVoter request)
        {
            var response = new ResponseFindVoter();
            var voters = new List<VoterModel>();

            if (request.SearchBy == SearchByEnum.Ids)
            {
                if ((request.VoterId.Length + request.DriversLicense.Length + request.AffidavitNumber.Length)>0)
                {
                    if (SearchAreaEnum.Poll.Equals(request.SearchArea))
                    {
                       voters = ServiceContainer.Resolve<IVoterDao>().FindVotersInPollByIds(
                            request.VoterId
                            , request.DriversLicense
                            , request.AffidavitNumber
                            , _maxRows
                            , request.ConsolidationId
                        );
                    }
                    else if (SearchAreaEnum.Precinct.Equals(request.SearchArea))
                    {
                        voters = ServiceContainer.Resolve<IVoterDao>().FindVotersInPrecinctByIds(
                            request.VoterId
                            , request.DriversLicense
                            , request.AffidavitNumber
                            , _maxRows
                            , request.PrecinctIdsCsv
                        );
                    }
                    else if (SearchAreaEnum.County.Equals(request.SearchArea))
                    {
                        voters = ServiceContainer.Resolve<IVoterDao>().FindVotersInCountyByIds(
                            request.VoterId
                            , request.DriversLicense
                            , request.AffidavitNumber
                            , _maxRows
                            , request.CountyId
                        );
                    }
                    else if (SearchAreaEnum.State.Equals(request.SearchArea))
                    {
                        voters = ServiceContainer.Resolve<IVoterDao>().FindVotersInStateByIds(
                            request.VoterId
                            , request.DriversLicense
                            , request.AffidavitNumber
                            , _maxRows
                        );
                    }
                }
            }
            else if (request.SearchBy == SearchByEnum.Address)
            {
                if ((request.StreetName.Length + 
                     request.HouseNumber.Length + 
                     request.AptNumber.Length +
                     request.Zipcode.Length) > 0)
                {
                    if (SearchAreaEnum.Poll.Equals(request.SearchArea))
                    {
                        voters = ServiceContainer.Resolve<IVoterDao>().FindVotersInPollByAddress(
                            request.StreetName
                            , request.HouseNumber
                            , request.AptNumber
                            , request.Zipcode,
                            _maxRows
                            , request.ConsolidationId
                            );
                    }
                    else if (SearchAreaEnum.Precinct.Equals(request.SearchArea))
                    {
                        voters = ServiceContainer.Resolve<IVoterDao>().FindVotersInPrecinctByAddress(
                            request.StreetName
                            , request.HouseNumber
                            , request.AptNumber
                            , request.Zipcode
                            , _maxRows
                            , request.PrecinctIdsCsv
                        );
                    }
                    else if (SearchAreaEnum.County.Equals(request.SearchArea))
                    {
                        voters = ServiceContainer.Resolve<IVoterDao>().FindVotersInCountyByAddress(
                            request.StreetName
                            , request.HouseNumber
                            , request.AptNumber
                            , request.Zipcode
                            , _maxRows
                            , request.CountyId
                        );
                    }
                    else if (SearchAreaEnum.State.Equals(request.SearchArea))
                    {
                        voters = ServiceContainer.Resolve<IVoterDao>().FindVotersInStateByAddress(
                            request.StreetName
                            , request.HouseNumber
                            , request.AptNumber
                            , request.Zipcode,
                            _maxRows
                        );
                    }
                }
            }
            else
            {
                if ((request.LastName.Length + request.FirstName.Length + request.MiddleInitial.Length +
                     request.Zipcode.Length + request.BirthDate.Length) > 0)
                {
                    if (SearchAreaEnum.Poll.Equals(request.SearchArea))
                    {
                        voters = ServiceContainer.Resolve<IVoterDao>().FindVotersInPollByName(
                        request.LastName
                        , request.FirstName
                        , request.MiddleInitial
                        , request.Zipcode
                        , request.BirthDate
                        , _maxRows
                        , request.ConsolidationId);
                    }
                    else if (SearchAreaEnum.Precinct.Equals(request.SearchArea))
                    {
                        voters = ServiceContainer.Resolve<IVoterDao>().FindVotersInPrecinctByName(
                        request.LastName
                        , request.FirstName
                        , request.MiddleInitial
                        , request.Zipcode
                        , request.BirthDate
                        , _maxRows
                        , request.PrecinctIdsCsv);
                    }
                    else if (SearchAreaEnum.County.Equals(request.SearchArea))
                    {
                        voters = ServiceContainer.Resolve<IVoterDao>().FindVotersInCountyByName(
                        request.LastName
                        , request.FirstName
                        , request.MiddleInitial
                        , request.Zipcode
                        , request.BirthDate
                        , _maxRows
                        , request.CountyId);
                    }
                    else if (SearchAreaEnum.State.Equals(request.SearchArea))
                    {
                        voters = ServiceContainer.Resolve<IVoterDao>().FindVotersInStateByName(
                        request.LastName
                        , request.FirstName
                        , request.MiddleInitial
                        , request.Zipcode
                        , request.BirthDate
                        , _maxRows);
                    }
                }
            }

            response.Voters = voters;
            response.ServiceComplete();
            return response;
        }

        public ResponseGetVoterCounts GetVoterCounts(RequestGetVoterCounts request)
        {
            throw new NotImplementedException();
        }

        public ResponseGetPrecinctVoters GetPrecinctVoters(RequestGetPrecinctVoters request)
        {
            throw new NotImplementedException();
        }

        public ResponseGetVoter GetVoter(RequestGetVoter request)
        {
            var response = new ResponseGetVoter();

            response.Voter = ServiceContainer.Resolve<IVoterDao>().GetVoter(request.VoterId);
            response.Voter.SignatureReference = ServiceContainer.Resolve<IVoterDao>().GetVoterSignature(request.VoterId);
            response.Voter.Precinct = ServiceContainer.Resolve<IPrecinctDao>().GetPrecinct(response.Voter.PrecinctId);
            response.Voter.Party = ServiceContainer.Resolve<IPartyDao>().GetParty(response.Voter.PartyId);
            response.Voter.County = ServiceContainer.Resolve<ICountyDao>().GetCounty(response.Voter.CountyId);
            
            response.ServiceComplete();
            return response;
        }

        public ResponseGetVoterBallot GetVoterBallot(RequestGetVoterBallot request)
        {
            throw new NotImplementedException();
        }

        public ResponseGetVoterHistory GetVoterHistory(RequestGetVoterHistory request)
        {
            throw new NotImplementedException();
        }

        public ResponseGetVoterIdentification GetVoterIdentification(RequestGetVoterIdentification request)
        {
            throw new NotImplementedException();
        }

        public ResponseGetVoterPicture GetVoterPicture(RequestGetVoterPicture request)
        {
            throw new NotImplementedException();
        }

        public ResponseGetVoterSignatureCaptured GetVoterSignatureCaptured(RequestGetVoterSignatureCaptured request)
        {
            throw new NotImplementedException();
        }

        public ResponseGetVoterSignatureReference GetVoterSignatureReference(RequestGetVoterSignatureReference request)
        {
            throw new NotImplementedException(); 
        }

        public ResponseGetVotersListAvailableHours GetVotersListAvailableHours(RequestGetVotersListAvailableHours request)
        {
            throw new NotImplementedException();
        }

        public ResponseGetVoterStatusCounts GetVoterStatusCounts(RequestGetVoterStatusCounts request)
        {
            throw new NotImplementedException();
        }

        public ResponseUpdateMailingAddress UpdateMailingAddress(RequestUpdateMailingAddress request)
        {
            throw new NotImplementedException();
        }

        public ResponseUpdatePreviousAddress UpdatePreviousAddress(UpdatePreviousAddress request)
        {
            throw new NotImplementedException();
        }

        public ResponseUpdateVoter UpdateVoter(RequestUpdateVoter request)
        {
            throw new NotImplementedException();
        }

        public ResponseUpdateVoterAddress UpdateVoterAddress(RequestUpdateVoterAddress request)
        {
            throw new NotImplementedException();
        }

        public ResponseUpdateVoterMisc UpdateVoterMisc(RequestUpdateVoterMisc request)
        {
            throw new NotImplementedException();
        }

        public ResponseVerifyVoter VerifyVoter(RequestVerifyVoter request)
        {
            throw new NotImplementedException();
        }
    }
}

VoterDao.cs

using System;
using System.Collections.Generic;
using System.Data;
using ExpressPoll.Core.CBE;
using ExpressPoll.Core.CBE.Pdm;
using ExpressPoll.Core.CBE.Pdm.Dao;
using ExpressPoll.Core.CBE.Pdm.Model;
using ExpressPoll.Droid.ABE.Utility;
using ExpressPoll.Droid.Services.DataAccess;
using Mono.Data.Sqlite;

namespace ExpressPoll.Droid.ABE.Dao
{
    public class VoterDao : DaoBase, IVoterDao, IDaoBase
    {
        // select
        private const string VoterModelSelectClause =
                @"SELECT 
                   voterId 
                   , status 
                   , CAST( dateOfBirth AS VARCHAR(20)) AS  dateOfBirth 
                   , precinctId 
                   , consolidationId 
                   , countyId 
                   , partyId 
                   , language 
                   , idRequired 
                   , absentee 
                   , ssnLast4 
                   , driversLicense 
                   , affidavitNumber 
                   , houseNumber 
                   , houseFraction 
                   , streetName 
                   , apartmentNumber 
                   , city 
                   , state
                   , zip 
                   , nameFirst 
                   , nameMiddle 
                   , nameLast 
                   , middleInitial 
                   , namePrefix 
                   , nameSuffix 
                   , comments 
                   , userField1 
                   , userField2 
                FROM 
                   voters v 
                ";

        private const string GetVoterSql =
                VoterModelSelectClause +
                @" WHERE 
                   v.voterId = @VOTERID 
                ORDER BY 
                   nameLast 
                   , nameFirst 
                   , middleInitial 
                LIMIT 1";
        
        // where
        private const string FindVoterByNameWhereClause =
                @" WHERE 
                     v.nameLast LIKE @LASTNAME 
                     AND v.nameFirst LIKE @FIRSTNAME 
                     AND IFNULL(v.middleInitial, '') LIKE @MIDDLENAME 
                     AND IFNULL(v.zip, '') LIKE @ZIPCODE 
                     AND strftime('%m%d', IFNULL(dateOfBirth,0)/10000000 - 62135596800, 'unixepoch') LIKE @DOBMD
                ";

        private const string FindVoterByAddressWhereClause =
                @" WHERE 
                    v.streetName LIKE @STREETNAME 
                    AND IFNULL(v.houseNumber, '') LIKE @HOUSENUMBER
                    AND IFNULL(v.apartmentNumber, '') LIKE @APTNUMBER
                    AND IFNULL(v.zip, '') LIKE @ZIPCODE
                ";

        private const string FindVoterByIdsWhereClause =
                @" WHERE
                    v.voterId LIKE @VOTERID
                    AND IFNULL(v.driversLicense, '') LIKE @DLNUMBER
                    AND IFNULL(v.affidavitNumber, '') LIKE @AFFIDAVITNUMBER
                ";

        // order by
        private const string FindVoterOrderByClause = 
                @" ORDER BY 
                    nameLast 
                    , nameFirst 
                    , middleInitial 
                  LIMIT @ROWLIMIT
        ";

        // name
        private const string FindVoterInPollByName = 
                            VoterModelSelectClause +
                            FindVoterByNameWhereClause +
                            " AND v.consolidationId = @CONSOLIDATIONID " +
                            FindVoterOrderByClause;

        private string FindVoterInPrecinctByName(string precinctIdsCsv)
        {
            return 
                VoterModelSelectClause +
                FindVoterByNameWhereClause +
                " AND v.precinctId IN (" + precinctIdsCsv + ") " +
                FindVoterOrderByClause;
        }

        private string FindVoterInCountyByName = 
                VoterModelSelectClause +
                FindVoterByNameWhereClause +
                " AND v.countyId = @COUNTYID " +
                FindVoterOrderByClause;

        private const string FindVoterInStateByName =
                            VoterModelSelectClause +
                            FindVoterByNameWhereClause +
                            FindVoterOrderByClause;

        // address
        private const string FindVoterInPollByAddress =
                            VoterModelSelectClause +
                            FindVoterByAddressWhereClause +
                            " AND v.consolidationId = @CONSOLIDATIONID " +
                            FindVoterOrderByClause;

        private string FindVoterInPrecinctByAddress(string precinctIdsCsv)
        {
            return
                VoterModelSelectClause +
                FindVoterByAddressWhereClause +
                " AND v.precinctId IN (" + precinctIdsCsv + ") " +
                FindVoterOrderByClause;
        }

        private string FindVoterInCountyByAddress =
                VoterModelSelectClause +
                FindVoterByAddressWhereClause +
                " AND v.countyId = @COUNTYID " +
                FindVoterOrderByClause;

        private const string FindVoterInStateByAddress =
                            VoterModelSelectClause +
                            FindVoterByAddressWhereClause +
                            FindVoterOrderByClause;

        // ids
        private const string FindVoterInPollByIds =
                            VoterModelSelectClause +
                            FindVoterByIdsWhereClause +
                            " AND v.consolidationId = @CONSOLIDATIONID " +
                            FindVoterOrderByClause;

        private string FindVoterInPrecinctByIds(string precinctIdsCsv)
        {
            return 
                VoterModelSelectClause +
                FindVoterByIdsWhereClause +
                " AND v.precinctId IN (" + precinctIdsCsv + ") " +
                FindVoterOrderByClause;
        }

        private string FindVoterInCountyByIds =
                VoterModelSelectClause +
                FindVoterByIdsWhereClause +
                " AND v.countyId = @COUNTYID " +
                FindVoterOrderByClause;

        private const string FindVoterInStateByIds =
                            VoterModelSelectClause +
                            FindVoterByIdsWhereClause +
                            FindVoterOrderByClause;


        
        // name
        public List<VoterModel> FindVotersInPollByName(string lastName, string firstName, string middleName, string zipcode, string dateOfBirth, int rowLimit, int consolidationId)
        {
            var voters = new List<VoterModel>();
            using (var connection = new SqliteConnection(ServiceContainer.Resolve<IPolldata>().GetConnectionString()))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    Console.WriteLine(FindVoterInPollByName);
                    command.CommandText = FindVoterInPollByName;
                    command.Parameters.Add(new SqliteParameter("@LASTNAME", lastName.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@FIRSTNAME", firstName.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@MIDDLENAME", middleName.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@ZIPCODE", zipcode.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@DOBMD", dateOfBirth.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@CONSOLIDATIONID", consolidationId));
                    command.Parameters.Add(new SqliteParameter("@ROWLIMIT", rowLimit));

                    var results = command.ExecuteReader();
                    while (results.Read())
                    {
                        VoterModel voter = LoadVoter(results);
                        voters.Add(voter);
                    }
                    Close(results);
                }
                Close(connection);
            }

            return voters;
        }


        public List<VoterModel> FindVotersInPrecinctByName(string lastName, string firstName, string middleName, string zipcode, string dateOfBirth, int rowLimit, string precinctIdsCsv)
        {
            var voters = new List<VoterModel>();
            using (var connection = new SqliteConnection(ServiceContainer.Resolve<IPolldata>().GetConnectionString()))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    Console.WriteLine(FindVoterInPrecinctByName(precinctIdsCsv));
                    command.CommandText = FindVoterInPrecinctByName(precinctIdsCsv);
                    command.Parameters.Add(new SqliteParameter("@LASTNAME", lastName.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@FIRSTNAME", firstName.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@MIDDLENAME", middleName.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@ZIPCODE", zipcode.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@DOBMD", dateOfBirth.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@ROWLIMIT", rowLimit));

                    var results = command.ExecuteReader();
                    while (results.Read())
                    {
                        VoterModel voter = LoadVoter(results);
                        voters.Add(voter);
                    }
                    Close(results);
                }
                Close(connection);
            }

            return voters;
        }

        public List<VoterModel> FindVotersInStateByName(string lastName, string firstName, string middleName, string zipcode, string dateOfBirth, int rowLimit)
        {
            var voters = new List<VoterModel>();
            using (var connection = new SqliteConnection(ServiceContainer.Resolve<IPolldata>().GetConnectionString()))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    Console.WriteLine(FindVoterInStateByName);
                    command.CommandText = FindVoterInStateByName;
                    command.Parameters.Add(new SqliteParameter("@LASTNAME", lastName.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@FIRSTNAME", firstName.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@MIDDLENAME", middleName.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@ZIPCODE", zipcode.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@DOBMD", dateOfBirth.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@ROWLIMIT", rowLimit));

                    var results = command.ExecuteReader();
                    while (results.Read())
                    {
                        VoterModel voter = LoadVoter(results);
                        voters.Add(voter);
                    }
                    Close(results);
                }
                Close(connection);
            }

            return voters;
        }


        // address
        public List<VoterModel> FindVotersInPollByAddress(string streetName, string houseNumber, string aptNumber, string zipcode, int rowLimit, int consolidationId)
        {
            var voters = new List<VoterModel>();
            using (var connection = new SqliteConnection(ServiceContainer.Resolve<IPolldata>().GetConnectionString()))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    Console.WriteLine(FindVoterInPollByAddress);
                    command.CommandText = FindVoterInPollByAddress;
                    command.Parameters.Add(new SqliteParameter("@STREETNAME", streetName.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@HOUSENUMBER", houseNumber.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@APTNUMBER", "%" + aptNumber.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@ZIPCODE", zipcode.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@CONSOLIDATIONID", consolidationId));
                    command.Parameters.Add(new SqliteParameter("@ROWLIMIT", rowLimit));

                    var results = command.ExecuteReader();
                    while (results.Read())
                    {
                        VoterModel voter = LoadVoter(results);
                        voters.Add(voter);
                    }
                    Close(results);
                }
                Close(connection);
            }

            return voters;
        }


        public List<VoterModel> FindVotersInPrecinctByAddress(string streetName, string houseNumber, string aptNumber, string zipcode, int rowLimit, string precinctIdsCsv)
        {
            var voters = new List<VoterModel>();
            using (var connection = new SqliteConnection(ServiceContainer.Resolve<IPolldata>().GetConnectionString()))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    Console.WriteLine(FindVoterInPrecinctByAddress(precinctIdsCsv));
                    command.CommandText = FindVoterInPrecinctByAddress(precinctIdsCsv);
                    command.Parameters.Add(new SqliteParameter("@STREETNAME", streetName.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@HOUSENUMBER", houseNumber.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@APTNUMBER", "%" + aptNumber.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@ZIPCODE", zipcode.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@ROWLIMIT", rowLimit));

                    var results = command.ExecuteReader();
                    while (results.Read())
                    {
                        VoterModel voter = LoadVoter(results);
                        voters.Add(voter);
                    }
                    Close(results);
                }
                Close(connection);
            }

            return voters;
        }


        public List<VoterModel> FindVotersInStateByAddress(string streetName, string houseNumber, string aptNumber, string zipcode, int rowLimit)
        {
            var voters = new List<VoterModel>();
            using (var connection = new SqliteConnection(ServiceContainer.Resolve<IPolldata>().GetConnectionString()))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    Console.WriteLine(FindVoterInStateByAddress);
                    command.CommandText = FindVoterInStateByAddress;
                    command.Parameters.Add(new SqliteParameter("@STREETNAME", streetName.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@HOUSENUMBER", houseNumber.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@APTNUMBER", "%" + aptNumber.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@ZIPCODE", zipcode.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@ROWLIMIT", rowLimit));

                    var results = command.ExecuteReader();
                    while (results.Read())
                    {
                        VoterModel voter = LoadVoter(results);
                        voters.Add(voter);
                    }
                    Close(results);
                }
                Close(connection);
            }

            return voters;
        }

        // ids 

        public List<VoterModel> FindVotersInPollByIds(string voterId, string driversLicense, string affidavitNumber, int rowLimit, int consolidationId)
        {
            var voters = new List<VoterModel>();
            using (var connection = new SqliteConnection(ServiceContainer.Resolve<IPolldata>().GetConnectionString()))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    Console.WriteLine(FindVoterInPollByIds);
                    command.CommandText = FindVoterInPollByIds;
                    command.Parameters.Add(new SqliteParameter("@VOTERID", voterId.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@DLNUMBER", driversLicense.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@AFFIDAVITNUMBER", "%" + affidavitNumber.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@CONSOLIDATIONID", consolidationId));
                    command.Parameters.Add(new SqliteParameter("@ROWLIMIT", rowLimit));

                    var results = command.ExecuteReader();
                    while (results.Read())
                    {
                        VoterModel voter = LoadVoter(results);
                        voters.Add(voter);
                    }
                    Close(results);
                }
                Close(connection);
            }

            return voters;
        }

        public List<VoterModel> FindVotersInPrecinctByIds(string voterId, string driversLicense, string affidavitNumber, int rowLimit, string precinctIdsCsv)
        {
            var voters = new List<VoterModel>();
            using (var connection = new SqliteConnection(ServiceContainer.Resolve<IPolldata>().GetConnectionString()))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    Console.WriteLine(FindVoterInPrecinctByIds(precinctIdsCsv));
                    command.CommandText = FindVoterInPrecinctByIds(precinctIdsCsv);
                    command.Parameters.Add(new SqliteParameter("@VOTERID", voterId.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@DLNUMBER", driversLicense.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@AFFIDAVITNUMBER", "%" + affidavitNumber.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@ROWLIMIT", rowLimit));

                    var results = command.ExecuteReader();
                    while (results.Read())
                    {
                        VoterModel voter = LoadVoter(results);
                        voters.Add(voter);
                    }
                    Close(results);
                }
                Close(connection);
            }

            return voters;
        }

        public List<VoterModel> FindVotersInStateByIds(string voterId, string driversLicense, string affidavitNumber, int rowLimit)
        {
            var voters = new List<VoterModel>();
            using (var connection = new SqliteConnection(ServiceContainer.Resolve<IPolldata>().GetConnectionString()))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    Console.WriteLine(FindVoterInStateByIds);
                    command.CommandText = FindVoterInStateByIds;
                    command.Parameters.Add(new SqliteParameter("@VOTERID", voterId.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@DLNUMBER", driversLicense.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@AFFIDAVITNUMBER", "%" + affidavitNumber.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@ROWLIMIT", rowLimit));

                    var results = command.ExecuteReader();
                    while (results.Read())
                    {
                        VoterModel voter = LoadVoter(results);
                        voters.Add(voter);
                    }
                    Close(results);
                }
                Close(connection);
            }

            return voters;
        }


        public byte[] GetVoterSignature(int voterId)
        {
            byte[] signature = null;

            string sql =
                "SELECT " +
                "   signature " +
                "FROM " +
                "   votersignatures s " +
                "WHERE " +
                "   s.voterId = @VOTERID " +
                "LIMIT 1";

            using (var connection = new SqliteConnection(ServiceContainer.Resolve<IPolldata>().GetConnectionString()))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    Console.WriteLine(sql);
                    command.CommandText = sql;
                    command.Parameters.Add(new SqliteParameter("@VOTERID", voterId));

                    var results = command.ExecuteReader();
                    if (results.Read())
                    {
                        signature = (byte[])results["signature"];
                    }
                }
            }

            return signature;
        }


        public VoterModel GetVoter(int voterId)
        {
            var voter = new VoterModel();
            using (var connection = new SqliteConnection(ServiceContainer.Resolve<IPolldata>().GetConnectionString()))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    Console.WriteLine(GetVoterSql);
                    command.CommandText = GetVoterSql;
                    command.Parameters.Add(new SqliteParameter("@VOTERID", voterId));
                    
                    var results = command.ExecuteReader();
                    if (results.Read())
                    {
                        voter.VoterId = DbUtil.ConvertInt(results["voterId"]);
                        voter.Status = DbUtil.ConvertString(results["status"]);
                        voter.DateOfBirth = DbUtil.ConvertDateTime(results["dateOfBirth"]);
                        voter.PrecinctId = DbUtil.ConvertInt(results["precinctId"]);
                        voter.ConsolidationId = DbUtil.ConvertInt(results["consolidationId"]);
                        voter.CountyId = DbUtil.ConvertInt(results["countyId"]);
                        voter.PartyId = DbUtil.ConvertInt(results["partyId"]);
                        voter.Language = DbUtil.ConvertString(results["language"]);
                        voter.IdRequired = DbUtil.ConvertString(results["idRequired"]);
                        voter.Absentee = DbUtil.ConvertString(results["absentee"]);
                        voter.SsnLast4 = DbUtil.ConvertString(results["ssnLast4"]);
                        voter.DriversLicense = DbUtil.ConvertString(results["driversLicense"]);
                        voter.AffidavitNumber = DbUtil.ConvertString(results["affidavitNumber"]);
                        voter.HouseNumber = DbUtil.ConvertString(results["houseNumber"]);
                        voter.HouseFraction = DbUtil.ConvertString(results["houseFraction"]);
                        voter.StreetName = DbUtil.ConvertString(results["streetName"]);
                        voter.ApartmentNumber = DbUtil.ConvertString(results["apartmentNumber"]);
                        voter.City = DbUtil.ConvertString(results["city"]);
                        voter.State = DbUtil.ConvertString(results["state"]);
                        voter.Zip = DbUtil.ConvertString(results["zip"]);
                        voter.NameFirst = DbUtil.ConvertString(results["nameFirst"]);
                        voter.NameMiddle = DbUtil.ConvertString(results["nameMiddle"]);
                        voter.NameLast = DbUtil.ConvertString(results["nameLast"]);
                        voter.MiddleInitial = DbUtil.ConvertString(results["middleInitial"]);
                        voter.NamePrefix = DbUtil.ConvertString(results["namePrefix"]);
                        voter.NameSuffix = DbUtil.ConvertString(results["nameSuffix"]);
                        voter.Comments = DbUtil.ConvertString(results["comments"]);
                        voter.UserField1 = DbUtil.ConvertString(results["userField1"]);
                        voter.UserField2 = DbUtil.ConvertString(results["userField2"]);
                    }
                    Close(results);
                }
                Close(connection);
            }

            return voter;
        }

     
        private VoterModel LoadVoter(SqliteDataReader results)
        {
            var voter = new VoterModel();
            voter.VoterId = DbUtil.ConvertInt(results["voterId"]);
            voter.Status = DbUtil.ConvertString(results["status"]);
            voter.DateOfBirth = DbUtil.ConvertDateTime(results["dateOfBirth"]);
            voter.PrecinctId = DbUtil.ConvertInt(results["precinctId"]);
            voter.ConsolidationId = DbUtil.ConvertInt(results["consolidationId"]);
            voter.CountyId = DbUtil.ConvertInt(results["countyId"]);
            voter.PartyId = DbUtil.ConvertInt(results["partyId"]);
            voter.Language = DbUtil.ConvertString(results["language"]);
            voter.IdRequired = DbUtil.ConvertString(results["idRequired"]);
            voter.Absentee = DbUtil.ConvertString(results["absentee"]);
            voter.SsnLast4 = DbUtil.ConvertString(results["ssnLast4"]);
            voter.DriversLicense = DbUtil.ConvertString(results["driversLicense"]);
            voter.AffidavitNumber = DbUtil.ConvertString(results["affidavitNumber"]);
            voter.HouseNumber = DbUtil.ConvertString(results["houseNumber"]);
            voter.HouseFraction = DbUtil.ConvertString(results["houseFraction"]);
            voter.StreetName = DbUtil.ConvertString(results["streetName"]);
            voter.ApartmentNumber = DbUtil.ConvertString(results["apartmentNumber"]);
            voter.City = DbUtil.ConvertString(results["city"]);
            voter.State = DbUtil.ConvertString(results["state"]);
            voter.Zip = DbUtil.ConvertString(results["zip"]);
            voter.NameFirst = DbUtil.ConvertString(results["nameFirst"]);
            voter.NameMiddle = DbUtil.ConvertString(results["nameMiddle"]);
            voter.NameLast = DbUtil.ConvertString(results["nameLast"]);
            voter.MiddleInitial = DbUtil.ConvertString(results["middleInitial"]);
            voter.NamePrefix = DbUtil.ConvertString(results["namePrefix"]);
            voter.NameSuffix = DbUtil.ConvertString(results["nameSuffix"]);
            voter.Comments = DbUtil.ConvertString(results["comments"]);
            voter.UserField1 = DbUtil.ConvertString(results["userField1"]);
            voter.UserField2 = DbUtil.ConvertString(results["userField2"]);

            return voter;
        }
        
        public List<VoterModel> FindVotersInCountyByName(string lastName, string firstName, string middleName, string zipcode, string dateOfBirth, int rowLimit, int countyId)
        {
            var voters = new List<VoterModel>();
            using (var connection = new SqliteConnection(ServiceContainer.Resolve<IPolldata>().GetConnectionString()))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    Console.WriteLine(FindVoterInCountyByName);
                    command.CommandText = FindVoterInCountyByName;
                    command.Parameters.Add(new SqliteParameter("@LASTNAME", lastName.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@FIRSTNAME", firstName.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@MIDDLENAME", middleName.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@ZIPCODE", zipcode.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@DOBMD", dateOfBirth.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@COUNTYID", countyId));
                    command.Parameters.Add(new SqliteParameter("@ROWLIMIT", rowLimit));

                    var results = command.ExecuteReader();
                    while (results.Read())
                    {
                        VoterModel voter = LoadVoter(results);
                        voters.Add(voter);
                    }
                    Close(results);
                }
                Close(connection);
            }

            return voters;
        }

        public List<VoterModel> FindVotersInCountyByAddress(string streetName, string houseNumber, string aptNumber, string zipcode, int rowLimit, int countyId)
        {
            var voters = new List<VoterModel>();
            using (var connection = new SqliteConnection(ServiceContainer.Resolve<IPolldata>().GetConnectionString()))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    Console.WriteLine(FindVoterInCountyByAddress);
                    command.CommandText = FindVoterInCountyByAddress;
                    command.Parameters.Add(new SqliteParameter("@STREETNAME", streetName.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@HOUSENUMBER", houseNumber.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@APTNUMBER", "%" + aptNumber.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@ZIPCODE", zipcode.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@COUNTYID", countyId));
                    command.Parameters.Add(new SqliteParameter("@ROWLIMIT", rowLimit));

                    var results = command.ExecuteReader();
                    while (results.Read())
                    {
                        VoterModel voter = LoadVoter(results);
                        voters.Add(voter);
                    }
                    Close(results);
                }
                Close(connection);
            }

            return voters;
        }

        public List<VoterModel> FindVotersInCountyByIds(string voterId, string driversLicense, string affidavitNumber, int rowLimit, int countyId)
        {
            var voters = new List<VoterModel>();
            using (var connection = new SqliteConnection(ServiceContainer.Resolve<IPolldata>().GetConnectionString()))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    Console.WriteLine(FindVoterInCountyByIds);
                    command.CommandText = FindVoterInCountyByIds;
                    command.Parameters.Add(new SqliteParameter("@VOTERID", voterId.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@DLNUMBER", driversLicense.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@AFFIDAVITNUMBER", "%" + affidavitNumber.ToUpper() + "%"));
                    command.Parameters.Add(new SqliteParameter("@COUNTYID", countyId));
                    command.Parameters.Add(new SqliteParameter("@ROWLIMIT", rowLimit));

                    var results = command.ExecuteReader();
                    while (results.Read())
                    {
                        VoterModel voter = LoadVoter(results);
                        voters.Add(voter);
                    }
                    Close(results);
                }
                Close(connection);
            }

            return voters;
        }

     
    }
}

VoterRepository.cs

using System.Collections.Generic;
using System.Threading.Tasks;
using ExpressPoll.Core.Common.QueryBuilder;
using ExpressPoll.Core.Common.QueryBuilder.Enums;
using ExpressPoll.Core.Common.SQLite;
using ExpressPoll.Core.Domain.Models;
using SQLite.Net;
using SQLite.Net.Async;

namespace ExpressPoll.Core.Domain
{
    public class VoterRepository : IVoterRepository
    {
        private readonly SQLiteConnection _db;
        private readonly SQLiteAsyncConnection _dbAsync;

        public VoterRepository(ISQLiteConnectionFactory sqLiteConnectionFactory)
        {
            _db = sqLiteConnectionFactory.Create();
            _dbAsync = sqLiteConnectionFactory.CreateAsync();
        }

        public void Add(Voter voter)
        {
            _db.Insert(voter);
        }

        public IEnumerable<Voter> FindVoters(string lastName = null,
                                             string firstName = null,
                                             string middleName = null,
                                             string streetName = null,
                                             string houseNumber = null,
                                             string aptNumber = null,
                                             string zipcode = null,
                                             string voterId = null,
                                             string driversLicense = null,
                                             string affidavitNumber = null,
                                             int consolidationId = -1,
                                             PrecinctIdList precinctIdList = null,
                                             int skip = -1,
                                             int take = -1)
        {
            var query = BuildFindVotersQuery(lastName,
                                             firstName,
                                             middleName,
                                             streetName,
                                             houseNumber,
                                             aptNumber,
                                             zipcode,
                                             voterId,
                                             driversLicense,
                                             affidavitNumber,
                                             consolidationId,
                                             precinctIdList,
                                             skip,
                                             take);

            return _db.Query<Voter>(query);
            
        }

        public async Task<IEnumerable<Voter>> FindVotersAsync(string lastName = null,
                                                              string firstName = null,
                                                              string middleName = null,
                                                              string streetName = null,
                                                              string houseNumber = null,
                                                              string aptNumber = null,
                                                              string zipcode = null,
                                                              string voterId = null,
                                                              string driversLicense = null,
                                                              string affidavitNumber = null,
                                                              int consolidationId = -1,
                                                              PrecinctIdList precinctIdList = null,
                                                              int skip = -1,
                                                              int take = -1)
        {
            var query = BuildFindVotersQuery(lastName,
                                             firstName,
                                             middleName,
                                             streetName,
                                             houseNumber,
                                             aptNumber,
                                             zipcode,
                                             voterId,
                                             driversLicense,
                                             affidavitNumber,
                                             consolidationId,
                                             precinctIdList,
                                             skip,
                                             take);

            return await _dbAsync.QueryAsync<Voter>(query);
        }

        private static string BuildFindVotersQuery(string lastName,
                                                   string firstName,
                                                   string middleName,
                                                   string streetName,
                                                   string houseNumber,
                                                   string aptNumber,
                                                   string zipcode,
                                                   string voterId,
                                                   string driversLicense,
                                                   string affidavitNumber,
                                                   int consolidationId,
                                                   PrecinctIdList precinctIdList,
                                                   int skip = -1,
                                                   int take = -1)
        {
            var selectQueryBuilder = new SelectQueryBuilder();

            selectQueryBuilder.SelectFromTable("Voters");
            selectQueryBuilder.SelectAllColumns();

            if (lastName != null)
                selectQueryBuilder.AddWhere("nameLast", Comparison.Like, lastName.ToUpper() + "%");

            if (firstName != null)
                selectQueryBuilder.AddWhere("nameFirst", Comparison.Like, firstName.ToUpper() + "%");

            if (middleName != null)
                selectQueryBuilder.AddWhere("middleInitial", Comparison.Like, middleName.ToUpper() + "%");

            if (streetName != null)
                selectQueryBuilder.AddWhere("streetName", Comparison.Like, streetName.ToUpper() + "%");

            if (houseNumber != null)
                selectQueryBuilder.AddWhere("strehouseNumberetName", Comparison.Like, houseNumber.ToUpper() + "%");

            if (aptNumber != null)
                selectQueryBuilder.AddWhere("aptNumber", Comparison.Like, aptNumber.ToUpper() + "%");

            if (zipcode != null)
                selectQueryBuilder.AddWhere("zip", Comparison.Like, zipcode.ToUpper() + "%");

            if (voterId != null)
                selectQueryBuilder.AddWhere("voterId", Comparison.Like, voterId.ToUpper() + "%");

            if (driversLicense != null)
                selectQueryBuilder.AddWhere("driversLicense", Comparison.Like, driversLicense.ToUpper() + "%");

            if (affidavitNumber != null)
                selectQueryBuilder.AddWhere("affidavitNumber", Comparison.Like, affidavitNumber.ToUpper() + "%");

            if (consolidationId > 0)
                selectQueryBuilder.AddWhere("consolidationId", Comparison.Equals, consolidationId);

            if (precinctIdList != null && precinctIdList.HasPrecincts)
                selectQueryBuilder.AddWhere("precinctId", Comparison.In, precinctIdList);

            if (skip > 0)
                selectQueryBuilder.Skip(skip);

            if (take > 0)
                selectQueryBuilder.Take(take);

            return selectQueryBuilder.BuildQuery();
        }
    }
}