public AdoQueryFieldsModel ExecuteQueryCommand(string sql)
{
var queryCollection = new AdoQueryFieldsModel {
TotalRowsCount = GetCountOfAllRows(sql)
};
var sqlToexecute = ReplaceSelectWith("select top 10 * ", sql.ToLowerInvariant());
_sqlConnection = new SqlConnection(_dbConnectionString);
_sqlConnection.Open();
var metaDataList = new List <AdoQueryFieldsModel>();
var cmd = new SqlCommand(sqlToexecute, _sqlConnection);
var firstRow = false;
using (var reader = cmd.ExecuteReader())
{
var hasRows = reader.HasRows;
while (reader.Read())
{
var queryRow = new AdoQueryRow();
for (var i = 0; i < reader.FieldCount; i++)
{
var queryField = new AdoQueryField();
var columnName = reader.GetName(i);
var value = reader[i];
var dotNetType = reader.GetFieldType(i);
//var sqlType = reader.GetDataTypeName(i);
//var specificType = reader.GetProviderSpecificFieldType(i);
queryField.Name = columnName;
queryField.Value = value.ToString();
if (dotNetType != null)
{
queryField.Type = dotNetType.ToString();
}
queryRow.QueryRowFields.Add(queryField);
}
queryCollection.QueryRows.Add(queryRow);
if (firstRow == false)
{
queryCollection.ColumnHeaders =
queryRow.QueryRowFields.Select(x => x.Name).Distinct().ToList();
firstRow = true;
}
}
}
_sqlConnection.Close();
return(queryCollection);
}
public static AdoQueryFieldsViewModel MapCriteriaDtoToCriteriaGridViewModel(
AdoQueryFieldsModel queryFieldsViewModel)
{
var returnModel = new AdoQueryFieldsViewModel
{
ColumnHeaders = queryFieldsViewModel.ColumnHeaders,
QueryRows = queryFieldsViewModel.QueryRows,
TotalRowsCount = queryFieldsViewModel.TotalRowsCount.ToString("#,##0")
};
if (queryFieldsViewModel.ColumnHeaders.Count > 10)
{
returnModel.ColumnHeaders.RemoveRange(10, queryFieldsViewModel.ColumnHeaders.Count - 10);
returnModel.FieldsToShow = 10;
}
else
{
returnModel.FieldsToShow = queryFieldsViewModel.ColumnHeaders.Count;
}
return(returnModel);
}
