导出SQL脚本小程序

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.IO;
 
namespace ExportSQL
{
    class Tsest
    {
        static void Main(string[] args)
        {
            string connstr = @"Data Source = .; User ID = sa; Password = 123456; Initial Catalog = Test; Connection Reset = FALSE; Pooling = true; Max Pool Size = 500";
            ExportSQL(connstr, "v", "v_StoreWithArea, uspGetCustRenewHist");
        }
 
        /// <summary>
        /// 导出sql脚本
        /// </summary>
        /// <param name="connStr">数据库连接字符串</param>
        /// <param name="sqlType">需要导出的脚本的类型，可以为null或""</param>
        /// <param name="nameStr">脚本的名称（列入存储过程的名称、视图的名称等）可以为null或""param>
        static void ExportSQL(string connStr, string sqlType, string nameStr)
        {
            List<SqlParameter> sqlParameters = new List<SqlParameter>();
            StringBuilder str = new StringBuilder("SELECT o.name,s.text FROM sysobjects o inner join   syscomments s on o.id = s.id ");
            if (!string.IsNullOrEmpty(sqlType))
            {
                sqlParameters.Add(new SqlParameter("@sqlType", sqlType));
                str.Append(" AND o.xtype =@sqlType ");
            }
            if (!string.IsNullOrEmpty(nameStr))
            {
                var whereArray = nameStr.Split(',');
                str.Append(" and o.name  in ( ");
                for (int i = 0; i < whereArray.Length; i++)
                {
                    sqlParameters.Add(new SqlParameter("@name" + i, whereArray[i].Trim()));
                    str.Append("@name" + i);
                    if (i < whereArray.Length - 1)
                    {
                        str.Append(",");
                    }
                }
                str.Append(" )");
            }
 
            string _sqltype = "";
            switch (sqlType.Trim().ToUpper())
            {
                case "P":
                    _sqltype = "proc";
                    break;
                case "V":
                    _sqltype = "view";
                    break;
                case "TF":
                    _sqltype = "funtion";
                    break;
                case "TR":
                    _sqltype = "trigger";
                    break;
                case "U":
                    _sqltype = "table";
                    break;
                default:
                    _sqltype = "proc";
                    break;
            }
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand sqlCommand = new SqlCommand(str.ToString(), conn))
                {
                    if (sqlParameters != null && sqlParameters.Count != 0)
                    {
                        sqlCommand.Parameters.AddRange(sqlParameters.ToArray());
                    }
                    SqlDataReader reader = sqlCommand.ExecuteReader();
                    string fileName = "sql脚本" + DateTime.Now.ToString("yyyyMMdd") + ".txt";
                    string filePath = "C:\\项目\\" + fileName;     //文件路径
                    //创建文件流   FileMode.OpenOrCreate  有就打开，没有就创建
                    FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate);
                    fs.Close();
                    StreamWriter write = new StreamWriter(filePath,false);//覆盖现有内容
                    while (reader.Read())
                    {
                        string name = reader["name"].ToString();
 
                        write.WriteLine("--------------------" + name + "开始---------------");
                        write.WriteLine("");
                        write.WriteLine(@"if exists(select * from sys.objects  where name='{0}'  and type='{1}')", name, sqlType);
                        write.WriteLine("begin");
                        write.WriteLine(@"  drop {0} {1}", _sqltype, name);
                        write.WriteLine("end");
                        write.WriteLine("go");
                        write.WriteLine("");
                        write.WriteLine(reader["text"]);
                        write.WriteLine("--------------------" + name + "结束---------------");
                        write.WriteLine("");
                    }
                    Console.WriteLine("导出sql成功");
                    write.Close();
                    reader.Close();
                    Console.ReadLine();
                }
            }
        }
    }
}