示例#1
0
 public LookupTable(string name, FactorTableColumnDefinition[] keys, FactorTableColumnDefinition[] columns)
     : this()
 {
     Name = name;
     Keys = keys;
     Columns = columns;
 }
示例#2
0
        //public LookupTable ToLookupTable(DataTable table)
        //{
        //    if (!table.Rows.Cast<DataRow>().Any())
        //        return new LookupTable();
        //    var lookupTable = new LookupTable();
        //    var xml = XElement.Parse(table.Rows.Cast<DataRow>().First()[0].ToString());
        //    var tableXml = xml.Element("table");
        //    if (tableXml != null)
        //    {
        //        lookupTable.Name = tableXml.Element("name").Value;
        //        lookupTable.Properties.Schema = tableXml.Element("schema").Value;
        //        lookupTable.Properties.EffectiveDate = tableXml.Element("effectiveDate").Value;
        //        foreach (var row in tableXml.Element("rows").Elements("row"))
        //        {
        //            var columns = row.Elements().Where(x => !MetadataColumnNames.Contains(x.Name.ToString()));
        //        }
        //    }
        //    return lookupTable;
        //}
        public LookupTable ToLookupTable(IDictionary<string, object> parameters, DataTable table)
        {
            var dataTableColumns = table.Columns.Cast<DataColumn>().ToList();

            var metadataXml = XElement.Parse(Convert.ToString(parameters["Metadata"]));
            var factorKeyColumns = new List<FactorTableColumnDefinition>();
            var factorNonKeyColumns = new List<FactorTableColumnDefinition>();
            var metadataColumns = new List<FactorTableColumnDefinition>();

            var columnXml = metadataXml.Elements("ColumnMetadata");

            foreach (var column in columnXml.Elements("Column"))
            {
                //get the base properties
                var columnName = column.Element("Name");

                var columnDefinition = new FactorTableColumnDefinition
                {
                    Name = columnName != null ? columnName.Value : string.Empty
                };

                columnDefinition.DataType =
                    dataTableColumns.Single(c => c.ColumnName == columnDefinition.Name).DataType;

                //get the extended properties
                if (column.Elements("Properties").Descendants().Any())
                {
                    foreach (var property in column.Elements("Properties").Elements("Property"))
                    {
                        var propertyName = property.Element("Name");
                        if (propertyName != null)
                        {
                            var propertyValue = property.Element("Value");

                            if (propertyValue != null && !string.IsNullOrEmpty(propertyValue.Value))
                            {
                                if (propertyName.Value == "IsKey")
                                    columnDefinition.IsKey = propertyValue.Value == "1";
                                else if (propertyName.Value == "LookupType")
                                    columnDefinition.LookupType =
                                        (LookupType)Enum.Parse(typeof(LookupType), propertyValue.Value);
                            }
                        }
                    }
                }

                if (MetadataColumnNames.Contains(columnDefinition.Name))
                    metadataColumns.Add(columnDefinition);
                else if (columnDefinition.IsKey)
                    factorKeyColumns.Add(columnDefinition);
                else
                    factorNonKeyColumns.Add(columnDefinition);
            }

            var properties = new ExpandoObject() as IDictionary<string, object>;
            foreach (var property in metadataXml.Elements().Where(e => e.Name != "ColumnMetadata"))
            {
                properties.Add(property.Name.ToString(), property.Value);
            }

            var lookupTable = new LookupTable(properties["Table"].ToString(),
                metadataColumns.ToArray(),
                factorKeyColumns.ToArray(),
                factorNonKeyColumns.ToArray())
            {
                Id = parameters["ChangeId"].ToString(),
                Properties = properties
            };

            foreach (var row in table.Rows.Cast<DataRow>())
            {
                var lookupTableRow = new LookupTableRow
                {
                    Active = Convert.ToBoolean(row["Active"]),
                    EffectiveDate = Convert.ToDateTime(row["EffectiveDate"]),
                    ExpirationDate = Convert.ToDateTime(row["ExpirationDate"]),
                    RowId = row["Id"].ToString(),
                    Sequence = Convert.ToInt32(row["Sequence"]),
                    ChangeId = row["ChangeId"].ToString(),
                    IsOverride = Convert.ToBoolean(row["IsOverride"].ToString())
                };

                foreach (var key in lookupTable.Keys)
                {
                    lookupTableRow.KeyValues.Add(new LookupTableKey(key.Name, row[key.Name].ToString(), null));
                }

                foreach (var col in lookupTable.Columns)
                {
                    lookupTableRow.ColumnValues.Add(col.Name, row[col.Name]);
                }

                lookupTable.Rows.Add(lookupTableRow);
            }

            return lookupTable;
        }
示例#3
0
 public LookupTableKeyModel ToLookupTableKeyModel(FactorTableColumnDefinition keyColumn, string tableName, string tableId, string tableSchema)
 {
     return new LookupTableKeyModel
     {
         Name = keyColumn.Name,
         TableId = tableId,
         TableName = tableName,
         TableSchema = tableSchema,
         LookupType = keyColumn.LookupType
     };
 }
示例#4
0
 public LookupTable(string name, FactorTableColumnDefinition[] metadata, FactorTableColumnDefinition[] keys,
     FactorTableColumnDefinition[] columns)
     : this(name, keys, columns)
 {
     MetadataColumns = metadata;
 }
示例#5
0
 private string ColumnExtendedProperty(FactorTableColumnDefinition column, string name, object value)
 {
     return string.Format(
         "EXEC sys.sp_addextendedproperty @name=N'{0}', @value=N'{1}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{2}', @level2type=N'COLUMN',@level2name=N'{3}'",
             name, value, Table.Name, FlatTableQueries.FixColumnName(column.Name));
 }
示例#6
0
        /// <summary>
        /// Gets a formatted SQL query (targeted to SQL Server) for creating a table. The query will
        /// create the table and add extended properties to the table and the columns.
        /// </summary>
        /// <param name="schema">The name of the database schema.</param>
        /// <param name="table">The name of the table.</param>
        /// <param name="tableType"></param>
        /// <param name="effectiveDate">The effective date to add to the table.</param>
        /// <param name="metadataColumns">A listing of the metadata columns to apply to the table.</param>
        /// <param name="keyColumns">A listing of key columns to apply to the table.</param>
        /// <param name="dataColumns">A listing of data columns to apply to the table.</param>
        /// <param name="changeId"></param>
        /// <returns>The formatted SQL query.</returns>
        public static string GetCreateTableStatement(string schema,
                                                     string table,
                                                     string changeId,
                                                     string tableType,
                                                     DateTime effectiveDate,
                                                     FactorTableColumnDefinition[] metadataColumns,
                                                     FactorTableColumnDefinition[] keyColumns,
                                                     FactorTableColumnDefinition[] dataColumns)
        {
            if (string.IsNullOrEmpty(schema))
                throw new ArgumentNullException("schema");

            if (string.IsNullOrEmpty(table))
                throw new ArgumentNullException("table");

            if(string.IsNullOrEmpty(changeId))
                throw new ArgumentNullException("changeId");

            if (effectiveDate == DateTime.MinValue || effectiveDate == DateTime.MaxValue)
                throw new ArgumentException("Effective date must be a valid effective date.");

            if (!metadataColumns.Any() || !keyColumns.Any() || !dataColumns.Any())
                throw new ArgumentException("Metadata, key, and data columns must be supplied for table creation.");

            var allColumnNames =
                metadataColumns.Union(keyColumns)
                    .Union(dataColumns)
                    .Select(c => new { c.Name, c.DataType })
                    .ToDictionary(c => c.Name, c => c.DataType);

            const string createTemplate = "CREATE TABLE [{0}].[{1}] ({2});";

            //create table statement
            var builder = new StringBuilder();
            builder.AppendFormat(createTemplate, schema, table,
                string.Join(",", allColumnNames.Select(c => FormatCreateSqlColumn(c.Key, c.Value))))
                   .AppendLine();

            //table extended properties
            const string sqlTableExtendedPropertyFormat =
                "EXEC sp_addextendedproperty @name = N'{0}', @value = '{1}', @level0type = N'Schema', @level0name = '{2}', @level1type = 'Table', @level1name = '{3}';";

            builder.AppendFormat(sqlTableExtendedPropertyFormat, "Active", 1, schema, table).AppendLine();
            builder.AppendFormat(sqlTableExtendedPropertyFormat, "ChangeId", changeId, schema, table).AppendLine();
            builder.AppendFormat(sqlTableExtendedPropertyFormat, "CreateDate", DateTime.Now, schema, table).AppendLine();
            builder.AppendFormat(sqlTableExtendedPropertyFormat, "EffectiveDate", effectiveDate, schema, table).AppendLine();
            builder.AppendFormat(sqlTableExtendedPropertyFormat, "Id", 1, schema, table).AppendLine();
            builder.AppendFormat(sqlTableExtendedPropertyFormat, "TableType", tableType, schema, table).AppendLine();
            builder.AppendLine();

            //column extended properties
            const string sqlTableColumnExtendedPropertyFormat =
                "EXEC sp_addextendedproperty @name = N'{0}', @value = '{1}', @level0type = N'Schema', @level0name = '{2}', @level1type = 'Table', @level1name = '{3}', @level2type = 'Column', @level2name = '{4}';";

            var keysDataColumns = keyColumns.Union(dataColumns);

            foreach (var column in keysDataColumns)
            {
                builder.AppendFormat(sqlTableColumnExtendedPropertyFormat, "IsKey", column.IsKey ? 1 : 0, schema, table,
                    column.Name).AppendLine();

                if (column.IsKey)
                {
                    builder.AppendFormat(sqlTableColumnExtendedPropertyFormat, "LookupType",
                        Enum.GetName(typeof(LookupType), column.LookupType), schema, table, column.Name).AppendLine();
                }
            }

            return builder.ToString();
        }