/// <summary>
/// 数据库还原
/// </summary>
/// <param name="url">备份文件的地址</param>
/// <returns></returns>
public bool DbRestore(string url)
{
if (exepro() != true)//执行存储过程
{
return(false);
}
else
{
SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(server, uid, pwd);
oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
oRestore.Database = database;
oRestore.Files = url;//@"d:\Northwind.bak";
oRestore.FileNumber = 1;
oRestore.ReplaceDatabase = true;
oRestore.SQLRestore(oSQLServer);
return(true);
}
catch
{
return(false);
}
finally
{
oSQLServer.DisConnect();
}
}
}
/// <summary>
/// database backup
/// </summary>
/// <returns>Backup is successful</returns>
public bool DbBackup()
{
CreatePath();
SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(server, uid, pwd);
oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);
oBackup.PercentComplete += pceh;
oBackup.Database = database;
oBackup.Files = backPath;
oBackup.BackupSetName = database;
oBackup.BackupSetDescription = "Database Backup";
oBackup.Initialize = true;
oBackup.SQLBackup(oSQLServer);
return(true);
}
catch (Exception ex)
{
return(false);
throw ex;
}
finally
{
oSQLServer.DisConnect();
}
}
protected void Save_Click(object sender, EventArgs e)
{
//try
//{
SQLDMO.Backup backup = new SQLDMO.BackupClass();
SQLDMO.SQLServer sqlserver = new SQLDMO.SQLServerClass();
sqlserver.LoginSecure = false;
sqlserver.Connect("" + sUrl + "", "" + sUser + "", "" + sPwd + ""); //连接
backup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
backup.Database = "" + sName + ""; //数据库
string str1 = this.dburl.Text;
backup.Files = str1; //路径
backup.BackupSetName = "" + sDbUrl + ""; //数据库名
backup.BackupSetDescription = "数据库备份";
backup.Initialize = true;
backup.SQLBackup(sqlserver);
SqlHelper.ExecuteSql("INSERT INTO hk_DbBackUp(DbName,DbUrl,UserID,UserName,SetTimes) VALUES('" + sDbUrl + "','" + hkdb.GetStr(dburl.Text) + "','" + this.Session["userid"] + "','" + this.Session["username"] + "',GETDATE())");
//日志
mydb.InsertLog("备份数据库【" + sDbUrl + "】", "60109");
mydb.Alert_Refresh_Close("备份成功！", "Sjkbf.aspx");
//}
//catch
//{
// mydb.Alert("备份失败，请检查备份路径及相关参数后重试！");
//}
}
/// <summary>
/// SQL数据库备份
/// </summary>
/// <param name="ServerIP">SQL服务器IP或(Localhost)</param>
/// <param name="LoginName">数据库登录名</param>
/// <param name="LoginPass">数据库登录密码</param>
/// <param name="DBName">数据库名</param>
/// <param name="BackPath">备份到的路径</param>
public static bool SQLBACK(string ServerIP, string LoginName, string LoginPass, string DBName, string BackPath)
{
SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(ServerIP, LoginName, LoginPass);
oBackup.Database = DBName;
oBackup.Files = BackPath;
oBackup.BackupSetName = DBName;
oBackup.BackupSetDescription = "数据库备份";
oBackup.Initialize = true;
oBackup.SQLBackup(oSQLServer);
return(true);
}
catch (Exception)
{
return(false);
}
finally
{
oSQLServer.DisConnect();
}
}
/// <summary>
/// SQL恢复数据库
/// </summary>
/// <param name="ServerIP">SQL服务器IP或(Localhost)</param>
/// <param name="LoginName">数据库登录名</param>
/// <param name="LoginPass">数据库登录密码</param>
/// <param name="DBName">要还原的数据库名</param>
/// <param name="BackPath">数据库备份的路径</param>
public static bool SQLDbRestore(string ServerIP, string LoginName, string LoginPass, string DBName, string BackPath)
{
SQLDMO.Restore orestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(ServerIP, LoginName, LoginPass);
orestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
orestore.Database = DBName;
orestore.Files = BackPath;
orestore.FileNumber = 1;
orestore.ReplaceDatabase = true;
orestore.SQLRestore(oSQLServer);
return(true);
}
catch (Exception)
{
return(false);
}
finally
{
oSQLServer.DisConnect();
}
}
/**/
/// <summary>
/// 构造文件名
/// </summary>
/// <returns>文件名</returns>
public string CreatePath()
{
return(HttpContext.Current.Server.MapPath(path).ToString());
}
/// <summary>
/// 切割字符串
/// </summary>
/// <param name="str"></param>
/// <param name="bg"></param>
/// <param name="ed"></param>
/// <returns></returns>
public string StringCut(string str, string bg, string ed)
{
string sub;
sub = str.Substring(str.IndexOf(bg) + bg.Length);
sub = sub.Substring(0, sub.IndexOf(";"));
return(sub);
}
/**/
/// <summary>
/// 数据库备份
/// </summary>
/// <returns>备份是否成功</returns>
public bool DbBackup()
{
string path = CreatePath();
SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(server, uid, pwd);
oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
oBackup.Database = database;
oBackup.Files = path;
oBackup.BackupSetName = database;
oBackup.BackupSetDescription = "数据库备份";
oBackup.Initialize = true;
oBackup.SQLBackup(oSQLServer);
return(true);
}
catch (Exception ex)
{
return(false);
throw ex;
}
finally
{
oSQLServer.DisConnect();
}
}
//取得指定数据库服务器的数据库列表
public ArrayList GetDbList(string strServerName, string strUserName, string strPwd)
{
ArrayList alDbs = new ArrayList();
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(strServerName, strUserName, strPwd);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString().Trim());
return(alDbs);
}
foreach (SQLDMO.Database db in svr.Databases)
{
if (db.Name != null)
{
alDbs.Add(db.Name);
}
}
svr.DisConnect();
sqlApp.Quit();
return(alDbs);
}
/// <summary>
/// 备份
/// </summary>
/// <param name="url">备份的地址 必须是绝对路径 </param>
/// <returns></returns>
public bool DbBackup(string url)
{
SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(server, uid, pwd);
oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
oBackup.Database = database;
oBackup.Files = url;//"d:\Northwind.bak";
oBackup.BackupSetName = database;
oBackup.BackupSetDescription = "数据库备份";
oBackup.Initialize = true;
oBackup.SQLBackup(oSQLServer);
return true;
}
catch
{
return false;
throw;
}
finally
{
oSQLServer.DisConnect();
}
}
/// <summary>
/// SQL恢复数据库
/// </summary>
/// <param name="ServerIP">SQL服务器IP或(Localhost)</param>
/// <param name="LoginName">数据库登录名</param>
/// <param name="LoginPass">数据库登录密码</param>
/// <param name="DBName">要还原的数据库名</param>
/// <param name="BackPath">数据库备份的路径</param>
public static void SQLDbRestore(string ServerIP, string LoginName, string LoginPass, string DBName, string BackPath)
{
SQLDMO.Restore orestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
KillProcesses(DBName); //杀死此数据库的进程
oSQLServer.LoginSecure = false;
oSQLServer.Connect(ServerIP, LoginName, LoginPass);
orestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
orestore.Database = DBName;
orestore.Files = BackPath;
orestore.FileNumber = 1;
orestore.ReplaceDatabase = true;
orestore.SQLRestore(oSQLServer);
}
catch (Exception e)
{
throw new Exception(e.ToString());
}
finally
{
oSQLServer.DisConnect();
}
}
/// <summary>
/// 返回数据库列表
/// </summary>
/// <returns></returns>
public ArrayList GetDbList()
{
ArrayList alDbs = new ArrayList();
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
foreach (SQLDMO.Database db in svr.Databases)
{
if (db.Name != null)
{
alDbs.Add(db.Name);
}
}
}
catch (Exception e)
{
throw (new Exception("连接数据库出错：" + e.Message));
}
finally
{
svr.Close();
sqlApp.Quit();
}
return(alDbs);
}
//Get the database list
public ArrayList GetDbList()
{
string ServerName = server;
string UserName = uid;
string Password = pwd;
ArrayList alDbs = new ArrayList();
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
foreach (SQLDMO.Database db in svr.Databases)
{
if (db.Name != null)
{
alDbs.Add(db.Name);
}
//listBox2.Items.Add(db.Name);
}
}
catch (Exception e)
{
MessageBox.Show("There was an error connecting to the database：" + e.Message);
}
finally
{
svr.DisConnect();
sqlApp.Quit();
}
return(alDbs);
}
public string GetStoredText(string DataBaseName, string StoredName)
{
string getStoredText = "";
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
SQLDMO.Database dbs = new SQLDMO.DatabaseClass();
dbs = (SQLDMO.Database)svr.Databases.Item(DataBaseName, "owner");
foreach (SQLDMO.StoredProcedure tb in dbs.StoredProcedures)
{
if (tb.Name == StoredName)
{
getStoredText = tb.Text;
}
}
}
catch (Exception err)
{
//throw (new Exception("！" + err.Message));
ShowError("修改存储过程失败!" + err.Message);
getStoredText = "";
}
finally
{
svr.DisConnect();
}
return(getStoredText);
}
//数据库备份
public static void DbBackup(string savefilepath)
{
SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = true;
oSQLServer.Connect(".", "sa", "sa");//服务器名、账号、密码
oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
oBackup.Database = "EatingTradDB";
oBackup.Files = savefilepath;
oBackup.BackupSetName = "EatingTradDB";
oBackup.BackupSetDescription = "数据库备份";
oBackup.Initialize = true;
oBackup.SQLBackup(oSQLServer);
}
catch
{
throw;
}
finally
{
oSQLServer.DisConnect();
}
}
/// <summary>
/// 备份
/// </summary>
/// <param name="url">备份的地址 必须是绝对路径 </param>
/// <returns></returns>
public bool DbBackup(string url)
{
SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(server, uid, pwd);
oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
oBackup.Database = database;
oBackup.Files = url;//"d:\Northwind.bak";
oBackup.BackupSetName = database;
oBackup.BackupSetDescription = "数据库备份";
oBackup.Initialize = true;
oBackup.SQLBackup(oSQLServer);
return(true);
}
catch
{
return(false);
throw;
}
finally
{
oSQLServer.DisConnect();
}
}
/// <summary>
/// 数据库的备份和实时进度显示
/// </summary>
/// <param name="strDbName"></param>
/// <param name="strFileName"></param>
/// <param name="pgbMain"></param>
/// <returns></returns>
public bool BackUPDB(string strDbName, string strFileName)
{
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
SQLDMO.Backup bak = new SQLDMO.BackupClass();
bak.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
bak.Initialize = true;
bak.Files = strFileName;
bak.Database = strDbName;
bak.SQLBackup(svr);
return(true);
}
catch (Exception err)
{
//throw (new Exception("备份数据库失败" + err.Message));
ShowError("连接数据库出错：" + err.Message);
return(false);
}
finally
{
svr.DisConnect();
}
}
public ArrayList GetStored(string DatabaseName)
{
ArrayList alDbs = new ArrayList();
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
SQLDMO.Database mydb = new SQLDMO.DatabaseClass();
mydb = (SQLDMO.Database)svr.Databases.Item(DatabaseName, "owner");
foreach (SQLDMO.StoredProcedure db in mydb.StoredProcedures)
{
if (db.Name != null)
{
alDbs.Add(db.Name);
}
}
}
catch (Exception e)
{
//throw (new Exception("连接数据库出错：" + e.Message));
ShowError("连接数据库出错：" + e.Message);
return(null);
}
finally
{
svr.DisConnect();
sqlApp.Quit();
}
return(alDbs);
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void loginOk_Click(object sender, EventArgs e)
{
SQLDMO.SQLServer SqlEXPRESS = new SQLDMO.SQLServerClass();
//组合SQLEXPRESS服务器名称
string sqlServer = txtSQL.Text.ToString();
string uid = txtUid.Text.ToString();
string pwd = txtPwd.Text.ToString();
try
{
SqlEXPRESS.Connect(sqlServer, uid, pwd);
Session["SqlServer"] = sqlServer;
Session["user"] = uid;
Session["password"] = pwd;
Response.Redirect("mainServer.aspx");
}
catch
{
Response.Write("服务器连接错误");
for (int i = 0; i < this.Controls.Count; i++)
{
this.Controls[i].Visible = false;
}
}
}
/// <summary>
/// SQL数据库备份
/// </summary>
/// <param name="filename">要备份到的文件全路径</param>
/// <param name="connStrBuilder">连接字符串构造器</param>
/// <param name="Backup_PercentComplete">进度</param>
/// <param name="oBackup">数据库备份服务对象</param>
/// <param name="remark">备份备注</param>
public bool SQLDbBackup(string filename, SqlConnectionStringBuilder connStrBuilder, SQLDMO.BackupSink_PercentCompleteEventHandler Backup_PercentComplete, out SQLDMO.Backup oBackup, string remark)
{
string ServerIP = connStrBuilder.DataSource;
string LoginUserName = connStrBuilder.UserID;
string LoginPass = connStrBuilder.Password;
string DBName = connStrBuilder.InitialCatalog;
FileInfo fi = new FileInfo(filename);
string DBFile = fi.Name.Substring(0, fi.Name.Length - fi.Extension.Length);
oBackup = new SQLDMO.BackupClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(ServerIP, LoginUserName, LoginPass);
oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
oBackup.PercentComplete += Backup_PercentComplete;
oBackup.Database = DBName;
oBackup.Files = @"" + string.Format("[{0}]", filename) + "";
oBackup.BackupSetName = DBFile;
oBackup.BackupSetDescription = "备份集" + DBFile;
oBackup.Initialize = true;
oBackup.SQLBackup(oSQLServer);
return(true);
}
catch (Exception e)
{
MessageBox.Show("数据库备份失败：" + e.Message);
return(false);
}
finally
{
oSQLServer.DisConnect();
}
}
/// <summary>
/// 还原bak类型备份的数据库函数
/// </summary>
/// <param name="strDbName">数据库名</param>
/// <param name="strFileName">数据库备份文件的完整路径名</param>
/// <returns></returns>
public bool RestoreDB(string strDbName, string strFileName, string userid, string pwd)
{
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
//服务器名，数据库用户名，数据库用户名密码
svr.Connect(M_DataBaseManageConfig.Server, userid, pwd);
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;
}
}
//杀死使用strDbName数据库的进程
for (int i = 1; i <= qr.Rows; i++)
{
int lPID = qr.GetColumnLong(i, iColPIDNum);
string strDBName = qr.GetColumnString(i, iColDbName);
if (strDBName.ToUpper() == strDbName.ToUpper())
{
svr.KillProcess(lPID);
}
}
SQLDMO.Restore res = new SQLDMO.RestoreClass();
res.Action = 0;
res.Files = strFileName;
res.Database = strDbName;
res.ReplaceDatabase = true;
res.SQLRestore(svr);
return(true);
}
catch
{
return(false);
}
finally
{
svr.DisConnect();
}
}
/// <summary>
/// 获得SQL实例
/// </summary>
/// <returns></returns>
private static SQLDMO.SQLServer getSQL()
{
var Host = WebTools.GetAppConfig("HostName");
var UID = WebTools.GetAppConfig("UID");
var Pwd = WebTools.GetAppConfig("Pwd");
SQLDMO.SQLServer sqlserver = new SQLDMO.SQLServerClass();
sqlserver.LoginSecure = false;
sqlserver.Connect(Host, UID, Pwd);
return(sqlserver);
}
public bool InsertTable(string DatabaseName, string TableName, DataTable dt)
{
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
string keyName = "";
try
{
svr.Connect(ServerName, UserName, Password);
SQLDMO.Database myDb = new SQLDMO.DatabaseClass();
myDb = (SQLDMO.Database)svr.Databases.Item(DatabaseName, "owner");
SQLDMO.Table myTb = new SQLDMO.TableClass();
myTb.Name = TableName;
foreach (DataRow dr in dt.Rows)
{
SQLDMO.Column column = new SQLDMO.ColumnClass();
column.Name = dr["ColumnName"].ToString();
column.Datatype = dr["ColumnType"].ToString();
column.Length = int.Parse(dr["ColumnSize"].ToString());
if (bool.Parse(dr["ColumnKey"].ToString()))
{
keyName = column.Name;
}
column.AllowNulls = bool.Parse(dr["ColumnNull"].ToString());
myTb.Columns.Add(column);
}
if (keyName != "")
{
SQLDMO.Key key = new SQLDMO.KeyClass();
key.Name = keyName;
key.Type = SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Primary;
key.KeyColumns.Add(keyName);
myTb.Keys.Add(key);
}
myDb.Tables.Add(myTb);
return(true);
}
catch (Exception e)
{
//throw (new Exception("连接数据库出错：" + e.Message));
ShowError("添加数据库失败!" + e.Message);
return(false);
}
finally
{
svr.DisConnect();
sqlApp.Quit();
}
}
private void btnRestore_Click(object sender, EventArgs e)
{
if (textBoxLJHF.Text == "")
{
MessageBox.Show("请确定数据库备份地址", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
if (MessageBox.Show("恢复数据库会覆盖当前数据，建议建立新数据库后进行此操作，继续进行恢复操作吗？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.No)
{
return;
}
SQLDMO.Restore restore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer server = new SQLDMO.SQLServerClass();
server.Connect(strDataBaseAddr, strDataBaseUser, strDataBasePass);
//KILL DataBase Process
sqlConn.ConnectionString = strConn;
sqlConn.Open();
sqlComm.CommandText = "use master Select spid FROM sysprocesses ,sysdatabases Where sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='" + strDataBaseName + "'";
sqldr = sqlComm.ExecuteReader();
while (sqldr.Read())
{
server.KillProcess(Convert.ToInt32(sqldr[0].ToString()));
}
sqldr.Close();
sqlConn.Close();
try
{
restore.Action = 0;
SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step2);
restore.PercentComplete += pceh;
restore.Database = strDataBaseName;
restore.Files = @textBoxLJHF.Text;
restore.ReplaceDatabase = true;
restore.SQLRestore(server);
MessageBox.Show("数据库恢复成功！");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
server.DisConnect();
toolStripProgressBar1.Value = 0;
}
}
//确定
protected void Save_Click(object sender, EventArgs e)
{
if (exepro() != true)
{
Response.Write("执行失败，请重试！");
}
else
{
SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect("" + sUrl + "", "" + sUser + "", "" + sPwd + "");
oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
oRestore.Database = "" + sName + "";
oRestore.Files = sDbUrl;
oRestore.FileNumber = 1;
oRestore.ReplaceDatabase = true;
oRestore.SQLRestore(oSQLServer);
//因进程被kill重连db
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["haokeConnectionString"].ConnectionString);
sqlConn.Open();
SqlCommand sqlComm = new SqlCommand("INSERT INTO hk_DbRecovery(DbName,DbUrl,UserID,UserName,SetTimes) VALUES('" + sDbName + "','" + sDbUrl + "','" + this.Session["userid"] + "','" + this.Session["username"] + "',GETDATE())", sqlConn);
sqlComm.CommandType = System.Data.CommandType.Text;
sqlComm.ExecuteNonQuery();
//日志
SqlCommand sqlComm2 = new SqlCommand("INSERT INTO hk_SystemLog(LogContent,ModuId,UserID,UserName,Nowtime,IPAddress,DeptID,DeptName,QxStr) VALUES('还原数据库【" + sDbName + "】','" + 60109 + "','" + this.Session["userid"] + "','" + this.Session["username"] + "',GETDATE(),'" + Request.UserHostAddress + "','" + this.Session["deptid"] + "','" + this.Session["deptname"] + "','" + this.Session["deptqx"] + "')", sqlConn);
sqlComm2.CommandType = System.Data.CommandType.Text;
sqlComm2.ExecuteNonQuery();
sqlConn.Close();
mydb.Alert_Close("还原数据库成功！");
}
catch
{
mydb.Alert("还原数据库失败，请重试！");
}
finally
{
oSQLServer.DisConnect();
}
}
}
public bool CreateDB(string dbName, string path)
{
//SQLDMO.SQLServer.EnumDirectories(string path);
// 创建数据库文件
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
SQLDMO.DBFile dbFile = new SQLDMO.DBFileClass();
try
{
svr.Connect(ServerName, UserName, Password);
svr.EnumDirectories("c:");
dbFile.Name = dbName + "_Data";
dbFile.PhysicalName = Path.Combine(path, dbName + "_Data.MDF");
dbFile.PrimaryFile = true;
//dbFile.Size = 2; // 设置初始化大小(MB)
//dbFile.FileGrowthType = SQLDMO_GROWTH_TYPE.SQLDMOGrowth_MB; // 设置文件增长方式
//dbFile.FileGrowth=1; // 设置增长幅度
// 创建日志文件
SQLDMO._LogFile logFile = new SQLDMO.LogFileClass();
logFile.Name = dbName + "_Log";
logFile.PhysicalName = Path.Combine(path, dbName + "_Log.MDF");
//logFile.Size = 3;
//logFile.FileGrowthType=SQLDMO_GROWTH_TYPE.SQLDMOGrowth_MB;
//logFile.FileGrowth=1;
// 创建数据库
SQLDMO.Database db = new SQLDMO.DatabaseClass();
db.Name = dbName;
db.FileGroups.Item("PRIMARY").DBFiles.Add(dbFile);
db.TransactionLog.LogFiles.Add(logFile);
// 建立数据库联接，并添加数据库到服务器
svr.Databases.Add(db);
return(true);
}
catch (Exception err)
{
//throw (new Exception("！" + err.Message));
ShowError("添加数据库失败!" + err.Message);
return(false);
}
finally
{
svr.DisConnect();
}
}
/// <summary>
/// Kill all the processes of the current library
/// </summary>
/// <returns></returns>
private bool exepro()
{
bool success = true;
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(server, uid, pwd);
//Get all the list of processes
SQLDMO.QueryResults qr = svr.EnumProcesses(-1);
int iColPIDNum = -1;
int iColDbName = -1;
//Find and restore database-related processes
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;
}
}
//The relevant process will be closed
for (int i = 1; i <= qr.Rows; i++)
{
int lPID = qr.GetColumnLong(i, iColPIDNum);
string strDBName = qr.GetColumnString(i, iColDbName);
if (strDBName.ToUpper() == database)
{
svr.KillProcess(lPID);
}
}
}
catch (Exception ex)
{
success = false;
}
return(success);
}
private bool IsDataBaseExist(string strDataBase)
{
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
srv.Connect(strServer, strUser, strPass);
bool ret = false;
for (int i = 0; i < srv.Databases.Count; i++)
{
if (srv.Databases.Item(i + 1, "dbo").Name == strDataBase)
{
ret = true;
break;
}
}
srv.DisConnect();
return(ret);
}
public static bool backupDB(string strdbname, string strfilename)
{
string conStr = ConfigurationManager.AppSettings["SqlConString"].ToString();
string[] con = conStr.Split(';');
int nameIndex = con[2].IndexOf('=') + 1;
string username = con[2].Substring(nameIndex, con[2].Length - nameIndex);
int pwdIndex = con[3].IndexOf('=') + 1;
string pwd = con[3].Substring(pwdIndex, con[3].Length - pwdIndex);
int ipIndex = con[0].IndexOf('=') + 1;
string ip = con[0].Substring(ipIndex, con[0].Length - ipIndex);
string server = ip;
string uid = username;
string password = pwd;
// 引用 DLL 文件，需要设置项目目标框架为 .NET framewo 2.0
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(server, uid, password);
SQLDMO.Backup bak = new SQLDMO.BackupClass();
bak.Action = 0;
bak.Initialize = true;
//SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(step);
//bak.PercentComplete += pceh;
bak.Files = strfilename;
bak.Database = "CarSecretary";
bak.SQLBackup(svr);
return(true);
}
catch (Exception ex)
{
//CarLog.WriteLog(ex.Message);
//MessageBox.Show("备份数据库失败,请确保没有其他用户使用数据库!!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(false);
}
finally
{
svr.DisConnect();
}
}
//private void btnSearchServer_Click(object sender, EventArgs e)
//{
// cboServers.Items.Clear();
// SQLDMO.ApplicationClass sqlApp = new SQLDMO.ApplicationClass();
// SQLDMO.NameList namelist;
// namelist = sqlApp.ListAvailableSQLServers();
// for(int i=0;i<namelist.Count;i++)
// {
// object obj = namelist.Item(i);
// if (obj != null)
// {
// this.cboServers.Items.Add(obj);
// }
// }
// if (this.cboServers.Items.Count > 0)
// this.cboServers.SelectedIndex = 0;
// else
// this.cboServers.Text = "<No available SQL Servers>";
//}
private void btnConnectTest_Click(object sender, EventArgs e)
{
if (tbxServerName.Text.Trim() == "")
{
MessageBox.Show("请输入服务器名称", "输入提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
tbxServerName.Focus();
}
if (tbxUserName.Text.Trim() == "")
{
MessageBox.Show("请输入用户名", "输入提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
tbxUserName.Focus();
}
if (tbxPassword.Text.Trim() == "")
{
MessageBox.Show("请输入密码", "输入提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
tbxPassword.Focus();
}
cboDBName.Items.Clear();
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
try
{
srv.Connect(tbxServerName.Text.Trim(), tbxUserName.Text.Trim(), tbxPassword.Text.Trim());
foreach (SQLDMO.Database db in srv.Databases)
{
if (db.Name != null)
{
cboDBName.Items.Add(db.Name);
}
}
if (this.cboDBName.Items.Count != 0)
{
cboDBName.SelectedIndex = 0;
}
MessageBox.Show("服务器连接成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
btnExport.Visible = true;
}
catch
{
MessageBox.Show("连接服务器：" + this.tbxServerName.Text.Trim() + "失败！\n请核对用户名和密码。", "提示：", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public bool AttachDB(string dbName, string dbFile)
{
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
svr.AttachDB(dbName, dbFile);
return true;
}
catch (Exception err)
{
//throw (new Exception("��" + err.Message));
ShowError("�������ݿ�ʧ��" + err.Message);
return false;
}
finally
{
svr.DisConnect();
}
}
public bool AttachDB(string dbName, string dbFile)
{
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
svr.AttachDB(dbName, dbFile);
return(true);
}
catch (Exception err)
{
//throw (new Exception("！" + err.Message));
ShowError("附加数据库失败" + err.Message);
return(false);
}
finally
{
svr.DisConnect();
}
}
public bool KillDB(string dbName)
{
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
svr.KillDatabase(dbName);
return(true);
}
catch (Exception err)
{
//throw (new Exception("！" + err.Message));
ShowError("删除数据库失败!" + err.Message);
return(false);
}
finally
{
svr.DisConnect();
}
}
public bool KillStored(string DataBaseName, string tbName)
{
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
svr.Databases.Item(DataBaseName, "owner").StoredProcedures.Remove(tbName, "owner");
return(true);
}
catch (Exception err)
{
//throw (new Exception("！" + err.Message));
ShowError("删除存储过程失败!" + err.Message);
return(false);
}
finally
{
svr.DisConnect();
}
}
public static bool backupDB(string strdbname, string strfilename)
{
string conStr = ConfigurationManager.AppSettings["SqlConString"].ToString();
string[] con = conStr.Split(';');
int nameIndex = con[2].IndexOf('=') + 1;
string username = con[2].Substring(nameIndex, con[2].Length - nameIndex);
int pwdIndex = con[3].IndexOf('=') + 1;
string pwd = con[3].Substring(pwdIndex, con[3].Length - pwdIndex);
int ipIndex = con[0].IndexOf('=') + 1;
string ip = con[0].Substring(ipIndex, con[0].Length - ipIndex);
string server = ip;
string uid = username;
string password = pwd;
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(server, uid, password);
SQLDMO.Backup bak = new SQLDMO.BackupClass();
bak.Action = 0;
bak.Initialize = true;
//SQLDMO.BackupSink_PercentCompleteEventHandler pceh = new SQLDMO.BackupSink_PercentCompleteEventHandler(step);
//bak.PercentComplete += pceh;
bak.Files = strfilename;
bak.Database = "CarSecretary";
bak.SQLBackup(svr);
return true;
}
catch (Exception ex)
{
//CarLog.WriteLog(ex.Message);
//MessageBox.Show("备份数据库失败,请确保没有其他用户使用数据库!!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return false;
}
finally
{
svr.DisConnect();
}
}
public ArrayList GetTables(string DatabaseName)
{
ArrayList alDbs = new ArrayList();
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
SQLDMO.Database mydb = new SQLDMO.DatabaseClass();
mydb = (SQLDMO.Database)svr.Databases.Item(DatabaseName, "owner");
foreach (SQLDMO.Table db in mydb.Tables)
{
if (db.Name != null)
alDbs.Add(db.Name);
}
}
catch (Exception e)
{
//throw (new Exception("�������ݿ�����" + e.Message));
ShowError("�������ݿ�����" + e.Message);
return null;
}
finally
{
svr.DisConnect();
sqlApp.Quit();
}
return alDbs;
}
/// <summary>
/// 测试连接
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void TestConnect_Click(object sender, EventArgs e)
{
//SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
if (ConnOption.SelectedIndex == 0)
{
SQLDMO.SQLServer sr = new SQLDMO.SQLServerClass();
DbName.Items.Clear();
if (UserName.Text == "" || Password.Text == "")
{
if (Password.Text == "" && UserName.Text == "")
{
try
{
sr.Connect(DbSource.Text.ToString(), null, null);
MessageBox.Show("测试连接成功！");
sr.DisConnect();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
else if (Password.Text == "")
{
try
{
sr.Connect(DbSource.Text.ToString(), UserName.Text, null);
MessageBox.Show("测试连接成功！");
sr.DisConnect();
}
catch (Exception ex)
{
MessageBox.Show("连接不成功，出现如下错误：" + ex.ToString());
}
}
else if (UserName.Text == "")
{
MessageBox.Show("请填写用户名，并重新测试！");
}
}
else
{
try
{
sr.Connect(DbSource.Text.ToString(), UserName.Text, Password.Text);
MessageBox.Show("测试连接成功！");
sr.DisConnect();
}
catch (Exception ex)
{
MessageBox.Show("连接不成功，出现如下错误：" + ex.Message.ToString());
}
}
}
else if (ConnOption.SelectedIndex == 1)
{
SqlConnection conn = new SqlConnection(ConnString);
try
{
conn.Open();
if (conn.State == ConnectionState.Open)
{
MessageBox.Show("连接成功！");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
}
/// <summary>
/// 数据库还原
/// </summary>
/// <param name="url">备份文件的地址</param>
/// <returns></returns>
public bool DbRestore(string url)
{
if (exepro() != true)//执行存储过程
{
return false;
}
else
{
SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(server, uid, pwd);
oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
oRestore.Database = database;
oRestore.Files = url;//@"d:\Northwind.bak";
oRestore.FileNumber = 1;
oRestore.ReplaceDatabase = true;
oRestore.SQLRestore(oSQLServer);
return true;
}
catch
{
return false;
}
finally
{
oSQLServer.DisConnect();
}
}
}
public bool KillTable(string DataBaseName,string tbName)
{
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
svr.Databases.Item(DataBaseName, "owner").Tables.Remove(tbName, "owner");
return true;
}
catch (Exception err)
{
//throw (new Exception("��" + err.Message));
ShowError("ɾ����ʧ��!" + err.Message);
return false;
}
finally
{
svr.DisConnect();
}
}
/// <summary>
/// �������ݿ��б�
/// </summary>
/// <returns></returns>
public ArrayList GetDbList()
{
ArrayList alDbs = new ArrayList();
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
foreach (SQLDMO.Database db in svr.Databases)
{
if (db.Name != null)
alDbs.Add(db.Name);
}
}
catch (Exception e)
{
throw (new Exception("�������ݿ�����" + e.Message));
}
finally
{
svr.Close();
sqlApp.Quit();
}
return alDbs;
}
/// <summary>
/// SQL恢复数据库
/// </summary>
/// <param name="ServerIP">SQL服务器IP或(Localhost)</param>
/// <param name="LoginName">数据库登录名</param>
/// <param name="LoginPass">数据库登录密码</param>
/// <param name="DBName">要还原的数据库名</param>
/// <param name="BackPath">数据库备份的路径</param>
public static void SQLDbRestore(string ServerIP, string LoginName, string LoginPass, string DBName, string BackPath)
{
SQLDMO.Restore orestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(ServerIP, LoginName, LoginPass);
orestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
orestore.Database = DBName;
orestore.Files = BackPath;
orestore.FileNumber = 1;
orestore.ReplaceDatabase = true;
orestore.SQLRestore(oSQLServer);
}
catch (Exception e)
{
throw new Exception(e.ToString());
}
finally
{
oSQLServer.DisConnect();
}
}
/// <summary>
/// SQL数据库备份
/// </summary>
/// <param name="ServerIP">SQL服务器IP或(Localhost)</param>
/// <param name="LoginName">数据库登录名</param>
/// <param name="LoginPass">数据库登录密码</param>
/// <param name="DBName">数据库名</param>
/// <param name="BackPath">备份到的路径</param>
public static void SQLBACK(string ServerIP, string LoginName, string LoginPass, string DBName, string BackPath)
{
SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(ServerIP, LoginName, LoginPass);
oBackup.Database = DBName;
oBackup.Files = BackPath;
oBackup.BackupSetName = DBName;
oBackup.BackupSetDescription = "数据库备份";
oBackup.Initialize = true;
oBackup.SQLBackup(oSQLServer);
}
catch (Exception e)
{
throw new Exception(e.ToString());
}
finally
{
oSQLServer.DisConnect();
}
}
/// <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="strDbName"></param>
/// <param name="strFileName"></param>
/// <param name="pgbMain"></param>
/// <returns></returns>
public bool BackUPDB(string strDbName, string strFileName)
{
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
SQLDMO.Backup bak = new SQLDMO.BackupClass();
bak.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
bak.Initialize = true;
bak.Files = strFileName;
bak.Database = strDbName;
bak.SQLBackup(svr);
return true;
}
catch (Exception err)
{
//throw (new Exception("�������ݿ�ʧ��" + err.Message));
ShowError("�������ݿ�����" + err.Message);
return false;
}
finally
{
svr.DisConnect();
}
}
public bool CreateDB(string dbName, string path)
{
//SQLDMO.SQLServer.EnumDirectories(string path);
// �������ݿ��ļ�
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
SQLDMO.DBFile dbFile = new SQLDMO.DBFileClass();
try
{
svr.Connect(ServerName, UserName, Password);
svr.EnumDirectories("c:");
dbFile.Name = dbName + "_Data";
dbFile.PhysicalName = Path.Combine(path, dbName + "_Data.MDF");
dbFile.PrimaryFile = true;
//dbFile.Size = 2; // ���ó�ʼ����С(MB)
//dbFile.FileGrowthType = SQLDMO_GROWTH_TYPE.SQLDMOGrowth_MB; // �����ļ�������ʽ
//dbFile.FileGrowth=1; // ������������
// ������־�ļ�
SQLDMO._LogFile logFile = new SQLDMO.LogFileClass();
logFile.Name = dbName + "_Log";
logFile.PhysicalName = Path.Combine(path, dbName + "_Log.MDF");
//logFile.Size = 3;
//logFile.FileGrowthType=SQLDMO_GROWTH_TYPE.SQLDMOGrowth_MB;
//logFile.FileGrowth=1;
// �������ݿ�
SQLDMO.Database db = new SQLDMO.DatabaseClass();
db.Name = dbName;
db.FileGroups.Item("PRIMARY").DBFiles.Add(dbFile);
db.TransactionLog.LogFiles.Add(logFile);
// �������ݿ����ӣ���������ݿ������
svr.Databases.Add(db);
return true;
}
catch (Exception err)
{
//throw (new Exception("��" + err.Message));
ShowError("������ݿ�ʧ��!" + err.Message);
return false;
}
finally
{
svr.DisConnect();
}
}
/// <summary>
/// 创建数据库
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnCreateData_Click(object sender, EventArgs e)
{
if (!CheckForm())
{
return;
}
string connstr1 = " Connection Timeout=2; Pooling=False ;server=" + txtServerAddress.Text.Trim() + ";database=Master;uid=" + txtServerUser.Text.Trim() + ";pwd=" + txtServerPwd.Text.Trim() + ";";
if (!CheckConn(connstr1))
{
SQLDMOHelper.MesShow("无法连接到服务器，请确认服务器信息!");
return;
}
SQLDMOHelper smh = new SQLDMOHelper(txtServerAddress.Text.Trim(),txtServerUser.Text.Trim(),txtServerPwd.Text.Trim());
ArrayList datalist = smh.GetDbList();
if (datalist.Contains(txtServerName.Text.Trim()))
{
SQLDMOHelper.MesShow("该服务器中已在名为 " + txtServerName.Text.Trim() + " 的数据库");
return;
}
FrmDirTree frmd = new FrmDirTree();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
svr.Connect(txtServerAddress.Text.Trim(), txtServerUser.Text.Trim(), txtServerPwd.Text.Trim());
frmd.svr=svr;
frmd.Text = txtServerAddress.Text.Trim() + "选择路径";
string FilePath="";
if (frmd.ShowDialog()==DialogResult.OK)
{
FilePath = frmd.SelectPaht;
}
else
{
return;
}
WaitDialogForm frm = new WaitDialogForm("正在创建数据库，请稍后...");
frm.Show();
if (smh.CreateDB(txtServerName.Text.Trim(), FilePath))
{
//读取配置数据表.sql文件
IList<SysDataFiles> sdflist = ServicesSys.BaseService.GetList<SysDataFiles>("SelectSysDataFilesList", "");
if (sdflist.Count==0)
{
frm.Hide();
SQLDMOHelper.MesShow("服务器中创建数据库文件不存在，请管管员先添加该文件！ ");
return;
}
SysDataFiles file = sdflist[0];
string path = Application.StartupPath + "\\BlogData";
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string filepath = path + "\\" + file.FileName;
if (File.Exists(filepath))
{
File.Delete(filepath);
}
getfile(file.Files, filepath);
ArrayList alist = smh.GetSqlFile(filepath, txtServerName.Text.Trim());
if (File.Exists(filepath))
{
File.Delete(filepath);
}
string connstr2 = "Pooling=False ; server=" + txtServerAddress.Text.Trim() + ";database=" + txtServerName.Text.Trim() + ";uid=" + txtServerUser.Text.Trim() + ";pwd=" + txtServerPwd.Text.Trim() + ";";
SqlConnection conn = new SqlConnection(connstr2);
frm.Caption = "正在创建数据表，请稍后...";
if (smh.ExecuteCommand(alist,conn))
{
frm.Caption = "正在初始化数据，请稍后...";
//添加数据
if (CopyData(smh))
{
frm.Hide();
SQLDMOHelper.MesShow("数据库 " + txtServerName.Text.Trim() + " 已成功创建");
}
else
{
frm.Hide();
SQLDMOHelper.MesShow("数据库 " + txtServerName.Text.Trim() + " 已成功创建,初始化数据败");
}
}
else
{
frm.Hide();
SQLDMOHelper.MesShow("创建数据库表失败，请检查服务器中创建数据库文件是否损坏");
}
}
frm.Hide();
}
public bool UpdateStored(string DataBaseName, string StoredName,string StoredText)
{
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
if (StoredName == "")
{
SQLDMO.StoredProcedure spd = new SQLDMO.StoredProcedureClass();
spd.Text = StoredText;
svr.Databases.Item(DataBaseName, "owner").StoredProcedures.Add(spd);
}
else
{
SQLDMO.Database dbs = new SQLDMO.DatabaseClass();
SQLDMO.StoredProcedure spd = new SQLDMO.StoredProcedureClass();
dbs = (SQLDMO.Database)svr.Databases.Item(DataBaseName, "owner");
foreach (SQLDMO.StoredProcedure sp in dbs.StoredProcedures)
{
if (sp.Name == StoredName)
spd = sp;
}
spd.Alter(StoredText);
}
return true;
}
catch (Exception err)
{
//throw (new Exception("��" + err.Message));
ShowError("�Ĵ洢����ʧ��!" + err.Message);
return false;
}
finally
{
svr.DisConnect();
}
}
/// <summary>
/// 恢复指定数据库文件
/// </summary>
/// <param name="strDbName">数据库名称</param>
/// <param name="strFileName">还原文件路径</param>
/// <param name="strServerName">服务器名称</param>
/// <param name="strUserName">用户名</param>
/// <param name="strPassword">密码</param>
/// <returns></returns>
public static bool RestoreDB(string strDbName, string strFileName, string strServerName, string strUserName, string strPassword, ProgressBar bar)
{
pBar = bar;
SQLDMO.SQLServer sqlServer = new SQLDMO.SQLServerClass();
string str = "";
string tmpPath = strFileName.Substring(0, strFileName.LastIndexOf("\\")).ToString();
int isEmpty = tmpPath.IndexOf(" ");
try
{
// 连接到数据库服务器
sqlServer.Connect(strServerName, strUserName, strPassword);
// 获取所有的进程列表
SQLDMO.QueryResults qr = sqlServer.EnumProcesses(-1);
// 查找 SPID 和 DBName 的位置
int iColPIDNum = -1; // 标记 SPID 的位置
int iColDbName = -1; // 标记 DBName 的位置
for (int i = 1; i <= qr.Columns; i++)
{
string strName = qr.get_ColumnName(i);
if (strName.ToUpper().Trim() == "SPID")
{
// 标记 SPID
iColPIDNum = i;
}
else if (strName.ToUpper().Trim() == "DBNAME")
{
// 标记 DBName
iColDbName = i;
}
// 如果找到 SPID 和 DBName, 则跳出循环
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() == strDbName.ToUpper())
{
sqlServer.KillProcess(lPID);
}
}
// 实例化还原操作对象
SQLDMO.Restore res = new SQLDMO.RestoreClass();
// 路径中有空格(不包括文件名) 备份到路径的根目录的临时文件夹tmpBackup中
if (isEmpty > 1 && strFileName.Substring(4).LastIndexOf("\\") > 1)
{
str = strFileName.Substring(0, 1).ToString() + ":\\tmp_backup.kj";
File.Move(strFileName, str);
}
else
{
str = strFileName;
}
// 数据库存放的路径和要恢复的数据库名字
res.Files = str;
res.Database = strDbName;
// 所恢复的数据库文件的类型
res.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
res.ReplaceDatabase = true;
#region 进度条处理
if (pBar != null)
{
pBar.Visible = true;
SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);
res.PercentComplete += pceh;
}
#endregion
// 执行数据库恢复
res.SQLRestore(sqlServer);
return true;
}
catch (Exception ex)
{
string tmpErr = "还原失败";
if (ex.Message.IndexOf("文件不是有效的 Microsoft 磁带格式备份集") > 1)
{
tmpErr = "文件格式不正确";
}
MessageBox.Show(tmpErr);
return false;
}
finally
{
sqlServer.DisConnect();
// 文件夹名称中有空格 将备份的文件移回到用户指定的文件夹并将临时目录删除
if (isEmpty > 1 && strFileName.Substring(4).LastIndexOf("\\") > 1)
{
File.Move(str, strFileName);
}
}
}
public string GetStoredText(string DataBaseName, string StoredName)
{
string getStoredText = "";
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
SQLDMO.Database dbs = new SQLDMO.DatabaseClass();
dbs = (SQLDMO.Database)svr.Databases.Item(DataBaseName, "owner");
foreach (SQLDMO.StoredProcedure tb in dbs.StoredProcedures)
{
if (tb.Name == StoredName)
getStoredText= tb.Text;
}
}
catch (Exception err)
{
//throw (new Exception("��" + err.Message));
ShowError("�Ĵ洢����ʧ��!" + err.Message);
getStoredText= "";
}
finally
{
svr.DisConnect();
}
return getStoredText;
}
/// <summary>
/// 登录服务器，获取数据库列表
/// </summary>
/// <param name="strServerName">服务器名称</param>
/// <param name="strUserName">用户名</param>
/// <param name="strPwd">密码</param>
/// <returns></returns>
public static ArrayList GetDbList(string strServerName, string strUserName, string strPwd)
{
ArrayList alDbs = new ArrayList();
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
// 连接到数据库
svr.Connect(strServerName, strUserName, strPwd);
// 将服务器上所有的数据库遍历
foreach (SQLDMO.Database db in svr.Databases)
{
if (db.Name != null)
{
if (db.Name != "master" && db.Name != "model"
&& db.Name != "msdb" && db.Name != "pubs"
&& db.Name != "Northwind" && db.Name != "tempdb")
{
alDbs.Add(db.Name);
}
}
}
}
catch (Exception e)
{
//throw (new Exception("连接数据库出错：" + e.Message));
MessageBox.Show("数据库连接失败");
}
finally
{
svr.DisConnect();
sqlApp.Quit();
}
return alDbs;
}
private void cmdConnect_Click(object sender, System.EventArgs e)
{
if (srv!=null) srv.DisConnect();
srv=new SQLDMO.SQLServerClass();
if (ckbNTAuth.Checked)
srv.LoginSecure=true;
else
srv.LoginSecure=false;
srv.Connect(txtServerName.Text,txtSqlUser.Text,txtSqlPassword.Text);
trvSqlServerSchema.Nodes.Clear();
for(int n=1;n<=srv.Databases.Count;n++)
{
SQLDMO._Database db=srv.Databases.Item(n,null);
if (!db.SystemObject)
{
if (("" == txtBase.Text) || (txtBase.Text.Trim().ToLower() == db.Name.Trim().ToLower()))
{
TreeNode dbNode=trvSqlServerSchema.Nodes.Add(db.Name);
dbNode.Tag=db;
for(int i=1;i<=db.Tables.Count;i++)
{
SQLDMO._Table tab=db.Tables.Item(i,null);
if (!tab.SystemObject)
{
TreeNode tabNode=dbNode.Nodes.Add(tab.Name);
tabNode.Tag=tab;
}
}
}
}
}
}
/// <summary>
/// �ָ����ݿ⣬�ָ�ǰɱ�������뱾���ݿ���ؽ���
/// </summary>
/// <param name="strDbName">���ݿ���</param>
/// <param name="strFileName">���·��</param>
/// <param name="pgbMain"></param>
/// <returns></returns>
public bool RestoreDB(string strDbName, string strFileName)
{
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
//ȡ�����еĽ����б�
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;
}
//����ؽ���ɱ��
for (int i = 1; i <= qr.Rows; i++)
{
int lPID = qr.GetColumnLong(i, iColPIDNum);
string strDBName = qr.GetColumnString(i, iColDbName);
if (strDBName.ToUpper() == strDbName.ToUpper())
svr.KillProcess(lPID);
}
SQLDMO.Restore res = new SQLDMO.RestoreClass();
res.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
res.Files = strFileName;
res.Database = strDbName;
res.FileNumber = 1;
res.ReplaceDatabase = true;
res.SQLRestore(svr);
return true;
}
catch (Exception err)
{
//throw (new Exception("" + err.Message));
ShowError("�ָ����ݿ�ʧ��,��ر����к����ݿ����ӵij���" + err.Message);
return false;
}
finally
{
svr.DisConnect();
}
}
public bool InsertTable(string DatabaseName, string TableName,DataTable dt)
{
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
string keyName = "";
try
{
svr.Connect(ServerName, UserName, Password);
SQLDMO.Database myDb = new SQLDMO.DatabaseClass();
myDb = (SQLDMO.Database)svr.Databases.Item(DatabaseName, "owner");
SQLDMO.Table myTb = new SQLDMO.TableClass();
myTb.Name = TableName;
foreach (DataRow dr in dt.Rows)
{
SQLDMO.Column column = new SQLDMO.ColumnClass();
column.Name = dr["ColumnName"].ToString();
column.Datatype = dr["ColumnType"].ToString();
column.Length = int.Parse(dr["ColumnSize"].ToString());
if (bool.Parse(dr["ColumnKey"].ToString()))
{
keyName = column.Name;
}
column.AllowNulls = bool.Parse(dr["ColumnNull"].ToString());
myTb.Columns.Add(column);
}
if (keyName != "")
{
SQLDMO.Key key = new SQLDMO.KeyClass();
key.Name = keyName;
key.Type = SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Primary;
key.KeyColumns.Add(keyName);
myTb.Keys.Add(key);
}
myDb.Tables.Add(myTb);
return true;
}
catch (Exception e)
{
//throw (new Exception("�������ݿ�����" + e.Message));
ShowError("������ݿ�ʧ��!" + e.Message);
return false;
}
finally
{
svr.DisConnect();
sqlApp.Quit();
}
}