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