C # Database backup and restore implementation Code "reprint"

"Reprint" http://www.codesky.net/article/200908/128600.html

C # Database backup and restore 1. In the user's configuration, we need to list all the database servers in the current LAN, and to list all the databases of the specified server , the implementation code is as follows:

Get a list of database servers:

 PublicArrayList getserverlist () {ArrayList alservers=NewArrayList (); SQLDMO. Application Sqlapp=NewSQLDMO.   ApplicationClass (); Try{SQLDMO. NameList serverlist=sqlapp.listavailablesqlservers ();  for(inti =1; i<= serverlist.count;i++) {Alservers.add (Serverlist.item (i)); }   }   Catch(Exception e) {Throw(NewException ("error fetching database server list:"+e.message))   ; }   finally{sqlapp.quit (); }   returnalservers; }   

Gets the list of databases for the specified database server

 PublicArrayList Getdblist (stringstrServerName,stringstrUserName,stringstrpwd) {ServerName=strServerName; UserName=strUserName; Password=strpwd; ArrayList Aldbs=NewArrayList (); SQLDMO. Application Sqlapp=NewSQLDMO.   　　ApplicationClass (); SQLDMO. SQL Server SVR=NewSQLDMO.   　　Sqlserverclass (); Try{svr.   　　　　Connect (Servername,username,password); foreach(SQLDMO. Database DBinchSVR. Databases) {if(Db. name!=NULL) Aldbs.add (db.   　　　　Name); }}Catch(Exception e) {Throw(NewException ("Error connecting database:"+e.message))   　　; }finally{svr.   　　　　DisConnect ();   　　Sqlapp.quit (); }returnAldbs; }  

C # Database backup and restore 2. Database backup and real-time progress display code:

 Public BOOLBackupDB (stringstrDbName,stringstrFileName, ProgressBar pgbmain) {PBar=Pgbmain; SQLDMO. SQL Server SVR=NewSQLDMO.   　　Sqlserverclass (); Try{svr.   　　　　Connect (Servername,username,password); SQLDMO. Backup Bak=NewSQLDMO.   　　　　Backupclass (); Bak. Action=0 ; Bak. Initialize=true ; SQLDMO. Backupsink_percentcompleteeventhandler PCEH=NewSQLDMO.   　　　　Backupsink_percentcompleteeventhandler (Step); Bak. PercentComplete+=Pceh; Bak. Files=strFileName; Bak. Database=strDbName; Bak.   　　　　SQLBackup (SVR); return true ; }Catch(Exception err) {Throw(NewException ("Backup Database failed"+Err.   　　Message)); }finally{svr.   　　DisConnect (); }   }    Private voidStep (stringMessageintpercent) {Pbar.value=percent; }  

Where these two statements enable real-time display of progress:

SQLDMO. Backupsink_percentcompleteeventhandler PCEH = new SQLDMO. Backupsink_percentcompleteeventhandler (Step);
Bak. PercentComplete + = PCEH;

Step is the method name of the above private void Step (string message,int percent), which is used to display the current progress of the progress bar.

C # Database backup and restore 3. The code for the recovery and killing process of the database:

 Public BOOLRestoreDB (stringstrDbName,stringstrFileName, ProgressBar pgbmain) {PBar=Pgbmain; SQLDMO. SQL Server SVR=NewSQLDMO.   　　Sqlserverclass (); Try{svr.   　　　　Connect (Servername,username,password); SQLDMO. QueryResults QR= SVR. EnumProcesses (-1) ; intIcolpidnum =-1 ; intIcoldbname =-1 ;  for(intI=1; I<=qr. columns;i++) {stringStrName =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(intI=1; I<=qr. rows;i++) {intLpid =qr.   　　　　　　GetColumnLong (I,icolpidnum); stringstrDbName =qr.   　　　　　　GetColumnString (I,icoldbname); if(Strdbname.toupper () = =strdbname.toupper ()) SVR.   　　　　KillProcess (LPID); } SQLDMO. Restore Res=NewSQLDMO.   Restoreclass (); Res. Action=0 ; SQLDMO. Restoresink_percentcompleteeventhandler PCEH=NewSQLDMO.   Restoresink_percentcompleteeventhandler (Step); Res. PercentComplete+=Pceh; Res. Files=strFileName; Res. Database=strDbName; Res. ReplaceDatabase=true ; Res.   SQLRestore (SVR); return true ; }   Catch(Exception err) {Throw(NewException ("failed to recover the database, please close all the programs connected to the database! "+Err.   Message)); }   finally{svr.   DisConnect (); }   }  

Where this statement gets a list of all the processes:

SQLDMO. QueryResults qr = svr. EnumProcesses (-1);

The following statement finds and kills the processes associated with the database to be recovered:

intIcolpidnum =-1 ; intIcoldbname =-1 ;  for(intI=1; I<=qr. columns;i++)   {   stringStrName =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(intI=1; I<=qr. rows;i++)   {   intLpid =qr.   GetColumnLong (I,icolpidnum); stringstrDbName =qr.   GetColumnString (I,icoldbname); if(Strdbname.toupper () = =strdbname.toupper ()) SVR.   KillProcess (LPID); }