老生常谈，获取数据库服务器的相关信息。-程序员宅基地

SQLDmo：

获取服务器列表：

 

///   <summary>   得到局域網內處於活動狀態的SQLServer服務   </summary>  
  public   static   void   GetActiveSQLServer(ref   ComboBox   cmbList)  
  {  
  cmbList.Items.Clear();  
   
  SQLDMO.ApplicationClass   app   =   new   SQLDMO.ApplicationClass();  
  SQLDMO.NameList   list   =   app.Application.ListAvailableSQLServers();  
  string   server;  
  for(int   i=0;i<list.Count;i++)  
  {  
  server   =   list.Item(i);  
  if(server==null)  
  continue;  
  cmbList.Items.Add(server);  
  }  
  if(list.Count<1)  
  cmbList.Items.Add("localhost");  
  cmbList.SelectedIndex   =   0;  
  }  
  }   

 

获取某实例数据库列表：

 

//得到指定SQL服务器所有数据库的列表  
  　　SQLDMO.Application   sqlApp   =   new   SQLDMO.ApplicationClass();  
  　　SQLDMO.SQLServer   srv   =   new   SQLDMO.SQLServerClass();  
  　　srv.Connect(this.cboServers.SelectedItem.ToString(),this.txtUser.Text,this.txtPassword.Text);  
  　　foreach(SQLDMO.Database   db   in   srv.Databases)  
  　　{  
  　　if(db.Name!=null)  
  　　this.cboDatabase.Items.Add(db.Name);  
  　　}   

 

获取数据库表

 

　//得到所有的存储过程，所有的表放到Tables集合，所以的视图放到Views集合  
  　　SQLDMO.SQLServer   srv   =   new   SQLDMO.SQLServerClass();  
  　　srv.Connect(this.cboServers.SelectedItem.ToString(),this.txtUser.Text,this.txtPassword.Text);  
  　　for(int   i=0;i  
  　　{  
  　　if(srv.Databases.Item(i+1,"dbo").Name   ==   this.cboDatabase.SelectedItem.ToString())  
  　　{  
  　　SQLDMO._Database   db=   srv.Databases.Item(i+1,"dbo");  
  　　this.lstObjects.Items.Clear();  
  　　for(int   j=0;j  
  　　{  
  　　this.lstObjects.Items.Add(db.StoredProcedures.Item(j+1,"dbo").Name);  
  　　}  
  　　break;  
  　　}  
  　　}   

 

数据库备份

 

1/**//// <summary>
 2        /// 数据库备份
 3        /// </summary>
 4        /// <returns>备份是否成功</returns>
 5        public bool DbBackup()
 6        {
 7            string path = CreatePath();
 8            SQLDMO.Backup oBackup = new QLDMO.BackupClass();
 9            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
10            try
11            {
12                oSQLServer.LoginSecure = false;
13                oSQLServer.Connect(server,uid, pwd);
14                oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
15                oBackup.Database = database;
16                oBackup.Files = path;
17                oBackup.BackupSetName = database;
18                oBackup.BackupSetDescription = "数据库备份";
19                oBackup.Initialize = true;
20                oBackup.SQLBackup(oSQLServer);
21
22                return true;
23            }
24            catch(Exception ex)
25            {
26                return false;
27                throw ex;
28            }
29            finally
30            {
31                oSQLServer.DisConnect();
32            }
33        }

 

数据库恢复

 

/**//// <summary>
 2        /// 数据库恢复
 3        /// </summary>
 4        public string DbRestore()
 5        {
 6            if(exepro()!=true)//执行存储过程
 7            {
 8                return "操作失败";
 9            }
10            else
11            {
12                SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
13                SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
14                try
15                {
16                    exepro();
17                    oSQLServer.LoginSecure = false;
18                    oSQLServer.Connect(server, uid, pwd);
19                    oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
20                    oRestore.Database = database;
21                    /**////自行修改
22                    oRestore.Files = @"d:\aaa\aaa.bak";
23                    oRestore.FileNumber = 1;
24                    oRestore.ReplaceDatabase = true;
25                    oRestore.SQLRestore(oSQLServer);
26
27                    return "ok";
28                }
29                catch(Exception e)
30                {
31                    return "恢复数据库失败";
32                    throw e;
33                }
34                finally
35                {
36                    oSQLServer.DisConnect();
37                }
38            }
39        }
40        
41        /**//// <summary>
42        /// 杀死当前库的所有进程
43        /// </summary>
44        /// <returns></returns>
45        private bool exepro()
46        {
47
48            SqlConnection conn1 = new SqlConnection("server="+server+";uid="+uid+";pwd="+pwd+";database=master");
49            SqlCommand cmd = new SqlCommand("killspid",conn1);
50            cmd.CommandType = CommandType.StoredProcedure;
51            cmd.Parameters.Add("@dbname","aaa");
52            try
53            {
54                conn1.Open();
55                cmd.ExecuteNonQuery();
56                return true;
57            }
58            catch(Exception ex)
59            {
60                return false;
61            }
62            finally
63            {
64                conn1.Close();
65            }
66        }

 

相关存储过程

 

create proc p_killspid
@dbname varchar(200) --要关闭进程的数据库名
as
declare @sql nvarchar(500)
declare @spid nvarchar(20)

declare #tb cursor for
select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
open #tb
fetch next from #tb into @spid
while @@fetch_status=0
begin
exec('kill '+@spid)
fetch next from #tb into @spid
end
close #tb
deallocate #tb
go 

 

API 方式：

 

/// <summary>

/// 获取网内的数据库服务器名称

/// </summary>

public class SqlLocator

{

     [System.Runtime.InteropServices.DllImport("odbc32.dll")]

     private static extern short SQLAllocHandle(short hType, IntPtr inputHandle, out IntPtr outputHandle);

     [System.Runtime.InteropServices.DllImport("odbc32.dll")]

     private static extern short SQLSetEnvAttr(IntPtr henv, int attribute, IntPtr valuePtr, int strLength);

     [System.Runtime.InteropServices.DllImport("odbc32.dll")]

     private static extern short SQLFreeHandle(short hType, IntPtr handle);

     [System.Runtime.InteropServices.DllImport("odbc32.dll",CharSet= System.Runtime.InteropServices.CharSet.Ansi)]

     private static extern short SQLBrowseConnect(IntPtr hconn, System.Text.StringBuilder inString,

         short inStringLength, System.Text.StringBuilder outString, short outStringLength,

         out short outLengthNeeded); 

     private const short SQL_HANDLE_ENV = 1;
     private const short SQL_HANDLE_DBC = 2;
     private const int SQL_ATTR_ODBC_VERSION = 200;
     private const int SQL_OV_ODBC3 = 3;
     private const short SQL_SUCCESS = 0;
     private const short SQL_NEED_DATA = 99;
     private const short DEFAULT_RESULT_SIZE = 1024;
     private const string SQL_DRIVER_STR = "DRIVER=SQL SERVER";

     private SqlLocator(){}

     /// <summary>
     /// 获取网内的数据库服务器名称，是一个字符串数组。
     /// </summary>
     /// <returns></returns>
     public static string[] GetServers()
     {
         string list = string.Empty;
         IntPtr henv = IntPtr.Zero;
         IntPtr hconn = IntPtr.Zero;
         System.Text.StringBuilder inString = new System.Text.StringBuilder(SQL_DRIVER_STR);
         System.Text.StringBuilder outString = new System.Text.StringBuilder(DEFAULT_RESULT_SIZE);
         short inStringLength = (short) inString.Length;
         short lenNeeded = 0;
         try
         {
              if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_ENV, henv, out henv))
              {
                   if (SQL_SUCCESS == SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(IntPtr)SQL_OV_ODBC3,0))
                   {
                       if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_DBC, henv, out hconn))
                       {

                            if (SQL_NEED_DATA ==  SQLBrowseConnect(hconn, inString, inStringLength, outString,
                                 DEFAULT_RESULT_SIZE, out lenNeeded))
                            {

                                 if (DEFAULT_RESULT_SIZE < lenNeeded)
                                 {
                                     outString.Capacity = lenNeeded;
                                     if (SQL_NEED_DATA != SQLBrowseConnect(hconn, inString, inStringLength, outString,
                                          lenNeeded,out lenNeeded))
                                     {
                                          throw new ApplicationException("Unabled to aquire SQL Servers from ODBC driver.");
                                     }   
                                 }
                                 list = outString.ToString();
                                 int start = list.IndexOf("{ ") + 1;
                                 int len = list.IndexOf("}") - start;
                                 if ((start > 0) && (len > 0))
                                 {
                                     list = list.Substring(start,len);
                                 }
                                 else
                                 {
                                     list = string.Empty;
                                 }
                            }           
                        }
                   }
              }
         }
         catch
         {
              list = string.Empty;
         }
         finally
         {
              if (hconn != IntPtr.Zero)
              {
                   SQLFreeHandle(SQL_HANDLE_DBC,hconn);

              }
              if (henv != IntPtr.Zero)
              {
                   SQLFreeHandle(SQL_HANDLE_ENV,hconn);
              }
         }

         string[] array = null;

         if (list.Length > 0)

         {
              array = list.Split(',');
         }

         return array;

     }

}

 

 转载请注明：http://www.cnblogs.com/winzheng/archive/2009/04/11/1433724.html