/// <summary> /// Internal helper method to return the data in a View as a Data Table that can be added to /// a View Model in the public method. This dynamically generates SQL to query the table. /// </summary> /// <param name="viewID">The ID of the Staging Table to be queried</param> /// <returns></returns> private DataTable getViewDataDetail(int viewID) { DataTable dt; SqlDataAdapter sda; Geographical_NeedsEntities context = new Geographical_NeedsEntities(); String columns = String.Join(",", context.DataViews.Single(x => x.DataViewID.Equals(viewID)).DataViewColumns.Select(x => String.Format("[{0}]", x.ColumnName))); String viewName = context.DataViews.Single(x => x.DataViewID.Equals(viewID)).ViewName; String sql = String.Format("Select {1} from [{0}]", viewName, columns); 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(); return(dt); }
/// <summary> /// Method to create the measure in the database. This just creates the basic Measure information and /// does not add values under the Measure. This process is based off creating SQL dynamically based on /// a template file. /// </summary> /// <param name="model">The populated model containing the data needed to create the Measure</param> public void createMeasure(CreateMeasureModel model) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); Measure newMeasure = context.Measures.Create(); newMeasure.MeasureName = model.MeasureName; createDimSets(model.selectedDimensions); IEnumerable <DimensionSet> dimSets = context.Dimensions.Where(x => model.selectedDimensions.Contains(x.DimensionID)).SelectMany(x => x.DimensionSetMembers).Select(x => x.DimensionSet).ToList(); IEnumerable <DimensionSet> dimSetsForExclusion = context.Dimensions.Where(x => !model.selectedDimensions.Contains(x.DimensionID)).SelectMany(x => x.DimensionSetMembers).Select(x => x.DimensionSet).ToList(); IEnumerable <DimensionSet> dimSetsFinal = dimSets.Where(x => !dimSetsForExclusion.Select(y => y.DimensionSetID).Contains(x.DimensionSetID)).Distinct(); foreach (DimensionSet set in dimSetsFinal) { MeasureBreakdown newBreakdown = context.MeasureBreakdowns.Create(); newBreakdown.MeasureBreakdownName = String.Format("{0} {1}", model.MeasureName, set.DimensionSetName); newBreakdown.DimensionSetID = set.DimensionSetID; newMeasure.MeasureBreakdowns.Add(newBreakdown); } context.Measures.Add(newMeasure); context.SaveChanges(); context.Dispose(); }
/// <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); }
/// <summary> /// Method to add the data from a DataTable to a Staging Table. This /// uses a SQLBulkCopy to update the database /// </summary> /// <param name="dt">The Table containing the data</param> /// <param name="tableID">The ID of the table to be updated.</param> private void copyDataToTable(DataTable dt, int tableID) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); string datasetName = context.StagingDatasets.Single(x => x.StagingDatasetID.Equals(tableID)).DatasetName; using (SqlConnection conn = new SqlConnection(context.Database.Connection.ConnectionString)) { //make our command and dispose at the end using (var copy = new SqlBulkCopy(conn)) { //Open our connection conn.Open(); ///Set target table and tell the number of rows copy.DestinationTableName = datasetName; copy.BatchSize = dt.Rows.Count; try { //Send it to the server copy.WriteToServer(dt); } catch (Exception ex) { } } } context.Dispose(); }
/// <summary> /// A method to add Columns to a Staging Table. This adds them to both the real table /// and the meta data about the table. The columns to add are based from the Data /// Table provided. /// </summary> /// <param name="dt">The Data Table to use to get the Columns to be added</param> /// <param name="tableID">The ID of the Staging Table to be added to.</param> private void addColumnsToTables(DataTable dt, int tableID) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); string datasetName = context.StagingDatasets.Single(x => x.StagingDatasetID.Equals(tableID)).DatasetName; foreach (DataColumn column in dt.Columns) { if (column.ColumnName != "UploadRef") { SqlParameter param1 = new SqlParameter("@tableName", datasetName); SqlParameter param2 = new SqlParameter("@columnName", column.ColumnName); context.Database.ExecuteSqlCommand("AddColumnToTable @tableName, @columnName", param1, param2); } StagingColumn newColumn = new StagingColumn(); newColumn.ColumnName = column.ColumnName; newColumn.StagingDatasetID = tableID; context.StagingColumns.Add(newColumn); } context.SaveChanges(); context.Dispose(); }
/// <summary> /// Method to create a Staging Table initially. This intial creation creates /// a record of the table in the meta data tables and then a trigger creates the /// actual table. Initially there are no columns associated with the table /// as these are added later when the data is uploaded. /// </summary> /// <param name="model"> /// The populated model containing the name of the table to be created. /// </param> public void createStagingTable(GeneralStagingModel model) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); StagingDataset staging = context.StagingDatasets.Create(); staging.DatasetName = model.TableName; context.StagingDatasets.Add(staging); context.SaveChanges(); context.Dispose(); }
public void createMeasureValues(MeasureValueModel model) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); var breakdowns = context.Measures.Single(x => x.MeasureName.Equals(model.MeasureName)).MeasureBreakdowns; foreach (var breakdown in breakdowns) { var combinations = breakdown.DimensionSet.DimensionSetCombinations; foreach (var combination in combinations) { var values = combination.DimensionSetCombinationMembers.Select(x => x.DimensionValue); List <String> whereClauses = new List <string>(); foreach (var value in values) { var detail = model.MeasureValueDetails.Single(x => x.DimValueID.Equals(value.DimensionValueID)); whereClauses.Add(String.Format("[{0}] = '{1}'", detail.DimColumnInStaging, detail.DimValueInStaging.Replace("'", "''"))); } String whereClause = String.Format("Where {0}", String.Join(" AND ", whereClauses)); InsertMeasureValuesTemplate template = new InsertMeasureValuesTemplate(); template.DimensionSetCombinationID = combination.DimensionSetCombinationID; template.GeographyTypeID = model.GeographyTypeID; template.MeasureBreakdownID = breakdown.MeasureBreakdownID; template.MeasureColumnName = model.MeasureStagingColumnName; template.StagingGeographyColumn = model.StagingGeographyColumn; template.StagingTableName = model.StagingTableName; template.UseMeasureColumn = model.UseMeasureColumn; template.WhereClause = whereClause; String output = template.TransformText(); context.Database.ExecuteSqlCommand(output); } } context.Dispose(); }
/// <summary> /// Method to create a Dimension based off the model returned from a View. This creates a Dimension /// and then populates values with those found in the specified Staging Table column /// </summary> /// <param name="model">The populated model passed back from the View</param> public void CreateDimension(CreateDimensionModel model) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); Dimension newDim = context.Dimensions.Create(); newDim.DimensionName = model.DimensionName; //DimensionSet newSet = context.DimensionSets.Create(); //newSet.DimensionSetName = String.Format("By {0}", model.DimensionName); //DimensionSetMember newMember = context.DimensionSetMembers.Create(); //newDim.DimensionSetMembers.Add(newMember); //newSet.DimensionSetMembers.Add(newMember); String stagingColumnName = context.StagingColumns.Single(x => x.StagingColumnID.Equals(model.DimColumnInStaging)).ColumnName; String stagingTableName = context.StagingColumns.Single(x => x.StagingColumnID.Equals(model.DimColumnInStaging)).StagingDataset.DatasetName; String sqlQuery = String.Format("select distinct [{0}] from [{1}]", stagingColumnName, stagingTableName); IEnumerable <String> stagingDimensionValues = context.Database.SqlQuery <String>(sqlQuery); foreach (String dimValue in stagingDimensionValues) { DimensionValue value = context.DimensionValues.Create(); value.DimensionValue1 = dimValue; newDim.DimensionValues.Add(value); } context.Dimensions.Add(newDim); //context.DimensionSets.Add(newSet); //context.DimensionSetMembers.Add(newMember); context.SaveChanges(); context.Dispose(); }
/// <summary> /// A method to create the View from the user entered data that /// has been specified through the web interface. It uses the template /// for a new View to generate the necessary SQL. /// </summary> /// <param name="model">The fully created model</param> public void CreateView(CreateViewModel model) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); DataView newView = context.DataViews.Create(); newView.ViewName = model.ViewName; DataViewColumn geogColumn = context.DataViewColumns.Create(); geogColumn.ColumnName = context.GeographyTypes.Single(x => x.GeographyTypeID.Equals(model.SelectedGeographyType)).GeographyType1; geogColumn.DataView = newView; context.DataViewColumns.Add(geogColumn); foreach (var column in model.Columns) { DataViewColumn newColumn = context.DataViewColumns.Create(); newColumn.ColumnName = column.ColumnName; newColumn.DataView = newView; context.DataViewColumns.Add(newColumn); } context.DataViews.Add(newView); CreateViewTemplate template = new CreateViewTemplate(); template.Model = model; String output = template.TransformText(); context.Database.ExecuteSqlCommand(output); context.SaveChanges(); context.Dispose(); }
/// <summary> /// Method to create a Dimension based off the model returned from a View. This creates a Dimension /// and then populates values with those found in the specified Staging Table column /// </summary> /// <param name="model">The populated model passed back from the View</param> public void CreateDimension(CreateDimensionModel model) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); Dimension newDim = context.Dimensions.Create(); newDim.DimensionName = model.DimensionName; //DimensionSet newSet = context.DimensionSets.Create(); //newSet.DimensionSetName = String.Format("By {0}", model.DimensionName); //DimensionSetMember newMember = context.DimensionSetMembers.Create(); //newDim.DimensionSetMembers.Add(newMember); //newSet.DimensionSetMembers.Add(newMember); String stagingColumnName = context.StagingColumns.Single(x => x.StagingColumnID.Equals(model.DimColumnInStaging)).ColumnName; String stagingTableName = context.StagingColumns.Single(x => x.StagingColumnID.Equals(model.DimColumnInStaging)).StagingDataset.DatasetName; String sqlQuery = String.Format("select distinct [{0}] from [{1}]", stagingColumnName, stagingTableName); IEnumerable<String> stagingDimensionValues = context.Database.SqlQuery<String>(sqlQuery); foreach (String dimValue in stagingDimensionValues) { DimensionValue value = context.DimensionValues.Create(); value.DimensionValue1 = dimValue; newDim.DimensionValues.Add(value); } context.Dimensions.Add(newDim); //context.DimensionSets.Add(newSet); //context.DimensionSetMembers.Add(newMember); context.SaveChanges(); context.Dispose(); }
/// <summary> /// Internal helper method to return the data in a View as a Data Table that can be added to /// a View Model in the public method. This dynamically generates SQL to query the table. /// </summary> /// <param name="viewID">The ID of the Staging Table to be queried</param> /// <returns></returns> private DataTable getViewDataDetail(int viewID) { DataTable dt; SqlDataAdapter sda; Geographical_NeedsEntities context = new Geographical_NeedsEntities(); String columns = String.Join(",", context.DataViews.Single(x => x.DataViewID.Equals(viewID)).DataViewColumns.Select(x => String.Format("[{0}]", x.ColumnName))); String viewName = context.DataViews.Single(x => x.DataViewID.Equals(viewID)).ViewName; String sql = String.Format("Select {1} from [{0}]", viewName, columns); 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(); return dt; }
public void createMeasureValues(MeasureValueModel model) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); var breakdowns = context.Measures.Single(x => x.MeasureName.Equals(model.MeasureName)).MeasureBreakdowns; foreach (var breakdown in breakdowns) { var combinations = breakdown.DimensionSet.DimensionSetCombinations; foreach (var combination in combinations) { var values = combination.DimensionSetCombinationMembers.Select(x => x.DimensionValue); List<String> whereClauses = new List<string>(); foreach (var value in values) { var detail = model.MeasureValueDetails.Single(x => x.DimValueID.Equals(value.DimensionValueID)); whereClauses.Add(String.Format("[{0}] = '{1}'", detail.DimColumnInStaging, detail.DimValueInStaging.Replace("'", "''"))); } String whereClause = String.Format("Where {0}", String.Join(" AND ", whereClauses)); InsertMeasureValuesTemplate template = new InsertMeasureValuesTemplate(); template.DimensionSetCombinationID = combination.DimensionSetCombinationID; template.GeographyTypeID = model.GeographyTypeID; template.MeasureBreakdownID = breakdown.MeasureBreakdownID; template.MeasureColumnName = model.MeasureStagingColumnName; template.StagingGeographyColumn = model.StagingGeographyColumn; template.StagingTableName = model.StagingTableName; template.UseMeasureColumn = model.UseMeasureColumn; template.WhereClause = whereClause; String output = template.TransformText(); context.Database.ExecuteSqlCommand(output); } } context.Dispose(); }
/// <summary> /// Method to create the measure in the database. This just creates the basic Measure information and /// does not add values under the Measure. This process is based off creating SQL dynamically based on /// a template file. /// </summary> /// <param name="model">The populated model containing the data needed to create the Measure</param> public void createMeasure(CreateMeasureModel model) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); Measure newMeasure = context.Measures.Create(); newMeasure.MeasureName = model.MeasureName; createDimSets(model.selectedDimensions); IEnumerable<DimensionSet> dimSets = context.Dimensions.Where(x => model.selectedDimensions.Contains(x.DimensionID)).SelectMany(x => x.DimensionSetMembers).Select(x => x.DimensionSet).ToList(); IEnumerable<DimensionSet> dimSetsForExclusion = context.Dimensions.Where(x => !model.selectedDimensions.Contains(x.DimensionID)).SelectMany(x => x.DimensionSetMembers).Select(x => x.DimensionSet).ToList(); IEnumerable<DimensionSet> dimSetsFinal = dimSets.Where(x => !dimSetsForExclusion.Select(y => y.DimensionSetID).Contains(x.DimensionSetID)).Distinct(); foreach (DimensionSet set in dimSetsFinal) { MeasureBreakdown newBreakdown = context.MeasureBreakdowns.Create(); newBreakdown.MeasureBreakdownName = String.Format("{0} {1}", model.MeasureName, set.DimensionSetName); newBreakdown.DimensionSetID = set.DimensionSetID; newMeasure.MeasureBreakdowns.Add(newBreakdown); } context.Measures.Add(newMeasure); context.SaveChanges(); context.Dispose(); }
private void createDimSets(IEnumerable<int> dimIDs) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); List<DimensionSet> allNewSets = new List<DimensionSet>(); foreach (int dimID in dimIDs) { Dimension dim = context.Dimensions.Single(x => x.DimensionID.Equals(dimID)); foreach (DimensionSet set in allNewSets) { DimensionSet derivedSet = context.DimensionSets.Create(); derivedSet.DimensionSetName = String.Format("{0} and {1}", set.DimensionSetName, dim.DimensionName); context.DimensionSets.Add(derivedSet); DimensionSetMember newDimMember = context.DimensionSetMembers.Create(); newDimMember.DimensionSet = derivedSet; newDimMember.Dimension = dim; context.DimensionSetMembers.Add(newDimMember); foreach (DimensionSetMember dimSetMember in set.DimensionSetMembers) { DimensionSetMember derivedMember = context.DimensionSetMembers.Create(); derivedMember.Dimension = dimSetMember.Dimension; derivedSet.DimensionSetMembers.Add(derivedMember); context.DimensionSetMembers.Add(derivedMember); } foreach (DimensionSetCombination combination in set.DimensionSetCombinations) { foreach (DimensionValue dimValue in dim.DimensionValues) { DimensionSetCombination derivedCombination = context.DimensionSetCombinations.Create(); derivedCombination.DimensionSetCombinationName = String.Format("{0} | {1}: {2}", combination.DimensionSetCombinationName, dim.DimensionName, dimValue.DimensionValue1); derivedCombination.DimensionSet = derivedSet; context.DimensionSetCombinations.Add(derivedCombination); DimensionSetCombinationMember newCombinationMember = context.DimensionSetCombinationMembers.Create(); newCombinationMember.DimensionSetCombination = derivedCombination; newCombinationMember.DimensionValue = dimValue; context.DimensionSetCombinationMembers.Add(newCombinationMember); foreach (DimensionSetCombinationMember dimSetCombinationMember in combination.DimensionSetCombinationMembers) { DimensionSetCombinationMember derivedCombinationMember = context.DimensionSetCombinationMembers.Create(); derivedCombinationMember.DimensionSetCombination = derivedCombination; derivedCombinationMember.DimensionValue = dimSetCombinationMember.DimensionValue; context.DimensionSetCombinationMembers.Add(derivedCombinationMember); } } } } String newDimSetName = String.Format("By {0}", dim.DimensionName); if (context.DimensionSets.Count(x => x.DimensionSetName.Equals(newDimSetName)) == 0) { DimensionSet newSet = context.DimensionSets.Create(); newSet.DimensionSetName = String.Format("By {0}", dim.DimensionName); DimensionSetMember newMember = context.DimensionSetMembers.Create(); dim.DimensionSetMembers.Add(newMember); newSet.DimensionSetMembers.Add(newMember); foreach (DimensionValue dimValue in dim.DimensionValues) { DimensionSetCombination newCombination = context.DimensionSetCombinations.Create(); newCombination.DimensionSet = newSet; newCombination.DimensionSetCombinationName = String.Format("{0}: {1}", dim.DimensionName, dimValue.DimensionValue1); DimensionSetCombinationMember newCombinationMember = context.DimensionSetCombinationMembers.Create(); newCombinationMember.DimensionValue = dimValue; newCombinationMember.DimensionSetCombination = newCombination; context.DimensionSetCombinations.Add(newCombination); context.DimensionSetCombinationMembers.Add(newCombinationMember); } allNewSets.Add(newSet); } } context.SaveChanges(); context.Dispose(); }
private void createDimSets(IEnumerable <int> dimIDs) { Geographical_NeedsEntities context = new Geographical_NeedsEntities(); List <DimensionSet> allNewSets = new List <DimensionSet>(); foreach (int dimID in dimIDs) { Dimension dim = context.Dimensions.Single(x => x.DimensionID.Equals(dimID)); foreach (DimensionSet set in allNewSets) { DimensionSet derivedSet = context.DimensionSets.Create(); derivedSet.DimensionSetName = String.Format("{0} and {1}", set.DimensionSetName, dim.DimensionName); context.DimensionSets.Add(derivedSet); DimensionSetMember newDimMember = context.DimensionSetMembers.Create(); newDimMember.DimensionSet = derivedSet; newDimMember.Dimension = dim; context.DimensionSetMembers.Add(newDimMember); foreach (DimensionSetMember dimSetMember in set.DimensionSetMembers) { DimensionSetMember derivedMember = context.DimensionSetMembers.Create(); derivedMember.Dimension = dimSetMember.Dimension; derivedSet.DimensionSetMembers.Add(derivedMember); context.DimensionSetMembers.Add(derivedMember); } foreach (DimensionSetCombination combination in set.DimensionSetCombinations) { foreach (DimensionValue dimValue in dim.DimensionValues) { DimensionSetCombination derivedCombination = context.DimensionSetCombinations.Create(); derivedCombination.DimensionSetCombinationName = String.Format("{0} | {1}: {2}", combination.DimensionSetCombinationName, dim.DimensionName, dimValue.DimensionValue1); derivedCombination.DimensionSet = derivedSet; context.DimensionSetCombinations.Add(derivedCombination); DimensionSetCombinationMember newCombinationMember = context.DimensionSetCombinationMembers.Create(); newCombinationMember.DimensionSetCombination = derivedCombination; newCombinationMember.DimensionValue = dimValue; context.DimensionSetCombinationMembers.Add(newCombinationMember); foreach (DimensionSetCombinationMember dimSetCombinationMember in combination.DimensionSetCombinationMembers) { DimensionSetCombinationMember derivedCombinationMember = context.DimensionSetCombinationMembers.Create(); derivedCombinationMember.DimensionSetCombination = derivedCombination; derivedCombinationMember.DimensionValue = dimSetCombinationMember.DimensionValue; context.DimensionSetCombinationMembers.Add(derivedCombinationMember); } } } } String newDimSetName = String.Format("By {0}", dim.DimensionName); if (context.DimensionSets.Count(x => x.DimensionSetName.Equals(newDimSetName)) == 0) { DimensionSet newSet = context.DimensionSets.Create(); newSet.DimensionSetName = String.Format("By {0}", dim.DimensionName); DimensionSetMember newMember = context.DimensionSetMembers.Create(); dim.DimensionSetMembers.Add(newMember); newSet.DimensionSetMembers.Add(newMember); foreach (DimensionValue dimValue in dim.DimensionValues) { DimensionSetCombination newCombination = context.DimensionSetCombinations.Create(); newCombination.DimensionSet = newSet; newCombination.DimensionSetCombinationName = String.Format("{0}: {1}", dim.DimensionName, dimValue.DimensionValue1); DimensionSetCombinationMember newCombinationMember = context.DimensionSetCombinationMembers.Create(); newCombinationMember.DimensionValue = dimValue; newCombinationMember.DimensionSetCombination = newCombination; context.DimensionSetCombinations.Add(newCombination); context.DimensionSetCombinationMembers.Add(newCombinationMember); } allNewSets.Add(newSet); } } context.SaveChanges(); context.Dispose(); }