form1.cs

一个SQLServer数据库的字段内容批量替换程序。 在实际生产中使用过

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace SQL_Replace
{
	/// <summary>
	/// Form1 的摘要说明。
	/// </summary>
	public class Form1 : System.Windows.Forms.Form
	{
		private System.Windows.Forms.TextBox txtDBIp;
		private System.Windows.Forms.Label label1;
		private System.Windows.Forms.Label label2;
		private System.Windows.Forms.TextBox txtDBName;
		private System.Windows.Forms.Label label3;
		private System.Windows.Forms.TextBox txtDBUserName;
		private System.Windows.Forms.Label label4;
		private System.Windows.Forms.Button btnSure;
		private System.Windows.Forms.TextBox txtDBUserPasswd;
		private System.Windows.Forms.TextBox txtSText;
		private System.Windows.Forms.TextBox txtTText;
		private System.Windows.Forms.Label label5;
		private System.Windows.Forms.Label label6;

		private DataTable myDataTable;
		private System.Windows.Forms.Button button1;
		/// <summary>
		/// 必需的设计器变量。
		/// </summary>
		private System.ComponentModel.Container components = null;

		public Form1()
		{
			//
			// Windows 窗体设计器支持所必需的
			//
			InitializeComponent();

			//
			// TODO: 在 InitializeComponent 调用后添加任何构造函数代码
			//
		}

		/// <summary>
		/// 清理所有正在使用的资源。
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if (components != null) 
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows 窗体设计器生成的代码
		/// <summary>
		/// 设计器支持所需的方法 - 不要使用代码编辑器修改
		/// 此方法的内容。
		/// </summary>
		private void InitializeComponent()
		{
			this.btnSure = new System.Windows.Forms.Button();
			this.txtDBIp = new System.Windows.Forms.TextBox();
			this.label1 = new System.Windows.Forms.Label();
			this.label2 = new System.Windows.Forms.Label();
			this.txtDBName = new System.Windows.Forms.TextBox();
			this.label3 = new System.Windows.Forms.Label();
			this.txtDBUserName = new System.Windows.Forms.TextBox();
			this.label4 = new System.Windows.Forms.Label();
			this.txtDBUserPasswd = new System.Windows.Forms.TextBox();
			this.txtSText = new System.Windows.Forms.TextBox();
			this.txtTText = new System.Windows.Forms.TextBox();
			this.label5 = new System.Windows.Forms.Label();
			this.label6 = new System.Windows.Forms.Label();
			this.button1 = new System.Windows.Forms.Button();
			this.SuspendLayout();
			// 
			// btnSure
			// 
			this.btnSure.Location = new System.Drawing.Point(304, 360);
			this.btnSure.Name = "btnSure";
			this.btnSure.TabIndex = 0;
			this.btnSure.Text = "确  定";
			this.btnSure.Click += new System.EventHandler(this.btnSure_Click);
			// 
			// txtDBIp
			// 
			this.txtDBIp.Location = new System.Drawing.Point(96, 8);
			this.txtDBIp.Name = "txtDBIp";
			this.txtDBIp.Size = new System.Drawing.Size(168, 21);
			this.txtDBIp.TabIndex = 1;
			this.txtDBIp.Text = "";
			// 
			// label1
			// 
			this.label1.Location = new System.Drawing.Point(16, 16);
			this.label1.Name = "label1";
			this.label1.Size = new System.Drawing.Size(80, 16);
			this.label1.TabIndex = 2;
			this.label1.Text = "数据库地址：";
			// 
			// label2
			// 
			this.label2.Location = new System.Drawing.Point(16, 48);
			this.label2.Name = "label2";
			this.label2.Size = new System.Drawing.Size(72, 16);
			this.label2.TabIndex = 3;
			this.label2.Text = "数据库名：";
			// 
			// txtDBName
			// 
			this.txtDBName.Location = new System.Drawing.Point(96, 40);
			this.txtDBName.Name = "txtDBName";
			this.txtDBName.Size = new System.Drawing.Size(168, 21);
			this.txtDBName.TabIndex = 4;
			this.txtDBName.Text = "";
			// 
			// label3
			// 
			this.label3.Location = new System.Drawing.Point(16, 80);
			this.label3.Name = "label3";
			this.label3.Size = new System.Drawing.Size(72, 16);
			this.label3.TabIndex = 5;
			this.label3.Text = "用户名：";
			// 
			// txtDBUserName
			// 
			this.txtDBUserName.Location = new System.Drawing.Point(96, 72);
			this.txtDBUserName.Name = "txtDBUserName";
			this.txtDBUserName.Size = new System.Drawing.Size(168, 21);
			this.txtDBUserName.TabIndex = 6;
			this.txtDBUserName.Text = "";
			// 
			// label4
			// 
			this.label4.Location = new System.Drawing.Point(16, 112);
			this.label4.Name = "label4";
			this.label4.Size = new System.Drawing.Size(72, 16);
			this.label4.TabIndex = 7;
			this.label4.Text = "密  码：";
			// 
			// txtDBUserPasswd
			// 
			this.txtDBUserPasswd.Location = new System.Drawing.Point(96, 104);
			this.txtDBUserPasswd.Name = "txtDBUserPasswd";
			this.txtDBUserPasswd.PasswordChar = '*';
			this.txtDBUserPasswd.Size = new System.Drawing.Size(168, 21);
			this.txtDBUserPasswd.TabIndex = 8;
			this.txtDBUserPasswd.Text = "";
			// 
			// txtSText
			// 
			this.txtSText.Location = new System.Drawing.Point(96, 136);
			this.txtSText.Multiline = true;
			this.txtSText.Name = "txtSText";
			this.txtSText.Size = new System.Drawing.Size(432, 88);
			this.txtSText.TabIndex = 9;
			this.txtSText.Text = "";
			// 
			// txtTText
			// 
			this.txtTText.Location = new System.Drawing.Point(96, 240);
			this.txtTText.Multiline = true;
			this.txtTText.Name = "txtTText";
			this.txtTText.Size = new System.Drawing.Size(432, 88);
			this.txtTText.TabIndex = 10;
			this.txtTText.Text = "";
			// 
			// label5
			// 
			this.label5.Location = new System.Drawing.Point(16, 240);
			this.label5.Name = "label5";
			this.label5.Size = new System.Drawing.Size(80, 16);
			this.label5.TabIndex = 11;
			this.label5.Text = "目标字符串：";
			// 
			// label6
			// 
			this.label6.Location = new System.Drawing.Point(16, 144);
			this.label6.Name = "label6";
			this.label6.Size = new System.Drawing.Size(72, 16);
			this.label6.TabIndex = 12;
			this.label6.Text = "源字符串：";
			// 
			// button1
			// 
			this.button1.Location = new System.Drawing.Point(136, 360);
			this.button1.Name = "button1";
			this.button1.TabIndex = 13;
			this.button1.Text = "查  看";
			this.button1.Click += new System.EventHandler(this.button1_Click);
			// 
			// Form1
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
			this.ClientSize = new System.Drawing.Size(544, 421);
			this.Controls.Add(this.button1);
			this.Controls.Add(this.label6);
			this.Controls.Add(this.label5);
			this.Controls.Add(this.txtTText);
			this.Controls.Add(this.txtSText);
			this.Controls.Add(this.txtDBUserPasswd);
			this.Controls.Add(this.label4);
			this.Controls.Add(this.txtDBUserName);
			this.Controls.Add(this.label3);
			this.Controls.Add(this.txtDBName);
			this.Controls.Add(this.label2);
			this.Controls.Add(this.label1);
			this.Controls.Add(this.txtDBIp);
			this.Controls.Add(this.btnSure);
			this.Name = "Form1";
			this.Text = "Form1";
			this.Load += new System.EventHandler(this.Form1_Load);
			this.ResumeLayout(false);

		}
		#endregion

		/// <summary>
		/// 应用程序的主入口点。
		/// </summary>
		[STAThread]
		static void Main() 
		{
			Application.Run(new Form1());
		}

		private void btnSure_Click(object sender, System.EventArgs e)
		{
			string strConnParm;
			string strSQL;
			int nNum = 0;

			DialogResult drReplace = MessageBox.Show(this, "将更新数据库，请确认是否替换！");
			switch(drReplace)
			{
				case DialogResult.Yes:
					break;
				case DialogResult.No:
					return;
					break;
				default:
					break;
			}

			strConnParm = "data source=" + this.txtDBIp.Text.Trim() + 
                ";initial catalog=" + this.txtDBName.Text.Trim() + 
				";user id=" + this.txtDBUserName.Text.Trim() + 
                ";Password=" + this.txtDBUserPasswd.Text.Trim() + 
				";persist security info=False;packet size=4096;Connect Timeout=15";

			strSQL = "select dataid, databody, dataname from datasetting where databody like '%" + 
                this.txtSText.Text.Trim() + "%'";

			try
			{
				SqlConnection cn = new SqlConnection(strConnParm);
				cn.Open();

				SqlDataAdapter catDA = new SqlDataAdapter(strSQL, cn);       

				catDA.UpdateCommand = new SqlCommand("UPDATE datasetting SET databody = @databody " +
					"WHERE dataid = @dataid", cn);

				SqlParameter workParm1 = catDA.UpdateCommand.Parameters.Add("@databody", SqlDbType.Text);
				workParm1.SourceColumn = "databody";

				SqlParameter workParm2 = catDA.UpdateCommand.Parameters.Add("@dataid", SqlDbType.UniqueIdentifier);
				workParm2.SourceColumn = "dataid";
				//workParm2.SourceVersion = DataRowVersion.Original;

				DataSet catDS = new DataSet();
				catDA.Fill(catDS, "datasetting");   

				myDataTable = catDS.Tables["datasetting"];
				for(int nI = 0; nI < myDataTable.Rows.Count; nI ++)
				{
					string strdataBody = myDataTable.Rows[nI]["databody"].ToString();
					if (strdataBody.IndexOf(this.txtSText.Text.Trim()) != -1)
					{
						strdataBody = ReplaceFuncBody(strdataBody, txtSText.Text.Trim(), txtTText.Text.Trim());
						
						myDataTable.Rows[nI]["databody"] = strdataBody;
						nNum ++;
					}
				}

				catDA.Update(catDS, "datasetting");

				cn.Close();

				DialogResult drRet = MessageBox.Show(this, "替换成功！总共替换" + nNum.ToString() + "个函数。是否保存替换记录！", "是否保存", MessageBoxButtons.YesNo);
				switch(drRet)
				{
					case DialogResult.Yes:
						SaveImportRecord();
						break;
					case DialogResult.No:
						break;
					default:
						break;
				}
			}
			catch(Exception ex)
			{
				throw ex;
			}
		}

		private void Form1_Load(object sender, System.EventArgs e)
		{
			this.txtDBIp.Text = "127.0.0.1";
			this.txtDBName.Text = "XXXX";
			this.txtDBUserName.Text = "sa";
			this.txtDBUserPasswd.Text = "";
			this.txtSText.Text = "";
			this.txtTText.Text = "";
		}

		private string ReplaceFuncBody(string strdataBody, string strSText, string strTText)
		{
			string strRetdataBody = "";

			strRetdataBody = strdataBody.Replace(strSText, strTText);
			/*
		{
			// 将___strtrim(aaa);替换成aaa=strtrim(aaa);
			int nIndex = 0;
			string strMatch = "   strtrim(";

			while(strdataBody.IndexOf(strMatch, nIndex) != -1)
			{
				int nStation = strdataBody.IndexOf(strMatch, nIndex);
				int nStation2 = strdataBody.IndexOf(')', nStation);

				string strParm = strdataBody.Substring(nStation + strMatch.Length, nStation2 - nStation - strMatch.Length);

				strdataBody = strdataBody.Insert(nStation+3, strParm + "=");

				nIndex = nStation2 + strParm.Length + 1;
			}
		}
		*/

			return strRetdataBody;
		}

		private void SaveImportRecord()
		{
			Stream myStream ;
			SaveFileDialog saveFileDialog1 = new SaveFileDialog();
 
			saveFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*"  ;
			saveFileDialog1.FilterIndex = 1;
			saveFileDialog1.RestoreDirectory = true ;
 
			if(saveFileDialog1.ShowDialog() == DialogResult.OK)
			{
				if((myStream = saveFileDialog1.OpenFile()) != null)
				{
					StreamWriter swFileStream = new StreamWriter(myStream);

					swFileStream.WriteLine("修改日期：" + DateTime.Now.ToString());
					swFileStream.WriteLine("源字符串：");
					swFileStream.WriteLine(this.txtSText.Text.Trim());
					swFileStream.WriteLine("目标字符串：");
					swFileStream.WriteLine(this.txtTText.Text.Trim());
					swFileStream.WriteLine("");
					swFileStream.WriteLine("");
					swFileStream.WriteLine("修改的函数名称：");

					// 记录数据
					for(int nI = 0; nI < myDataTable.Rows.Count; nI ++)
					{
						swFileStream.WriteLine(myDataTable.Rows[nI]["dataname"].ToString());
					}

					swFileStream.Close();
					myStream.Close();
				}
			}

			MessageBox.Show("成功保存修改日志！");
		}

		private void button1_Click(object sender, System.EventArgs e)
		{
			Form2 formResult = new Form2(txtSText.Text.Trim(), txtDBIp.Text.Trim(), txtDBName.Text.Trim(), 
				txtDBUserName.Text.Trim(), txtDBUserPasswd.Text.Trim());
			formResult.Show();
		}
	}
}