public static bool Update(Role role)
        {
            bool result = false;
            var conn = OracleDL.connect();
            OracleTransaction txn = conn.BeginTransaction(IsolationLevel.ReadCommitted);
            try
            {
                OracleCommand cmd = conn.CreateCommand();
                cmd.CommandText = "UPDATE SYSTEMROLES SET NAME = :name WHERE ID = :id";
                cmd.Parameters.Add(":name", OracleDbType.Varchar2, role.Name, ParameterDirection.Input);
                cmd.Parameters.Add(":id", OracleDbType.Int32, role.Id, ParameterDirection.Input);

                int rowsInserted = cmd.ExecuteNonQuery();
                if (rowsInserted > 0)
                {
                    cmd = conn.CreateCommand();
                    cmd.CommandText = "DELETE FROM SYSTEMROLEFUNCTIONS WHERE ROLEID = :id";
                    cmd.Parameters.Add(":id", OracleDbType.Int32, role.Id, ParameterDirection.Input);

                    int rowsdeleted = cmd.ExecuteNonQuery();
                    if (rowsdeleted > 0)
                    {
                        var roleId = role.Id;
                        var rolefunctions = (from function in role.Functions
                                             select new RoleFunctions()
                                             {
                                                 RoleId = roleId,
                                                 FunctionId = function.Id
                                             }).ToList();

                        if (RoleFunctionsDL.Save(rolefunctions, conn))
                        {
                            result = true;
                            txn.Commit();
                        }
                    }
                }

                OracleDL.close(conn);

                return result;
            }
            catch (Exception ex)
            {
                txn.Rollback();
                throw ex;
            }
        }
        public static bool Save(Role role)
        {
            bool result = false;
            var conn = OracleDL.connect();
            OracleTransaction txn = conn.BeginTransaction(IsolationLevel.ReadCommitted);
            try
            {
                OracleCommand cmd = conn.CreateCommand();
                cmd.CommandText = "INSERT INTO SYSTEMROLES(NAME) VALUES(:name) RETURNING ID INTO :id";
                cmd.Parameters.Add(":name", OracleDbType.Varchar2, role.Name, ParameterDirection.Input);

                OracleParameter outputParameter = new OracleParameter("id", OracleDbType.Int32);
                outputParameter.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(outputParameter);

                int rowsInserted = cmd.ExecuteNonQuery();
                if (rowsInserted > 0)
                {
                    var roleId = Convert.ToInt32(outputParameter.Value.ToString());
                    var rolefunctions = (from function in role.Functions
                                         select new RoleFunctions()
                                         {
                                             RoleId = roleId,
                                             FunctionId = function.Id
                                         }).ToList();

                    if(RoleFunctionsDL.Save(rolefunctions, conn))
                    {
                        result = true;
                        txn.Commit();
                    }
                }

                OracleDL.close(conn);

                return result;
            }
            catch (Exception ex)
            {
                txn.Rollback();
                throw ex;
            }
        }
        public static Response Save(Role role, string username, bool overrideApproval)
        {
            try
            {
                if (RoleDL.RoleExists(role.Name))
                {
                    return new Response
                    {
                        SuccessMsg = string.Empty,
                        ErrorMsg = string.Format("Role with name {0} already exists.", role.Name)
                    };
                }
                else
                {
                    if (!overrideApproval)
                    {
                        bool logForApproval = ApprovalConfigurationDL.RetrieveByType(StatusUtil.GetDescription(StatusUtil.ApprovalType.CreateRole)).Approve;

                        if (logForApproval)
                        {
                            Approval approvalObj = new Approval();
                            approvalObj.Type = StatusUtil.GetDescription(StatusUtil.ApprovalType.CreateRole);
                            approvalObj.Details = JsonConvert.SerializeObject(role);
                            approvalObj.Obj = JsonConvert.SerializeObject(role);
                            approvalObj.RequestedBy = username;
                            approvalObj.RequestedOn = System.DateTime.Now;
                            approvalObj.Status = StatusUtil.ApprovalStatus.Pending.ToString();

                            if (ApprovalDL.Save(approvalObj))
                            {
                                return new Response
                                {
                                    SuccessMsg = "Role successfully logged for approval",
                                    ErrorMsg = string.Empty
                                };
                            }
                            else
                            {
                                return new Response
                                {
                                    SuccessMsg = string.Empty,
                                    ErrorMsg = "Operation failed"
                                };
                            }
                        }
                        else
                        {
                            if (RoleDL.Save(role))
                            {
                                AuditTrail obj = new AuditTrail();
                                obj.Type = StatusUtil.GetDescription(StatusUtil.ApprovalType.CreateRole);
                                obj.Details = JsonConvert.SerializeObject(role);
                                obj.RequestedBy = username;
                                obj.RequestedOn = System.DateTime.Now;
                                obj.ApprovedBy = username;
                                obj.ApprovedOn = System.DateTime.Now;
                                AuditTrailDL.Save(obj);

                                return new Response
                                {
                                    SuccessMsg = "Role added successfully",
                                    ErrorMsg = string.Empty
                                };
                            }
                            else
                            {
                                return new Response
                                {
                                    SuccessMsg = string.Empty,
                                    ErrorMsg = "Operation failed"
                                };
                            }
                        }
                    }
                    else
                    {
                        if (RoleDL.Save(role))
                        {
                            return new Response
                            {
                                SuccessMsg = "Role added successfully",
                                ErrorMsg = string.Empty
                            };
                        }
                        else
                        {
                            return new Response
                            {
                                SuccessMsg = string.Empty,
                                ErrorMsg = "Operation failed"
                            };
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteError(ex);
                return new Response
                {
                    SuccessMsg = string.Empty,
                    ErrorMsg = ex.Message
                };
            }
        }