public static TableRecord LoadTableRecords(ApplicationDbContext context, string entityName, string primaryKeyName, string colums, string search = "", string recordId = "") { DataTable dtRecords = new DataTable(); TableRecord lookupRecord = new TableRecord(); List <RecordRow> recordRows = new List <RecordRow>(); List <string> columnHeaders = new List <string>(); bool isSearch = !string.IsNullOrWhiteSpace(search); using (var command = context.Database.GetDbConnection().CreateCommand()) { string query = $"SELECT TOP 1000"; query += $" Id, {primaryKeyName}, {colums} FROM [{entityName}]"; if (isSearch) { query += $" WHERE {primaryKeyName} LIKE '%{search}%' AND Status = 0"; } else if (!string.IsNullOrWhiteSpace(recordId)) { query += $" WHERE Id = '{recordId}'"; } else { query += $" WHERE Status = 0 "; } query += $" ORDER BY {primaryKeyName} ASC"; command.CommandText = query; context.Database.OpenConnection(); using (var result = command.ExecuteReader(CommandBehavior.CloseConnection)) { dtRecords.Load(result); } for (int i = 2; i < dtRecords.Columns.Count; i++) { columnHeaders.Add(dtRecords.Columns[i].ColumnName); } foreach (DataRow row in dtRecords.Rows) { var lookupItem = new RecordRow { RecordId = row["Id"].ToString() }; lookupItem.ColumnValues = new List <string>(); for (int i = 2; i < dtRecords.Columns.Count; i++) { lookupItem.ColumnValues.Add(row[dtRecords.Columns[i].ColumnName].ToString()); } recordRows.Add(lookupItem); } lookupRecord.ColumnHeaders = columnHeaders; lookupRecord.Rows = recordRows; } return(lookupRecord); }
public static TableRecord LoadTableRecords(ApplicationDbContext context, string primaryRecordId, TableDef tableDef, bool isNewAssociation = false, string search = "") { DataTable dtRecords = new DataTable(); TableRecord lookupRecord = new TableRecord(); List <RecordRow> recordRows = new List <RecordRow>(); List <string> columnHeaders = new List <string>(); List <string> ids = GetSelectedRecords(context, primaryRecordId, tableDef); bool isSearch = !string.IsNullOrWhiteSpace(search); using (var command = context.Database.GetDbConnection().CreateCommand()) { string[] columnSet = tableDef.ColumnSet.Select(c => $"{c.Key} [{c.Value}]").ToArray(); string query = $"SELECT "; if (ids.Count <= 0 && !isNewAssociation) { query += "TOP 0 "; } if (isNewAssociation) { query += "TOP 1000"; } query += $" Id, {string.Join(",", columnSet)} FROM [{tableDef.SecondaryEntity}]"; if (ids.Count > 0) { query += !query.Contains("WHERE") ? " WHERE " : " AND "; query += "Id " + (isNewAssociation ? "NOT " : "") + "IN ({0})"; string[] paramArray = ids.Select((x, i) => "@id" + i).ToArray(); query = string.Format(query, string.Join(",", paramArray)); if (!string.IsNullOrWhiteSpace(search)) { query += $" AND {tableDef.ColumnSet[0].Key} LIKE '%{search}%'"; } if (!string.IsNullOrWhiteSpace(search) || isNewAssociation) { query += " AND Status = 0"; } query += $" ORDER BY {tableDef.ColumnSet[0].Key} ASC"; command.CommandText = query; for (int i = 0; i < ids.Count; ++i) { command.Parameters.Add(new SqlParameter("@id" + i, ids[i])); } } else { if (isSearch) { query += $" WHERE {tableDef.ColumnSet[0].Key} LIKE '%{search}%'"; } query += query.Contains("WHERE") ? " AND" : " WHERE"; query += " Status = 0"; query += $" ORDER BY {tableDef.ColumnSet[0].Key} ASC"; command.CommandText = query; } context.Database.OpenConnection(); using (var result = command.ExecuteReader(CommandBehavior.CloseConnection)) { dtRecords.Load(result); } for (int i = 1; i < dtRecords.Columns.Count; i++) { columnHeaders.Add(dtRecords.Columns[i].ColumnName); } foreach (DataRow row in dtRecords.Rows) { var lookupItem = new RecordRow { RecordId = row["Id"].ToString() }; lookupItem.ColumnValues = new List <string>(); for (int i = 1; i < dtRecords.Columns.Count; i++) { lookupItem.ColumnValues.Add(row[dtRecords.Columns[i].ColumnName].ToString()); } recordRows.Add(lookupItem); } lookupRecord.ColumnHeaders = columnHeaders; lookupRecord.Rows = recordRows; } return(lookupRecord); }