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;
}
