Esempio n. 1
0
        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);
        }
Esempio n. 2
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);
        }