private ClusteredIndexInfo GetClusteredIndexInfo(Table table)
{
ClusteredIndexInfo info = null;
StringBuilder sb = new StringBuilder();
sb.AppendLine(@"SELECT");
sb.AppendLine(@" i.name,");
sb.AppendLine(@" i.is_unique,");
sb.AppendLine(@" i.is_primary_key,");
sb.AppendLine(@" c.key_ordinal,");
sb.AppendLine(@" f.name,");
sb.AppendLine(@" f.is_nullable");
sb.AppendLine(@"FROM sys.indexes AS i");
sb.AppendLine(@"INNER JOIN sys.tables AS t ON t.object_id = i.object_id");
sb.AppendLine(@"INNER JOIN sys.index_columns AS c ON c.object_id = t.object_id AND c.index_id = i.index_id");
sb.AppendLine(@"INNER JOIN sys.columns AS f ON f.object_id = t.object_id AND f.column_id = c.column_id");
sb.AppendLine(@"WHERE");
sb.AppendLine(@" t.object_id = OBJECT_ID(@table) AND i.type = 1 -- CLUSTERED");
sb.AppendLine(@"ORDER BY");
sb.AppendLine(@"c.key_ordinal ASC;");
string sql = sb.ToString();
using (SqlConnection connection = new SqlConnection(this.ConnectionString))
using (SqlCommand command = new SqlCommand(sql, connection))
{
connection.Open();
command.Parameters.AddWithValue("table", table.Name);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
info = new ClusteredIndexInfo()
{
NAME = reader.GetString(0),
IS_UNIQUE = reader.GetBoolean(1),
IS_PRIMARY_KEY = reader.GetBoolean(2)
};
info.COLUMNS.Add(new ClusteredIndexColumnInfo()
{
KEY_ORDINAL = reader.GetByte(3),
NAME = reader.GetString(4),
IS_NULLABLE = reader.GetBoolean(5)
});
while (reader.Read())
{
info.COLUMNS.Add(new ClusteredIndexColumnInfo()
{
KEY_ORDINAL = reader.GetByte(3),
NAME = reader.GetString(4),
IS_NULLABLE = reader.GetBoolean(5)
});
}
}
}
}
return(info);
}
private void ReadSQLMetadata(Table table)
{
List <FieldSqlInfo> sql_fields = GetSqlFields(table);
ClusteredIndexInfo indexInfo = this.GetClusteredIndexInfo(table);
foreach (FieldSqlInfo info in sql_fields)
{
bool found = false; Field field = null;
foreach (Field f in table.Fields)
{
if (f.Name == info.COLUMN_NAME)
{
field = f;
found = true;
break;
}
}
if (!found)
{
Property property = new Property()
{
Entity = table.Entity,
Name = info.COLUMN_NAME,
Purpose = PropertyPurpose.System
};
table.Entity.Properties.Add(property);
field = new Field()
{
Table = table,
Name = info.COLUMN_NAME,
Purpose = FieldPurpose.Value,
Property = property
};
table.Fields.Add(field);
}
field.TypeName = info.DATA_TYPE;
field.Length = info.CHARACTER_MAXIMUM_LENGTH;
field.Precision = info.NUMERIC_PRECISION;
field.Scale = info.NUMERIC_SCALE;
field.IsNullable = info.IS_NULLABLE;
if (indexInfo != null)
{
ClusteredIndexColumnInfo columnInfo = indexInfo.GetColumnByName(info.COLUMN_NAME);
if (columnInfo != null)
{
field.IsPrimaryKey = true;
field.KeyOrdinal = columnInfo.KEY_ORDINAL;
}
}
}
}
private void ReadSQLMetadata(MetaObject metaObject)
{
List <SqlFieldInfo> sql_fields = GetSqlFields(metaObject.Name);
if (sql_fields.Count == 0)
{
return;
}
ClusteredIndexInfo indexInfo = this.GetClusteredIndexInfo(metaObject.Name);
if (indexInfo == null) /* TODO: handle situation somehow*/ } {
private void ReadSQLMetadata(MetaObject @object)
{
if (string.IsNullOrWhiteSpace(@object.TableName))
{
return;
}
List <FieldSqlInfo> sql_fields = GetSqlFields(@object.TableName);
ClusteredIndexInfo indexInfo = this.GetClusteredIndexInfo(@object.TableName);
foreach (FieldSqlInfo info in sql_fields)
{
bool found = false; MetaField field = null;
foreach (MetaProperty p in @object.Properties)
{
if (string.IsNullOrEmpty(p.DbName))
{
continue;
}
if (info.COLUMN_NAME.TrimStart('_') == DBToken.Periodicity)
{
continue;
}
if (info.COLUMN_NAME.TrimStart('_').StartsWith(p.DbName))
{
field = new MetaField()
{
Name = info.COLUMN_NAME,
Purpose = SqlUtility.ParseFieldPurpose(info.COLUMN_NAME)
};
p.Fields.Add(field);
found = true;
break;
}
}
if (!found)
{
string propertyName = string.Empty;
List <int> propertyTypes = null;
if (@object.Token == DBToken.AccumRgT && @object.Owner != null && @object.Owner.Token == DBToken.AccumRg)
{
// find property name in main table object by field name
foreach (MetaProperty ownerProperty in @object.Owner.Properties)
{
if (ownerProperty.Fields.Where(f => f.Name == info.COLUMN_NAME).FirstOrDefault() != null)
{
propertyName = ownerProperty.Name;
if (ownerProperty.PropertyTypes.Count > 0)
{
propertyTypes = ownerProperty.PropertyTypes.ToList();
}
break;
}
}
}
MetaProperty property = @object.Properties.Where(p => p.Name == propertyName).FirstOrDefault();
if (property == null)
{
property = new MetaProperty()
{
Parent = @object,
Name = string.IsNullOrEmpty(propertyName) ? info.COLUMN_NAME : propertyName,
Purpose = MetaPropertyPurpose.System
};
@object.Properties.Add(property);
MatchFieldToProperty(info, property);
if (property.PropertyTypes.Count == 0 && propertyTypes != null)
{
property.PropertyTypes = propertyTypes;
}
}
field = new MetaField()
{
Name = info.COLUMN_NAME,
Purpose = SqlUtility.ParseFieldPurpose(info.COLUMN_NAME)
};
property.Fields.Add(field);
}
field.TypeName = info.DATA_TYPE;
field.Length = info.CHARACTER_MAXIMUM_LENGTH;
field.Precision = info.NUMERIC_PRECISION;
field.Scale = info.NUMERIC_SCALE;
field.IsNullable = info.IS_NULLABLE;
if (indexInfo != null)
{
ClusteredIndexColumnInfo columnInfo = indexInfo.GetColumnByName(info.COLUMN_NAME);
if (columnInfo != null)
{
field.IsPrimaryKey = true;
field.KeyOrdinal = columnInfo.KEY_ORDINAL;
}
}
}
}
private void ReadSQLMetadata(MetaObject metaObject)
{
if (string.IsNullOrWhiteSpace(metaObject.TableName))
{
return;
}
List <FieldSqlInfo> sql_fields = GetSqlFields(metaObject.TableName);
ClusteredIndexInfo indexInfo = GetClusteredIndexInfo(metaObject.TableName);
foreach (FieldSqlInfo info in sql_fields)
{
bool found = false; MetaField field = null;
foreach (MetaProperty p in metaObject.Properties)
{
if (string.IsNullOrEmpty(p.Field))
{
continue;
}
if (info.COLUMN_NAME.TrimStart('_') == MetadataTokens.Periodicity)
{
continue;
}
if (info.COLUMN_NAME.TrimStart('_').StartsWith(p.Field))
{
field = new MetaField()
{
Name = info.COLUMN_NAME,
Purpose = SqlUtility.ParseFieldPurpose(info.COLUMN_NAME)
};
p.Fields.Add(field);
found = true;
break;
}
}
if (!found)
{
string propertyName = string.Empty;
int propertyType = (int)DataTypes.NULL;
if (metaObject.Owner != null && // таблица итогов регистра накопления
metaObject.TypeName == MetaObjectTypes.AccumulationRegister &&
metaObject.Owner.TypeName == MetaObjectTypes.AccumulationRegister)
{
// find property name in main table object by field name
foreach (MetaProperty ownerProperty in metaObject.Owner.Properties)
{
if (ownerProperty.Fields.Where(f => f.Name == info.COLUMN_NAME).FirstOrDefault() != null)
{
propertyName = ownerProperty.Name;
if (ownerProperty.PropertyType == (int)DataTypes.Multiple)
{
propertyType = (int)DataTypes.Multiple;
}
break;
}
}
}
MetaProperty property = metaObject.Properties.Where(p => p.Name == propertyName).FirstOrDefault();
if (property == null)
{
property = new MetaProperty()
{
Name = string.IsNullOrEmpty(propertyName) ? info.COLUMN_NAME : propertyName,
Purpose = PropertyPurpose.System
};
metaObject.Properties.Add(property);
MatchFieldToProperty(info, property);
if (property.PropertyType == (int)DataTypes.NULL)
{
property.PropertyType = propertyType;
}
}
field = new MetaField()
{
Name = info.COLUMN_NAME,
Purpose = SqlUtility.ParseFieldPurpose(info.COLUMN_NAME)
};
property.Fields.Add(field);
}
field.TypeName = info.DATA_TYPE;
field.Length = info.CHARACTER_MAXIMUM_LENGTH;
field.Precision = info.NUMERIC_PRECISION;
field.Scale = info.NUMERIC_SCALE;
field.IsNullable = info.IS_NULLABLE;
if (indexInfo != null)
{
ClusteredIndexColumnInfo columnInfo = indexInfo.GetColumnByName(info.COLUMN_NAME);
if (columnInfo != null)
{
field.IsPrimaryKey = true;
field.KeyOrdinal = columnInfo.KEY_ORDINAL;
}
}
}
}
