public LookupTable(string name, FactorTableColumnDefinition[] keys, FactorTableColumnDefinition[] columns)
: this()
{
Name = name;
Keys = keys;
Columns = columns;
}
//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;
}
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
};
}
public LookupTable(string name, FactorTableColumnDefinition[] metadata, FactorTableColumnDefinition[] keys,
FactorTableColumnDefinition[] columns)
: this(name, keys, columns)
{
MetadataColumns = metadata;
}
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));
}
/// <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();
}