As a part of ETL, sometimes you need to generate the Insert script of the data, and then copy the human flesh to another place for execution.

Students who are familiar with SMSS all know that there is a task of generating scripts, which can generate database create scripts, and in fact can also produce data in tables.

Automatic ETL can't even export data... One is easy to make mistakes, the other is too low.

The C ා console code can do these things, directly on the code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management; 
using Microsoft.SqlServer.Management.Sdk.Sfc;  

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            String todayDate = DateTime.Now.Day + "_" + DateTime.Now.Month + "_" + DateTime.Now.Year;
            String backupDirectory = "1a:\\DBBackup\\";
            String backupFileName = backupDirectory + todayDate + ".sql";
            if (File.Exists(backupFileName))
            {

                File.Delete(backupFileName);

            }
            StreamWriter sw = File.CreateText(backupFileName);


            Console.WriteLine(backupFileName);
            Console.ReadKey();            
            Console.WriteLine("hello!");


            //Console.ReadLine();
            String dbName = "2oy"; // database name   
            Server srv = new Server("3lba1");

            // Reference the database.    
            Database db = srv.Databases[dbName];

            // Define a Scripter object and set the required scripting options.   
            Scripter scrp = new Scripter(srv);
           
            scrp.Options.ScriptSchema = false;
            scrp.Options.ScriptDrops = false;
            scrp.Options.WithDependencies = false;
            scrp.Options.Indexes = false;   // To include indexes  
            scrp.Options.DriAllConstraints = false;   // to include referential constraints in the script  
            scrp.Options.ScriptData = true; //Data include!!!!!!

            
        

            //Iterate through the tables in database and script each one.

            foreach (Table tb in db.Tables)
            {

                if (!tb.IsSystemObject)
                {

                    foreach (string s in scrp.EnumScript(new Urn[] { tb.Urn }))
                    {

                        sw.WriteLine(s);

                        sw.Flush();

                    }

                }

            }


            /*
             * This method can not generate script with data, but can generate schema script
            foreach (Table tb in db.Tables)
            {
                System.Collections.Specialized.StringCollection sc = scrp.Script(new Urn[]{tb.Urn}); 

                foreach (string st in sc)
                {
                    Console.WriteLine(st);
                }
                Console.WriteLine("--");

            }
             */
             
        }
    }
}