/// <summary>
/// Returns a list of INSERT statements to insert data for the result sets of one or more queries.
/// </summary>
public static IList <string> GetInsertStatements(string serverName, string databaseName, string loginId, string password,
TextReader queryReader)
{
string[] queries = ScriptHelper.ReadDatabaseScript(queryReader);
if (queries.Length == 0)
{
return(new string[0]);
}
DbTaskHelper.DefaultServerAndDbNames(ref serverName, ref databaseName);
IList <string> statements = new List <string>();
using (SqlConnection connection = new SqlConnection(ScriptHelper.GetSqlConnectionString(
serverName, databaseName, loginId, password)))
{
connection.Open();
foreach (string query in queries)
{
statements.Add(GetInsertStatementsForQuery(connection, query));
}
}
return(statements);
}
/// <summary>
/// Returns a list of tables in an SQL database in the order in which they can be
/// deleted (or data in them can be deleted), taking into account foreign keys.
/// </summary>
public static IList <string> GetListInDeleteOrder(string serverName, string databaseName)
{
DbTaskHelper.DefaultServerAndDbNames(ref serverName, ref databaseName);
using (SqlConnection connection = new SqlConnection(ScriptHelper.GetSqlConnectionString(
serverName, databaseName)))
{
connection.Open();
// Get a list of all user tables.
IList <string> tables = GetAllTables(connection);
// For each table get the tables that reference it and move it below all those table.
List <string> ordered = new List <string>(tables);
Dictionary <string, IList <string> > dependenceCache = new Dictionary <string, IList <string> >();
int i = 0;
while (i < ordered.Count)
{
IList <string> dependentTables = GetDependentTables(connection, ordered[i], dependenceCache);
if (!MoveBelowAll(ordered, i, dependentTables))
{
i++;
}
}
return(ordered);
}
}
