SimplyGold

Introduction

The CheckBoxList, like most other web controls, makes my life a whole lot easier by automating what used to be a lot of drudge work. The CheckBoxList also, like most other web controls, spits out ugly, ugly markup that makes me incredibly pissed off. But the good thing in ASP .NET is that if you don’t like the way something works, you can almost always inherit from the class and override the behavior, which is what we’re going to be doing with CheckBoxList.

Web Custom Controls vs. User Controls

Web custom controls have several benefits over user controls. To use a user control, you need to copy the .ascx file to each and every project that you you want to use it in, whereas web controls are compiled into .dll files, which means that to use them you just add a reference to the .dll file. There’s also the fact that user controls don’t have designer support (admittedly not a big deal for me, since I work almost exclusively in the “Source View,” but I know lots of other people swear by the “Design View”); web custom controls, on the other hand, do.

Web Custom Controls

Creating web custom controls can look like an untamable beast, but actually it’s quite easy. Start by clicking File » New » Project » Visual C# » Windows » Web Control Library. You can just as easily create a web custom control in whatever web project you happen to be working on, but because we’re interested in easy reuse, we’ll put our extended CheckBoxList file in a web control library. Name it whatever you want.

Visual Studio will automatically create a file called WebCustomControl1.cs for you. Delete everything inside the class — we don’t need it. Rename the file to something more appropriate, like UlCheckBoxList, and change all instances of WebCustomControl1 in the file to UlCheckBoxList (Visual Studio will probably automatically change the class name for you, but you also need to change the ToolboxData).

Base Class

All web custom controls need to inherit from the WebControl class either directly or indirectly. The CheckBoxList control already inherits from this class. Since we’re interested in only the rendering part of the CheckBoxList we’ll inherit from this one.

The Code

1. using System;
2. using System.Collections.Generic;
3. using System.ComponentModel;
4. using System.Text;
5. using System.Web;
6. using System.Web.UI;
7. using System.Web.UI.WebControls;
8.  
9. namespace XhtmlWebControls
10. {
11. [ToolboxData("<{0}:UlCheckBoxList runat=server></{0}:UlCheckBoxList>")]
12. public class UlCheckBoxList : CheckBoxList
13. {
14. protected override void Render(HtmlTextWriter writer)
15. {
16. Controls.Clear();
17.  
18. string input = "<input id={0}{1}{0} name={0}{2}{0} type={0}checkbox{0} value={0}{3}{0}{4} />";
19. string label = "<label for={0}{1}{0}>{2}</label>";
20.  
21. writer.WriteLine("<ul>");
22.  
23. for (int index = 0; index < Items.Count; index++)
24. {
25. writer.Indent++;
26. writer.Indent++;
27.  
28. writer.WriteLine("<li>");
29.  
30. writer.Indent++;
31.  
32. StringBuilder sbInput = new StringBuilder();
33. StringBuilder sbLabel = new StringBuilder();
34.  
35. sbInput.AppendFormat(
36. input,
37. """,
38. base.ClientID + "_" + index.ToString(),
39. base.ClientID + "$" + index.ToString(),
40. Items[index].Value,
41. (Items[index].Selected ? " checked" : ""));
42.  
43. sbLabel.AppendFormat(
44. label,
45. """,
46. base.ClientID + "_" + i.ToString(),
47. Items[index].Text);
48.  
49.  
50. writer.WriteLine(sbInput.ToString());
51. writer.WriteLine(sbLabel.ToString());
52.  
53. writer.Indent–;
54.  
55. writer.WriteLine("</li>");
56.  
57. writer.WriteLine();
58.  
59. writer.Indent–;
60. writer.Indent–;
61. }
62.  
63. writer.WriteLine("</ul>");
64. }
65. }
66. }
67.  

Explained

Line 11: [ToolboxData("<{0}:UlCheckBoxList runat=server></{0}:UlCheckBoxList>")]

This tells Visual Studio what to write in the “Source View” of the .aspx page in which we’re going to use the control. That is, if you drag and drop the control from the Toolbox onto the .aspx page.

Line 16: Controls.Clear(); The CheckBoxList has a single child control of type CheckBox, which is used to render all the checkboxes in the list. But as we’re going to be writing the HTML ourselves, we don’t care about this control. So I cleared it, just in case having it around screws up something.

Line 18: string input = “<input id={0}{1}{0} name={0}{2}{0} type={0}checkbox{0} value={0}{3}{0}{4} />”;

Line 19: string label = “<label for={0}{1}{0}>{2}</label>”;

The html that’s going to be outputted with some “string format variables” thrown in. The formats are going to be replaced with the actual values later on (lines 35 - 47).

Line 25: writer.Indent++;

Makes the <li> tags and everything underneath appear one tab to the right (in the HTML source code) of the <ul> tag.

Line 38: base.ClientID + “_” + i.ToString(),

Line 39: base.ClientID + “$” + i.ToString(),

If your CheckBoxList’s name was “CheckBoxList1,” then the id and the name you see in the HTML source code would be “CheckBoxList1_x” and “CheckBoxList1$x,” where x is the number of the checkbox in the list. I saw no reason to change this.

Adding Our Class to the Toolbox

Compile the Web Control Library you just created. Go to the Toolbox, right-click on “General” (or whichever tab you want to put the class under) and select “Choose Items” — In “Choose Toolbox Items,” under “.NET Framework Components,” select “Browse” and point to the .dll file of the class (should be located in the “Debug” folder in the “Bin” folder of your Web Control Library folder). Click OK. And you’re good to go.

As anybody who looked at the source code for the previous post might have noticed that I have indeed succeeded in cleaning up my syntax highlighter to produce the UI in a more pleasant manner. I’ve also included stylings for more language-specific elements (such as namespaces, classes, and operators). The line-handler is also much simpler and much more efficient. And finally, the spaces and the tabs (i.e., the presentation) is no longer present in the actual code (which is how it should be; at any rate, a list of ten spaces doesn’t even show up, so we’re better off getting rid of it).

The Code:

1. using System;
2. using System.Text;
3. using System.Text.RegularExpressions;
4.  
5. namespace SG.Net.Utilities.SyntaxHighlighter
6. {
7. public class CsharpSyntaxHighlighter
8. {
9. #region "Data and Data Access"
10.  
11. private bool useRegions = false;
12.  
13. private bool useLineNumbers = true;
14.  
15. public bool UseRegions
16. {
17. get { return useRegions; }
18.  
19. set { useRegions = value; }
20. }
21.  
22. public bool UseLineNumbers
23. {
24. get { return useLineNumbers; }
25.  
26. set { useLineNumbers = value; }
27. }
28.  
29. #endregion
30.  
31. public string Highlight(string code)
32. {
33. Regex all = new Regex(MakePatterns(), RegexOptions.Singleline);
34.  
35. code = all.Replace(code, new MatchEvaluator(HandleMatch));
36.  
37. //Handle Lines
38. StringBuilder line = new StringBuilder();
39.  
40. //Matches line with tabs
41. line.Append(@"(^ +.*?$)|");
42.  
43. //Matches line with multiple spaces at the start
44. line.Append(@"(^ {4,}.*?$)|");
45.  
46. //Matches all other lines
47. line.Append(@"(^.*?$)");
48.  
49. Regex lines = new Regex(line.ToString(), RegexOptions.Multiline);
50.  
51. code = lines.Replace(code, new MatchEvaluator(HandleLine));
52.  
53. //Break multi-line comments properly
54. Regex mlcToLines = new Regex(@"/*.*?*/", RegexOptions.Singleline);
55.  
56. code = mlcToLines.Replace(code, new MatchEvaluator(HandleMLC));
57.  
58. //Break hard strings properly
59. Regex hardStrToLines = new Regex(@"@&quot;.*?(?<!\)&quot;", RegexOptions.Singleline);
60.  
61. code = hardStrToLines.Replace(code, new MatchEvaluator(HandleHardStrings));
62.  
63. //Trim all extra white space
64. code = Regex.Replace(code, " {2,}", String.Empty);
65.  
66. return " <ol class = "code"> " + code + " </ol> ";
67. }
68.  
69. #region "Helper Methods"
70.  
71. private string MakePatterns()
72. {
73. StringBuilder patterns = new StringBuilder();
74.  
75. //Regular expression for single-quote strings
76. patterns.Append(@"(’[^ ]*?(?<!\)’)|");
77.  
78. //Regular expression for double-quote strings
79. patterns.Append(@"((?<!@)&quot;[^ ]*?(?<!\)&quot;)|");
80.  
81. //Regular expression for hard strings
82. patterns.Append(@"(@&quot;.*?(?<!\)&quot;)|");
83.  
84. //Regular expression for single-line comments
85. patterns.Append(@"(/(?!//)/[^ ]*)|");
86.  
87. //Regular expression for formal documentation comments
88. patterns.Append(@"(///[^ ]*)|");
89.  
90. //Regular expression for multi-line comments
91. patterns.Append(@"(/*.*?*/)|");
92.  
93. //Regular expression for language-specific syntax
94. //such as keywords, operators, namespaces, classes,
95. //and functions.
96. patterns.Append(GetSpecialSyntax());
97.  
98. return patterns.ToString();
99. }
100.  
101. private string GetSpecialSyntax()
102. {
103. StringBuilder specialSyntax = new StringBuilder();
104.  
105. specialSyntax.Append(GetOperators() + "|");
106.  
107. specialSyntax.Append(GetKeywords() + "|");
108.  
109. specialSyntax.Append(GetNamespaces() + "|");
110.  
111. specialSyntax.Append(GetClasses() + "|");
112.  
113. specialSyntax.Append(GetPreprocessorDirectives());
114.  
115. return specialSyntax.ToString();
116. }
117.  
118. private string GetOperators()
119. {
120. StringBuilder ops = new StringBuilder(@"/s+(
121.  
122. !|
123. !=|
124. %|
125. %=|
126. &|
127. &&|
128. &=|
129. (|
130. )|
131. *|
132. *=|
133. +|
134. ++|
135. +=|
136. -|
137. –|
138. -=|
139. ->|
140. .|
141. /|
142. /=|
143. :|
144. <|
145. <<|
146. <<=|
147. <=|
148. =|
149. ==|
150. >|
151. >=|
152. >>|
153. >>=|
154. ?|
155. [|
156. ]|
157. ^|
158. ^=|
159. {|
160. ||
161. |=|
162. |||
163. }|
164. ~
165.  
166. )/s+");
167.  
168. ops.Replace(" ", "");
169. ops.Replace(" ", "");
170. ops.Replace(" ", "");
171. ops.Replace(" ", "");
172. ops.Replace("/s", " ");
173.  
174. return ops.ToString();
175. }
176.  
177. private string GetKeywords()
178. {
179. StringBuilder kwds = new StringBuilder(@"b(
180.  
181. A list of all the keywords, omitted for brevity
182.  
183. )b");
184.  
185. kwds.Replace(" ", "");
186. kwds.Replace(" ", "");
187. kwds.Replace(" ", "");
188. kwds.Replace(" ", "");
189.  
190. return kwds.ToString();
191. }
192.  
193. private string GetNamespaces()
194. {
195. StringBuilder nsps = new StringBuilder(@"b.?(
196.  
197. A list of all the namespaces, omitted for brevity
198.  
199. ).?;?b");
200.  
201. nsps.Replace(" ", "");
202. nsps.Replace(" ", "");
203. nsps.Replace(" ", "");
204. nsps.Replace(" ", "");
205.  
206. return nsps.ToString();
207. }
208.  
209. private string GetClasses()
210. {
211. StringBuilder classes = new StringBuilder(@"b.?(
212.  
213. A list of all the classes, ommitted for brevity
214.  
215. )(?)?b");
216.  
217. classes.Replace(" ", "");
218. classes.Replace(" ", "");
219. classes.Replace(" ", "");
220. classes.Replace(" ", "");
221.  
222. return classes.ToString();
223. }
224.  
225. //Doesn’t work properly. For example, this matches
226. //#region in blah#region. But for some reason, /b
227. //isn’t working for this one.
228. private string GetPreprocessorDirectives()
229. {
230. StringBuilder preproc = new StringBuilder(@"(
231.  
232. #if|
233. #else|
234. #elif|
235. #endif|
236. #define|
237. #undef|
238. #warning|
239. #error|
240. #line|
241. #region|
242. #endregion
243.  
244. )");
245.  
246. preproc.Replace(" ", "");
247. preproc.Replace(" ", "");
248. preproc.Replace(" ", "");
249. preproc.Replace(" ", "");
250. preproc.Replace("/s", " ");
251.  
252. return preproc.ToString();
253. }
254.  
255. #endregion
256.  
257. #region "Match Handlers"
258.  
259. private string HandleMatch(Match m)
260. {
261. //Strings
262. if (m.Groups[1].Success || m.Groups[2].Success || m.Groups[3].Success)
263. {
264. return "<span class = "str">" + m.Value + "</span>";
265. }
266.  
267. //Single-line comments
268. else if (m.Groups[4].Success)
269. {
270. return "<span class = "slc">" + m.Value + "</span>";
271. }
272.  
273. //Formal documentation comments
274. else if (m.Groups[5].Success)
275. {
276. return "<span class = "fdc">" + m.Value + "</span>";
277. }
278.  
279. //Multi-Line Comments
280. else if (m.Groups[6].Success)
281. {
282. return "<span class = "mlc">" + m.Value + "</span>";
283. }
284.  
285. //Operators
286. else if (m.Groups[7].Success)
287. {
288. return "<span class = "op">" + m.Value + "</span>";
289. }
290.  
291. //Keywords
292. else if (m.Groups[8].Success)
293. {
294. return "<span class = "kwd">" + m.Value + "</span>";
295. }
296.  
297. //Namespaces
298. else if (m.Groups[9].Success)
299. {
300. return "<span class = "nsp">" + m.Value + "</span>";
301. }
302.  
303. //Classes
304. else if (m.Groups[10].Success)
305. {
306. return "<span class = "cls">" + m.Value + "</span>";
307. }
308.  
309. //Preprocessor directives
310. else if (m.Groups[11].Success)
311. {
312. return "<span class = "preproc">" + m.Value + "</span>";
313. }
314.  
315. else
316. return m.Value;
317. }
318.  
319. private string HandleLine(Match m)
320. {
321. //If the line is empty, add an   to make it show up
322. if (m.Value.Trim() == String.Empty)
323. return "<li>&nbsp;</li> ";
324.  
325. //Line with tabs
326. else if (m.Groups[1].Success)
327. {
328. //For each tab, we’re going to add 25px of left padding
329. int numberOfTabs = Regex.Match(m.Value, @"^ +").Length;
330.  
331. string line = "<li style={0}padding-left:{1}px;{0}>{2}</li> ";
332.  
333. return String.Format(line, """, numberOfTabs * 25, m.Value.TrimEnd(‘ ’, ‘ ’));
334. }
335.  
336. //Line with multiple spaces
337. else if (m.Groups[2].Success)
338. {
339. //We’re going to add 25px of left padding for every 4 spaces
340. int numberOfSpaces = Regex.Match(m.Value, @"^ {4,}").Length;
341.  
342. numberOfSpaces /= 4;
343.  
344. string line = "<li style={0}padding-left:{1}px;{0}>{2}</li> ";
345.  
346. return String.Format(line, """, numberOfSpaces * 25, m.Value.TrimEnd(‘ ’, ‘ ’));
347. }
348.  
349. //All other lines
350. else
351. {
352. return "<li>" + m.Value.TrimEnd(‘ ’, ‘ ’) + "</li> ";
353. }
354. }
355.  
356. private string HandleMLC(Match m)
357. {
358. StringBuilder value = new StringBuilder(m.Value);
359.  
360. value.Replace("<li>", "<li><span class = "mlc">");
361.  
362. value.Replace("px;">", "px;"><span class = "mlc">");
363.  
364. value.Replace("</li>", "<span></li>");
365.  
366. return value.ToString();
367. }
368.  
369. private string HandleHardStrings(Match m)
370. {
371. StringBuilder value = new StringBuilder(m.Value);
372.  
373. value.Replace("<li>", "<li><span class = "str">");
374.  
375. value.Replace("px;">", "px;"><span class = "str">");
376.  
377. value.Replace("</li>", "<span></li>");
378.  
379. return value.ToString();
380. }
381.  
382. #endregion
383. }
384. }

The GridView is a flexible control that’s quickly becoming my favorite. It makes so many things a breeze. One of these things happens to be pagination. All you need to do to get the data in pages is to add the following 2 lines (the second one being optional):

1. <asp:GridView
2. ID="PagedGridView"
3. AllowPaging=true
4. PageSize=25
5. EnableViewState=false
6. runat="server">
7. </asp:GridView>

Looks too good to be true? Unfortunately, it is. The way that GridView does pagination is horribly inefficient: it gets all the records from the database, then goes on to discard all the records that it doesn’t need. And it does this every time the page loads. Which doesn’t matter much if your table contains only 100 records, but if it contains 10,000 records or 50,000 records, then it starts to become a problem.

Fortunately, creating a more efficient paging for the GridView is easy, though we need to write more than two lines to make it work.####Getting Back Only the Records That We Want

To make the pagination process more efficient, we want to get from the database only those records that we actually need. Duh! We’ll do this using a nice SQL function called ROWNUMBER(). Unfortunately, this function is available only in SQL Server 2005, which not everyone has. We can’t simply substitute a increment-by-one primary key column for ROWNUMBER() because we’ll probably be deleting rows in the middle, which will screw up anything. If you don’t have SQL Server 2005, here’s a link to a tutorial that shows how to get the same result using a temporary table.

The stored procedure that does use ROW_NUMBER():

1. ALTER PROCEDURE dbo.EfficientGridViewPaging
2.  
3. @pageIndex int,
4. @pageSize int
5.  
6. AS
7.  
8. BEGIN
9.  
10. WITH Entries AS (
11.  
12. SELECT ROW_NUMBER() OVER (ORDER BY ID DESC)
13. AS RowNumber, random_data1, random_data2, random_data_etc
14. FROM the_table
15. )
16.  
17. SELECT random_data1, random_data2, random_data_etc
18.  
19. FROM Entries
20.  
21. WHERE RowNumber BETWEEN (@pageIndex - 1) * @pageSize + 1 AND @pageIndex * @pageSize
22.  
23. END

I first came across ROWNUMBER() in article while browsing the Internet as a means of procrastination learning more on SQL Server 2005. (Actually, to be technically accurate, I first learned about ROWNUMBER() while studying for Oracle certification, but I forgot about it completely until the article refreshed my memory. Hehe.) I created a stored procedure from scratch, feeling rather awesome. But as often happens, I found out that I wasn’t the only person to have this problem.

The stored procedure takes in two parameters (the current page and the maximum number of records to return) and returns the appropriate records.

Total Number of Pages

One rather inconvenient fact is that GridView’s PageCount property is read-only. This means that we need to figure out a way to get the total number of pages. Most implementations I saw have the total number of records as the output parameter of our afore-mentioned stored procedure. But as it’s unlikely that the number of records changes frequently, I chose to create a stored procedure that gets the number of records in the table and store it in the Session variable.

Creating the Links

With that out of the way, creating the links to different pages is fairly straight-forward. Here’s a somewhat sloppy implementation that allows you to go to the first page, the previous page, the next page, or the last page:

1. using System;
2. using System.Data;
3. using System.Configuration;
4. using System.Web;
5. using System.Web.Security;
6. using System.Web.UI;
7. using System.Web.UI.WebControls;
8. using System.Web.UI.WebControls.WebParts;
9. using System.Web.UI.HtmlControls;
10.  
11. using System.Data.Sql;
12. using System.Data.SqlClient;
13.  
14. public partial class _Default : System.Web.UI.Page
15. {
16. private int pageCount = 0;
17. private int pageIndex = 0;
18. private int pageSize = 25;
19.  
20. private string dbConn = "Your Connection String — should be in web.config";
21.  
22. protected void Page_Load(object sender, EventArgs e)
23. {
24. if (Request["page"] == null)
25. pageIndex = 1;
26.  
27. else
28. pageIndex = Int16.Parse(Request["page"]);
29.  
30. if (Session["pageCount"] == null)
31. {
32. //Get the total number of pages in the table
33. using (SqlConnection cn = new SqlConnection(dbConn))
34. {
35.     cn.Open();
36.  
37.     SqlCommand cmd = cn.CreateCommand();
38.  
39.     cmd.CommandText = "GetTotalPages";
40.     cmd.CommandType = CommandType.StoredProcedure;
41.  
42.     //The stored procedure returns total number of records
43.     pageCount = Convert.ToInt16(cmd.ExecuteScalar());
44. }
45.  
46. pageCount = pageCount / pageSize;
47.  
48. Session["pageCount"] = pageCount;
49. }
50.  
51. else
52. pageCount = (int)Session["pageCount"];
53.  
54. GetCurrentPageData();
55. }
56.  
57. private void GetCurrentPageData()
58. {
59. //Do error processing here
60. if (pageIndex > pageCount || pageIndex < 1)
61. return;
62.  
63. SqlDataSource sds = new SqlDataSource();
64.  
65. sds.ConnectionString = dbConn;
66.  
67. sds.SelectCommand = "EfficientGridViewPaging";
68. sds.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
69.  
70. sds.SelectParameters.Add("pageIndex", pageIndex.ToString());
71. sds.SelectParameters.Add("pageSize", pageSize.ToString());
72.  
73. PagedGridView.DataSource = sds;
74. PagedGridView.DataBind();
75.  
76. UpdatePagination();
77. }
78.  
79. private void UpdatePagination()
80. {
81. //Update the hrefs to point to the right page
82. FirstPageLink.HRef = "Default.aspx?page=1";
83. PrevPageLink.HRef = "Default.aspx?page=" + (pageIndex - 1);
84. NextPageLink.HRef = "Default.aspx?page=" + (pageIndex + 1);
85. LastPageLink.HRef = "Default.aspx?page=" + pageCount;
86.  
87. //We’re on first page
88. if (pageIndex <= 1)
89. {
90. FirstPageLink.HRef = String.Empty;
91. PrevPageLink.HRef = String.Empty;
92. }
93.  
94. //We’re on last page
95. else if (pageIndex >= pageCount)
96. {
97. NextPageLink.HRef = String.Empty;
98. LastPageLink.HRef = String.Empty;
99. }
100.  
101. CurrentPage.Text = "Page " + pageIndex.ToString() + " of " + pageCount.ToString();
102. }
103. }
104.  

The Problem

You want to insert 5000 records into the database. You normally do this by creating a separate insert statement for each record, and this is fine with your boss, but you’re a glutton for punishment and want to optimize this scenario because it occurs often enough in your application. On the other hand, you’re also lazy, have ten thousand interesting things to do, and would love to not have to spend a lot of time tackling the problem. Oh, and you’ll be using stored procedures because, as everyone knows, they’re better than dynamic SQL. So? What do you do?

Enter OPENXML

OPENXML is a nice little function that will allow us to solve the above-mentioned problem without feeling like we’re hacking our way through it. The need for a hack wouldn’t arise if we can just send any number of parameters that we want to the stored procedure; i.e., in .NET, we have the params[] array, and in Javascript, we have the args array, but in stored procedures, we don’t have any such luxury.

The code, then, of the stored procedure that bulk-inserts data into a table using OPENXML:

1. CREATE procedure [dbo].[openxml_test]
2.  
3. @xmlDocument text
4.  
5. as
6.  
7. declare @handle int
8.  
9. exec sp_xml_preparedocument @handle output, @xmlDocument
10.  
11. begin
12.  
13. set nocount on;
14.  
15. insert into openxml_test_db(ID, random_data)
16.  
17.      select tid, tdata from openxml(@handle, N‘ROOT/Thread’, 2)
18.  
19.      with (tid uniqueidentifier, tdata varchar(100))
20.  
21. exec sp_xml_removedocument @handle
22.  
23. end
24.  
25.  
26.  

The XML structure is going to be of the form:

1. <ROOT>
2.  
3. <Thread>
4.  
5. <TGuid>cf69e831-4b82-43bc-bc0c-00013af696d3</TGuid>
6.  
7. <TData>An awful lot of data</TData>
8.  
9. </Thread>
10.  
11. </ROOT>

What We’re Doing

Line 3: Sending the variable as text is less effecient than sending it as varchar(8000). If you’re reasonably sure that your xml (including the tags, data, and all) that you send to the stored procedure isn’t going to exceed 8000 characters in length, I suggest you use that instead.

Line 9: sp_xml_preparedocument is a system stored procedure that, as its name implies, prepares the xml document. This stored procedure needs to be called before a call to openxml.

Line 17: We’re getting the data from the xml document and inserting it into the table. The @handle is the handle returned by sp_xml_preparedocument, the‘ROOT/Thread’ is the XPATH that tells OPENXML which rows to process, and the 2 describes the mapping between OPENXML’s rowset and the XML Document. This last parameter is optional. The default mapping is attribute-centric mapping, which we would use if our XML structure was like the following:

1. …
2. <TGuid value=”The guid goes here” />
3. <TData value=”An awful lot of data” />
4. …

Line 19: We’re basically telling how to format the data we get from OPENXML.

Line 21: The XML Document that SQL Server created is stored in memory. To avoid memory leaks, we close the handle.

So, What Kind of Savings Can We Expect?

Two things to notice:

1. The savings become more and more significant as the number of records we’re inserting becomes larger.
2. Even when we’re inserting only 50 records, the difference is noticeable (about .34 seconds).