Field Notes Inside an Integrated Communications Agency

Sometimes you come across a program that is repeatedly making database roundtrips to perform the same or similar actions repeatedly, such as adding a great number of objects to the database under one transaction. This is very tough on performance, because the db round-trip usually takes more time than the actual insert.

One great way to fix this is to turn your objects into XML and pass the XML to a stored procedure (use the "text" data type for it's unlimited size). In SQL Server 2000 and 2005, you can convert that XML into a table and perform all kinds of operations with it very efficiently.

In addition to inserts, this paradigm helps with searches and other types of queries which are often carried out by ad-hoc sql statements, which are less than ideal.
Here's a way to break up a bunch of search terms and check them against a table of tags or keywords, written in C# and SQL:

protected void buttonSearch_Click(object sender, EventArgs e)
    {
        //Using an ordinary string here instead of the stringbuilder...
        //...yes, I know, stringbuilders are better. Get over it.
        string strXML = "<keywords>";
        string strKeywords = txtSearch.Text;
        //commas and spaces as delimiters
        strKeywords = strKeywords.Replace(" ", ",");
        //make an array of the words they put in the search
        string[] arKeywords = strKeywords.Split(Convert.ToChar(","));
        //build your XML
        for (int i = 0; i < arKeywords.Length; i++)
        {
            if (arKeywords[i] != "")
            {
                strXML += "<keyword kword=\"";
                strXML += arKeywords[i];
                strXML += "\"></keyword>";
            }
        }
        strXML += "</keywords>";
        divSearchResults.InnerHtml = "";
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString1"].ConnectionString);
        try
        {
            conn.Open();
            SqlCommand comm = new SqlCommand("SearchKeywords", conn);// @Keywords);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@Keywords", strXML);
            SqlDataReader dr = comm.ExecuteReader();
            if (dr.HasRows)
            {
                divSearchResults.InnerHtml = "";
                while (dr.Read())
                {
                    divSearchResults.InnerHtml += "<p><a href=\"#Content" + dr["ContentID"] + "\">";
                    divSearchResults.InnerHtml += dr["ContentTitle"];
                    divSearchResults.InnerHtml += "</a></p>";
                }
            }
            else
            {
                divSearchResults.InnerHtml = "No results were returned.";
            }
        }
        catch (Exception exc)
        {
            string strError = exc.InnerException.ToString();
        }
        finally
        {
            conn.Close();
        }
    }

Here is the stored proc that the above code calls:

CREATE PROCEDURE dbo.SearchKeywords
 
 (
 @Keywords nvarchar(250)
 )
 
AS
Declare @iDoc int
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @Keywords
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT  distinct c.ContentID, c.ContentTitle, c.CreatedDate
from       OPENXML (@idoc, '/keywords/keyword',1)
            WITH (kword  nvarchar(100)) KeywordTable
JOIN Keyword kw ON kw.keyword like KeywordTable.kword + '%'
JOIN Content c ON c.ContentID = kw.ContentID
ORDER BY c.CreatedDate desc
EXEC sp_xml_removedocument @iDoc

Just play around with the code and stored procedure to get it to work for your situation.