C#批量附加指定目录下的所有数据库文件到数据库中

C#批量附加指定目录下的所有数据库文件到数据库中

应用场合：因为经常更换操作系统，所以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();
            }
        }