C# DbManager类代码示例

 public static DateTime GetLastDeltaIndexDate(ModuleInfo module)
 {
     using (var db = new DbManager("default"))
     {
         return db.ExecuteScalar<DateTime>(new SqlQuery("webstudio_index").Select("last_modified").Where("index_name", module.Delta));
     }
 }

        public static void SaveFeeds(IEnumerable<FeedRow> feeds, string key, DateTime value)
        {
            using (var db = new DbManager(Constants.FeedDbId))
            {
                db.ExecuteNonQuery(new SqlInsert("feed_last", true).InColumnValue("last_key", key).InColumnValue("last_date", value));
            }

            const int feedsPortionSize = 1000;
            var aggregatedDate = DateTime.UtcNow;

            var feedsPortion = new List<FeedRow>();
            foreach (var feed in feeds)
            {
                feedsPortion.Add(feed);
                if (feedsPortion.Sum(f => f.Users.Count) <= feedsPortionSize) continue;

                SaveFeedsPortion(feedsPortion, aggregatedDate);
                feedsPortion.Clear();
            }

            if (feedsPortion.Any())
            {
                SaveFeedsPortion(feedsPortion, aggregatedDate);
            }
        }

 private static int TestSqlConnection_AddNewBookthenRollback_ReturnBookInitialCount()
 {
     var dbManager = new DbManager<BooksContext>();
     var before = 0;
     dbManager.Connect(database =>
     {
         try
         {
             TransactionHelper.Begin(() =>
             {
                 before = database.Books.Count();
                 database.Books.Add(new Book
                 {
                     Name = "Test Book",
                     Isbn = "IKS9001F"
                 });
                 database.SaveChanges();
                 throw new Exception("Database offline! please try again later");
             });
         }
         catch (Exception ex)
         {
             throw new CustomException<DatabaseErrorExceptionArgs>(new DatabaseErrorExceptionArgs(ex.Message));
         }
     });
     return before;
 }

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

        public WebDomainDto AddWebDomain(string name, bool isVerified, DbManager db)
        {
            if (string.IsNullOrEmpty(name))
                throw new ArgumentNullException("name");

            if (db == null)
                throw new ArgumentNullException("db");

            var domain = GetDomain(name, db);
            if (domain != null)
                throw new DuplicateNameException(String.Format("Domain with name {0}. Already added to tenant {1}.",
                                                               domain.name, domain.tenant));

            var domainAddTime = DateTime.UtcNow.ToDbStyle();

            var addWebDomainQuery = new SqlInsert(DomainTable.name)
                .InColumnValue(DomainTable.Columns.id, 0)
                .InColumnValue(DomainTable.Columns.name, name)
                .InColumnValue(DomainTable.Columns.tenant, tenant)
                .InColumnValue(DomainTable.Columns.is_verified, isVerified)
                .InColumnValue(DomainTable.Columns.date_added, domainAddTime)
                .Identity(0, 0, true);

            var addedDomainId = db.ExecuteScalar<int>(addWebDomainQuery);
            return new WebDomainDto(addedDomainId, name, tenant, isVerified);
        }

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

        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 DnsDto CreateFreeDnsRecords(string dkimSelector, string domainCheckerPrefix, 
            string spf, DbManager db = null)
        {
            if (string.IsNullOrEmpty(dkimSelector))
                throw new ArgumentNullException("dkimSelector");

            if (string.IsNullOrEmpty(domainCheckerPrefix))
                throw new ArgumentNullException("domainCheckerPrefix");

            if (string.IsNullOrEmpty(spf))
                throw new ArgumentNullException("spf");

            string privateKey, publicKey;
            DnsChecker.DnsChecker.GenerateKeys(out privateKey, out publicKey);

            var domainCheckValue = PasswordGenerator.GenerateNewPassword(16);
            var domainCheck = domainCheckerPrefix + ": " + domainCheckValue;

            var insertValuesQuery = new SqlInsert(DnsTable.name)
                .InColumnValue(AddressTable.Columns.id, 0)
                .InColumnValue(DnsTable.Columns.user, _user)
                .InColumnValue(DnsTable.Columns.tenant, _tenant)
                .InColumnValue(DnsTable.Columns.dkim_selector, dkimSelector)
                .InColumnValue(DnsTable.Columns.dkim_private_key, privateKey)
                .InColumnValue(DnsTable.Columns.dkim_public_key, publicKey)
                .InColumnValue(DnsTable.Columns.domain_check, domainCheck)
                .InColumnValue(DnsTable.Columns.spf, spf)
                .Identity(0, 0, true);

            var dnsRecordId = NullSafeExecuteScalar<int>(db, insertValuesQuery);
            return new DnsDto(dnsRecordId, _tenant, _user, Defines.UNUSED_DNS_SETTING_DOMAIN_ID, dkimSelector, 
                privateKey, publicKey, domainCheck, spf);
        }

 public AccountController()
 {
     _dbManager = new DbManager();
     _repository = new Repository();
     var store = new UserStore<ApplicationUser>(_dbManager);
     _userManager = new UserManager<ApplicationUser>(store);
 }

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

        protected DbDao(DbManager db, int tenant)
        {
            if (db == null) throw new ArgumentNullException("db");

            this.db = db;
            Tenant = tenant;
        }