C# DbManager.ExecuteList方法代码示例

        public List<Tenant> GetTenants()
        {
            var result = new List<Tenant>();
            var tenants = CoreContext.TenantManager.GetTenants();

            if (useUserActivity && last != DateTime.MinValue)
            {
                using (var db = new DbManager(dbid))
                {
                    var ids = db
                        .ExecuteList(new SqlQuery("webstudio_useractivity").Select("tenantid").Where(Exp.Ge("ActivityDate", last.AddMinutes(-1))).GroupBy(1))
                        .ConvertAll(r => (int)r[0]);
                    result.AddRange(tenants.FindAll(t => ids.Contains(t.TenantId)));

                    ids = db
                        .ExecuteList(new SqlQuery("files_file").Select("tenant_id").Where(Exp.Ge("modified_on", last.AddMinutes(-1))).GroupBy(1))
                        .ConvertAll(r => (int)r[0]);
                    result.AddRange(tenants.FindAll(t => ids.Contains(t.TenantId)));
                }
            }
            else
            {
                result.AddRange(tenants);
            }
            last = DateTime.UtcNow;

            result.RemoveAll(t => t.Status != TenantStatus.Active);
            return result;
        }

        public override List<UsageSpaceStatItem> GetStatData()
        {
            if (!DbRegistry.IsDatabaseRegistered(FileConstant.DatabaseId))
            {
                DbRegistry.RegisterDatabase(FileConstant.DatabaseId, ConfigurationManager.ConnectionStrings[FileConstant.DatabaseId]);
            }

            using (var filedb = new DbManager(FileConstant.DatabaseId))            
            {
                var q = new SqlQuery("files_file f")
                    .Select("b.right_node")
                    .SelectSum("f.content_length")
                    .InnerJoin("files_folder_tree t", Exp.EqColumns("f.folder_id", "t.folder_id"))
                    .InnerJoin("files_bunch_objects b", Exp.EqColumns("t.parent_id", "b.left_node"))
                    .Where("b.tenant_id", TenantProvider.CurrentTenantID)
                    .Where(Exp.Like("b.right_node", "crm/crm_common/", SqlLike.StartWith))
                    .GroupBy(1);

                return filedb.ExecuteList(q)
                    .Select(r => new UsageSpaceStatItem
                        {

                            Name = Resources.CRMCommonResource.ProductName,
                            SpaceUsage = Convert.ToInt64(r[1]),
                            Url = VirtualPathUtility.ToAbsolute(PathProvider.StartURL())
                        })
                    .ToList();
            }        
        }

 public IPGeolocationInfo GetIPGeolocation(string ip)
 {
     try
     {
         var ipformatted = FormatIP(ip);
         using (var db = new DbManager(dbid))
         {
             var q = new SqlQuery("dbip_location")
                 .Select("ip_start", "ip_end", "country", "city", "timezone_offset", "timezone_name")
                 .Where(Exp.Le("ip_start", ipformatted))
                 .OrderBy("ip_start", false)
                 .SetMaxResults(1);
             return db
                 .ExecuteList(q)
                 .Select(r => new IPGeolocationInfo()
                 {
                     IPStart = Convert.ToString(r[0]),
                     IPEnd = Convert.ToString(r[1]),
                     Key = Convert.ToString(r[2]),
                     City = Convert.ToString(r[3]),
                     TimezoneOffset = Convert.ToDouble(r[4]),
                     TimezoneName = Convert.ToString(r[5])
                 })
                 .SingleOrDefault(i => ipformatted.CompareTo(i.IPEnd) <= 0) ??
                 IPGeolocationInfo.Default;
         }
     }
     catch (Exception error)
     {
         log.Error(error);
     }
     return IPGeolocationInfo.Default;
 }

 public static IEnumerable<int> Search(ModuleInfo module)
 {
     using (var db = new DbManager(TextIndexCfg.ConnectionStringName))
     {
         return db.ExecuteList(module.SqlQuery).ConvertAll(r => GetId(r[0]));
     }
 }

 public ReportTemplate GetTemplate(int id)
 {
     using (var db = new DbManager(DatabaseId))
     {
         return db.ExecuteList(Query(ReportTable).Select(columns).Where("id", id)).ConvertAll(ToTemplate).SingleOrDefault();
     }
 }

 public List<Subtask> GetSubtasks(int taskid)
 {
     using (var db = new DbManager(DatabaseId))
     {
         return db.ExecuteList(CreateQuery().Where("task_id", taskid)).ConvertAll(ToSubTask);
     }
 }

        public DBResourceReader(string fileName, CultureInfo culture)
        {
            language = culture.Name;
            Data = new Hashtable();

            if (language == "")
                language = "Neutral";

            using (var dbManager = new DbManager("tmresource"))
            {
                var sql = new SqlQuery("res_data")
                    .Select("res_data.title", "textValue")
                    .InnerJoin("res_files", Exp.EqColumns("res_files.id", "res_data.fileID"))
                    .LeftOuterJoin("res_cultures", Exp.EqColumns("res_cultures.title", "res_data.cultureTitle"))
                    .Where("ResName", fileName)
                    .Where("res_cultures.Title", language);

                var list = dbManager.ExecuteList(sql);

                foreach (var t in list)
                {
                    Data.Add(t[0], t[1]);
                }
            }
        }

        public override List<UsageSpaceStatItem> GetStatData()
        {
            using (var filedb = new DbManager(FileConstant.DatabaseId))
            using (var projdb = new DbManager(PROJECTS_DBID))
            {
                var q = new SqlQuery("files_file f")
                    .Select("b.right_node")
                    .SelectSum("f.content_length")
                    .InnerJoin("files_folder_tree t", Exp.EqColumns("f.folder_id", "t.folder_id"))
                    .InnerJoin("files_bunch_objects b", Exp.EqColumns("t.parent_id", "b.left_node"))
                    .Where("b.tenant_id", TenantProvider.CurrentTenantID)
                    .Where(Exp.Like("b.right_node", "projects/project/", SqlLike.StartWith))
                    .GroupBy(1);

                var sizes = filedb.ExecuteList(q)
                    .Select(r => new {ProjectId = Convert.ToInt32(((string) r[0]).Substring(17)), Size = Convert.ToInt64(r[1])})
                    .GroupBy(r => r.ProjectId)
                    .ToDictionary(g => g.Key, g => g.Sum(a => a.Size));

                q = new SqlQuery("projects_projects")
                    .Select("id", "title")
                    .Where("tenant_id", TenantProvider.CurrentTenantID)
                    .Where(Exp.In("id", sizes.Keys));

                return projdb.ExecuteList(q)
                    .Select(r => new UsageSpaceStatItem
                        {
                            Name = Convert.ToString(r[1]),
                            SpaceUsage = sizes[Convert.ToInt32(r[0])],
                            Url = String.Concat(PathProvider.BaseAbsolutePath, "projects.aspx?prjID=" + Convert.ToInt32(r[0]))
                        })
                    .OrderByDescending(i => i.SpaceUsage)
                    .ToList();
            }
        }

        public List<TimeSpend> GetByFilter(TaskFilter filter, bool isAdmin, bool checkAccess)
        {
            var query = CreateQuery();

            if (filter.Max != 0 && !filter.Max.Equals(int.MaxValue))
            {
                query.SetFirstResult((int)filter.Offset);
                query.SetMaxResults((int)filter.Max * 2);
            }

            if (!string.IsNullOrEmpty(filter.SortBy))
            {
                var sortColumns = filter.SortColumns["TimeSpend"];
                sortColumns.Remove(filter.SortBy);

                query.OrderBy("t." + filter.SortBy, filter.SortOrder);

                foreach (var sort in sortColumns.Keys)
                {
                    query.OrderBy("t." + sort, sortColumns[sort]);
                }
            }

            query = CreateQueryFilter(query, filter, isAdmin, checkAccess);

            using (var db = new DbManager(DatabaseId))
            {
                return db.ExecuteList(query).ConvertAll(ToTimeSpend);
            }
        }

        public override List<UsageSpaceStatItem> GetStatData()
        {
            if (!DbRegistry.IsDatabaseRegistered(MailDatabaseId))
            {
                DbRegistry.RegisterDatabase(MailDatabaseId, ConfigurationManager.ConnectionStrings[MailDatabaseId]);
            }

            using (var mail_db = new DbManager(MailDatabaseId))
            {
                var query = new SqlQuery("mail_attachment a")
                    .InnerJoin("mail_mail m", Exp.EqColumns("a.id_mail", "m.id"))
                    .Select("m.id_user")
                    .Select("sum(a.size) as size")
                    .Where("m.tenant", TenantProvider.CurrentTenantID)
                    .Where("a.need_remove", 0)
                    .GroupBy(1)
                    .OrderBy(2, false);

                return mail_db.ExecuteList(query)
                    .Select(r =>
                        {
                            var user_id = new Guid(Convert.ToString(r[0]));
                            var user = CoreContext.UserManager.GetUsers(user_id);
                            var item = new UsageSpaceStatItem
                                {
                                    Name = DisplayUserSettings.GetFullUserName(user, false),
                                    ImgUrl = UserPhotoManager.GetSmallPhotoURL(user.ID),
                                    Url = CommonLinkUtility.GetUserProfile(user.ID),
                                    SpaceUsage = Convert.ToInt64(r[1])
                                };
                            return item;
                        })
                    .ToList();
            }
        }

 public List<Message> GetAll()
 {
     using (var db = new DbManager(DatabaseId))
     {
         return db.ExecuteList(CreateQuery()).ConvertAll(ToMessage);
     }
 }

        public override List<SpaceUsageStatManager.UsageSpaceStatItem> GetStatData()
        {
            var data = new List<SpaceUsageStatManager.UsageSpaceStatItem>();
            
            if (!DbRegistry.IsDatabaseRegistered(FILES_DBID)) DbRegistry.RegisterDatabase(FILES_DBID, ConfigurationManager.ConnectionStrings[FILES_DBID]);
            using (var filedb = new DbManager(FILES_DBID))            
            {
                var q = new SqlQuery("files_file f")
                    .Select("b.right_node")
                    .SelectSum("f.content_length")
                    .InnerJoin("files_folder_tree t", Exp.EqColumns("f.folder_id", "t.folder_id"))
                    .InnerJoin("files_bunch_objects b", Exp.EqColumns("t.parent_id", "b.left_node"))
                    .Where("b.tenant_id", CoreContext.TenantManager.GetCurrentTenant().TenantId)
                    .Where(Exp.Like("b.right_node", "crm/crm_common/", SqlLike.StartWith))
                    .GroupBy(1);

                var size = filedb.ExecuteList(q).Select(r =>  Convert.ToInt64(r[1])).FirstOrDefault();
                
                
                    data.Add(new UsageSpaceStatItem()
                    {
                         
                        Name = Resources.CRMCommonResource.ProductName,
                        SpaceUsage = size,
                        Url = VirtualPathUtility.ToAbsolute(PathProvider.StartURL())
                    });
            }        

            return data;
        }

 public Dictionary<int, string> GetTags()
 {
     using (var db = new DbManager(DatabaseId))
     {
         return db.ExecuteList(GetTagQuery()).ToDictionary(r => Convert.ToInt32(r[0]), n => n[1].ToString().HtmlEncode());
     }
 }

        static MailQueueItemSettings()
        {
            using (var db = new DbManager(MailBoxManager.ConnectionStringName))
            {
                var imap_flags = db.ExecuteList(new SqlQuery(Dal.DbSchema.ImapFlags.table)
                                                    .Select(Dal.DbSchema.ImapFlags.Columns.folder_id,
                                                            Dal.DbSchema.ImapFlags.Columns.name,
                                                            Dal.DbSchema.ImapFlags.Columns.skip))
                                   .ConvertAll(r => new
                                       {
                                           folder_id = Convert.ToInt32(r[0]),
                                           name = (string) r[1],
                                           skip = Convert.ToBoolean(r[2])
                                       });

                SkipImapFlags = imap_flags.FindAll(i => i.skip).ConvertAll(i => i.name).ToArray();

                ImapFlags = new Dictionary<string, int>();
                imap_flags.FindAll(i => !i.skip).ForEach(i => { ImapFlags[i.name] = i.folder_id; });

                SpecialDomainFolders = new Dictionary<string, Dictionary<string, MailboxInfo>>();
                db.ExecuteList(new SqlQuery(ImapSpecialMailbox.table)
                                   .Select(ImapSpecialMailbox.Columns.server,
                                           ImapSpecialMailbox.Columns.name,
                                           ImapSpecialMailbox.Columns.folder_id,
                                           ImapSpecialMailbox.Columns.skip))
                  .ForEach(r =>
                      {
                          var server = ((string) r[0]).ToLower();
                          var name = ((string)r[1]).ToLower();
                          var mb = new MailboxInfo
                              {
                                  folder_id = Convert.ToInt32(r[2]),
                                  skip = Convert.ToBoolean(r[3])
                              };
                          if (SpecialDomainFolders.Keys.Contains(server))
                              SpecialDomainFolders[server][name] = mb;
                          else
                              SpecialDomainFolders[server] = new Dictionary<string, MailboxInfo> { { name, mb } };
                      });

                PopUnorderedDomains = db.ExecuteList(new SqlQuery(PopUnorderedDomain.table)
                                                         .Select(PopUnorderedDomain.Columns.server))
                                        .ConvertAll(r => (string) r[0])
                                        .ToArray();
            }
        }

 public Template GetByID(int id)
 {
     using (var db = new DbManager(DatabaseId))
     {
         var query = Query(TemplatesTable + " p").Select(templateColumns).Where("p.id", id);
         return db.ExecuteList(query).ConvertAll(ToTemplate).SingleOrDefault();
     }
 }