createtbssqlscript.cs

自己编写的基本Orcale的通用的数据库初始化工具。

using System;
using System.IO;
using System.Collections;
using System.Text.RegularExpressions;
using System.Windows.Forms;

namespace DS.EMIS.StartPrepare
{
	/// <summary>
	/// SQLScript 的摘要说明。
	/// </summary>
	public class CreateTBSSQLScript
	{

        public string sqltext = string.Empty;//要处理的脚本
        IList objectlist = new System.Collections.ArrayList();//脚本对象列表，用于存放分解之后的脚本
		public CreateTBSSQLScript()
		{
			//
			// TODO: 在此处添加构造函数逻辑
			//
		}
	

        /// <summary>
        /// 打开文件，并读取
        /// </summary>
        public void readfile()
        {
            OpenFileDialog openFileDialog1 = new OpenFileDialog();
            openFileDialog1.Filter = "SQL文件|*.sql";
            try
            {
                if(openFileDialog1.ShowDialog()  ==  DialogResult.OK)
                {
                    FileStream  fs  =  new  FileStream(openFileDialog1.FileName,FileMode.Open,FileAccess.Read);
                    StreamReader  m_streamReader  =  new  StreamReader(fs)  ;  
                    //使用StreamReader类来读取文件
                    m_streamReader.BaseStream.Seek(0,SeekOrigin.Begin)  ;
                    //  从数据流中读取每一行，直到文件的最后一行
                    string  strLine  =  m_streamReader.ReadLine()  ;
                    while(strLine!=null)
                    {
                        sqltext  +=  strLine  +  "\n"  ;
                        strLine  =  m_streamReader.ReadLine()  ;
                    }
                    //关闭此StreamReader对象
                    m_streamReader.Close()  ;
                }                 
            }
            catch(  Exception  em  ) 
            {
                throw em;
            }
        }


        /// <summary>
        /// 写文件
        /// </summary>
        public void writefile()
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            try
            {
                //获得另存为的文件名称
                if(  saveFileDialog.ShowDialog()  ==  DialogResult.OK  )
                {  
                    //创建一个文件流，用以写入或者创建一个StreamWriter
                    FileStream  fs  =  new  FileStream(@saveFileDialog.FileName,FileMode.OpenOrCreate,FileAccess.Write)  ;
                    StreamWriter  m_streamWriter  =  new  StreamWriter(fs)  ;
                    m_streamWriter.Flush()  ;
    
                    //  使用StreamWriter来往文件中写入内容
                    m_streamWriter.BaseStream.Seek(0, SeekOrigin.Begin)  ;
                    //  把对象中的内容写入文件
                    m_streamWriter.Write(ObjecttoText())  ;
                    //关闭此文件
                    m_streamWriter.Flush()  ;
                    m_streamWriter.Close()  ;
                }
            }
            catch(Exception  em)
            {
                throw em;
            }
        }

        /// <summary>
        /// 文本转换成对象
        /// 通过CREATE分割成一个个子串，每个子串对应一个对象
        /// </summary>   
        public IList TexttoObject(string sqlstring)
        {
            try
            {
                string[] sqlStringList = Split(sqlstring,"CREATE");
                foreach(string matchstring in sqlStringList)
                {
                    if(matchstring.StartsWith("CREATE"))
                    {
                        Sqlobject sqlobject = new Sqlobject();
                        //两个单引号之间的是datafile
                        string Pattern = @"'[\s\S]*'";
                        MatchCollection Matchs1 = Regex.Matches(matchstring,Pattern,RegexOptions.IgnoreCase|RegexOptions.ExplicitCapture);
                        if(Matchs1[0].Success)sqlobject.Datafile = matchstring.Substring(Matchs1[0].Index+1,Matchs1[0].Length-2);
                        // SIZE 空间大小
                        Pattern = @"\sSIZE\s[0-9]+M\s";
                        MatchCollection Matchs2 = Regex.Matches(matchstring,Pattern,RegexOptions.IgnoreCase|RegexOptions.ExplicitCapture);
                        if(Matchs2[0].Success)sqlobject.Size = matchstring.Substring(Matchs2[0].Index+6,Matchs2[0].Length-8);
                        // AUTOEXTEND ON NEXT 扩展串
                        Pattern = @"\sNEXT\s[0-9]+M\s";
                        MatchCollection Matchs3 = Regex.Matches(matchstring,Pattern,RegexOptions.IgnoreCase|RegexOptions.ExplicitCapture);
                        if(Matchs3[0].Success)sqlobject.Nextsize = matchstring.Substring(Matchs3[0].Index+6,Matchs3[0].Length-8);

                        sqlobject.P_nextsize = matchstring.Substring(Matchs3[0].Index+Matchs3[0].Length-1,matchstring.Length-Matchs3[0].Index-Matchs3[0].Length);
                        objectlist.Add(sqlobject);
                    }
                }
                return objectlist;
            }
            catch(Exception  em)
            {
                throw em;
                return objectlist;
            }
        }

        /// <summary>
        /// 取得分割后的字符串数据,数组以0开始
        /// </summary>
        /// <param name="str">字符串</param>
        /// <param name="separator">分割符</param>
        /// <returns>返回分割后的字符串数组</returns>
        /// <example>string str=Split("rrrrddrew@@tqwewerewddccc@@", "@@")[1].ToString()</example>
        public static string[] Split(string str, string separator)
        {
            int Len = str.Length;
            int Count = 0;
            int BitCount = 0;
            string strValue = "";
            string InSeparator = "";
            InSeparator = str.Substring(str.Length - separator.Length, separator.Length);
            if (InSeparator != separator)
            {
                str += separator;
            }
            Count = (str.Length - str.Replace(separator, "").Length) / separator.Length;
            string[] returnValue = new string[Count];
            
            BitCount = str.IndexOf(separator, 0);
            strValue = str.Substring(0, BitCount);
            str = str.Replace(strValue, "");
            returnValue[0] = strValue;

            for (int i = 1; i < Count; i++)
            {
                BitCount = str.IndexOf(separator, 6);//第一个分割符不算
                strValue = str.Substring(0, BitCount);
                str = str.Replace(strValue, "");                
                returnValue[i] = strValue;
            }
            return returnValue;
        }

        /// <summary>
        /// 对象转换成文本
        /// </summary>
        public string ObjecttoText()
        {
            string rtnstring = string.Empty;
            foreach(Sqlobject sqlobject in objectlist)
            {
                rtnstring +="CREATE TABLESPACE USER_DATA_TABSPACE\nDATAFILE \'"+sqlobject.Datafile+"\'";
                rtnstring +=" SIZE "+sqlobject.Size+"M";
                rtnstring +=" AUTOEXTEND ON NEXT "+sqlobject.Nextsize+"M ";
                rtnstring += sqlobject.P_nextsize;
                rtnstring +="\n";
            }
            //\n显示为黑方格
            rtnstring = rtnstring.Replace("\n","\r\n");
            return rtnstring;
        }

        /// <summary>
        /// 对象转换成文本
        /// </summary>
        public string UserObjecttoText(User user)
        {
            string rtnstring = string.Empty;
            
            rtnstring +="--创建用户\n CREATE USER " + user.UserName + " PROFILE DEFAULT IDENTIFIED BY "+user.PassWord;
            rtnstring +="\nDEFAULT TABLESPACE USER_DATA_TABSPACE \n TEMPORARY TABLESPACE USER_TEMP_TABSPACE \nACCOUNT UNLOCK;\n\n\n\n";
            rtnstring +=" --授权\n GRANT CONNECT TO " + user.UserName + ";\n GRANT DBA TO " + user.UserName + ";";
            rtnstring +="\n";
           
            //\n显示为黑方格
            rtnstring = rtnstring.Replace("\n","\r\n");
            return rtnstring;
        }
        /// <summary>
        /// 文本转换成对象
        /// 通过CREATE分割成一个个子串，每个子串对应一个对象
        /// </summary>   
        public User TexttoUser(string sqlstring)
        {
            try
            {
                User user = new User();
                //用户名
                string Pattern = @"USER[\s\S]*PROFILE";
                MatchCollection Matchs1 = Regex.Matches(sqlstring,Pattern,RegexOptions.IgnoreCase|RegexOptions.ExplicitCapture);
                if(Matchs1[0].Success)user.UserName = sqlstring.Substring(Matchs1[0].Index+5,Matchs1[0].Length-13);
                // SIZE 空间大小
                Pattern = @"IDENTIFIED\sBY[\s]*[\S]*[\r|\n|\r\n|\n\r]";
                MatchCollection Matchs2 = Regex.Matches(sqlstring,Pattern,RegexOptions.IgnoreCase|RegexOptions.ExplicitCapture);
                if(Matchs2[0].Success)user.PassWord = sqlstring.Substring(Matchs2[0].Index+14,Matchs2[0].Length-15);
                return user;              
            }
            catch(Exception  em)
            {
                throw em;
            }
        }
    }

    /// <summary>
    /// 脚本对象
    /// </summary>
    public class User
    {
        private string _UserName;//用户名
        public  string UserName
        {
            get{return this._UserName;}
            set{this._UserName = value;}
        }

        private string _PassWord;//密码
        public  string PassWord
        {
            get{return this._PassWord;}
            set{this._PassWord = value;}
        }
    }

    /// <summary>
    /// 脚本对象
    /// </summary>
    public class Sqlobject
    {
        private string _F_datafile;//DadaFile之前的串
        public  string F_datafile
        {
            get{return this._F_datafile;}
            set{this._F_datafile = value;}
        }

        private string _Datafile;//DadaFile串
        public  string Datafile
        {
            get{return this._Datafile;}
            set{this._Datafile = value;}
        }

        private string _F_size;//空间大小之前的串
        public  string F_size
        {
            get{return this._F_size;}
            set{this._F_size = value;}
        }

        private string _Size;//空间大小的串
        public  string Size
        {
            get{return this._Size;}
            set{this._Size = value;}
        }

        private string _F_nextsize;//扩展空间大小之前的串
        public  string F_nextsize
        {
            get{return this._F_nextsize;}
            set{this._F_nextsize = value;}
        }

        private string _Nextsize;//扩展空间大小串
        public  string Nextsize
        {
            get{return this._Nextsize;}
            set{this._Nextsize = value;}
        }

        private string _P_nextsize;//扩展空间大小之后的串
        public  string P_nextsize
        {
            get{return this._P_nextsize;}
            set{this._P_nextsize = value;}
        }
    }
}