导出SQL脚本小程序

深碍√TFBOYSˉ_ 2021-12-15 08:41 197阅读 0赞

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();
                    }
                }
            }
        }
    }