Пример #1
0
        /// <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);
        }
Пример #2
0
        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
        }
Пример #3
0
        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);
            }
        }