C#和Java中執行SQL文件腳本的代碼(非常有用)

我們在做程序的時候有事后會涉及到利用sql文件 直接執行，可是在sql文件中有很多注釋，我們要一句一句的執行首先必須的得把sql文件解析

去除其中的注釋，還有把每一句sql語句取出來，然后再利用各個平臺中的數據庫相關執行它。

接下來放代碼！

java版本的

package com.zz;

import java.io.*;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.List;
import java.util.Vector;

/*
 * 作者 祝君 
 * 時間 2014年1月16號
 * java執行數據庫腳本代碼
 */
public class SqlHelper {

    /**
     * @param args
     */
    public static void main(String[] args) {

        String path=new String("d:\\zzadmin.sql");
        String sql=GetText(path);
        String[] arr=getsql(sql);
        for(int i=0;i<arr.length;i++)
            System.out.println("第"+i+"句:"+arr[i]);

    }
    public static String GetText(String path){
        File file=new File(path);
        if(!file.exists()||file.isDirectory())
            return null;
        StringBuffer sb=new StringBuffer();
        try 
        {
            FileInputStream fis = new FileInputStream(path); 
            InputStreamReader isr = new InputStreamReader(fis, "UTF-8"); 
            BufferedReader br = new BufferedReader(isr); 
            String temp=null;
            temp=br.readLine();
            while(temp!=null){
            sb.append(temp+"\r\n");
            temp=br.readLine();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return sb.toString();
    }

    /**
     * 獲取sql文件中的sql語句數組
     * @param sql
     * @return 數組
     */
    public static String[] getsql(String sql)
    {
        String s=sql;
        s=s.replace("\r\n","\r");
        s=s.replace("\r", "\n");
        String[] ret=new String[1000];
        String[] sqlarray=s.split(";\n");
        sqlarray=filter(sqlarray);
        int num=0;
        for (String item : sqlarray) 
        {
            String ret_item = "";
            String[] querys = item.trim().split("\n");
            querys = filter(querys);//去空
            for (String query : querys) 
            {
                String str1 = query.substring(0, 1);
                String str2 = query.substring(0, 2);
                if (str1.equals("#") || str2.equals("--") || str2.equals("/*") || str2.equals("http://"))//去除注釋的關鍵步奏
                {
                    continue;
                }
                ret_item += query;
            }
            ret[num] = ret_item;
            num++;
        }
        return filter(ret);
    }
    /// <summary>
    /// 去除空值數組
    /// </summary>
    /// <param name="ss">數組</param>
    /// <returns></returns>
    public static String[] filter(String[] ss)
    {
        List<String> strs = new ArrayList<String>();
        for (String s : ss) {
             if (s != null && !s.equals("")) 
                 strs.add(s);
        }

        String[] result=new String[strs.size()];
        for(int i=0;i<strs.size();i++)
        {
            result[i]=strs.get(i).toString();
        }
        return result;
    }

    //刪除注釋
    public void deletezs(String fileStr)
    {
      try{
      Vector<String> vec=new Vector<String>();
      String str="",tm="",mm="";
      BufferedReader br = new BufferedReader( new FileReader(fileStr));
      boolean bol=false;
      while( null != (str = br.readLine() ) )
      {
        if ((str.indexOf("/*")>=0)&&((bol==false)))
        {
          if (str.indexOf("*/")>0)
          {
            bol=false;
            vec.addElement(str.substring(0,str.indexOf("/*"))+str.substring(str.indexOf("*/")+2,str.length()));
          }
          else
          {
             bol=true;
             mm=str.substring(0,str.indexOf("/*"));
             if (!(mm.trim().equals("")))
                 vec.addElement(mm);
          }
        }
        else if (bol==true)
        {
            if (str.indexOf("*/")>=0)
            {
                bol=false;
                mm=str.substring(str.indexOf("*/")+2,str.length());
                if (!mm.trim().equals(""))
                   vec.addElement(mm);
            }
        }
        else if (str.indexOf("http://")>=0)
        {
                     tm=str.substring(0,str.indexOf("http://"));
                     if (!tm.trim().equals(""))
                        vec.addElement(tm);
        }
        else
        {
            vec.addElement(str);
        }
        }
      br.close();
      File fName=new File(fileStr);
      FileWriter in=new  FileWriter(fName);
      String ssss="";
      Enumeration<String> ew=vec.elements();

             while (ew.hasMoreElements()) {
               ssss= ew.nextElement().toString();
               in.write(ssss+"\n");
             }

      in.close();
      vec.clear();

      }catch(Exception ee){
          ee.printStackTrace();
      }

    }


}

c#版本的

 //-------------------------第一種-------------------------------------
        /// <summary>
        /// 獲取sql文件中的sql語句數組 第一種方法
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static string[] sql_split(string sql)
        {
            string s = sql;
            Regex reg = new Regex("/TYPE=(InnoDB|MyISAM|MEMORY)( DEFAULT CHARSET=[^; ]+)?/");
            reg.Replace(sql, "ENGINE=\\1 DEFAULT CHARSET=utf8");
            s = s.Replace('\r', '\n');
            string[] ret = new string[10000];
            string[] sqlarray = StringSplit(s, ";\n");
            int num = 0;
            foreach (string item in sqlarray)
            {
                ret[num] = "";
                string[] queries = item.Split('\n');
                queries = filter(queries);
                foreach (string query in queries)
                {
                    string str1 = query.Substring(0, 1);
                    string str2 = query.Substring(0, 2);
                    if (str1 != "#" && str2 != "--" && str2 != "/*" && str2 != "http://")//去除注釋的關鍵步奏
                    {
                        ret[num] += query;
                    }
                }
                num++;
            }
            ret = filter(ret);
            return ret;
        }

        /// <summary>
        /// 去除空值數組
        /// </summary>
        /// <param name="ss"></param>
        /// <returns></returns>
        public static string[] filter(string[] ss)
        {
            List<string> strs = new List<string>();
            foreach (string s in ss)
            {
                if (!string.IsNullOrEmpty(s)) strs.Add(s);
            }
            string[] result = strs.ToArray();
            return result;
        }
        /// <summary>
        /// 將字符串分割成數組
        /// </summary>
        /// <param name="strSource"></param>
        /// <param name="strSplit"></param>
        /// <returns></returns>
        public static string[] StringSplit(string strSource, string strSplit)
        {
            string[] strtmp = new string[1];
            int index = strSource.IndexOf(strSplit, 0);
            if (index < 0)
            {
                strtmp[0] = strSource;
                return strtmp;
            }
            else
            {
                strtmp[0] = strSource.Substring(0, index);
                return StringSplit(strSource.Substring(index + strSplit.Length), strSplit, strtmp);
            }
        }

        /// <summary>
        /// 采用遞歸將字符串分割成數組
        /// </summary>
        /// <param name="strSource"></param>
        /// <param name="strSplit"></param>
        /// <param name="attachArray"></param>
        /// <returns></returns>
        private static string[] StringSplit(string strSource, string strSplit, string[] attachArray)
        {
            string[] strtmp = new string[attachArray.Length + 1];
            attachArray.CopyTo(strtmp, 0);

            int index = strSource.IndexOf(strSplit, 0);
            if (index < 0)
            {
                strtmp[attachArray.Length] = strSource;
                return strtmp;
            }
            else
            {
                strtmp[attachArray.Length] = strSource.Substring(0, index);
                return StringSplit(strSource.Substring(index + strSplit.Length), strSplit, strtmp);
            }
        }

        //-----------------------------------------------------

        //-----------------------第二種------------------------------
        /// <summary>
        /// 獲取sql文件中的sql語句數組 第二種
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string[] getsqls(string sql)
        {
            string s = sql;
            s = s.Replace("\r\n", "\n");
            s = s.Replace("\r","\n").Trim();
            string[] ret = new string[1000];

            string[] sqlarray= StringSplit(s, ";\n");
            sqlarray = filter(sqlarray);//去空

            int num=0;
            foreach (string item in sqlarray)
            {
                string ret_item = "";
                string[] querys = item.Trim().Split('\n');
                querys = filter(querys);//去空

                foreach (string query in querys)
                {
                    string str1 = query.Substring(0, 1);
                    string str2 = query.Substring(0, 2);
                    if (str1 == "#" || str2 == "--" || str2 == "/*" || str2 == "http://")//去除注釋的關鍵步奏
                    {
                        continue;
                    }
                    ret_item += query;
                }
                ret[num] = ret_item;
                num++;
            }
            return filter(ret);
        }