public static int GetRecordCount(string connectionString, string tableName, IEnumerable <FieldFilter> filters) { var command = $"SELECT COUNT(*) FROM {tableName}"; if (filters != null && filters.Any()) { filters = SpecflowDbValidatorHelper.AddQuotationMarks(filters); var filtering = new List <string>(); foreach (var filter in filters) { filtering.Add($"{filter.FieldName} IN ({filter.FieldValues})"); } command = $"{command}{Environment.NewLine}{"WHERE"} {string.Join(" AND ", filtering)}"; } using (var conn = CreateDbConnection(connectionString)) { using (var comm = CreateDbCommand(command, conn)) { conn.Open(); var reader = comm.ExecuteReader(CommandBehavior.KeyInfo); reader.Read(); return(int.Parse(reader[0].ToString())); } } }
public static Object.Collections.DataCollection BuildDataCollection(string connectionString, string tableName, IEnumerable <string> fields, IEnumerable <FieldFilter> filters, FormatterManager formatter) { var rows = new List <Object.Collections.DataRow>(); var command = $"SELECT {string.Join(",", fields)} FROM {tableName}"; if (filters != null && filters.Any()) { filters = SpecflowDbValidatorHelper.AddQuotationMarks(filters); var filtering = new List <string>(); foreach (var filter in filters) { filtering.Add($"{filter.FieldName} IN ({filter.FieldValues})"); } command = $"{command}{Environment.NewLine}{"WHERE"} {string.Join(" AND ", filtering)}"; } using (var conn = CreateDbConnection(connectionString)) { using (var comm = CreateDbCommand(command, conn)) { conn.Open(); var reader = comm.ExecuteReader(CommandBehavior.KeyInfo); while (reader.Read()) { var dataRow = new Object.Collections.DataRow(); rows.Add(dataRow); var dataCells = new List <Object.Collections.DataCell>(); for (int i = 0; i < reader.FieldCount; i++) { var columnName = reader.GetName(i); var dbValue = reader[i]; var value = dbValue.GetType() == typeof(DBNull) ? null : formatter.Format(tableName, columnName, dbValue); var cell = new KeyValuePair <string, string>(columnName, value); var dataCell = Object.Collections.DataCell.Load(cell); dataCells.Add(dataCell); } dataRow.Values = dataCells.ToArray(); } } } return(new Object.Collections.DataCollection { Rows = rows.ToArray(), }); }