public ActionResult DisplayData() { using (ExcelSourceEntities db = new ExcelSourceEntities()) { var userdata = db.ExcelSources.ToList(); var dataVms = new List <DataVm>(); foreach (var user in userdata) { var dataVm = new DataVm(); dataVm.FirstName = user.FirstName; dataVm.LastName = user.LastName; dataVm.EmailAddress = user.EmailAddress; dataVms.Add(dataVm); } return(View(dataVms)); } }
public DataVm GetColumn(int sqlConfigId, string sqlContent) { try { //tạo parameter ảo, tạm thời chưa dùng var storeParam = _sqlStoreParamRepository.GetAll().Where(x => x.SqlConfigId == sqlConfigId).ToList(); var storeParamMap = ObjectMapper.Map <List <SqlStoreParamDto> >(storeParam); //(from c in _context.ConfigReportFilter // where c.ReportId == reportid && c.IsDelete == false // select c).ToList(); List <Parameter> param = new List <Parameter>(); var config = _sqlConfigRepository.FirstOrDefault(sqlConfigId); if (sqlContent.IsNullOrEmpty()) { sqlContent = config.SqlContent; } ////var config = (from c in _context.ConfigReport //// where c.Id == reportid //// select c).SingleOrDefault(); //long datasourceId = config.DataSourceId; // dataSource = _context.ConfigReportDataSource.SingleOrDefault(m => m.Id == datasourceId); //string sqlType = dataSource.SqlType; //Bảo cmt, param của store. tạm thời chưa dùng if (storeParamMap.Count > 0) { foreach (SqlStoreParamDto element in storeParamMap) { Parameter a = new Parameter("", null); //tạo danh sách param test if (element.ValueString != null) { a.Varible = "@" + element.Code; a.Value = element.ValueString; param.Add(a); } else { a.Varible = "@" + element.Code; a.Value = DBNull.Value; param.Add(a); } } } //Get danh sách report display hiện có var reportDisplayOld = _sqlConfigDetailRepository.GetAll().Where(x => x.SqlConfigId == sqlConfigId).ToList(); var reportDisplayOldMap = ObjectMapper.Map <List <SqlConfigDetailDto> >(reportDisplayOld); //(from c in _context.ConfigReportDisplay // where c.ReportId == reportid && c.IsDelete == false // select c).ToList(); //get danh sách cột mới theo câu sql var items = new List <Dictionary <string, object> >(); using (var conn = new SqlConnection(connectionString)) if (config.IsRawQuery == true) { using (var command = new SqlCommand(sqlContent, conn) { CommandType = CommandType.Text }) { conn.Open(); if (param != null) { foreach (Parameter _param in param) { SqlParameter iparam = new SqlParameter(); iparam.ParameterName = _param.Varible; iparam.Value = _param.Value; command.Parameters.Add(iparam); } } //command.Parameters.Add(new SqlParameter("@"+param.Varible.ToString(), param.Value)); IDataReader reader = command.ExecuteReader(); var table = reader.GetSchemaTable(); foreach (DataRow column in table.Rows) { var item = new Dictionary <string, object>(reader.FieldCount); item["col"] = column.ItemArray[0]; items.Add(item); } reader.Close(); conn.Close(); } } else { using (var command = new SqlCommand(sqlContent, conn) { CommandType = CommandType.StoredProcedure }) { conn.Open(); if (param != null) { foreach (Parameter _param in param) { SqlParameter iparam = new SqlParameter(); iparam.ParameterName = _param.Varible; iparam.Value = _param.Value; command.Parameters.Add(iparam); } } //command.Parameters.Add(new SqlParameter("@"+param.Varible.ToString(), param.Value)); IDataReader reader = command.ExecuteReader(); var table = reader.GetSchemaTable(); foreach (DataRow column in table.Rows) { var item = new Dictionary <string, object>(reader.FieldCount); item["col"] = column.ItemArray[0]; items.Add(item); } reader.Close(); conn.Close(); } } //so sánh danh sách col cũ và mới giữ lại những col trùng và xóa đi những col không trùng List <SqlConfigDetailDto> reportDisplayNew = new List <SqlConfigDetailDto>(); foreach (SqlConfigDetailDto temp in reportDisplayOldMap) { bool key = false; foreach (var a in items) { if (temp.Code == a["col"].ToString()) { key = true; } } if (key == true) { reportDisplayNew.Add(temp); } } foreach (var a in items) { bool key = false; foreach (SqlConfigDetailDto temp in reportDisplayOldMap) { if (temp.Code == a["col"].ToString()) { key = true; } } if (key == false) { SqlConfigDetailDto reportDisplay = new SqlConfigDetailDto(); reportDisplay.Code = a["col"].ToString(); reportDisplay.GroupLevel = -1; reportDisplay.Name = a["col"].ToString(); reportDisplay.IsDisplay = true; reportDisplay.GroupSort = ""; reportDisplay.SqlConfigId = sqlConfigId; reportDisplayNew.Add(reportDisplay); } } return(DataVm.Success("Success", "Thành công", reportDisplayNew)); } catch (Exception e) { return(DataVm.Fail("Error", "Thất bại")); } }