StringBuilder.AppendFormat

saloj
saloj used Ask the Experts™
on
Hi EE,
how can I write the following code in StringBuilder.AppendFormat.
what is the advantage to use Stringbuilder.AppendFormat insted of string concatenation.

     SQL = "INSERT INTO tblLog"
        SQL += "(ACCOUNT,IP_ADDRESS,USER_AGENT,REFERER,LOGIN_DATE,LOGIN_RESULT_TYPE)"
        SQL += " values"
        SQL += "(N'" & LCase(Trim(UserAccount)) & "',"
        SQL += "'" & UserIp & "',"
        SQL += "'" & UserAgent & "',"
        SQL += "'" & UserReferer & "',"
        SQL += "getdate(),"
        SQL += "'" & result & "'"
        SQL += ")"
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
try this

dim SQL  As New StringBuilder()

SQL.append "INSERT INTO tblLog"
SQL.append "(ACCOUNT,IP_ADDRESS,USER_AGENT,REFERER,LOGIN_DATE,LOGIN_RESULT_TYPE)"
SQL.append " values"
SQL.append "(N'" & LCase(Trim(UserAccount)) & "',"
SQL.append  "'" & UserIp & "',"
SQL.append  "'" & UserAgent & "',"
SQL.append  "'" & UserReferer & "',"
SQL.append  "getdate(),"
SQL.append  "'" & result & "'"
SQL.append  ")"

Commented:
>>what is the advantage to use Stringbuilder.AppendFormat insted of string concatenation.
From BOL;

Performance Considerations
The Concat and AppendFormat methods both concatenate new data to an existing String or StringBuilder object. A String object concatenation operation always creates a new object from the existing string and the new data. A StringBuilder object maintains a buffer to accommodate the concatenation of new data. New data is appended to the end of the buffer if room is available; otherwise, a new, larger buffer is allocated, data from the original buffer is copied to the new buffer, then the new data is appended to the new buffer.

The performance of a concatenation operation for a String or StringBuilder object depends on how often a memory allocation occurs. A String concatenation operation always allocates memory, whereas a StringBuilder concatenation operation only allocates memory if the StringBuilder object buffer is too small to accommodate the new data. Consequently, the String class is preferable for a concatenation operation if a fixed number of String objects are concatenated. In that case, the individual concatenation operations might even be combined into a single operation by the compiler. A StringBuilder object is preferable for a concatenation operation if an arbitrary number of strings are concatenated; for example, if a loop concatenates a random number of strings of user input.
**************
When we are working with large amount of text data we can notice differences using stringbuilder class methods compared to string class.

Commented:
saloj,  
StringBuilder is faster for excessive string manipulation.
it uses parameters like tokens, which is easy to  understand.
http://msdn.microsoft.com/en-us/library/hdekwk0b.aspx

String concatenation is very confusing when ur using for subqueries or longer strings with paramteres.

thanks,
Top Expert 2009

Commented:
This is a lil more concise:

StringBuilder sb = new StringBuilder();

sb.Append("INSERT INTO tblLog (ACCOUNT,IP_ADDRESS,USER_AGENT,REFERER,LOGIN_DATE,LOGIN_RESULT_TYPE)");

sb.AppendFormat(" values (N'{0}', '{1}', '{2}', '{3}', getdate(), '{4}')"
                UserAccount.Trim().ToLower(), UserIp, UserAgent, UserReferer, result);

Sorry, could not convert to vb.net.

Arun
Top Expert 2009

Commented:
Here's is a post from this morning about this issue:

http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_26299354.html

Arun
Top Expert 2009

Commented:
I finally got the VB version:

Dim sb As New StringBuilder()
sb.Append("INSERT INTO tblLog (ACCOUNT,IP_ADDRESS,USER_AGENT,REFERER,LOGIN_DATE,LOGIN_RESULT_TYPE)")
sb.AppendFormat(" values (N'{0}', '{1}', '{2}', '{3}', getdate(), '{4}')", UserAccount.Trim().ToLower(), UserIp, UserAgent, UserReferer, result)

Arun

Author

Commented:
Hi Guys, Thanks
i am trying to do following but getting exception error. pls help

 SQLBD.Append("INSERT INTO tblLog(ACCOUNT,IP_ADDRESS,USER_AGENT,REFERER,LOGIN_DATE,LOGIN_RESULT_TYPE)")
        SQLBD.AppendFormat(" values")
        SQLBD.AppendFormat("(N'{0}',", LCase(EF.Text_To_Msdb(Trim(UserAccount))))
        SQLBD.AppendFormat("'{1}',", UserIp)
        SQLBD.AppendFormat("'{2}',", UserAgent)
        SQLBD.AppendFormat("'{3}',", UserReferer)
        SQLBD.AppendFormat("getdate(),")
        SQLBD.AppendFormat("'{4}')", result(0))

Thanks
Top Expert 2009
Commented:
SQLBD.Append("INSERT INTO tblLog(ACCOUNT,IP_ADDRESS,USER_AGENT,REFERER,LOGIN_DATE,LOGIN_RESULT_TYPE)")
         SQLBD.AppendFormat(" values")
         SQLBD.AppendFormat("(N'{0}',", LCase(EF.Text_To_Msdb(Trim(UserAccount))))
         SQLBD.AppendFormat("'{0}',", UserIp)
         SQLBD.AppendFormat("'{0}',", UserAgent)
         SQLBD.AppendFormat("'{0}',", UserReferer)
         SQLBD.Append("getdate(),")
         SQLBD.AppendFormat("'{0}')", result(0))

This should fix your issue.

Arun

Commented:
change it like this paramter index number starts from 0, if you combine all the lines to one appendformat then use {0},{1}...

try this

 SQLBD.Append("INSERT INTO tblLog(ACCOUNT,IP_ADDRESS,USER_AGENT,REFERER,LOGIN_DATE,LOGIN_RESULT_TYPE)")
        SQLBD.AppendFormat(" values")
        SQLBD.AppendFormat("(N'{0}',", LCase(EF.Text_To_Msdb(Trim(UserAccount))))
        SQLBD.AppendFormat("'{0}',", UserIp)
        SQLBD.AppendFormat("'{0}',", UserAgent)
        SQLBD.AppendFormat("'{0}',", UserReferer)
        SQLBD.AppendFormat("getdate(),")
        SQLBD.AppendFormat("'{0}')", result(0))

or this

SQLBD.Append("INSERT INTO tblLog(ACCOUNT,IP_ADDRESS,USER_AGENT,REFERER,LOGIN_DATE,LOGIN_RESULT_TYPE)")
        SQLBD.AppendFormat(" values")
        SQLBD.AppendFormat("(N'{0}','{1}','{2}','{3}', getDate(), '{4}')", LCase(EF.Text_To_Msdb(Trim(UserAccount))), UserIp, UserAgent, UserReferer, result(0))
Shahan AyyubSenior Software Engineer

Commented:
what exception did you get ??
See the value of 'SQLBD.ToString()' in watch window it should look like your normal query.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial