Can it be done? Yes, it can. At first it seems that you cannot. There is no Clone method on SqlParameter. Or is there? Yes, there is. SqlParameter implements IClonable explicitly (look at this blog for more information on explicit and implicit interface implementation) and that is why you cannot see or use it directly. But casting the SqlParameter object to IClonable let’s you use the clone method.

Not only SqlParameter implements IClonable. All parameter classes implement it.

I want to talk with you about the performance difference between string concatenation and doing the same with a stringbuilder. I can tell you already that the bottomline is that using a stringbuilder to concatenate string is much faster.

Why? That is because strings are immutable which means that once strings are created they cannot be changed. But I can change a string you say. Oh right, that is right. What I mean is that a string buffer is not reused when a new value is assigned to a string variable. A new buffer is created to hold the new value. The old one is left behind to be recycled by the garbage collector. Not so very efficient memory use or is it? The creation of new buffers for new values for strings impacts the performance of an application if alot of strings are changed because the garbage collector has to free memory many more times than normal.

A stringbuilder does not have this problem because it works internally with flexible memory buffer that is extended dynamically. This is a fast and memory efficient model.

Look at this picture. It tells more than a 1.000 words.

String Test Result

So it is clear that when you want to concatenate alot of strings it is better to use a stringbuilder.

The test code I used is this:

<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;10</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #ffc9a0">static</span> <span style="font-weight: normal; color: #ffc9a0">void</span> <span style="font-weight: normal; color: #8ce6ff">Main</span>(<span style="font-weight: normal; color: #ffc9a0">string</span>[] <span style="font-weight: normal; color: #8ce6ff">args</span>)
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;11</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; {
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;12</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #ff80ff">Console</span><span style="font-weight: normal; color: #8080ff">.</span><span style="font-weight: normal; color: #8ce6ff">WriteLine</span>(<span style="font-weight: normal; color: #d896cd">"Start String vs. StringBuilder performance test."</span>);
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;13</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #ff80ff">Console</span><span style="font-weight: normal; color: #8080ff">.</span><span style="font-weight: normal; color: #8ce6ff">WriteLine</span>();
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;14</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #ff80ff">Console</span><span style="font-weight: normal; color: #8080ff">.</span><span style="font-weight: normal; color: #8ce6ff">WriteLine</span>(<span style="font-weight: normal; color: #d896cd">"Started string performance test."</span>);
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;15</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #ff80ff">Console</span><span style="font-weight: normal; color: #8080ff">.</span><span style="font-weight: normal; color: #8ce6ff">WriteLine</span>();
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;16</span>&#160;
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;17</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: lime">// Create a stopwatch to time the tests</span>
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;18</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #ff80ff">Stopwatch</span> <span style="font-weight: normal; color: #8ce6ff">stopwatch</span> <span style="font-weight: normal; color: #8080ff">=</span> <span style="font-weight: normal; color: #ffc9a0">new</span> <span style="font-weight: normal; color: #ff80ff">Stopwatch</span>();
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;19</span>&#160;
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;20</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: lime">// Test the performance of strings allocation</span>
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;21</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #8ce6ff">stopwatch</span><span style="font-weight: normal; color: #8080ff">.</span><span style="font-weight: normal; color: #8ce6ff">Start</span>();
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;22</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #ffc9a0">string</span> <span style="font-weight: normal; color: #8ce6ff">testString</span> <span style="font-weight: normal; color: #8080ff">=</span> <span style="font-weight: normal; color: #ffc9a0">string</span><span style="font-weight: normal; color: #8080ff">.</span><span style="font-weight: normal; color: #8ce6ff">Empty</span>;
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;23</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #ffc9a0">for</span> (<span style="font-weight: normal; color: #ffc9a0">int</span> <span style="font-weight: normal; color: #8ce6ff">i</span> <span style="font-weight: normal; color: #8080ff">=</span> <span style="font-weight: normal; color: #ff8080">0</span>; <span style="font-weight: normal; color: #8ce6ff">i</span> <span style="font-weight: normal; color: #8080ff">&lt;</span> <span style="font-weight: normal; color: #ff8080">100000</span>; <span style="font-weight: normal; color: #8ce6ff">i</span><span style="font-weight: normal; color: #8080ff">++</span>)
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;24</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; {
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;25</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #8ce6ff">testString</span> <span style="font-weight: normal; color: #8080ff">+=</span> <span style="font-weight: normal; color: #8ce6ff">i</span><span style="font-weight: normal; color: #8080ff">.</span><span style="font-weight: normal; color: #8ce6ff">ToString</span>();
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;26</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; }
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;27</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #8ce6ff">stopwatch</span><span style="font-weight: normal; color: #8080ff">.</span><span style="font-weight: normal; color: #8ce6ff">Stop</span>();
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;28</span>&#160;
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;29</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #ff80ff">Console</span><span style="font-weight: normal; color: #8080ff">.</span><span style="font-weight: normal; color: #8ce6ff">WriteLine</span>(<span style="font-weight: normal; color: #d896cd">"Ended string performance test."</span>);
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;30</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #ff80ff">Console</span><span style="font-weight: normal; color: #8080ff">.</span><span style="font-weight: normal; color: #8ce6ff">WriteLine</span>();
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;31</span>&#160;
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;32</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: lime">// Write the elapsed time</span>
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;33</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #ff80ff">Console</span><span style="font-weight: normal; color: #8080ff">.</span><span style="font-weight: normal; color: #8ce6ff">WriteLine</span>(<span style="font-weight: normal; color: #d896cd">"100,000 string allocations time elapsed : {0} ms"</span>, 
				    <span style="font-weight: normal; color: #8ce6ff">stopwatch</span><span style="font-weight: normal; color: #8080ff">.</span><span style="font-weight: normal; color: #8ce6ff">ElapsedMilliseconds</span>);
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;34</span>&#160;
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;35</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #8ce6ff">stopwatch</span><span style="font-weight: normal; color: #8080ff">.</span><span style="font-weight: normal; color: #8ce6ff">Reset</span>();
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;36</span>&#160;
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;37</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #ff80ff">Console</span><span style="font-weight: normal; color: #8080ff">.</span><span style="font-weight: normal; color: #8ce6ff">WriteLine</span>(<span style="font-weight: normal; color: #d896cd">"Started StringBuilder performance test."</span>);
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;38</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #ff80ff">Console</span><span style="font-weight: normal; color: #8080ff">.</span><span style="font-weight: normal; color: #8ce6ff">WriteLine</span>();
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;39</span>&#160;
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;40</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: lime">// Test the performance of stringbuilder allocation</span>
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;41</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #8ce6ff">stopwatch</span><span style="font-weight: normal; color: #8080ff">.</span><span style="font-weight: normal; color: #8ce6ff">Start</span>();
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;42</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #ff80ff">StringBuilder</span> <span style="font-weight: normal; color: #8ce6ff">sb</span> <span style="font-weight: normal; color: #8080ff">=</span> <span style="font-weight: normal; color: #ffc9a0">new</span> <span style="font-weight: normal; color: #ff80ff">StringBuilder</span>();
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;43</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #ffc9a0">for</span> (<span style="font-weight: normal; color: #ffc9a0">int</span> <span style="font-weight: normal; color: #8ce6ff">i</span> <span style="font-weight: normal; color: #8080ff">=</span> <span style="font-weight: normal; color: #ff8080">0</span>; <span style="font-weight: normal; color: #8ce6ff">i</span> <span style="font-weight: normal; color: #8080ff">&lt;</span> <span style="font-weight: normal; color: #ff8080">100000</span>; <span style="font-weight: normal; color: #8ce6ff">i</span><span style="font-weight: normal; color: #8080ff">++</span>)
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;44</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; {
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;45</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #8ce6ff">sb</span><span style="font-weight: normal; color: #8080ff">.</span><span style="font-weight: normal; color: #8ce6ff">Append</span>(<span style="font-weight: normal; color: #8ce6ff">i</span>);
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;46</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; }
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;47</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #8ce6ff">stopwatch</span><span style="font-weight: normal; color: #8080ff">.</span><span style="font-weight: normal; color: #8ce6ff">Stop</span>();
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;48</span>&#160;
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;49</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #ff80ff">Console</span><span style="font-weight: normal; color: #8080ff">.</span><span style="font-weight: normal; color: #8ce6ff">WriteLine</span>(<span style="font-weight: normal; color: #d896cd">"Ended StringBuilder performance test."</span>);
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;50</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #ff80ff">Console</span><span style="font-weight: normal; color: #8080ff">.</span><span style="font-weight: normal; color: #8ce6ff">WriteLine</span>();
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;51</span>&#160;
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;52</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: lime">// Write the elapsed time</span>
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;53</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #ff80ff">Console</span><span style="font-weight: normal; color: #8080ff">.</span><span style="font-weight: normal; color: #8ce6ff">WriteLine</span>(<span style="font-weight: normal; color: #d896cd">"100,000 stringbuilder allocations time elapsed : {0} ms"</span>, 
                                    <span style="font-weight: normal; color: #8ce6ff">stopwatch</span><span style="font-weight: normal; color: #8080ff">.</span><span style="font-weight: normal; color: #8ce6ff">ElapsedMilliseconds</span>);
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;54</span>&#160;
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;55</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #8ce6ff">stopwatch</span><span style="font-weight: normal; color: #8080ff">.</span><span style="font-weight: normal; color: #8ce6ff">Reset</span>();
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;56</span>&#160;
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;57</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: lime">// Keep window open </span>
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;58</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; &#160;&#160;&#160; <span style="font-weight: normal; color: #ff80ff">Console</span><span style="font-weight: normal; color: #8080ff">.</span><span style="font-weight: normal; color: #8ce6ff">ReadKey</span>();
<span style="font-weight: normal; color: #2b91af">&#160;&#160;&#160;59</span>&#160;&#160;&#160;&#160; &#160;&#160;&#160; }