internal void ExecuteSyncDescriptions(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox sandbox, IServiceProvider provider, string tableName)
{
try
{
#if DENALI || SQL2014
var db = sandbox.Database;
Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.AMOCode code;
#else
Database db = null;
if (!sandbox.IsTabularMetadata)
{
db = ((Microsoft.AnalysisServices.BackEnd.DataModelingSandboxAmo)sandbox.Impl).Database;
}
else
{
db = null;
}
Microsoft.AnalysisServices.BackEnd.AMOCode code;
#endif
code = delegate
{
int iDescriptionsSet;
Microsoft.AnalysisServices.BackEnd.SandboxTransactionProperties properties = new Microsoft.AnalysisServices.BackEnd.SandboxTransactionProperties();
properties.RecalcBehavior = Microsoft.AnalysisServices.BackEnd.TransactionRecalcBehavior.Default;
using (Microsoft.AnalysisServices.BackEnd.SandboxTransaction tran = sandbox.CreateTransaction(properties))
{
if (!TabularHelpers.EnsureDataSourceCredentials(sandbox))
{
MessageBox.Show("Cancelling Sync Descriptions because data source credentials were not entered.", "BIDS Helper Tabular Sync Descriptions - Cancelled!");
tran.RollbackAndContinue();
return;
}
#if !(DENALI || SQL2014)
Microsoft.AnalysisServices.BackEnd.DataModelingTable table = sandbox.Tables[tableName];
if (table.IsStructuredDataSource)
{
MessageBox.Show("BI Developer Extensions does not yet support modern (Power Query) data sources.", "BI Developer Extensions");
return;
}
iDescriptionsSet = SyncDescriptionsPlugin.SyncDescriptions(table, true);
if (iDescriptionsSet > 0)
{
table.UpdateNowOrLater();
}
#else
Dimension d = db.Dimensions.GetByName(tableName);
iDescriptionsSet = SyncDescriptionsPlugin.SyncDescriptions(d, true, provider, true);
if (iDescriptionsSet > 0)
{
db.Update(UpdateOptions.ExpandFull);
}
#endif
tran.GetType().InvokeMember("Commit", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.InvokeMethod | System.Reflection.BindingFlags.Public, null, tran, null); //The .Commit() function used to return a list of strings, but in the latest set of code it is a void method which leads to "method not found" errors
//tran.Commit();
}
MessageBox.Show("Set " + iDescriptionsSet + " descriptions successfully.", "BIDS Helper - Sync Descriptions");
};
#if DENALI || SQL2014
sandbox.ExecuteAMOCode(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationType.Update, Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationCancellability.AlwaysExecute, code, true);
#else
sandbox.ExecuteEngineCode(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationType.Update, Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationCancellability.AlwaysExecute, code, true);
#endif
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace, "BIDS Helper - Error");
}
}
internal static int SyncDescriptions(Microsoft.AnalysisServices.BackEnd.DataModelingTable d, bool bPromptForProperties)
{
int iUpdatedDescriptions = 0;
Microsoft.AnalysisServices.BackEnd.EditMappingUtility util = new Microsoft.AnalysisServices.BackEnd.EditMappingUtility(d.Sandbox);
var conn = ((Microsoft.AnalysisServices.BackEnd.RelationalDataStorage)((util.GetDataSourceConnection(util.GetDataSourceID(d.Id), d.Sandbox)))).DataSourceConnection;
conn.Open();
System.Data.Common.DbCommand cmd = conn.CreateCommand();
string sDBTableName = d.SourceTableName;
sq = conn.Cartridge.IdentStartQuote;
fq = conn.Cartridge.IdentEndQuote;
//cartridge = conn.Cartridge;
if (conn.SourceType != Microsoft.AnalysisServices.BackEnd.DataSourceType.SqlServer && conn.SourceType != Microsoft.AnalysisServices.BackEnd.DataSourceType.SqlAzure)
{
MessageBox.Show("Data source [" + conn.ConnectionName + "] connects to " + conn.SourceType.ToString() + " which may not be supported.");
}
String sql = "select distinct Name from sys.extended_properties order by Name";
if (bPromptForProperties)
{
SSAS.SyncDescriptionsForm form = new SSAS.SyncDescriptionsForm();
cmd.CommandText = sql;
System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
List <string> listNames = new List <string>();
while (reader.Read())
{
listNames.Add(Convert.ToString(reader["Name"]));
form.listOtherProperties.Items.Add(Convert.ToString(reader["Name"]));
}
reader.Close();
form.cmbDescriptionProperty.DataSource = listNames;
DialogResult result = form.ShowDialog();
if (result != DialogResult.OK)
{
return(iUpdatedDescriptions);
}
DescriptionPropertyName = form.cmbDescriptionProperty.GetItemText(form.cmbDescriptionProperty.SelectedItem);
List <string> listOtherProperties = new List <string>();
for (int i = 0; i < form.listOtherProperties.CheckedItems.Count; i++)
{
listOtherProperties.Add(form.listOtherProperties.GetItemText(form.listOtherProperties.CheckedItems[i]));
}
OtherPropertyNamesToInclude = listOtherProperties.ToArray();
OverwriteExistingDescriptions = form.chkOverwriteExistingDescriptions.Checked;
}
if ((string.IsNullOrEmpty(d.Description) || OverwriteExistingDescriptions) &&
!string.IsNullOrEmpty(sDBTableName))
{
sql = "SELECT PropertyName = p.name" + "\r\n"
+ ",PropertyValue = CAST(p.value AS sql_variant)" + "\r\n"
+ "FROM sys.all_objects AS tbl" + "\r\n"
+ "INNER JOIN sys.schemas sch ON sch.schema_id = tbl.schema_id" + "\r\n"
+ "INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=0 AND p.class=1" + "\r\n"
//+ "where sch.name = '" + oDimensionKeyTable.ExtendedProperties["DbSchemaName"].ToString().Replace("'", "''") + "'\r\n"
+ "where tbl.object_id = object_id('" + sDBTableName.Replace("'", "''") + "')\r\n"
+ "order by p.name";
string sNewDimensionDescription = "";
//DataSet dsTableProperties = new DataSet();
cmd.CommandText = sql;
System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (string.Compare((string)reader["PropertyName"], DescriptionPropertyName, true) == 0)
{
sNewDimensionDescription = (string)reader["PropertyValue"];
}
}
reader.Close();
reader = cmd.ExecuteReader();
while (reader.Read())
{
foreach (string sProp in OtherPropertyNamesToInclude)
{
if (string.Compare((string)reader["PropertyName"], sProp, true) == 0 && !string.IsNullOrEmpty((string)reader["PropertyValue"]))
{
if (sNewDimensionDescription.Length > 0)
{
sNewDimensionDescription += "\r\n";
}
sNewDimensionDescription += (string)reader["PropertyName"] + ": " + (string)reader["PropertyValue"];
}
}
}
reader.Close();
if (!string.IsNullOrEmpty(sNewDimensionDescription))
{
d.Description = sNewDimensionDescription;
iUpdatedDescriptions++;
}
}
foreach (Microsoft.AnalysisServices.BackEnd.DataModelingColumn a in d.Columns)
{
if (a.IsRowNumber || a.IsCalculated)
{
continue;
}
if ((string.IsNullOrEmpty(a.Description) || OverwriteExistingDescriptions) &&
(!string.IsNullOrEmpty(sDBTableName)))
{
sql = "SELECT PropertyName = p.name" + "\r\n"
+ ",PropertyValue = CAST(p.value AS sql_variant)" + "\r\n"
+ "FROM sys.all_objects AS tbl" + "\r\n"
+ "INNER JOIN sys.schemas sch ON sch.schema_id = tbl.schema_id" + "\r\n"
+ "INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id" + "\r\n"
+ "INNER JOIN sys.extended_properties AS p ON p.major_id=clmns.object_id AND p.minor_id=clmns.column_id AND p.class=1" + "\r\n"
//+ "where sch.name = '" + oDsvTable.ExtendedProperties["DbSchemaName"].ToString().Replace("'", "''") + "'\r\n"
+ "where tbl.object_id = object_id('" + sDBTableName.Replace("'", "''") + "')\r\n"
+ "and clmns.name = '" + a.DBColumnName.Replace("'", "''") + "'\r\n"
+ "order by p.name";
string sNewDescription = "";
cmd.CommandText = sql;
System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (string.Compare((string)reader["PropertyName"], DescriptionPropertyName, true) == 0)
{
sNewDescription = (string)reader["PropertyValue"];
}
}
reader.Close();
cmd.CommandText = sql;
reader = cmd.ExecuteReader();
while (reader.Read())
{
foreach (string sProp in OtherPropertyNamesToInclude)
{
if (string.Compare((string)reader["PropertyName"], sProp, true) == 0 && !string.IsNullOrEmpty((string)reader["PropertyValue"]))
{
if (sNewDescription.Length > 0)
{
sNewDescription += "\r\n";
}
sNewDescription += (string)reader["PropertyName"] + ": " + (string)reader["PropertyValue"];
}
}
}
reader.Close();
if (!string.IsNullOrEmpty(sNewDescription))
{
a.Description = sNewDescription;
iUpdatedDescriptions++;
}
}
}
return(iUpdatedDescriptions);
}
