Class cSQLHelper
	Private m_oConn, m_xmlDefinition
	Private m_sLastError, m_bIsConnected, m_bIsDefinitionLoaded, m_iErrorState
	Private mc_ConnectionState, mc_DatabaseState, mc_DefinitionState, mc_XSLState, mc_StateOK, mc_StateError
	
	Private Sub Class_Initialize()
		Set m_oConn=Server.CreateObject("ADODB.Connection")
		'm_oConn.ConnectionString=
		Set m_xmlDefinition=Server.CreateObject("MSXML2.FreeThreadedDOMDocument")
		m_bIsConnected=false
		m_bIsDefinitionLoaded=false
		m_iErrorState=0
		'set up constants
		mc_ConnectionState=1
		mc_DatabaseState=2
		mc_DefinitionState=4
		mc_XSLState=8
		mc_StateOK=true
		mc_StateError=false
	End Sub
	
	Private Sub Class_Terminate()
		Me.Disconnect
		Set m_xmlDefinition=Nothing
		Set m_oConn=Nothing
	End Sub
	
	'---------------------------------------------
	
	Public Function Connect()
		Dim rVal
		m_bIsConnected=false
		if len(m_oConn.ConnectionString) then
			on error resume next
				m_oConn.Open
				if err.number<>0 then
					'alert error if connection failed
					rVal=err.number
					m_sLastError="Could not connect to server (" & m_oConn.ConnectionString & ") - " & err.Description
					SetState mc_ConnectionState, mc_StateError 'bad connection
				else
					'connection open
					rVal=0
					m_bIsConnected=true
					SetState mc_ConnectionState, mc_StateOK
				end if
			on error goto 0
		else
			'error, no querysting specified
			rVal=-1
			m_sLastError="No QueryString property set. Unable to open Connection."
			SetState mc_ConnectionState,mc_StateError 'bad connection
		end if
		Connect=rVal
	End Function
	
	'---------------------------------------------
	
	Public Function Disconnect()
		if m_oConn.State = adStateOpen then
			m_oConn.Close
		end if
		m_iErrorState=0 'all OK
		m_bIsConnected=false
		Disconnect=0
	End Function
	
	'---------------------------------------------
	
	Private Function ChangeDatabase(pDatabaseName)
		Dim rVal
		if CheckState(mc_ConnectionState) then
			if m_bIsConnected then
				on error resume next
				m_oConn.Execute "USE [" & pDatabaseName & "]"
				if err.number<>0 then
					rVal=err.number
					m_sLastError="Could not set database (" & pDatabaseName & ") - " & err.Description
					SetState mc_DatabaseState, mc_StateError 'bad database
				else
					'change successful
					rVal=0
					SetState mc_DatabaseState, mc_StateOK
				end if
				on error goto 0
			else
				rVal=-2
				m_sLastError="No connection currently open"
				SetState mc_DatabaseState, mc_StateError 'bad database
			end if
		else
			rVal=-1
		end if
		ChangeDatabase=rVal
	End Function
	
	'---------------------------------------------
	
	Public Function LoadDatabaseList()
		'this will use the system tables to retrieve a list of database on the SQL Server
		Dim ndOptDB, rVal
		ChangeDatabase("master")
		if CheckState(mc_ConnectionState) and CheckState(mc_DatabaseState) then
			Dim oCmdDBs, oRecDBs
			Set oCmdDBs=Server.CreateObject("ADODB.Command")
			oCmdDBs.ActiveConnection=m_oConn
			oCmdDBs.CommandType=adCmdText
			oCmdDBs.CommandText="Select sdb.name " & _
				"FROM sysdatabases sdb " & _
				"WHERE sdb.dbid>4 " & _
				"ORDER BY so.name"
				
			Set oRecDBs=Server.CreateObject("ADODB.Recordset")
			oRecDBs.Open oCmdDBs

			Set m_xmlDefinition.documentElement=m_xmlDefinition.createElement("databases")
			Do while not oRecDBs.EOF
				Set ndOptDB=m_xmlDefinition.createElement("database")
				ndOptDB.text=oRecDBs("name").value
				m_xmlDefinition.documentElement.appendChild ndOptDB
				oRecDBs.MoveNext
			loop
			oRecDBs.Close
			
			Set oRecDBs=Nothing
			Set oCmdDBs=Nothing
			m_bIsDefinitionLoaded=true
			rVal=0
		else
			Set m_xmlDefinition.documentElement=m_xmlDefinition.createElement("error")
			m_xmlDefinition.documentElement.Text=m_sLastError
			m_bIsDefinitionLoaded=false
			rVal=-3
		end if
		LoadDatabaseList=rVal
	End Function
	
	'---------------------------------------------
	
	Public Function LoadTableList(pDatabaseName)
	
		Dim ndOptTable
		if ChangeDatabase(pDatabaseName)=0 then
			Dim oCmdTables, oRecTables
			Set oCmdTables=Server.CreateObject("ADODB.Command")
			oCmdTables.ActiveConnection=m_oConn
			oCmdTables.CommandType=adCmdText
			oCmdTables.CommandText= "SELECT table_name name " & _
				"FROM INFORMATION_SCHEMA.TABLES " & _
				"WHERE TABLE_TYPE='BASE TABLE' and table_name <> 'dtproperties' " & _
				"ORDER BY table_name"
				
			Set oRecTables=Server.CreateObject("ADODB.Recordset")
			oRecTables.Open oCmdTables

			Set m_xmlDefinition.documentElement=m_xmlDefinition.createElement("tables")
			Do while not oRecTables.EOF
				Set ndOptTable=m_xmlDefinition.createElement("table")
				ndOptTable.text=oRecTables("name").value
				m_xmlDefinition.documentElement.appendChild ndOptTable
				oRecTables.MoveNext
			loop
			oRecTables.Close
		
			Set oRecTables=Nothing
			Set oCmdTables=Nothing
			m_bIsDefinitionLoaded=true
		else
			Set m_xmlDefinition.documentElement=m_xmlDefinition.createElement("error")
			m_xmlDefinition.documentElement.Text=m_sLastError
			m_bIsDefinitionLoaded=false
		end if
		LoadTableList=0
		
	End Function
	
	'---------------------------------------------
	
	Public Function LoadProcedureList(pDatabaseName)
	
		Dim ndOptUSP
		if ChangeDatabase(pDatabaseName)=0 then
			Dim oCmdUSP, oRecUSP
			Set oCmdUSP=Server.CreateObject("ADODB.Command")
			oCmdUSP.ActiveConnection=m_oConn
			oCmdUSP.CommandType=adCmdText
			oCmdUSP.CommandText= "SELECT SPECIFIC_NAME name " & _
				"FROM INFORMATION_SCHEMA.ROUTINES " & _
				"WHERE left(SPECIFIC_NAME,4)=CASE WHEN ?=1 THEN 'usp_' ELSE left(SPECIFIC_NAME,4) END " & _
				"ORDER BY SPECIFIC_NAME"
			oCmdUSP.Parameters.Append oCmdUSP.CreateParameter("@filter",adTinyInt,adParamInput,,1)
				
			Set oRecUSP=Server.CreateObject("ADODB.Recordset")
			oRecUSP.Open oCmdUSP
			if oRecUSP.EOF then
				'if nothing is returned (they are not using a "usp_" prefix, 
				'just grab all the stored proc
				oRecUSP.Close
				oCmdUSP.Parameters("@filter").Value=0
				oRecUSP.Open oCmdUSP
			end if

			Set m_xmlDefinition.documentElement=m_xmlDefinition.createElement("procedures")
			Do while not oRecUSP.EOF
				Set ndOptUSP=m_xmlDefinition.createElement("procedure")
				ndOptUSP.text=oRecUSP("name").value
				m_xmlDefinition.documentElement.appendChild ndOptUSP
				oRecUSP.MoveNext
			loop
			oRecUSP.Close
		
			Set oRecUSP=Nothing
			Set oCmdUSP=Nothing
			m_bIsDefinitionLoaded=true
		else
			Set m_xmlDefinition.documentElement=m_xmlDefinition.createElement("error")
			m_xmlDefinition.documentElement.Text=m_sLastError
			m_bIsDefinitionLoaded=false
		end if
		LoadProcedureList=0
		
	End Function
	
	'---------------------------------------------
	
	Public Function LoadTableDefinition(pDatabaseName, pTableName)
		Dim rVal, oCmdTableDef, oRecFields, bTableExists

		ChangeDatabase(pDatabaseName)
		if CheckState(mc_ConnectionState) and CheckState(mc_DatabaseState) then
			Set oCmdTableDef=Server.CreateObject("ADODB.Command")
			Set oRecFields=Server.CreateObject("ADODB.Recordset")
			rVal=0
			bTableExists=false
		
			m_xmlDefinition.documentElement=m_xmlDefinition.createElement("table")
			m_xmlDefinition.documentElement.setAttribute "name",pTableName
			m_bIsDefinitionLoaded=false
		
			oCmdTableDef.CommandText="" & _
				"DECLARE @tablename varchar(75);" & _
				"SELECT @tablename=?;" & _
				"SELECT CASE WHEN EXISTS(SELECT * FROM information_schema.tables WHERE table_name=@tablename) THEN 1 ELSE 0 END as table_exists; " & _
				"SELECT " & _
					"c.column_name as fld_name, " & _
					"c.ordinal_position as fld_order, " & _
					"c.data_type as data_type, " & _
					"c.CHARACTER_MAXIMUM_LENGTH as data_length, " & _
					"c.NUMERIC_PRECISION as numeric_precision, " & _
					"c.NUMERIC_SCALE as numeric_scale, " & _
					"CASE is_nullable WHEN 'YES' THEN 1 ELSE 0 END is_nullable," & _
					"COLUMNPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') is_identity, " & _
					"(SELECT Count(*) " & _
						"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS  tc " & _
						"INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu " & _
						"ON    tc.TABLE_CATALOG = kcu.TABLE_CATALOG " & _
						"AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA " & _
						"AND tc.TABLE_NAME = kcu.TABLE_NAME " & _
						"AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME " & _
						"WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY' " & _
						"AND (tc.TABLE_NAME = C.table_name) " & _
						"AND (kcu.COLUMN_NAME = C.column_name) " & _
					") as is_key " & _
				"FROM  " & _
					"information_schema.columns c " & _
				"WHERE " & _
					"table_name=@tablename"

			oCmdTableDef.ActiveConnection=m_oConn
			oCmdTableDef.CommandType=adCmdText
			oCmdTableDef.Parameters.append oCmdTableDef.CreateParameter("@TableName",adVarChar,adParamInput,75,pTableName)
		
			on error resume next
			oRecFields.Open oCmdTableDef
			if err.number<>0 then
				rVal=err.number
				m_sLastError="Unable to open table definition (" & err.Description & ")"
				SetState mc_DefinitionState, mc_StateError
			else
				if oRecFields("table_exists").value=1 then bTableExists=true
			end if
			Set oRecFields=oRecFields.NextRecordset
			on error goto 0
		
			if bTableExists then
				Dim oFld
				do while not oRecFields.EOF
					Set oFld=m_xmlDefinition.createElement("field")
					oFld.setAttribute "name", oRecFields("fld_name").value
					oFld.setAttribute "data_type", oRecFields("data_type").value
					oFld.setAttribute "data_type_class", GetJobClass(oRecFields("data_type").value)
					oFld.setAttribute "data_type_ado", GetADOVar(oRecFields("data_type").value)
					if not isnull(oRecFields("data_length")) then oFld.setAttribute "data_length", oRecFields("data_length").value
					if not isnull(oRecFields("numeric_precision")) then oFld.setAttribute "numeric_precision", oRecFields("numeric_precision").value
					if not isnull(oRecFields("numeric_scale")) then oFld.setAttribute "numeric_scale", oRecFields("numeric_scale").value
					oFld.setAttribute "is_nullable", oRecFields("is_nullable").value
					oFld.setAttribute "is_identity", oRecFields("is_identity").value
					oFld.setAttribute "is_key", oRecFields("is_key").value
					m_xmlDefinition.documentElement.appendChild oFld
					oRecFields.MoveNext
				loop
				m_bIsDefinitionLoaded=true
				SetState mc_DefinitionState, mc_StateOK
			elseif rVal=0 then
				rVal=-5
				m_sLastError="Table not found (" & pTableName & ")"
				SetState mc_DefinitionState, mc_StateError
			end if
			oRecFields.close
		
			Set oRecFields=Nothing
			Set oCmdTableDef=Nothing
		else
			'error in connection or database change
			rVal=-4
		end if
		
		LoadTableDefinition=rVal
	End Function
	
	'---------------------------------------------
	
	
	Public Function LoadProcedureDefinition(pDatabaseName, pProcName)
		Dim rVal, oCmdProcDef, oRecFields, bProcExists
		
		ChangeDatabase(pDatabaseName)
		if CheckState(mc_ConnectionState) and CheckState(mc_DatabaseState) then
			Set oCmdProcDef=Server.CreateObject("ADODB.Command")
			Set oRecFields=Server.CreateObject("ADODB.Recordset")
			rVal=0
			bProcExists=false
		
			m_xmlDefinition.documentElement=m_xmlDefinition.createElement("procedure")
			m_xmlDefinition.documentElement.setAttribute "name",pProcName
			m_bIsDefinitionLoaded=false
		
			oCmdProcDef.CommandText="" & _
				"DECLARE @procedurename varchar(75);" & _
				"SELECT @procedurename=?;" & _
				"SELECT CASE WHEN EXISTS(SELECT * FROM information_schema.routines WHERE specific_name=@procedurename) THEN 1 ELSE 0 END as proc_exists;" & _
				"SELECT " & _
					"PARAMETER_NAME as [fld_name], " & _
					"ORDINAL_POSITION as [fld_order], " & _
					"DATA_TYPE [data_type], " & _
					"CHARACTER_MAXIMUM_LENGTH [data_length], " & _
					"NUMERIC_PRECISION [numeric_precision], " & _
					"NUMERIC_SCALE [numeric_scale], " & _
					"CASE WHEN PARAMETER_MODE LIKE '%OUT' THEN 1 ELSE 0 END [is_output], " & _
					"CASE is_result WHEN 'YES' THEN 1 ELSE 0 END [is_result] " & _
			"FROM INFORMATION_SCHEMA.PARAMETERS " & _
			"WHERE SPECIFIC_NAME=@procedurename"

			oCmdProcDef.ActiveConnection=m_oConn
			oCmdProcDef.CommandType=adCmdText
			oCmdProcDef.Parameters.append oCmdProcDef.CreateParameter("@ProcedureName",adVarChar,adParamInput,75,pProcName)
		
			on error resume next
			oRecFields.Open oCmdProcDef
			if err.number<>0 then
				rVal=err.number
				m_sLastError="Unable to open table definition (" & err.Description & ")"
				SetState mc_DefinitionState, mc_StateError
			else
				if oRecFields("proc_exists").value=1 then bProcExists=true
			end if
			Set oRecFields=oRecFields.NextRecordset
			on error goto 0
		
			if bProcExists then
				Dim oFld
				do while not oRecFields.EOF
					Set oFld=m_xmlDefinition.createElement("param")
					oFld.setAttribute "name", oRecFields("fld_name").value
					oFld.setAttribute "data_type", oRecFields("data_type").value
					oFld.setAttribute "data_type_class", GetJobClass(oRecFields("data_type").value)
					oFld.setAttribute "data_type_ado", GetADOVar(oRecFields("data_type").value)
					if not isnull(oRecFields("data_length")) then oFld.setAttribute "data_length", oRecFields("data_length").value
					if not isnull(oRecFields("numeric_precision")) then oFld.setAttribute "numeric_precision", oRecFields("numeric_precision").value
					if not isnull(oRecFields("numeric_scale")) then oFld.setAttribute "numeric_scale", oRecFields("numeric_scale").value
					oFld.setAttribute "is_output", oRecFields("is_output").value
					oFld.setAttribute "is_result", oRecFields("is_result").value
					m_xmlDefinition.documentElement.appendChild oFld
					oRecFields.MoveNext
				loop
				m_bIsDefinitionLoaded=true
				SetState mc_DefinitionState, mc_StateOK
			elseif rVal=0 then
				rVal=-6
				m_sLastError="Stored procedure not found (" & pProcName & ")"
				SetState mc_DefinitionState, mc_StateError
			end if
			oRecFields.close
			
		
			Set oRecFields=Nothing
			Set oCmdProcDef=Nothing
		else
			'error in connection or database
			rVal=-4
		end if
		
		LoadProcedureDefinition=rVal
	End Function
	
	'---------------------------------------------
	
	Public Function GetXML()
		if CheckState(mc_ConnectionState) and CheckState(mc_DatabaseState) and CheckState(mc_DefinitionState) then
			if m_bIsDefinitionLoaded then
				Set GetXML=m_xmlDefinition
			else
				Set m_xmlDefinition.documentElement=m_xmlDefinition.createElement("error")
				m_xmlDefinition.documentElement.Text="No definition loaded"
				Set GetXML=m_xmlDefinition
			end if
		else
			Set GetXML=m_xmlDefinition
		end if
	End Function
	
	Public Function GetStylesheetProc(pStyleSheetName)
		Dim oXSL, oXSLDoc, xslProc
		SET oXSLDoc = Server.CreateObject("MSXML2.FreeThreadedDOMDocument")
		SET oXSL = Server.CreateObject("MSXML2.XSLTemplate")
		
		if CheckState(mc_ConnectionState) and CheckState(mc_DatabaseState) and CheckState(mc_DefinitionState) then
			oXSLDoc.async=false
			oXSLDoc.load(pStyleSheetName)
			if oXSLDoc.parseError.errorCode=0 then
				Set oXSL.stylesheet=oXSLDoc
				Set xslProc = oXSL.createProcessor()
				xslProc.input = m_xmlDefinition

				SetState mc_XSLState, mc_StateOK
				Set GetStylesheetProc=xslProc
			else
				m_sLastError="Error Loading XSL (" & pStyleSheetName & " - " & oXSLDoc.parseError.reason & ")"
				SetState mc_XSLState, mc_StateError
				Set GetStylesheetProc=Nothing
			end if
		else
			SetState mc_XSLState, mc_StateError
		end if
		
		Set oXSL=Nothing
		Set oXSlDoc=Nothing
		Set xslProc=Nothing
	End Function
	
	'---------------------------------------------
	
	Public Function ApplyStyleSheet(pStyleSheetName)
		'apply stylesheet to definition and return the result of transformation
		Dim oXSLDoc
		Set oXSLDoc=Server.CreateObject("MSXML2.FreeThreadedDOMDocument")
		
		if CheckState(mc_ConnectionState) and CheckState(mc_DatabaseState) and CheckState(mc_DefinitionState) then
			oXSLDoc.async=false
			oXSLDoc.load pStyleSheetName
			if oXSLDoc.parseError.errorCode=0 then
				ApplyStyleSheet=m_xmlDefinition.transformNode(oXSLDoc)
				SetState mc_XSLState, mc_StateOK
			else
				ApplyStyleSheet=""
				m_sLastError="Error Loading XSL (" & pStyleSheetName & " - " & oXSLDoc.parseError.reason & ")"
				SetState mc_XSLState, mc_StateError
			end if
		else
			SetState mc_XSLState, mc_StateError
		end if
		Set oXSLDoc=Nothing
	End Function
	
	'---------------------------------------------
	
	Private Function CheckState(pItem)
		if (m_iErrorState AND pItem)=0 then
			CheckState = true
		else
			CheckState = false
		end if
	End Function
	Private Function SetState(pItem, pState)
		if pState=mc_StateOK then
			 m_iErrorState=0
		else
			if (m_iErrorState AND pItem)=0 then m_iErrorState=m_iErrorState + pItem
		end if
	End Function
	
	'---------------------------------------------
	
	Private Function GetJobClass(DataType)
		Dim rVal
		Select Case DataType
			case "binary"
				rVal=2	
			case "char"
				rVal=2
			case "nchar"
				rVal=2
			case "nvarchar"
				rVal=2
			case "varbinary"
				rVal=2
			case "varchar"
				rVal=2
			case "decimal"
				rVal=3
			case "float"
				rVal=3
			case else
				rVal=1
		End Select
		GetJobClass=rVal
	End Function
	
	'---------------------------------------------
	
	Private Function GetADOVar(DataType)
		Dim rVal
		Select Case DataType
			Case "bit"
				rVal="adBoolean"
			Case "tinyint"
				rVal="adTinyInt"
			Case "smallint"
				rVal="adSmallInt"
			Case "int"
				rVal="adInteger"
			Case "real"
				rVal="adSingle"
			Case "float"
				rVal="adDouble"
			Case "money"
				rVal="adCurrency"
			Case "smallmoney"
				rVal="adCurrency"
			Case "decimal"
				rVal="adDecimal"
			Case "numeric"
				rVal="adNumeric"
			Case "datetime"
				rVal="adDBTimeStamp"
			Case "smalldatetime"
				rVal="adDBTimeStamp"
			Case "varchar"
				rVal="adVarChar"
			Case "nvarchar"
				rVal="adVarchar"
			Case "text"
				rVal="adLongVarWChar"	
			Case "char"
				rVal="adChar"
		End Select
		GetADOVar=rVal
	End Function
	
	'---------------------------------------------
		
	Public Property Get ConnectionString()
		ConnectionString=m_oConn.ConnectionString
	End Property
	Public Property Let ConnectionString(pValue)
		m_oConn.ConnectionString=pValue
	End Property
	
	Public Property Get ErrorCode()
		ErrorCode=m_iErrorState
	End Property
	Public Property Get LastErrorMessage()
		LastErrorMessage=m_sLastError
	End Property

End Class