/// <summary>
 /// Delete all existing logs from the database. Useful in demo scenarios to initialize the database.
 /// </summary>
 /// <param name="connectionInfo">Information required to connect to the configuration and logging database.</param>
 public static void ClearLogTable(ConfigDatabaseConnectionInfo connectionInfo)
 {
     using (var connection = new SqlConnection(GetConnectionString(connectionInfo)))
     {
         connection.Open();
         using (var command = new SqlCommand())
         {
             command.Connection  = connection;
             command.CommandType = CommandType.Text;
             command.CommandText = "DELETE FROM [dbo].[PartitionedModelLog];";
             command.ExecuteNonQuery();
         }
     }
 }
        private static string GetConnectionString(ConfigDatabaseConnectionInfo connectionInfo)
        {
            string connectionString;

            if (connectionInfo.IntegratedAuth)
            {
                connectionString = $"Server={connectionInfo.Server};Database={connectionInfo.Database};Integrated Security=SSPI;";
            }
            else
            {
                connectionString = $"Server={connectionInfo.Server};Database={connectionInfo.Database};User ID={connectionInfo.UserName};Password={connectionInfo.Password};";
            }

            return(connectionString);
        }
        /// <summary>
        /// Read configuration information from the database.
        /// </summary>
        /// <param name="connectionInfo">Information required to connect to the configuration and logging database.</param>
        /// <returns>Collection of partitioned models with configuration information.</returns>
        public static List <ModelConfiguration> ReadConfig(ConfigDatabaseConnectionInfo connectionInfo)
        {
            using (SqlConnection connection = new SqlConnection(GetConnectionString(connectionInfo)))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection  = connection;
                    command.CommandType = CommandType.Text;
                    command.CommandText = @"  
                        SELECT [ModelConfigurationID]
                              ,[AnalysisServicesServer]
                              ,[AnalysisServicesDatabase]
                              ,[InitialSetUp]
                              ,[IncrementalOnline]
                              ,[IncrementalParallelTables]
                              ,[IntegratedAuth]
                              ,[TableConfigurationID]
                              ,[AnalysisServicesTable]
                              ,[Partitioned]
                              ,[PartitioningConfigurationID]
                              ,[Granularity]
                              ,[NumberOfPartitionsFull]
                              ,[NumberOfPartitionsForIncrementalProcess]
                              ,[MaxDate]
                              ,[SourceTableName]
                              ,[SourcePartitionColumn]
                          FROM [dbo].[vPartitioningConfiguration]
                          ORDER BY
                               [ModelConfigurationID],
                               [TableConfigurationID],
                               [PartitioningConfigurationID];";

                    List <ModelConfiguration> modelConfigs = new List <ModelConfiguration>();
                    ModelConfiguration        modelConfig  = null;
                    int currentModelConfigurationID        = -1;

                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        TableConfiguration tableConfig  = null;
                        int currentTableConfigurationID = -1;

                        if (modelConfig == null || currentModelConfigurationID != Convert.ToInt32(reader["ModelConfigurationID"]))
                        {
                            modelConfig = new ModelConfiguration();
                            modelConfig.TableConfigurations = new List <TableConfiguration>();
                            modelConfigs.Add(modelConfig);

                            modelConfig.ModelConfigurationID         = Convert.ToInt32(reader["ModelConfigurationID"]);
                            modelConfig.AnalysisServicesServer       = Convert.ToString(reader["AnalysisServicesServer"]);
                            modelConfig.AnalysisServicesDatabase     = Convert.ToString(reader["AnalysisServicesDatabase"]);
                            modelConfig.InitialSetUp                 = Convert.ToBoolean(reader["InitialSetUp"]);
                            modelConfig.IncrementalOnline            = Convert.ToBoolean(reader["IncrementalOnline"]);
                            modelConfig.IncrementalParallelTables    = Convert.ToBoolean(reader["IncrementalParallelTables"]);
                            modelConfig.IntegratedAuth               = Convert.ToBoolean(reader["IntegratedAuth"]);
                            modelConfig.ConfigDatabaseConnectionInfo = connectionInfo;

                            currentModelConfigurationID = modelConfig.ModelConfigurationID;
                        }

                        if (tableConfig == null || currentTableConfigurationID != Convert.ToInt32(reader["TableConfigurationID"]))
                        {
                            tableConfig = new TableConfiguration();
                            tableConfig.PartitioningConfigurations = new List <PartitioningConfiguration>();
                            modelConfig.TableConfigurations.Add(tableConfig);

                            tableConfig.TableConfigurationID  = Convert.ToInt32(reader["TableConfigurationID"]);
                            tableConfig.AnalysisServicesTable = Convert.ToString(reader["AnalysisServicesTable"]);

                            currentTableConfigurationID = tableConfig.TableConfigurationID;
                        }

                        if (Convert.ToBoolean(reader["Partitioned"]))
                        {
                            tableConfig.PartitioningConfigurations.Add(
                                new PartitioningConfiguration(
                                    Convert.ToInt32(reader["PartitioningConfigurationID"]),
                                    (Granularity)Convert.ToInt32(reader["Granularity"]),
                                    Convert.ToInt32(reader["NumberOfPartitionsFull"]),
                                    Convert.ToInt32(reader["NumberOfPartitionsForIncrementalProcess"]),
                                    Convert.ToDateTime(reader["MaxDate"]),
                                    Convert.ToString(reader["SourceTableName"]),
                                    Convert.ToString(reader["SourcePartitionColumn"])
                                    )
                                );
                        }
                    }

                    return(modelConfigs);
                }
            }
        }
Ejemplo n.º 4
0
        /// <summary>
        /// Read configuration information from the database.
        /// </summary>
        /// <param name="connectionInfo">Information required to connect to the configuration and logging database.</param>
        /// <param name="modelConfigurationIDs">Comma-delimited list of ModelConfigurationIDs to filter on when getting worklist from the configuration and logging database.</param>
        /// <returns>Collection of partitioned models with configuration information.</returns>
        public static List <ModelConfiguration> ReadConfig(ConfigDatabaseConnectionInfo connectionInfo, string modelConfigurationIDs)
        {
            string commandText = String.Format(@"  
                        SELECT [ModelConfigurationID]
                              ,[AnalysisServicesServer]
                              ,[AnalysisServicesDatabase]
                              ,[InitialSetUp]
                              ,[IncrementalOnline]
                              ,[IntegratedAuth]
                              ,[MaxParallelism]
                              ,[CommitTimeout]
                              ,[RetryAttempts]
                              ,[RetryWaitTimeSeconds]
                              ,[TableConfigurationID]
                              ,[AnalysisServicesTable]
                              ,[Partitioned]
                              ,[PartitioningConfigurationID]
                              ,[Granularity]
                              ,[NumberOfPartitionsFull]
                              ,[NumberOfPartitionsForIncrementalProcess]
                              ,[MaxDateIsNow]
                              ,[MaxDate]
                              ,[IntegerDateKey]
                              ,[TemplateSourceQuery]
                          FROM [dbo].[vPartitioningConfiguration]
                          WHERE [DoNotProcess] = 0 {0}
                          ORDER BY
                               [ModelConfigurationID],
                               [TableConfigurationID],
                               [PartitioningConfigurationID];",
                                               (String.IsNullOrEmpty(modelConfigurationIDs) ? "" : $" AND [ModelConfigurationID] IN ({modelConfigurationIDs}) "));

            using (SqlConnection connection = new SqlConnection(GetConnectionString(connectionInfo)))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection  = connection;
                    command.CommandType = CommandType.Text;
                    command.CommandText = commandText;

                    List <ModelConfiguration> modelConfigs = new List <ModelConfiguration>();
                    ModelConfiguration        modelConfig  = null;
                    int currentModelConfigurationID        = -1;
                    TableConfiguration tableConfig         = null;
                    int currentTableConfigurationID        = -1;

                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        if (modelConfig == null || currentModelConfigurationID != Convert.ToInt32(reader["ModelConfigurationID"]))
                        {
                            modelConfig = new ModelConfiguration();
                            modelConfig.TableConfigurations = new List <TableConfiguration>();
                            modelConfigs.Add(modelConfig);

                            modelConfig.ModelConfigurationID         = Convert.ToInt32(reader["ModelConfigurationID"]);
                            modelConfig.AnalysisServicesServer       = Convert.ToString(reader["AnalysisServicesServer"]);
                            modelConfig.AnalysisServicesDatabase     = Convert.ToString(reader["AnalysisServicesDatabase"]);
                            modelConfig.InitialSetUp                 = Convert.ToBoolean(reader["InitialSetUp"]);
                            modelConfig.IncrementalOnline            = Convert.ToBoolean(reader["IncrementalOnline"]);
                            modelConfig.IntegratedAuth               = Convert.ToBoolean(reader["IntegratedAuth"]);
                            modelConfig.MaxParallelism               = Convert.ToInt32(reader["MaxParallelism"]);
                            modelConfig.CommitTimeout                = Convert.ToInt32(reader["CommitTimeout"]);
                            modelConfig.RetryAttempts                = Convert.ToInt32(reader["RetryAttempts"]);
                            modelConfig.RetryWaitTimeSeconds         = Convert.ToInt32(reader["RetryWaitTimeSeconds"]);
                            modelConfig.ConfigDatabaseConnectionInfo = connectionInfo;

                            currentModelConfigurationID = modelConfig.ModelConfigurationID;
                        }

                        if (tableConfig == null || currentTableConfigurationID != Convert.ToInt32(reader["TableConfigurationID"]))
                        {
                            tableConfig = new TableConfiguration();
                            tableConfig.PartitioningConfigurations = new List <PartitioningConfiguration>();
                            modelConfig.TableConfigurations.Add(tableConfig);

                            tableConfig.TableConfigurationID  = Convert.ToInt32(reader["TableConfigurationID"]);
                            tableConfig.AnalysisServicesTable = Convert.ToString(reader["AnalysisServicesTable"]);

                            currentTableConfigurationID = tableConfig.TableConfigurationID;
                        }

                        if (Convert.ToBoolean(reader["Partitioned"]))
                        {
                            tableConfig.PartitioningConfigurations.Add(
                                new PartitioningConfiguration(
                                    Convert.ToInt32(reader["PartitioningConfigurationID"]),
                                    (Granularity)Convert.ToInt32(reader["Granularity"]),
                                    Convert.ToInt32(reader["NumberOfPartitionsFull"]),
                                    Convert.ToInt32(reader["NumberOfPartitionsForIncrementalProcess"]),
                                    Convert.ToBoolean(reader["MaxDateIsNow"]),
                                    (reader["MaxDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(reader["MaxDate"])),
                                    Convert.ToBoolean(reader["IntegerDateKey"]),
                                    Convert.ToString(reader["TemplateSourceQuery"])
                                    )
                                );
                        }
                    }

                    return(modelConfigs);
                }
            }
        }