In this article I will do something a bit different from my usual. Typically I review programming issues or topics of interest. In this case, however, I am presenting a handy new tool that will hopefully speed up a small, but time-consuming part of anyone’s development. That is, I have put together a tool that will let you separate a list of items, and duplicate each of those items several times while injecting code of your choosing between each duplicate as well as in front and in back.
An Example Situation
Still not so sure of the usefulness of the functionality I described? Let me give an example that might explain the problem in a little more depth.
If you run a simple table query in SSMS (SQL Server Management Studio), you are presented with an automatically generated SQL statement for the table that you are viewing. Often the default auto-generated SQL is not good enough and you need to add your own custom code. For example, if you are working with a SELECT statement that you are embedding into a Web form, you will likely need to convert database NULLS to some other value… if you don’t, then you will see an error message on your Web page when you try to display the results.
Further to the example situation above, let’s say that you are under pressure to deliver a Web page quickly that just outputs the results of your query in an ASP.NET GridView control exactly like it looks in the database table. The simplest way to embed the results on your Web page and avoid any errors from various data-types and nulls is to encapsulate each of your Select elements in both an ISNULL statement and a CAST AS VARCHAR function.
This means that a query that first looked like this:
SELECT FirstName, LastName, CatsName, DogsName FROM RandomTable
Will now look like this:
SELECT ISNULL(CAST(FirstName AS VARCHAR(255)),'') ,ISNULL(CAST(LastName AS VARCHAR(255)),'') ,ISNULL(CAST(CatsName AS VARCHAR(255)),'') ,ISNULL(CAST(DogsName AS VARCHAR(255)),'') FROM RandomTable
The Problem:
Ok, so as you can see, there will now be an ISNULL wrapper as well as a CAST to VARCHAR around each of your SELECT statement elements. This isn’t really a big deal until you run your statement and realize that by adding the wrappers, you have lost all of your column headers. Ouch, that means that you will need to copy the SELECT statement element name to the end of the line for each element for your GridView to properly map the columns. This isn’t a big task for an example like the SELECT statement above that has just a few columns selected, but once you start getting into a longer query with more elements, the copying and pasting gets to be tedious.
The example above is a simple illustration of a time when you would want to duplicate items in a list and inject code between them. There are a lot of other possible situations in everyday coding where you might want to do this without having to copy and paste manually.
The Solution:
Rather than copying each element in a list over several times and then putting the wrappers around the copies by hand, you probably should use a program. You’ll save yourself minutes of repetitive copying and pasting effort if a program can do it for you automatically.
And that’s where the small program that I’m posting the sample code for below comes in. Just copy and paste it into an HTML page on your desktop and run it in the browser of your choosing. I have tested this running IE 11 and Chrome 40.0.
<html>
<head>
<script language="JavaScript">
// Written and Developed in 2015 by Justin Cooney at http://jwcooney.com
// Copyright (c) 2015, Justin Cooney, All rights reserved.
function processText() {
var returnValue = '';
var strSeparator = document.getElementById('txtSeparator').value; // This value will be what is used to split the items in the list
var textToProcess = document.getElementById('txtBefore').value;
textToProcess = textToProcess.replace(/(\r\n|\n|\r)/gm, '') // remove line breaks. Seperating line breaks will be added later once processing is complete.
var arrProcText = textToProcess.split(strSeparator);
var selCnt = document.getElementById('selDuplicateCnt');
var insCnt = selCnt.options[selCnt.selectedIndex].value;
var spanItemContainer = document.getElementById('spanVariableArea');
var paragraphNodesList = spanItemContainer.getElementsByTagName('P');
var currNodeCnt = paragraphNodesList.length;
for (i = 0; i < arrProcText.length; i++) {
var txtPrepend = document.getElementById('txtAppendBefore').value;
var txtAppend = document.getElementById('txtAppendAfter').value;
var tmpInsertValue = '';
for (z = 0; z < insCnt; z++) {
// We loop through the requested number of clones and clone the value
tmpInsertValue += '' + arrProcText[i];
// Ok now we have the value and we need to get what the user wants to insert between the cloned items
if (currNodeCnt > z) {
var tmpChildNode = paragraphNodesList[z];
var tmpTextAreas = tmpChildNode.getElementsByTagName('input');
tmpInsertValue = tmpInsertValue + (tmpTextAreas[0]).value;
}
}
if (i > 0) txtPrepend = '' + strSeparator + '' + txtPrepend; // we don't need to add a comma (or whatever the selected separator was) to the first item
returnValue += txtPrepend + tmpInsertValue + txtAppend + '\r\n'; // append a final line break for item clarity
}
document.getElementById('txtAfter').value = returnValue;
}
function addInserts() {
var selCnt = document.getElementById('selDuplicateCnt');
var insCnt = selCnt.options[selCnt.selectedIndex].value;
intInsCnt = Math.abs(insCnt - 1);
var spanItemContainer = document.getElementById('spanVariableArea');
// clean up excess injection text areas based on the user's selection in the drop-down list
var paragraphNodesList = spanItemContainer.getElementsByTagName('P');
var currNodeCnt = paragraphNodesList.length;
if (intInsCnt < currNodeCnt) {
// remove excess text boxes backwards, leaving text boxes that are still valid based on the user's drop-down list selection.
for (x = currNodeCnt - 1; x >= intInsCnt; x--) {
var tmpChildNode = paragraphNodesList[x];
spanItemContainer.removeChild(tmpChildNode);
}
}
// Add fresh injection text areas based on the user's selection in the drop-down list. set starting node count to existing nodes rather than hardcoding zero
for (i = currNodeCnt; i < insCnt - 1; i++) {
var tmpParagraph = document.createElement('P');
var tmpTextBox = document.createElement('input');
tmpTextBox.setAttribute('type', 'text');
tmpTextBox.setAttribute('id', 'tmpText' + i);
var tmpText = document.createTextNode('Enter Text Between clone: ' + (i + 1) + ' and ' + (i + 2) + ': ');
tmpParagraph.appendChild(tmpText);
tmpParagraph.appendChild(tmpTextBox)
spanItemContainer.appendChild(tmpParagraph);
}
}
</script>
<title>Helper Function</title>
</head>
<body>
<h2>Take a value-seperated list of items and duplicate them N times and insert text around and between each clone</h2>
<table>
<tr><td>
Separate the list items by: </td><td>
<input type="text" id="txtSeparator" value="," style="width:50px;">
</td></tr>
<tr><td>
How many clones of each list item: </td><td>
<select id="selDuplicateCnt" on onchange="addInserts()">
<option value="1" selected>1</option>
<option value="2">2</option>
<option value="3">3</option>
</select></td></tr>
<tr><td>
Append Before Line:</td><td> <input type="text" id="txtAppendBefore"></td></tr>
<tr><td colspan="2" style="background-color:#C9E4FF;"><span id="spanVariableArea">
</span></td></tr>
<tr><td>Add at End of Line:</td><td><input type="text" id="txtAppendAfter"></td></tr>
<tr><td colspan="2">
<input type="button" value="Process Text" id="btnProcessText" onClick="processText();">
</td></tr></table>
<hr>
<table width="1000">
<tr><td valign="top">
Enter Text to Process:<br>
<textarea id="txtBefore" rows="20" cols="50">FirstName,LastName,CatsName,DogsName</textarea>
</td><td> </td><td valign="top">
Processed Text:<br>
<textarea id="txtAfter" rows="20" cols="50"></textarea>
</td>
</tr></table>
</body>
</html>
Justin Cooney 