CEL_Payroll/Payroll.BO/Query Analyzer/DAAdministrator.cs

using System;
using System.Collections;

using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.Collections.Specialized;


namespace Payroll.BO
{
	public class DAAdministrator
	{
		private OleDbConnection _oCon=null;
		private const string CONFIG_CONNECTION_STRING = "LocalDataSource";
		private int _nRestoreAttempt=0;
		private string _sFilePath="";
		string _sDBName="";
		string _sBackupFilePath="";
		string _sToDataFilePath="";
		string _sToLogFilePath="";

		public DAAdministrator()
		{
			NameValueCollection values = (NameValueCollection)ConfigurationSettings.GetConfig("appParams"); 
			if (values == null)
			{
				throw new ConfigurationException("Server connection is not specified");
			}
			// read the database connection string from the configuration information and store it in the connection string property
			string sLocalDataSource = values[CONFIG_CONNECTION_STRING];
			if (sLocalDataSource == null)
			{
				throw new ConfigurationException("Server connection is not specified");
			}
			
			try
			{
				string sConString="Provider=SQLOLEDB;Data Source=" + sLocalDataSource + ";User ID=sa;Password='';Initial Catalog=master";
				_oCon=new OleDbConnection(sConString);
			}
			catch(Exception exp)
			{
				throw new Exception(exp.Message);
			}
		}

		public void DatabaseBackup(string sFilePath)
		{
			OleDbCommand oCmd=new OleDbCommand();
			string sSQL="BACKUP DATABASE GPDSTS TO DISK='"+sFilePath+"'"; 
			try
			{
				oCmd.Connection=_oCon;
				oCmd.CommandTimeout=5000;
				oCmd.Connection.Open();
				oCmd.CommandText=sSQL;
				oCmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				throw new Exception(e.Message);
			}
			finally
			{
				oCmd=null;
				_oCon.Close();
				_oCon=null;
			}
		}

		public void RestoreDatabase(string sFilePath)
		{
			_sFilePath=sFilePath;
			OleDbCommand oCmd=new OleDbCommand();
			string sSQL="RESTORE DATABASE GPDSTS FROM DISK='"+sFilePath+"' WITH REPLACE,RECOVERY";
		//	RESTORE DATABASE TestDB 
		//   FROM DISK = 'c:\Northwind.bak'
		//   WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
		//   MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'

			try
			{
				oCmd.Connection=_oCon;
				oCmd.CommandTimeout=200;
				oCmd.Connection.Open();
				oCmd.CommandText=sSQL;

				_nRestoreAttempt++;
				EGlobal.SetStatusBusy("Restoring attempt " + _nRestoreAttempt.ToString() + "....");
				oCmd.ExecuteNonQuery();
				EGlobal.SetStatusBusy("Restore Successfull");
			}
			catch(Exception e)
			{
				if(_nRestoreAttempt<=10)
				{
					oCmd=null;
					_oCon.Close();

					RestoreDatabase(_sFilePath);
				}
				else
				{
					oCmd=null;
					_oCon.Close();
					_oCon=null;
					throw new Exception(e.Message);
				}
			}
		}
		public void RestoreDatabase(string sDBName, string sBackupFilePath,string sToDataFilePath,string sToLogFilePath)
		{
			_sDBName=sDBName;
			_sBackupFilePath=sBackupFilePath;
			_sToDataFilePath=sToDataFilePath;
			_sToLogFilePath=sToLogFilePath;
			OleDbCommand oCmd=new OleDbCommand();
			_nRestoreAttempt++;
			string sSQL="";
				

			try
			{
				oCmd.Connection=_oCon;
				oCmd.CommandTimeout=200;
				oCmd.Connection.Open();
				oCmd.CommandText="SELECT name FROM master.dbo.sysdatabases WHERE name = N'"+sDBName+ "'";
				object obj=oCmd.ExecuteScalar();

				if(obj != DBNull.Value && obj !=null)
				{
					throw new Exception("There is already a database exist with the name " + sDBName +"\nTo restore the database on existing database, check out the 'With file move'" );
				}
				
				string sDataFile=sDBName + "_" + "Data";
				string sLogFile=sDBName + "_" + "Log";
				
				
				OleDbDataAdapter oADP=new OleDbDataAdapter("RESTORE FILELISTONLY FROM DISK = '" + sBackupFilePath + "'" ,_oCon);
				DataSet oDSFilelist=new DataSet();
				oADP.Fill(oDSFilelist);

				string sBKDataFile="";
				string sBKLogFile="";

				if(oDSFilelist !=null && oDSFilelist.Tables.Count>0)
				{
					if(oDSFilelist.Tables[0].Rows !=null && oDSFilelist.Tables[0].Rows.Count>0)
					{
						sBKDataFile=oDSFilelist.Tables[0].Rows[0][0].ToString();
						sBKLogFile=oDSFilelist.Tables[0].Rows[1][0].ToString();
					}
				}

				sSQL="RESTORE DATABASE " + sDBName 
					  + "  FROM DISK = '" + sBackupFilePath +"' "
					  + "  WITH MOVE '" + sBKDataFile + "' TO '" + sToDataFilePath + @"\" + sDataFile +".mdf"+"', "
					  + "  MOVE '" + sBKLogFile + "' TO '" + sToLogFilePath + @"\" + sLogFile + ".ldf"  + "' ";
				
				oCmd.CommandText=sSQL;

				
				EGlobal.SetStatusBusy("Restoring attempt " + _nRestoreAttempt.ToString() + "....");
				oCmd.ExecuteNonQuery();
				EGlobal.SetStatusBusy("Restored Successfully");
			}
			catch(Exception e)
			{
				if(_nRestoreAttempt<=10)
				{
					oCmd=null;
					_oCon.Close();

					RestoreDatabase(_sDBName,_sBackupFilePath,_sToDataFilePath,_sToLogFilePath);
				}
				else
				{
					oCmd=null;
					_oCon.Close();
					_oCon=null;
					throw new Exception(e.Message);
				}
			}
		}
		public ArrayList GetDataBases()
		{
			ArrayList oDBList=new ArrayList();
			OleDbDataAdapter oADP;
			string sSQL="sp_databases"; 
			try
			{
				oADP=new OleDbDataAdapter(sSQL,_oCon);
				DataTable oTable=new DataTable("DBs");
				_oCon.Open();
				oADP.Fill(oTable);
				if(oTable !=null && oTable.Rows.Count>0)
				{
					foreach(DataRow oRow in oTable.Rows)
					{
						oDBList.Add(oRow[0]);
					}
				}
			}
			catch(Exception e)
			{
				throw new Exception("Failed get databases : " + e.Message);
			}
			finally
			{
				
				_oCon.Close();
				_oCon=null;
			}
			return oDBList;
		}

		public void AttachSingleDB(string sFilePath, string sDBName)
		{
			EGlobal.SetStatusBusy("Restoring ....");
			_sFilePath=sFilePath;
			OleDbCommand oCmd=new OleDbCommand();
			oCmd.Connection=_oCon;
			oCmd.CommandTimeout=200;
			oCmd.Connection.Open();

					
			try
			{

				oCmd.CommandText="SELECT name FROM master.dbo.sysdatabases WHERE name = N'" + sDBName+ "'";
				object obj=oCmd.ExecuteScalar();
			
				string sSQL="";
				if(obj != DBNull.Value && obj !=null)
				{
					oCmd.CommandText="EXEC sp_detach_db @dbname = '" + sDBName + "'";
					oCmd.ExecuteNonQuery();

					oCmd.CommandText="EXEC sp_attach_single_file_db @dbname = '" + sDBName + "', "
						+ " @physname = '"+ sFilePath + "'";
					oCmd.ExecuteNonQuery();

				}
				else
				{
					oCmd.CommandText="exec sp_attach_single_file_db '" + sDBName + "','"+ sFilePath +"'";
					oCmd.ExecuteNonQuery();
				}

				
				EGlobal.SetStatusBusy("Attached Successfully");
			}
			catch(Exception e)
			{
				throw new Exception("Failed to Attach : " + e.Message);
			}
			finally
			{
				oCmd=null;
				_oCon.Close();
				_oCon=null;
			}
		}
	}
}