C# EntitySet.Query方法代码示例

        public static IList<string> GetNeedUpgradeFiles()
        {
            var result = new List<string>();

            var currFiles = Directory.GetFiles(Application.StartupPath);
            var es = new EntitySet<QueryEntity>();
            string sql = "SELECT [Name],[FileVersion],[LastWriteTime] FROM [dbo].[sysFile] with(nolock)";
            es.Query(sql);

            foreach (var item in es)
            {
                string fileName = Path.Combine(Application.StartupPath, item.GetFieldValue<string>("Name"));
                //取本地文件信息
                var lastWriteTime = File.GetLastWriteTime(fileName);
                lastWriteTime = Convert.ToDateTime(lastWriteTime.ToString("yyyy-MM-dd HH:mm:ss"));
                FileVersionInfo version = FileVersionInfo.GetVersionInfo(fileName);
                var fileVersion = "1.0.0.0";
                if (version.FileVersion != null)
                    fileVersion = version.FileVersion;

                if (new Version(item.GetFieldValue<string>("FileVersion")) != new Version(fileVersion)
                    || item.GetFieldValue<DateTime>("LastWriteTime") != lastWriteTime)
                {
                    result.Add(item.GetFieldValue<string>("Name"));
                }
            }

            return result;
        }

        protected virtual void OnAddMenuToFavorite()
        {
            if (!AllowAddToFavorite) return;

            var es = new EntitySet<sysMyFavoriteMenu>();
            if (!es.TableIsExists()) return;

            es.Query("SELECT TOP 1 * FROM dbo.sysMyFavoriteMenu WITH(NOLOCK) WHERE UserId=:UserId and MenuId=:MenuId", Session.UserInfo.UserId, this.UniqueId);
            if (es.Count <= 0)
            {
                var obj = es.AddNew();
                obj.Iden = IdenGenerator.NewIden(obj.TableName);
                obj.MenuId = Convert.ToInt32(this.UniqueId);
                obj.UserId = Session.UserInfo.UserId;
                obj.RowNumber = 10000;
                es.SaveChanges();
            }

            MessageService.ShowMessage("菜单已经收藏至我的工作台.");

            var shell = ApplicationService.Current.MainForm as IShell;
            if (shell != null)
                shell.RefreshFavorite();

        }

 /// <summary>
 /// 
 /// </summary>
 /// <param name="username"></param>
 /// <param name="oldPassword"></param>
 /// <param name="newPassword"></param>
 /// <returns></returns>
 public override bool ChangePassword(string username, string oldPassword, string newPassword)
 {
     var users = new EntitySet<sysUser>();
     users.Query("select Iden, Password from sysUser with(nolock) where UserName=:UserName", username);
     if (users.Count <= 0 || !users.CurrentEntity.Password.Equals(oldPassword, StringComparison.InvariantCulture))
         return false;
     users.CurrentEntity.Password = newPassword;
     users.SaveChanges();
     return true;
 }

 private void TestWcfProxy()
 {
     ProgressService.Show("正在尝试连接远程服务器,请稍等");
     try
     {
         var es = new EntitySet<QueryEntity>();
         es.Query("select getdate()");
         ProgressService.Close();
         MessageService.ShowMessage("连接成功!");
     }
     catch (Exception ex)
     {
         ProgressService.Close();
         MessageService.ShowException(ex, "连接失败!");
     }
 }

        public void Save()
        {
            var es = new EntitySet<sysAppConfig>();
            es.Query("select * from sysAppConfig with(nolock) where UserId=:UserId", Session.UserInfo.UserId);
            sysAppConfig entity = null;
            if (es.Count <= 0)
            {
                entity = es.AddNew();
                entity.Iden = IdenGenerator.NewIden(entity.TableName);
            }
            else
                entity = es.First();

            entity.UserId = Session.UserInfo.UserId;
            entity.AppConfig = XmlSerializerHelper.Serialize<AppConfig>(this);
            es.SaveChanges();
        }

 /// <summary>
 /// 从数据库中加载用户的应用程序配置信息
 /// </summary>
 public void Load()
 {
     var es = new EntitySet<sysAppConfig>();
     es.Query("select * from sysAppConfig with(nolock) where UserId=:UserId", Session.UserInfo.UserId);
     if (es.Count > 0)
     {
         var str = es.CurrentEntity.GetFieldValue<string>("AppConfig");
         var obj = XmlSerializerHelper.Deserialize<AppConfig>(str);
         if (obj != null)
         {
             this.ThemeName = obj.ThemeName;
             this.ShowWelcomePage = obj.ShowWelcomePage;
             this.ShowNavigationPage = obj.ShowNavigationPage;
             this.ShowWorkSpace = obj.ShowWorkSpace;
         }
     }
 }

        private void btnOK_Click(object sender, EventArgs e)
        {
            var es = new EntitySet<sysUser>();
            es.Query("SELECT Iden,UserName,Password,IsActive,IsDeleted FROM dbo.sysUser where Iden=:UserId", Session.UserInfo.UserId);
            if (es.Count <= 0)
            {
                MessageService.ShowError("在系统中未找到用户,无法修改密码.");
                return;
            }

            var user = es.CurrentEntity;
            if (user.IsDeleted)
            {
                MessageService.ShowError("用户在系统中已被删除,无法修改密码.");
                return;
            }
            if (!user.IsActive)
            {
                MessageService.ShowError("用户在系统中已被禁用,无法修改密码.");
                return;
            }

            var oldPassword = SHA1Helper.Hash(this.txtOldPassword.Text);
            if (!user.Password.Equals(oldPassword, StringComparison.InvariantCulture))
            {
                MessageService.ShowError("原密码错误,无法修改密码.");
                this.txtOldPassword.Focus();
                return;
            }

            if (!this.txtNewPassword.Text.Equals(this.txtNewPassword2.Text, StringComparison.InvariantCulture))
            {
                MessageService.ShowError("新密码与确认新密码不一致,无法修改密码.");
                this.txtNewPassword.Focus();
                return;
            }

            user.Password = SHA1Helper.Hash(this.txtNewPassword.Text);
            es.SaveChanges();
            MessageService.ShowMessageFormatted("密码修改成功,请使用新密码登录系统.");
            this.Close();
        }

        public static void UpgradeFiles(IList<string> files, Action<string> ShowMessage)
        {
            CreateUpgradeDirectory();

            var es = new EntitySet<QueryEntity>();
            var newFileList = new List<string>();
            foreach (var item in files)
            {
                ShowMessage(string.Format("正在下载文件 {0}...", item));
                es.Query("SELECT top 1 [Name],[FileData],[LastWriteTime] FROM [sysFile] with(nolock) WHERE [Name][email protected]", item);
                if (es.Count > 0)
                {
                    var bytes = es[0].GetFieldValue<byte[]>("FileData");
                    if (bytes != null && bytes.Length > 0)
                    {
                        var fileName = Path.Combine(UpgradePath, item);
                        newFileList.Add(fileName);
                        File.WriteAllBytes(fileName, bytes);
                        File.SetLastWriteTime(fileName, es[0].GetFieldValue<DateTime>("LastWriteTime"));
                    }
                }
            }

            foreach (var newFile in newFileList)
            {
                ShowMessage(string.Format("正在更新文件 {0}...", Path.GetFileName(newFile)));
                var oldFileName = Path.Combine(Application.StartupPath, Path.GetFileName(newFile));
                var tempFileName = Path.Combine(TempPath, Path.GetFileName(newFile));
                if (File.Exists(oldFileName))
                {
                    File.Move(oldFileName, tempFileName);
                }
                File.Copy(newFile, oldFileName, true);
            }

        }

        /// <summary>
        /// 登录并设置Session的值
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <param name="message"></param>
        /// <returns></returns>
        public bool Login(string username, string password, out string message)
        {
            //TODO:客户端验证代码
            const string sql = @"
select top 1 Iden, UserName, UserFullName, Password, Email, TelephoneNo, IsActive, IsDeleted, IsSystem 
from sysUser with(nolock) 
where UserName=:UserName
";
            var users = new EntitySet<sysUser>();
            users.Query(sql, username);
            if (users.Count <= 0)
            {
                message = "用户不存在.";
                return false;
            }
            if (!users.CurrentEntity.Password.Equals(password, StringComparison.InvariantCulture))
            {
                message = "用户密码错误.";
                return false;
            }
            if (!users.CurrentEntity.IsActive)
            {
                message = "用户已被禁用.";
                return false;
            }
            if (users.CurrentEntity.IsDeleted)
            {
                message = "用户已被删除.";
                return false;
            }

            Session.UserInfo.Assign(users.CurrentEntity);

            message = string.Empty;
            return true;
        }

 /// <summary>
 /// 
 /// </summary>
 /// <param name="username"></param>
 /// <param name="password"></param>
 /// <returns></returns>
 public override bool ValidateUser(string username, string password)
 {
     var users = new EntitySet<sysUser>();
     users.Query("select top 1 Iden,Password,IsActive,IsDeleted from sysUser with(nolock) where UserName=:UserName", username);
     if (users.Count <= 0
         || !users.CurrentEntity.Password.Equals(password, StringComparison.InvariantCulture)
         || !users.CurrentEntity.IsActive
         || users.CurrentEntity.IsDeleted)
         return false;
     return true;
 }

 /// <summary>
 /// 
 /// </summary>
 /// <param name="userName"></param>
 /// <returns></returns>
 public override bool UnlockUser(string userName)
 {
     var users = new EntitySet<sysUser>();
     users.Query("select Iden,IsActive from sysUser with(nolock) where UserName=:UserName", userName);
     if (users.Count <= 0)
         return false;
     users.CurrentEntity.IsActive = true;
     users.SaveChanges();
     return true;
 }

        private void ReCreateMenuParam()
        {
            var sql = @"
SELECT FileName=b.Name,a.ClassName
FROM dbo.sysBusinessView a WITH(NOLOCK)
LEFT JOIN dbo.sysFile b WITH(NOLOCK) ON a.FileId=b.Iden
WHERE a.Iden=:Iden";

            var viewId = this.ViewModel.MainEntitySet.CurrentEntity.BusinessViewId;
            var esView = new EntitySet<QueryEntity>();
            esView.Query(sql,viewId);
            if (esView.IsEmpty()) return;

            var entity = esView.First();
            var fileName = entity.GetFieldValue<string>("FileName");
            if (fileName.IsEmpty()) return;

            var typeName = entity.GetFieldValue<string>("ClassName");
            if (typeName.IsEmpty()) return;

            var dllFile = Path.Combine(Application.StartupPath, fileName);
            if (!File.Exists(dllFile)) return;

            AppDomain ad = AppDomain.CreateDomain("sysMenuView Test DLL Marked ViewParameterAttribute");
            try
            {
                LoadAssemblyProxyObject obj = (LoadAssemblyProxyObject)ad.CreateInstanceFromAndUnwrap(LoadAssemblyProxyObject.AssemblyFileName, LoadAssemblyProxyObject.ProxyObjectTypeName);

                if (!Path.GetExtension(fileName).Equals(".dll", StringComparison.InvariantCultureIgnoreCase)
                    && !Path.GetExtension(fileName).Equals(".exe", StringComparison.InvariantCultureIgnoreCase)) return;

                obj.LoadAssembly(dllFile);
                var types = obj.GetType(typeName);
                if (types == null) return;

                var list = types.GetAllPropertyMarked<ViewParameterAttribute>();
                if (list.IsEmpty()) return;

                var deleteList = this.ViewModel.MenuParamEntitySet.Where(p => !p.Name.In(list.Select(x => x.Name)));
                foreach (var item in deleteList)
                {
                    item.Delete();
                }

                foreach (var item in list)
                {
                    var attr = item.GetCustomAttribute<ViewParameterAttribute>();

                    var param = this.ViewModel.MenuParamEntitySet.FirstOrDefault(p => p.Name == item.Name);
                    if (param == null)
                    {
                        param = this.ViewModel.MenuParamEntitySet.AddNew();
                        param.Iden = IdenGenerator.NewIden(param.IdenGroup);
                        param.MenuId = this.ViewModel.MainEntitySet.CurrentEntity.Iden;
                        param.Value = string.Empty;
                    }

                    param.Name = item.Name;
                    param.ControlType = (int)attr.ControlType;
                    param.Description = attr.Desctiption;
                    param.Category = attr.Category;
                }
                this.grvParams.RefreshData();
                this.grvParams.BestFitColumns();

            }
            finally
            {
                AppDomain.Unload(ad);
            }
        }

        private XtraReport GetCurrentReportFormat()
        {
            XtraReport report = null;
            if (CurrReportFormat == null)
            {
                return null;
            }

            if (CurrReportFormat.FormatData == null || CurrReportFormat.FormatData.Length < 1)
            {
                var es = new EntitySet<sysReportFormat>();
                es.Query("SELECT FormatData FROM dbo.sysReportFormat WITH(NOLOCK) WHERE Iden=:Iden", CurrReportFormat.Iden);
                if (es.Count > 0)
                    CurrReportFormat.FormatData = es[0].FormatData;
            }

            if (CurrReportFormat.FormatData == null || CurrReportFormat.FormatData.Length < 1)
                report = null;
            else
                report = XtraReport.FromStream(new MemoryStream(CurrReportFormat.FormatData), true);

            if (report != null)
            {
                report.DisplayName = CurrReportFormat.Name;
                report.DataSource = GetReportDataSource();
            }
            return report;
        }

        private void readXmljp(string filename)
        {
            XmlSerializer xml = new XmlSerializer(typeof(开目信息文件));
            using (var file = new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                开目信息文件 obj = (开目信息文件)xml.Deserialize(file);

                //MessageBox.Show(obj.数据.元素["产品代号"].Value);//.下级信息[0].数据[0].元素[0].标识

                // MessageBox.Show(obj.数据.元素.FirstOrDefault(p => p.标识 == "产品代号").标识);
                for (int i = 0; i < obj.数据.下级信息[0].数据.Count(); i++)
                {
                    EntitySet<bomRoting> entity = new EntitySet<bomRoting>();
                    entity.Query("select * from  bomRoting with(nolock) where 1=0");
                    bomRoting bm = entity.AddNew();
                    bm.Iden = IdenGenerator.NewIden("bomRoting");
                    bm.BomId = obj.数据.元素.FirstOrDefault(p => p.标识 == "零部组件代号").Value.Substring(0, obj.数据.元素.FirstOrDefault(p => p.标识 == "零部组件代号").Value.IndexOf("-"));
                    bm.BomChildId = string.IsNullOrEmpty(obj.数据.元素.FirstOrDefault(p => p.标识 == "零部组件代号").Value) ? string.Empty : obj.数据.元素.FirstOrDefault(p => p.标识 == "零部组件代号").Value;
                    bm.OpDep = string.IsNullOrEmpty(obj.数据.下级信息[0].数据[i].元素.FirstOrDefault(p => p.标识 == "生产部门").Value) ? string.Empty : obj.数据.下级信息[0].数据[i].元素.FirstOrDefault(p => p.标识 == "生产部门").Value;
                    bm.RotingId = string.IsNullOrEmpty(obj.数据.下级信息[0].数据[i].元素.FirstOrDefault(p => p.标识 == "工序号").Value) ? string.Empty : obj.数据.下级信息[0].数据[i].元素.FirstOrDefault(p => p.标识 == "工序号").Value;
                    bm.RotingName = string.IsNullOrEmpty(obj.数据.下级信息[0].数据[i].元素.FirstOrDefault(p => p.标识 == "工序名称").Value) ? string.Empty : obj.数据.下级信息[0].数据[i].元素.FirstOrDefault(p => p.标识 == "工序名称").Value;
                    bm.RotingDesc = string.IsNullOrEmpty(obj.数据.下级信息[0].数据[i].元素.FirstOrDefault(p => p.标识 == "工序内容").Value) ? string.Empty : obj.数据.下级信息[0].数据[i].元素.FirstOrDefault(p => p.标识 == "工序内容").Value;
                    var Production = obj.数据.下级信息[0].数据[i].元素.FirstOrDefault(p => p.标识 == "设备");
                    if (Production == null)
                    {
                        bm.Production = string.Empty;
                    }
                    else
                    {
                        bm.Production = obj.数据.下级信息[0].数据[i].元素.FirstOrDefault(p => p.标识 == "设备").Value;
                    } 
                    var RotingProduction=obj.数据.下级信息[0].数据[i].元素.FirstOrDefault(p => p.标识 == "工艺装备");
                    if(RotingProduction==null) 
                    {
                        bm.RotingProduction=string.Empty;
                    }
                    else
                    {
                        bm.RotingProduction = obj.数据.下级信息[0].数据[i].元素.FirstOrDefault(p => p.标识 == "工艺装备").Value;
                    }

                    var FZMeterial = obj.数据.下级信息[0].数据[i].元素.FirstOrDefault(p => p.标识 == "辅助材料");
                    if (FZMeterial == null)
                    {
                        bm.FZMeterial = string.Empty;
                    }
                    else
                    {
                        bm.FZMeterial = obj.数据.下级信息[0].数据[i].元素.FirstOrDefault(p => p.标识 == "辅助材料").Value;
                    } 
                   
                    var ZhunJie = obj.数据.下级信息[0].数据[i].元素.FirstOrDefault(p => p.标识 == "准结");
                    if (ZhunJie == null)
                    {
                        bm.ZhunJie =0;
                    }
                    else
                    {
                        bm.ZhunJie =Convert.ToDecimal(obj.数据.下级信息[0].数据[i].元素.FirstOrDefault(p => p.标识 == "准结").Value);
                    } 
                   var DanJian = obj.数据.下级信息[0].数据[i].元素.FirstOrDefault(p => p.标识 == "单件");
                    if (DanJian == null)
                    {
                        bm.DanJian = 0;
                    }
                    else
                    {
                        bm.DanJian = Convert.ToDecimal(obj.数据.下级信息[0].数据[i].元素.FirstOrDefault(p => p.标识 == "单件").Value);
                    } 
                    if (isExportBomRoting(bm.BomId, bm.BomChildId, bm.RotingId))
                        continue;
                    entity.SaveChanges();
                }


            }
        }

        protected override void OnInitViewParam()
        {
            base.OnInitViewParam();

            var es = new EntitySet<QueryEntity>();

            if (DataPortal.TableIsExists(es.ConnectionName, "[dbo].[sysMenuParam]"))
            {
                es.Query("SELECT Name,[Value] FROM [dbo].[sysMenuParam] with(nolock) WHERE [MenuId]=:MenuId", this.UniqueId);
                if (!es.IsEmpty())
                {
                    foreach (var entity in es)
                    {
                        this.ViewParameters[entity.GetFieldValue<string>("Name")] = entity.GetFieldValue<string>("Value");
                    }
                }
            }
        }