formsqltool.cs

c#编写的一个数据库批量更新小工具。对测试人员来说比较有用。(可以一次性批量添加N条记录）

/*****************************************
 * 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();
        }

    }
}