private Model.StoredProcedure GetNewStoredProcedure(SQLDMO.StoredProcedure dmoStoredProcedure)
{
Model.StoredProcedure storedProcedure = new Model.StoredProcedure(dmoStoredProcedure.Name, Script.GetSingluar(dmoStoredProcedure.Name), false);
storedProcedure.Enabled = false;
// Columns
//int ordinalPosition = 0;
//SQLDMO.Column[] dmoColumns = GetColumns(dmoStoredProcedure);
//foreach (SQLDMO.Column dmoColumn in dmoColumns)
//{
/*if (UnsupportedDataTypes.ToLower().IndexOf("'" + dmoColumn.PhysicalDatatype.ToLower() + "'") >= 0)
* {
* continue;
* }
*
* Column column = new Column(dmoColumn.Name, Script.GetSingluar(dmoColumn.Name), dmoColumn.Name, storedProcedure, ordinalPosition, dmoColumn.AllowNulls, dmoColumn.PhysicalDatatype, dmoColumn.Length,
* dmoColumn.InPrimaryKey, false, false, dmoColumn.Identity);
* storedProcedure.Columns.Add(column);
* ordinalPosition++;*/
//}
// Parameters
SQLDMO.QueryResults queryResults = dmoStoredProcedure.EnumParameters();
for (int i = 1; i <= queryResults.Rows; i++)
{
//Model.StoredProcedure.Parameter parameter = new Model.StoredProcedure.Parameter(queryResults.GetColumnString(i, 1), queryResults.GetColumnString(i, 1).Replace("@", ""), queryResults.GetColumnString(i, 2));
//storedProcedure.AddParameter(parameter);
}
return(storedProcedure);
}
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);
}
}
}
/// <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();
}
}
private void treeView1_BeforeExpand(object sender, TreeViewCancelEventArgs e)
{
if (e.Node.Nodes.Count == 0)
{
try
{
SQLDMO.QueryResults result = svr.EnumDirectories(e.Node.Tag.ToString());
AddTree(e.Node, result);
}
catch (Exception)
{
throw;
}
}
}
private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
{
if (e.Node.Nodes.Count == 0)
{
try
{
SQLDMO.QueryResults result = svr.EnumDirectories(e.Node.Tag.ToString());
AddTree(e.Node, result);
}
catch (Exception)
{
throw;
}
}
txtpath.Text = treeView1.SelectedNode.Tag.ToString();
}
private void IntiData()
{
// EnumAvailableMedia(SQLDMO.SQLDMO_MEDIA_TYPE)：列举对SQL Server可见的媒介质，参数是一个枚举类型，用于指定方法返回的类型，分别是
//_All 返回所有类型
//SQLDMOMedia_CDROM 返回光驱列表
//SQLDMOMedia_FixedDisk 返回硬盘列表
SQLDMO.QueryResults disk = svr.EnumAvailableMedia(SQLDMO.SQLDMO_MEDIA_TYPE.SQLDMOMedia_FixedDisk);
for (int i = 1; i < disk.Rows; i++)
{
TreeNode newnode = new TreeNode();
newnode.Text = disk.GetColumnString(i, 1);
newnode.Tag = newnode.Text;
newnode.StateImageIndex = 0;
treeView1.Nodes.Add(newnode);
}
}
/// <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 void AddTree(TreeNode node, SQLDMO.QueryResults result)
{
for (int i = 1; i < result.Rows; i++)
{
TreeNode newnode = new TreeNode();
newnode.Text = result.GetColumnString(i, 1);
if (!node.Tag.ToString().EndsWith("\\"))
{
newnode.Tag = node.Tag + "\\" + newnode.Text;
}
else
{
newnode.Tag = node.Tag + newnode.Text;
}
newnode.StateImageIndex = 1;
node.Nodes.Add(newnode);
}
}
/// <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);
}
}
}
/// <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);
}
}
}
/// <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("恢复数据库失败,请关闭所有和该数据库连接的程序！" + err.Message);
return(false);
}
finally
{
svr.DisConnect();
}
}
