Archive for September, 2007
09-28-2007
SQL Search and Replace on Text Type Columns
Recently I was asked by a client to perform a global search and replace in one of their products databases to reflect a change in the branding of the company. Let’s say the company’s name was “Hello World” and they were rebranding themselves simply “World”. It seems like it should be a simple proposition. Using SQL Server’s handy replace function surely this should be a doddle?
Wrong - at least where text type data columns are involved. The replace function is perfect for most data types such as char, varchar, nchar and nvarchar, but it does not work for text type columns.
I did some research online and I managed to find some SQL code that claimed to do what I needed to do. However, for various reasons none of the code snippets I found would work with text type columns. I did, however, find an excellent stored procedure which will do a global search and replace on your whole database. It doesn’t work for text fields either, but it’s perfect for checking all of the other column types in your database (char, varchar, nchar and nvarchar types). It uses the database’s own schema tables to loop over all tables and all columns and then uses the replace function to search and replace. It can be found here:
http://vyaskn.tripod.com/sql_server_search_and_replace.htm
Anyways, under time pressure I eventually buckled, abandoned the pure SQL route and resolved to write an application in .NET to quickly read out the matching text fields, replace the strings as required, and re-write the updated text back to the database. As there was more than one text field in the database that needed to be updated, I required an application that I could easily configure. I wanted the ability to specify which table and which column to examine, along with the string to search for and the string to replace it with. Also, it needed to run on a staging server first and then on the live server later, so the database connection string also needed to be configurable. So I needed to use the app.config file to specify each of these values quickly and easily for the application.
The code that I used is available for download at the end of this article. Hopefully it will come in useful and save some folk some time – I would have liked to have had it last week! Here’s how to use it:
Open Visual Studio 2005
File -> New -> Project…
Choose Visual C#, Windows, Windows Application
Give your project a name and click OK.
Right click on your project in the Solution Explorer and choose Add New Item…
Choose Application Configuration File and click Add.
Now copy the code from this page into the correct files (overwriting all existing code there). Edit the App.Config file with the correct details for your situation.Enter your SQL connection string, the table and column names, the string to search for and the string to replace it with. If you don’t do this you’ll get a nasty crash when you try to run the application.
With any luck you’re up and running!
When the code runs it allows you to view each entry as you go, to test that it’s working okay one update at a time. The first thing it does is load up the data from your database table – if it’s a large table then you won’t see any progress for a few seconds while the data loads. When the data finally loads up, you will be presented with a simple user interface. Here you can review each update before committing it to the database. When you are happy that it’s working okay, you can then choose to search and replace all of the matching fields in one go (handy when you have hundreds of thousands of fields to update).
The code has been tested in both SQL Server 2000 and SQL Server 2005, and is written in .NET2. It uses inline SQL query strings for simplicity (and also because I had problems with data being truncated when using a TableAdapter and I was in a rush!) It should help you to get your task done quickly and get you moving on to the next issue, however, it has not been exhaustively tested and you are using it at your own risk. I recommend backing up the database before you begin.
Hope that helps,
David Conlisk
update: I noticed that when you copy and paste the code from this blog entry you end up with errors in your code because of strange characters, etc. I’ll fix this when I get a chance. Meanwhile, if you’re stuck and fancy giving the code a shot then email me.
Form1.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
namespace FindAndReplace
{
/// <summary>
/// Author: David Conlisk
/// Date: 28.09.07
/// (c) Equator Limited 2007
/// </summary>
public partial class Form1 : Form
{
private int _index = 0;
private int _currentID = 0;
private string _currentItem = “”;
private string _oldText = “”;
private string _newText = “”;
private string _tableName = “”;
private string _columnName = “”;
private string _primaryKeyName = “”;
private DataTable _dt;
private bool _reviewProgress;
public string strPrimaryKeyName
{
get
{
return _primaryKeyName;
}
set
{
_primaryKeyName = value;
}
}
public string strTableName
{
get
{
return _tableName;
}
set
{
_tableName = value;
}
}
public string strColumnName
{
get
{
return _columnName;
}
set
{
_columnName = value;
}
}
public int intCurrentItem
{
get
{
return _currentID;
}
set
{
_currentID = value;
}
}
public string strCurrentItem
{
get
{
return _currentItem;
}
set
{
_currentItem = value;
}
}
public bool reviewProcess
{
get
{
return _reviewProgress;
}
set
{
_reviewProgress = value;
}
}
public DataTable dt
{
get
{
return _dt;
}
set
{
_dt = value;
}
}
public int index
{
get
{
return _index;
}
set
{
_index = value;
}
}
public string oldText
{
get
{
return _oldText;
}
set
{
_oldText = value;
}
}
public string newText
{
get
{
return _newText;
}
set
{
_newText = value;
}
}
/// <summary>
/// Initialise the application: read the variables from the config file and go and get
/// the matching data from the database. May cause a delay in startup for larger database tables
/// </summary>
public Form1()
{
InitializeComponent();
// Read the variables from the config file
this.oldText = ConfigurationManager.AppSettings["oldText"];
this.newText = ConfigurationManager.AppSettings["newText"];
this.strTableName = ConfigurationManager.AppSettings["tablename"];
this.strColumnName = ConfigurationManager.AppSettings["columnName"];
this.strPrimaryKeyName = ConfigurationManager.AppSettings["primaryKeyName"];
this.reviewProcess = true;
this.dt = new DataTable();
this.dt.Columns.Add(this.strPrimaryKeyName);
this.dt.Columns.Add(this.strColumnName);
// Read the text from the db into a datatable
SqlConnection sqlConnection1 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["FindAndReplace.Properties.Settings.ConnectionString"].ConnectionString.ToString());
SqlCommand cmd = new SqlCommand();
SqlDataReader dr;
DataRow row;
cmd.CommandText = “SELECT ” + this.strPrimaryKeyName + “, ” + this.strColumnName + ” FROM ” + this.strTableName + ” WHERE ” + this.strColumnName + ” LIKE ‘%” + this.oldText + “%’”;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
// Fill datatable from datareader
dr = cmd.ExecuteReader();
while (dr.Read()) {
row = dt.NewRow();
row[0] = dr.GetValue(0).ToString();
row[1] = dr.GetValue(1).ToString();
dt.Rows.Add(row);
}
if (dt.Rows.Count > 0)
{
label4.Text = dt.Rows.Count.ToString();
}
else
{
DisableAll();
MessageBox.Show(”No matching records found!”, “No matches found”, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
sqlConnection1.Close();
}
/// <summary>
/// Load the next data item into the window when the user clicks the button
/// </summary>
/// <param name=”sender”></param>
/// <param name=”e”></param>
private void button1_Click(object sender, EventArgs e)
{
LoadNextItem();
}
/// <summary>
/// Replace the strings in the window for review
/// </summary>
/// <param name=”sender”></param>
/// <param name=”e”></param>
private void button2_Click(object sender, EventArgs e)
{
textBox1.Text = textBox1.Text.Replace(oldText, newText);
}
/// <summary>
/// Load the next item into the window that has content and contains the old word to replace
/// </summary>
private void LoadNextItem()
{
this.strCurrentItem = dt.Rows[index][this.strColumnName].ToString();
this.intCurrentItem = int.Parse(dt.Rows[index][this.strPrimaryKeyName].ToString());
this.index += 1;
// loop through the table until we find the next item that has content
// that contains the old text to replace, or we reach the end of the table
while ((this.index < this.dt.Rows.Count) && ((this.strCurrentItem.Length == 0) || (!this.strCurrentItem.Contains(this.oldText))))
{
this.strCurrentItem = dt.Rows[index][this.strColumnName].ToString();
this.intCurrentItem = int.Parse(dt.Rows[index][this.strPrimaryKeyName].ToString());
this.index += 1;
}
if (this.index == this.dt.Rows.Count)
{
DisableAll();
MessageBox.Show(”Reached End of Data Table”, “Finished”, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
// output the text to the window
textBox1.Text = this.strCurrentItem;
txtID.Text = this.intCurrentItem.ToString();
}
}
/// <summary>
/// Update the current item in the database
/// </summary>
/// <param name=”sender”></param>
/// <param name=”e”></param>
private void button3_Click_1(object sender, EventArgs e)
{
UpdateItemInDB(true);
}
/// <summary>
/// Update the current item in the DB
/// Executes a SQL string to avoid data truncation issues using the TableAdapter
/// </summary>
private void UpdateItemInDB(bool userReview)
{
string strNewText = this.strCurrentItem.Replace(oldText, newText);
//int intResult = this.textData.UpdateText(strNewText, this.intCurrentItem, this.intCurrentItem);
SqlConnection sqlConnection1 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["FindAndReplace.Properties.Settings.ConnectionString"].ConnectionString.ToString());
SqlCommand cmd = new SqlCommand();
cmd.CommandText = “UPDATE ” + this.strTableName + ” SET ” + this.strColumnName + ” = ‘” + @strNewText.Replace(”‘”, “””) + “‘ WHERE ” + this.strPrimaryKeyName + ” = ” + this.intCurrentItem.ToString();
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
int rowsAffected = cmd.ExecuteNonQuery();
sqlConnection1.Close();
if (userReview)
{
if (rowsAffected == 1)
{
MessageBox.Show(”Your operation was successful”, “Success”, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show(”There was an error! The return code from the operation was ” + rowsAffected.ToString(), “An Error Occurred”, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void button4_Click(object sender, EventArgs e)
{
UpdateAllItems();
}
private void UpdateAllItems()
{
while (this.index < this.dt.Rows.Count)
{
//1. Load next item
LoadNextItem();
//2. Update item in DB
UpdateItemInDB(false);
}
}
/// <summary>
/// Disable all buttons and set textbox to empty
/// </summary>
private void DisableAll()
{
button1.Enabled = false;
button2.Enabled = false;
button3.Enabled = false;
button4.Enabled = false;
textBox1.Text = “”;
}
}
}
Form1.Designer.cs
namespace FindAndReplace
{
partial class Form1
{
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.IContainer components = null;/// <summary>
/// Clean up any resources being used.
/// </summary>
/// <param name=”disposing”>true if managed resources should be disposed; otherwise, false.</param>
protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.textBox1 = new System.Windows.Forms.TextBox();
this.button1 = new System.Windows.Forms.Button();
this.button2 = new System.Windows.Forms.Button();
this.button3 = new System.Windows.Forms.Button();
this.label1 = new System.Windows.Forms.Label();
this.button4 = new System.Windows.Forms.Button();
this.txtID = new System.Windows.Forms.TextBox();
this.label3 = new System.Windows.Forms.Label();
this.label4 = new System.Windows.Forms.Label();
this.SuspendLayout();
//
// textBox1
//
this.textBox1.Location = new System.Drawing.Point(29, 40);
this.textBox1.Multiline = true;
this.textBox1.Name = “textBox1″;
this.textBox1.ScrollBars = System.Windows.Forms.ScrollBars.Vertical;
this.textBox1.Size = new System.Drawing.Size(850, 490);
this.textBox1.TabIndex = 0;
//
// button1
//
this.button1.Location = new System.Drawing.Point(32, 562);
this.button1.Name = “button1″;
this.button1.Size = new System.Drawing.Size(125, 23);
this.button1.TabIndex = 1;
this.button1.Text = “Load Next Item”;
this.button1.UseVisualStyleBackColor = true;
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// button2
//
this.button2.Location = new System.Drawing.Point(32, 591);
this.button2.Name = “button2″;
this.button2.Size = new System.Drawing.Size(125, 23);
this.button2.TabIndex = 2;
this.button2.Text = “Replace For Review”;
this.button2.UseVisualStyleBackColor = true;
this.button2.Click += new System.EventHandler(this.button2_Click);
//
// button3
//
this.button3.Location = new System.Drawing.Point(32, 624);
this.button3.Name = “button3″;
this.button3.Size = new System.Drawing.Size(125, 23);
this.button3.TabIndex = 3;
this.button3.Text = “Update Item in DB”;
this.button3.UseVisualStyleBackColor = true;
this.button3.Click += new System.EventHandler(this.button3_Click_1);
//
// label1
//
this.label1.AutoSize = true;
this.label1.Location = new System.Drawing.Point(36, 539);
this.label1.Name = “label1″;
this.label1.Size = new System.Drawing.Size(32, 13);
this.label1.TabIndex = 5;
this.label1.Text = “rowid”;
//
// button4
//
this.button4.Location = new System.Drawing.Point(727, 562);
this.button4.Name = “button4″;
this.button4.Size = new System.Drawing.Size(152, 23);
this.button4.TabIndex = 6;
this.button4.Text = “Update All Remaining Items”;
this.button4.UseVisualStyleBackColor = true;
this.button4.Click += new System.EventHandler(this.button4_Click);
//
// txtID
//
this.txtID.Enabled = false;
this.txtID.Location = new System.Drawing.Point(71, 536);
this.txtID.Name = “txtID”;
this.txtID.Size = new System.Drawing.Size(86, 20);
this.txtID.TabIndex = 4;
//
// label3
//
this.label3.AutoSize = true;
this.label3.Location = new System.Drawing.Point(82, 19);
this.label3.Name = “label3″;
this.label3.Size = new System.Drawing.Size(147, 13);
this.label3.TabIndex = 8;
this.label3.Text = “Matching Record(s) Returned”;
//
// label4
//
this.label4.AutoSize = true;
this.label4.Location = new System.Drawing.Point(41, 19);
this.label4.Name = “label4″;
this.label4.Size = new System.Drawing.Size(29, 13);
this.label4.TabIndex = 9;
this.label4.Text = “Zero”;
//
// Form1
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(911, 661);
this.Controls.Add(this.label4);
this.Controls.Add(this.label3);
this.Controls.Add(this.button4);
this.Controls.Add(this.label1);
this.Controls.Add(this.txtID);
this.Controls.Add(this.button3);
this.Controls.Add(this.button2);
this.Controls.Add(this.button1);
this.Controls.Add(this.textBox1);
this.Name = “Form1″;
this.Text = “Form1″;
this.ResumeLayout(false);
this.PerformLayout();}
#endregion
private System.Windows.Forms.TextBox textBox1;
private System.Windows.Forms.Button button1;
private System.Windows.Forms.Button button2;
private System.Windows.Forms.Button button3;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Button button4;
private System.Windows.Forms.TextBox txtID;
private System.Windows.Forms.Label label3;
private System.Windows.Forms.Label label4;
}
}
App.Config
<?xml version=”1.0″ encoding=”utf-8″ ?>
<configuration>
<configSections>
</configSections>
<connectionStrings>
<add name=”FindAndReplace.Properties.Settings.ConnectionString”
connectionString=”Enter your connection string here”
providerName=”System.Data.SqlClient” />
</connectionStrings>
<appSettings><add key=”oldText” value=”Enter the text string to search for”/>
<add key=”newText” value=”Enter the text string to replace oldText with”/><add key=”tableName” value=”Enter the name of your table here”/>
<!– columnname - will be treated as a string, so use a column of type text, varchar, nvarchar, etc. –>
<add key=”columnName” value=”Enter the name of your column here”/><!– primary key must be a column of type integer –>
<add key=”primaryKeyName” value=”Enter the name of your integer primary key table here”/>
</appSettings>
</configuration>
Posted by dotnetdave in .NET 2.0 Development | 1 Comment »