/// <summary>
/// Creates a database table for each DataTable that's part of a BacksightDataSet,
/// inserts initial rows, and defines constraints.
/// </summary>
/// <param name="logger">Something to display progress messages (not null)</param>
/*
* public void CreateTables(ILog logger)
* {
* try
* {
* if (logger == null)
* throw new ArgumentNullException();
*
* //Transaction.Execute(delegate
* //{
* CreateBacksightTables(logger);
* //});
* }
*
* catch (Exception ex)
* {
* logger.LogMessage(ex.Message);
* throw ex;
* }
* }
*/
/*
* void CreateBacksightTables(ILog logger)
* {
* DropForeignKeyConstraints();
*
* // Create the ced schema
* logger.LogMessage("CREATE SCHEMA ced");
* Smo.Schema s = new Smo.Schema(m_Database, "ced");
* s.Create();
*
* BacksightDataSet ds = new BacksightDataSet();
* foreach (DataTable dt in ds.Tables)
* {
* logger.LogMessage("CREATE TABLE " + GetTableName(dt));
* CreateTable(s, dt);
* }
*
* // Add simple checks (unfortunately, this info isn't held as part of the
* // generated DataSet, so need to explicitly identify each table involved)
*
* AddSimpleChecks(logger, ds.EntityType, ds.EntityType.Checks);
* AddSimpleChecks(logger, ds.EntityTypeSchema, ds.EntityTypeSchema.Checks);
* AddSimpleChecks(logger, ds.Font, ds.Font.Checks);
* AddSimpleChecks(logger, ds.IdAllocation, ds.IdAllocation.Checks);
* AddSimpleChecks(logger, ds.IdFree, ds.IdFree.Checks);
* AddSimpleChecks(logger, ds.IdGroup, ds.IdGroup.Checks);
* AddSimpleChecks(logger, ds.Layer, ds.Layer.Checks);
* AddSimpleChecks(logger, ds.Schema, ds.Schema.Checks);
* AddSimpleChecks(logger, ds.SchemaTemplate, ds.SchemaTemplate.Checks);
* AddSimpleChecks(logger, ds.Template, ds.Template.Checks);
* AddSimpleChecks(logger, ds.Theme, ds.Theme.Checks);
*
* // Insert initial rows & save to database
* logger.LogMessage("Inserting initial rows");
* ds.AddInitialRows();
* ds.Save(ConnectionString);
*
* // Define foreign key constraints
* AddForeignKeyConstraints(logger);
* }
*/
/*
* void AddSimpleChecks(ILog logger, DataTable dt, string[] checks)
* {
* string tableName = GetTableName(dt);
* Smo.Table t = m_Database.Tables[tableName, "ced"];
* if (t==null)
* throw new Exception("Cannot locate table ced."+tableName);
*
* for (int i=0; i<checks.Length; i++)
* {
* string checkName = String.Format("{0}Check{1}", t.Name, i+1);
* logger.LogMessage("ADD CHECK "+checkName);
* AddCheck(t, checkName, checks[i]);
* }
* }
*/
/*
* void AddForeignKeyConstraints(ILog logger)
* {
* BacksightDataSet ds = new BacksightDataSet();
*
* foreach (DataRelation dr in ds.Relations)
* {
* DataTable parent = dr.ParentTable;
* DataTable child = dr.ChildTable;
*
* string parentTableName = GetTableName(parent);
* string childTableName = GetTableName(child);
*
* Smo.Table parentTable = m_Database.Tables[parentTableName, "ced"];
* Smo.Table childTable = m_Database.Tables[childTableName, "ced"];
*
* if (parentTable!=null && childTable!=null)
* {
* if (logger!=null)
* logger.LogMessage("ADD CONSTRAINT "+dr.RelationName);
*
* AddForeignKeyConstraint(dr);
* }
* }
* }
*/
/*
* void DropForeignKeyConstraints()
* {
* BacksightDataSet ds = new BacksightDataSet();
* List<Smo.ForeignKey> fks = new List<Smo.ForeignKey>();
*
* foreach (DataTable dt in ds.Tables)
* {
* string tableName = GetTableName(dt);
* Smo.Table t = m_Database.Tables[tableName, "ced"];
* if (t!=null)
* {
* foreach (Smo.ForeignKey fk in t.ForeignKeys)
* fks.Add(fk);
* }
* }
*
* foreach (Smo.ForeignKey fk in fks)
* fk.Drop();
* }
*/
void CreateTable(Smo.Schema s, DataTable dt)
{
// Drop any previously created version of the table
string tableName = GetTableName(dt);
Smo.Table t = m_Database.Tables[tableName, "ced"];
if (t != null)
{
t.Drop();
}
// Create the table
t = new Smo.Table(m_Database, tableName);
t.Schema = s.Name;
foreach (DataColumn c in dt.Columns)
{
AddColumn(t, c);
}
t.Create();
// Define primary key
CreatePrimaryKey(t, dt);
// Define pk & any unique constraints
CreateIndexes(t, dt);
}
public MySmo.Schema GetSchema(Smo.Schema smo_s, MySmo.Database parent = null)
{
#region implement
SetDataLimit();
var mysmo_s = new MySmo.Schema();
mysmo_s.ParentDatabase = parent;
mysmo_s.Name = smo_s.Name;
mysmo_s.Owner = smo_s.Owner;
mysmo_s.ExtendedProperties = GetExtendProperties(mysmo_s, smo_s.ExtendedProperties);
FormatExtendProperties(mysmo_s);
return(mysmo_s);
#endregion
}
public void ExtractScript(SQLObjectName oname, SQLScripts SQLScriptsCollection, bool Verbose)
{
// Store extracted scripts. Each extract may include multiple scripts.
StringCollection OutputScripts = new StringCollection();
string FinalScript = String.Empty;
switch (oname.ObjectType)
{
case SQLObjectType.Table:
Microsoft.SqlServer.Management.Smo.Table scriptTable = connDatabase.Tables[oname.Name, oname.Schema];
if (scriptTable != null)
{
StringCollection CheckScripts = new StringCollection(); // Store scripts to be checked
String TableScript = String.Empty; // Stores individual script for output collection.
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.DriAll = true;
scriptOptions.Statistics = true;
scriptOptions.ClusteredIndexes = true;
scriptOptions.NonClusteredIndexes = true;
scriptOptions.DriAllConstraints = true;
scriptOptions.WithDependencies = false;
// Get table and related scripts
CheckScripts = scriptTable.Script(scriptOptions);
// Check scripts so we can remove invalide SQL 2012 column store options from the script.
// (Why doesn't the target server version remove this?
// This is a crappy place to do this, and it's version specific.
// Need to implement the new versioning code to check target model.
foreach (string CheckCCI in CheckScripts)
{
if (CheckCCI.Contains(", DATA_COMPRESSION = COLUMNSTORE"))
{
TableScript = CheckCCI.Replace(", DATA_COMPRESSION = COLUMNSTORE", "");
}
else
{
TableScript = CheckCCI;
}
// Add the script into the OutputScripts collection.
OutputScripts.Add(TableScript);
}
}
break;
case SQLObjectType.View:
Microsoft.SqlServer.Management.Smo.View scriptView = connDatabase.Views[oname.Name, oname.Schema];
if (scriptView != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.DriAll = true;
scriptOptions.ClusteredIndexes = true;
scriptOptions.NonClusteredIndexes = true;
scriptOptions.WithDependencies = false;
// Must specify tables seperatly, but safer to do so
// to avoid having duplicate table names in the model.
OutputScripts = scriptView.Script(scriptOptions);
}
break;
case SQLObjectType.StoredProcedure:
Microsoft.SqlServer.Management.Smo.StoredProcedure scriptStoredProcedure = connDatabase.StoredProcedures[oname.Name, oname.Schema];
if (scriptStoredProcedure != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = scriptStoredProcedure.Script(scriptOptions);
}
break;
case SQLObjectType.PartitionScheme:
{
Microsoft.SqlServer.Management.Smo.PartitionScheme scriptPScheme = connDatabase.PartitionSchemes[oname.Name];
if (scriptPScheme != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = scriptPScheme.Script(scriptOptions);
}
}
break;
case SQLObjectType.PartitionFunction:
{
Microsoft.SqlServer.Management.Smo.PartitionFunction scriptPFunction = connDatabase.PartitionFunctions[oname.Name];
if (scriptPFunction != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = scriptPFunction.Script(scriptOptions);
}
}
break;
case SQLObjectType.Schema:
{
Microsoft.SqlServer.Management.Smo.Schema scriptSchema = connDatabase.Schemas[oname.Name];
if (scriptSchema != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
scriptOptions.ScriptOwner = true; // This includes the "with authorize" part.
OutputScripts = scriptSchema.Script(scriptOptions);
}
}
break;
case SQLObjectType.FileGroup:
{
Microsoft.SqlServer.Management.Smo.FileGroup scriptFG = connDatabase.FileGroups[oname.Name];
if (scriptFG != null)
{
// Create manual script for FileGroups
OutputScripts.Add("ALTER DATABASE [$(DatabaseName)] ADD FILEGROUP " + scriptFG.Name);
}
}
break;
case SQLObjectType.User:
{
Microsoft.SqlServer.Management.Smo.User scriptUser = connDatabase.Users[oname.Name];
if (scriptUser != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = scriptUser.Script(scriptOptions);
}
}
break;
case SQLObjectType.Function:
Microsoft.SqlServer.Management.Smo.UserDefinedFunction userDefinedFunction = connDatabase.UserDefinedFunctions[oname.Name, oname.Schema];
if (userDefinedFunction != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = userDefinedFunction.Script(scriptOptions);
}
break;
}
if (OutputScripts.Count > 0)
{
Console.WriteLine("Extracted SQL script: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name));
foreach (string script in OutputScripts)
{
// Add the script to the script collection.
FinalScript = FinalScript + script + Environment.NewLine + "GO" + Environment.NewLine;
}
}
else
{
Console.WriteLine("Warning - Could not retrieve: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name));
FinalScript = String.Empty;
}
if (FinalScript != String.Empty)
{
SQLScriptsCollection.Scripts.Add(FinalScript);
}
else
{
SQLScriptsCollection.MissingScripts.Add("Missing SQL object: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name));
}
// Print script(s) if verbose is on.
if (Verbose)
{
Console.WriteLine(FinalScript);
}
}