C# DbManager.ExecuteScalar方法代码示例

        public static void AddResource(string cultureTitle, string resType, DateTime date, ResWord word, bool isConsole, string authorLogin, bool updateIfExist = true)
        {
            using (var db = new DbManager("tmresource"))
            {
                var resData = db.ExecuteScalar<string>(GetQuery("res_data", cultureTitle, word));
                var resReserve = db.ExecuteScalar<string>(GetQuery("res_reserve", cultureTitle, word)); 

                //нет ключа
                if (string.IsNullOrEmpty(resData))
                {
                    //добавляем в основную таблицу
                    db.ExecuteNonQuery(Insert("res_data", cultureTitle, word)
                                              .InColumnValue("resourceType", resType)
                                              .InColumnValue("timechanges", date)
                                              .InColumnValue("flag", 2)
                                              .InColumnValue("authorLogin", authorLogin));

                    //добавляем в резервную таблицу
                    if (isConsole)  db.ExecuteNonQuery(Insert("res_reserve", cultureTitle, word));
                }
                else if(updateIfExist)
                {
                    var isChangeResData = resData != word.ValueFrom;
                    var isChangeResReserve = resReserve != word.ValueFrom;

                    //при работе с консолью изменилось по сравнению с res_data и res_reserve, либо при работе с сайтом изменилось по сравнению с res_reserve
                    if ((isConsole && isChangeResData && isChangeResReserve) || !isConsole)
                    {
                        // изменилась нейтральная культура - выставлен флаг у всех ключей из выбранного файла с выбранным title
                        if (cultureTitle == "Neutral")
                        {
                            var update = new SqlUpdate("res_data")
                                            .Set("flag", 3)
                                            .Where("fileID", word.ResFile.FileID)
                                            .Where("title", word.Title);

                            db.ExecuteNonQuery(update);
                        }
                        // изменилась не нейтральная культура 
                        db.ExecuteNonQuery(Insert("res_data", cultureTitle, word)
                                              .InColumnValue("resourceType", resType)
                                              .InColumnValue("timechanges", date)
                                              .InColumnValue("flag", 2)
                                              .InColumnValue("authorLogin", authorLogin));

                        if (isConsole) db.ExecuteNonQuery(Update("res_reserve", cultureTitle, word));
                    }
                    else if (isChangeResData)
                    {
                        db.ExecuteNonQuery(Update("res_reserve", cultureTitle, word));
                    }
                }
            }
        }

        public static void AddResource(string cultureTitle, string resType, DateTime date, ResWord word, bool isConsole, string authorLogin)
        {
            using (var db = new DbManager("tmresource"))
            {
                var resData = db.ExecuteScalar<string>(GetQuery("res_data", cultureTitle, word));
                var resReserve = db.ExecuteScalar<string>(GetQuery("res_reserve", cultureTitle, word)); 

                
                if (string.IsNullOrEmpty(resData))
                {
                    
                    db.ExecuteNonQuery(Insert("res_data", cultureTitle, word)
                                              .InColumnValue("resourceType", resType)
                                              .InColumnValue("timechanges", date)
                                              .InColumnValue("flag", 2)
                                              .InColumnValue("authorLogin", authorLogin));

                    
                    if (isConsole)  db.ExecuteNonQuery(Insert("res_reserve", cultureTitle, word));
                }
                else
                {
                    var isChangeResData = resData != word.ValueFrom;
                    var isChangeResReserve = resReserve != word.ValueFrom;

                    
                    if ((isConsole && isChangeResData && isChangeResReserve) || !isConsole)
                    {
                        
                        if (cultureTitle == "Neutral")
                        {
                            var update = new SqlUpdate("res_data")
                                            .Set("flag", 3)
                                            .Where("fileID", word.ResFile.FileID)
                                            .Where("title", word.Title);

                            db.ExecuteNonQuery(update);
                        }
                        
                        db.ExecuteNonQuery(Insert("res_data", cultureTitle, word)
                                              .InColumnValue("resourceType", resType)
                                              .InColumnValue("timechanges", date)
                                              .InColumnValue("flag", 2)
                                              .InColumnValue("authorLogin", authorLogin));

                        if (isConsole) db.ExecuteNonQuery(Update("res_reserve", cultureTitle, word));
                    }
                    else if (isConsole && isChangeResData && !isChangeResReserve)
                    {
                        db.ExecuteNonQuery(Update("res_reserve", cultureTitle, word));
                    }
                }
            }
        }

 public string GetById(int id)
 {
     using (var db = new DbManager(DatabaseId))
     {
         return db.ExecuteScalar<string>(Query(TagsTable).Select("title").Where("id", id));
     }
 }

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

 /// <summary>
 /// Gets the Token Secret given a request or access token.
 /// </summary>
 /// <param name="token">The request or access token.</param>
 /// <returns>
 /// The secret associated with the given token.
 /// </returns>
 /// <exception cref="ArgumentException">Thrown if the secret cannot be found for the given token.</exception>
 public string GetTokenSecret(string token)
 {
     using (var db=new DbManager(_dbId))
     {
         return db.ExecuteScalar<string>(new SqlQuery(TOKEN_TABLE).Select("token_secret").Where("token", token));
     }
 }

        private bool ShowEmptyScreen()
        {
            if (CoreContext.UserManager.GetUsers(SecurityContext.CurrentAccount.ID).IsVisitor())
            {
                return false;
            }
            if (showEmptyScreen.Get("communityScreen" + TenantProvider.CurrentTenantID) != null)
            {
                return false;
            }

            var q = new SqlExp(string.Format(@"select exists(select 1 from blogs_posts where tenant = {0}) or " +
                "exists(select 1 from bookmarking_bookmark where tenant = {0}) or exists(select 1 from events_feed where tenant = {0}) or " +
                "exists(select 1 from forum_category where tenantid = {0})", CoreContext.TenantManager.GetCurrentTenant().TenantId));

            using (var db = new DbManager("community"))
            {
                var hasacitvity = db.ExecuteScalar<bool>(q);
                if (hasacitvity)
                {
                    showEmptyScreen.Insert("communityScreen" + TenantProvider.CurrentTenantID, new object(), TimeSpan.FromMinutes(30));
                }
                return !hasacitvity;
            }
        }

        public override List<UsageSpaceStatItem> GetStatData()
        {
            if (!DbRegistry.IsDatabaseRegistered(PROJECTS_DBID))
            {
                DbRegistry.RegisterDatabase(PROJECTS_DBID, ConfigurationManager.ConnectionStrings[PROJECTS_DBID]);
            }
            if (!DbRegistry.IsDatabaseRegistered(FileConstant.DatabaseId))
            {
                DbRegistry.RegisterDatabase(FileConstant.DatabaseId, ConfigurationManager.ConnectionStrings[FileConstant.DatabaseId]);
            }
            using (var db = new DbManager(FileConstant.DatabaseId))
            {
                var q = new SqlQuery("files_file f")
                    .Select("f.create_by")
                    .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", "files/my/", SqlLike.StartWith))
                    .GroupBy(1);

                var result = db.ExecuteList(q)
                    .Select(
                        r =>
                        new
                            {
                                User = CoreContext.UserManager.GetUsers(new Guid((string) r[0])),
                                Size = Convert.ToInt64(r[1])
                            })
                    .GroupBy(r => r.User).ToDictionary(g => g.Key, g => g.Sum(a => a.Size));

                q = new SqlQuery("files_file f")
                    .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("b.right_node", "files/common/");

                var common = db.ExecuteScalar<long>(q);
                if (result.ContainsKey(Constants.LostUser))
                {
                    result[Constants.LostUser] += common;
                }
                else
                {
                    result.Add(Constants.LostUser, common);
                }
                return result.Select(i => new UsageSpaceStatItem
                                              {
                                                  Name = i.Key.Equals(Constants.LostUser) ? FilesUCResource.CorporateFiles : i.Key.DisplayUserName(false),
                                                  ImgUrl = i.Key.Equals(Constants.LostUser) ? PathProvider.GetImagePath("corporatefiles_big.png") : i.Key.GetSmallPhotoURL(),
                                                  Url = i.Key.Equals(Constants.LostUser) ? PathProvider.GetFolderUrl(Global.FolderCommon, false, null) : i.Key.GetUserProfilePageURL(),
                                                  SpaceUsage = i.Value
                                              })
                    .OrderByDescending(t => t.SpaceUsage).ToList();
            }
        }

        public int GetCount()
        {
            var q = Query(TemplatesTable + " p").SelectCount();

            using (var db = new DbManager(DatabaseId))
            {
                return db.ExecuteScalar<int>(q);
            }
        }

        public bool IsExist(EntityType entityType, String tagName, DbManager db)
        {
            var q = new SqlQuery("crm_tag")
               .Select("1")
               .Where("tenant_id", TenantID)
               .Where("entity_type", (int)entityType)
               .Where("lower(title)", tagName.ToLower())
               .SetMaxResults(1);

            return db.ExecuteScalar<bool>(q);
        }

 public static bool CheckState()
 {
     using (var db = new DbManager("default"))
     {
         var last = db.ExecuteScalar<DateTime>(new SqlQuery("webstudio_index").SelectMax("last_modified"));
         var period = TextIndexCfg.ChangedCron.Period();
         if (!period.HasValue)
             return false;
         return last.Add(period.Value) > DateTime.UtcNow;
     }
 }

        public static DateTime GetLastUpdate()
        {
            using (var dbManager = new DbManager("tmresource"))
            {
                var sql = new SqlQuery("res_files");

                sql.SelectMax("LastUpdate");

                return dbManager.ExecuteScalar<DateTime>(sql);
            }
        }

        public static DateTime GetLastTimeAggregate(string key)
        {
            var q = new SqlQuery("feed_last")
                .Select("last_date")
                .Where("last_key", key);

            using (var db = new DbManager(Constants.FeedDbId))
            {
                var value = db.ExecuteScalar<DateTime>(q);
                return value != default(DateTime) ? value.AddSeconds(1) : value;
            }
        }

        public static bool GetIsMobileAppUser(Guid userId, DeviceType? type)
        {
            string userEmail = CoreContext.UserManager.GetUsers(userId).Email;

            var query = new SqlQuery("push_app_user")
                .SelectCount()
                .Where("user_email", userEmail);

            if (type.HasValue)
                query.Where("device_type", (int)type.Value);

            using (var db = new DbManager("core"))
            {
                return db.ExecuteScalar<int>(query) > 0;
            }
        }

 public void AddLink(string obj, LoginProfile profile)
 {
     using (var db = new DbManager(DbId))
     {
         using (var tx = db.BeginTransaction())
         {
             db.ExecuteScalar<int>(
                 new SqlInsert(LinkTable,true)
                     .InColumnValue("id", obj)
                     .InColumnValue("uid", profile.HashId)
                     .InColumnValue("provider", profile.Provider)
                     .InColumnValue("profile", profile.ToSerializedString())
                     .InColumnValue("linked", DateTime.UtcNow)
                 );
             tx.Commit();
         }
     }
 }