Copyright 2012 Jeremy Child |Support

Released under MIT Licence

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

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

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

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

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

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

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

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

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

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

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

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"

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

For Each e in employeeList

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

Next

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

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

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

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")

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.