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;
        }

        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 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;
 }

        private static void AddLoginEvent(EventMessage message)
        {
            using (var db = new DbManager(messagesDbId))
            {
                var i = new SqlInsert(loginEventsTable)
                    .InColumnValue("ip", message.IP)
                    .InColumnValue("login", message.Initiator)
                    .InColumnValue("browser", message.Browser)
                    .InColumnValue("mobile", message.Mobile)
                    .InColumnValue("platform", message.Platform)
                    .InColumnValue("date", message.Date)
                    .InColumnValue("tenant_id", message.TenantId)
                    .InColumnValue("user_id", message.UserId)
                    .InColumnValue("page", message.Page)
                    .InColumnValue("action", message.Action);

                if (message.Description != null && message.Description.Any())
                {
                    i = i.InColumnValue("description", JsonConvert.SerializeObject(message.Description, new JsonSerializerSettings
                        {
                            DateTimeZoneHandling = DateTimeZoneHandling.Utc
                        }));
                }

                db.ExecuteNonQuery(i);
            }
        }

 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 PanelDbManager(IHost host, PluginParameter data)
        {
			_host = host;
			_shellData = (Shell)data[0];
			_shellSqlConn = GetShellSqlConn();

			// init StrRes to translate string
			StrRes.SetHost(_host);
            Init();			

            //绑定事件
			_dbManager = new DbManager(_host, _shellData, _shellSqlConn.type);
			_dbManager.ConnectDbCompletedToDo += DbManagerConnectDbCompletedToDo;
			_dbManager.GetDbNameCompletedToDo += DbManagerGetDbNameCompletedToDo;
			_dbManager.GetDbTableNameCompletedToDo += DbManagerGetTableNameCompletedToDo;
			_dbManager.GetColumnTypeCompletedToDo += DbManagerGetColumnTypeCompletedToDo;
			_dbManager.ExecuteReaderCompletedToDo += DbManagerExecuteReaderCompletedToDo;
			_dbManager.ExecuteNonQueryCompletedToDo += DbManagerExecuteNonQueryCompletedToDo;

			RefreshServerStatus(false);


	        if (string.IsNullOrEmpty(_shellSqlConn.type) || string.IsNullOrEmpty(_shellSqlConn.conn))
	        {
		        MessageBox.Show("shell's sqlConnection is null or space");
	        }
	        else
	        {
				//连接数据库
				_dbManager.ConnectDb(_shellSqlConn.conn);
	        }
        }