C# venuesEntities.CreateQuery方法代码示例

        public static string GetHandBook()
        {
            JObject ja = new JObject();
            try
            {
                using (venuesEntities db = new venuesEntities())
                {
                    string strSql = "SELECT tbo.BookBackgroundImg,tbo.BookCreateTIme,tbo.BookFacebookLink,tbo.BookTwitterLink,tbo.BookWeiboLink,tbo.BookWeixinLink,tbo.ID FROM tbl_book AS tbo";

                    ObjectQuery<DbDataRecord> kss = db.CreateQuery<DbDataRecord>(strSql);

                    foreach (var ks in kss)
                    {
                        string adsInfo = ADManager.GetAllAd(null);
                        JArray objAdsInfo = JArray.Parse(adsInfo);
                        ja = new JObject(
                              new JProperty("ID", ks["ID"].ToString()),
                              new JProperty("BookBackgroundImg", ks["BookBackgroundImg"].ToString()),
                              new JProperty("BookCreateTIme", ks["BookCreateTIme"].ToString()),
                              new JProperty("BookFacebookLink", ks["BookFacebookLink"].ToString()),
                              new JProperty("BookTwitterLink", ks["BookTwitterLink"].ToString()),
                              new JProperty("BookWeiboLink", ks["BookWeiboLink"].ToString()),
                              new JProperty("BookWeixinLink", ks["BookWeixinLink"].ToString()),
                              new JProperty("AdInfo", objAdsInfo)
                           );
                    };
                }
            }
            catch (Exception e)
            {
                // LogManager.addLog(KeyManager.LogTypeId_Error, KeyManager.MENUS.Menu_HandBookSetManager, "查询手册信息,error=" + e.Message, loginUserInfo);
            }
            return ja.ToString();
        }

        /// <summary>
        /// 根据用户id，删除用户权限表中所有关联关系
        /// </summary>
        /// <param name="userID"></param>
        public static string deleteAllAuthoriyByUserID(int userID, int userTypeParentId, string loginUserInfo)
        {
            using (venuesEntities db = new venuesEntities())
            {
                try
                {
                    //ObjectQuery<tbl_sys_authority> tsus = db.tbl_sys_authority.Where("[email protected] AND [email protected]", new ObjectParameter("v", userID), new ObjectParameter("u", userTypeParentId));

                    string strSql = string.Format("SELECT aa.AT_Id FROM tbl_sys_authority AS aa  WHERE aa.AT_UserId={0} AND aa.AT_UserTypeId={1}", userID, userTypeParentId);

                    ObjectQuery<DbDataRecord> results = db.CreateQuery<DbDataRecord>(strSql);
                    foreach (var item in results)
                    {
                        int deleteAT_Id = int.Parse(item["AT_Id"].ToString());
                        deleteAuthoriyByID(deleteAT_Id, loginUserInfo);
                        //tbl_sys_authority su = db.tbl_sys_authority.First<tbl_sys_authority>(a => a.AT_Id.Equals(deleteAT_Id));

                        //db.DeleteObject(su);
                    };
                    //if (db.SaveChanges() != 0)
                    //    return "成功!";
                    //else
                    //    return "不成功!";
                    return "成功!";
                }
                catch (Exception e)
                {
                    return e.Message;
                }
            }
        }

        public static string AddVenue(string loginUserInfo)
        {
            string returnStr = "";
            int newVenueID = 0;
            JObject objUser = JObject.Parse(loginUserInfo);
            try
            {
                using (venuesEntities db = new venuesEntities())
                {

                    tbl_venue tbv = new tbl_venue();
                    tbv.VenueTypeID = KeyManager.VenueTypeId_Meeting;
                    tbv.VenuePayType = "免费";
                    tbv.VenuePassword = GeneralFuction.MD5Encrypt(KeyManager.Default_Pwd);
                    tbv.VenueName = "";
                    tbv.VenueManager = "";
                    tbv.VenueLng = float.Parse("0");
                    tbv.VenueLat = float.Parse("0");
                    tbv.VenueCreateTime = DateTime.Now;
                    tbv.VenueContact = "";
                    tbv.VenueAddress = "";
                    tbv.VenueDescript = "";
                    tbv.VenueImage = "";
                    tbv.VenueStar = "";

                    tbv.Video = "";
                    tbv.LogoImg = "";
                    tbv.HomeImg ="";
                    tbv.Phone = "";
                    tbv.PostCode = "";
                    tbv.Fax = "";
                    tbv.WebSite = "" ;
                    tbv.Email = "";
                    db.AddTotbl_venue(tbv);
                    if (db.SaveChanges() != 0)
                    {
                        string strSql = "SELECT MAX(s.ID) as max FROM tbl_venue AS s";

                        ObjectQuery<DbDataRecord> resultm = db.CreateQuery<DbDataRecord>(strSql);
                        foreach (var item in resultm)
                        {
                            newVenueID = int.Parse(item["max"].ToString());
                            returnStr = item["max"].ToString();
                        }
                    }
                    else
                    {
                        returnStr = KeyManager.RetrunUnSucceed;
                    }

                }
            }
            catch (Exception e)
            {
                returnStr = e.Message;
                LogManager.addLog(KeyManager.LogTypeId_Error, KeyManager.MENUS.Menu_VenusManager, "新增场所失败，错误信息：" + e.Message, loginUserInfo);
            }

            return returnStr;
        }

        public static int GetMaxSequence()
        {
            string returnStr = "";
            int maxBookVenueSequence = 0;
            try
            {
                using (venuesEntities db = new venuesEntities())
                {
                    string strSql = "SELECT MAX(s.BookVenueSequence) as max FROM tbl_book_venue AS s";

                    ObjectQuery<DbDataRecord> resultm = db.CreateQuery<DbDataRecord>(strSql);
                    foreach (var item in resultm)
                    {
                        maxBookVenueSequence = int.Parse(item["max"].ToString());
                    }
                }
            }
            catch (Exception e)
            {
                returnStr = e.Message;
               // LogManager.addLog(KeyManager.LogTypeId_Error, KeyManager.MENUS.Menu_HandBookPublishManager, "获取当前tbl_book_venue中最大的排列数，错误信息：" + e.Message, loginUserInfo);
            }

            return maxBookVenueSequence;
        }

        public static string GetPageContentByPageId(int pageId, string loginUserInfo)
        {
            JArray ja = new JArray();
            try
            {
                using (venuesEntities db = new venuesEntities())
                {
                    string strSql = string.Format("SELECT tpc.ContentSequence,tpc.ContentValue,tpc.ID,tpc.ModuleID,tpc.PageID,tpc.TemplateID ,tm.ModuleType FROM tbl_page_content AS tpc LEFT JOIN tbl_module AS tm ON tm.ID=tpc.ModuleID WHERE tpc.PageID= {0}", pageId);

                    ObjectQuery<DbDataRecord> kss = db.CreateQuery<DbDataRecord>(strSql);
                    string path = System.Configuration.ConfigurationManager.AppSettings["FilePath"].ToString();
                    foreach (var ks in kss)
                    {
                        ja.Add(
                            new JObject(
                               new JProperty("ID", ks["ID"].ToString()),
                               new JProperty("ContentSequence", ks["ContentSequence"].ToString()),
                               new JProperty("ContentValue", ks["ContentValue"].ToString()),
                               new JProperty("FileNameServer", ks["ContentValue"].ToString()),
                               new JProperty("ModuleID", ks["ModuleID"].ToString()),
                               new JProperty("ModuleTypeID", ks["ModuleType"].ToString()),
                               new JProperty("PageID", ks["PageID"].ToString()),
                               new JProperty("TemplateID", ks["TemplateID"].ToString())
                              // new JProperty("FilePathAddress", path)
                                )
                            );
                    };
                }
            }
            catch (Exception e)
            {
                LogManager.addLog(KeyManager.LogTypeId_Error, KeyManager.MENUS.Menu_PagesManager, "根据pageId获取该页面内容信息：" + e.Message, loginUserInfo);
            }
            return ja.ToString();
        }

        public static string GetModuleType()
        {
            JArray ja = new JArray();
            try
            {
                using (venuesEntities db = new venuesEntities())
                {
                    string strSql = string.Format("SELECT d.DA_Name,d.DA_Id,d.DA_Code FROM tbl_sys_dictionary as d WHERE d.DA_ParentId={0}", KeyManager.ModuleTypeID);

                    ObjectQuery<DbDataRecord> kss = db.CreateQuery<DbDataRecord>(strSql);

                    foreach (var ks in kss)
                    {
                        ja.Add(
                            new JObject(
                               new JProperty("DA_Id", ks["DA_Id"].ToString()),
                               new JProperty("DA_Name", ks["DA_Name"].ToString()),
                               new JProperty("DA_Code", ks["DA_Code"].ToString())
                                )
                            );
                    };
                }
            }
            catch (Exception e)
            {
                //LogManager.addLog(KeyManager.LogTypeId_Error, KeyManager.MENUS.Menu_ModulesManager, "获取模块类型,错误信息：" + e.Message, loginUserInfo);
            }
            return ja.ToString();
        }

        public static string GetNewsPageByNewsID(int NewsID, string loginUserInfo)
        {
            JArray ja = new JArray();
            try
            {
                using (venuesEntities db = new venuesEntities())
                {
                    string strSql = string.Format("SELECT tn.ID,tn.NewsAuthor,tn.NewsCreateTime,tn.NewsPage,tn.NewsSequence,tn.NewsTitle,tn.UserID,tn.SmallImage FROM tbl_news AS tn WHERE tn.ID={0}", NewsID);

                    ObjectQuery<DbDataRecord> kss = db.CreateQuery<DbDataRecord>(strSql);
                    foreach (var ks in kss)
                    {
                        int pageId = int.Parse(ks["NewsPage"].ToString());

                        ja.Add(
                            new JObject(
                                new JProperty("ID", ks["ID"].ToString()),
                               new JProperty("NewsAuthor", ks["NewsAuthor"].ToString()),
                               new JProperty("NewsCreateTime", ks["NewsCreateTime"].ToString()),
                               new JProperty("NewsPage", ks["NewsPage"].ToString()),
                               new JProperty("NewsTitle", ks["NewsTitle"].ToString()),
                               new JProperty("SmallImage", ks["SmallImage"].ToString()),
                               new JProperty("UserID", ks["UserID"].ToString())
                                )
                            );
                    }
                }
            }
            catch (Exception e)
            {
                LogManager.addLog(KeyManager.LogTypeId_Error, KeyManager.MENUS.Menu_NewsManager, "根据NewsID获取该新闻页面信息,错误信息：" + e.Message, loginUserInfo);
            }
            return ja.ToString();
        }

        public static string GetAllTheme()
        {
            JArray ja = new JArray();
            try
            {
                using (venuesEntities db = new venuesEntities())
                {
                    string strSql = "SELECT tt.ID,tt.ThemeImage,tt.ThemeName,tt.Sequence FROM tbl_theme AS tt ORDER BY tt.Sequence";

                    ObjectQuery<DbDataRecord> kss = db.CreateQuery<DbDataRecord>(strSql);

                    foreach (var ks in kss)
                    {
                        ja.Add(
                            new JObject(
                               new JProperty("ID", ks["ID"].ToString()),
                               new JProperty("ThemeImage", ks["ThemeImage"].ToString()),
                               new JProperty("Sequence", ks["Sequence"].ToString()),
                               new JProperty("ThemeName", ks["ThemeName"].ToString())
                                )
                            );
                    };
                }
            }
            catch (Exception e)
            {
               // LogManager.addLog(KeyManager.LogTypeId_Error, KeyManager.MENUS.Menu_SalesManager, "查询所有主题,error=" + e.Message, loginUserInfo);
            }
            return ja.ToString();
        }

        public static string CopyTemplate(string mInfo, string loginUserInfo)
        {
            string returnStr = "";
            JObject objVenue = JObject.Parse(mInfo);
            try
            {
                using (venuesEntities db = new venuesEntities())
                {
                    tbl_template tbt = new tbl_template();
                    tbt.TemplateColor = int.Parse(objVenue["templateColor"].ToString());
                    tbt.TemplateCreateTime = DateTime.Now;
                    tbt.TemplateDescription = objVenue["templateDescription"].ToString();
                    tbt.TemplateHeight = int.Parse(objVenue["templateHeight"].ToString());
                    tbt.TemplateImage = objVenue["templateImage"].ToString();
                    tbt.TemplateName = objVenue["templateName"].ToString()+"(复制)";
                    tbt.TemplateType = int.Parse(objVenue["templateTypeID"].ToString());
                    tbt.TemplateWidth = int.Parse(objVenue["templateWidth"].ToString());
                    tbt.smallImage = objVenue["smallImage"].ToString();

                    string moduleInfo = objVenue["moduleInfo"].ToString();
                    db.AddTotbl_template(tbt);

                    if (db.SaveChanges() != 0)
                    {
                        string strSql = "SELECT MAX(s.ID) as max FROM tbl_template AS s";
                        int newTemplateID = 0;
                        ObjectQuery<DbDataRecord> resultm = db.CreateQuery<DbDataRecord>(strSql);
                        foreach (var item in resultm)
                        {
                            newTemplateID = int.Parse(item["max"].ToString());
                        }
                        if (moduleInfo != "[]")
                        {
                            returnStr = KeyManager.RetrunSucceed;

                            JArray arrModules = JArray.Parse(objVenue["moduleInfo"].ToString());
                            foreach (JObject objModule in arrModules)
                            {
                                returnStr = ModuleManager.SaveModule(newTemplateID, objModule.ToString(), loginUserInfo);
                            }
                        }
                        returnStr = KeyManager.RetrunSucceed;
                    }
                    else
                    {
                        returnStr = KeyManager.RetrunUnSucceed;
                    }
                }
            }
            catch (Exception e)
            {
                returnStr = e.Message;
                LogManager.addLog(KeyManager.LogTypeId_Error, KeyManager.MENUS.Menu_ModulesManager, "复制模板，mInfo=" + mInfo + ";错误信息：" + e.Message, loginUserInfo);
            }

            return returnStr;
        }

        public static string PublishCurrentBook( string loginUserInfo)
        {
            string returnStr = "";
            string strSql = "";
            try
            {
                using (venuesEntities db = new venuesEntities())
                {
                    //先将已发布过的手册的页面恢复为未发布的状态
                    strSql = string.Format("SELECT tp.ID AS PageID FROM tbl_page AS tp LEFT JOIN  tbl_book_venue AS tbv ON tbv.VenueID=tp.VenueID WHERE tp.PagePublish={0}", KeyManager.PagePublish_Yes);
                    ObjectQuery<DbDataRecord> list = db.CreateQuery<DbDataRecord>(strSql);
                    foreach (var item in list)
                    {
                        int pageId = int.Parse(item["PageID"].ToString());
                        returnStr = PageManager.SetPagePublish(pageId, KeyManager.PagePublish_Not, loginUserInfo);

                    }
                    db.SaveChanges();

                    //将审核通过的页面置为发布状态
                    strSql = string.Format("SELECT tp.ID AS PageID FROM tbl_page AS tp LEFT JOIN  tbl_book_venue AS tbv ON tbv.VenueID=tp.VenueID WHERE tp.PageState={0}", KeyManager.PageStatu_AuditPassID);

                    ObjectQuery<DbDataRecord> kss = db.CreateQuery<DbDataRecord>(strSql);
                    foreach (var ks in kss)
                    {
                        int pageId = int.Parse(ks["PageID"].ToString());
                        returnStr = PageManager.SetPagePublish(pageId, KeyManager.PagePublish_Yes, loginUserInfo);
                    }
                    db.SaveChanges();

                    //将所有审核通过状态置为编辑状态
                    //strSql = string.Format("SELECT tp.ID AS PageID FROM tbl_page AS tp LEFT JOIN  tbl_book_venue AS tbv ON tbv.VenueID=tp.VenueID WHERE tp.PageState={0}", KeyManager.PageStatu_AuditPassID);

                    //ObjectQuery<DbDataRecord> keds = db.CreateQuery<DbDataRecord>(strSql);
                    //foreach (var ks in keds)
                    //{
                    //    int pageId = int.Parse(ks["PageID"].ToString());
                    //    returnStr = PageManager.SetPageState(pageId, KeyManager.PageStatu_EditID, loginUserInfo);
                    //}

                    //if (returnStr == KeyManager.RetrunSucceed)
                    //{
                    //    //DeleteALLBookVenues();//如果手册发布成功，将手册用户关联表中的数据全部删除
                    //}

                }
            }
            catch (Exception e)
            {
                LogManager.addLog(KeyManager.LogTypeId_Error, KeyManager.MENUS.Menu_HandBookPublishManager, "发布当前手册,错误信息：" + e.Message, loginUserInfo);
            }
            return returnStr;
        }

 /// <summary>
 /// 获取Menu
 /// </summary>
 /// <returns></returns>
 public static string GetMenu(int curUserID, int userTypeParentId, string loginUserInfo)
 {
     JArray ja = new JArray();
     try
     {
         using (venuesEntities db = new venuesEntities())
         {
             string strSql = "";
             ObjectQuery<DbDataRecord> results = null;
             if (userTypeParentId == KeyManager.UserTypeId_BuissiensUser)
             {
                 strSql = string.Format("SELECT tsm.Menu_Id,tsm.Menu_Name,tsm.Menu_Url FROM tbl_sys_menu AS tsm WHERE tsm.Menu_Id=6  OR tsm.Menu_Id=7 OR tsm.Menu_Id=8 OR tsm.Menu_Id=10");
                  results = db.CreateQuery<DbDataRecord>(strSql);
             }
             else
             {
                  strSql = string.Format("SELECT m.Menu_Id,m.Menu_Name,m.Menu_IsMenu,m.Menu_Url FROM tbl_sys_menu as m join tbl_sys_authority as a on m.Menu_Id=a.AT_MenuId WHERE m.Menu_ParentId is NULL and a.AT_UserId={0} AND a.AT_UserTypeId={1} ORDER BY m.Menu_Id ASC", curUserID, userTypeParentId);
                  results = db.CreateQuery<DbDataRecord>(strSql);
             }
             foreach (var item in results)
             {
                 ja.Add(
                     new JObject(
                         new JProperty("Menu_Id", item["Menu_Id"].ToString()),
                         new JProperty("Menu_Name", item["Menu_Name"].ToString()),
                         new JProperty("Menu_Url", item["Menu_Url"].ToString())
                         )
                     );
             };
         }
     }
     catch (Exception e)
     {
         LogManager.addLog(KeyManager.LogTypeId_Error, KeyManager.MENUS.Menu_SystemManager, "获取用户对应的权限菜单,userID =" + curUserID + "；错误信息：" + e.Message, loginUserInfo);
     }
     //LogManager.addLog(KeyManager.LogTypeId_Option, KeyManager.MENUS.Menu_SystemManager, "获取用户对应的权限菜单,userID =" + KeyManager.CUR_USERID);
     return ja.ToString();
 }

 /// <summary>
 /// 获取最新用户id
 /// </summary>
 /// <returns></returns>
 public static int GetNewUserID()
 {
     using (venuesEntities db = new venuesEntities())
     {
         string strSql = "SELECT MAX(u.User_Id) as newUserId FROM tbl_sys_user as u";
         ObjectQuery<DbDataRecord> kss = db.CreateQuery<DbDataRecord>(strSql);
         int newUserId = 0;
         foreach (var ks in kss)
         {
             newUserId = int.Parse(ks["newUserId"].ToString());
         };
         return newUserId;
     }
 }

        public static string GetMeetingRoomByMrId(int mrID, string loginUserInfo)
        {
            JArray ja = new JArray();
            try
            {
                using (venuesEntities db = new venuesEntities())
                {
                    string strSql = string.Format("SELECT vmr.ID,vmr.MeetingImage,vmr.MRSequence,vmr.VenueID,vmr.Banquet,vmr.Boardroom,vmr.Classroom,vmr.Dimensions,vmr.FunctionRoom,vmr.Height,vmr.HollowSquare,vmr.Reception,vmr.Size,vmr.Theatre,vmr.UShape FROM tbl_venue_meetingroom AS vmr WHERE vmr.ID= {0}", mrID);

                    ObjectQuery<DbDataRecord> kss = db.CreateQuery<DbDataRecord>(strSql);

                    foreach (var ks in kss)
                    {
                        ja.Add(
                            new JObject(
                               new JProperty("ID", ks["ID"].ToString()),
                                new JProperty("MRSequence", ks["MRSequence"].ToString()),
                               new JProperty("Dimensions", ks["Dimensions"].ToString()),
                               new JProperty("FunctionRoom", ks["FunctionRoom"].ToString()),
                               new JProperty("Size", ks["Size"].ToString()),
                               new JProperty("Height", ks["Height"].ToString()),
                               new JProperty("Reception", ks["Reception"].ToString()),
                               new JProperty("Banquet", ks["Banquet"].ToString()),
                               new JProperty("Theatre", ks["Theatre"].ToString()),
                               new JProperty("Classroom", ks["Classroom"].ToString()),
                               new JProperty("UShape", ks["UShape"].ToString()),
                               new JProperty("Boardroom", ks["Boardroom"].ToString()),
                               new JProperty("HollowSquare", ks["HollowSquare"].ToString()),
                               new JProperty("MeetingImage", ks["MeetingImage"].ToString()),
                               new JProperty("VenueID", ks["VenueID"].ToString())
                                )
                            );
                    };
                }
            }
            catch (Exception e)
            {
                LogManager.addLog(KeyManager.LogTypeId_Error, KeyManager.MENUS.Menu_VenusManager, "根据条件查询会议室信息,错误信息：" + e.Message, loginUserInfo);
            }
            return ja.ToString();
        }

        public static int GetBookVenueInfoByVenueID(int venueId, string loginUserInfo)
        {
            int bookVenueID = 0;
            try
            {
                using (venuesEntities db = new venuesEntities())
                {
                    string strSql = string.Format("SELECT tbv.ID FROM tbl_book_venue AS tbv WHERE tbv.VenueID={0}", venueId);
                    ObjectQuery<DbDataRecord> kss = db.CreateQuery<DbDataRecord>(strSql);
                    foreach (var ks in kss)
                    {
                       bookVenueID=int.Parse(ks["ID"].ToString());
                    }
                }
            }
            catch (Exception e)
            {
                LogManager.addLog(KeyManager.LogTypeId_Error, KeyManager.MENUS.Menu_HandBookPublishManager, "根据场所id获取该场所与手册是否有关联关系,venueId=" + venueId + "；错误信息：" + e.Message, loginUserInfo);
            }

            return bookVenueID;
        }

        public static string GetCurHandBookInfo()
        {
            JObject ja = new JObject();
            string bookInfo = "";
            try
            {
                using (venuesEntities db = new venuesEntities())
                {

                    string strSql = "SELECT tb.AllCurBookInfo FROM tbl_book as tb";

                    ObjectQuery<DbDataRecord> resultm = db.CreateQuery<DbDataRecord>(strSql);
                    foreach (var item in resultm)
                    {
                        bookInfo = (item["AllCurBookInfo"].ToString());
                    }
                }
            }
            catch (Exception e)
            {
                //LogManager.addLog(KeyManager.LogTypeId_Error, KeyManager.MENUS.Menu_HandBookSetManager, "查询手册信息,error=" + e.Message);
            }
            return bookInfo;
        }