Our company has a legacy CGI tool, rather like ColdFusion, called Protium. The syntax is reminiscent of SGML. We were having some pages that were taking far too long to build so we experimented with having the browser do all the table generation rather than the server. We ended up not using the technique but it was fun researching and writing the code.
Up on my Github you'll find the COM DLL tool that we built, PTools.Kit, to do SQL Server SELECTs and return the resulting DataTable as JSON.
PTools.Kit currently exposes 3 methods: TagValue, TagAttrValue and EvaluateSQLReturnJSON. The first two were just to get my head around the hows of writing a COM DLL in C#. The last one was what we were there for.
EvaluateSQLReturnJSON takes two mandatory string parameters and one optional integer. The first is the connection string to the database. The second is the SQL statement. The third is a timeout which defaults to 60 seconds.
The implementation requires a public interface to describe the ComVisible parts (just showing EvaluteSQLReturnJSON)
[Guid("5E10370D-B1C1-400B-80C0-481A9E2AD499")]
[ComVisible(true)]
public interface IKit
{
string EvaluateSQLReturnJSON(string connection, string sql, int timeout = 60);
}
and then there's the implementation of the interface
[Guid("E9C9ADFC-57F9-4BE9-9593-38B80E1B1284")]
[ClassInterface(ClassInterfaceType.None)]
[ComVisible(true)]
public class Kit : IKit
{
string IKit.EvaluateSQLReturnJSON(string connection, string sql, int timeout)
{
SqlConnection sqlConnection;
try
{
sqlConnection = new SqlConnection(connection);
sqlConnection.Open();
}
catch (Exception e)
{
return JsonConvert.SerializeObject(new FailureBlock
{
Error = e.Message,
Cargo = null
});
}
while (sqlConnection.State == ConnectionState.Connecting)
{
Thread.Sleep(1);
}
using (DataTable table = new DataTable())
{
using (var command = sqlConnection.CreateCommand())
{
command.CommandText = sql;
command.CommandTimeout = timeout;
command.CommandType = CommandType.Text;
try
{
using (SqlDataReader reader = command.ExecuteReader())
{
table.Load(reader);
}
}
catch (Exception e)
{
return JsonConvert.SerializeObject(new FailureBlock
{
Error = e.Message,
Cargo = null
});
}
}
return JsonConvert.SerializeObject(new SuccessBlock
{
Error = null,
Cargo = table
});
}
FailureBlock and SuccessBlock are two public classes used to maintain a standard JSON layout. In this case, we return a JSON object containing two keys: Error and Cargo. If Error is not null, an error has occurred and Error is a string containing the error message. If Error is null, then Cargo contains data.
We copied PTools.DLL and the Newtonsoft.Json.DLL to the server (Azure) and then installed PTools.DLL using
c:\Windows\Microsoft.NET\Framework\v4.0.30319\RegAsm.exe -tlb -codebase PTools.dll
After this we were able to use the COM object via the Windows Scripting Host:
var ptools = new ActiveXObject("PTools.Kit");
Next came how to use the data. To render a table from the database the following was written:
<@ DEFUDOLITLIT>S.J|__Transformer|
<@ DEFKEYLIT>__WSHLanguage|JScript</@>
<@ LETSCPCAP>jscript|new ActiveXObject("PTools.Kit").EvaluateSQLReturnJSON("XXX connectionstring XXX", "<@ SAYPAR>1</@>");</@>
<@ LETRESCLCSCP>...|jscript</@>
<@ DEFKEYLIT>__WSHLanguage|VBScript</@>
</@>
// the above Protium code defines a user-defined operator called 'S.J`.
// The first parameter of a call to S.J is passed in as
// the SQL statement for the second parameter of the
// EvaluateSQLReturnJSON method call.
<div id='top_slowest'></div>
<script id='top_slowest'>
let slowestData = JSON.parse('<@ SAYTRICAP><@ SAYS.JLIT>SELECT * FROM tblScriptEcho order by scriptmilliseconds desc</@></@>');
// the above captures and trims the output of the S.J call
// and then leaves behind a string which is then parsed
// into a JSON object.
if (slowestData.Error === null) {
let table = document.createElement('table');
table.setAttribute('id','top_slowest');
table.border = 1;
let caption = table.createCaption();
let top = ('<@ SAYVAR>top</@>' === '' ? slowestData.Cargo.length : parseInt('<@ SAYVAR>top</@>',10));
// top is a variable defined on the query string.
// if present, use it. If not, take the length of the
// Cargo array.
caption.innerHTML = `Top ${top} (of ${slowestData.Cargo.length}) slowest loading searches`;
let thead = table.createTHead();
let here = thead.insertRow(-1);
here.insertCell(-1).textContent = 'Url'
here.insertCell(-1).textContent = 'Milliseconds';
for (let r = 0; r < slowestData.Cargo.length; r++) {
if (r >= top) { break; }
here = table.insertRow(-1);
let anchor = document.createElement('a');
anchor.href = 'https://hopl.info/' + slowestData.Cargo[r].ScriptEcho;
anchor.target = '_blank';
anchor.innerText = slowestData.Cargo[r].ScriptEcho;
let cell = here.insertCell();
cell.appendChild(anchor);
here.insertCell().textContent = slowestData.Cargo[r].ScriptMilliSeconds;
}
let top_slowest = document.querySelector("div#top_slowest");
top_slowest.appendChild(table);
}
</script>
The Cargo component of the JSON string contains an array, each element of which being a record of fieldname and value. That data is then used to create the table on the fly and finally append it to the "top_slowest" DIV.
We ultimately abandoned the idea of table generation and went with generating static pages for the most processor-demanding queries. Nevertheless, it was an interesting thing to try and I had fun figuring out how to get all the cogs meshing appropriately.
Top comments (0)