public ActionResult Index(TheTableInformation tableData, string command) { if (command.StartsWith("Show")) { ModelState.Clear(); var model = TheTableInformation.FetchTableAndHistory(tableName: tableData.TheName); return View("Index", model); } if (command.StartsWith("Save")) { var errorMsg = FileNameValidationAttribute.GetErrorMessage(tableData.TheName); // validate the name if (!String.IsNullOrWhiteSpace(errorMsg)) { throw new HttpException(500, errorMsg); } var columnNames = new List<string> { "TheString", "TheDate", "TheInt", "TheBool" }; var tableDataAsParam = tableData.TheData.AsTableValuedParameter(typeName: "dbo.TableDataType", orderedColumnNames: columnNames); DapperWrapper.StoredProcedure("InsertATable", new { JobName = tableData.TheName, UserId = User.Identity.GetUserId(), tableData = tableDataAsParam }); return View("Index", tableData); } if (command.StartsWith("Download")) { return GenerateExcelFile(tableData); } throw new ArgumentException("Action " + command + " is invalid." ); }
// GET: Excel public ActionResult Index() { // generate dummy data var now = DateTime.Now; var modelData = Enumerable.Range(0, 10).Select(i => new TheTableEntry { TheString = "TheString", TheDate = now, TheInt = i, TheBool = (i % 2) == 0 }).ToList(); var model = new TheTableInformation { TheName = "some name", TheData = modelData, TheHistory = null }; return View("Index", model); }
private ActionResult GenerateExcelFile(TheTableInformation tableData) { ModelState.Clear(); var tableInfoForExcel = new TableInformationToExcel(tableData, author: User.Identity.GetUserName(), title: "table Data " + tableData.TheName); return tableInfoForExcel.ReturnSpreadsheet("table Data " + tableData.TheName); }
public TableInformationToExcel(TheTableInformation tableInformationInstance, string author, string title) : base(author: author, title: title) { TableInformationInstance = tableInformationInstance; }
public static TheTableInformation FetchTableAndHistory(Guid? jobID = null, string tableName = null) { if ((jobID == null) == (string.IsNullOrWhiteSpace(tableName))) { throw new ArgumentException("Either job ID or job Name should be supplied, not both."); } var dbArgs = new Dapper.DynamicParameters(); if (jobID != null) { dbArgs.Add("@jobID", jobID); tableName = (string)DapperWrapper.ExecuteScalar("SELECT [dbo].[FetchJobName](@jobID)", dbArgs); dbArgs.Add("@JobNamePattern", tableName); } else { dbArgs.Add("@JobNamePattern", tableName); jobID = (Guid?)DapperWrapper.ExecuteScalar("SELECT [dbo].[FetchJobID](@JobNamePattern)", dbArgs); if (jobID != null) { dbArgs.Add("@jobID", jobID); } } var model = new TheTableInformation { TheName = tableName, TheData = null, TheHistory = null }; if (jobID != null) { model.TheData = DapperWrapper.Query<TheTableEntry>("SELECT * FROM [dbo].[FetchATable](@jobID)", dbArgs).ToList(); model.TheHistory = DapperWrapper.Query<TheTableHistoryEntry>( "SELECT * FROM [dbo].[FetchJobHistory](@jobID, @JobNamePattern) ORDER BY [Completed] DESC", dbArgs).ToList(); } return model; }