View difference between Paste ID: zL7r6UQY and FH0fj30W

 require("CLRPackage")
 --
 import "System.Collections"
 import "System.Collections.Generic"
 import "System.Data"
 import "System.Data.SqlClient"
 --
 sqlServer = {} -- CLASS OBJECT
 --
 function sqlServer.ctor(instance, database, modeFlag) -- Constructor
 --
 -- NOTE: modeFlag is not implemented at the moment.
---       It's purpose will be to forced the connection to close
+--       It's purpose will be to force the connection to close
 --       after each transaction. Right now the connection must
 --       be opened and closed manually.
 --
 	local self = {} -- PUBLIC FUNCTIONS CONTAINER
 
     -- #PRIVATE FIELDS
 	cmd      = nil
 	conn     = nil
 	connStr  = ""
 	database = database or ""
 	dQuote   = "\""
 	errormsg = ""
 	instance = instance or ""
 	mode     = modeFlag or false
 	null     = "\"\""
 	provider = "sqlServer"
 	useTable = true
 	version  = "N/A"
 	--
 	csPattern = "Server=%s;Trusted_Connection=yes;connection timeout=30;Initial Catalog=%s"
 	--
 	-- #PRIVATE METHODS
 	--
 	function setConnStr(instance, database)
 
 		if instance ~= nil then self.instance = instance end
 
 		if database ~= nil then self.database = database end
 
 	end
 
 	function setParams(	args)
 
 		cmd.Parameters:Clear()
 
 		local ok = true
 
 		if type(args) == "table" then
 			for k, v in pairs(args)
 			do
 				if type(v) ~= "string" then v = ""..v end
 				ok, errormsg = pcall(function() cmd.Parameters:AddWithValue(k, value) end)
 			end
 		end
 
 		return ok
 
 	end
 
 	function getData(dr)
 
 		local record, index, h = {}, 0
 
 		if dr.HasRows == true then
 			repeat
 				while (dr:Read() == true)
 					do
 						h = {}
 						for i = 0, dr.FieldCount - 1
 							do
 								if useTable then h[dr:GetName(i)] = dr:GetValue(i)
 									else h[i] = dr:GetValue(i)
 								end
 							end
 						record[index] = h
 						index = index + 1
 					end
 			until (dr:NextResult() == false)
 		end
 
 		_, errormsg = pcall(function() dr:Close() end)
 
 		return record
 
 	end
 	--
 	-- #PUBLIC METHODS
 	--
 	self.isOpen = function()
 
 		if type(conn) == "userdata" and conn.State == ConnectionState.Open then
 			return true
 		end
 
 		return false
 
 	end
 
 	self.ErrorMsg = function() return errormsg end
 
 	self.Instance = function() return instance end
 
 	self.Database = function() return database end
 
 	self.ConnStr  = function() return connStr  end
 
 	self.Version  = function() return version  end
 
 	self.Provider = function() return provider end
 
 	self.setDatabase = function(d)
 
 		database = ""
 
 		if type(d) == "string" then database = d end
 
 		self.Close()
 
 	end
 
 	self.setInstance = function(i)
 
 		instance = ""
 
 		if type(i) == "string" then instance = i end
 
 		self.Close()
 
 	end
 
 	self.UseTable = function(flag)
 
 		if flag == nil then return useTable end
 
 		if type(flag) == "boolean" then useTable = flag end
 
 	end
 
 	self.Null = function(str)
 
 		if str == nil then return null end
 
 		if type(flag) == "string" then null = str end
 
 	end
 
 	function self.Open()
 
 		mode = modeFlag or mode
 
 		self.Close()
 
 		connStr = csPattern:format(instance, database)
 
 		local ok
 
 		ok, errormsg = pcall(function() conn = SqlConnection(connStr) end)
 
 		if ok ~= true then return ok end
 
 		ok, errormsg = pcall(function() conn:Open() end)
 
 		if ok then version = conn.ServerVersion
 			else version = "N/A"
 		end
 
 		return ok
 
 	end
 
 	function self.Close()
 
 		local ok
 
 		if type(conn) == "userdata" and conn.State == ConnectionState.Open then
 			ok, errormsg = pcall(function() conn:Close() end)
 		end
 
 		return ok
 
 	end
 
 	function self.execNonQuery(sql, args)
 
 		errormsg = ""
 
 		cmd = SqlCommand(sql, conn)
 
 		cmd.CommandType = CommandType.Text
 
 		local rc, ok
 
 		ok = setParams(args)
 
 		if ok == false then return -1 end
 
 		_, errormsg = pcall(function() rc = cmd:ExecuteNonQuery() end)
 
 		return rc
 
 	end
 
 	function self.execProc(sql, args)
 
 		errormsg = ""
 
 		cmd = SqlCommand(sql, conn)
 
 		cmd.CommandType = CommandType.StoredProcedure
 
 		local dr, ok
 
 		ok = setParams(args)
 
 		if ok == false then return null end
 
 		ok, errormsg = pcall(function() dr = cmd:ExecuteReader() end)
 
 		if ok then return getData(dr) end
 
 		return null
 
 	end
 
 	function self.execProcNR(sql, args)
 
 		errormsg = ""
 
 		cmd = SqlCommand(sql, conn)
 
 		cmd.CommandType = CommandType.StoredProcedure
 
 		local rc, ok
 
 		ok = setParams(args)
 
 		if ok == false then return -1 end
 
 		_, errormsg = pcall(function() rc = cmd:ExecuteNonQuery() end)
 
 		return rc
 
 	end
 
 	function self.execScalar(sql, args)
 
 		errormsg = ""
 
 		cmd = SqlCommand(sql, conn)
 
 		cmd.CommandType = CommandType.Text
 
 		local result, ok
 
 		ok = setParams(args)
 
 		if ok == false then return null end
 
 		ok, errormsg = pcall(function() result = cmd:ExecuteScalar() end)
 
 		if ok then return result end
 
 		return null
 
 	end
 
 	function self.execSelect(sql, args)
 
 		errormsg = ""
 
 		cmd = SqlCommand(sql, conn)
 
 		cmd.CommandType = CommandType.Text
 
 		local dr, ok
 
 		ok = setParams(args)
 
 		if ok then ok, errormsg = pcall(function() dr = cmd:ExecuteReader() end)
 			if ok then return getData(dr) end
 		end
 
 		return null
 
 	end
 
 	return self -- return public container to caller.
 --
 end