private List <FieldSqlInfo> GetSqlFields(Table table)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine(@"SELECT");
sb.AppendLine(@" ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE,");
sb.AppendLine(@" ISNULL(CHARACTER_MAXIMUM_LENGTH, 0) AS CHARACTER_MAXIMUM_LENGTH,");
sb.AppendLine(@" ISNULL(NUMERIC_PRECISION, 0) AS NUMERIC_PRECISION,");
sb.AppendLine(@" ISNULL(NUMERIC_SCALE, 0) AS NUMERIC_SCALE,");
sb.AppendLine(@" CASE WHEN IS_NULLABLE = 'NO' THEN CAST(0x00 AS bit) ELSE CAST(0x01 AS bit) END AS IS_NULLABLE");
sb.AppendLine(@"FROM");
sb.AppendLine(@" INFORMATION_SCHEMA.COLUMNS");
sb.AppendLine(@"WHERE");
sb.AppendLine(@" TABLE_NAME = N'{0}'");
sb.AppendLine(@"ORDER BY");
sb.AppendLine(@" ORDINAL_POSITION ASC;");
string sql = string.Format(sb.ToString(), table.Name);
List <FieldSqlInfo> list = new List <FieldSqlInfo>();
using (SqlConnection connection = new SqlConnection(this.ConnectionString))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
FieldSqlInfo item = new FieldSqlInfo()
{
ORDINAL_POSITION = reader.GetInt32(0),
COLUMN_NAME = reader.GetString(1),
DATA_TYPE = reader.GetString(2),
CHARACTER_MAXIMUM_LENGTH = reader.GetInt32(3),
NUMERIC_PRECISION = reader.GetByte(4),
NUMERIC_SCALE = reader.GetInt32(5),
IS_NULLABLE = reader.GetBoolean(6)
};
list.Add(item);
}
}
}
}
return(list);
}
private void MatchFieldToProperty(FieldSqlInfo field, MetaProperty property)
{
string columnName = field.COLUMN_NAME.TrimStart('_');
if (columnName.StartsWith(DBToken.IDRRef))
{
property.Name = "Ссылка";
property.DbName = DBToken.IDRRef;
property.PropertyTypes.Add(int.MaxValue);
}
else if (columnName.StartsWith(DBToken.Version))
{
property.Name = "ВерсияДанных";
property.DbName = DBToken.Version;
property.PropertyTypes.Add((int)TypeCodes.Binary);
}
else if (columnName.StartsWith(DBToken.Marked))
{
property.Name = "ПометкаУдаления";
property.DbName = DBToken.Marked;
property.PropertyTypes.Add((int)TypeCodes.Boolean);
}
else if (columnName.StartsWith(DBToken.PredefinedID))
{
property.Name = "ИмяПредопределённыхДанных";
property.DbName = DBToken.PredefinedID;
property.PropertyTypes.Add((int)TypeCodes.Guid);
}
else if (columnName.StartsWith(DBToken.Code))
{
property.Name = "Код";
property.DbName = DBToken.Code;
//property.PropertyTypes.Add((int)TypeCodes.String);
}
else if (columnName.StartsWith(DBToken.Description))
{
property.Name = "Наименование";
property.DbName = DBToken.Description;
property.PropertyTypes.Add((int)TypeCodes.String);
}
else if (columnName.StartsWith(DBToken.Folder))
{
property.Name = "ЭтоГруппа";
property.DbName = DBToken.Folder;
property.PropertyTypes.Add((int)TypeCodes.Boolean);
}
else if (columnName.StartsWith(DBToken.ParentIDRRef))
{
property.Name = "Родитель";
property.DbName = DBToken.ParentIDRRef;
property.PropertyTypes.Add(int.MaxValue);
}
else if (columnName.StartsWith(DBToken.OwnerID))
{
property.Name = "Владелец";
property.DbName = DBToken.OwnerID;
property.PropertyTypes.Add(int.MaxValue);
}
else if (columnName.StartsWith(DBToken.DateTime))
{
property.Name = "Дата";
property.DbName = DBToken.DateTime;
property.PropertyTypes.Add((int)TypeCodes.DateTime);
}
else if (columnName == DBToken.Number)
{
property.Name = "Номер";
property.DbName = DBToken.Number;
//property.PropertyTypes.Add((int)TypeCodes.String);
}
else if (columnName.StartsWith(DBToken.Posted))
{
property.Name = "Проведён";
property.DbName = DBToken.Posted;
property.PropertyTypes.Add((int)TypeCodes.Boolean);
}
else if (columnName == DBToken.NumberPrefix)
{
property.Name = "МоментВремени";
property.DbName = DBToken.NumberPrefix;
}
else if (columnName.StartsWith(DBToken.Periodicity))
{
property.Name = "Периодичность";
property.DbName = DBToken.Periodicity;
}
else if (columnName.StartsWith(DBToken.Period))
{
property.Name = "Период";
property.DbName = DBToken.Period;
}
else if (columnName.StartsWith(DBToken.ActualPeriod))
{
property.Name = "ПериодАктуальности";
property.DbName = DBToken.ActualPeriod;
}
else if (columnName.StartsWith(DBToken.Recorder))
{
property.Name = "Регистратор";
property.DbName = DBToken.Recorder;
property.PropertyTypes.Add(int.MaxValue);
}
else if (columnName.StartsWith(DBToken.Active))
{
property.Name = "Активность";
property.DbName = DBToken.Active;
property.PropertyTypes.Add((int)TypeCodes.Boolean);
}
else if (columnName.StartsWith(DBToken.LineNo))
{
property.Name = "НомерСтроки";
property.DbName = DBToken.LineNo;
}
else if (columnName.StartsWith(DBToken.RecordKind))
{
property.Name = "ВидДвижения";
property.DbName = DBToken.RecordKind;
}
else if (columnName.StartsWith(DBToken.KeyField))
{
property.Name = "КлючСтроки";
property.DbName = DBToken.KeyField;
}
else if (columnName.EndsWith(DBToken.IDRRef)) // табличные части
{
property.Name = "Ссылка";
property.DbName = DBToken.IDRRef;
property.PropertyTypes.Add(int.MaxValue);
}
else if (columnName == DBToken.EnumOrder)
{
property.Name = "Порядок";
property.DbName = DBToken.EnumOrder;
}
else if (columnName == DBToken.Type) // ПланВидовХарактеристик
{
property.Name = "ТипЗначения"; // ОписаниеТипов - TypeConstraint
property.DbName = DBToken.Type;
}
else if (columnName == DBToken.Splitter)
{
property.Name = "РазделительИтогов";
property.DbName = DBToken.Splitter;
}
else if (columnName == DBToken.NodeTRef)
{
property.Name = "Узел";
property.DbName = DBToken.Node;
property.PropertyTypes.Add(int.MaxValue);
}
else if (columnName == DBToken.NodeRRef)
{
property.Name = "Узел";
property.DbName = DBToken.Node;
}
else if (columnName == DBToken.MessageNo)
{
property.Name = "НомерСообщения";
property.DbName = DBToken.MessageNo;
}
else if (columnName == DBToken.RepetitionFactor)
{
property.Name = "КоэффициентПериодичности";
property.DbName = DBToken.RepetitionFactor;
}
else if (columnName == DBToken.UseTotals)
{
property.Name = "ИспользоватьИтоги";
property.DbName = DBToken.UseTotals;
}
else if (columnName == DBToken.UseSplitter)
{
property.Name = "ИспользоватьРазделительИтогов";
property.DbName = DBToken.UseSplitter;
}
else if (columnName == DBToken.MinPeriod)
{
property.Name = "МинимальныйПериод";
property.DbName = DBToken.MinPeriod;
}
else if (columnName == DBToken.MinCalculatedPeriod)
{
property.Name = "МинимальныйПериодРассчитанныхИтогов";
property.DbName = DBToken.MinCalculatedPeriod;
}
}
private void MatchFieldToProperty(FieldSqlInfo field, MetaProperty property)
{
string columnName = field.COLUMN_NAME.TrimStart('_');
if (columnName.StartsWith(MetadataTokens.IDRRef))
{
property.Name = "Ссылка";
property.Field = MetadataTokens.IDRRef;
property.PropertyType = (int)DataTypes.Object;
}
else if (columnName.StartsWith(MetadataTokens.Version))
{
property.Name = "ВерсияДанных";
property.Field = MetadataTokens.Version;
property.PropertyType = (int)DataTypes.Binary;
}
else if (columnName.StartsWith(MetadataTokens.Marked))
{
property.Name = "ПометкаУдаления";
property.Field = MetadataTokens.Marked;
property.PropertyType = (int)DataTypes.Boolean;
}
else if (columnName.StartsWith(MetadataTokens.PredefinedID))
{
property.Name = "ИмяПредопределённыхДанных";
property.Field = MetadataTokens.PredefinedID;
property.PropertyType = (int)DataTypes.UUID;
}
else if (columnName.StartsWith(MetadataTokens.Code))
{
property.Name = "Код";
property.Field = MetadataTokens.Code;
// TODO: find out field data type: string or numeric
//property.PropertyTypes.Add((int)DataTypes.String);
}
else if (columnName.StartsWith(MetadataTokens.Description))
{
property.Name = "Наименование";
property.Field = MetadataTokens.Description;
property.PropertyType = (int)DataTypes.String;
}
else if (columnName.StartsWith(MetadataTokens.Folder))
{
property.Name = "ЭтоГруппа";
property.Field = MetadataTokens.Folder;
property.PropertyType = (int)DataTypes.Boolean;
}
else if (columnName.StartsWith(MetadataTokens.ParentIDRRef))
{
property.Name = "Родитель";
property.Field = MetadataTokens.ParentIDRRef;
property.PropertyType = (int)DataTypes.Object;
}
else if (columnName.StartsWith(MetadataTokens.OwnerID))
{
property.Name = "Владелец";
property.Field = MetadataTokens.OwnerID;
// TODO: apply object or multiple data type
property.PropertyType = (int)DataTypes.Object;
}
else if (columnName.StartsWith(MetadataTokens.DateTime))
{
property.Name = "Дата";
property.Field = MetadataTokens.DateTime;
property.PropertyType = (int)DataTypes.DateTime;
}
else if (columnName == MetadataTokens.Number)
{
property.Name = "Номер";
property.Field = MetadataTokens.Number;
// TODO: find out field data type: string or numeric
//property.PropertyTypes.Add((int)DataTypes.String);
}
else if (columnName.StartsWith(MetadataTokens.Posted))
{
property.Name = "Проведён";
property.Field = MetadataTokens.Posted;
property.PropertyType = (int)DataTypes.Boolean;
}
else if (columnName == MetadataTokens.NumberPrefix)
{
property.Name = "МоментВремени";
property.Field = MetadataTokens.NumberPrefix;
}
else if (columnName.StartsWith(MetadataTokens.Periodicity))
{
property.Name = "Периодичность";
property.Field = MetadataTokens.Periodicity;
}
else if (columnName.StartsWith(MetadataTokens.Period))
{
property.Name = "Период";
property.Field = MetadataTokens.Period;
}
else if (columnName.StartsWith(MetadataTokens.ActualPeriod))
{
property.Name = "ПериодАктуальности";
property.Field = MetadataTokens.ActualPeriod;
}
else if (columnName.StartsWith(MetadataTokens.Recorder))
{
property.Name = "Регистратор";
property.Field = MetadataTokens.Recorder;
// TODO: apply object or multiple data type
property.PropertyType = (int)DataTypes.Object;
}
else if (columnName.StartsWith(MetadataTokens.Active))
{
property.Name = "Активность";
property.Field = MetadataTokens.Active;
property.PropertyType = (int)DataTypes.Boolean;
}
else if (columnName.StartsWith(MetadataTokens.LineNo))
{
property.Name = "НомерСтроки";
property.Field = MetadataTokens.LineNo;
}
else if (columnName.StartsWith(MetadataTokens.RecordKind))
{
property.Name = "ВидДвижения";
property.Field = MetadataTokens.RecordKind;
}
else if (columnName.StartsWith(MetadataTokens.KeyField))
{
property.Name = "КлючСтроки";
property.Field = MetadataTokens.KeyField;
}
else if (columnName.EndsWith(MetadataTokens.IDRRef)) // табличные части
{
property.Name = "Ссылка";
property.Field = MetadataTokens.IDRRef;
property.PropertyType = (int)DataTypes.Object;
}
else if (columnName == MetadataTokens.EnumOrder)
{
property.Name = "Порядок";
property.Field = MetadataTokens.EnumOrder;
}
else if (columnName == MetadataTokens.Type) // ПланВидовХарактеристик
{
property.Name = "ТипЗначения"; // ОписаниеТипов - TypeConstraint
property.Field = MetadataTokens.Type;
}
else if (columnName == MetadataTokens.Splitter)
{
property.Name = "РазделительИтогов";
property.Field = MetadataTokens.Splitter;
}
else if (columnName == MetadataTokens.NodeTRef)
{
property.Name = "Узел";
property.Field = MetadataTokens.Node;
property.PropertyType = (int)DataTypes.Numeric;
}
else if (columnName == MetadataTokens.NodeRRef)
{
property.Name = "Узел";
property.Field = MetadataTokens.Node;
property.PropertyType = (int)DataTypes.Object;
}
else if (columnName == MetadataTokens.MessageNo)
{
property.Name = "НомерСообщения";
property.Field = MetadataTokens.MessageNo;
}
else if (columnName == MetadataTokens.RepetitionFactor)
{
property.Name = "КоэффициентПериодичности";
property.Field = MetadataTokens.RepetitionFactor;
}
else if (columnName == MetadataTokens.UseTotals)
{
property.Name = "ИспользоватьИтоги";
property.Field = MetadataTokens.UseTotals;
}
else if (columnName == MetadataTokens.UseSplitter)
{
property.Name = "ИспользоватьРазделительИтогов";
property.Field = MetadataTokens.UseSplitter;
}
else if (columnName == MetadataTokens.MinPeriod)
{
property.Name = "МинимальныйПериод";
property.Field = MetadataTokens.MinPeriod;
}
else if (columnName == MetadataTokens.MinCalculatedPeriod)
{
property.Name = "МинимальныйПериодРассчитанныхИтогов";
property.Field = MetadataTokens.MinCalculatedPeriod;
}
else if (columnName == MetadataTokens.Kind)
{
property.Name = "ТипСчёта";
property.Field = MetadataTokens.Kind;
property.PropertyType = (int)DataTypes.Numeric;
}
else if (columnName == MetadataTokens.OrderField)
{
property.Name = "Порядок";
property.Field = MetadataTokens.OrderField;
property.PropertyType = (int)DataTypes.String;
}
else if (columnName == MetadataTokens.OffBalance)
{
property.Name = "Забалансовый";
property.Field = MetadataTokens.OffBalance;
property.PropertyType = (int)DataTypes.Boolean;
}
else if (columnName == MetadataTokens.AccountDtRRef)
{
property.Name = "СчётДебет";
property.Field = MetadataTokens.AccountDtRRef;
property.PropertyType = (int)DataTypes.Object;
}
else if (columnName == MetadataTokens.AccountCtRRef)
{
property.Name = "СчётКредит";
property.Field = MetadataTokens.AccountCtRRef;
property.PropertyType = (int)DataTypes.Object;
}
else if (columnName == MetadataTokens.EDHashDt)
{
property.Name = "ХешДебет";
property.Field = MetadataTokens.EDHashDt;
property.PropertyType = (int)DataTypes.Numeric;
}
else if (columnName == MetadataTokens.EDHashCt)
{
property.Name = "ХэшКредит";
property.Field = MetadataTokens.EDHashCt;
property.PropertyType = (int)DataTypes.Numeric;
}
else if (columnName == MetadataTokens.SentNo)
{
property.Name = "НомерОтправленного";
property.Field = MetadataTokens.SentNo;
property.PropertyType = (int)DataTypes.Numeric;
}
else if (columnName == MetadataTokens.ReceivedNo)
{
property.Name = "НомерПринятого";
property.Field = MetadataTokens.ReceivedNo;
property.PropertyType = (int)DataTypes.Numeric;
}
}