Lambda表达式转SQL语句类库_.NET_编程开发_程序员俱乐部

/* 作者：道法自然  
 * 个人邮件：myyangbin@sina.cn
 * 2014-10-1
 */

Lambda表达式转SQL语句类库
源码下载：http://download.csdn.net/detail/xftyyyyb/8044085
一、可以达到的功能

本功能类库主要提供给代码中使用Lambda表达式，后需转成SQL的条件语句这一需求。

二、不能做的

1、本类库不能解析Linq语句；

2、不能解析SQL中的Select部分；

三、案例：

1、以Northwind数据库Customers表为例，为使问题简单，仅用部分字段演示

      /// <summary>
    /// 客户管理
    /// </summary>
    public class Customers
    {
        /// <summary>
        /// 客户ID
        /// </summary>
        public string CustomerID { get; set; }

        /// <summary>
        /// 公司名称
        /// </summary>
        public string CompanyName { get; set; }

        /// <summary>
        /// 地址
        /// </summary>
        public string Address { get; set; }
    }

2、可能会有如下方法访问数据库

        public static Customers Data_Fetch(string aiWhere, string aiOrderBy)
        {
            var cnstr = ConfigurationManager.ConnectionStrings["DemoConnectionString"].ConnectionString;
            Customers aiCustomers = new Customers();
            StringBuilder aisd = new StringBuilder();
            using (SqlConnection cn = new SqlConnection(cnstr))
            {
                #region 数据访问
                #region 查询SQL语句
                aisd.Append(" SELECT ");
                aisd.Append(" CustomerID,CompanyName,Address ");
                aisd.Append(" FROM  Customers ");
                aisd.Append(aiWhere);
                aisd.Append(aiOrderBy);
                #endregion
                cn.Open();
                using (SqlCommand cmd = new SqlCommand(aisd.ToString(), cn))
                {
                    using (SqlDataReader myDataReader = cmd.ExecuteReader())
                    {
                        if (myDataReader.HasRows)
                        {
                            myDataReader.Read();
                            aiCustomers = new Customers
                            {
                                #region  类赋值
                                CustomerID = (string)myDataReader["CustomerID"],
                                CompanyName = (string)myDataReader["CompanyName"],
                                Address = (string)myDataReader["Address"]
                                #endregion
                            };
                        }

                    };
                }

                #endregion

            }
            return aiCustomers;
        }

3、构建一个表达式方法

    public static class DbCustomersTest
    {
        /// <summary>
        /// 构建一个表达式方法
        /// </summary>
        /// <param name="aiExp">表达式</param>
        /// <returns></returns>
        public static Customers GetCustomers(Expression<Func<IQueryable<Customers>, IQueryable<Customers>>> aiExp)
        {
            AiExpConditions<Customers> expc = new AiExpConditions<Customers>();
            expc.Add(aiExp);
            return Customers.Data_Fetch(expc.Where(), expc.OrderBy());
        }

    }


 4、调用用例测试

    Customers aic = DbCustomersTest.GetCustomers(c => c.Where(o => o.CustomerID == "ALFKI"));

    Customers aic2 = DbCustomersTest.GetCustomers(c => c.Where(o => o.CustomerID.Contains("CO")).OrderBy(o=>o.CompanyName));