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; } } } }