Scripting Tables, Views and Data using SMO (part 3)

Below is the final code that I used to script off my Tables, Views and the data using sp_generate_inserts from Vyas.  This is something that I have needed to do, and I found that using SMO for this is a great tool.  Mind you, that the data file that gets output to me is 22 MB from this and it runs within 2 seconds including putting out all the data to me in a file.  I did run into some interesting side affects in getting data back and trying to put it into a Text file.  I noticed that if I used any encoding except Unicode, that I got binary in my output file.  I have not figured it out in powershell yet, but in .NET and SMO I can use the Encoding.Unicode when I open the file to append to it. I know that I can do it in Powershell using .NET objects, but I want to use the Add-Content, but have not been able to make it work. 

The nice trick that I found with this code is that I create a Dependency Tree (or at least Smo does), and then I script the tables data out in the order that it should so that Primary Keys exist before the tables with Foreign Keys to them exist, so it really is nice to script it all out.  I also have the additional code to do Stored Procedures, but we have not got there yet, but it is essentially like the Views except you use the Stored Procedure object.  If you have any ideas on the Powershell Add-Content method, and getting Unicode instead of UTF8 out of it, I am all ears.  Happy days to you and your SMO.

            string dbName = "DATABASENAME";
            string outputfile = @"c:\temp\output.sql";

            Server srv = new Server(@"(local)\INSTANCE");
            srv.ConnectionContext.LoginSecure = true;

            Database db = new Database();
            db = srv.Databases[dbName];
            
            Scripter scr = new Scripter(srv);
            srv.SetDefaultInitFields(typeof(View), "IsSystemObject");

            ScriptingOptions options = new ScriptingOptions();
            options.DriAll = true;
            options.ClusteredIndexes = true;
            options.Default = true;
            options.DriAll = true;
            options.Indexes = true;
            options.IncludeHeaders = true;
            options.AppendToFile = false;
            options.FileName = outputfile;
            options.ToFileOnly = true;
            scr.Options = options;
            
            Table[] tbls = new Table[db.Tables.Count];
            db.Tables.CopyTo(tbls, 0);
            scr.Script(tbls);
            
            options.AppendToFile = true;
            View[] view = new View[1];
            for (int idx = 0; idx < db.Views.Count; idx++)
            {
                if (!db.Views[idx].IsSystemObject)
                {
                    view[0] = db.Views[idx];
                    scr.Script(view);
                }
            }

            DependencyTree tree = scr.DiscoverDependencies(tbls, true);
            DependencyWalker depwalker = new Microsoft.SqlServer.Management.Smo.DependencyWalker();
            DependencyCollection depcoll = depwalker.WalkDependencies(tree);

            StreamWriter sw = new StreamWriter(outputfile, true, Encoding.Unicode);
            
            StringBuilder sb = new StringBuilder();
            foreach(DependencyCollectionNode dep in depcoll)
            {
                sb.AppendFormat("EXEC sp_generate_inserts @table_name='{0}', @owner='dbo'{1}", dep.Urn.GetAttribute("Name"), Environment.NewLine);
            }

            DataSet ds = new DataSet();
            ds = db.ExecuteWithResults(sb.ToString());
            foreach (DataTable dt in ds.Tables)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    sw.WriteLine(dr[0].ToString());
                }
            }
            sw.Close();