using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using Microsoft.Win32;
namespace JohnKenedy.DataAccess
{
public class BusinessMSDEInstaller
{
protected string _ComputerInstanceName = "";
protected string _ComputerName = "";
protected string _InstanceName = "";
protected string _MSDESourcePath = "MSDE2000";
protected string _MSDEInstallPath = Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles).Trim() + "\\MSDE2000";
public string MSDEInstallPath
{
get
{
return _MSDEInstallPath;
}
set
{
_MSDEInstallPath = value;
}
}
protected string _UserName = "sa";
protected string _SAPassword = "sa";
protected string _DatabaseName = "MyDatabase";
public BusinessMSDEInstaller(string _computerName, string _instanceName, string _databaseName)
{
if (_instanceName == "")
{
_ComputerInstanceName = "";
}
else
{
_ComputerInstanceName = _computerName + "\\" + _instanceName;
_InstanceName = _instanceName;
}
_DatabaseName = _databaseName;
}
protected string GetMSDESourceFullPath()
{
string _path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, _MSDESourcePath);
if (Directory.Exists(_path) == false) Directory.CreateDirectory(_path);
return _path;
}
#region Uninstall MSDE Methods
protected string GetInstalltionProductName()
{
string[] _instance = _ComputerInstanceName.Split('\\');
if (_instance.Length != 2) return "";
string _keyValue = "Microsoft SQL Server Desktop Engine (" + _instance[1] + ")";
return _keyValue;
}
protected RegistryKey GetRegistryBySubFolderKeyName(RegistryKey _registryKey, string _keyName, string _keyvalue)
{
string[] _subkeys = _registryKey.GetSubKeyNames();
if (_subkeys.Length <= 0) return null;
foreach (string _str in _subkeys)
{
RegistryKey _key = _registryKey.OpenSubKey(_str, true);
try
{
if (_key.GetValue(_keyName).ToString().ToLower() == _keyvalue.ToLower())
{
return _key;
}
}
catch{}
}
return null;
}
protected string GetRegistryValueNameByValue(RegistryKey _registryKey, string _value)
{
string[] _valueNames = _registryKey.GetValueNames();
foreach (string _name in _valueNames)
{
if (_registryKey.GetValue(_name).ToString().ToLower() == _value.ToLower())
{
return _name;
}
}
return "";
}
protected string GetRegistryShortName(string _name)
{
int _index = _name.LastIndexOf('\\');
string _result = _name.Substring(_index + 1);
return _result;
}
protected string GetInstallationPath(string _path)
{
// c:\saya.exe abc
// 012345678901234
int _index = _path.LastIndexOf(".exe");
string _filename = _path;
if (_index >= 0)
{
_filename = _path.Substring(0, _index + 3);
}
DirectoryInfo _info = Directory.GetParent(_filename);
return _info.FullName;
}
#endregion
public string GetCurrentSQLInstallationPath()
{
string _keyValue = GetInstalltionProductName();
string[] _instanceName = null;
if (_ComputerInstanceName != "") _instanceName = _ComputerInstanceName.Split('\\');
string _folderToDelete = "";
RegistryKey _instances = Registry.LocalMachine.OpenSubKey("Software").OpenSubKey("Microsoft", true);
RegistryKey _instance = null;
if (_ComputerInstanceName == "")
{
_instance = _instances.OpenSubKey("MSSQLServer", true);
_folderToDelete = GetInstallationPath(_instance.GetValue("ImagePath").ToString());
}
else
{
if (_instanceName.Length == 2)
{
_instances = _instances.OpenSubKey("Microsoft SQL Server", true);
_instance = _instances.OpenSubKey(_instanceName[1], true);
RegistryKey _folderDelete = _instance.OpenSubKey("Setup", true);
_folderToDelete = GetInstallationPath(_folderDelete.GetValue("SQLPath").ToString());
}
}
return _folderToDelete;
}
public bool IsDatabaseExist()
{
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
srv.Connect(_InstanceName, _UserName, _SAPassword);
foreach (SQLDMO.Database db in srv.Databases)
{
if (db.Name != null)
{
if (db.Name.Trim().ToLower() == _DatabaseName.Trim().ToLower()) return true;
}
}
return false;
}
public bool IsMSDEInstanceExist()
{
string _keyValue = GetInstalltionProductName();
RegistryKey _registry = Registry.ClassesRoot.OpenSubKey("Installer").OpenSubKey("Products");
RegistryKey _product = GetRegistryBySubFolderKeyName(_registry, "ProductName", _keyValue);
if (_product != null) return true;
return false;
}
public void Uninstall()
{
string _keyValue = GetInstalltionProductName();
string[] _instanceName = null;
if (_ComputerInstanceName != "") _instanceName = _ComputerInstanceName.Split('\\');
string _folderToDelete = "";
RegistryKey _product = null;
try
{
// Delete Product Registration
RegistryKey _products = Registry.ClassesRoot.OpenSubKey("Installer").OpenSubKey("Products", true);
_product = GetRegistryBySubFolderKeyName(_products, "ProductName", _keyValue);
if (_product != null) _products.DeleteSubKeyTree(GetRegistryShortName(_product.Name));
}
catch { }
try
{
// Delete Patches
RegistryKey _patches = Registry.ClassesRoot.OpenSubKey("Installer").OpenSubKey("Patches", true);
_patches.DeleteSubKeyTree(GetRegistryShortName(_product.Name));
}
catch { }
try
{
// Delete Instances
RegistryKey _instances = Registry.LocalMachine.OpenSubKey("Software").OpenSubKey("Microsoft", true);
RegistryKey _instance = null;
if (_ComputerInstanceName == "")
{
_instance = _instances.OpenSubKey("MSSQLServer", true);
_folderToDelete = GetInstallationPath(_instance.GetValue("ImagePath").ToString());
}
else
{
if (_instanceName.Length == 2)
{
_instances = _instances.OpenSubKey("Microsoft SQL Server", true);
_instance = _instances.OpenSubKey(_instanceName[1], true);
RegistryKey _folderDelete = _instance.OpenSubKey("Setup", true);
_folderToDelete = GetInstallationPath(_folderDelete.GetValue("SQLDataRoot").ToString());
}
}
_instances.DeleteSubKeyTree(GetRegistryShortName(_instance.Name));
}
catch{}
RegistryKey _uinstall = null;
try
{
// Delete Uninstall
RegistryKey _uinstalls = Registry.LocalMachine.OpenSubKey("Software").OpenSubKey("Microsoft").OpenSubKey("Windows").OpenSubKey("CurrentVersion").OpenSubKey("Uninstall", true);
_uinstall = GetRegistryBySubFolderKeyName(_uinstalls, "DisplayName", _keyValue);
_uinstalls.DeleteSubKeyTree(GetRegistryShortName(_uinstall.Name));
}
catch { }
try
{
// Delete InstanceComponent
RegistryKey _components = Registry.LocalMachine.OpenSubKey("Software").OpenSubKey("Microsoft").OpenSubKey("Microsoft SQL Server").OpenSubKey("Component Set", true);
string _component = GetRegistryValueNameByValue(_components, GetRegistryShortName(_uinstall.Name));
if (_component != "") _components.DeleteValue(_component);
}
catch { }
try
{
// Delete Services
RegistryKey _sqlServices = Registry.LocalMachine.OpenSubKey("SYSTEM").OpenSubKey("CurrentControlSet").OpenSubKey("Services", true);
RegistryKey _sqlService = null;
if (_ComputerInstanceName == "")
{
_sqlService = _sqlServices.OpenSubKey("MSSQLServer", true);
}
else
{
if (_instanceName.Length == 2)
{
_sqlService = _sqlServices.OpenSubKey("MSSQL$" + _instanceName[1], true);
}
}
_sqlServices.DeleteSubKeyTree(GetRegistryShortName(_sqlService.Name));
}
catch { }
try
{
// Delete Agents
RegistryKey _sqlAgents = Registry.LocalMachine.OpenSubKey("SYSTEM").OpenSubKey("CurrentControlSet").OpenSubKey("Services", true);
RegistryKey _sqlAgent = null;
if (_ComputerInstanceName == "")
{
_sqlAgent = _sqlAgents.OpenSubKey("SQLServerAgent", true);
}
else
{
if (_instanceName.Length == 2)
{
_sqlAgent = _sqlAgents.OpenSubKey("SQLAgent$" + _instanceName[1], true);
}
}
_sqlAgents.DeleteSubKeyTree(GetRegistryShortName(_sqlAgent.Name));
}
catch{ }
// Delete Source Files
Directory.Delete(_folderToDelete, true);
}
public int Install(string _optionalSAPassword, string _optionalInstallPath)
{
if (IsMSDEInstanceExist() == true)
{
throw new Exception("MSDE Instance name " + _InstanceName + "already exist");
}
if (_optionalSAPassword != "") _SAPassword = _optionalSAPassword;
if (_optionalInstallPath != "") _MSDEInstallPath = _optionalInstallPath;
string _msdeFullPath = GetMSDESourceFullPath();
if (File.Exists(Path.Combine(_msdeFullPath, "Setup.Exe")) == false)
{
throw new Exception("MSDE Installer does not exist in " + Path.Combine(_msdeFullPath, "Setup.exe"));
}
string _file = Path.Combine(_msdeFullPath, "setup.ini");
string _targetDir = "\"" + _MSDEInstallPath + "\\Binn\"";
string _dataDir = "\"" + _MSDEInstallPath + "\\Data\"";
if (File.Exists(_file)) File.Delete(_file);
FileStream fsOutput = new FileStream(_file, FileMode.Create, FileAccess.Write);
StreamWriter srOutput = new StreamWriter(fsOutput);
string s1;
s1 = "[Options] DISABLENETWORKPROTOCOLS=0 SECURITYMODE=SQL TARGETDIR=" + _targetDir + " DATADIR=" + _dataDir;
srOutput.WriteLine(s1.ToString());
srOutput.Close();
fsOutput.Close();
ProcessStartInfo psi = new ProcessStartInfo();
psi.FileName = Path.Combine(_msdeFullPath, "setup.exe");
psi.Arguments = "INSTANCENAME=" + _InstanceName + " SAPWD=" + _SAPassword + " DISABLENETWORKPROTOCOLS=0 SECURITYMODE=SQL TARGETDIR=" + _targetDir + " DATADIR=" + _dataDir + "/qb";
Process p = new Process();
p.StartInfo = psi;
p.Start();
p.WaitForExit();
return p.ExitCode;
}
public void BackupDatabase(string _dbname, string _backupName, string _optionalSAUsername, string _optionalSAPassword)
{
try
{
if (_optionalSAUsername != "") _UserName = _optionalSAUsername;
if (_optionalSAPassword != "") _SAPassword = _optionalSAPassword;
string _msdeFullPath = GetMSDESourceFullPath();
string _RealDBPath = Path.Combine(_msdeFullPath, _backupName);
if (File.Exists(_RealDBPath) == true) File.Delete(_RealDBPath);
SQLDMO.Backup a = new SQLDMO.BackupClass();
SQLDMO._SQLServer srv = new SQLDMO.SQLServerClass();
srv.Connect(_ComputerInstanceName, _UserName, _SAPassword);
a.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
a.Database = _dbname;
a.Files = "[" + _RealDBPath + "]";
a.BackupSetName = _dbname;
a.BackupSetDescription = "Database backup";
a.Initialize = true;
a.SQLBackup(srv);
srv.DisConnect();
}
catch (Exception ex)
{
throw new Exception("Library unhandle exception : " + ex.Message);
}
}
public void RestoreDatabase(string _optionalMDFFileName, string _optionalSAUsername, string _optionalSAPassword)
{
try
{
if (_optionalSAUsername != "") _UserName = _optionalSAUsername;
if (_optionalSAPassword != "") _SAPassword = _optionalSAPassword;
string _MSDEFullPath = GetMSDESourceFullPath();
string _RealDBPath = Path.Combine(_MSDEFullPath, "Application_Data.MDF");
if (_optionalMDFFileName != "") _RealDBPath = Path.Combine(_MSDEFullPath, _optionalMDFFileName);
if (File.Exists(_RealDBPath) == false)
{
throw new Exception("Cannot attach Database File '" + _RealDBPath + "'. Database file does not exist");
}
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
SQLDMO.Registry2 _reg = null;
srv.Connect(_ComputerInstanceName, _UserName, _SAPassword);
_reg = (SQLDMO.Registry2)srv.Registry;
string _directory = Path.GetDirectoryName(_RealDBPath);
_reg.BackupDirectory = _directory;
string _location = GetCurrentSQLInstallationPath();
_location = Path.Combine(_location, "MSSQL\\Data");
if (Directory.Exists(_location)) Directory.CreateDirectory(_location);
_location = Path.Combine(_location, _DatabaseName + "_Data.MDF");
string _location2 = _location.Replace("_Data.MDF", "_Log.LDF");
SQLDMO.Restore restore = new SQLDMO.Restore();
restore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
restore.Database = _DatabaseName;
restore.Files = "[" + Path.GetFileName(_RealDBPath) + "]";
SQLDMO.QueryResults _res = restore.ReadFileList(srv);
string _a11 = _res.GetColumnString(1, 1);
string _a12 = _res.GetColumnString(1, 2);
string _b21 = _res.GetColumnString(2, 1);
string _b22 = _res.GetColumnString(2, 2);
string _moveFilesString = "[" + _a11 + "],[" + _a12 + "],[" + _a11 + "],[" + _location + "]";
_moveFilesString += ",[" + _b21 + "],[" + _b22 + "],[" + _b21 + "],[" + _location2 + "]";
restore.RelocateFiles = _moveFilesString;
restore.FileNumber = 1;
restore.ReplaceDatabase = true;
restore.SQLRestore(srv);
srv.DisConnect();
}
catch (Exception ex)
{
throw new Exception("Library unhandle exception : " + ex.Message);
}
}
public void AttachDatabase(string _optionalMDFFileName, string _optionalSAUsername, string _optionalSAPassword)
{
if (_optionalSAUsername != "") _UserName = _optionalSAUsername;
if (_optionalSAPassword != "") _SAPassword = _optionalSAPassword;
string _msdeFullPath = GetMSDESourceFullPath();
string _RealDBPath = Path.Combine(_msdeFullPath, "Application_Data.MDF");
if (_optionalMDFFileName != "") _RealDBPath = Path.Combine(_msdeFullPath, _optionalMDFFileName);
if (File.Exists(_RealDBPath) == false)
{
throw new Exception("Cannot attach Database File '" + _RealDBPath + "'. Database file does not exist");
}
_RealDBPath = "[" + _RealDBPath + "]";
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
try
{
srv.Connect(_ComputerInstanceName, _UserName, _SAPassword);
srv.AttachDB(_DatabaseName, _RealDBPath);
srv.DisConnect();
}
catch (Exception ex)
{
throw new Exception("Library unhandle exception : " + ex.Message);
}
}
}
}