Replacing on the server side

First let’s look at how would you replace a part of a string from the server side. The SQL command simply uses the Replace function in the UPDATE.

update test
set summary = Replace(summary, ’constructing’, ’making’)

The above UPDATE command replaces the word ‘constructing’ with ‘making’ in the summary column. In the above example, the column ‘summary’ is a varchar. The Replace wouldn’t work if the column is of data type ‘text’.

update test
set article = Replace(article, ’constructing’, ’making’)

Msg 8116, Level 16, State 1, Line 1
Argument data type text is invalid for argument 1 of replace function.

As you can see from the complaint above, the replace function couldn’t handle the text datatype.

Replacing from the client side

The text columns can be handled by using stored procedures (perhaps using updatetext). However, this article looks at doing the replacing from the client side. The advantages of this approach would be that you can use the regular expressions to search and replace in your text. Since it’s on the client side, you can build a gui or perhaps even do additional checks.

However, if you have tons of records (thousands or millions of records) that you need to search and replace, the server side would be faster. Most likely you would not do this often – so, replacing from the client side might not be that bad after all.

Change Text UI

Figure 1. A simple UI for Find/Replace

This example has a very simple Forms based UI. Find a piece of string and replace it with something else. The text controls txtFind and txtReplace are used in the code below. This UI can be generalized to include db, table, etc. But to simplify, all that has been placed in the code shown below.

Find/Replace code

The code behind Replace button is shown in Listing 1.

Code Listing 1. Search and replace from client side

/// <summary>
/// Replace the matching string inside the text column
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnReplace_Click(object sender, EventArgs e)
{
    // The Connection String
    string connStr = @"Data Source=(local);Initial Catalog=testdb;Integrated Security=True;User ID=dbo";

    // Use and close the connection
    using (SqlConnection sqlConn = new SqlConnection(connStr))
    {
        // -------------------------------------------------
        // Set up the sql data adapter and fill the data set
        // -------------------------------------------------

        // The select command must contain primary key or unique key
        // Otherwise, the update below fails. Here ID is the primary key.
        SqlCommand selectCommand = new SqlCommand("SELECT ID, TextCol FROM testtable", sqlConn);
        SqlDataAdapter sqlAdapter = new SqlDataAdapter(selectCommand);

        // This generates the necessary SQL commands (in this case, UpdateCommand, among others)
        SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(sqlAdapter);

        DataSet dataSet = new DataSet();
        sqlAdapter.Fill(dataSet);

        // --------------------------------------------
        // Go through the rows and make the replacement
        // --------------------------------------------
        foreach (DataRow dataRow in dataSet.Tables[0].Rows)
        {
            // Replace parts of the string using regex
            dataRow["TextCol"] = Regex.Replace(
                dataRow["TextCol"].ToString(), txtFind.Text, txtReplace.Text);
        }

        // --------------
        // Update changes
        // --------------
        sqlAdapter.Update(dataSet);
    }
}

So, the approach here is to set up a Data Set. You fill this data set by using SQL Data Adapter. In order to get the rows down, you would use a Select Command.

At the time of update (the last line of code), the SQL Data Adapter would need to know what update command would it need to use at the time of writing the changes to the database. For this purpose, you will use the SqlCommandBuilder, which will make up the update command (and others) based on the select command you used. For this to work though, the select command must have a primary key or a unique key.

The search/replace is done by using the Regex.Replace. In this case, the find and replace strings are obtained from the text boxes in the UI. Don’t forget to add the following namespaces for the above sql client classes and regular expression classes to work.

using System.Data.SqlClient;
using System.Text.RegularExpressions;