www.pudn.com > startprepare.rar > CreateTBSSQLScript.cs
using System;
using System.IO;
using System.Collections;
using System.Text.RegularExpressions;
using System.Windows.Forms;
namespace DS.EMIS.StartPrepare
{
///
/// SQLScript 的摘要说明。
///
public class CreateTBSSQLScript
{
public string sqltext = string.Empty;//要处理的脚本
IList objectlist = new System.Collections.ArrayList();//脚本对象列表，用于存放分解之后的脚本
public CreateTBSSQLScript()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
///
/// 打开文件，并读取
///
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;
}
}
///
/// 写文件
///
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;
}
}
///
/// 文本转换成对象
/// 通过CREATE分割成一个个子串，每个子串对应一个对象
///
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;
}
}
///
/// 取得分割后的字符串数据,数组以0开始
///
/// 字符串
/// 分割符
/// 返回分割后的字符串数组
/// string str=Split("rrrrddrew@@tqwewerewddccc@@", "@@")[1].ToString()
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;
}
///
/// 对象转换成文本
///
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;
}
///
/// 对象转换成文本
///
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;
}
///
/// 文本转换成对象
/// 通过CREATE分割成一个个子串，每个子串对应一个对象
///
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;
}
}
}
///
/// 脚本对象
///
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;}
}
}
///
/// 脚本对象
///
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;}
}
}
}