DB Class

Class DB_m
	Private conn
	private error_string
	
	Public Sub Class_Initialize()
		init()
	End Sub

	public sub init()
		set conn = server.createObject("ADODB.Connection")
		conn.open "Provider=SQLOLEDB.1;Data Source=[DB server];" & _
				"User ID=[account];Password=[password];Initial Catalog=[DB name];Net=dbmssocn"
	end sub
	
	Public Sub Class_Terminate()
		conn.close()
	End Sub

	Public Function useSql(sql)
		dim rsTemp

		Set rsTemp = Server.CreateObject("ADODB.Recordset")
		rsTemp.Open sql, conn, 3, 1

		if err.number = 0 then
			Set useSql = rsTemp
		else
			error_string = "無法取得資料"
			set useSql = null
		end if
	End Function

	Public Function useSqlUpdate(sql)
		dim rsTemp
		
		Set rsTemp = Server.CreateObject("ADODB.Recordset")
		rsTemp.Open sql, conn, 1, 2

		if err.number = 0 then
			Set useSqlUpdate = rsTemp
		else
			error_string = "無法更新資料"
			set useSqlUpdate = null
		end if
	End Function
	

	Public sub executeSql(sql)
		conn.execute sql

		if err.number <> 0 then
			error_string = "無法執行動作"
		end if
	End sub


	'data is a dictionary object
	Public Function insert(data, table)
		data.add "created_time", now()

		sql = "insert into " & table & " ("

		keys = data.keys()
		sep = ""
		For Each k In keys
			sql = sql & sep & k
			sep = ", "
		next
		sql = sql & ") values ("

		sep = ""
		For Each k In keys
			value = data.item(k)
			if check_int(value) = false then
				value = "N'" & format_sql_input(value) & "'"
			end if

			sql = sql & sep & value
			sep = ", "
		next
		sql = sql & ")"
		
		sql = "SET NOCOUNT ON;" & sql & ";Select @@Identity as insert_id"
		set rs = conn.execute(sql)

		insert = rs("insert_id")
	End Function

	public sub update(id_field, id, data, table)
		data.Add "updated_time", now()

		sql = "update " & table & " set "

		keys = data.keys()
		sep = ""
		For Each k In keys
			value = data.item(k)
			if check_int(value) = false then
				value = "N'" & format_sql_input(value) & "'"
			end if

			sql = sql & sep & k & " = " & value
			sep = ", "
		next

		if check_int(id) = false then
			id = "N'" & format_sql_input(id) & "'"
		end if
		sql = sql & " where " & id_field & " = " & id

		conn.execute(sql)
	end sub

	public function in_array(v, arr)
		dim result, i
		
		result = -1
		i = 0
		
		if not isNull(arr) and uBound(arr) > -1 then
			for i = 0 to uBound(arr)
				if cStr(arr(i)) = cStr(v) then
					result = i
					exit for
				end if
			next
		end if
		
		in_array = result
	end function
	
	
	public function check_int(n)
		dim digits_str(9), result, i, t
		
		result = true
		if isNull(n) or len(n) = 0 then
			result = false
		else
			for i = 0 to uBound(digits_str)
				digits_str(i) = cStr(i)
			next
		
			for i = 1 to len(n)
				t = cstr(n)
				if in_array(mid(t, i, 1), digits_str) = -1 then
					result = false
					exit for
				end if
			next
		end if
		
		check_int = result
	end function


	public function format_sql_input(input)
		dim replace_list(11), replace_list_to(11), result, temp_str, replace_index, i

		result = input
		replace_list(0) = "'" : replace_list(1) = """"
		replace_list(2) = "<" : replace_list(3) = ">"
		replace_list(4) = chr(12) & chr(15) : replace_list(5) = chr(15) & chr(12)
		replace_list(6) = chr(10) & chr(13) : replace_list(7) = chr(13) & chr(10)
		replace_list(8) = chr(10) : replace_list(9) = chr(12)
		replace_list(10) = chr(13) : replace_list(11) = chr(15)

		replace_list_to(0) = "&apos;" : replace_list_to(1) = "&quot;"
		replace_list_to(2) = "&lt;" : replace_list_to(3) = "&gt;"
		replace_list_to(4) = "<br/>" : replace_list_to(5) = "<br/>"
		replace_list_to(6) = "<br/>" : replace_list_to(7) = "<br/>"
		replace_list_to(8) = "" : replace_list_to(9) = ""
		replace_list_to(10) = "" : replace_list_to(11) = ""
		
		for i = 0 to Ubound(replace_list)
			if inStr(result, replace_list(i)) > 0 then
				result = replace(result, replace_list(i), replace_list_to(i))
			end if
		next
		
		format_sql_input = result
	end function

	function get_max_log_id()
		dim sql, result
		sql = "select max(id) as m from dbo.TWExtendLog"
		'response.write sql
		'response.End()
		set result = use_sql_pcc(sql)

		if err.number = 0 then
			get_max_log_id = cLng(result("m")) + 1
		else
			error_string = "無法取得紀錄編號"
			get_max_log_id = 0
		end if
	end function
end class