Clapper
Asp Classic Sql Connection Mapper
Install / Use
/learn @jeremylcarter/ClapperREADME
<h2>Clapper : Asp Classic Sql Mapper</h2>
<p>Copyright 2012 Jeremy Child |<a href="mailto:jeremychild@gmail.com">Support</a></p>
<p>Released under <a href="http://en.wikipedia.org/wiki/MIT_License">MIT Licence</a></p>
<h3>Connecting</h3>
<p>You can connect with any provider that is supported with ADODB.Connection. You can check providers using ODBC in Windows or you can connect to a dsn.</p>
<p>Connecting in easy. Create a new instance of the SqlServerConnection class and pass it your connection string.</p>
<pre><code class="vbscript">
Dim sqlConnection : Set sqlConnection = New SqlServerConnection<br />
sqlConnection.ConnectionString = "SomeConnString"
</code></pre>
<p>You can enable tracing which provides the debug output you see in the below examples, detailing the sql generated and a dump of a Recordset if there is one returned. To enable it set the Trace property to True.</p>
<pre><code class="vbscript">
sqlConnection.Trace = True
</code></pre>
<h3>QueryToList</h3>
<p>
You can execute a generic sql query that returns columns (with names or alias) and have them presented as objects with properties dynamically created at execution. These objects are just plain old objects. The type of the property is infered from the type given from the sql provider, or is returned as a string. You can use IsNull as per usual.</p>
</p>
<p>
Because you are getting an Object back that is not a value type you must use the 'Set' syntax on results back that are not value types.
</p>
<pre><code class="vbscript">
Set employeeList = sqlConnection.QueryToList("Select * From Employee")<br />
For Each employee in employeeList<br />
Response.Write(employeee.FirstName) ' Yes thats right you can just type in the field name!<br />
Response.Write(employeee.EmployeeId) ' OMG its already an integer!<br />
Response.Write(employeee.HireDtm) ' OMG its already an DateTime!<br />
Next
</code></pre>
<div style="background-color: #FCF8E3; color: #C09853; padding: 3px; border: 1px solid #FBEED5;">Select * From Employee</div><TABLE width="850" style="border: 2px solid #17b; margin: 0.3em 0.2em;"><TR><tr><TD colspan="8" style="color: white; background-color: #0066FF"><B>Dump of Recordset</B></TD><tr><TD style="color: black; background-color: #ddd"><B>EmployeeId</B> <font color="grey"><Int32></font></TD><TD style="color: black; background-color: #ddd"><B>FirstName</B> <font color="grey"><String></font></TD><TD style="color: black; background-color: #ddd"><B>LastName</B> <font color="grey"><String></font></TD><TD style="color: black; background-color: #ddd"><B>CompanyId</B> <font color="grey"><Int32></font></TD><TD style="color: black; background-color: #ddd"><B>HireDtm</B> <font color="grey"><DateTime></font></TD><TD style="color: black; background-color: #ddd"><B>Active</B> <font color="grey"><Boolean></font></TD><TD style="color: black; background-color: #ddd"><B>Number</B> <font color="grey"><Int64></font></TD><TD style="color: black; background-color: #ddd"><B>Address</B> <font color="grey"><String></font></TD></TR><TR><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">1</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">Jeremy</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">Child</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">1</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">1/01/2000</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">True</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">465443534534534</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">Someville</TD></TR><TRBGCOLOR="#d3d3d3"><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">2</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">Peter</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">Mason</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">1</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">1/01/2000</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">True</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">435345435334411</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">Someville</TD></TR><TR><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">3</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">Leeroy</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">Jenkins</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">1</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">1/01/2004</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">False</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">342342322216678</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">Sometown</TD></TR><TRBGCOLOR="#d3d3d3"><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">4</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">Jack</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">Jackson</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">1</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">1/01/2008</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">True</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">23432119876</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;"><font color="green"><i>null</i></font></TD></TR></TABLE>
<h3>QueryToList Fluent Sql</h3>
<pre><code class="vbscript">
Set sql = SqlBuilder.Select().From("Employee").Where("CompanyId = 1").Where("EmployeeId Between 1 And 300")<br />
Set employeeList = sqlConnection.QueryToList(sql)<br />
For Each employee in employeeList<br />
Response.Write(employeee.FirstName) ' Yes thats right you can just type in the field name!<br />
Next
</code></pre>
<div style="background-color: #FCF8E3; color: #C09853; padding: 3px; border: 1px solid #FBEED5;">SELECT * FROM Employee WHERE (CompanyId = 1) AND (EmployeeId Between 1 And 300)</div><TABLE width="850" style="border: 2px solid #17b; margin: 0.3em 0.2em;"><TR><tr><TD colspan="8" style="color: white; background-color: #0066FF"><B>Dump of Recordset</B></TD><tr><TD style="color: black; background-color: #ddd"><B>EmployeeId</B> <font color="grey"><Int32></font></TD><TD style="color: black; background-color: #ddd"><B>FirstName</B> <font color="grey"><String></font></TD><TD style="color: black; background-color: #ddd"><B>LastName</B> <font color="grey"><String></font></TD><TD style="color: black; background-color: #ddd"><B>CompanyId</B> <font color="grey"><Int32></font></TD><TD style="color: black; background-color: #ddd"><B>HireDtm</B> <font color="grey"><DateTime></font></TD><TD style="color: black; background-color: #ddd"><B>Active</B> <font color="grey"><Boolean></font></TD><TD style="color: black; background-color: #ddd"><B>Number</B> <font color="grey"><Int64></font></TD><TD style="color: black; background-color: #ddd"><B>Address</B> <font color="grey"><String></font></TD></TR><TR><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">1</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">Jeremy</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">Child</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">1</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">1/01/2000</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">True</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">465443534534534</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">Someville</TD></TR><TRBGCOLOR="#d3d3d3"><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">2</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">Peter</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">Mason</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">1</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">1/01/2000</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">True</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">435345435334411</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">Someville</TD></TR><TR><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">3</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">Leeroy</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">Jenkins</TD><TD style="vertical-align: top;border: 1px solid #aaa; padding: 0.1em 0.2em;margin: 0;">1<
