예제 #1
0
        public async static Task<long> Insert(Group group)
        {
            string commandText = "Insert into Groups (Id, ParentId, Name, Description) values (@id, @parentId, @name, @description)";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            if (group.Id > 0)
            {
                parameters.Add("@id", group.Id);
            } else
            {
                parameters.Add("@id", null);
            }
            parameters.Add("@parentId", @group.ParentId > 0 ? @group.ParentId : null);
            parameters.Add("@name", group.Name);
            parameters.Add("@description", group.Description);

            long results;

            using (var conn = await DataAccessBase.GetOpenAsync(DataAccessBase.QuangAuthConn))
            {

                var id = await conn.ExecuteAsync(commandText, parameters);
                results = id;
            }

            return results;
        }
예제 #2
0
        public async static Task<long> Update(Group group)
        {
            string commandText = "Update Groups set ParentId = @parentId, Name = @name, Description = @description  where Id = @id";
            var parameters = new Dictionary<string, object>();
            if (group.ParentId > 0)
            {
                parameters.Add("@parentId", group.ParentId.Value);
            } else
            {
                parameters.Add("@parentId", null);
            }
            parameters.Add("@id", group.Id);
            parameters.Add("@name", group.Name);
            parameters.Add("@description", group.Description);

            long results;

            using (var conn = await DataAccessBase.GetOpenAsync(DataAccessBase.QuangAuthConn))
            {

                var id = await conn.ExecuteAsync(commandText, parameters);
                results = id;
            }

            return results;
        }
예제 #3
0
 public int Update(Group group)
 {
     const string commandText = "Update Groups set ParentId = @parentId, Name = @name, Description = @description  where Id = @id";
     var parameters = new Dictionary<string, object>();
     var parentId = @group.ParentId;
     if ((parentId.GetValueOrDefault() <= 0 ? 0 : (parentId.HasValue ? 1 : 0)) != 0)
         parameters.Add("@parentId", @group.ParentId.Value);
     else
         parameters.Add("@parentId", null);
     parameters.Add("@id", @group.Id);
     parameters.Add("@name", @group.Name);
     parameters.Add("@description", @group.Description);
     return _database.Execute(commandText, parameters);
 }
예제 #4
0
 public int Insert(Group group)
 {
     const string commandText = "Insert into Groups (Id, ParentId, Name, Description) values (@id, @parentId, @name, @description)";
     var parameters = new Dictionary<string, object>();
     if (group.Id > 0)
         parameters.Add("@id", @group.Id);
     else
         parameters.Add("@id", null);
     int? parentId = @group.ParentId;
     parameters.Add("@parentId",
         (parentId.GetValueOrDefault() <= 0 ? 0 : (parentId.HasValue ? 1 : 0)) != 0 ? @group.ParentId : null);
     parameters.Add("@name", @group.Name);
     parameters.Add("@description", @group.Description);
     return _database.Execute(commandText, parameters);
 }
예제 #5
0
 public IDictionary<int, Group> GetAllGroups()
 {
     var dictionary1 = new Dictionary<int, Group>();
     foreach (var dictionary2 in _database.Query("" + "select g.Id, g.Name, g.Description, g.ParentId, g1.Name as ParentName " + ", sum(if(gu.UserId is not null, 1, 0)) as TotalMembers " + "from Groups g " + "left join GroupUsers gu on gu.GroupId = g.Id " + "left join Groups as g1 on g1.Id = g.ParentId " + "group  by g.Id, g.Name, g.Description, g.ParentId, ParentName " + "order by g.ParentId, g.Name"))
     {
         var group = new Group();
         group.Id = int.Parse(dictionary2["Id"]);
         group.Name = dictionary2["Name"];
         group.Description = dictionary2["Description"];
         if (!string.IsNullOrEmpty(dictionary2["ParentId"]))
         {
             group.ParentId = int.Parse(dictionary2["ParentId"]);
             group.ParentName = dictionary2["ParentName"];
         }
         if (!string.IsNullOrEmpty(dictionary2["TotalMembers"]))
             group.TotalMembers = int.Parse(dictionary2["TotalMembers"]);
         dictionary1.Add(@group.Id, group);
     }
     return dictionary1;
 }
예제 #6
0
 /// <summary>
 /// 
 /// </summary>
 /// <param name="group"></param>
 /// <returns></returns>
 public async static Task<long> Update(Group group)
 {
     return await GroupDal.Update(group);
 }
예제 #7
0
 /// <summary>
 /// 
 /// </summary>
 /// <param name="group"></param>
 /// <returns></returns>
 public async static Task<long> Insert(Group group)
 {
     return await GroupDal.Insert(group);
 }
예제 #8
0
        public async static Task<IDictionary<long, Group>> GetAllGroups()
        {
            IDictionary<long, Group> groups = new Dictionary<long, Group>();
            string commandText = "";
            commandText += "select g.Id, g.Name, g.Description, g.ParentId, g1.Name as ParentName ";
            commandText += ", sum(if(gu.UserId is not null, 1, 0)) as TotalMembers ";
            commandText += "from Groups g ";
            commandText += "left join GroupUsers gu on gu.GroupId = g.Id ";
            commandText += "left join Groups as g1 on g1.Id = g.ParentId ";
            commandText += "group  by g.Id, g.Name, g.Description, g.ParentId, ParentName ";
            commandText += "order by g.ParentId, g.Name";

            using (var conn = await DataAccessBase.GetOpenAsync(DataAccessBase.QuangAuthConn))
            {

                var rows = await conn.QueryAsync<Group>(commandText, new{ });
                foreach (var row in rows)
                {
                    var group = new Group();
                    group.Id = row.Id;
                    group.Name = row.Name;
                    group.Description = row.Description;
                    if (row.ParentId.HasValue)
                    {
                        group.ParentId = row.ParentId;
                        group.ParentName = row.ParentName;
                    }
                    if ((row.TotalMembers > 0))
                    {
                        group.TotalMembers = row.TotalMembers;
                    }

                    groups.Add(group.Id, group);
                }
            }
          

            return groups;
        }
예제 #9
0
   public Group GetOneGroup(int groupId)
   {
       Group group = null;
       List<Dictionary<string, string>> list = _database.Query("Select * from Groups where Id = @id", new Dictionary<string, object>()
 {
   {
     "@id",
     groupId
   }
 });
       if (list != null && list.Count == 1)
       {
           Dictionary<string, string> dictionary = list[0];
           group = new Group {Id = int.Parse(dictionary["Id"]), ParentId = new int?()};
           if (!string.IsNullOrEmpty(dictionary["ParentId"]))
               group.ParentId = int.Parse(dictionary["ParentId"]);
           group.Name = string.IsNullOrEmpty(dictionary["Name"]) ? null : dictionary["Name"];
           group.Description = string.IsNullOrEmpty(dictionary["Description"]) ? null : dictionary["Description"];
       }
       return group;
   }
예제 #10
0
 public IEnumerable<Group> GetPaging(int pageSize, int pageNumber, int? parentId, string keyword)
 {
     var parameters = new Dictionary<string, object>();
     string str = "select g1.*,g2.Name as ParentName from Groups g1 left join Groups g2 on g2.Id = g1.ParentId where g1.Name LIKE @param";
     parameters.Add("@param", "%" + Utils.EncodeForLike(keyword) + "%");
     if (parentId.HasValue)
     {
         int? nullable = parentId;
         if ((nullable.GetValueOrDefault() <= 0 ? 0 : (1)) != 0)
         {
             str += " and g1.ParentId = @param1";
             parameters.Add("@param1", parentId.Value);
         }
     }
     string commandText = str + " order by g1.Name limit @rowNumber, @pageSize";
     parameters.Add("@rowNumber", pageSize * pageNumber);
     parameters.Add("@pageSize", pageSize);
     var list = new List<Group>();
     foreach (Dictionary<string, string> dictionary in _database.Query(commandText, parameters))
     {
         var group = new Group();
         group.Id = int.Parse(dictionary["Id"]);
         group.Name = dictionary["Name"];
         group.ParentName = dictionary["ParentName"];
         group.Description = dictionary["Description"];
         if (!string.IsNullOrEmpty(dictionary["ParentId"]))
             group.ParentId = int.Parse(dictionary["ParentId"]);
         list.Add(group);
     }
     return list;
 }
예제 #11
0
 public IEnumerable<Group> GetGroupsByUser(int userId)
 {
     var parameters = new Dictionary<string, object>();
     const string commandText = "" + "select g.* " + "from GroupUsers gu " + "inner join Users u on u.Id = gu.UserId " + "inner join Groups g on g.Id = gu.GroupId " + "where u.Id = @userId";
     parameters.Add("@userId", userId);
     IList<Group> list = new List<Group>();
     foreach (Dictionary<string, string> dictionary in _database.Query(commandText, parameters))
     {
         var group = new Group();
         group.Id = int.Parse(dictionary["Id"]);
         group.ParentId = new int?();
         if (!string.IsNullOrEmpty(dictionary["ParentId"]))
             group.ParentId = int.Parse(dictionary["ParentId"]);
         group.Name = dictionary["Name"];
         group.Description = dictionary["Description"];
         list.Add(group);
     }
     return list;
 }