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.