有关SQLDMO

网上相关文章较少，自己摸索到的一些方法。

 #region 返回数据库列表
        public ArrayList GetDbList()
        {
            ArrayList alDbs = new ArrayList();
            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
            try
            {
                svr.Connect(ServerName, UserName, Password);
                foreach (SQLDMO.Database db in svr.Databases)
                {
                    if (db.Name != null)
                        alDbs.Add(db.Name);
                }
            }
            catch (Exception e)
            {
                throw (new Exception("连接数据库出错：" + e.Message));
            }
            finally
            {
                svr.DisConnect();
                sqlApp.Quit();
            }
            return alDbs;
        }
#endregion

        #region 返回字段表
        public DataTable GetColumns(string DatabaseName, string TableName)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ColumnName", typeof(string));
            dt.Columns.Add("ColumnType", typeof(string));
            dt.Columns.Add("ColumnSize", typeof(int));
            dt.Columns.Add("ColumnKey", typeof(bool));
            dt.Columns.Add("ColumnNull", typeof(bool));
            dt.Columns.Add("ColumnID", typeof(int));
            dt.Columns.Add("ColumnRemark", typeof(string));

            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer2 svr = new SQLDMO.SQLServer2Class();

            try
            {
                svr.Connect(ServerName, UserName, Password);
                SQLDMO.Database2 myDb = new SQLDMO.Database2Class();
                myDb = (SQLDMO.Database2)svr.Databases.Item(DatabaseName, "owner");
                SQLDMO.Table2 myTb = new SQLDMO.Table2Class();

                foreach (SQLDMO.Table2 tb in myDb.Tables)
                {
                    if (tb.Name == TableName)
                        myTb = tb;
                }
                foreach (SQLDMO.Column2 column in myTb.Columns)
                {
                    DataRow dr = dt.NewRow();
                    dr["ColumnName"] = column.Name;
                    dr["ColumnType"] = column.Datatype;
                    dr["ColumnSize"] = column.Length;
                    dr["ColumnKey"] = column.InPrimaryKey;
                    dr["ColumnNull"] = column.AllowNulls;
                    dr["ColumnID"] = column.ID;
                    dr["ColumnRemark"] = column.Properties.Application.ODBCVersionString;
                   
                   
                    dt.Rows.Add(dr);
                }
            }
            catch (Exception e)
            {
                //throw (new Exception("连接数据库出错：" + e.Message));
                ShowError("连接数据库出错：" + e.Message);
                return null;
            }
            finally
            {
                svr.DisConnect();
                sqlApp.Quit();
            }
            return dt;
        } 

 

#region 返回表

        public ArrayList GetTables(string DatabaseName)
        {
            ArrayList alDbs = new ArrayList();
            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
           
            try
            {
                svr.Connect(ServerName, UserName, Password);
                SQLDMO.Database mydb = new SQLDMO.DatabaseClass();
                mydb = (SQLDMO.Database)svr.Databases.Item(DatabaseName, "owner");

                foreach (SQLDMO.Table db in mydb.Tables)
                {
                    if (db.Name != null)
                        alDbs.Add(db.Name);
                }
            }
            catch (Exception e)
            {
                //throw (new Exception("连接数据库出错：" + e.Message));
                ShowError("连接数据库出错：" + e.Message);
                return null;
            }
            finally
            {
                svr.DisConnect();
                sqlApp.Quit();
            }   
            return alDbs;
        }
        #endregion

        #region 返回存储过程列表
        public ArrayList GetStored(string DatabaseName)
        {
            ArrayList alDbs = new ArrayList();
            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();

            try
            {
                svr.Connect(ServerName, UserName, Password);
                SQLDMO.Database mydb = new SQLDMO.DatabaseClass();
                mydb = (SQLDMO.Database)svr.Databases.Item(DatabaseName, "owner");

                foreach (SQLDMO.StoredProcedure db in mydb.StoredProcedures)
                {
                    if (db.Name != null)
                        alDbs.Add(db.Name);
                }
            }
            catch (Exception e)
            {
                //throw (new Exception("连接数据库出错：" + e.Message));
                ShowError("连接数据库出错：" + e.Message);
                return null;
            }
            finally
            {
                svr.DisConnect();
                sqlApp.Quit();
            }
            return alDbs;
        }

        #endregion

        #region 备份数据库

        /// <summary>
        /// 数据库的备份和实时进度显示
        /// </summary>
        /// <param name="strDbName"></param>
        /// <param name="strFileName"></param>
        /// <param name="pgbMain"></param>
        /// <returns></returns>
        public bool BackUPDB(string strDbName, string strFileName)
        {
            SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
            try
            {
                svr.Connect(ServerName, UserName, Password);
                SQLDMO.Backup bak = new SQLDMO.BackupClass();
                bak.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
                bak.Initialize = true;
                bak.Files = strFileName;
                bak.Database = strDbName;
                bak.SQLBackup(svr);
                return true;
            }
            catch (Exception err)
            {
                //throw (new Exception("备份数据库失败" + err.Message));
                ShowError("连接数据库出错：" + err.Message);
                return false;
            }
            finally
            {
                svr.DisConnect();
            }
        }
        #endregion

        #region 还原数据库
        /// <summary>
        /// 恢复数据库，恢复前杀死所有与本数据库相关进程
        /// </summary>
        /// <param name="strDbName">数据库名</param>
        /// <param name="strFileName">存放路径</param>
        /// <param name="pgbMain"></param>
        /// <returns></returns>
        public bool RestoreDB(string strDbName, string strFileName)
        {
            SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
            try
            {
                svr.Connect(ServerName, UserName, Password);
                //取得所有的进程列表
                SQLDMO.QueryResults qr = svr.EnumProcesses(-1);
                int iColPIDNum = -1;
                int iColDbName = -1;
                //找到和要恢复数据库相关的进程
                for (int i = 1; i <= qr.Columns; i++)
                {
                    string strName = qr.get_ColumnName(i);
                    if (strName.ToUpper().Trim() == "SPID")
                    {
                        iColPIDNum = i;
                    }
                    else if (strName.ToUpper().Trim() == "DBNAME")
                    {
                        iColDbName = i;
                    }
                    if (iColPIDNum != -1 && iColDbName != -1)
                        break;
                }
                //将相关进程杀死
                for (int i = 1; i <= qr.Rows; i++)
                {
                    int lPID = qr.GetColumnLong(i, iColPIDNum);
                    string strDBName = qr.GetColumnString(i, iColDbName);
                    if (strDBName.ToUpper() == strDbName.ToUpper())
                        svr.KillProcess(lPID);
                }

                SQLDMO.Restore res = new SQLDMO.RestoreClass();

                res.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
                res.Files = strFileName;

                res.Database = strDbName;
                res.FileNumber = 1;
               
                res.ReplaceDatabase = true;
                res.SQLRestore(svr);
               
                return true;
            }
            catch (Exception err)
            {
                //throw (new Exception("" + err.Message));
                ShowError("恢复数据库失败,请关闭所有和该数据库连接的程序！" + err.Message);
                return false;
            }
            finally
            {
                svr.DisConnect();
            }
        }
        #endregion

        #region 附加数据库
        public bool AttachDB(string dbName, string dbFile)
        {
            SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
            try
            {
                svr.Connect(ServerName, UserName, Password);
                svr.AttachDB(dbName, dbFile);
                return true;
            }
            catch (Exception err)
            {
                //throw (new Exception("！" + err.Message));
                ShowError("附加数据库失败" + err.Message);
                return false;
            }
            finally
            {
                svr.DisConnect();
            }
        }
        #endregion

        #region 创建库

        public bool CreateDB(string dbName, string path)
        {
            // 创建数据库文件
            SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
            SQLDMO.DBFile dbFile = new SQLDMO.DBFileClass();
            try
            {
                svr.Connect(ServerName, UserName, Password);
                dbFile.Name = dbName + "_Data";
                dbFile.PhysicalName = Path.Combine(path, dbName + "_Data.MDF");
                dbFile.PrimaryFile = true;
                //dbFile.Size = 2; // 设置初始化大小(MB)
                //dbFile.FileGrowthType = SQLDMO_GROWTH_TYPE.SQLDMOGrowth_MB; // 设置文件增长方式
                //dbFile.FileGrowth=1; // 设置增长幅度

                // 创建日志文件
                SQLDMO._LogFile logFile = new SQLDMO.LogFileClass();
                logFile.Name = dbName + "_Log";
                logFile.PhysicalName = Path.Combine(path, dbName + "_Log.MDF");
                //logFile.Size = 3;
                //logFile.FileGrowthType=SQLDMO_GROWTH_TYPE.SQLDMOGrowth_MB;
                //logFile.FileGrowth=1;

                // 创建数据库
                SQLDMO.Database db = new SQLDMO.DatabaseClass();
                db.Name = dbName;
                db.FileGroups.Item("PRIMARY").DBFiles.Add(dbFile);
                db.TransactionLog.LogFiles.Add(logFile);

                // 建立数据库联接，并添加数据库到服务器
                svr.Databases.Add(db);
                return true;
            }
            catch (Exception err)
            {
                //throw (new Exception("！" + err.Message));
                ShowError("添加数据库失败!" + err.Message);
                return false;
            }
            finally
            {
                svr.DisConnect();
            }
        }
        #endregion

        #region 删除数据库
        public bool KillDB(string dbName)
        {
            SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
            try
            {
                svr.Connect(ServerName, UserName, Password);
                svr.KillDatabase(dbName);
                return true;
            }
            catch (Exception err)
            {
                //throw (new Exception("！" + err.Message));
                ShowError("删除数据库失败!" + err.Message);
                return false;
            }
            finally
            {
                svr.DisConnect();
            }
        }
        #endregion

        #region 删除表

        public bool KillTable(string DataBaseName,string tbName)
        {
            SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();   
            try
            {
               
                svr.Connect(ServerName, UserName, Password);
                svr.Databases.Item(DataBaseName, "owner").Tables.Remove(tbName, "owner");
                return true;
            }
            catch (Exception err)
            {
                //throw (new Exception("！" + err.Message));
                ShowError("删除表失败!" + err.Message);
                return false;
            }
            finally
            {
                svr.DisConnect();
            }
        }
        #endregion

        #region 删除存储过程
        public bool KillStored(string DataBaseName, string tbName)
        {
            SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
            try
            {

                svr.Connect(ServerName, UserName, Password);
                svr.Databases.Item(DataBaseName, "owner").StoredProcedures.Remove(tbName, "owner");
                return true;
            }
            catch (Exception err)
            {
                //throw (new Exception("！" + err.Message));
                ShowError("删除存储过程失败!" + err.Message);
                return false;
            }
            finally
            {
                svr.DisConnect();
            }
        }
        #endregion

        #region 添加存储过程
        public bool UpdateStored(string DataBaseName, string StoredName,string StoredText)
        {
            SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
            try
            {

                svr.Connect(ServerName, UserName, Password);
                if (StoredName == "")
                {
                    SQLDMO.StoredProcedure spd = new SQLDMO.StoredProcedureClass();
                    spd.Text = StoredText;
                    svr.Databases.Item(DataBaseName, "owner").StoredProcedures.Add(spd);
                }
                else
                {
                    SQLDMO.Database dbs = new SQLDMO.DatabaseClass();
                    SQLDMO.StoredProcedure spd = new SQLDMO.StoredProcedureClass();
                    dbs = (SQLDMO.Database)svr.Databases.Item(DataBaseName, "owner");
                    foreach (SQLDMO.StoredProcedure sp in dbs.StoredProcedures)
                    {
                        if (sp.Name == StoredName)
                            spd = sp;
                    }
                    spd.Alter(StoredText);
                 }
                    return true;
            }
            catch (Exception err)
            {
                //throw (new Exception("！" + err.Message));
                ShowError("修改存储过程失败!" + err.Message);
                return false;
            }
            finally
            {
                svr.DisConnect();
            }
        }
        #endregion

        #region 返回存储过程
        public string GetStoredText(string DataBaseName, string StoredName)
        {
            string getStoredText = "";
            SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
            try
            {
                svr.Connect(ServerName, UserName, Password);
                SQLDMO.Database dbs = new SQLDMO.DatabaseClass();
                dbs = (SQLDMO.Database)svr.Databases.Item(DataBaseName, "owner");

                foreach (SQLDMO.StoredProcedure tb in dbs.StoredProcedures)
                {
                    if (tb.Name == StoredName)
                        getStoredText= tb.Text;
                }
            }
            catch (Exception err)
            {
                //throw (new Exception("！" + err.Message));
                ShowError("修改存储过程失败!" + err.Message);
                getStoredText= "";
            }
            finally
            {
                svr.DisConnect();
            }
            return getStoredText;
        }
        #endregion