Lambda表达式转SQL语句类库

Lambda表达式转SQL语句类库

/* 作者：道法自然 * 个人邮件：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));