www.pudn.com > sqlTool.rar > FormsqlTool.cs
/*****************************************
* Create by zhangchuan
*
* solution sqlTool (C) 2006
*
* 执行自定义sql语句到指定数据库，可以方便测试人员。
* **************************************/
using System;
using System.IO;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Runtime.InteropServices;
namespace sqlTool
{
public partial class FormSqlTool : Form
{
public FormSqlTool()
{
InitializeComponent();
}
[DllImport("kernel32")]
private static extern long WritePrivateProfileString(string section,
string key, string val, string filepath);
[DllImport("kernel32")]
private static extern int GetPrivateProfileString(string section,
string key, string def, StringBuilder retval,
int size, string filepath);
public void iniWriteValue(string section, string key, string value, string filepath)//对ini文件进行写操作的函数
{
WritePrivateProfileString(section, key, value, filepath);
}
public string iniReadValue(string section, string key, string filepath)//对ini文件进行读操作的函数
{
StringBuilder temp = new StringBuilder(255);
int i = GetPrivateProfileString(section, key, "", temp, 255, filepath);
return temp.ToString();
}
public string getConnString()
{
string connString;
if(radioButton2.Checked)//如果选择的是sql
{
connString = "server="+textBoxSqlServer.Text+
";uid="+textBoxUser.Text+
";pwd="+textBoxPsw.Text+
";database="+textBoxDbName.Text;
return connString;
}
else if(radioButton1.Checked)
{
connString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source="+textBoxFileName.Text;
return connString;
}
else
{
connString ="";
MessageBox.Show("请选择一种连接方式!");
return connString;
}
}
private void linkLabelContextMenu_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
contextMenuStrip1.Left = linkLabelContextMenu.Left;
contextMenuStrip1.Top = linkLabelContextMenu.Top;
contextMenuStrip1.Show(MousePosition);
}
private void buttonOpen_Click(object sender, EventArgs e)
{
openFileDialog1.ShowDialog();
if (openFileDialog1.CheckFileExists)
textBoxFileName.Text = openFileDialog1.FileName;
}
private void radioButton2_CheckedChanged(object sender, EventArgs e)
{
if (radioButton2.Checked)
{
textBoxSqlServer.Enabled = true;
textBoxUser.Enabled = true;
textBoxPsw.Enabled = true;
textBoxDbName.Enabled = true;
buttonTest.Enabled = true;
buttonOpen.Enabled = false;
}
else
{
textBoxSqlServer.Enabled = false;
textBoxUser.Enabled = false;
textBoxPsw.Enabled = false;
textBoxDbName.Enabled = false;
buttonTest.Enabled = false;
buttonOpen.Enabled = true;
}
}
private void buttonTest_Click(object sender, EventArgs e)
{
string connString = getConnString();
SqlConnection conn = new SqlConnection(connString);
// OleDbConnection conn = new OleDbConnection(connString);
try
{
conn.Open();
//statusStrip1.Items.Clear();
if(conn.State.ToString()=="Open")
statusStrip1.Items[0].Text = "连接成功" ;
}
catch(Exception ex)
{
//statusStrip1.Items.Clear();
statusStrip1.Items[0].Text = "连接失败";
}
finally
{
conn.Close();
}
}
//public string pathStr = (Application.ExecutablePath);
public string m_filename = (Application.ExecutablePath).Substring(0, (Application.ExecutablePath).Length - 4) + ".ini";
private void FormSqlTool_Load(object sender, EventArgs e)
{
if (!File.Exists(m_filename))
{
File.Create(m_filename);
}
}
private void FormSqlTool_Shown(object sender, EventArgs e)
{
textBoxSqlServer.Text = iniReadValue("sql", "server", m_filename);
textBoxUser.Text = iniReadValue("sql", "uid", m_filename);
textBoxPsw.Text = iniReadValue("sql", "pwd", m_filename);
textBoxDbName.Text = iniReadValue("sql", "dbname", m_filename);
textBoxSqlStr.Text = iniReadValue("sqlstring", "str", m_filename);
}
//连接 sql 数据库
public void connectBySql()
{
int i,recordChanged = 0;
string connString = getConnString();
string sqlString = textBoxSqlStr.Text;
SqlConnection conn = new SqlConnection(connString);
int loopTime;
//如果选择循环
if (checkBoxLoop.Checked/* && textBoxSqlStr.Text.Substring(0, 6).ToLower() != "delete" && textBoxSqlStr.Text.Substring(0, 6).ToLower()!="update"*/)
{
loopTime = (int)numericLoopTime.Value;
}
else
{
loopTime = 1;
}
try
{
conn.Open();
// statusStrip1.Items.Add(sqlString);
//执行循环的次数。
for (i = 0; i < loopTime; i++)
{
SqlCommand cmd = new SqlCommand(confusesqlString(sqlString,i), conn);
recordChanged += cmd.ExecuteNonQuery();
// if (recordChanged == 0)
// break;
}
statusStrip1.Items[0].Text = "执行成功";
statusStrip1.Items[1].Text = recordChanged + "条记录受影响。";
}
catch (Exception ex)
{
//statusStrip1.Items.Clear();
statusStrip1.Items[0].Text = "连接失败";
statusStrip1.Items[1].Text = "";
}
finally
{
conn.Close();
}
}
//连接access数据库
public void connectByAccess()
{
int i,recordChanged = 0;
string connString = getConnString();
string sqlString = textBoxSqlStr.Text;
OleDbConnection conn = new OleDbConnection(connString);
int loopTime;
//如果选择循环
if (checkBoxLoop.Checked)
{
loopTime = (int)numericLoopTime.Value;
}
else
{
loopTime = 1;
}
try
{
conn.Open();
// statusStrip1.Items.Add(sqlString);
//执行循环的次数。
for (i = 0; i < loopTime; i++)
{
OleDbCommand cmd = new OleDbCommand(confusesqlString(sqlString, i), conn);
recordChanged += cmd.ExecuteNonQuery();
// if (recordChanged == 0)
// break;
}
statusStrip1.Items[0].Text = "执行成功";
statusStrip1.Items[1].Text = recordChanged + "条记录受影响。";
}
catch (Exception ex)
{
//statusStrip1.Items.Clear();
statusStrip1.Items[0].Text = "连接失败";
statusStrip1.Items[1].Text = "";
}
finally
{
conn.Close();
}
}
//处理sql语句。尽量使每次的输入都不一样。
//^1^i 1表示位数 i表示数字 a表示字母 中表示中文
//目前先实现为数为1-9位数字的算法 2006-11-1
public string confusesqlString(string sql,int i)
{
int numLong = (int)numericNumLong.Value;
int iLong = Convert.ToString(i).Length;
string resultStr = Convert.ToString(i);
for (int x = 0; x < numLong - iLong; x++)
{
resultStr = "0" + resultStr;
}
return sql.Replace("^^i",resultStr);
}
private void buttonExecute_Click(object sender, EventArgs e)
{
this.Enabled = false;
FormHint frm = new FormHint();
frm.Show();
if (radioButton2.Checked)
connectBySql();
else if (radioButton1.Checked)
connectByAccess();
frm.Dispose();
this.Enabled = true;
this.Activate();
}
private void FormSqlTool_FormClosing(object sender, FormClosingEventArgs e)
{
iniWriteValue("Sql", "server", textBoxSqlServer.Text, m_filename);
iniWriteValue("Sql", "uid", textBoxUser.Text, m_filename);
iniWriteValue("Sql", "pwd", textBoxPsw.Text, m_filename);
iniWriteValue("Sql", "dbname", textBoxDbName.Text, m_filename);
iniWriteValue("sqlstring", "str", textBoxSqlStr.Text, m_filename);
}
private void buttonView_Click(object sender, EventArgs e)
{
FormDataView frm = new FormDataView();
frm.ShowDialog();
}
}
}