DAC--使用DAC来导出数据库脚本

 

   //==============================================

    //功能介绍：使用DAC来导出数据库脚本

    //注意事项：

    //1.本程序涉及到的DLL有:

    //  --Microsoft.SqlServer.ConnectionInfo.dll

    //  --Microsoft.SqlServer.Management.Dac.dll

    //  --Microsoft.SqlServer.Management.Sdk.Sfc.dll

    // --Microsoft.SqlServer.Management.SmoMetadataProvider.dll

    //2.以上DLL可以在C:\Windows\assembly\GAC_MSIL下找到，

    //如果找不到,可以使用SQLSERVER 2012安装包安装以下MSI:

    //  --SQLSysClrTypes.msi

    //  --SharedManagementObjects.msi

    //  --DACFramework.msi

    //

   //==============================================

   

   

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.SqlClient;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Dac;

using Microsoft.SqlServer.Management.Smo;

using System.IO;

 

class DACExtracter

    {

        private List <string>GetDatabaseNames(SqlConnection conn)

        {

            SqlCommand comm = newSqlCommand( "SELECT name FROM sys.databases where database_id>4",conn);

            List<string >dbNames = new List<string >();

            try

            {

                conn.Open();

                SqlDataReader reader= comm.ExecuteReader();

                while(reader.Read())

                {

                   dbNames.Add(reader["name" ].ToString());

                }

 

                return dbNames;

            }

            catch

            {

                Console.WriteLine("connect dbfailed");

                throw(new Exception("connect db failed"));

            }

            finally

            {

                if(conn!=null )

                {

                    conn.Close();

                    conn.Dispose();

                }

            }

 

 

        }

        public voidExtractDAC(string connectionString, string extractFolderPath)

        {

            try

            {

                SqlConnectionsqlConnection = new SqlConnection(connectionString);

                ServerConnectionconn = new ServerConnection(sqlConnection);

                Server destServer =new Server(conn);

                List<string >dbNames = GetDatabaseNames(sqlConnection);

                string version ="1.0.0.1" ;

                foreach (stringdbName in dbNames)

                {

                   ExtractDAC(destServer, dbName, version, extractFolderPath);

                }

            }

            catch(Exception )

            {

               Console.WriteLine("End with error" );

            }

        }

        private voidExtractDAC(Server destServer,string databaseName,string version,stringextractFolderPath)

        {

            try

            {

               Console.WriteLine(destServer.Information.Version);

                DacExtractionUnitdacUnit = new DacExtractionUnit(destServer, databaseName, databaseName, newVersion (version));

 

                DirectoryInfo dir =new DirectoryInfo(extractFolderPath);

                if (!dir.Exists)

                {

                    dir.Create();

                }

                string dacFilePath =dir.FullName + @"\" + databaseName + ".dacpac" ;

                if (!File.Exists(dacFilePath))

                {

                   File.Delete(dacFilePath);

                }

               dacUnit.Extract(dacFilePath);

               Console.WriteLine("extract "+databaseName+"successfully");

            }

            catch(IOException )

            {

               Console.WriteLine("please check extract folder");

               Console.WriteLine("extract " + databaseName + "failed" );

            }

            catch (Exception )

            {

               Console.WriteLine("extract " + databaseName + "failed" );

            }

          

        }

    }

 

        static void Main(string[] args)

        {

            String connectionString= "Data Source=88.88.88.88,18991;Initial Catalog=master;IntegratedSecurity=False;User ID=sa;Password=sa;";

            DACExtracterdacExtracter = new DACExtracter();

           dacExtracter.ExtractDAC(connectionString, @"E:\DBScript" );

           Console.WriteLine("press any key to close");

            Console.ReadLine();

        }