Frequently you will use the StringBuilder in preparing SQL statements (like SELECT, UPDATE, DELETE, etc.). Typically, you will be getting the values to store/update in the database from the user interface elements like text boxes, dropdowns, etc.
In the example below, cmdStr is a StringBuilder and the products table is being updated by using values from various textboxes and dropdown lists and current time, etc.
StringBuilder, the Append Way
One way is to use Append. You will use a whole bunch of Append methods to make up the whole UPDATE command that will be executed later in the code. This is typically pretty verbose (some may find it more readable, however):
cmdStr.Append("UPDATE Products SET ");
cmdStr.Append("Name=’" + artName + "’, ")
.Append("Summary=’" + artSummary + "’, ");
cmdStr.Append("Description=’" + artText + "’, ")
.Append("CategoryID=’" + ddlCategory.Text + "’, ")
.Append("SubcategoryID=’" + ddlSubCategory.Text + "’, ");
cmdStr.Append("Status=’" + ddlStatus.Text + "’, ")
.Append("Created=’" + DateTime.Now.ToString() + "’ ");
cmdStr.Append(" WHERE ID=’" + Request.QueryString["ID"] + "’");
StringBuilder, the AppendFormat Way
In this example below, the same query is made by using AppendFormat. In the following example, just one AppendFormat makes up the detailed part of the UPDATE query. Below both Append and AppendFormat could be merged into one single AppendFormat.
This is much more readable from the query point of view, though immediately finding out which number corresponds to which UI element becomes a little less readable.
cmdStr.Append("UPDATE Products SET ");
cmdStr.AppendFormat("Name=’{0}’, Description=’{1}’, Summary=’{2}’, Article=’{3}’, CategoryID=’{4}’, SubcategoryID=’{5}’, Status=’{6}’, Created=’{7}’ WHERE ID=’{8}’", artName, txtDescription.Text, artSummary, artText, ddlCategory.Text, ddlSubCategory.Text, ddlStatus.Text, DateTime.Now.ToString(), Request.QueryString["ID"]);
Same example for the INSERT statement:
cmdStr.Append("INSERT INTO Products (ID, Name, Description, Summary, Article, CategoryID, SubcategoryID, Status, Created)");
cmdStr.AppendFormat(" VALUES (’{0}’, ’{1}’, ’{2}’, ’{3}’, ’{4}’, ’{5}’, ’{6}’, ’{7}’, ’{8}’)",
articleID, artName, txtDescription.Text, artSummary, artText, ddlCategory.Text, ddlSubCategory.Text, ddlStatus.Text, DateTime.Now.ToString());