使用SQLDMO控制SQLServer，实现基本操作

/**/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//作者：覃健
//功能：使用SQLDMO控制SQLServer，实现基本操作
//提供建库，见表，备份，恢复，脚本导入，脚本导出，数据库服务器注册，注销，数据导入导出等功能谁有兴趣，偶愿意共享 
//可以，企业管理器我们常用的功能基本就实现了，可以加入dataset里，这样我们的dataset就拥有这些功能了 
//也就是说，可以从DataSet继承一个类并聚合你这个类，达到很好的数据操控目的？？ 

/**/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
using System;
using System.Collections;
using System.Text;
using SQLDMO;

namespace GetSqlServer
{
    导入/导出数据文件类型#region 导入/导出数据文件类型
    /**//// <summary>
    /// 导入/导出数据文件类型
    /// </summary>
    public enum DataFileType
    {
        /**//// <summary>
        /// 默认格式为CommaDelimitedChar
        /// </summary>
        Default,
        /**//// <summary>
        /// Unicode本机格式, bcp开关-N或本机格式,bcp开关-n
        /// </summary>
        NativeFormat,
        /**//// <summary>
        /// 字符格式, bcp开关-c
        /// </summary>
        TabDelimitedChar,
        /**//// <summary>
        /// 字符格式, bcp开关-c, -t, -r
        /// </summary>
        CommaDelimitedChar,
        /**//// <summary>
        /// 格式文件
        /// </summary>
        FormatFile
    }
    #endregion
    
    SQLServer#region SQLServer
    /**//// <summary>
    /// SQLServer
    /// </summary>
    public class SQLServer: IDisposable
    {
        内部变量#region 内部变量
        private string _ServerName;
        private string _Login;
        private string _Password;
        #endregion 

        公共属性#region 公共属性
        public string ServerName
        {
            get { return _ServerName; }
            set { _ServerName = value; }
        }

        public string Login
        {
            get { return _Login; }
            set { _Login = value; }
        }

        public string Password
        {
            get { return _Password; }
            set { _Password = value; }
        }
        #endregion

        构造函数#region 构造函数
        public SQLServer( string sqlServerName, string login, string password )
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
            this._ServerName = sqlServerName;
            this._Login = login;
            this._Password = password;
        }
        #endregion 

        获取服务器列表#region 获取服务器列表
        public ArrayList GetSqlServers()
        {
            ArrayList alServers = new ArrayList();
            SQLDMO.Application sqlApp =  new SQLDMO.ApplicationClass();

            try
            {
                SQLDMO.NameList serverList = sqlApp.ListAvailableSQLServers();
                for ( int i = 1; i <= serverList.Count; i++ )
                {
                    System.Text.StringBuilder sb = new StringBuilder( serverList.Item(i) );
                    alServers.Add( sb );
                }
            }
            catch ( Exception ex)
            {
                throw( new Exception( "获取数据库服务器列表出错, " + ex.Message ) );
            }
            finally
            {
                sqlApp.Quit();

                GC.Collect();
            }

            return alServers;
        }
        #endregion 

        获取服务器上的数据库#region 获取服务器上的数据库
        public ArrayList GetDataBase(  string sqlServerName, string login, string password  )
        {
            this._ServerName = sqlServerName;
            this._Login = login;
            this._Password = password;

            return this.GetDataBase();
        }

        public ArrayList GetDataBase()
        {
            ArrayList alDB = null;
            ArrayList alDBName = null;
            Hashtable htTables = null;
            Hashtable htStoreProcedures = null;
            Hashtable htViews = null;
            Hashtable htRoles = null;
            Hashtable htUsers = null;
            Hashtable htDefault = null;
            Hashtable htRules = null;
            Hashtable htUserDefinedDatatype  = null;

//            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer sqlSvr = new SQLDMO.SQLServerClass();
            
            try
            {
                sqlSvr.Connect( this._ServerName, this._Login, this._Password );

                SQLDMO.Databases dbs = sqlSvr.Databases;;

                alDBName = new ArrayList( dbs.Count );
                htTables = new Hashtable( dbs.Count );
                htStoreProcedures = new Hashtable( dbs.Count );
                htViews =  new Hashtable( dbs.Count );
                htRoles = new Hashtable( dbs.Count );
                htUsers = new Hashtable( dbs.Count );
                htDefault = new Hashtable( dbs.Count );
                htRules = new Hashtable( dbs.Count );
                htUserDefinedDatatype  = new Hashtable( dbs.Count );
                
                foreach ( SQLDMO.Database db in dbs )
                {
                    ArrayList alTempTable = new ArrayList( db.Tables.Count );
                    ArrayList alTempStoreProcedures = new ArrayList( db.StoredProcedures.Count );
                    ArrayList alTempViews = new ArrayList( db.Views.Count );
                    ArrayList alTempRoles = new ArrayList( db.DatabaseRoles.Count );
                    ArrayList alTempUsers = new ArrayList( db.Users.Count );
                    ArrayList alTempDefault = new ArrayList( db.Defaults.Count );
                    ArrayList alTempRules = new ArrayList( db.Rules.Count );
                    ArrayList alTempUserDefinedDatatype = new ArrayList( db.UserDefinedDatatypes.Count );

                    if ( db.Name != null )
                    {
                        alDBName.Add( db.Name );

                        foreach ( SQLDMO.Table table in db.Tables )
                        {
                            alTempTable.Add( table.Name );
                        }

                        foreach ( SQLDMO.StoredProcedure storeProc in db.StoredProcedures )
                        {
                            alTempStoreProcedures.Add( storeProc.Name );
                        }

                        foreach ( SQLDMO.View view in db.Views )
                        {
                            alTempViews.Add( view.Name );
                        }

                        foreach ( SQLDMO.DatabaseRole rule in db.DatabaseRoles )
                        {
                            alTempRoles.Add( rule.Name );
                        }

                        foreach ( SQLDMO.User user in db.Users )
                        {
                            alTempUsers.Add( user.Name );
                        }

                        foreach ( SQLDMO.Default dbDefault in db.Defaults )
                        {
                            alTempDefault.Add( dbDefault.Name );
                        }

                        foreach ( SQLDMO.Rule dbRule in db.Rules )
                        {
                            alTempRules.Add( dbRule.Name );
                        }

                        foreach ( SQLDMO.UserDefinedDatatype dbUserDefinedDatatype in db.UserDefinedDatatypes )
                        {
                            alTempUserDefinedDatatype.Add( dbUserDefinedDatatype.Name );
                        }

                    }
                    string path = "c:\\" + db.Name + ".sql";
                    
                    //                    db.Script(  SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_IncludeHeaders | SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_DatabasePermissions | SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Aliases | SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_AppendToFile | SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Bindings | SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_ClusteredIndexes | SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default, 
                    //                                path, SQLDMO.SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_MarkTriggers | SQLDMO.SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_FullTextIndex | SQLDMO.SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_EncryptPWD | SQLDMO.SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_OnlyUserTriggers | SQLDMO.SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_MarkTriggers | SQLDMO.SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_ExtendedProperty | SQLDMO.SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default );

                    htTables.Add( db.Name, alTempTable );
                    htStoreProcedures.Add( db.Name, alTempStoreProcedures );
                    htViews.Add( db.Name, alTempViews );
                    htRoles.Add( db.Name, alTempRoles );
                    htUsers.Add( db.Name, alTempUsers );
                    htDefault.Add( db.Name, alTempDefault );
                    htRules.Add( db.Name, alTempRules );
                    htUserDefinedDatatype .Add( db.Name, alTempUserDefinedDatatype );
                }
                alDB = new ArrayList( 7 );
                alDB.Add( alDBName );
                alDB.Add( htTables );
                alDB.Add( htViews );
                alDB.Add( htStoreProcedures );
                alDB.Add( htUsers );
                alDB.Add( htRoles );
                alDB.Add( htRules );
                alDB.Add( htDefault );
                alDB.Add( htUserDefinedDatatype  );

            }
            catch ( Exception ex)
            {
                throw( new Exception( "获取数据库列表出错, " + ex.Message ) );
            }
            finally
            {
                sqlSvr.DisConnect();
//                sqlApp.Quit();

                GC.Collect();
            }

            return alDB;
        }

        #endregion 

        建列#region 建列
        private SQLDMO.Column NewColumn( string colName, string colDataType, int colLenth, bool isNull ) 
        {
            SQLDMO.Column col = new SQLDMO.Column();

            col.Name = colName;
            col.Datatype = colDataType;
            col.Length = colLenth;
            col.AllowNulls = isNull;

            return col;
        }
        #endregion 

        建表#region 建表
        public SQLDMO.Table NewTable( string dbName, string tableName, ArrayList colName, ArrayList colDataType, ArrayList colLength, ArrayList isNull, string owner)//ArrayList defaultValue)//, ArrayList isPrimarykey )
        {
//            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer sqlSvr = new SQLDMO.SQLServerClass();
            SQLDMO.Table table = null;

            try
            {
                sqlSvr.Connect( this._ServerName, this._Login, this._Password );

                table = new SQLDMO.Table();
                table.Name = tableName;

                for( int i = 0; i < colName.Count; i++ )
                {
                    table.Columns.Add( this.NewColumn( colName[i].ToString(),
                                                       colDataType[i].ToString(), 
                                                       Int32.Parse(colLength[i].ToString()), 
                                                       bool.Parse(isNull[i].ToString()) 
                                                      )
                                      );
                }
                
                SQLDMO.Key key = new KeyClass();
                key.Type = SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Primary;
                key.Clustered = true;
                key.KeyColumns.Add( colName[0].ToString() );
                table.Keys.Add( key );
                
                SQLDMO.Database db = (SQLDMO.Database)sqlSvr.Databases.Item( dbName, owner );

                db.Tables.Add( table );
            }
            catch ( Exception ex)
            {
                throw( new Exception( "创建数据库表出错, " + ex.Message ) );
            }
            finally
            {
                sqlSvr.DisConnect();
//                sqlApp.Quit();

                GC.Collect();
            }

            return table;
        }
        #endregion 

        建库#region 建库
        /**//// <summary>
        /// 建库
        /// </summary>
        /// <param name="dbName">数据库名</param>
        public void NewDatabase( string dbName )
        {
//            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer sqlSvr = new SQLDMO.SQLServerClass();
        
            try
            {
                sqlSvr.Connect( this._ServerName, this._Login, this._Password );

                SQLDMO.Database db = new SQLDMO.DatabaseClass();
                db.Name = dbName;

                sqlSvr.Databases.Add( db );
            }
            catch ( Exception ex)
            {
                throw( new Exception( "创建数据库出错, " + ex.Message ) );
            }
            finally
            {
                sqlSvr.DisConnect();
//                sqlApp.Quit();

                GC.Collect();
            }
            
        }
        #endregion 

        删库#region 删库
        /**//// <summary>
        /// 删库
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <param name="owner">数据库所有者</param>
        public void DeleteDatabase( string dbName, string owner )
        {
//            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer sqlSvr = new SQLDMO.SQLServerClass();

            try
            {
                
                sqlSvr.Connect( this._ServerName, this._Login, this._Password );
                
                sqlSvr.Databases.Remove( dbName, owner );
            }
            catch ( Exception ex)
            {
                throw( new Exception( "删除数据库出错, " + ex.Message ) );
            }
            finally
            {
                sqlSvr.DisConnect();
//                sqlApp.Quit();
            }
        }
        #endregion 

        删表#region 删表
        /**//// <summary>
        /// 删表
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <param name="tableName">表名</param>
        /// <param name="owner">数据库所有者</param>
        public void DeleteTable( string dbName, string tableName, string owner )
        {
//            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer sqlSvr = new SQLDMO.SQLServerClass();

            try
            {
                sqlSvr.Connect( this._ServerName, this._Login, this._Password );
                
                SQLDMO.Database db = (SQLDMO.Database)sqlSvr.Databases.Item( dbName, owner );
                db.Tables.Remove( tableName, owner );
            }
            catch ( Exception ex)
            {
                throw( new Exception( "删除数据表出错, " + ex.Message ) );
            }
            finally
            {
                sqlSvr.DisConnect();
//                sqlApp.Quit();

                GC.Collect();
            }
        }
        #endregion 

        建视图#region 建视图
        /**//// <summary>
        /// 建视图
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <param name="viewName">视图名</param>
        /// <param name="scriptView">视图脚本</param>
        /// <param name="owner">数据库所有者</param>
        public void NewView( string dbName, string viewName, string scriptView, string owner )
        {
//            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer sqlSvr = new SQLDMO.SQLServerClass();

            try
            {
                sqlSvr.Connect( this._ServerName, this._Login, this._Password );

                SQLDMO.Database db = (SQLDMO.Database)sqlSvr.Databases.Item( dbName, owner );

                SQLDMO.View view = new SQLDMO.ViewClass();
                view.Name = viewName;
                view.Text = scriptView;
                
                db.Views.Add( view );
            }
            catch ( Exception ex)
            {
                throw( new Exception( "创建视图出错 " + ex.Message ) );
            }
            finally
            {
                sqlSvr.DisConnect();
//                sqlApp.Quit();

                GC.Collect();
            }
        }
        #endregion 

        删视图#region 删视图
        /**//// <summary>
        /// 删视图
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <param name="viewName">视图名</param>
        /// <param name="owner">数据库所有者</param>
        public void DeleteView( string dbName, string viewName, string owner )
        {
//            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer sqlSvr = new SQLDMO.SQLServerClass();

            try
            {
                sqlSvr.Connect( this._ServerName, this._Login, this._Password );

                SQLDMO.Database db = (SQLDMO.Database)sqlSvr.Databases.Item( dbName, owner );
                db.Views.Remove( viewName, owner );
            }
            catch ( Exception ex)
            {
                throw( new Exception( "删除视图出错, " + ex.Message ) );
            }
            finally
            {
                sqlSvr.DisConnect();
//                sqlApp.Quit();

                GC.Collect();
            }
        }
        #endregion 

        建存储过程#region 建存储过程
        /**//// <summary>
        /// 建存储过程
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <param name="storeProcedureName">存储过程名</param>
        /// <param name="scriptStoreProcedure">存储过程脚本</param>
        /// <param name="owner">数据库所有者</param>
        public void NewStoreProcedure( string dbName, string storeProcedureName, string scriptStoreProcedure, string owner )
        {
//            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer sqlSvr = new SQLDMO.SQLServerClass();

            try
            {
                sqlSvr.Connect( this._ServerName, this._Login, this._Password );

                SQLDMO.Database db = (SQLDMO.Database)sqlSvr.Databases.Item( dbName, owner );
                
                SQLDMO.StoredProcedure storeProc = new SQLDMO.StoredProcedureClass();
                storeProc.Name = storeProcedureName;
                storeProc.Text = scriptStoreProcedure;
                storeProc.Startup = false;

                db.StoredProcedures.Add( storeProc );
            }
            catch ( Exception ex)
            {
                throw( new Exception( "创建存储过程出错, " + ex.Message ) );
            }
            finally
            {
                sqlSvr.DisConnect();
//                sqlApp.Quit();

                GC.Collect();
            }
        }
        #endregion

        删存储过程#region 删存储过程
        /**//// <summary>
        /// 删存储过程
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <param name="storeProcedureName">储存过程名</param>
        /// <param name="owner">数据库所有者</param>
        public void DeleteStoreProcedure( string dbName, string storeProcedureName, string owner )
        {
//            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer sqlSvr = new SQLDMO.SQLServerClass();

            try
            {
                sqlSvr.Connect( this._ServerName, this._Login, this._Password );

                SQLDMO.Database db = (SQLDMO.Database)sqlSvr.Databases.Item( dbName, owner );
                db.StoredProcedures.Remove( storeProcedureName, owner );
            }
            catch ( Exception ex)
            {
                throw( new Exception( "删除存储过程出错, " + ex.Message ) );
            }
            finally
            {
                sqlSvr.DisConnect();
//                sqlApp.Quit();

                GC.Collect();
            }
        }
        #endregion

        脚本导出#region 脚本导出
        /**//// <summary>
        /// 脚本导出
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <param name="scirptFilePath">脚步文件路径</param>
        /// <param name="owner">数据库所有者</param>
        public void EmportScript( string dbName, string scirptFilePath, string owner )
        {
//            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer sqlSvr = new SQLDMO.SQLServerClass();

            try
            {
                sqlSvr.Connect( this._ServerName, this._Login, this._Password );

                SQLDMO.SQLDMO_SCRIPT_TYPE scriptType = SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Drops | SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_IncludeHeaders | 
                                                       SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default | SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_AppendToFile |
                                                       SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Bindings;
                SQLDMO.SQLDMO_SCRIPT2_TYPE script2Type = SQLDMO.SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default;

                SQLDMO.Database db = (SQLDMO.Database)sqlSvr.Databases.Item( dbName, owner );

                db.Script( scriptType, scirptFilePath, script2Type );

                导出自定义类型#region 导出自定义类型
                foreach ( SQLDMO.UserDefinedDatatype udd in db.UserDefinedDatatypes )
                {
                    udd.Script( scriptType, scirptFilePath, script2Type );
                }
                #endregion

                导出表和触发器,并过滤掉系统表和系统触发器#region 导出表和触发器,并过滤掉系统表和系统触发器
                foreach ( SQLDMO.Table table in db.Tables )
                {
                    if ( table.SystemObject == false )
                    {
                        table.Script( scriptType, scirptFilePath, null, script2Type );

                        foreach( SQLDMO.Trigger trigger in table.Triggers )
                        {
                            if ( trigger.SystemObject == false )
                            {
                                trigger.Script( scriptType, scirptFilePath, script2Type );
                            }
                        }
                    }
                }
                #endregion

                导出规则#region 导出规则
                foreach ( SQLDMO.Rule rule in db.Rules )
                {
                    rule.Script( scriptType, scirptFilePath, script2Type );
                }
                #endregion

                导出存储过程,并过滤掉系统存储过程#region 导出存储过程,并过滤掉系统存储过程
                foreach ( SQLDMO.StoredProcedure storedProcedure in db.StoredProcedures )
                {
                    if ( storedProcedure.SystemObject == false )
                    {
                        storedProcedure.Script( scriptType, scirptFilePath, script2Type );
                    }
                }
                #endregion

                导出视图,并过滤掉系统视图#region 导出视图,并过滤掉系统视图
                foreach ( SQLDMO.View view in db.Views )
                {
                    if ( view.SystemObject == false )
                    {
                        view.Script( scriptType, scirptFilePath, script2Type );
                    }
                }
                #endregion

//                foreach ( SQLDMO.FullTextCatalog ftc in db.FullTextCatalogs )
//                {
//                    ftc.Script( scriptType, scirptFilePath, script2Type );
//                }

//                #region 导出角色
//                foreach ( SQLDMO.DatabaseRole role in db.DatabaseRoles )
//                {
//                    role.Script( scriptType, scirptFilePath, script2Type );
//                }
//                #endregion

                导出用户自定义数据类型#region 导出用户自定义数据类型
                foreach ( SQLDMO.UserDefinedDatatype userDefinedDatatype in db.UserDefinedDatatypes )
                {
                    userDefinedDatatype.Script( scriptType, scirptFilePath, script2Type );
                }
                #endregion 

                导出默认#region 导出默认
                foreach ( SQLDMO.Default dbDefault in db.Defaults )
                {
                    dbDefault.Script( scriptType, scirptFilePath, script2Type );
                }
                #endregion 
            }
            catch ( Exception ex)
            {
                throw( new Exception( "导出数据库脚本出错, " + ex.Message ) );
            }
            finally
            {
                sqlSvr.DisConnect();
//                sqlApp.Quit();

                GC.Collect();
            }
        }
        #endregion 

        脚本导入#region 脚本导入
        /**//// <summary>
        /// 脚本导入
        /// </summary>
        /// <param name="sqlScriptFilePath">脚本文件</param>
        public void ImportScript( string sqlScriptFilePath )
        {
//            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer sqlSvr = new SQLDMO.SQLServerClass();

            try
            {
                string sql = "";
                sqlSvr.Connect( this._ServerName, this._Login, this._Password );

                using ( System.IO.FileStream fileStream = new System.IO.FileStream( sqlScriptFilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read ))
                {
                    byte[] bytes = new byte[fileStream.Length];

                    fileStream.Read( bytes, 0, bytes.Length );

                    sql = System.Text.Encoding.Default.GetString( bytes );
                }
                
                sqlSvr.ExecuteWithResults( sql, sql.Length );
            }
            catch ( Exception ex)
            {
                throw( new Exception( "导出数据库脚本出错, " + ex.Message ) );
            }
            finally
            {
                sqlSvr.DisConnect();
//                sqlApp.Quit();

                GC.Collect();
            }
        }
        #endregion

        备份数据库#region 备份数据库
        /**//// <summary>
        /// 备份数据库
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <param name="backupFile">备份文件名</param>
        public void BackupDatabase( string dbName, string backupFile )
        {
//            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer sqlSvr = new SQLDMO.SQLServerClass();
            
            try
            {
                sqlSvr.Connect( this._ServerName, this._Login, this._Password );

                SQLDMO.Backup backup = new SQLDMO.BackupClass();
                backup.Database = dbName;
                backup.Files = backupFile;

                if ( System.IO.File.Exists( backupFile ) )
                {
                    System.IO.File.Delete( backupFile );
                }

                backup.SQLBackup( sqlSvr );
            }
            catch ( Exception ex)
            {
                throw( new Exception( "备份数据库出错, " + ex.Message ) );
            }
            finally
            {
                sqlSvr.DisConnect();
//                sqlApp.Quit();

                GC.Collect();
            }
        }
        #endregion 

        还原数据库#region 还原数据库
        /**//// <summary>
        /// 还原数据库
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <param name="backupFile">备份文件</param>
        public void RestoreDatabase( string dbName, string backupFile )
        {
//            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer sqlSvr = new SQLDMO.SQLServerClass();
            
            try
            {
                sqlSvr.Connect( this._ServerName, this._Login, this._Password );

                SQLDMO.Restore restore = new SQLDMO.RestoreClass();
                restore.Database = dbName;
                restore.Files = backupFile;
                restore.SQLRestore( sqlSvr );
            }
            catch ( Exception ex)
            {
                throw( new Exception( "还原数据库出错, " + ex.Message ) );
            }
            finally
            {
                sqlSvr.DisConnect();
//                sqlApp.Quit();

                GC.Collect();
            }
        }
        #endregion 
        
        导入数据#region 导入数据
        /**//// <summary>
        /// 导入数据
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <param name="tableName">表名</param>
        /// <param name="dataFilePath">数据文件路径</param>
        /// <param name="dataFileType">数据文件类型</param>
        /// <param name="owner">数据库所有者</param>
        public void ImportData( string dbName, string tableName, string dataFilePath, DataFileType dataFileType, string owner )
        {
//            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer sqlSvr = new SQLDMO.SQLServerClass();

            try
            {
                sqlSvr.Connect( this._ServerName, this._Login, this._Password );

                SQLDMO.Database db = (SQLDMO.Database)sqlSvr.Databases.Item( dbName, owner );
                SQLDMO.Table table = (SQLDMO.Table )db.Tables.Item( tableName, owner );;
                SQLDMO.BulkCopy bulkCopy = this.SetBulkCopy( dataFilePath, dataFileType );

                table.ImportData( bulkCopy );
            }
            catch ( Exception ex)
            {
                throw( new Exception( "导入数据出错, " + ex.Message ) );
            }
            finally
            {
                sqlSvr.DisConnect();
//                sqlApp.Quit();

                GC.Collect();
            }
        }
        #endregion

        导出数据#region 导出数据
        /**//// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <param name="tableName">表名</param>
        /// <param name="dataFilePath">数据文件路径</param>
        /// <param name="dataFileType">数据文件类型</param>
        /// <param name="owner">数据库所有者</param>
        public void ExportData( string dbName, string tableName, string dataFilePath, DataFileType dataFileType, string owner )
        {
//            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer sqlSvr = new SQLDMO.SQLServerClass();

            try
            {
                sqlSvr.Connect( this._ServerName, this._Login, this._Password );

                SQLDMO.Database db = (SQLDMO.Database)sqlSvr.Databases.Item( dbName, owner );
                SQLDMO.Table table = (SQLDMO.Table )db.Tables.Item( tableName, owner );
                SQLDMO.BulkCopy bulkCopy = this.SetBulkCopy( dataFilePath, dataFileType );

                table.ExportData( bulkCopy );
            }
            catch ( Exception ex)
            {
                throw( new Exception( "导出数据出错, " + ex.Message ) );
            }
            finally
            {
                sqlSvr.DisConnect();
//                sqlApp.Quit();

                GC.Collect();
            }
        }
        #endregion

        设置BulkCopy#region 设置BulkCopy
        /**//// <summary>
        /// 设置BulkCopy
        /// </summary>
        /// <param name="dataFilePath">数据文件路径</param>
        /// <param name="dataFileType">数据文件类型</param>
        /// <returns>BulkCopy对象</returns>
        protected SQLDMO.BulkCopy SetBulkCopy( string dataFilePath, DataFileType dataFileType )
        {
            SQLDMO.BulkCopy bulkCopy = new SQLDMO.BulkCopyClass();

            switch ( dataFileType )
            {
                case DataFileType.Default :
                case DataFileType.CommaDelimitedChar :
                {
                    /**//// 字符格式, bcp开关-c, -t, -r
                    bulkCopy.DataFileType = SQLDMO.SQLDMO_DATAFILE_TYPE.SQLDMODataFile_CommaDelimitedChar;
                    bulkCopy.DataFilePath = dataFilePath;

                    break;
                }
                case DataFileType.NativeFormat :
                {
                    /**//// Unicode本机格式, bcp开关-N或本机格式,bcp开关-n
                    bulkCopy.DataFileType = SQLDMO.SQLDMO_DATAFILE_TYPE.SQLDMODataFile_NativeFormat;
                    bulkCopy.DataFilePath = dataFilePath;

                    break;
                }
                case DataFileType.TabDelimitedChar :
                {
                    /**//// 字符格式, bcp开关-c
                    bulkCopy.DataFileType = SQLDMO.SQLDMO_DATAFILE_TYPE.SQLDMODataFile_TabDelimitedChar;
                    bulkCopy.DataFilePath = dataFilePath;

                    break;
                }
                case DataFileType.FormatFile :
                {
                    break;
                }
                default:
                {
                    break;
                }
            }

            return bulkCopy;
        }
        #endregion 

        注册SQLServer#region 注册SQLServer
        /**//// <summary>
        /// 注册SQLServer, 使用帐户
        /// </summary>
        /// <param name="serverGroup">组名</param>
        /// <param name="serverName">服务器名</param>
        /// <param name="login">登陆名</param>
        /// <param name="password">密码</param>
        public void RegisteredServer( string serverGroup,string serverName, string login, string password )
        {
            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
//            SQLDMO.SQLServer sqlSvr = new SQLDMO.SQLServerClass();

            try
            {
//                sqlSvr.Connect( this._ServerName, this._Login, this._Password );

                SQLDMO.RegisteredServer registSvr = new SQLDMO.RegisteredServerClass();
                /**//// 使用指定用户名和密码
                registSvr.UseTrustedConnection = 0;
                registSvr.Name = serverName;
                registSvr.Login = login;
                registSvr.Password = password;

                SQLDMO.ServerGroup sg = sqlApp.ServerGroups.Item( serverGroup );
                sg.RegisteredServers.Add( registSvr );
            }
            catch ( Exception ex)
            {
                throw( new Exception( "注册服务器出错, " + ex.Message ) );
            }
            finally
            {
//                sqlSvr.DisConnect();
                sqlApp.Quit();

                GC.Collect();
            }
        }
    
        /**//// <summary>
        /// 注册SQLServer, 使用WindowsNT集成安全
        /// </summary>
        /// <param name="serverGroup">组名</param>
        /// <param name="serverName">服务器名</param>
        public void RegisteredServer( string serverGroup,string serverName )
        {
            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
//            SQLDMO.SQLServer sqlSvr = new SQLDMO.SQLServerClass();

            try
            {
//                sqlSvr.Connect( this._ServerName, this._Login, this._Password );

                SQLDMO.RegisteredServer registSvr = new SQLDMO.RegisteredServerClass();
                /**//// 使用WindowsNT集成安全
                registSvr.UseTrustedConnection = 1;
                registSvr.Name = serverName;

                SQLDMO.ServerGroup sg = sqlApp.ServerGroups.Item( serverGroup );
                sg.RegisteredServers.Add( registSvr );
            }
            catch ( Exception ex)
            {
                throw( new Exception( "注册服务器出错, " + ex.Message ) );
            }
            finally
            {
//                sqlSvr.DisConnect();
                sqlApp.Quit();

                GC.Collect();
            }
        }
        #endregion 

        删除SQLServer#region 删除SQLServer
        public void RemoveSQLServer( string serverGroup,string serverName )
        {
            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();

            try
            {
                SQLDMO.ServerGroup sg = sqlApp.ServerGroups.Item( serverGroup );
                sg.RegisteredServers.Remove( serverName );
            }
            catch ( Exception ex)
            {
                throw( new Exception( "删除服务器出错, " + ex.Message ) );
            }
            finally
            {
                //                sqlSvr.DisConnect();
                sqlApp.Quit();

                GC.Collect();
            }
        }
        #endregion 

        添加SQLServerGroup#region 添加SQLServerGroup
        public void AddSQLGroup( string serverGroup )
        {
            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();

            try
            {
                SQLDMO.ServerGroup sg = new SQLDMO.ServerGroupClass();
                sg.Name = serverGroup;
                
                sqlApp.ServerGroups.Add( sg );
            }
            catch ( Exception ex)
            {
                throw( new Exception( "添加SQLServerGroup出错, " + ex.Message ) );
            }
            finally
            {
                sqlApp.Quit();

                GC.Collect();
            }
        }
        #endregion 

        删除SQLServerGroup#region 删除SQLServerGroup
        public void RemoveSQLGroup( string serverGroup )
        {
            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();

            try
            {
                sqlApp.ServerGroups.Remove( serverGroup );
            }
            catch ( Exception ex)
            {
                throw( new Exception( "删除SQLServerGroup出错, " + ex.Message ) );
            }
            finally
            {
                sqlApp.Quit();

                GC.Collect();
            }
        }
        #endregion 

        IDisposable 成员#region IDisposable 成员

        public void Dispose()
        {
            // TODO:  添加 SQLServer.Dispose 实现
        }

        #endregion
    }
    #endregion 
}