public int UpdateUserPermissions(UserPermissionsBO obj) { try { string sql = "dbo.fn_update_userpermissions"; int returnvalue; conn = new NpgsqlConnection(connString); conn.Open(); using (var cmd = new NpgsqlCommand(sql, conn)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("_permissionkey", NpgsqlTypes.NpgsqlDbType.Uuid, obj.PermissionKey); cmd.Parameters.AddWithValue("_userkey", NpgsqlTypes.NpgsqlDbType.Uuid, obj.UserKey); cmd.Parameters.AddWithValue("_modulename", NpgsqlTypes.NpgsqlDbType.Varchar, obj.Modulename); cmd.Parameters.AddWithValue("_fview", NpgsqlTypes.NpgsqlDbType.Smallint, obj.fView); cmd.Parameters.AddWithValue("_fnew", NpgsqlTypes.NpgsqlDbType.Smallint, obj.fNew); cmd.Parameters.AddWithValue("_fedit", NpgsqlTypes.NpgsqlDbType.Smallint, obj.fEdit); cmd.Parameters.AddWithValue("_fdelete", NpgsqlTypes.NpgsqlDbType.Smallint, obj.fDelete); returnvalue = cmd.ExecuteNonQuery(); } return(returnvalue); } catch (Exception msg) { throw msg; } finally { conn.Close(); } }
public List <UserPermissionsBO> getUserPermissionsByUserkey(string userkey) { try { string sql = "SELECT permissionkey,userkey, modulename, fview, fnew, fedit, fdelete, status, statusdate FROM dbo.user_permissions where userkey=@userkey and status=1 order by modulename asc"; List <UserPermissionsBO> permissionlist = new List <UserPermissionsBO>(); conn = new NpgsqlConnection(connString); conn.Open(); using (var cmd = new NpgsqlCommand(sql, conn)) { cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("userkey", NpgsqlTypes.NpgsqlDbType.Uuid, Guid.Parse(userkey)); var reader = cmd.ExecuteReader(); do { while (reader.Read()) { var BO = new UserPermissionsBO(); BO.PermissionKey = Guid.Parse(reader["permissionkey"].ToString()); BO.UserKey = Guid.Parse(reader["userkey"].ToString()); BO.Modulename = Utils.CustomParse <string>(reader["modulename"]); BO.fView = Utils.CustomParse <Int16>(reader["fview"]); BO.fNew = Utils.CustomParse <Int16>(reader["fnew"]); BO.fEdit = Utils.CustomParse <Int16>(reader["fedit"]); BO.fDelete = Utils.CustomParse <Int16>(reader["fdelete"]); permissionlist.Add(BO); } }while (reader.NextResult()); reader.Close(); } return(permissionlist); } catch (Exception msg) { throw msg; } finally { conn.Close(); } }
public Guid AddUserPermissions(UserPermissionsBO permission) { try { var PermissionKey = Guid.Empty; string sql = "dbo.fn_insert_userpermissions"; conn = new NpgsqlConnection(connString); conn.Open(); using (var cmd = new NpgsqlCommand(sql, conn)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("_userkey", NpgsqlTypes.NpgsqlDbType.Uuid, permission.UserKey); cmd.Parameters.AddWithValue("_modulename", NpgsqlTypes.NpgsqlDbType.Varchar, permission.Modulename); cmd.Parameters.AddWithValue("_fview", NpgsqlTypes.NpgsqlDbType.Smallint, permission.fView); cmd.Parameters.AddWithValue("_fnew", NpgsqlTypes.NpgsqlDbType.Smallint, permission.fNew); cmd.Parameters.AddWithValue("_fedit", NpgsqlTypes.NpgsqlDbType.Smallint, permission.fEdit); cmd.Parameters.AddWithValue("_fdelete", NpgsqlTypes.NpgsqlDbType.Smallint, permission.fDelete); var reader = cmd.ExecuteReader(); while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { PermissionKey = Guid.Parse(reader[i].ToString()); } } reader.Close(); } return(PermissionKey); } catch (Exception msg) { throw msg; } finally { conn.Close(); } }
public List <UserPermissionsBO> getMenus() { try { string sql = "SELECT menukey, menuname, status, statusdate FROM dbo.menu where status=1 order by menuname asc"; List <UserPermissionsBO> permissionlist = new List <UserPermissionsBO>(); conn = new NpgsqlConnection(connString); conn.Open(); using (var cmd = new NpgsqlCommand(sql, conn)) { cmd.CommandType = System.Data.CommandType.Text; var reader = cmd.ExecuteReader(); do { while (reader.Read()) { var BO = new UserPermissionsBO(); BO.Modulename = Utils.CustomParse <string>(reader["menuname"]); permissionlist.Add(BO); } }while (reader.NextResult()); reader.Close(); } return(permissionlist); } catch (Exception msg) { throw msg; } finally { conn.Close(); } }