由于项目需要,我写了一个初始MySQL数据库的工具;
一, 思路:
(1),首先读取包含mysql语句的脚本(其中有创建数据库,表,插入初始数据等!)。
(2),然后替换里面的一些数据值(如guid,注释等!)。
(3),把替换后的数据流重新写入mysql脚本。
(4),调用MySQL命令执行相应的命令。
主要思路结束!
二, 现具体过程如下:
1,主界面如图:
![52e48f479a9a5ae29213a41e99130562.png](https://img.php1.cn/3cd4a/1eebe/cd5/1113165c4904ecc5.webp)
2,全局代码和构造方法如下:
![9310e85a14af99de4811ff4c77f1f911.png](https://img.php1.cn/3cd4a/1eebe/cd5/857a46d091981bac.webp)
Code
stringIP;
stringuserName;
stringuserPwd;
stringpath;
stringmesg="";
Boolean info=false;
//字符串形式的guid
string_guID="";
stringtimeMesg="";
inti=0;
//测量间隔运行时间
Stopwatch stop=newStopwatch();
publicToolForm()
![24a924a57ba6b3f2b51fc9edb7ea4186.png](https://img.php1.cn/3cd4a/1eebe/cd5/8be1ccb5166feb93.webp)
![9310e85a14af99de4811ff4c77f1f911.png](https://img.php1.cn/3cd4a/1eebe/cd5/857a46d091981bac.webp)
{
InitializeComponent();
}
3,在按钮事件中验证了各个文本框中的值是否为空;IP地址和端口号的验证!
代码如下:
![9310e85a14af99de4811ff4c77f1f911.png](https://img.php1.cn/3cd4a/1eebe/cd5/857a46d091981bac.webp)
Code![24a924a57ba6b3f2b51fc9edb7ea4186.png](https://img.php1.cn/3cd4a/1eebe/cd5/8be1ccb5166feb93.webp)
/**
///按钮事件
///
///
///
privatevoidbtnOK_Click(objectsender, EventArgs e)
![24a924a57ba6b3f2b51fc9edb7ea4186.png](https://img.php1.cn/3cd4a/1eebe/cd5/8be1ccb5166feb93.webp)
![9310e85a14af99de4811ff4c77f1f911.png](https://img.php1.cn/3cd4a/1eebe/cd5/857a46d091981bac.webp)
{
i=0;
this.lbMessage.Text="";
IP=this.tbIP.Text.Trim();
userName=this.tbUserName.Text.Trim();
userPwd=this.tbPWD.Text.Trim();
DirPath=Application.StartupPath;
Regex regexIP=newRegex(@"^((0|1[0-9]{0,2}|2[0-9]{0,1}|2[0-4][0-9]|25[0-5]|[3-9][0-9]{0,1})\.){3}(0|1[0-9]{0,2}|2[0-9]{0,1}|2[0-4][0-9]|25[0-5]|[3-9][0-9]{0,1})$");
if(string.IsNullOrEmpty(IP))
![715f2d05503b99d41f3b6ba2cdccc84d.png](https://img.php1.cn/3cd4a/1eebe/cd5/bdd1ca32a69bc8b2.webp)
![e083dfde5a91f50979fe8979b4012b9d.png](https://img.php1.cn/3cd4a/1eebe/cd5/43a754c811e7ec5c.webp)
{
MessageBox.Show("请输入服务器地址!");
this.tbIP.Focus();
return;
}
elseif(string.IsNullOrEmpty(userName))
![715f2d05503b99d41f3b6ba2cdccc84d.png](https://img.php1.cn/3cd4a/1eebe/cd5/bdd1ca32a69bc8b2.webp)
![e083dfde5a91f50979fe8979b4012b9d.png](https://img.php1.cn/3cd4a/1eebe/cd5/43a754c811e7ec5c.webp)
{
MessageBox.Show("请输入用户名!");
this.tbUserName.Focus();
return;
}
elseif(string.IsNullOrEmpty(userPwd))
![715f2d05503b99d41f3b6ba2cdccc84d.png](https://img.php1.cn/3cd4a/1eebe/cd5/bdd1ca32a69bc8b2.webp)
![e083dfde5a91f50979fe8979b4012b9d.png](https://img.php1.cn/3cd4a/1eebe/cd5/43a754c811e7ec5c.webp)
{
MessageBox.Show("请输入密码!");
this.tbPWD.Focus();
return;
}
elseif(regexIP.IsMatch(IP)||(string.Compare(IP,"localhost",true)==0))
![715f2d05503b99d41f3b6ba2cdccc84d.png](https://img.php1.cn/3cd4a/1eebe/cd5/bdd1ca32a69bc8b2.webp)
![e083dfde5a91f50979fe8979b4012b9d.png](https://img.php1.cn/3cd4a/1eebe/cd5/43a754c811e7ec5c.webp)
{
//得到目录下的所有文件
string[] files=Directory.GetFiles(DirPath);
//循环读文件
foreach(stringfileinfiles)
![715f2d05503b99d41f3b6ba2cdccc84d.png](https://img.php1.cn/3cd4a/1eebe/cd5/bdd1ca32a69bc8b2.webp)
![e083dfde5a91f50979fe8979b4012b9d.png](https://img.php1.cn/3cd4a/1eebe/cd5/43a754c811e7ec5c.webp)
{
//如果后缀名为.sql文件
if(".sql".IndexOf(file.Substring(file.LastIndexOf(".")+1))>-1)
![715f2d05503b99d41f3b6ba2cdccc84d.png](https://img.php1.cn/3cd4a/1eebe/cd5/bdd1ca32a69bc8b2.webp)
![e083dfde5a91f50979fe8979b4012b9d.png](https://img.php1.cn/3cd4a/1eebe/cd5/43a754c811e7ec5c.webp)
{
//把.sql文件全名加人到fileList集合中
fileList.Add(file);
}
}![1408c5260b2f05e450dee929db9be5f7.png](https://img.php1.cn/3cd4a/189d8/b64/5b34b53b79a39fdd.jpeg)
if(fileList.Count>1)
![715f2d05503b99d41f3b6ba2cdccc84d.png](https://img.php1.cn/3cd4a/1eebe/cd5/bdd1ca32a69bc8b2.webp)
![e083dfde5a91f50979fe8979b4012b9d.png](https://img.php1.cn/3cd4a/1eebe/cd5/43a754c811e7ec5c.webp)
{
MessageBox.Show("目录下多个.sql文件!该程序只能读一个!");
return;
}
elseif(fileList.Count<1)
![715f2d05503b99d41f3b6ba2cdccc84d.png](https://img.php1.cn/3cd4a/1eebe/cd5/bdd1ca32a69bc8b2.webp)
![e083dfde5a91f50979fe8979b4012b9d.png](https://img.php1.cn/3cd4a/1eebe/cd5/43a754c811e7ec5c.webp)
{
MessageBox.Show("目录下没有.sql文件&#xff01;");
return;
}
else![715f2d05503b99d41f3b6ba2cdccc84d.png](https://img.php1.cn/3cd4a/1eebe/cd5/bdd1ca32a69bc8b2.webp)
![e083dfde5a91f50979fe8979b4012b9d.png](https://img.php1.cn/3cd4a/1eebe/cd5/43a754c811e7ec5c.webp)
{
path&#61;fileList.First();
this.lbMessage.Text&#61;"正在导入数据&#xff01;请稍等
..";
//测量时间开始
stop.Start();
//调用主方法
MainMethod();
}
}
else![715f2d05503b99d41f3b6ba2cdccc84d.png](https://img.php1.cn/3cd4a/1eebe/cd5/bdd1ca32a69bc8b2.webp)
![e083dfde5a91f50979fe8979b4012b9d.png](https://img.php1.cn/3cd4a/1eebe/cd5/43a754c811e7ec5c.webp)
{
MessageBox.Show("服务器IP地址不规范&#xff01;");
this.tbIP.Focus();
return;
}
}![731655954c7be9d8835ece551b5385f8.png](https://img.php1.cn/3cd4a/1eebe/cd5/2fdc212433a29829.png)
4&#xff0c; 如果输入符和规范&#xff0c;则调用主方法(主方法里实现思路中主要方法的调用)&#xff1b;
主方法代码如下&#xff1a;
![9310e85a14af99de4811ff4c77f1f911.png](https://img.php1.cn/3cd4a/1eebe/cd5/857a46d091981bac.webp)
Code![24a924a57ba6b3f2b51fc9edb7ea4186.png](https://img.php1.cn/3cd4a/1eebe/cd5/8be1ccb5166feb93.webp)
/**
///主方法
///
///
///
///
///
///
privatevoidMainMethod()
![24a924a57ba6b3f2b51fc9edb7ea4186.png](https://img.php1.cn/3cd4a/1eebe/cd5/8be1ccb5166feb93.webp)
![9310e85a14af99de4811ff4c77f1f911.png](https://img.php1.cn/3cd4a/1eebe/cd5/857a46d091981bac.webp)
{
try![715f2d05503b99d41f3b6ba2cdccc84d.png](https://img.php1.cn/3cd4a/1eebe/cd5/bdd1ca32a69bc8b2.webp)
![e083dfde5a91f50979fe8979b4012b9d.png](https://img.php1.cn/3cd4a/1eebe/cd5/43a754c811e7ec5c.webp)
{
//读文件
stringfileRed&#61;ReadSQLFromFile(path);
//写文件
File.WriteAllText(path, fileRed);
//调用进度条的timer
this.timer1.Start();
//调用执行脚本文件的timer
this.timer2.Start();
}
catch(Exception ex)
![715f2d05503b99d41f3b6ba2cdccc84d.png](https://img.php1.cn/3cd4a/1eebe/cd5/bdd1ca32a69bc8b2.webp)
![e083dfde5a91f50979fe8979b4012b9d.png](https://img.php1.cn/3cd4a/1eebe/cd5/43a754c811e7ec5c.webp)
{
MessageBox.Show(ex.Message);
}![1408c5260b2f05e450dee929db9be5f7.png](https://img.php1.cn/3cd4a/189d8/b64/5b34b53b79a39fdd.jpeg)
}
5&#xff0c; 读脚本文件&#xff0c;替换guid标识的方法&#xff1b;
代码如下&#xff1a;
![9310e85a14af99de4811ff4c77f1f911.png](https://img.php1.cn/3cd4a/1eebe/cd5/857a46d091981bac.webp)
Code![24a924a57ba6b3f2b51fc9edb7ea4186.png](https://img.php1.cn/3cd4a/1eebe/cd5/8be1ccb5166feb93.webp)
/**
///读文件流,替换guid标示
///
///
///
///
privatestringReadSQLFromFile(stringpath)
![24a924a57ba6b3f2b51fc9edb7ea4186.png](https://img.php1.cn/3cd4a/1eebe/cd5/8be1ccb5166feb93.webp)
![9310e85a14af99de4811ff4c77f1f911.png](https://img.php1.cn/3cd4a/1eebe/cd5/857a46d091981bac.webp)
{
//新生成一个guid
Guid guId&#61;Guid.NewGuid();
//把guid转换为字符串形式的guid
_guID&#61;"{"&#43;guId&#43;"}";
FileStream fs&#61;null;
stringstrQuery&#61;"";
try![715f2d05503b99d41f3b6ba2cdccc84d.png](https://img.php1.cn/3cd4a/1eebe/cd5/bdd1ca32a69bc8b2.webp)
![e083dfde5a91f50979fe8979b4012b9d.png](https://img.php1.cn/3cd4a/1eebe/cd5/43a754c811e7ec5c.webp)
{
fs&#61;newFileStream(path, FileMode.Open, FileAccess.Read);
stringstrTemp&#61;Encoding.UTF8.GetString(ConvertStreamToByteBuffer(fs));
//用新生成的guid替换掉脚本文件中的guid;
strQuery&#61;Regex.Replace(strTemp,&#64;"(\{[A-Fa-f0-9]{8}(-[A-Fa-f0-9]{4}){3}-[A-Fa-f0-9]{12}\})&#43;", _guID, RegexOptions.Singleline);
intindexValue&#61;strQuery.IndexOf("USE")&#43;3;
intindexStart&#61;indexValue;
intindexEnd&#61;0;
while(strQuery.Substring(indexValue,1)!&#61;";")
![715f2d05503b99d41f3b6ba2cdccc84d.png](https://img.php1.cn/3cd4a/1eebe/cd5/bdd1ca32a69bc8b2.webp)
![e083dfde5a91f50979fe8979b4012b9d.png](https://img.php1.cn/3cd4a/1eebe/cd5/43a754c811e7ec5c.webp)
{
indexValue&#43;&#43;;
indexEnd&#61;indexValue;
}
DBName&#61;strQuery.Substring(indexStart, (indexEnd-indexStart));
}
catch(Exception ex)
![715f2d05503b99d41f3b6ba2cdccc84d.png](https://img.php1.cn/3cd4a/1eebe/cd5/bdd1ca32a69bc8b2.webp)
![e083dfde5a91f50979fe8979b4012b9d.png](https://img.php1.cn/3cd4a/1eebe/cd5/43a754c811e7ec5c.webp)
{
MessageBox.Show(ex.Message);
strQuery&#61;"";
}
finally![715f2d05503b99d41f3b6ba2cdccc84d.png](https://img.php1.cn/3cd4a/1eebe/cd5/bdd1ca32a69bc8b2.webp)
![e083dfde5a91f50979fe8979b4012b9d.png](https://img.php1.cn/3cd4a/1eebe/cd5/43a754c811e7ec5c.webp)
{
fs.Close();
}
returnstrQuery;
}![731655954c7be9d8835ece551b5385f8.png](https://img.php1.cn/3cd4a/1eebe/cd5/2fdc212433a29829.png)
6&#xff0c;把替换后的字符串写入脚本文件&#xff1a;
代码如下&#xff1a;
![9310e85a14af99de4811ff4c77f1f911.png](https://img.php1.cn/3cd4a/1eebe/cd5/857a46d091981bac.webp)
Code
File.WriteAllText(path, fileRed);
7&#xff0c;然后是开启两个timer&#xff0c;一个控制进度条&#xff0c;另一个获取执行时间的间隔。这里略。。。
8&#xff0c;在获取执行时间的timer事件中&#xff0c;调用执行脚本文件的方法。
该方法的代码如下&#xff1a;
![9310e85a14af99de4811ff4c77f1f911.png](https://img.php1.cn/3cd4a/1eebe/cd5/857a46d091981bac.webp)
Code![24a924a57ba6b3f2b51fc9edb7ea4186.png](https://img.php1.cn/3cd4a/1eebe/cd5/8be1ccb5166feb93.webp)
/**
///调用mysql命令来执行脚本文文件
///
///
///
///
///
///
publicstringRunCmd()
![24a924a57ba6b3f2b51fc9edb7ea4186.png](https://img.php1.cn/3cd4a/1eebe/cd5/8be1ccb5166feb93.webp)
![9310e85a14af99de4811ff4c77f1f911.png](https://img.php1.cn/3cd4a/1eebe/cd5/857a46d091981bac.webp)
{
Stopwatch a&#61;newStopwatch();
longm&#61;a.ElapsedMilliseconds;
stringmesg&#61;"";
stringmesgError&#61;"";
Process p&#61;null;
try![715f2d05503b99d41f3b6ba2cdccc84d.png](https://img.php1.cn/3cd4a/1eebe/cd5/bdd1ca32a69bc8b2.webp)
![e083dfde5a91f50979fe8979b4012b9d.png](https://img.php1.cn/3cd4a/1eebe/cd5/43a754c811e7ec5c.webp)
{
![e083dfde5a91f50979fe8979b4012b9d.png](https://img.php1.cn/3cd4a/1eebe/cd5/43a754c811e7ec5c.webp)
进入cmd调用mysql命令来执行脚本文件#region进入cmd调用mysql命令来执行脚本文件
p&#61;newProcess();
p.StartInfo.FileName&#61;"cmd.exe";
p.StartInfo.UseShellExecute&#61;false;
p.StartInfo.RedirectStandardInput&#61;true;
p.StartInfo.RedirectStandardOutput&#61;true;
p.StartInfo.RedirectStandardError&#61;true;
p.StartInfo.CreateNoWindow&#61;true;
p.Start();
//p.StandardInput.WriteLine(&#64;"net start mysql");
p.StandardInput.WriteLine(&#64;"c:");
p.StandardInput.WriteLine(&#64;"cd C:\Program Files\MySQL\MySQL Server 5.0\bin");
![715f2d05503b99d41f3b6ba2cdccc84d.png](https://img.php1.cn/3cd4a/1eebe/cd5/bdd1ca32a69bc8b2.webp)
p.StandardInput.WriteLine(string.Format("mysql.exe -h{0} -u{1} -p{2} -f{3}\"", IP, userName, userPwd, path));
p.StandardInput.WriteLine("exit");
mesgError&#61;p.StandardError.ReadToEnd();
mesg&#61;mesgError;
p.WaitForExit();
#endregion
}
catch(Exception err)
![715f2d05503b99d41f3b6ba2cdccc84d.png](https://img.php1.cn/3cd4a/1eebe/cd5/bdd1ca32a69bc8b2.webp)
![e083dfde5a91f50979fe8979b4012b9d.png](https://img.php1.cn/3cd4a/1eebe/cd5/43a754c811e7ec5c.webp)
{
//MessageBox.Show(err.Message);//显示错误信息。
mesg&#61;err.Message;
}
finally![715f2d05503b99d41f3b6ba2cdccc84d.png](https://img.php1.cn/3cd4a/1eebe/cd5/bdd1ca32a69bc8b2.webp)
![e083dfde5a91f50979fe8979b4012b9d.png](https://img.php1.cn/3cd4a/1eebe/cd5/43a754c811e7ec5c.webp)
{
p.Close();
}
returnmesg;
}![731655954c7be9d8835ece551b5385f8.png](https://img.php1.cn/3cd4a/1eebe/cd5/2fdc212433a29829.png)
9,把给定的文件流转换为二进制字节数组的方法,
代码如下:
![9310e85a14af99de4811ff4c77f1f911.png](https://img.php1.cn/3cd4a/1eebe/cd5/857a46d091981bac.webp)
Code![9310e85a14af99de4811ff4c77f1f911.png](https://img.php1.cn/3cd4a/1eebe/cd5/857a46d091981bac.webp)
把给定的文件流转换为二进制字节数组byte[] ConvertStreamToByteBuffer(System.IO.Stream theStream)#region把给定的文件流转换为二进制字节数组byte[] ConvertStreamToByteBuffer(System.IO.Stream theStream)![715f2d05503b99d41f3b6ba2cdccc84d.png](https://img.php1.cn/3cd4a/1eebe/cd5/bdd1ca32a69bc8b2.webp)
/**
///把给定的文件流转换为二进制字节数组。
///
///
///
publicbyte[] ConvertStreamToByteBuffer(System.IO.Stream theStream)
![715f2d05503b99d41f3b6ba2cdccc84d.png](https://img.php1.cn/3cd4a/1eebe/cd5/bdd1ca32a69bc8b2.webp)
![e083dfde5a91f50979fe8979b4012b9d.png](https://img.php1.cn/3cd4a/1eebe/cd5/43a754c811e7ec5c.webp)
{
intb1;
System.IO.MemoryStream tempStream&#61;newSystem.IO.MemoryStream();
while((b1&#61;theStream.ReadByte())!&#61;-1)
![715f2d05503b99d41f3b6ba2cdccc84d.png](https://img.php1.cn/3cd4a/1eebe/cd5/bdd1ca32a69bc8b2.webp)
![e083dfde5a91f50979fe8979b4012b9d.png](https://img.php1.cn/3cd4a/1eebe/cd5/43a754c811e7ec5c.webp)
{
tempStream.WriteByte(((byte)b1));
}
returntempStream.ToArray();
}
#endregion
10,控制进度条的timer事件;
代码如下:
![9310e85a14af99de4811ff4c77f1f911.png](https://img.php1.cn/3cd4a/1eebe/cd5/857a46d091981bac.webp)
Code//控制进度条显示/privatevoidtimer1_Tick(objectsender, EventArgs e)
{try{this.progressBar1.Value&#61;i;
i&#43;&#61;10;if(i&#61;&#61;1000)
{this.timer1.Stop();if(string.IsNullOrEmpty(mesg)&&info)
{
lbMessage.Text&#61;"数据导入成功&#xff01;"&#43;timeMesg;newMCFrom(_guID, IP, userName, userPwd, DBName).ShowDialog();
}else{
lbMessage.Text&#61;"数据导入失败&#xff01;";
}
stop.Reset();
}
}catch(System.Exception )
{
}
}
11,获取执行时间间隔的time事件;
代码如下:
![9310e85a14af99de4811ff4c77f1f911.png](https://img.php1.cn/3cd4a/1eebe/cd5/857a46d091981bac.webp)
Code![24a924a57ba6b3f2b51fc9edb7ea4186.png](https://img.php1.cn/3cd4a/1eebe/cd5/8be1ccb5166feb93.webp)
/**
///调用执行脚本文件的方法&#xff0c;并得到从点击按钮到成功导入数据库后的间隔时间。
///
///
///
privatevoidtimer2_Tick(objectsender, EventArgs e)
![24a924a57ba6b3f2b51fc9edb7ea4186.png](https://img.php1.cn/3cd4a/1eebe/cd5/8be1ccb5166feb93.webp)
![9310e85a14af99de4811ff4c77f1f911.png](https://img.php1.cn/3cd4a/1eebe/cd5/857a46d091981bac.webp)
{
//执行文件中的命令
mesg&#61;RunCmd();
this.timer2.Stop();
info&#61;true;
timeMesg&#61;stop.Elapsed.Seconds.ToString()&#43;"秒";
stop.Stop();
}
12,整个小工具大体的程序代码完成&#xff01;有些细节省略掉了 &#xff01;请在我的随笔里查看&#xff01;
![a728892deccad84897bd2f7556d5b8eb.png](https://img.php1.cn/3cd4a/1eebe/cd5/7494af3c1cda418d.webp)