private void SetChildrens(DbObject target, DbEntityEnum childrenType)
{
if (!(Connection.GetConnection() is SqlConnection))
{
throw new InvalidCastException();
}
using (SqlConnection connection = (SqlConnection)Connection.GetConnection())
{
connection.Open();
List <DbObject> childs = new List <DbObject>();
string query = _provider.GetLoadNameScript(target.Type, childrenType);
SqlCommand command = new SqlCommand(query, connection);
foreach (var param in _provider.GetChildrenLoadParameters(target, childrenType))
{
command.Parameters.Add(param);
}
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
DbObject child = (DbEntityFactory.ObjectCreator.Create(reader, childrenType));
if (!target.AddChild(child))
{
throw new ArgumentException();
}
childs.Add(child);
}
}
foreach (var child in childs)
{
if (child.CanHaveDefinition)
{
query = _provider.GetDefinitionScript();
SqlCommand descCommand = new SqlCommand(query, connection);
foreach (var param in _provider.GetDefinitionParamteters(child))
{
descCommand.Parameters.Add(param);
}
using (SqlDataReader descReader = descCommand.ExecuteReader())
{
StringBuilder defText = new StringBuilder();
while (descReader.Read())
{
defText.Append(descReader.GetString(0));
}
child.Definition = defText.ToString();
}
}
}
}
}
public async Task LoadModel(MetadataViewModelBase obj, DbEntityEnum type)
{
Root.DbLoader.Connection.InitialCatalog = obj.Model.DataBaseName;
try
{
if (Root.IsConnected)
{
if (type == DbEntityEnum.All)
{
await DbLoader.LoadChildren(obj.Model);
}
else
{
await DbLoader.LoadChildren(obj.Model, type);
}
}
}
catch (DbException d)
{
MessageBox.Show(d.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
if (Root.IsDefaultDatabase)
{
Root.DbLoader.Connection.InitialCatalog = string.Empty;
}
}
}
public void DeleteChildrens(DbEntityEnum type)
{
if (_childrenMap.ContainsKey(type))
{
_childrenMap.Remove(type);
}
}
public override Task LoadChildren(DbObject obj, DbEntityEnum childType)
{
if (!Hierarchy.HierarchyObject.GetChildTypes(obj.Type).Contains(childType))
{
throw new ArgumentException();
}
return(Task.Run(() => SetChildrens(obj, childType)));
}
private string GetBaseName(DbEntityEnum type)
{
if (Type == type)
{
return(this.Name);
}
foreach (var chunc in FullName)
{
if (chunc.Type == type)
{
return(chunc.Name);
}
}
return(null);
}
public CategoryViewModel(MetadataViewModelBase parent, DbEntityEnum type) : base(parent, true)
{
Type = type;
Model = parent.Model;
}
public abstract string GetLoadNameScript(DbEntityEnum parentType, DbEntityEnum childType);
public bool IsPossibleChilds(DbEntityEnum type)
{
return(GetChildTypes(type).Count() != 0);
}
public DbObject Create(DbDataReader reader, DbEntityEnum type)
{
return(_objectCreator[type](reader));
}
public object Convert(object value, Type targetType, object parameter, CultureInfo culture)
{
targetValue = (DbEntityEnum)value;
return(targetValue.HasFlag((DbEntityEnum)parameter));
}
public Chunk(string name, DbEntityEnum type)
{
Name = name;
Type = type;
}
public abstract Task LoadChildren(DbObject obj, DbEntityEnum childType);
private IEnumerable <MetadataViewModelBase> FindMatchesNode(MetadataViewModelBase node, string text, DbEntityEnum mask)
{
if (node.Name.Contains(text) && !(node is CategoryViewModel) && mask.HasFlag(node.Type))
{
yield return(node);
}
if (node.Children.Count != 0 && !node.HasFakeChild)
{
foreach (var treeViewItemViewModelBase in node.Children)
{
var treeViewItem = (MetadataViewModelBase)treeViewItemViewModelBase;
foreach (MetadataViewModelBase res in FindMatchesNode(treeViewItem, text, mask))
{
yield return(res);
}
}
}
}
public TreeWindowViewModel()
{
ItemProperties = new ObservableCollection <KeyValuePair <string, object> >();
_searchMask = DbEntityEnum.All;
}
public override IEnumerable <IDbDataParameter> GetChildrenLoadParameters(DbObject obj, DbEntityEnum childType)
{
List <IDbDataParameter> parameters = new List <IDbDataParameter>();
switch (childType)
{
case DbEntityEnum.Table:
parameters.Add(new SqlParameter(Constants.TypeParameter, "BASE TABLE"));
parameters.Add(new SqlParameter(Constants.SchemaNameParameter, obj.SchemaName));
break;
case DbEntityEnum.View:
case DbEntityEnum.Function:
case DbEntityEnum.Procedure:
parameters.Add(new SqlParameter(Constants.TypeParameter, childType.ToString().ToUpper()));
parameters.Add(new SqlParameter(Constants.SchemaNameParameter, obj.SchemaName));
break;
case DbEntityEnum.Column:
case DbEntityEnum.Constraint:
case DbEntityEnum.Key:
parameters.Add(new SqlParameter(Constants.SchemaNameParameter, obj.SchemaName));
parameters.Add(new SqlParameter(Constants.NameParameter, obj.Name));
break;
case DbEntityEnum.Trigger:
case DbEntityEnum.Parameter:
case DbEntityEnum.Index:
parameters.Add(new SqlParameter(Constants.FullParentNameParameter, obj.FullName.ToString()));
break;
}
return(parameters);
}
public override string GetLoadNameScript(DbEntityEnum parentType, DbEntityEnum childType)
{
switch (childType)
{
case DbEntityEnum.Schema:
return($"SELECT SCHEMA_NAME AS '{Constants.NameProperty}' FROM INFORMATION_SCHEMA.SCHEMATA");
case DbEntityEnum.Table:
case DbEntityEnum.View:
return($"SELECT TABLE_NAME AS '{Constants.NameProperty}' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = {Constants.TypeParameter} AND TABLE_SCHEMA = {Constants.SchemaNameParameter}");
case DbEntityEnum.Function:
case DbEntityEnum.Procedure:
return($"SELECT ROUTINE_NAME AS '{Constants.NameProperty}' FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = {Constants.TypeParameter} AND ROUTINE_SCHEMA = {Constants.SchemaNameParameter}");
case DbEntityEnum.Constraint:
return
($"SELECT CONSTRAINT_NAME AS {Constants.NameProperty} FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE ='CHECK' " +
$"AND TABLE_SCHEMA = {Constants.SchemaNameParameter} AND TABLE_NAME = {Constants.NameParameter}");
case DbEntityEnum.Column:
switch (parentType)
{
case DbEntityEnum.Table:
return($"SELECT col.name AS'{Constants.NameProperty}', " +
$"TYPE_NAME(col.system_type_id) AS '{Constants.TypeNameProperty}', " +
$"col.max_length AS '{Constants.MaxLengthProperty}', " +
$"col.precision AS '{Constants.PrecisionProperty}', " +
$"col.scale AS '{Constants.ScaleProperty}' " +
$"FROM sys.columns AS col " +
$"JOIN INFORMATION_SCHEMA.COLUMNS AS inf ON col.name=inf.COLUMN_NAME AND col.object_id=OBJECT_ID(inf.TABLE_CATALOG+'.'+inf.TABLE_SCHEMA+'.'+inf.TABLE_NAME) where inf.TABLE_NAME={Constants.NameParameter} AND inf.TABLE_SCHEMA={Constants.SchemaNameParameter}");
case DbEntityEnum.View:
return($"SELECT col.name AS'{Constants.NameProperty}', " +
$"TYPE_NAME(col.system_type_id) AS '{Constants.TypeNameProperty}', " +
$"col.max_length AS '{Constants.MaxLengthProperty}', " +
$"col.precision AS '{Constants.PrecisionProperty}', " +
$"col.scale AS '{Constants.ScaleProperty}' " +
$"FROM sys.columns AS col " +
$"JOIN INFORMATION_SCHEMA.VIEW_COLUMN_USAGE AS inf ON col.name=inf.COLUMN_NAME AND col.object_id=OBJECT_ID(inf.TABLE_CATALOG+'.'+inf.TABLE_SCHEMA+'.'+inf.TABLE_NAME) WHERE inf.VIEW_NAME = {Constants.NameParameter} AND inf.VIEW_SCHEMA={Constants.SchemaNameParameter}");
default:
throw new ArgumentException();
}
case DbEntityEnum.Trigger:
return($"SELECT name AS '{Constants.NameProperty}' FROM sys.triggers WHERE parent_id = COALESCE (OBJECT_ID({Constants.FullParentNameParameter}),0)");
case DbEntityEnum.Parameter:
return($"SELECT name AS'{Constants.NameProperty}', " +
$"TYPE_NAME(system_type_id) AS '{Constants.TypeNameProperty}', " +
$"max_length AS '{Constants.MaxLengthProperty}', " +
$"precision AS '{Constants.PrecisionProperty}', " +
$"scale AS '{Constants.ScaleProperty}' " +
$"FROM sys.parameters WHERE object_id = OBJECT_ID({Constants.FullParentNameParameter}) AND parameter_id != 0");
case DbEntityEnum.Key:
return($"SELECT DISTINCT CONSTRAINT_NAME AS '{Constants.NameProperty}' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = {Constants.SchemaNameParameter} AND TABLE_NAME = {Constants.NameParameter}");
case DbEntityEnum.Index:
return
($"SELECT name AS '{Constants.NameProperty}' FROM sys.indexes WHERE is_hypothetical = 0 AND index_id != 0 AND object_id = OBJECT_ID({Constants.FullParentNameParameter})");
case DbEntityEnum.Database:
return($"SELECT name AS '{Constants.NameProperty}' FROM sys.databases");
default:
throw new ArgumentException();
}
}
public object ConvertBack(object value, Type targetType, object parameter, CultureInfo culture)
{
return(targetValue ^= (DbEntityEnum)parameter);
}
public abstract IEnumerable <IDbDataParameter> GetChildrenLoadParameters(DbObject obj, DbEntityEnum childType);
protected override bool CanBeChild(DbObject obj)
{
DbEntityEnum type = obj.Type;
return(type == DbEntityEnum.Column || type == DbEntityEnum.Trigger || type == DbEntityEnum.Index);
}
public IEnumerable <DbEntityEnum> GetChildTypes(DbEntityEnum type)
{
return(ChildresTypes.ContainsKey(type) ? ChildresTypes[type] : Empty);
}
