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