public WebDataTable(string name, string sortIndex, string sortOrder, long pageIndex, long pageSize, FilterDataObject filterData, WebISGDatabaseType databaseType)
        {
            Name = name;
            Rows = new List<WebDataTableRow>();

            // The "Actions" column will be the same across every table
            Columns =
                new List<WebDataTableColumn>
                    {
                        new WebDataTableColumn("act")
                            {
                                ParentTable = name,
                                DatabaseType = databaseType
                            }
                    };

            using (var serviceClient = new NeonISGDataServiceClient(Configuration.ActiveNeonDataServiceEndpoint))
            {
                var database = Mapper.Map<WebISGDatabaseType, NeonISGDatabaseType>(databaseType);
                var rtrn = serviceClient.GetData(Name, sortIndex, sortOrder, pageIndex, pageSize, filterData.FilterQuery, database);

                foreach (var column in rtrn.Columns)
                {
                    Columns.Add(
                        new WebDataTableColumn(column.Name)
                            {
                                IsNullable = column.IsNullable,
                                Length = column.Length,
                                Precision = column.Precision,
                                Scale = column.Scale,
                                TypeName = column.TypeName,
                                ParentTable = name,
                                IsPrimaryKey = column.IsPrimaryKey,
                                IsReadonlyIdentity = column.IsReadonlyIdentity,
                                DatabaseType = databaseType
                            });
                }
                Columns = Columns.OrderBy(x => x.Order).ThenBy(x => x.FriendlyName).ToList();

                foreach (var row in rtrn.Rows)
                {
                    var dataRow = new WebDataTableRow(row.RowId);
                    foreach (var column in Columns.Where(c => !c.Name.Equals("act")))
                    {
                        var cell = row.Cells.FirstOrDefault(x => string.Equals(x.ColumnName, column.Name, StringComparison.CurrentCultureIgnoreCase));
                        var value = (cell != null) ? cell.Value : string.Empty;

                        dataRow.Cells.Add(
                            new WebDataTableCell(column)
                            {
                                Value = value
                            });
                    }
                    Rows.Add(dataRow);
                }
            }
        }
 public static long GetRowCount(string tableName, FilterDataObject filterData, WebISGDatabaseType databaseType)
 {
     long rtrn;
     using (var serviceClient = new NeonISGDataServiceClient(Configuration.ActiveNeonDataServiceEndpoint))
     {
         var database = Mapper.Map<WebISGDatabaseType, NeonISGDatabaseType>(databaseType);
         rtrn = serviceClient.GetRowCount(tableName, filterData.FilterQuery, database);
     }
     return rtrn;
 }
 public static bool SaveData(string tableName, NeonDataTableRowMetaData row, WebISGDatabaseType databaseType)
 {
     try
     {
         using (var serviceClient = new NeonISGDataServiceClient(Configuration.ActiveNeonDataServiceEndpoint))
         {
             var neonDatabase = Mapper.Map<WebISGDatabaseType, NeonISGDatabaseType>(databaseType);
             // ReSharper disable once UnusedVariable
             var rtrn = serviceClient.SaveRow(tableName, row, neonDatabase);
         }
         return true;
     }
     catch (Exception ex)
     {
         throw new Exception(string.Format("Unable to save information to database for table {0}", tableName), ex);
     }
 }
        public WebDataTableMetaData(NeonDataTableMetaData serviceMetadata)
        {
            Name = serviceMetadata.Name;
            DatabaseType = Mapper.Map<NeonISGDatabaseType, WebISGDatabaseType>(serviceMetadata.DatabaseType);

            // The "Actions" column will be the same across every table
            Columns =
                new List<WebDataTableColumn>
                    {
                        new WebDataTableColumn("act")
                            {
                                ParentTable = serviceMetadata.Name,
                                IsNullable = true,
                                DatabaseType = DatabaseType
                            }
                    };

            foreach (var metaData in serviceMetadata.Columns)
            {
                Columns.Add(
                    new WebDataTableColumn(metaData.Name)
                        {
                            IsNullable = metaData.IsNullable,
                            Length = metaData.Length,
                            Precision = metaData.Precision,
                            Scale = metaData.Scale,
                            TypeName = metaData.TypeName,
                            ParentTable = serviceMetadata.Name,
                            ParentSourceTable = metaData.ParentSourceTable,
                            IsPrimaryKey = metaData.IsPrimaryKey,
                            IsReadonlyIdentity = metaData.IsReadonlyIdentity,
                            DatabaseType = DatabaseType
                        });
            }
            Columns = Columns.OrderBy(x => x.Name).ToList();

            var firstColumn = Columns.FirstOrDefault(c => !string.Equals(c.FriendlyName, "Actions", StringComparison.CurrentCultureIgnoreCase));
            if (firstColumn != null)
            {
                SourceName = firstColumn.ParentSourceTable;
            }
        }
        public ActionResult GetGridData(string sidx, string sord, string filters, int page, int rows, string tableName, WebISGDatabaseType databaseType)
        {
            var jsonData = new JsonResult();
            if (string.IsNullOrEmpty(tableName))
            {
                return jsonData;
            }
            var filterData = filters != null ? JsonConvert.DeserializeObject<FilterDataObject>(filters) : new FilterDataObject();

            var rowCount = WebDataTable.GetRowCount(tableName, filterData, databaseType);

            var currentPage = page;
            var pageIndex = currentPage - 1;
            var pageSize = rows != -1 ? rows : rowCount;
            var totalRecords = rowCount;
            var totalPages = (int)Math.Ceiling(totalRecords / (float)pageSize);

            var dataTable = new WebDataTable(tableName, sidx, sord, pageIndex, pageSize, filterData, databaseType);

            var rowData = new
            {
                total = totalPages,
                page = currentPage,
                records = totalRecords,
                rows =
                    (
                        from row in dataTable.Rows
                        select new
                        {
                            id = row.RowId,
                            cell = row.CellValueArray
                        }
                        ).ToArray()
            };

            jsonData = Json(rowData, JsonRequestBehavior.AllowGet);
            return jsonData;
        }
 public ActionResult GetDropdownOptions(string table, string column, WebISGDatabaseType databaseType)
 {
     List<string> distinctValues;
     using (var serviceClient = new NeonISGDataServiceClient(Configuration.ActiveNeonDataServiceEndpoint))
     {
         var database = Mapper.Map<WebISGDatabaseType, NeonISGDatabaseType>(databaseType);
         distinctValues = serviceClient.GetDistinctValues(table, column, database);
     }
     var jsonData = Json(distinctValues, JsonRequestBehavior.AllowGet);
     return jsonData;
 }
        /// <summary>
        ///  This method is called to allow the user to download the entire table as a CSV file.
        /// </summary>
        /// <returns></returns>
        public FileStreamResult DownloadFullExcel(string id, WebISGDatabaseType databaseType, bool showHiddenColumns = false)
        {
            //Create a workbook & worksheet
            var wb = new XLWorkbook();
            var ws = wb.Worksheets.Add(id);

            if (!string.IsNullOrEmpty(id))
            {
                var filterData = new FilterDataObject();
                var rowCount = WebDataTable.GetRowCount(id, filterData, databaseType);
                var dataTable = new WebDataTable(id, "", "asc", 0, rowCount, filterData, databaseType);

                // get column headers
                var colHeaders = dataTable.Columns
                    .Where(c => !c.Name.Equals("act") &&
                        (showHiddenColumns || c.Hidden.ToLower() != "true"))
                    .Select(cd => cd.FriendlyName ?? string.Empty)
                    .ToArray();

                // get row data
                var rowsToInsert = new List<Array>();
                // ReSharper disable once LoopCanBeConvertedToQuery
                foreach (var row in dataTable.Rows)
                {
                    rowsToInsert.Add(
                        row.Cells
                        .Where(cd => showHiddenColumns || cd.Column.Hidden.ToLower() != "true")
                        .Select(cd => cd.Value ?? string.Empty)
                        .ToArray()
                    );
                }

                // add data to worksheet
                for (var i = 0; i < colHeaders.Length; i++)
                {
                    ws.Cell(1, i + 1).Value = colHeaders[i];
                }
                // ReSharper disable once UnusedVariable
                var table = ws.Cell(1, 1).InsertTable(rowsToInsert);
                ws.Columns().AdjustToContents(1,99);
            }

            var memoryStream = new MemoryStream();
            wb.SaveAs(memoryStream);
            memoryStream.Flush();
            memoryStream.Position = 0;

            return File(memoryStream, "application/vnd.ms-excel", string.Format("{0}.xlsx", id));
        }