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