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()); } } } }