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(),
});
}
