sqlServer2008升级2016raiserror语法修改工具

2021-10-20 11:34:28

  private void btnUpdate_Click(object sender, EventArgs e)
        {
            List<string> listFileName = Directory.GetFiles(@AppConfig.FilePath).ToList();
            StringBuilder text = new StringBuilder();
            bool twoLine = false;
            //StringBuilder strBox = new StringBuilder();
            listFileName.ForEach(f =>
            {
                File.Delete(@"D:\temp2\" + Path.GetFileName(f));
                StreamWriter sw = new StreamWriter(@"D:\temp2\" + Path.GetFileName(f), true, Encoding.UTF8);                
                using (FileStream fs = new FileStream(f, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    using (StreamReader sr = new StreamReader(fs, Encoding.UTF8))
                    {
                        Regex regex = new Regex(@"^[0-9]{1,7}$");
                        while (sr.Peek() > -1)
                        {
                            string line = sr.ReadLine();
                            if (twoLine)
                            {
                                text.Append(line.Replace('\'', '"').Split('"').ToList()[0] + "',16,1)");
                                sw.WriteLine(text);
                                text.Clear();
                                twoLine = false;
                                continue;
                            }
                            if ((line.IndexOf("raiserror", StringComparison.CurrentCultureIgnoreCase) != -1) &&
                               (!line.Trim().StartsWith("/*")) && (!line.Trim().EndsWith("*/")) && (!line.Trim().StartsWith("--"))
                               && (!line.Contains("@")) && (!line.Trim().EndsWith("\"")) && (!line.Trim().EndsWith("'"))
                               && (!line.Trim().EndsWith(";")) && (!line.Trim().EndsWith(")"))
                               && (!(line.Split('\'').ToList().Where(x => !string.IsNullOrEmpty(x.Trim())).Last().ToUpper().Contains("RETURN")))
                               && (!(line.Split('\'').ToList().Where(x => !string.IsNullOrEmpty(x.Trim())).Last().ToUpper().Contains("END"))))
                            {  //RAISERROR 99999 'The record has already been changed by another person,please reload
                               // the program and edit it again.'
                                List<string> listRaise = new List<string>();
                                listRaise = Regex.Replace(line, "raiserror", "^", RegexOptions.IgnoreCase).Split('^').ToList();
                                listRaise.ForEach(x =>
                                {
                                    if (!string.IsNullOrEmpty(x.Trim()))
                                    {
                                        List<string> listSub = new List<string>();
                                        listSub = x.Trim().Split('\'').ToList();
                                        if (x.Contains("\"")) //RAISERROR 99999 "This Record Already Exists In This Table."
                                            listSub = x.Trim().Split('"').ToList();
                                        //Regex regex2 = new Regex(@"(\([^\)]*.*\))");                                       
                                        if (regex.IsMatch(listSub[0].Trim()))
                                        {
                                            text.Append(string.Format(" RAISERROR ('{0}", listSub[1].ToString()));
                                        }
                                    }
                                });
                                twoLine = true;
                                continue;
                            }
                            //create PROCEDURE[dbo].[GenerateDatacollectionByHour_sp_test]
                            //(line.ToUpper().Contains("PROCEDURE") || line.ToUpper().Contains("PROC")) &&

 

                            if (!string.IsNullOrEmpty(line.Trim()) && (line.ToUpper().Replace("\t", " ").Split(' ').ToList().Where(s => !string.IsNullOrEmpty(s.Trim())).ToList()[0].Equals("CREATE"))
                            && (line.ToUpper().Replace("PROCEDURE", "PROC").Contains("PROC")))
                            {
                                if ((!line.Trim().StartsWith("/*")) && (!line.Trim().EndsWith("*/")) && (!line.Trim().StartsWith("--")))
                                { // CREAT PROCEDURE[DBO].[CREATEWOBYMANU]  --Created by Yang for Repair Check Function at TJ 2015-12-18 Begin
                                  //CREATE PROCEDURE [dbo].[ where SERIAL_NUMBER='7CE552P4CM']存储名带空格  
                                    text.Append(string.Format("ALTER PROCEDURE [{0}[{1}",
                                                line.Split('[').ToList().ToList()[1],
                                                line.Split('[').ToList().ToList()[2]));
                                    sw.WriteLine(text);
                                    text.Clear();
                                    continue;
                                }
                            }
                            //以end或return结尾的  考虑变量里含有return关键字//RAISERROR 99999 @_strReturnMsg 
                            if ((line.IndexOf("raiserror", StringComparison.CurrentCultureIgnoreCase) != -1) &&
                               (!line.Trim().StartsWith("/*")) && (!line.Trim().EndsWith("*/")) && (!line.Trim().StartsWith("--"))
                               && (line.ToUpper().Trim().EndsWith("END")
                               || regex.IsMatch( line.ToUpper().Trim().Split(' ').ToList().Last())))
                            {
                                //RAISERROR 99996 @retmsg end  //RAISERROR 99996 @retmsg return 101
                                if (line.Contains("@"))
                                {                                   
                                    text.Append(string.Format(" RAISERROR (@{0},16,1) {1}",
                                        line.Split('@')[1].Split(' ').ToList().Where(x=>!string.IsNullOrEmpty(x.Trim())).First(),
                                        line.Split('@')[1].Remove(0, line.Split('@')[1].Split(' ').ToList().Where(x => !string.IsNullOrEmpty(x.Trim())).First().Length)));
                                    sw.WriteLine(text);
                                    text.Clear();
                                    continue;
                                }
                                else  //RAISERROR 99999 'Transaction type can not be NULL.'  RETURN 100  
                                      //99999 'Transaction type can not be NULL.'   end 
                                {
                                    text.Append(string.Format(" RAISERROR ('{0}',16,1) {1}", line.Split('\'').ToList()[1], line.Split('\'').ToList().Last().Replace(";", "")));
                                    sw.WriteLine(text);
                                    text.Clear();
                                    continue;
                                }
                            }
                            if ((line.IndexOf("raiserror", StringComparison.CurrentCultureIgnoreCase) != -1) &&
                            (!line.Trim().StartsWith("/*")) && (!line.Trim().EndsWith("*/")) && (!line.Trim().StartsWith("--")))
                            {
                                List<string> listRaise = new List<string>();
                                listRaise = Regex.Replace(line, "raiserror", "^", RegexOptions.IgnoreCase).Split('^').ToList().Where(s => !string.IsNullOrEmpty(s.Trim())).ToList();
                                if ( listRaise.Count == 1)
                                {
                                    string x = listRaise[0].Trim();
                                    if (x.ToArray().ToList().Where(s => !string.IsNullOrEmpty(s.ToString().Trim())).ToList()[0].ToString().Equals("("))
                                    { //RAISERROR(@strError,16,1)  //raiserror(15009, -1, -1, @objname, @dbname)
                                      //raiserror(15197, -1, -1, @objname)  //raiserror(15471, -1, -1)
                                      //RAISERROR(@strRror, 16, 1)

 

                                         //RAISERROR  (@ErrorMessage,  
                                         //  @ErrorSeverity,  
                                         //  @ErrorState 
                                         //  ); 
                                        text.Append(string.Format("RAISERROR{0}", x));
                                        sw.WriteLine(text);
                                        text.Clear();
                                        continue;
                                    }
                                    List<string> listSub = new List<string>();
                                    listSub = x.Trim().Split('\'').ToList().Where(s => !string.IsNullOrEmpty(s.Trim())).ToList();
                                    if (x.ToArray().ToList().Where(s => !string.IsNullOrEmpty(s.ToString().Trim())).ToList().Where(s => s.Equals('"')).Count() == 2)
                                    { // 99999 "This Record Already Exists In This Table."
                                      // 99999 'ERROR: Must save An Fail Analy!!!"'
                                        listSub = x.Trim().Split('"').ToList().Where(s => !string.IsNullOrEmpty(s.Trim())).ToList();
                                    }
                                    if (regex.IsMatch(listSub[0].Trim()))
                                    {
                                        text.Append(string.Format(" RAISERROR ('{0}',16,1)", listSub[1].ToString()));
                                        sw.WriteLine(text);
                                        text.Clear();
                                        continue;
                                    }
                                    if (listSub[0].Contains("@"))
                                    {  //   99999 @strRror   //RAISERROR 99999 @_strReturnMsg (变量含有return关键字)
                                        // raiserror 99999 @res_msg;
                                        text.Append(string.Format(" RAISERROR (@{0},16,1)", 
                                            (listSub[0].Split('@').ToList())[1].ToString().Replace(";","")));
                                        sw.WriteLine(text);
                                        text.Clear();
                                        continue;
                                    }                                   
                                }
                                else
                                {
                                    sw.WriteLine(line);                                    
                                }
                            }
                            else
                            {
                                sw.WriteLine(line);
                            }
                        }
                    }
                }
                sw.Close();
            });
            MessageBox.Show("修改成功");
        }

 

 

 

 private void btnUpdate_Click(object sender, EventArgs e)
        {
            List<string> listFileName = Directory.GetFiles(@AppConfig.FilePath).ToList();
            StringBuilder text = new StringBuilder();
            bool twoLine = false;
            //StringBuilder strBox = new StringBuilder();
            listFileName.ForEach(f =>
            {
                File.Delete(@"D:\temp2\" + Path.GetFileName(f));
                StreamWriter sw = new StreamWriter(@"D:\temp2\" + Path.GetFileName(f), true, Encoding.UTF8);                
                using (FileStream fs = new FileStream(f, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    using (StreamReader sr = new StreamReader(fs, Encoding.UTF8))
                    {
                        Regex regex = new Regex(@"^[0-9]{1,7}$");
                        while (sr.Peek() > -1)
                        {
                            string line = sr.ReadLine();
                            if (twoLine)
                            {
                                text.Append(line.Replace('\'', '"').Split('"').ToList()[0] + "',16,1)");
                                sw.WriteLine(text);
                                text.Clear();
                                twoLine = false;
                                continue;
                            }
                            if ((line.IndexOf("raiserror", StringComparison.CurrentCultureIgnoreCase) != -1) &&
                               (!line.Trim().StartsWith("/*")) && (!line.Trim().EndsWith("*/")) && (!line.Trim().StartsWith("--"))
                               && (!line.Contains("@")) && (!line.Trim().EndsWith("\"")) && (!line.Trim().EndsWith("'"))
                               && (!line.Trim().EndsWith(";")) && (!line.Trim().EndsWith(")"))
                               && (!(line.Split('\'').ToList().Where(x => !string.IsNullOrEmpty(x.Trim())).Last().ToUpper().Contains("RETURN")))
                               && (!(line.Split('\'').ToList().Where(x => !string.IsNullOrEmpty(x.Trim())).Last().ToUpper().Contains("END"))))
                            {  //RAISERROR 99999 'The record has already been changed by another person,please reload
                               // the program and edit it again.'
                                List<string> listRaise = new List<string>();
                                listRaise = Regex.Replace(line, "raiserror", "^", RegexOptions.IgnoreCase).Split('^').ToList();
                                listRaise.ForEach(x =>
                                {
                                    if (!string.IsNullOrEmpty(x.Trim()))
                                    {
                                        List<string> listSub = new List<string>();
                                        listSub = x.Trim().Split('\'').ToList();
                                        if (x.Contains("\"")) //RAISERROR 99999 "This Record Already Exists In This Table."
                                            listSub = x.Trim().Split('"').ToList();
                                        //Regex regex2 = new Regex(@"(\([^\)]*.*\))");                                       
                                        if (regex.IsMatch(listSub[0].Trim()))
                                        {
                                            text.Append(string.Format(" RAISERROR ('{0}", listSub[1].ToString()));
                                        }
                                    }
                                });
                                twoLine = true;
                                continue;
                            }
                            //create PROCEDURE[dbo].[GenerateDatacollectionByHour_sp_test]
                            //(line.ToUpper().Contains("PROCEDURE") || line.ToUpper().Contains("PROC")) &&

 

                            if (!string.IsNullOrEmpty(line.Trim()) && (line.ToUpper().Replace("\t", " ").Split(' ').ToList().Where(s => !string.IsNullOrEmpty(s.Trim())).ToList()[0].Equals("CREATE"))
                            && (line.ToUpper().Replace("PROCEDURE", "PROC").Contains("PROC")))
                            {
                                if ((!line.Trim().StartsWith("/*")) && (!line.Trim().EndsWith("*/")) && (!line.Trim().StartsWith("--")))
                                { // CREAT PROCEDURE[DBO].[CREATEWOBYMANU]  --Created by Yang for Repair Check Function at TJ 2015-12-18 Begin
                                  //CREATE PROCEDURE [dbo].[ where SERIAL_NUMBER='7CE552P4CM']存储名带空格  
                                    text.Append(string.Format("ALTER PROCEDURE [{0}[{1}",
                                                line.Split('[').ToList().ToList()[1],
                                                line.Split('[').ToList().ToList()[2]));
                                    sw.WriteLine(text);
                                    text.Clear();
                                    continue;
                                }
                            }
                            //以end或return结尾的  考虑变量里含有return关键字//RAISERROR 99999 @_strReturnMsg 
                            if ((line.IndexOf("raiserror", StringComparison.CurrentCultureIgnoreCase) != -1) &&
                               (!line.Trim().StartsWith("/*")) && (!line.Trim().EndsWith("*/")) && (!line.Trim().StartsWith("--"))
                               && (line.ToUpper().Trim().EndsWith("END")
                               || regex.IsMatch( line.ToUpper().Trim().Split(' ').ToList().Last())))
                            {
                                //RAISERROR 99996 @retmsg end  //RAISERROR 99996 @retmsg return 101
                                if (line.Contains("@"))
                                {                                   
                                    text.Append(string.Format(" RAISERROR (@{0},16,1) {1}",
                                        line.Split('@')[1].Split(' ').ToList().Where(x=>!string.IsNullOrEmpty(x.Trim())).First(),
                                        line.Split('@')[1].Remove(0, line.Split('@')[1].Split(' ').ToList().Where(x => !string.IsNullOrEmpty(x.Trim())).First().Length)));
                                    sw.WriteLine(text);
                                    text.Clear();
                                    continue;
                                }
                                else  //RAISERROR 99999 'Transaction type can not be NULL.'  RETURN 100  
                                      //99999 'Transaction type can not be NULL.'   end 
                                {
                                    text.Append(string.Format(" RAISERROR ('{0}',16,1) {1}", line.Split('\'').ToList()[1], line.Split('\'').ToList().Last().Replace(";", "")));
                                    sw.WriteLine(text);
                                    text.Clear();
                                    continue;
                                }
                            }
                            if ((line.IndexOf("raiserror", StringComparison.CurrentCultureIgnoreCase) != -1) &&
                            (!line.Trim().StartsWith("/*")) && (!line.Trim().EndsWith("*/")) && (!line.Trim().StartsWith("--")))
                            {
                                List<string> listRaise = new List<string>();
                                listRaise = Regex.Replace(line, "raiserror", "^", RegexOptions.IgnoreCase).Split('^').ToList().Where(s => !string.IsNullOrEmpty(s.Trim())).ToList();
                                if ( listRaise.Count == 1)
                                {
                                    string x = listRaise[0].Trim();
                                    if (x.ToArray().ToList().Where(s => !string.IsNullOrEmpty(s.ToString().Trim())).ToList()[0].ToString().Equals("("))
                                    { //RAISERROR(@strError,16,1)  //raiserror(15009, -1, -1, @objname, @dbname)
                                      //raiserror(15197, -1, -1, @objname)  //raiserror(15471, -1, -1)
                                      //RAISERROR(@strRror, 16, 1)

 

                                         //RAISERROR  (@ErrorMessage,  
                                         //  @ErrorSeverity,  
                                         //  @ErrorState 
                                         //  ); 
                                        text.Append(string.Format("RAISERROR{0}", x));
                                        sw.WriteLine(text);
                                        text.Clear();
                                        continue;
                                    }
                                    List<string> listSub = new List<string>();
                                    listSub = x.Trim().Split('\'').ToList().Where(s => !string.IsNullOrEmpty(s.Trim())).ToList();
                                    if (x.ToArray().ToList().Where(s => !string.IsNullOrEmpty(s.ToString().Trim())).ToList().Where(s => s.Equals('"')).Count() == 2)
                                    { // 99999 "This Record Already Exists In This Table."
                                      // 99999 'ERROR: Must save An Fail Analy!!!"'
                                        listSub = x.Trim().Split('"').ToList().Where(s => !string.IsNullOrEmpty(s.Trim())).ToList();
                                    }
                                    if (regex.IsMatch(listSub[0].Trim()))
                                    {
                                        text.Append(string.Format(" RAISERROR ('{0}',16,1)", listSub[1].ToString()));
                                        sw.WriteLine(text);
                                        text.Clear();
                                        continue;
                                    }
                                    if (listSub[0].Contains("@"))
                                    {  //   99999 @strRror   //RAISERROR 99999 @_strReturnMsg (变量含有return关键字)
                                        // raiserror 99999 @res_msg;
                                        text.Append(string.Format(" RAISERROR (@{0},16,1)", 
                                            (listSub[0].Split('@').ToList())[1].ToString().Replace(";","")));
                                        sw.WriteLine(text);
                                        text.Clear();
                                        continue;
                                    }                                   
                                }
                                else
                                {
                                    sw.WriteLine(line);                                    
                                }
                            }
                            else
                            {
                                sw.WriteLine(line);
                            }
                        }
                    }
                }
                sw.Close();
            });
            MessageBox.Show("修改成功");
        }

上一篇：使用docker-compose.yml快速搭建Linux/Mac开发/生产环境


下一篇：mac双系统安装教程