public static Database ImportViews(ILogger <SqlServerDatabaseFactory> logger, string connectionString, params string[] views)
{
var databaseFactory = new SqlServerDatabaseFactory
{
ConnectionString = connectionString,
ImportSettings = new DatabaseImportSettings
{
ImportTables = false
}
};
var database = new Database();
using (var connection = databaseFactory.GetConnection())
{
connection.Open();
database.Name = connection.Database;
if (views.Length == 0)
{
database.DbObjects.AddRange(databaseFactory.GetDbObjects(connection).ToList());
}
else
{
database.DbObjects.AddRange(databaseFactory.GetDbObjects(connection).Where(item => views.Contains(item.FullName)).ToList());
}
database.Views.AddRange(databaseFactory.GetViews(connection, database.GetViews()).ToList());
}
return(database);
}
/// <summary>
/// Imports an existing database from SQL Server instance
/// </summary>
/// <param name="logger">Instance for <see cref="Logger"/> class</param>
/// <param name="connectionString">Connection string</param>
/// <param name="views">View names to include in import action</param>
/// <returns>An instance of <see cref="Database"/> class that represents an existing database in SQL Server instance</returns>
public static async Task <Database> ImportViewsAsync(ILogger <SqlServerDatabaseFactory> logger, string connectionString, params string[] views)
{
var databaseFactory = new SqlServerDatabaseFactory(logger)
{
DatabaseImportSettings = new DatabaseImportSettings
{
ConnectionString = connectionString,
ImportTables = false
}
};
using (var connection = databaseFactory.GetConnection())
{
await connection.OpenAsync();
var database = SqlServerDatabase.CreateWithDefaults(connection.Database);
database.ServerName = connection.DataSource;
if (views.Length == 0)
{
database.DbObjects.AddRange(await databaseFactory.GetDbObjectsAsync(connection));
}
else
{
database.DbObjects.AddRange((await databaseFactory.GetDbObjectsAsync(connection)).Where(item => views.Contains(item.FullName)).ToList());
}
database.Views.AddRange((await databaseFactory.GetViewsAsync(connection, database.GetViews())).ToList());
return(database);
}
}
/// <summary>
/// Adds if not exists or updates if exists an extended property for database object
/// </summary>
/// <param name="databaseFactory">Instance of <see cref="SqlServerDatabaseFactory"/> class</param>
/// <param name="view">Instance of <see cref="View"/> class</param>
/// <param name="column">Instance of <see cref="Column"/> class</param>
/// <param name="name">Extended property name</param>
/// <param name="value">Extended property value</param>
public static void AddOrUpdateExtendedProperty(this SqlServerDatabaseFactory databaseFactory, IView view, Column column, string name, string value)
{
var model = new ExtendedProperty(name, "schema", view.Schema, "view", view.Name, "column", column.Name)
{
Value = value
};
using (var connection = databaseFactory.GetConnection())
{
connection.Open();
var repository = new ExtendedPropertyRepository(connection);
var extendedProperty = repository.GetExtendedProperties(model).FirstOrDefault();
if (extendedProperty == null)
{
repository.AddExtendedProperty(model);
}
else
{
repository.UpdateExtendedProperty(model);
}
column.Description = value;
}
}
/// <summary>
/// Drops an extended property for database object
/// </summary>
/// <param name="databaseFactory">Instance of <see cref="SqlServerDatabaseFactory"/> class</param>
/// <param name="view">Instance of <see cref="View"/> class</param>
/// <param name="column">Instance of <see cref="Column"/> class</param>
/// <param name="name">Extended property name</param>
public static void DropExtendedProperty(this SqlServerDatabaseFactory databaseFactory, IView view, Column column, string name)
{
using (var connection = databaseFactory.GetConnection())
{
connection.Open();
new ExtendedPropertyRepository(connection).DropExtendedProperty(new ExtendedProperty(name, "schema", view.Schema, "view", view.Name, "column", column.Name));
}
}
/// <summary>
/// Drops an extended property for database object
/// </summary>
/// <param name="databaseFactory">Instance of <see cref="SqlServerDatabaseFactory"/> class</param>
/// <param name="table">Instance of <see cref="Table"/> class</param>
/// <param name="name">Extended property name</param>
public static void DropExtendedProperty(this SqlServerDatabaseFactory databaseFactory, ITable table, string name)
{
using (var connection = databaseFactory.GetConnection())
{
connection.Open();
new ExtendedPropertyRepository(connection).DropExtendedProperty(new ExtendedProperty(name, "schema", table.Schema, "table", table.Name));
}
}
/// <summary>
/// Drops an extended property for database object
/// </summary>
/// <param name="databaseFactory">Instance of <see cref="SqlServerDatabaseFactory"/> class</param>
/// <param name="database"></param>
/// <param name="name">Extended property name</param>
public static void DropExtendedProperty(this SqlServerDatabaseFactory databaseFactory, Database database, string name)
{
using (var connection = databaseFactory.GetConnection())
{
connection.Open();
new ExtendedPropertyRepository(connection).DropExtendedProperty(new ExtendedProperty(name));
}
}
/// <summary>
/// Updates an extended property for database object
/// </summary>
/// <param name="databaseFactory">Instance of <see cref="SqlServerDatabaseFactory"/> class</param>
/// <param name="database"></param>
/// <param name="name">Extended property name</param>
/// <param name="value">Extended property value</param>
public static void UpdateExtendedProperty(this SqlServerDatabaseFactory databaseFactory, Database database, string name, string value)
{
using (var connection = databaseFactory.GetConnection())
{
connection.Open();
new ExtendedPropertyRepository(connection).UpdateExtendedProperty(new ExtendedProperty {
Name = name, Value = value
});
database.Description = value;
}
}
/// <summary>
/// Adds an extended property for database object
/// </summary>
/// <param name="databaseFactory">Instance of <see cref="SqlServerDatabaseFactory"/> class</param>
/// <param name="view">Instance of <see cref="View"/> class</param>
/// <param name="name">Extended property name</param>
/// <param name="value">Extended property value</param>
public static void AddExtendedProperty(this SqlServerDatabaseFactory databaseFactory, IView view, string name, string value)
{
using (var connection = databaseFactory.GetConnection())
{
connection.Open();
new ExtendedPropertyRepository(connection).AddExtendedProperty(new ExtendedProperty(name, "schema", view.Schema, "view", view.Name)
{
Value = value
});
view.Description = value;
}
}
/// <summary>
/// Adds an extended property for database object
/// </summary>
/// <param name="databaseFactory">Instance of <see cref="SqlServerDatabaseFactory"/> class</param>
/// <param name="table">Instance of <see cref="Table"/> class</param>
/// <param name="column">Instance of <see cref="Column"/> class</param>
/// <param name="name">Extended property name</param>
/// <param name="value">Extended property value</param>
public static void AddExtendedProperty(this SqlServerDatabaseFactory databaseFactory, ITable table, Column column, string name, string value)
{
using (var connection = databaseFactory.GetConnection())
{
connection.Open();
new ExtendedPropertyRepository(connection).AddExtendedProperty(new ExtendedProperty(name, "schema", table.Schema, "table", table.Name, "column", column.Name)
{
Value = value
});
column.Description = value;
}
}
/// <summary>
/// Drops an extended property for database object if exists
/// </summary>
/// <param name="databaseFactory">Instance of <see cref="SqlServerDatabaseFactory"/> class</param>
/// <param name="view">Instance of <see cref="View"/> class</param>
/// <param name="column">Instance of <see cref="Column"/> class</param>
/// <param name="name">Extended property name</param>
public static void DropExtendedPropertyIfExists(this SqlServerDatabaseFactory databaseFactory, IView view, Column column, string name)
{
using (var connection = databaseFactory.GetConnection())
{
connection.Open();
var extendedProperty = connection.GetExtendedProperties(view, name).FirstOrDefault();
if (extendedProperty != null)
{
new ExtendedPropertyRepository(connection).DropExtendedProperty(new ExtendedProperty(name, "schema", view.Schema, "view", view.Name, "column", column.Name));
}
}
}
/// <summary>
/// Drops an extended property for database object if exists
/// </summary>
/// <param name="databaseFactory">Instance of <see cref="SqlServerDatabaseFactory"/> class</param>
/// <param name="table">Instance of <see cref="Table"/> class</param>
/// <param name="name">Extended property name</param>
public static void DropExtendedPropertyIfExists(this SqlServerDatabaseFactory databaseFactory, ITable table, string name)
{
using (var connection = databaseFactory.GetConnection())
{
connection.Open();
var extendedProperty = connection.GetExtendedProperties(table, name).FirstOrDefault();
if (extendedProperty != null)
{
new ExtendedPropertyRepository(connection).DropExtendedProperty(new ExtendedProperty(name, "schema", table.Schema, "table", table.Name));
}
}
}
public static void DropMsDescription(this SqlServerDatabaseFactory databaseFactory, ITable table, Column column)
{
var repository = new ExtendPropertyRepository();
var name = "MS_Description";
var level0type = "schema";
var level0name = table.Schema;
var level1type = "table";
var level1name = table.Name;
var level2type = "column";
var level2name = column.Name;
using (var connection = databaseFactory.GetConnection())
{
connection.Open();
repository.DropExtendedProperty(connection, name, level0type, level0name, level1type, level1name, level2type, level2name);
}
}
public static void UpdateMsDescription(this SqlServerDatabaseFactory databaseFactory, ITable table, string description)
{
var repository = new ExtendPropertyRepository();
var name = "MS_Description";
var level0type = "schema";
var level0name = table.Schema;
var level1type = "table";
var level1name = table.Name;
var level2type = string.Empty;
var level2name = string.Empty;
using (var connection = databaseFactory.GetConnection())
{
connection.Open();
repository.UpdateExtendedProperty(connection, name, description, level0type, level0name, level1type, level1name, level2type, level2name);
table.Description = description;
}
}
/// <summary>
/// Imports an existing database from SQL Server instance
/// </summary>
/// <param name="logger">Instance for <see cref="Logger"/> class</param>
/// <param name="connectionString">Connection string</param>
/// <param name="names">Table or view names to include in import action</param>
/// <returns>An instance of <see cref="Database"/> class that represents an existing database in SQL Server instance</returns>
public static Database ImportTablesAndViews(ILogger <SqlServerDatabaseFactory> logger, string connectionString, params string[] names)
{
var databaseFactory = new SqlServerDatabaseFactory(logger)
{
DatabaseImportSettings = new DatabaseImportSettings
{
ConnectionString = connectionString
}
};
using (var connection = databaseFactory.GetConnection())
{
connection.Open();
var database = new Database
{
DataSource = connection.DataSource,
Catalog = connection.Database,
Name = connection.Database
};
if (names.Length == 0)
{
database.DbObjects.AddRange(databaseFactory.GetDbObjects(connection).ToList());
}
else
{
database.DbObjects.AddRange(databaseFactory.GetDbObjects(connection).Where(item => names.Contains(item.FullName)).ToList());
}
database.Tables.AddRange(databaseFactory.GetTables(connection, database.GetTables()).ToList());
database.Views.AddRange(databaseFactory.GetViews(connection, database.GetViews()).ToList());
return(database);
}
}
