linq实例

下面是我自己项目中用到的linq

连好数据库后，在逻辑层返回数据：

1.定义一个返回泛型数据集

using System;
using System.Collections.Generic;

namespace BlueCube.BusinessLogic
{

    /// <summary>
    /// Encapsulates execution result contains whether the execution is successful and what messages the invoker will receive.
    /// </summary>
    public class ExecutionResult<T>
    {
        /// <summary>
        /// True as execution is successful. False as failed.
        /// </summary>
        public bool Success
        {
            get;
            set;
        }

        private List<string> _Messages = null;

        /// <summary>
        /// Stores message list
        /// </summary>
        public List<string> Messages
        {
            get
            {
                // Initialize message list if it is null
                if (_Messages == null)
                {
                    _Messages = new List<string>();                   
                }
                return _Messages;
            }

            set
            {
                // Clear existed message list then add new list from value
                if (_Messages != null)
                {
                    _Messages.Clear();
                    foreach (string message in value)
                    {
                        _Messages.Add(message);
                    }
                }
                else
                {
                    _Messages = value;
                }
            }
        }

        /// <summary>
        /// Encapsulates the value if there is any return value during execution
        /// </summary>
        public T ReturnValue
        {
            get;
            set;
        }
    }
}

 

2.所有异常处理

using System;

namespace BlueCube.BusinessLogic
{
    /// <summary>
    /// Encapsulates const message
    /// </summary>
    internal static class ExeMessage
    {
        /// <summary>
        /// Message: 您的问题已经提交，我们的客服人员将尽快与您联系。
        /// </summary>
        public const string ADD_VISITOR_QUERY_SUCCESSFULLY = "您的问题已经提交，我们的客服人员将尽快与您联系。";

        /// <summary>
        /// Message: 抱歉，您的提交未能成功。
        /// </summary>
        public const string ADD_VISITOR_QUERY_UNSUCCCESSFULLY = "抱歉，您的提交未能成功。";

        /// <summary>
        /// Message: 没有找到匹配的用户咨询信息，请检查您的查询条件。
        /// </summary>
        public const string NO_MATCHED_QUERY = "没有找到匹配的用户咨询信息，请检查您的查询条件。";

        /// <summary>
        /// Message: 没有找到匹配的用户信息，请检查您的查询条件。
        /// </summary>
        public const string NO_MATCHED_ID = "没有找到匹配的用户信息，请检查您的查询条件。";

        /// <summary>
        /// Message: 登录失败，请确认您的用户名或密码是正确的。
        /// </summary>
        public const string LOGIN_FAILURE = "登录失败，请确认您的用户名或密码是正确的。";

        /// <summary>
        /// Message: 用户数据错误。
        /// </summary>
        public const string NO_USER_ERRER = "用户数据错误。";

        /// <summary>
        /// Message: 没有找到信息，请检查您的查询条件。
        /// </summary>
        public const string NO_MEMORABILIA_DATA = "没有找到信息，请检查您的查询条件。";

        /// <summary>
        /// Message: 没有找到信息的编号。
        /// </summary>
        public const string NO_MEMORABILIA_ID = "没有找到信息的编号。";

        /// <summary>
        /// Message: 没有找到当前用户的个人信息。
        /// </summary>
        public const string NO_MATCHED_USER_PROFILE = "没有找到当前用户的个人信息。";

        /// <summary>
        /// Message: 未能找到匹配的项目。
        /// </summary>
        public const string NO_MATCHED_MODULE = "未能找到匹配的项目。";

        /// <summary>
        /// Message: 您的客户信息更新失败，请检查您所输入的信息或联系我们的管理员。
        /// </summary>
        public const string PROFILE_UPDATE_FAILURE = "您的客户信息更新失败，请检查您所输入的信息或联系我们的管理员。";

        /// <summary>
        /// Message: 密码未能更新，请确认您输入的原密码是正确的。
        /// </summary>
        public const string NO_MATCHED_PASSWORD = "密码未能更新，请确认您输入的原密码是正确的。";

        /// <summary>
        /// Message: 您的密码刚刚在别处被更改，请您重新提交更改。
        /// </summary>
        public const string PASSWORD_CHANGED_ALREADY = "您的密码刚刚在别处被更改，请您重新提交更改。";

        /// <summary>
        /// Message: 没有找到与当前用户匹配的项目。
        /// </summary>
        public const string NO_MATCHED_CONTRACT = "没有找到与当前用户匹配的项目。";

        /// <summary>
        /// Message: 没有找到当前大事记的匹配信息。
        /// </summary>
        public const string NO_MATCHED_MEMORABILIA = "没有找到当前大事记的匹配信息。";

        /// <summary>
        /// Message: 没有找到当前大事记的详细信息。
        /// </summary>
        public const string NO_MATCHED_MEM_DETAIL = "没有找到当前大事记的详细信息。";

        /// <summary>
        /// Message: 数据错误，请检查
        /// </summary>
        public const string NO_MATCHED_VIP_REQUETION = "数据错误，请检查。";

        /// <summary>
        /// Message: 保存失败，请确定信息无误。
        /// </summary>
        public const string NO_MATCHED_MEM_INSERT_ERRER = "保存失败，请确定信息无误。";

        /// <summary>
        /// Message: 目前没有提交咨询
        /// </summary>
        public const string NO_QUESTION = "目前没有提交咨询。";

        /// <summary>
        /// Message: 未能提交咨询，请您稍候再试。
        /// </summary>
        public const string QUESTION_CONFLICT = "未能提交咨询，请您稍候再试。";

        #region Contract Management Message

        /// <summary>
        /// Message: 选中的项目登录ID已经添加了合同，请返回合同管理页面查看。
        /// </summary>
        public const string NO_MULTI_CONTRACT = "选中的项目登录ID已经添加了合同，请返回合同管理页面查看。";

        /// <summary>
        /// Message: 新的合同未能提交成功，请确认您的信息是否准确或者与我们的管理员联系。
        /// </summary>
        public const string SUBMIT_CONTRACT_UNSUCCESSFULLY = "新的合同未能提交成功，请确认您的信息是否准确或者与我们的管理员联系。";

        /// <summary>
        /// Message: 新的合同未能提交成功，请您稍候再试。
        /// </summary>
        public const string SUBMIT_CONTRACT_CONFLICT = "新的合同未能提交成功，请您稍候再试。";

        /// <summary>
        /// Message：没有符合当前查询条件的合同。
        /// </summary>
        public const string NO_MATCHED_CONTRACT_OVERVIEW = "没有符合当前查询条件的合同";

        /// <summary>
        /// Message:未能成功删除指定的合同，请确认您的信息是否准确或者与我们的管理员联系。
        /// </summary>
        public const string DELETE_CONTRACT_UNSUCCESSFULLY = "未能成功删除指定的合同，请确认您的信息是否准确或者与我们的管理员联系。";

        /// <summary>
        /// Message: 指定合同更新失败，请确认您的信息是否准确或者与我们的管理员联系。
        /// </summary>
        public const string UPDATE_CONTRACT_UNSUCCESSFULLY = "指定合同更新失败，请确认您的信息是否准确或者与我们的管理员联系。";

        /// <summary>
        /// Message: 当前的合同暂时没有下载项。
        /// </summary>
        public const string NO_MATCHED_DOWNLOAD_FILE = "当前的合同暂时没有下载项。";

        /// <summary>
        /// Message: 未能成功添加下载文件，请您稍候再试或者与我们的管理员联系。
        /// </summary>
        public const string ATTACH_DOWNLOAD_FILE_UNSUCCESSFULLY = "未能成功添加下载文件，请您稍候再试或者与我们的管理员联系。";

        #endregion

        #region Project Slide Management

        /// <summary>
        /// Error message: 从未给任何VIP用添加过展示图片。
        /// </summary>
        public const string NO_SLIDE = "从未给任何VIP用添加过展示图片。";

        /// <summary>
        /// Error message: 当前项目暂时没有展示图片。
        /// </summary>
        public const string NO_MATCHED_SLIDES = "当前项目暂时没有展示图片。";

        /// <summary>
        /// Error message: 没有找到图片。
        /// </summary>
        public const string NO_MATCHED_SLIDE = "没有找到图片。";

        /// <summary>
        /// Error message: 展示图片操作过程中存在冲突，请稍候再试。
        /// </summary>
        public const string CONFICTIVE_CHANGING_SLIDE = "展示图片操作过程中存在冲突，请稍候再试。";

        /// <summary>
        /// Error message: 未能成功添加新的展示图片。
        /// </summary>
        public const string SUBMIT_SLIDE_UNSUCCESSFULLY = "未能成功添加新的展示图片。";

        #endregion

        /// <summary>
        /// Message: 服务器错误，请稍候再试或联系我们的管理员。
        /// </summary>
        public const string UNKNOWN_ERROR = "服务器错误，请稍候再试或联系我们的管理员。";
    }
}

 

3.返回数据：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BlueCube.DataAccess;
using System.Data.Linq;
using System.Xml.Linq;

namespace BlueCube.BusinessLogic
{
    public class AdminProcess
    {
        #region Search Admin
        /// <summary>
        ///  Admin login via login id and password
        /// </summary>
        /// <param name="loginID">loginID</param>
        /// <param name="password">password</param>
        /// <returns>True as success, false as fail and there is error message. And the return value contains admin also, if there is matched admin.</returns>
        public ExecutionResult<Admin> AdminLogin(string loginID, string password)
        {
            ExecutionResult<Admin> result = new ExecutionResult<Admin> { Success = false };
            try
            {
                if (!string.IsNullOrEmpty(loginID) && !string.IsNullOrEmpty(password))
                {
                    // check if the userNameand password
                    DVipUserDataContext adminDC = new DVipUserDataContext();

                    // Exception will be thrown, if there is no matched login id and password.
                    var adminUser =
                        (from admin in adminDC.Admins
                         where admin.LoginID.ToUpper() == loginID.ToUpper()
                         &&  admin.Password == password                        
                         && admin.Enable.HasValue
                         && admin.Enable.Value
                         select admin).Single();
                    result.ReturnValue = adminUser;
                    if (adminUser.Password.Equals(password))
                        result.Success = true;
                    else
                        result.Success = false;
                }
            }
            catch (InvalidOperationException ioEx)
            {
                // Logging
                result.Success = false;
                result.Messages = new List<string>()
                {
                    ExeMessage.LOGIN_FAILURE
                };
            }
            catch (Exception ex)
            {
                // Logging
                result.Success = false;
                result.Messages = new List<string>()
                {
                    ExeMessage.UNKNOWN_ERROR
                };
            }
            return result;
        }

        public ExecutionResult<Admin> ExistsAdmin(string loginID)
        {
            ExecutionResult<Admin> result = new ExecutionResult<Admin> { Success = false };
            try
            {
                if (!string.IsNullOrEmpty(loginID))
                {
                    //check if the loginID
                    DVipUserDataContext adminDC = new DVipUserDataContext();

                    //Exception will be thrown,if there is matched loginID
                    var adminLo =
                        (from admin in adminDC.Admins
                         where admin.LoginID.ToUpper() == loginID.ToUpper()
                         select admin).Single();
                    result.ReturnValue = adminLo;
                    result.Success = true;
                }
            }
            catch (InvalidCastException ioEx)
            {
                //Logging
                result.Success = false;
                result.Messages = new List<string>()
                {
                    ExeMessage.LOGIN_FAILURE
                };
            }
            catch (Exception ex)
            {
                //Logging
                result.Success = false;
                result.Messages = new List<string>()
                {
                    ExeMessage.UNKNOWN_ERROR
                };
            }
            return result;
        }

        /// <summary>
        /// search admin via id
        /// </summary>
        /// <param name="id">id</param>
        /// <returns>admin</returns>
        public ExecutionResult<Admin> AdminLogin(string id)
        {
            ExecutionResult<Admin> result = new ExecutionResult<Admin> { Success = false };
            try
            {
                if (!string.IsNullOrEmpty(id))
                {
                    // check if the userNameand password
                    DVipUserDataContext adminDC = new DVipUserDataContext();

                    // Exception will be thrown, if there is no matched login id and password.
                    var adminUser =
                        (from admin in adminDC.Admins
                         where admin.ID == Convert.ToInt32(id)
                         select admin).Single();
                    result.Success = true;
                    result.ReturnValue = adminUser;
                }
            }
            catch (InvalidOperationException ioEx)
            {
                // Logging
                result.Success = false;
                result.Messages = new List<string>()
                {
                    ExeMessage.LOGIN_FAILURE
                };
            }
            catch (Exception ex)
            {
                // Logging
                result.Success = false;
                result.Messages = new List<string>()
                {
                    ExeMessage.UNKNOWN_ERROR
                };
            }
            return result;
        }
        #endregion

        #region Admin List
        /// <summary>
        /// search all
        /// </summary>
        /// <returns>list</returns>
        public ExecutionResult<List<BlueCube.DataAccess.Admin>> GetAdminInfo()
        {
            ExecutionResult<List<BlueCube.DataAccess.Admin>> result = new ExecutionResult<List<BlueCube.DataAccess.Admin>>()
            {
                Success = false
            };

            try
            {
                DVipUserDataContext adminDC = new DVipUserDataContext();

                //select info for Memorabilia
                var admin =
                (from adminlist in adminDC.Admins
                 select adminlist);

                result.ReturnValue = admin.ToList();// as Table<BlueCube.DataAccess.Admin>;
                result.Success = true;
            }
            catch (InvalidOperationException inEx)
            {
                result.Messages = new List<string>()
                    {
                        ExeMessage.NO_MATCHED_MEMORABILIA
                    };
            }
            catch (Exception ex)
            {
                // Logging
                result.Messages = new List<string>()
                    {
                        ExeMessage.UNKNOWN_ERROR
                    };
            }

            return result;
        }

        /// <summary>
        /// Admin login via roletype id and loginID
        /// </summary>
        /// <param name="roletype">roletype</param>
        /// <param name="loginID">loginID</param>
        /// <returns>admin</returns>
        public ExecutionResult<List<BlueCube.DataAccess.Admin>> GetAdminInfo(int roletype, string loginID)
        {
            ExecutionResult<List<BlueCube.DataAccess.Admin>> result = new ExecutionResult<List<BlueCube.DataAccess.Admin>>()
            {
                Success = false
            };           
            try
            {
                DVipUserDataContext adminDC = new DVipUserDataContext();
                if (!string.IsNullOrEmpty(loginID))
                {
                    var adminUser =
                        (from admin in adminDC.Admins
                         where admin.LoginID.ToUpper().StartsWith(loginID.ToUpper()) && admin.Role == roletype || admin.UserName.StartsWith(loginID.ToUpper())
                         select admin);
                    result.Success = true;
                    result.ReturnValue = adminUser.ToList();
                }
                else
                {
                    var adminUsers =
                        (from admin in adminDC.Admins
                         where admin.Role == roletype
                         select admin);
                    result.ReturnValue = adminUsers.ToList();
                    result.Success = true;
                }
            }
            catch (InvalidOperationException ioEx)
            {
                // Logging
                result.Success = false;
                result.Messages = new List<string>()
                {
                    ExeMessage.LOGIN_FAILURE
                };
            }
            catch (Exception ex)
            {
                // Logging
                result.Success = false;
                result.Messages = new List<string>()
                {
                    ExeMessage.UNKNOWN_ERROR
                };
            }
            return result;
        }

        #endregion

        #region manager admin
        /// <summary>
        /// create a new administrator
        /// </summary>
        /// <param name="profile"></param>
        /// <returns></returns>
        public ExecutionResult<bool> CreateNewAdmin(Admin admin)
        {
            ExecutionResult<bool> result = new ExecutionResult<bool>()
            {
                Success = false
            };

            try
            {
                if (admin != null)
                {
                    DVipUserDataContext adminDC = new DVipUserDataContext();
                    adminDC.Admins.InsertOnSubmit(admin);
                    adminDC.SubmitChanges();

                    result.Success = true;
                }
            }
            catch (InvalidOperationException inEx)
            {
                // Logging
                result.Messages = new List<string>()
                    {
                        ExeMessage.PROFILE_UPDATE_FAILURE
                    };
            }
            catch (Exception ex)
            {
                // Logging
                result.Messages = new List<string>()
                    {
                        ExeMessage.UNKNOWN_ERROR
                    };
            }

            return result;
        }

        /// <summary>
        /// modify the administrator
        /// </summary>
        /// <param name="admin">admin</param>
        /// <returns>Admin</returns>
        public ExecutionResult<bool> UpdateAdmin(Admin admin)
        {
            ExecutionResult<bool> result = new ExecutionResult<bool>()
            {
                Success = false
            };

            try
            {
                if (admin != null)
                {
                    DVipUserDataContext adminDC = new DVipUserDataContext();
                    var update =
                        (from admins in adminDC.Admins
                         where admins.LoginID == admin.LoginID
                         select admins).Single();
                    if (!string.IsNullOrEmpty(admin.Password))
                        update.Password = admin.Password;
                    update.LoginID = admin.LoginID;
                    update.UserName = admin.UserName;
                    update.Role = admin.Role;
                    update.Enable = admin.Enable;
                    adminDC.SubmitChanges();
                    result.Success = true;
                }
            }
            catch (InvalidOperationException inEx)
            {
                // Logging
                result.Messages = new List<string>()
                    {
                        ExeMessage.PROFILE_UPDATE_FAILURE
                    };
            }
            catch (Exception ex)
            {
                // Logging
                result.Messages = new List<string>()
                    {
                        ExeMessage.UNKNOWN_ERROR
                    };
            }

            return result;
        }

        /// <summary>
        /// modify admin's enable is false when delete administrator from the id
        /// </summary>
        /// <param name="id">id</param>
        /// <returns>idDelete</returns>
        public ExecutionResult<bool> DeleteAdmin(string id,bool statu)
        {
            ExecutionResult<bool> result = new ExecutionResult<bool>()
            {
                Success = false
            };

            try
            {
                DVipUserDataContext adminDC = new DVipUserDataContext();
                try
                {
                    var adminid =
                        (from admin in adminDC.Admins
                         where admin.ID == Convert.ToInt32(id)
                         select admin).Single();
                    adminid.Enable = statu;
                    adminDC.SubmitChanges();

                    result.Success = true;
                }
                catch (InvalidOperationException inEx)
                {
                    // Logging
                    result.Messages = new List<string>()
                    {
                        ExeMessage.NO_MATCHED_PASSWORD
                    };
                }
                catch (ChangeConflictException conflict)
                {
                    // Deal with change confilct. Don't change the value which is already changed in database, and return warning message.
                    foreach (ObjectChangeConflict occ in adminDC.ChangeConflicts)
                    {
                        occ.Resolve(RefreshMode.OverwriteCurrentValues);
                    }
                    result.Messages = new List<string>()
                    {
                        ExeMessage.PASSWORD_CHANGED_ALREADY
                    };
                }
            }
            catch (Exception ex)
            {
                result.Messages = new List<string>()
                    {
                        ExeMessage.UNKNOWN_ERROR
                    };
            }

            return result;
        }
        #endregion

        #region pageIndex
       
        /// <summary>
        /// Search fit Query items with Queryfilter
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="pageSize">pageSize</param>
        /// <param name="pageIndex">pageIndex,the frist pageIndex is 0</param>
        /// <param name="pageCount">pageCount</param>
        /// <returns>Query item list</returns>
        public ExecutionResult<List<Admin>> GetAdminQuery(QueryAdmin filter, int pageSize, int pageIndex, out int pageCount)
        {
            pageCount = 0;

            ExecutionResult<List<Admin>> result = new ExecutionResult<List<Admin>>()
            {
                Success = false,
                ReturnValue = null
            };
            try
            {
                DVisitorDataContext dv = new DVisitorDataContext();
               
                string sqlQuery = BuildSql(filter);

                //Get filter Count
                pageCount = dv.ExecuteQuery<Admin>(sqlQuery).Count();

                // If page size is less or equal than 0 or page index less or equal than -1, there is no item will be returned.
                if (!(pageSize <= 0 || pageIndex <= -1))
                {
                    // Order record by descending and pick up
                    var queries = dv.ExecuteQuery<Admin>(sqlQuery)
                        .OrderByDescending(a => a.CreatedDate)
                        .Skip(pageSize * pageIndex)
                        .Take(pageSize);
                    result.ReturnValue = queries.ToList();
                    result.Success = true;

                }
            }
            catch (InvalidOperationException invalid)
            {
                result.Success = false;
                result.Messages = new List<string>()
                {                   
                    ExeMessage.NO_MATCHED_QUERY
                };
            }
            catch (Exception ex)
            {
                result.Success = false;
                result.Messages = new List<string>()
                {                   
                    ExeMessage.UNKNOWN_ERROR
                };

            }

            return result;

        }

        /// <summary>
        /// Generate sql query with query filter
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        private string BuildSql(QueryAdmin filter)
        {
            System.Text.StringBuilder sqlBuilder =
                new System.Text.StringBuilder(
                    "SELECT [ID], [LoginID], [Password], [UserName],[Role], [CreatedDate], [Enable] FROM [Admin] b WHERE 1=1 and LOGINID not in ('admin')");

            if (filter != null)
            {
                if (!string.IsNullOrEmpty(filter.A_LoginID))
                {
                    sqlBuilder.AppendFormat(" AND [LoginID] LIKE N'%{0}%'", filter.A_LoginID);
                }

                if (!string.IsNullOrEmpty(filter.A_UserName))
                {
                    sqlBuilder.AppendFormat(" AND [UserName] LIKE N'%{0}%'", filter.A_UserName);
                }

                if (filter.A_Role.HasValue)
                {
                    if (filter.A_Role.Value == 3)
                        sqlBuilder.AppendFormat("", filter.A_Role);
                    else
                        sqlBuilder.AppendFormat(" AND [Role] = {0}", filter.A_Role);
                }
            }

            sqlBuilder.Append(" ORDER BY [CreatedDate] DESC");
            return sqlBuilder.ToString();
        }
    }

    /// <summary>
    /// Query filter contains user name, resgion, programme category id, programme id and time span.
    /// </summary>
    ///
    [Serializable]
    public class QueryAdmin
    {
        public int? A_ID
        {
            get;
            set;
        }

        public string A_LoginID
        {
            get;
            set;
        }

        public string A_UserName
        {
            get;
            set;
        }

        public int? A_Role
        {
            get;
            set;
        }

        public DateTime? A_CreatedDate
        {
            get;
            set;
        }
    }
        #endregion
}

4.实际页面应用

/// <summary>
        /// loginID and password enter the Administrator page
        /// </summary>
        /// <param name="loginID">loginID</param>
        /// <param name="Password">Password</param>
        /// <returns>bool</returns>
        protected void AdminLogin(string loginID, string Password)
        {
            AdminProcess adminUP = new AdminProcess();
            AdminEntity admin = new AdminEntity();
            ExecutionResult<BlueCube.DataAccess.Admin> adminInfo = adminUP.AdminLogin(loginID, Password);
            if (adminInfo.Success)
            {
                admin.ID = adminInfo.ReturnValue.ID;
                admin.LoginID = adminInfo.ReturnValue.LoginID;
                admin.UserName = adminInfo.ReturnValue.UserName;
                admin.Role = adminInfo.ReturnValue.Role;
                admin.CreatedDate = adminInfo.ReturnValue.CreatedDate;
                admin.Enable = Convert.ToBoolean(adminInfo.ReturnValue.Enable);
                Session["adminInfo"] = admin;
                Session.Timeout = 120;
                if (FormsAuthentication.Authenticate("Admin", "Admin"))
                {
                    if (Request.QueryString["returnUrl"] != null)
                    {
                        FormsAuthentication.RedirectFromLoginPage("Admin", false);
                    }
                    else
                    {
                        FormsAuthentication.SetAuthCookie("Admin", false);
                        if (admin.Role == 1)
                            Response.Redirect("UserVIPAdmin.aspx");
                        else
                            Response.Redirect("Administrator.aspx");
                    }
                }
                else
                {
                    if (adminInfo.Messages.Count > 0)
                    {
                        Common.ShowMessage(this, adminInfo.Messages[0]);
                    }
                }
            }
            else
            {
                Common.ShowMessage(this, "用户名和密码错误，或不可用！");
                return;
            }
        }

        protected void btnEnter_Click(object sender, EventArgs e)
        {
            AdminLogin(this.txtAdminName.Text, this.txtPassword.Text);
        }