/// <summary>
/// Adds a column in table function from row dictionary
/// </summary>
/// <param name="tableFunction">Instance of <see cref="TableFunction"/> class</param>
/// <param name="dictionary">Dictionary from data reader</param>
protected virtual void AddColumn(TableFunction tableFunction, IDictionary<string, object> dictionary)
{
var column = SqlServerDatabaseFactoryHelper.GetColumn(dictionary);
if (!DatabaseImportSettings.ExclusionTypes.Contains(column.Type))
tableFunction.Columns.Add(column);
}
/// <summary>
/// Gets views from database connection
/// </summary>
/// <param name="connection">Instance of <see cref="DbConnection"/> class</param>
/// <param name="views">Sequence of views</param>
/// <returns>A sequence of <see cref="View"/> that represents existing views in database</returns>
protected virtual IEnumerable<View> GetViews(DbConnection connection, IEnumerable<DbObject> views)
{
foreach (var dbObject in views)
{
using (var command = connection.CreateCommand())
{
var view = new View
{
Schema = dbObject.Schema,
Name = dbObject.Name
};
command.Connection = connection;
command.CommandText = string.Format("sp_help '{0}'", dbObject.FullName);
var queryResults = new List<QueryResult>();
using (var dataReader = command.ExecuteReader())
{
while (dataReader.NextResult())
{
var queryResult = new QueryResult();
while (dataReader.Read())
{
var names = SqlServerDatabaseFactoryHelper.GetNames(dataReader).ToList();
var row = new Dictionary<string, object>();
for (var i = 0; i < names.Count; i++)
row.Add(names[i], dataReader.GetValue(i));
queryResult.Items.Add(row);
}
queryResults.Add(queryResult);
}
foreach (var result in queryResults)
{
foreach (var item in result.Items)
{
if (item.ContainsKey("Column_name"))
AddColumn(view, item);
else if (item.ContainsKey("Identity"))
SetIdentity(view, item);
else if (item.ContainsKey("RowGuidCol"))
SetRowGuidCol(view, item);
else if (item.ContainsKey("index_name"))
AddIndexToView(view, item);
}
}
yield return view;
}
}
}
}
/// <summary>
/// Gets stored procedures from database connection
/// </summary>
/// <param name="connection">Instance of <see cref="DbConnection"/> class</param>
/// <param name="storedProcedures">Sequence of stored procedures</param>
/// <returns>A sequence of <see cref="StoredProcedure"/> that represents existing views in database</returns>
protected virtual IEnumerable<StoredProcedure> GetStoredProcedures(DbConnection connection, IEnumerable<DbObject> storedProcedures)
{
foreach (var dbObject in storedProcedures)
{
using (var command = connection.CreateCommand())
{
var storedProcedure = new StoredProcedure
{
Schema = dbObject.Schema,
Name = dbObject.Name
};
command.Connection = connection;
command.CommandText = string.Format("sp_help '{0}'", dbObject.FullName);
var queryResults = new List<QueryResult>();
using (var dataReader = command.ExecuteReader())
{
while (dataReader.NextResult())
{
var queryResult = new QueryResult();
while (dataReader.Read())
{
var names = SqlServerDatabaseFactoryHelper.GetNames(dataReader).ToList();
var row = new Dictionary<string, object>();
for (var i = 0; i < names.Count; i++)
row.Add(names[i], dataReader.GetValue(i));
queryResult.Items.Add(row);
}
queryResults.Add(queryResult);
}
foreach (var result in queryResults)
{
foreach (var item in result.Items)
{
if (item.ContainsKey("Parameter_name"))
AddParameter(storedProcedure, item);
}
}
yield return storedProcedure;
}
}
}
}
/// <summary>
/// Adds constraint to table
/// </summary>
/// <param name="table">Instance of <see cref="Table"/> class</param>
/// <param name="dictionary">Dictionary from data reader</param>
protected virtual void AddConstraintToTable(Table table, IDictionary<string, object> dictionary)
{
table.ConstraintDetails.Add(SqlServerDatabaseFactoryHelper.GetConstraintDetail(dictionary));
}
/// <summary>
/// Add index to view
/// </summary>
/// <param name="view">Instance of <see cref="View"/> class</param>
/// <param name="dictionary">Dictionary from data reader</param>
protected virtual void AddIndexToView(View view, IDictionary<string, object> dictionary)
{
view.Indexes.Add(SqlServerDatabaseFactoryHelper.GetIndex(dictionary));
}
/// <summary>
/// Adds index to table
/// </summary>
/// <param name="table">Instance of <see cref="Table"/> class</param>
/// <param name="dictionary">Dictionary from data reader</param>
protected virtual void AddIndexToTable(Table table, IDictionary<string, object> dictionary)
{
table.Indexes.Add(SqlServerDatabaseFactoryHelper.GetIndex(dictionary));
}
/// <summary>
/// Adds a parameter in table function from row dictionary
/// </summary>
/// <param name="tableFunction">Instance of <see cref="TableFunction"/> class</param>
/// <param name="dictionary">Dictionary from data reader</param>
protected virtual void AddParameter(TableFunction tableFunction, IDictionary<string, object> dictionary)
{
tableFunction.Parameters.Add(SqlServerDatabaseFactoryHelper.GetParameter(dictionary));
}
/// <summary>
/// Adds a parameter in stored procedure from row dictionary
/// </summary>
/// <param name="storedProcedure">Instance of <see cref="StoredProcedure"/> class</param>
/// <param name="dictionary">Dictionary from data reader</param>
protected virtual void AddParameter(StoredProcedure storedProcedure, IDictionary<string, object> dictionary)
{
storedProcedure.Parameters.Add(SqlServerDatabaseFactoryHelper.GetParameter(dictionary));
}
/// <summary>
/// Gets tables from database connection
/// </summary>
/// <param name="connection">Instance of <see cref="DbConnection"/> class</param>
/// <param name="tables">Sequence of <see cref="DbObject"/> that represents tables</param>
/// <returns>A sequence of <see cref="Table"/></returns>
protected virtual IEnumerable<Table> GetTables(DbConnection connection, IEnumerable<DbObject> tables)
{
foreach (var dbObject in tables)
{
using (var command = connection.CreateCommand())
{
var table = new Table
{
Schema = dbObject.Schema,
Name = dbObject.Name
};
command.Connection = connection;
command.CommandText = string.Format("sp_help '{0}'", dbObject.FullName);
var queryResults = new List<QueryResult>();
using (var dataReader = command.ExecuteReader())
{
while (dataReader.NextResult())
{
var queryResult = new QueryResult();
while (dataReader.Read())
{
var names = SqlServerDatabaseFactoryHelper.GetNames(dataReader).ToList();
var row = new Dictionary<string, object>();
for (var i = 0; i < names.Count; i++)
row.Add(names[i], dataReader.GetValue(i));
queryResult.Items.Add(row);
}
queryResults.Add(queryResult);
}
foreach (var result in queryResults)
{
foreach (var item in result.Items)
{
if (item.ContainsKey("Column_name"))
AddColumn(table, item);
else if (item.ContainsKey("Identity"))
SetIdentity(table, item);
else if (item.ContainsKey("RowGuidCol"))
SetRowGuidCol(table, item);
else if (item.ContainsKey("index_name"))
AddIndexToTable(table, item);
else if (item.ContainsKey("constraint_type"))
AddConstraintToTable(table, item);
else if (item.ContainsKey("Table is referenced by foreign key"))
AddTableReferenceToTable(table, item);
}
}
SetConstraintsFromConstraintDetails(table);
yield return table;
}
}
}
}
/// <summary>
/// Imports an existing database from SQL Server instance using database import settings
/// </summary>
/// <returns>An instance of <see cref="Database"/> class that represents a database from SQL Server instance</returns>
public virtual Database Import()
{
var database = new Database
{
DefaultSchema = "dbo",
SupportTransactions = true,
DatabaseTypeMaps = DatabaseTypeMapList.Definition,
NamingConvention = new SqlServerDatabaseNamingConvention()
};
using (var connection = GetConnection())
{
connection.Open();
database.Name = connection.Database;
SqlServerDatabaseFactoryHelper.AddUserDefinedDataTypes(database, connection);
var dbObjects = GetDbObjects(connection).ToList();
foreach (var dbObject in dbObjects)
{
if (DatabaseImportSettings.Exclusions.Contains(dbObject.FullName))
continue;
database.DbObjects.Add(dbObject);
}
if (DatabaseImportSettings.ExtendedProperties.Count > 0)
{
Logger?.LogInformation("Importing extended properties for database...");
ImportExtendedProperties(connection, database);
}
if (DatabaseImportSettings.ImportTables)
{
Logger?.LogInformation("Importing tables for '{0}'...", database.Name);
foreach (var table in GetTables(connection, database.GetTables()))
{
if (DatabaseImportSettings.Exclusions.Contains(table.FullName))
continue;
database.Tables.Add(table);
}
if (DatabaseImportSettings.ExtendedProperties.Count > 0)
{
Logger?.LogInformation("Importing extended properties for tables...");
foreach (var table in database.Tables)
ImportExtendedProperties(connection, table);
}
}
if (DatabaseImportSettings.ImportViews)
{
Logger?.LogInformation("Importing views for '{0}'...", database.Name);
foreach (var view in GetViews(connection, database.GetViews()))
{
if (DatabaseImportSettings.Exclusions.Contains(view.FullName))
continue;
database.Views.Add(view);
}
if (DatabaseImportSettings.ExtendedProperties.Count > 0)
{
Logger?.LogInformation("Importing extended properties for views...");
foreach (var view in database.Views)
ImportExtendedProperties(connection, view);
}
}
if (DatabaseImportSettings.ImportStoredProcedures)
{
Logger?.LogInformation("Importing stored procedures for '{0}'...", database.Name);
foreach (var storedProcedure in GetStoredProcedures(connection, database.GetStoredProcedures()))
{
if (DatabaseImportSettings.Exclusions.Contains(storedProcedure.FullName))
continue;
database.StoredProcedures.Add(storedProcedure);
}
Logger?.LogInformation("Getting first result sets for stored procedures...");
foreach (var storedProcedure in database.StoredProcedures)
{
foreach (var firstResultSet in SqlServerDatabaseFactoryHelper.GetFirstResultSetForObject(storedProcedure, connection))
{
storedProcedure.FirstResultSetsForObject.Add(firstResultSet);
}
}
if (DatabaseImportSettings.ExtendedProperties.Count > 0)
{
Logger?.LogInformation("Importing extended properties for stored procedures...");
foreach (var storedProcedure in database.StoredProcedures)
ImportExtendedProperties(connection, storedProcedure);
}
}
if (DatabaseImportSettings.ImportTableFunctions)
{
Logger?.LogInformation("Importing table functions for '{0}'...", database.Name);
foreach (var tableFunction in GetTableFunctions(connection, database.GetTableFunctions()))
{
if (DatabaseImportSettings.Exclusions.Contains(tableFunction.FullName))
continue;
database.TableFunctions.Add(tableFunction);
}
if (DatabaseImportSettings.ExtendedProperties.Count > 0)
{
Logger?.LogInformation("Importing extended properties for table functions...");
foreach (var tableFunction in database.TableFunctions)
ImportExtendedProperties(connection, tableFunction);
}
}
if (DatabaseImportSettings.ImportScalarFunctions)
{
Logger?.LogInformation("Importing scalar functions for '{0}'...", database.Name);
foreach (var scalarFunction in GetScalarFunctions(connection, database.GetScalarFunctions()))
{
if (DatabaseImportSettings.Exclusions.Contains(scalarFunction.FullName))
continue;
database.ScalarFunctions.Add(scalarFunction);
}
if (DatabaseImportSettings.ExtendedProperties.Count > 0)
{
Logger?.LogInformation("Importing extended properties for scalar functions...");
foreach (var scalarFunction in database.ScalarFunctions)
ImportExtendedProperties(connection, scalarFunction);
}
}
}
return database;
}
