Script Generator via SMO and C#

Bill Geronatsios 21 Reputation points
2020-12-03T06:25:58.72+00:00

Hi I would like to produce SQL Data Script via SMO with C#

  1. I am trying first to generate data script with one table. And i need to produce only insert script with a few rows only of a table , not all table data. How add filter ? like a primary key id or something ?
  2. My code is very slow when try to execute scripter.EnumScript , why ? how can make it better ?

My code :

        public string GenerateSqlScript(bool includeData) {
            Server server = new Server(@"" + DBMain.ConnDetails.Server);
            Database database = server.Databases[DBMain.ConnDetails.Catalog];
            List<Urn> list = new List<Urn>();


            DataTable dataTable = database.EnumObjects(DatabaseObjectTypes.Table);

            List<Table> tableList = new List<Table>();

            foreach (DataRow row in dataTable.Rows) {
                if(row.ItemArray[2].ToString()== "myTable")
                   list.Add(new Urn((string)row["Urn"]));
                //MessageBox.Show(row.ItemArray[2].ToString());
            }

            Scripter scripter = new Scripter();
            scripter.Server = server;
            scripter.Options.IncludeDatabaseContext = false;
            scripter.Options.IncludeHeaders = true;
            scripter.Options.SchemaQualify = true;
            scripter.Options.ScriptData = includeData;
            scripter.Options.SchemaQualifyForeignKeysReferences = true;
            scripter.Options.NoCollation = true;
            scripter.Options.DriAllConstraints = true;
            scripter.Options.DriAll = true;
            scripter.Options.DriAllKeys = true;
            scripter.Options.Triggers = true;
            scripter.Options.DriIndexes = true;
            scripter.Options.ClusteredIndexes = true;
            scripter.Options.NonClusteredIndexes = true;
            scripter.Options.ToFileOnly = false;
            //this is very slow...
            var scripts = scripter.EnumScript(list.ToArray());

            string result = "";
            foreach (var script in scripts)
                result += script + Environment.NewLine;
            return result;
        }
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,046 questions
0 comments No comments
{count} votes

Accepted answer
  1. Olaf Helper 42,451 Reputation points
    2020-12-03T07:52:56.51+00:00

    with a few rows only of a table , not all table data. How add filter ?

    There is no option to add a filter, the data script generator create INSERTS for all data in the table. If you want to limit it, create a view with all columns and a filter on the data.

    0 comments No comments

0 additional answers

Sort by: Most helpful