应用场合：因为经常更换操作系统，所以D盘存放数据库文件目录的数据库每次都要一个一个的附加到MSSQL中，因此设计程序批量附加省时间也方便自己和大家。
程序不足：没有去研究跟实现NDF日志文件附加和多个日志文件的数据库附加。
程序源码：
/// <summary>
/// 循环查找指定目录下要附加的数据库文件和对应的日志文件,连接本地数据库并执行数据库附加命令
/// </summary>
private void AttachFolderDB()
{
string strFileFolder = "";
FolderBrowserDialog myFolderBrowserDialog = new FolderBrowserDialog();
myFolderBrowserDialog.ShowDialog();
if (myFolderBrowserDialog.SelectedPath != "")
{
strFileFolder = myFolderBrowserDialog.SelectedPath;
}
//查找所有的MDF文件列表
string[] arrAttachFilePath = null;
if (strFileFolder != "")
{
DirectoryInfo dir = new DirectoryInfo(strFileFolder);
//判断目录下是否存在主数据库文件
FileInfo[] finfo = dir.GetFiles("*.mdf");
if (finfo.Length > 0)
{
arrAttachFilePath = new string[finfo.Length];
if (finfo.Length > 0)
{
int i = 0;
foreach (FileInfo f in finfo)
{
arrAttachFilePath[i] = f.FullName;
i = i + 1;
}
}
}
}
//循环附加数据库
if (arrAttachFilePath != null)
{
for (int i = 0; i < arrAttachFilePath.Length; i++)
{
string strFile = arrAttachFilePath[i].ToString();
string strMdfFilePath = arrAttachFilePath[i].ToString();//mdf路径
string strLogFilePath = "";//日志文件路径
string strLdfFilePath = "";//日志文件路径
string strDataFileName = strMdfFilePath.Substring(strMdfFilePath.LastIndexOf("\\") + 1, strMdfFilePath.Length - strMdfFilePath.LastIndexOf("\\") - 1);
strDataFileName = strDataFileName.Remove(strDataFileName.LastIndexOf("."));
string logIndex = "_Data";
int n = strDataFileName.IndexOf(logIndex);
if (n == -1)
{
strLogFilePath = strMdfFilePath.Remove(strMdfFilePath.LastIndexOf("\\")) + "\\" + strDataFileName + "_log.ldf";
strLdfFilePath = strMdfFilePath.Remove(strMdfFilePath.LastIndexOf("\\")) + "\\" + strDataFileName + ".ldf";
}
else
{
strDataFileName = strDataFileName.Remove(strDataFileName.LastIndexOf("_"));
strLogFilePath = strMdfFilePath.Remove(strMdfFilePath.LastIndexOf("\\")) + "\\" + strDataFileName + "_log.ldf";
strLdfFilePath = strMdfFilePath.Remove(strMdfFilePath.LastIndexOf("\\")) + "\\" + strDataFileName + ".ldf";
}
StringBuilder sb = new StringBuilder();
sb.Append("sp_attach_db @dbname='" + strDataFileName + "',@filename1='" + strMdfFilePath + "'");
if (System.IO.File.Exists(strLogFilePath))
{
sb.Append(",@filename2='" + strLogFilePath + "'");
AttachDataBase(sb.ToString());
}
else if (System.IO.File.Exists(strLdfFilePath))
{
sb.Append(",@filename2='" + strLdfFilePath + "'");
AttachDataBase(sb.ToString());
}
else
{
Console.WriteLine("数据库文件" + strMdfFilePath + "缺少必备的日志文件！");
}
}
}
}
/// <summary>
/// 连接数据库并执行附加对应的数据库文件命令
/// </summary>
/// <param name="strSql">附加数据库命令字符串</param>
/// <returns></returns>
private bool AttachDataBase(string strSql)
{
SqlConnection con = new SqlConnection(@"Data Source=(local);Initial Catalog=master;Integrated Security=True");
try
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = strSql;
cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
//如果数据库中存在名为要添加的数据库时则抛出异常
Console.WriteLine("附加数据库时异常:" + ex.Message);
return false;
}
finally
{
con.Close();
}
}