/// <summary>
/// Create foreign key constraint
/// </summary>
/// <param name="model">Relationship model</param>
public void AddRelationship(RelationshipModel model)
{
using (IMsSqlMapper mapper = new MsSqlMapper())
{
mapper.CreateForeignKey(model.Destination.Title, model.Source.Title, model.Attributes, model.Name, model.UpdateAction, model.DeleteAction);
}
}
/// <summary>
/// Rename table in DB
/// </summary>
/// <param name="oldName">Old table name</param>
/// <param name="newName">New table name</param>
public void RenameTable(string oldName, string newName)
{
using (IMapper mapper = new MsSqlMapper())
{
mapper.RenameTable(oldName, newName);
}
}
/// <summary>
/// Drop column in table
/// </summary>
/// <param name="table">Table name</param>
/// <param name="column">Column name</param>
public void RemoveColumn(string table, string column)
{
using (IMapper mapper = new MsSqlMapper())
{
mapper.DropColumn(table, column);
}
}
/// <summary>
/// Alter columnd to table in DB
/// </summary>
/// <param name="table">Table name</param>
/// <param name="model">Column model</param>
public void UpdateColumn(string table, TableRowModel model)
{
using (IMapper mapper = new MsSqlMapper())
{
mapper.AlterColumn(table, model);
}
}
/// <summary>
/// Drop table from DB
/// </summary>
/// <param name="table">Table name</param>
public void RemoveTable(TableModel table)
{
using (IMapper mapper = new MsSqlMapper())
{
mapper.DropTable(table.Title);
}
}
/// <summary>
/// Add columnd to table in DB
/// </summary>
/// <param name="table">Table name</param>
/// <param name="model">Column model</param>
public void AddColumn(string table, TableRowModel model)
{
using (IMapper mapper = new MsSqlMapper())
{
mapper.AddNewColumn(table, model);
}
}
/// <summary>
/// Drop selected DB - MS Sql Server only
/// </summary>
/// <param name="sender"></param>
/// <param name="dbName">DB for drop</param>
private async void DatabaseConnectionSidebarOnDropMsSqlDatabase(object sender, string dbName)
{
if (dbName.Equals(SessionProvider.Instance.Database))
{
await this.ShowMessageAsync("Drop database", $"Database {dbName} is currently in use");
return;
}
var progress = await this.ShowProgressAsync($"Drop database {dbName}", "Please wait...");
progress.SetIndeterminate();
try
{
using (IMsSqlMapper mapper = new MsSqlMapper())
{
await Task.Factory.StartNew(() => mapper.DropDatabase(dbName));
await DatabaseConnectionSidebar.LoadMsSqlData(true);
await progress.CloseAsync();
await this.ShowMessageAsync("Drop database", $"Database {dbName} dropped successfully");
}
}
catch (SqlException ex)
{
await progress.CloseAsync();
await this.ShowMessageAsync("Drop database", ex.Message);
}
}
/// <summary>
/// Create new table in DB
/// </summary>
/// <param name="name">Table name</param>
public void CreateTable(string name)
{
using (IMapper mapper = new MsSqlMapper())
{
mapper.CreateTable(name);
}
}
/// <summary>
/// Get table info with all collumns from DB
/// </summary>
/// <param name="id">Object ID</param>
/// <param name="name">Table name</param>
/// <returns>Table model</returns>
public TableModel ReadTableDetails(string id, string name)
{
using (IMapper mapper = new MsSqlMapper())
{
return(mapper.SelectTableDetails(id, name));
}
}
/// <summary>
/// List name of all foreign key coninstraints
/// </summary>
/// <returns>Names of all foreign key coninstraints</returns>
public IEnumerable <string> ListAllForeignKeys()
{
using (IMsSqlMapper mapper = new MsSqlMapper())
{
return(mapper.ListAllForeignKeys());
}
}
/// <summary>
/// Delete diagram from DB
/// </summary>
/// <param name="name">Diagram name</param>
/// <returns>One if successful, zero if not</returns>
public int DeleteDiagram(string name)
{
using (IMapper mapper = new MsSqlMapper())
{
return(mapper.DeleteDiagram(name));
}
}
/// <summary>
/// Execute raw query
/// </summary>
/// <param name="sql">SQL Command text</param>
/// <returns>Dataset with results</returns>
public DataSet ExecuteRawQuery(string sql)
{
using (IMapper mapper = new MsSqlMapper())
{
return(mapper.ExecuteRawQuery(sql));
}
}
/// <summary>
/// Rename column in table
/// </summary>
/// <param name="table">Table name</param>
/// <param name="oldName">Old name</param>
/// <param name="newName">New name</param>
public void RenameColumn(string table, string oldName, string newName)
{
using (IMapper mapper = new MsSqlMapper())
{
mapper.RenameColumn(table, oldName, newName);
}
}
/// <summary>
/// List tables in DB
/// </summary>
/// <returns>Collection of tables with ID and name</returns>
public IEnumerable <TableModel> ListTables()
{
using (IMapper mapper = new MsSqlMapper())
{
return(mapper.ListTables());
}
}
/// <summary>
/// Drop foreign key constraint
/// </summary>
/// <param name="model">Relationship model</param>
public void RemoveRelationship(RelationshipModel model)
{
using (IMapper mapper = new MsSqlMapper())
{
mapper.DropForeignKey(model.Destination.Title, model.Name);
}
}
/// <summary>
/// List foreign key coninstraints for table
/// </summary>
/// <param name="table">Name of table</param>
/// <param name="tables">Tables in designer</param>
/// <returns>Collection of FK constraints</returns>
public IEnumerable <RelationshipModel> ReadRelationshipModels(string table, IEnumerable <TableModel> tables)
{
using (MsSqlMapper mapper = new MsSqlMapper())
{
IEnumerable <ForeignKeyDto> keyDtos = mapper.ListForeignKeys(table);
var grouped = keyDtos.Where(t =>
{
if (t.PrimaryKeyTable.Equals(table, StringComparison.CurrentCultureIgnoreCase))
{
if (tables.Any(s => s.Title.Equals(t.ForeignKeyTable, StringComparison.CurrentCultureIgnoreCase)))
{
return(true);
}
}
if (t.ForeignKeyTable.Equals(table, StringComparison.CurrentCultureIgnoreCase))
{
if (tables.Any(s => s.Title.Equals(t.PrimaryKeyTable, StringComparison.CurrentCultureIgnoreCase)))
{
return(true);
}
}
return(false);
}).GroupBy(t => t.Name);
var res = new List <RelationshipModel>();
foreach (IGrouping <string, ForeignKeyDto> dtos in grouped)
{
var model = new RelationshipModel();
model.Name = dtos.Key;
var first = dtos.FirstOrDefault();
model.Source = tables.FirstOrDefault(t => t.Title.Equals(first.PrimaryKeyTable));
model.Destination = tables.FirstOrDefault(t => t.Title.Equals(first.ForeignKeyTable));
foreach (ForeignKeyDto keyDto in dtos)
{
RowModelPair pair = new RowModelPair();
pair.Source = model.Source.Attributes.FirstOrDefault(t => t.Name.Equals(keyDto.PrimaryKeyCollumn));
pair.Destination = model.Destination.Attributes.FirstOrDefault(t => t.Name.Equals(keyDto.ForeignKeyCollumn));
model.Attributes.Add(pair);
}
model.Optionality = model.Attributes.All(t => t.Destination.AllowNull) ? Optionality.Optional : Optionality.Mandatory;
model.DeleteAction = first.DeleteAction;
model.UpdateAction = first.UpdateAction;
model.Id = first.Id;
model.LastModified = first.LastModified;
res.Add(model);
}
return(res);
}
}
/// <summary>
/// Save diagram to DB
/// </summary>
/// <param name="name">Diagram name</param>
/// <param name="data">XML data</param>
/// <returns>One if successful, zero if not</returns>
public int SaveDiagram(string name, XDocument data)
{
using (IMapper mapper = new MsSqlMapper())
{
IEnumerable <DiagramModel> diagrams = SelectDiagrams();
mapper.CreateDiagramTable();
return(diagrams.Any(t => t.Name.Equals(name)) ? mapper.UpdateDiagram(name, data) : mapper.InsertDiagram(name, data));
}
}
/// <summary>
/// Select existing diagrams
/// </summary>
/// <returns>Collections of diagrams</returns>
public IEnumerable <DiagramModel> SelectDiagrams()
{
using (IMapper mapper = new MsSqlMapper())
{
try
{
IEnumerable <DiagramModel> diagrams = mapper.SelectDiagrams();
return(diagrams);
}
catch (SqlException)
{
return(new List <DiagramModel>());
}
}
}
/// <summary>
/// Update PK constraint
/// </summary>
/// <param name="table">Table model</param>
public void UpdatePrimaryKeyConstraint(TableModel table)
{
using (IMapper mapper = new MsSqlMapper())
{
string constraintName = table.Attributes.FirstOrDefault(t => t.PrimaryKeyConstraintName != null)?.PrimaryKeyConstraintName;
string[] columns = table.Attributes.Where(t => t.PrimaryKey).Select(s => s.Name).ToArray();
if (constraintName != null)
{
mapper.DropPrimaryKey(table.Title, constraintName);
}
if (columns.Length != 0)
{
mapper.CreatePrimaryKey(table.Title, columns);
}
}
}
/// <summary>
/// Create new DB - MS Sql Server only
/// </summary>
/// <param name="sender"></param>
/// <param name="eventArgs"></param>
private async void DatabaseConnectionSidebarOnCreateMsSqlDatabase(object sender, System.EventArgs eventArgs)
{
string name = await this.ShowInputAsync("Create database", "Database name");
if (name == null || name.Equals(string.Empty))
{
return;
}
try
{
using (IMsSqlMapper mapper = new MsSqlMapper())
{
mapper.CreateDatabase(name);
await DatabaseConnectionSidebar.LoadMsSqlData(true);
}
}
catch (SqlException exc)
{
await this.ShowMessageAsync("Create database", exc.Message);
}
}
/// <summary>
/// Load Ms Sql tree data
/// </summary>
/// <param name="loadPrev">Select previous DB</param>
public async Task LoadMsSqlData(bool loadPrev = false)
{
int selected = 0;
string name = string.Empty;
if (loadPrev)
{
DatabaseInfo info = DatabaseInfos.FirstOrDefault(t => t.Name.Equals(SessionProvider.Instance.Database));
if (info != null)
{
name = info.Name;
}
}
await Task.Run(() =>
{
var pomInfos = new List <DatabaseInfo>();
DatabaseInfos = new List <DatabaseInfo>();
using (MsSqlMapper mapper = new MsSqlMapper())
{
pomInfos = mapper.ListDatabases().ToList();
}
foreach (DatabaseInfo info in pomInfos)
{
try
{
using (MsSqlMapper mapper = new MsSqlMapper(SessionProvider.Instance.GetConnectionStringForMsSqlDatabase(info.Name)))
{
try
{
mapper.ListTables().ToList().ForEach(t => info.Tables.Add(t));
}
catch (SqlException)
{
Debug.WriteLine("Can't read table");
continue;
}
try
{
mapper.SelectDiagrams().ToList().ForEach(t => info.Diagrams.Add(t));
}
catch (SqlException)
{
Debug.WriteLine("Can't read diagrams");
}
DatabaseInfos.Add(info);
}
}
catch (SqlException e)
{
Debug.WriteLine(e.Message);
}
}
});
if (loadPrev)
{
int indexOf = DatabaseInfos.IndexOf(DatabaseInfos.FirstOrDefault(t => t.Name.Equals(name)));
selected = indexOf > 0 ? indexOf : 0;
}
if (DatabaseInfos.Any())
{
if (SessionProvider.Instance.Database.Equals(string.Empty))
{
SessionProvider.Instance.Database = DatabaseInfos[selected].Name;
}
MsSqlDatabaseComboBox.ItemsSource = DatabaseInfos;
MsSqlDatabaseComboBox.DisplayMemberPath = "Name";
MsSqlDatabaseComboBox.SelectedIndex = selected;
}
LoadMsSqlTreeViewData();
MsSqlServerGrid.Visibility = Visibility.Visible;
}
