Clapper : Asp Classic Sql Mapper
Copyright 2012 Jeremy Child |Support
Released under MIT Licence
Connecting
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.
Connecting in easy. Create a new instance of the SqlServerConnection class and pass it your connection string.

Dim sqlConnection : Set sqlConnection = New SqlServerConnection

sqlConnection.ConnectionString = "SomeConnString"

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.

sqlConnection.Trace = True

QueryToList

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.


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.


Set employeeList = sqlConnection.QueryToList("Select * From Employee")

For Each employee in employeeList

Response.Write(employeee.FirstName) ' Yes thats right you can just type in the field name!

Response.Write(employeee.EmployeeId) ' OMG its already an integer!

Response.Write(employeee.HireDtm) ' OMG its already an DateTime!

Next

Select * From Employee
Dump of Recordset
EmployeeId <Int32>
FirstName <String>
LastName <String>
CompanyId <Int32>
HireDtm <DateTime>
Active <Boolean>
Number <Int64>
Address <String>
1
Jeremy
Child
1
1/01/2000
True
465443534534534
Someville
2
Peter
Mason
1
1/01/2000
True
435345435334411
Someville
3
Leeroy
Jenkins
1
1/01/2004
False
342342322216678
Sometown
4
Jack
Jackson
1
1/01/2008
True
23432119876
null
QueryToList Fluent Sql

Set sql = SqlBuilder.Select().From("Employee").Where("CompanyId = 1").Where("EmployeeId Between 1 And 300")

Set employeeList = sqlConnection.QueryToList(sql)

For Each employee in employeeList

Response.Write(employeee.FirstName) ' Yes thats right you can just type in the field name!

Next

SELECT * FROM Employee WHERE (CompanyId = 1) AND (EmployeeId Between 1 And 300)
Dump of Recordset
EmployeeId <Int32>
FirstName <String>
LastName <String>
CompanyId <Int32>
HireDtm <DateTime>
Active <Boolean>
Number <Int64>
Address <String>
1
Jeremy
Child
1
1/01/2000
True
465443534534534
Someville
2
Peter
Mason
1
1/01/2000
True
435345435334411
Someville
3
Leeroy
Jenkins
1
1/01/2004
False
342342322216678
Sometown
4
Jack
Jackson
1
1/01/2008
True
23432119876
null
QueryToArray

Set employeeArray = sqlConnection.QueryToArray("Select * From Employee Where Active = 1")

Select * From Employee Where Active = 1
Dump of Recordset
EmployeeId <Int32>
FirstName <String>
LastName <String>
CompanyId <Int32>
HireDtm <DateTime>
Active <Boolean>
Number <Int64>
Address <String>
1
Jeremy
Child
1
1/01/2000
True
465443534534534
Someville
2
Peter
Mason
1
1/01/2000
True
435345435334411
Someville
4
Jack
Jackson
1
1/01/2008
True
23432119876
null
QuerySingle

Set singleEmployee = sqlConnection.QuerySingle("Select * From Employee Where EmployeeId = 1")

Select * From Employee Where EmployeeId = 1
Dump of Recordset
EmployeeId <Int32>
FirstName <String>
LastName <String>
CompanyId <Int32>
HireDtm <DateTime>
Active <Boolean>
Number <Int64>
Address <String>
1
Jeremy
Child
1
1/01/2000
True
465443534534534
Someville
QueryInteger

employeeCount = sqlConnection.QueryInt("Select COUNT(1) From Employee")
Response.Write(employeeCount) ' Integer no need to use Set

Select COUNT(1) From Employee
Dump of Recordset
 <Int32>
4
QueryDateTime

serverDate = sqlConnection.QueryDateTime("Select GETDATE() AS 'Today'")

'serverDate = sqlConnection.QueryDateTime("Select CAST(NULL As DateTime) AS 'Today'")

Response.Write(serverDate) ' DateTime no need to use Set

Select GETDATE() AS 'Today'
Dump of Recordset
Today <DateTime>
8/08/2012 4:06:23 PM
IsNull Checking

serverDate = sqlConnection.QueryDateTime("Select CAST(NULL As DateTime) AS 'Today'")

Response.Write(IsNull(serverDate)) ' Should return True

Select CAST(NULL As DateTime) AS 'SomeNullDate'
Dump of Recordset
SomeNullDate <DateTime>
null
Inserting

sql = "INSERT INTO [Test].[dbo].[Employee] ([FirstName],[LastName],[CompanyId],[HireDtm],[Active],[Number],[Address])" & _

"VALUES ('Testing','Person',1,GETDATE(),1,1234,'Sometown')"

insertedEmployeeId = sqlConnection.ExecuteReturnIdentity(sql)

Response.Write(insertedEmployeeId) ' Should return Integer

Updating

Set employeeList = sqlConnection.QueryToList("Select * From Employee")

For Each e in employeeList

   e.Active = True ' Yes thats right you can just type in the field name!

   sqlConnection.Update e, "Employee", "EmployeeId"

Next

Select * From Employee WHERE Address = 'Someville'
Dump of Recordset
EmployeeId <Int32>
FirstName <String>
LastName <String>
CompanyId <Int32>
HireDtm <DateTime>
Active <Boolean>
Number <Int64>
Address <String>
1
Jeremy
Child
1
1/01/2000
True
465443534534534
Someville
2
Peter
Mason
1
1/01/2000
True
435345435334411
Someville
UPDATE Employee SET FirstName='Jeremy',LastName='Child',CompanyId=1,HireDtm=CONVERT(DATETIME,'1/01/2000',103) ,Active=1,Number=465443534534534,Address='Someville' WHERE EmployeeId=1;
UPDATE Employee SET FirstName='Peter',LastName='Mason',CompanyId=1,HireDtm=CONVERT(DATETIME,'1/01/2000',103) ,Active=1,Number=435345435334411,Address='Someville' WHERE EmployeeId=2;
Updating/Inserting using ExpandoObject
ExpandoObject is an object created from a Dictionary specification. This object is created at runtime to have the properties specified in the dictionary. Types are infered from the typename of the property value.

Dim inspectionDefinition: Set inspectionDefinition = Server.CreateObject("Scripting.Dictionary")

inspectionDefinition.Add "InspectionTypeId", "Integer"

inspectionDefinition.Add "Question", "String"

inspectionDefinition.Add "ResponseTypeId", "Integer"

inspectionDefinition.Add "LastEdited", "DateTime"

inspectionDefinition.Add "LastEditedBy", "Integer"

inspectionDefinition.Add "Weight", "Integer"       


' Use a class if you want more control / poco behaviour

Dim newInspection : Set newInspection = ExpandoObject(inspectionDefinition)



sqlConnection.Update newInspection, "Inspection", "InspectionId"

'sqlConnection.Insert newInspection, "Inspection", "InspectionId"


Deleting

Set employeeList = sqlConnection.QueryToList("Select * From Employee")

For Each e in employeeList

   sqlConnection.Delete e, "Employee", "EmployeeId"

Next


Insert via Stored Procedure

Dim addEmployeeParams: Set addEmployeeParams = CreateObject("Scripting.Dictionary")

    addEmployeeParams.Add "employeeId", 1234

    addEmployeeParams.Add "someOtherId",4321

    addEmployeeParams.Add "someDate",Now()

    addEmployeeParams.Add "someString","Sometown"

    
Dim addEmployee : addEmployee = sqlConnection.ExecStoredProcedureIdentity("[InsertNewEmployee]",addEmployeeParams)

newEmployeeId = addEmployee


Execute

sqlConnection.Execute("UPDATE WHERE .... ")


Execute Rows Affected

Dim affected : affected = sqlConnection.ExecReturnRowsAffected("UPDATE WHERE .... ")


Data Types

Set dataTypeTest = sqlConnection.QuerySingle("Select 123 As Int, 1.3 As Decimal, GETDATE() As Date, 'SomeString' As VarChar, Cast(0 As BIT) As Bit, CAST(NULL As BIT) As SomeBoolNull,CAST(NULL As Bigint) As SomeInt64Null, 5155474835647 As BigInt")

Select 123 As Int, 1.3 As Decimal, GETDATE() As Date, 'SomeString' As VarChar, Cast(0 As BIT) As Bit, CAST(NULL As BIT) As SomeBoolNull,CAST(NULL As Bigint) As SomeInt64Null, 5155474835647 As BigInt
Dump of Recordset
Int <Int32>
Decimal <Decimal>
Date <DateTime>
VarChar <String>
Bit <Boolean>
SomeBoolNull <Boolean>
SomeInt64Null <Int64>
BigInt <Decimal>
123
1.3
8/08/2012 4:06:23 PM
SomeString
False
null
null
5155474835647
Method List

  

    
Method Name
    
Description
    
Return Type
  
  

    
RecordSet(string sql)
    
Returns an ADODB.Recordset object from the sql text provided
    
ADODB.Recordset
  
  

    
BinaryStream(string sql)
    
Return sn ADODB.Stream object from the sql text provided
    
ADODB.Stream
  
  

    
ExecReturnRowsAffected(string sql)
    
Returns the number of rows affected by the executed sql
    
Integer
  
  

    
Execute(string sql)
    
Returns boolean if the executed sql completed without error
    
Boolean
  
  

    
Open()
    
Opens the connection
    
Void
  
  

    
SanitizeString(string input)
    
Returns a sanitized sql formatted string
    
String
  
  

    
Close()
    
Closes the connection
    
Void
  
  

    
TraceRecordset(recordset rs)
    
Dumps the recordset into a HTML table
    
Void
  
  

    
DataTypeNameFromAdoCode(int32 code)
    
Returns a friendly name for an ADO data type code
    
String
  
  

    
QueryToList(string sql)
    
Returns a list of dynamic objects based on the sql results
    
System.Collections.ArrayList
  
  

    
Query(string sql)
    
Returns an array of dynamic objects based on the sql results
    
Array
  
  

    
QuerySingle(string sql)
    
Returns a single dynamic object based on the sql results
    
T is dynamic
  
  

    
QueryToString(string sql)
    
Retuns a string for the first column returned from the first row returned based on the sql results
    
String
  
  

    
QueryInt(string sql)
    
Retuns an integer for the first column returned from the first row returned based on the sql results
    
Integer
  
  

    
QueryBoolean(string sql)
    
Retuns a boolean for the first column returned from the first row returned based on the sql results
    
Boolean
  
  

    
QueryDateTime(string sql)
    
Return a DateTime for the  first column returned from the first row returned based on the sql results
    
DateTime
  
  

    
QueryToArray(string sql)
    
Returns an array of dynamic objects based on the sql results (see Query)
    
Array
  
  

    
QueryList(string sql)
    
Returns a list of dynamic objects based on the sql results (see QueryToList)
    
System.Collections.ArrayList
  
  

    
ExecuteReturnIdentity(string sql)
    
Returns the @@IDENTITY from the executed sql
    
Integer
  
  

    
GetNewGuid()
    
Returns a new GUID
    
String
  
  

    
GetCleanGuid()
    
Returns a new guid with only alpha numeric characters
    
String
  
  

    
CleanString(string input)
    
Returns a reasonably safe sql string
    
String
  
  

    
BoolToYesNo(object input)
    
Returns a "Yes" or "No" string based on a boolean or string value
    
String
  
  

    
IsEmailAddress(string input)
    
Returns true or false if the email is valid based on the in built regex pattern
    
Boolean
  




Licence
Copyright (C) 2012 Jeremy Child
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.