private static void KillSqlProcess(SQLDMO.SQLServer svr, string DbName)
{
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;
}
}
//杀死使用DbName数据库的进程
for (int i = 1; i <= qr.Rows; i++)
{
int lPID = qr.GetColumnLong(i, iColPIDNum);
string strDBName = qr.GetColumnString(i, iColDbName);
if (strDBName.ToUpper() == DbName.ToUpper())
{
svr.KillProcess(lPID);
}
}
}
protected virtual void btnOK_Click(object sender, EventArgs e)
{
try
{
srv = new SQLDMO.SQLServer();
//tempo de conexao com o servidor
srv.LoginTimeout = 20;
if (chbSSIP.Checked)
{
srv.LoginSecure = true;
srv.Connect(".",null,null);
}
else
{
srv.Connect(".",tbLogin.Text,tbSenha.Text);
}
this.ParentForm.Text = srv.TrueLogin + "@" + srv.TrueName;
this.ParentForm.Tag = "true";
this.Close();
}
catch (Exception)
{
this.ParentForm.Tag = "false";
}
}
protected virtual void btnOK_Click(object sender, EventArgs e)
{
try
{
srv = new SQLDMO.SQLServer();
//tempo de conexao com o servidor
srv.LoginTimeout = 20;
if (chbSSIP.Checked)
{
srv.LoginSecure = true;
srv.Connect(".", null, null);
}
else
{
srv.Connect(".", tbLogin.Text, tbSenha.Text);
}
this.ParentForm.Text = srv.TrueLogin + "@" + srv.TrueName;
this.ParentForm.Tag = "true";
this.Close();
}
catch (Exception)
{
this.ParentForm.Tag = "false";
}
}
public SqlStringEditorDialog()
{
InitializeComponent();
this._scsb = new SqlConnectionStringBuilder();
this._sqlapp = new SQLDMO.ApplicationClass();
this._sqlservers = this._sqlapp.ListAvailableSQLServers();
this._server = new SQLDMO.SQLServerClass();
}
/// <summary>
/// 备份数据库
/// </summary>
public bool BackupDataBase(string strDbName, string strFileName, ProgressBar proBar)
{
SQLDMO.SQLServer sqlServer = new SQLDMO.SQLServer();
try
{
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["PSAP.Properties.Settings.PSAPConnectionString"].ConnectionString;
string tmpStr = "Data Source=";
string DataSource = connectionString.Substring(connectionString.IndexOf(tmpStr) + tmpStr.Length);
DataSource = DataSource.Substring(0, DataSource.IndexOf(";"));
tmpStr = "User ID=";
string UserID = connectionString.Substring(connectionString.IndexOf(tmpStr) + tmpStr.Length);
if (UserID.IndexOf(";") == -1)
{
UserID = UserID.Substring(0);
}
else
{
UserID = UserID.Substring(0, UserID.IndexOf(";"));
}
tmpStr = "Password="******";") == -1)
{
Password = Password.Substring(0);
}
else
{
Password = Password.Substring(0, Password.IndexOf(";"));
}
sqlServer.Connect(DataSource, UserID, Password);
SQLDMO.Backup backup = new SQLDMO.Backup();
proBar.Value = 0;
backup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
backup.Initialize = true;
backup.PercentComplete += new SQLDMO.BackupSink_PercentCompleteEventHandler(Backup_PercentComplete);
backup.Files = strFileName;
backup.Database = strDbName;
backup.PercentCompleteNotification = 1;
backup.SQLBackup(sqlServer);
proBar.Value = 100;
backup.PercentComplete -= new SQLDMO.BackupSink_PercentCompleteEventHandler(Backup_PercentComplete);
backup = null;
return(true);
}
catch (Exception err)
{
throw (new Exception("备份数据库失败" + err.Message));
}
finally
{
sqlServer.DisConnect();
}
}
//获取指定SQL服务器的全部数据库列表
public ArrayList GetDataBaseList(string ServerName, string UserName, string Pwd)
{
ArrayList list = new ArrayList();
SQLDMO.SQLServer oServer = new SQLDMO.SQLServer();
oServer.Connect(ServerName, UserName, Pwd);
foreach (SQLDMO.Database db in oServer.Databases)
{
if ((db.Name != null) && (db.SystemObject == false))
{
list.Add(db.Name);
}
}
return(list);
}
/// <summary>
/// 杀死此数据库的进程
/// </summary>
/// <param name="DatabaseName">数据库名称</param>
private static void KillProcesses(string DatabaseName)
{
SQLDMO.SQLServer oSQLServer = getSQL();;
SQLDMO.QueryResults qr = oSQLServer.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() == DatabaseName.ToUpper())
{
oSQLServer.KillProcess(lPID);
}
}
}
private string GetTablesScript(string dtName)
{
try
{
SQLDMO.SQLServer oserver = new SQLDMO.SQLServer();
string connStr = KingTop.Common.SQLHelper.ConnectionStringLocalTransaction;
string[] arrConn = connStr.Split(';');
string server = string.Empty;
string database = string.Empty;
string login = string.Empty;
string password = string.Empty;
for (int i = 0; i < arrConn.Length; i++)
{
string[] itemArr = arrConn[i].Split('=');
if (itemArr[0].ToLower() == "server")
{
server = itemArr[1];
}
else if (itemArr[0].ToLower() == "database")
{
database = itemArr[1];
}
else if (itemArr[0].ToLower() == "uid")
{
login = itemArr[1];
}
else if (itemArr[0].ToLower() == "pwd")
{
password = itemArr[1];
}
}
oserver.Connect(server, login, password);
SQLDMO._Database mydb = oserver.Databases.Item(database, "owner");
SQLDMO._Table mytable = mydb.Tables.Item(dtName, "dbo");
string tableScript = mytable.Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default, null, null, SQLDMO.SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default);
tableScript = tableScript.Replace("[nvarchar] (0)", "[nvarchar] (max)");
tableScript = tableScript.Replace("[varchar] (-1)", "[varchar] (max)");
//去掉GO
int lastPosNum = tableScript.LastIndexOf("GO");
if (lastPosNum > 0)
{
tableScript = tableScript.Substring(0, lastPosNum);
}
oserver.DisConnect();
//注释掉不需复制的字段
string[] arrFields = noCopyFields.Split(',');
for (int i = 0; i < arrFields.Length; i++)
{
if (!string.IsNullOrEmpty(arrFields[i]) && tableScript.ToLower().IndexOf("[" + arrFields[i].ToLower() + "]") != -1)
{
tableScript = Regex.Replace(tableScript, "\\[" + arrFields[i] + "\\]", "--[" + arrFields[i] + "]", RegexOptions.IgnoreCase);
}
}
return(tableScript.Replace("'", "''"));
}
catch
{
Response.Write("<div align=center style='padding:20px'>复制失败，原因是sqldmo.dll未注册，注册方法如下:<br><br> 打开开始，在运行中输入 regsvr32 \"C:\\Program Files\\Microsoft SQL Server\\80\\Tools\\Binn\\sqldmo.dll\" 注册sqldmo.dll。<br><br>在注册前请确认sqldmo.dll是否存在，不存在请从网上下载sqldmo.dll到相应目录，再进行注册");
Response.Write("<br><br><a href=# onclick='history.back();'>[返回]</a></div>");
Response.End();
return("");
}
}
/// <summary>
/// 使用SQL DMO恢复数据库
/// </summary>
/// <param name="serverName"></param>
/// <param name="userName"></param>
/// <param name="pwd"></param>
/// <param name="dbName"></param>
/// <param name="dbBakFileName"></param>
public void RestoreSqlDb(string serverName, string userName, string pwd, string dbName, string dbBakFileName)
{
string dbFileDir = string.Empty;
SQLDMO.SQLServer sqlServerObj = new SQLDMO.SQLServer();
SQLDMO.Database dbObj = new SQLDMO.Database();
SQLDMO.DBFile dbFileObj = new SQLDMO.DBFile();
SQLDMO.LogFile logFileObj = new SQLDMO.LogFile();
try
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
sqlServerObj.Connect(serverName, userName, pwd);
dbFileDir = sqlServerObj.Registry.SQLDataRoot + @"\DATA\";
if (!Directory.Exists(dbFileDir))
{
Directory.CreateDirectory(dbFileDir);
}
}
catch (Exception exp)
{
throw new Exception("无法连接到Sql Server，" + exp.Message, exp);
}
#region 属性设置
try
{
dbObj.Name = dbName;
dbFileObj.Name = dbBakFileName;
dbFileObj.PhysicalName = dbFileDir + dbName + ".mdf";
dbFileObj.PrimaryFile = true;
dbFileObj.Size = 3;
dbFileObj.FileGrowthType = SQLDMO.SQLDMO_GROWTH_TYPE.SQLDMOGrowth_MB;
dbFileObj.FileGrowth = 1;
dbObj.FileGroups.Item("PRIMARY").DBFiles.Add(dbFileObj);
logFileObj.Name = dbName + "Log";
logFileObj.PhysicalName = dbFileDir + dbName + "_log.ldf";
logFileObj.Size = 3;
dbObj.TransactionLog.LogFiles.Add(logFileObj);
sqlServerObj.Databases.Add(dbObj);
}
catch (Exception exp)
{
throw new Exception("数据库属性设置失败，" + exp.Message, exp);
}
#endregion
#region 数据库恢复操作
try
{
SQLDMO.Restore restoreObj = new SQLDMO.RestoreClass();
restoreObj.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
restoreObj.Database = dbName;
//是否覆盖原数据库
restoreObj.ReplaceDatabase = true;
restoreObj.Files = dbBakFileName;// @"\data\mdernode.bak";
//restoreObj.FileNumber = 1;
restoreObj.UnloadTapeAfter = true;
restoreObj.SQLRestore(sqlServerObj);
}
catch (Exception exp)
{
throw new Exception("数据库恢复失败，" + exp.Message, exp);
}
finally
{
sqlServerObj.DisConnect();
}
#endregion
}
/// <summary>
/// 备份指定数据库文件
/// </summary>
/// <param name="strDbName">数据库名称</param>
/// <param name="strFileName">备份数据库的路径</param>
/// <param name="strServerName">服务器名称</param>
/// <param name="strUserName">用户名</param>
/// <param name="strPassword">密码</param>
/// <param name="prosBar">进度条</param>
/// <returns></returns>
public static bool BackUPDB(string strDbName, string strFileName, string strServerName, string strUserName, string strPassword, ProgressBar bar)
{
#region [ 判断磁盘剩余空间是否可进行备份 ]
DBAcess dba = new DBAcess();
DataSet tmpds = dba.GetDataSet("exec sp_spaceused");
if (tmpds.Tables.Count > 0 && tmpds.Tables[0] != null)
{
// 得到数据库大小
string tmpStr = tmpds.Tables[0].Rows[0]["database_size"].ToString();
int dbSize = Convert.ToInt32(tmpStr.Substring(0, tmpStr.LastIndexOf(".")));
// 获取磁盘剩余空间大小
try
{
DriveInfo d = new DriveInfo(strFileName.Substring(0, 1));
int diskSize = Convert.ToInt32(d.AvailableFreeSpace / 1024 / 1024);
if (diskSize < dbSize)
{
MessageBox.Show("备份数据库所需空间不能小于" + dbSize + "M\r\n磁盘[" + d.Name + "]存储空间过小,无法备份");
return(false);
}
}
catch (Exception)
{
MessageBox.Show("路径不正确");
return(false);
//throw;
}
}
#endregion
pBar = bar;
string strTmp = "";
string tmpPath = strFileName.Substring(0, strFileName.LastIndexOf("\\")).ToString();
int isEmpty = tmpPath.IndexOf(" ");
SQLDMO.SQLServer svr = null;
try
{
svr = new SQLDMO.SQLServerClass();
// 连接到数据库
svr.Connect(strServerName, strUserName, strPassword);
SQLDMO.Backup bak = new SQLDMO.BackupClass();
bak.Action = 0;
bak.Initialize = true;
#region 进度条处理
if (pBar != null)
{
pBar.Visible = true;
SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);
bak.PercentComplete += pceh;
}
#endregion
#region [ 文件夹名称中有空格: 备份前的处理 ]
// 文件夹不存在时自动创建
if (!Directory.Exists(tmpPath))
{
Directory.CreateDirectory(tmpPath);
}
// 文件夹名称 中有空格 备份文件路径设置为根目录的临时文件夹tmpBackup中
if (isEmpty > 1 && strFileName.Substring(4).LastIndexOf("\\") > 1)
{
strTmp = strFileName.Substring(0, 1).ToString() + ":\\tmp_backup.kj";
}
else
{
strTmp = strFileName;
}
#endregion
// 数据库的备份的名称及文件存放位置
bak.Files = strTmp;
bak.Database = strDbName;
// 备份
bak.SQLBackup(svr);
}
catch (Exception err)
{
if (SqlErrRepair(err.Message.ToString()))
{
BackUPDB(strDbName, strFileName, strServerName, strUserName, strPassword, pBar);
return(true);
}
return(false);
//MessageBox.Show("备份数据库失败");
}
finally
{
if (svr != null)
{
svr.DisConnect();
}
#region [ 文件夹名称中有空格: 备份完成后的处理 ]
// 文件夹名称 中有空格 将备份的文件移动到用户指定的文件夹并将临时目录删除
if (isEmpty > 1 && strFileName.Substring(4).LastIndexOf("\\") > 1)
{
// 文件存在则替换
if (File.Exists(strFileName.Substring(strFileName.LastIndexOf("\\") + 2)))
{
File.Delete(strFileName.Substring(strFileName.LastIndexOf("\\") + 2));
}
File.Move(strTmp, strFileName);
}
#endregion
}
return(true);
}
/// <summary>
/// 选择数据库
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void DbName_Click(object sender, EventArgs e)
{
sr = new SQLDMO.SQLServerClass();
if (DbName.Items.Count < 1)
{
//DbName.Items.Clear();
if (UserName.Text == "" || Password.Text == "")
{
if (Password.Text == "" && UserName.Text == "")
{
try
{
sr.Connect(DbSource.Text.ToString(), null, null);
foreach (SQLDMO.Database db in sr.Databases)
{
if (db.Name != null)
DbName.Items.Add(db.Name);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
else if (Password.Text == "")
{
try
{
sr.Connect(DbSource.Text.ToString(), UserName.Text, null);
foreach (SQLDMO.Database db in sr.Databases)
{
if (db.Name != null)
DbName.Items.Add(db.Name);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
}
//else if (UserName.Text == "")
//{
// MessageBox.Show("请填写用户名，并重新登陆！");
//}
else
{
try
{
try
{
sr.Connect(DbSource.Text.ToString(), UserName.Text, Password.Text);
}
catch
{
}
foreach (SQLDMO.Database db in sr.Databases)
{
if (db.Name != null)
DbName.Items.Add(db.Name);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
}
}
