/// <summary>
/// A controller method to redirect to a page showing the details relating to
/// a staging dataset. This takes the dataset database ID as a parameter and
/// utilises the data access classes to get the data back
///
/// This is accessed via /Staging/DatasetDetail
/// </summary>
/// <param name="datasetID"></param>
/// <returns></returns>
public ActionResult DatasetDetails(int datasetID)
{
StagingDataAccess dataAccess = new StagingDataAccess();
StagingDetailModel model = dataAccess.getStagingDetails(datasetID);
List <Breadcrumb> trail = new List <Breadcrumb>();
trail.Add(new Breadcrumb()
{
LinkText = "Home", Action = "Index", Controller = "Home", isCurrent = false
});
trail.Add(new Breadcrumb()
{
LinkText = "Staging Index", Action = "Index", Controller = "Staging", isCurrent = false
});
trail.Add(new Breadcrumb()
{
LinkText = "Staging Dataset Details", isCurrent = true
});
model.Breadcrumbs = trail;
return(View(model));
}
/// <summary>
/// Method to return the details of a Staging Table from the
/// database. This includes the name of the Staging Table and all
/// the columns associated with it.
/// </summary>
/// <param name="datasetID">The ID of the Staging Table to be returned</param>
/// <returns></returns>
public StagingDetailModel getStagingDetails(int datasetID)
{
DataTable dt;
SqlDataAdapter sda;
StagingDetailModel model = new StagingDetailModel();
Geographical_NeedsEntities context = new Geographical_NeedsEntities();
model.StagingDatasetName = context.StagingDatasets.Single(x => x.StagingDatasetID.Equals(datasetID)).DatasetName;
List <String> columns = context.StagingColumns.Where(x => x.StagingDatasetID.Equals(datasetID)).Select(x => x.ColumnName).ToList();
String sql = String.Format("Select {1} from [{0}]", model.StagingDatasetName, String.Join(",", columns.Select(x => String.Format("[{0}]", x))));
using (SqlConnection connection = new SqlConnection(
context.Database.Connection.ConnectionString))
{
SqlCommand command = new SqlCommand(sql, connection);
command.Connection.Open();
sda = new SqlDataAdapter(command);
dt = new DataTable("Results");
sda.Fill(dt);
}
context.Dispose();
model.Data = dt;
return(model);
}
