What is the purpose of the string builder here?

Question

I've only done C++ and some C# and I am new to MySQL and I don't understand the purpose of the string builder here.

This code is part of a program which is used to extract data from a webpage and insert it into an already existing Word File using bookmarks.

MY CODE

using System.Collections.Generic;
using System.Configuration;
using System.Text;
using MySql.Data.MySqlClient;

namespace ARExtractionLibrary
{
    public class DataExtractor
    {
        #region Attributes
        private string _databaseConfig = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        private const int AR_TRACKER_ID = 10;
        private const int AR_NUMBER_ID = 3;
        #endregion

        #region Methods
        public Dictionary<string, Field> GetFieldData(int TicketID)
        {
            MySqlConnection sqlConnection = new MySqlConnection(_databaseConfig);
            Dictionary<string, Field> fieldsDictionary = new Dictionary<string, Field>();

            StringBuilder queryDefaultFields = new StringBuilder();
            queryDefaultFields.Append("SELECT issues.id AS TicketID, issues.subject, issues.description, ");
            queryDefaultFields.Append(" CONCAT(users.firstname,' ', users.lastname) as Author");
            queryDefaultFields.Append(" FROM issues LEFT JOIN users ON users.id =issues.author_id");
            queryDefaultFields.Append("  WHERE issues.id = @TicketID");

            StringBuilder queryCustomFields = new StringBuilder();
            queryCustomFields.Append("SELECT custom_fields.name, custom_fields.field_format, CONCAT(users.firstname,' ',users.lastname) AS value");
            queryCustomFields.Append(" FROM custom_values");
            queryCustomFields.Append(" LEFT JOIN custom_fields ON custom_values.custom_field_id = custom_fields.id");
            queryCustomFields.Append(" JOIN users ON custom_values.value = users.id");
            queryCustomFields.Append(" WHERE custom_values.customized_id = @TicketId");
            queryCustomFields.Append(" AND field_format = 'user'");
            queryCustomFields.Append(" UNION");
            queryCustomFields.Append(" SELECT custom_fields.name, custom_fields.field_format, custom_values.value");
            queryCustomFields.Append(" FROM custom_values");
            queryCustomFields.Append(" LEFT JOIN custom_fields ON custom_values.custom_field_id = custom_fields.id");
            queryCustomFields.Append(" WHERE custom_values.customized_id = @TicketId");
            queryCustomFields.Append(" AND field_format <> 'user'");

            sqlConnection.Open();

            //First query
            MySqlCommand sqlCommand = null;
            sqlCommand = new MySqlCommand(queryDefaultFields.ToString(), sqlConnection);
            sqlCommand.Parameters.AddWithValue("@TicketId", TicketID);

            MySqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
            while (sqlDataReader.Read())
            {
                Field subject = new Field("Subject", sqlDataReader["subject"].ToString());
                fieldsDictionary.Add(subject.Name, subject);

                Field description = new Field("Description", sqlDataReader["description"].ToString());
                fieldsDictionary.Add(description.Name, description);

                Field ticketID = new Field("TicketID", sqlDataReader["TicketID"].ToString());
                fieldsDictionary.Add(ticketID.Name, ticketID);

                Field author = new Field("Author", sqlDataReader["Author"].ToString());
                fieldsDictionary.Add(author.Name, author);
            }
            sqlDataReader.Close();

            //Second query
            sqlCommand = new MySqlCommand(queryCustomFields.ToString(), sqlConnection);
            sqlCommand.Parameters.AddWithValue("@TicketId", TicketID);
            sqlDataReader = sqlCommand.ExecuteReader();

            while (sqlDataReader.Read())
            {
                string fieldName = sqlDataReader["name"].ToString();
                string fieldValue = sqlDataReader["value"].ToString();
                string fieldFormat = sqlDataReader["field_format"].ToString();

                if (fieldsDictionary.ContainsKey(fieldName))
                {
                    fieldsDictionary[fieldName].Values.Add(fieldValue);
                }

                else
                {
                    Field localField = new Field(fieldName, fieldValue, fieldFormat);
                    fieldsDictionary.Add(localField.Name, localField);
                }
            }
            sqlDataReader.Close();
            sqlConnection.Close();

            return fieldsDictionary;
        }

        public Dictionary<int, string> GetARs()
        {
            Dictionary<int, string> ARDictionary = new Dictionary<int, string>();
            MySqlConnection sqlConnection = new MySqlConnection(_databaseConfig);
            StringBuilder sqlCommandIsAR = new StringBuilder();
            sqlCommandIsAR.Append("SELECT issues.id AS TicketID, issues.tracker_id AS Tracker, issues.subject AS Subject, custom_values.custom_field_id, custom_values.value");
            sqlCommandIsAR.Append(" FROM issues LEFT JOIN Custom_values ON custom_values.customized_id = issues.id ");
            sqlCommandIsAR.Append(" WHERE tracker_id = @IsAR AND custom_field_id = @IsARNumber ");

            sqlConnection.Open();

            MySqlCommand commandGetARs = new MySqlCommand(sqlCommandIsAR.ToString(), sqlConnection);
            commandGetARs.Parameters.AddWithValue("@IsAR", AR_TRACKER_ID);
            commandGetARs.Parameters.AddWithValue("@IsARNumber", AR_NUMBER_ID);
            MySqlDataReader sqlDataReader = null;
            sqlDataReader = commandGetARs.ExecuteReader();

            while (sqlDataReader.Read())
            {
                string DictionaryValue = sqlDataReader["TicketID"].ToString() + " - " + sqlDataReader["Subject"].ToString();

                if (sqlDataReader["value"].ToString().Length != 0)
                {
                    DictionaryValue += " [" + sqlDataReader["value"].ToString() + "]";
                }

                int key = int.Parse(sqlDataReader["TicketID"].ToString());
                ARDictionary.Add(key, DictionaryValue);
            }

            sqlDataReader.Close();
            sqlConnection.Close();

            return ARDictionary;
        }

        #endregion
    }
}

*In this specific case* `StringBuilder` offers *nothing at all*; it just makes the code run slower. — Jon, Sep 20 '12 at 12:52
I get it now, but what MySQL does with all these strings?? This is what I don't get. — Conrad C, Sep 20 '12 at 12:55

score 6 · Answer 1 · edited May 23 '17 at 12:03

6

StringBuilder prevents having to recreate a string each time you are adding to it. Using the String class in C# means you are using an immutable object, but StringBuilder is much faster in most cases since it's not having to create a new String each time you append to it.

So in general, if you are appending to a String many times, StringBuilder will be more efficient than just doing str += "text to append" (where str is a String).

There are some caveats to be aware of here though, please refer to this answer.

edited May 23 '17 at 12:03

Community

1
1

answered Sep 20 '12 at 12:42

dcp

51,027
19
136
157

1

downvoter, care to explain why rather than just doing a hit and run? – dcp Sep 20 '12 at 14:51

Jon · Accepted Answer · 2012-09-20T12:51:19.327

In general

A StringBuilder allows you to build up a string without reallocating a buffer for the string's contents on every mutating operation (the same buffer is used throughout; it is only reallocated when the string expands beyond the current capacity). This makes the whole process of building up a big string much more performant.

In this code

In this case there is no dynamic building up of a string at all; the strings are practically hardcoded. Here StringBuilder is totally pointless; it would actually be better for performance if the strings were hardcoded literals like this:

var queryDefaultFields = @"SELECT issues.id AS TicketID, issues.subject, issues.description,
                           CONCAT(users.firstname,' ', users.lastname) as Author
                           FROM issues LEFT JOIN users ON users.id =issues.author_id
                           WHERE issues.id = @TicketID";

My guess is that the author of this code either misused StringBuilder because they remembered reading that "it's faster this way" but never really understood why, or else is guilty of negligent copy-pasting.

It's possibly that the author of code used `StringBuilder`'s `AppendFormat` method before as it is common practice for creating SQL queries (however not the perfect way, as this query is vulnerable to SQL injection). But after some time he hardcode the values and replace `AppendFormat` with `Append` method. — tsionyx, Sep 20 '12 at 12:54
@T_12: Possibly, but that's just another way of doing things wrong as you said. Doubly wrong, I might add. — Jon, Sep 20 '12 at 13:05

score 4 · Answer 3 · answered Sep 20 '12 at 12:42

4

To increase performance, as you are going to do a lot of addition of strings. It's well known that even being reference type string behaves like value type.

So on every addition of the string a new string object is allocated.

string s = "hello"; 
s += " world"; //NEW STRING OBJECT ALLOCATED

In case of the StringBuilder, instead, there is not any new allocation, but just appending to the end of it.

StringBuilder queryDefaultFields = new StringBuilder();
queryDefaultFields.Append("hello");   //THE SAME queryDefaultFields OBJECT
queryDefaultFields.Append(" world");  //THE SAME queryDefaultFields OBJECT

answered Sep 20 '12 at 12:42

Tigran

59,345
8
77
117

String does not behave like a value type. It behaves like a reference type, because it is one. It's immutable, but mutability has nothing at all to do with whether it's a value type or a reference type. Either type can be mutable or immutable. – Servy Sep 20 '12 at 13:57
@Servy: I hoped the concept: "behaves **like** value type" is a clear, in sence of immutability. – Tigran Sep 20 '12 at 14:12
But value types are not all immutable. Being immutable isn't even an attribute of value types in the first place. Beyond that, being a value type means that the variable actually holds the value, rather than the actual information being stored elsewhere. That's the one and only actual trait of a value type; the rest (of the actual attributes, of which immutability is not one) are essentially side effects. Value types have nothing at all to do with this. Oh, and you don't even discuss the provided code snippet or mention that this is an incorrect use of `StringBuilder` in your answer. – Servy Sep 20 '12 at 14:15
Reading the accepted answer would be a place to start. The fact that you have to ask means you either didn't read the code snippet or you don't actually know when you should or should not use a `StringBuilder`, meaning you have no place answering such a question. Here the string being built is entirely a compile time constant, not built by a number of concatenations unknown until runtime. – Servy Sep 20 '12 at 14:24
@Servy: the question asks about why would one use string builder in this case. It's obvious that code tends to concatenate strings. So my answer is: to speed up concatenation, I could go farther to suggest to not do that, but use single string constant, but lack of that additional suggession, doesn't make my asnwer invalid. – Tigran Sep 20 '12 at 14:27
The question doesn't ask why you should use `StringBuilder` in general (not that you answer such a question). It asks why you would use it here. The answer is that you shouldn't; it is used here because the person who coded it either didn't know that he was mis-using `StringBuilder`, or just didn't care. Using a stringbuilder here doesn't "speed up concatenation". It slows it down because it is doing a bunch of concatenation at runtime when it could just define the entire completed string at compile time and do no concatenation at all at runtime. – Servy Sep 20 '12 at 14:30

score 0 · Answer 4 · answered Sep 20 '12 at 12:43

0

Since StringBuilder is mutable (the contrary as string), it means you can simply add data to it., by using Append (or AppendLine) method, at any given time, as long as you have its reference.

answered Sep 20 '12 at 12:43

Mitja Bonca

3,928
5
22
29

What is the purpose of the string builder here?

4 Answers4

In general

In this code