public Code GetCode(string stringValue, CodeConverter codeConverter) { var seedValue = codeConverter.ConvertFromCode(stringValue); var code = new Code(); Connection.Open(); using (var command = Connection.CreateCommand()) { command.CommandText = @"SELECT * FROM Codes WHERE SeedValue = @seedValue"; command.Parameters.AddWithValue("@seedValue", seedValue); using (var reader = command.ExecuteReader()) { while (reader.Read()) { var seed = (int)reader["SeedValue"]; code.State = State.ConvertToString((byte)reader["State"]); code.StringValue = codeConverter.ConvertToCode(seed); } } } Connection.Close(); return(code); }
public List <Code> GetCodes(int promotionID, int pageNumber, int pageSize, string alphabet) { var convertCode = new CodeConverter(alphabet); var codes = new List <Code>(); var p = Pagination.PaginationPageNumber(pageSize, pageNumber); var pages = PageCount(promotionID); Connection.Open(); using (var command = Connection.CreateCommand()) { command.CommandText = @"Declare @codeIDStart int Declare @codeIDEnd int SET @codeIDStart = (SELECT CodeIDStart FROM Promotion WHERE ID = @promotionID) SET @codeIDEnd = (SELECT CodeIDEnd FROM Promotion WHERE ID = @promotionID) SELECT * FROM Code WHERE ID BETWEEN @codeIDStart AND @codeIDEnd ORDER BY ID OFFSET @page ROWS FETCH NEXT @pageSize ROWS ONLY"; command.Parameters.AddWithValue("@page", p); command.Parameters.AddWithValue("@pageSize", pageSize); command.Parameters.AddWithValue(@"promotionID", promotionID); using (var reader = command.ExecuteReader()) { while (reader.Read()) { var code = new Code(); var seed = (int)reader["SeedValue"]; code.State = States.ConvertToString((byte)reader["State"]); code.StringValue = convertCode.ConvertToCode(seed); codes.Add(code); } } } Connection.Close(); return(codes); }