public static List <string> GetSelectedRecords(ApplicationDbContext context, string primaryRecordId, TableDef tableDef)
{
List <string> ids = new List <string>();
using (var command = context.Database.GetDbConnection().CreateCommand())
{
var pid = tableDef.SecondaryEntity.Substring(tableDef.SecondaryEntity.Length - 4) == "View" ? tableDef.SecondaryEntity.Substring(0, tableDef.SecondaryEntity.Length - 4) : tableDef.SecondaryEntity;
string query = $"SELECT {pid}Id FROM [{tableDef.RelationShipName}] WHERE {tableDef.PrimaryEntity}Id = '{primaryRecordId}'";
command.CommandText = query;
context.Database.OpenConnection();
using (var result = command.ExecuteReader(CommandBehavior.CloseConnection))
{
if (result.HasRows)
{
while (result.Read())
{
ids.Add(result.GetValue(0).ToString());
}
}
}
}
return(ids);
}
public static bool AssociateRecord(ApplicationDbContext context, string primaryRecordId, string recordId, TableDef tableDef)
{
int result = 0;
using (var command = context.Database.GetDbConnection().CreateCommand())
{
var pid = tableDef.SecondaryEntity.Substring(tableDef.SecondaryEntity.Length - 4) == "View" ? tableDef.SecondaryEntity.Substring(0, tableDef.SecondaryEntity.Length - 4) : tableDef.SecondaryEntity;
string query = $"INSERT INTO [{tableDef.RelationShipName}] ({tableDef.PrimaryEntity}Id, {pid}Id) VALUES ('{primaryRecordId}', '{recordId}')";
command.CommandText = query;
context.Database.OpenConnection();
result = command.ExecuteNonQuery();
context.Database.CloseConnection();
}
return(result > 0);
}
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);
}