Lambda expression to SQL statement class library, lambdasql
/* Author: Tao nature
* Personal mail: myyangbin@sina.cn
* 2014-10-1
*/
Lambda expression to SQL statement class library
Source code download: http://download.csdn.net/detail/xftyyyyb/8044085
I. functions that can be achieved
This function class library mainly provides the need to use Lambda expressions in the Code and convert them into SQL conditional statements.
2. what cannot be done
1. This class library cannot parse the Linq statement;
2. The Select part of the SQL statement cannot be parsed;
Iii. cases:
1. Take the Northwind database MERs table as an example. To make the Problem Simple, only some fields are used for demonstration.
/// <Summary>
/// Customer Management
/// </Summary>
Public class MERs
{
/// <Summary>
/// Customer ID
/// </Summary>
Public string CustomerID {get; set ;}
/// <Summary>
/// Company name
/// </Summary>
Public string CompanyName {get; set ;}
/// <Summary>
/// Address
/// </Summary>
Public string Address {get; set ;}
}
2. The following methods may be used to access the database:
Public static MERs 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 Data Access
# Region Query SQL statements
Aisd. Append ("SELECT ");
Aisd. Append ("CustomerID, CompanyName, Address ");
Aisd. Append ("FROM MERs ");
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 MERs
{
# Region class assignment
CustomerID = (string) myDataReader ["CustomerID"],
CompanyName = (string) myDataReader ["CompanyName"],
Address = (string) myDataReader ["Address"]
# Endregion
};
}
};
}
# Endregion
}
Return aiCustomers;
}
3. Construct an expression method
Public static class DbCustomersTest
{
/// <Summary>
/// Construct an expression method
/// </Summary>
/// <Param name = "aiExp"> Expression </param>
/// <Returns> </returns>
Public static MERs getmers MERs (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. Call case Test
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 ));
Converts an SQL statement to a Lambda expression.
Reference code: UserInfo. where (u => UserInfo. where (m => m. demoName. contains ("s ")). select (k => k. id ). contains (3 ))
In c #, the SQL statement is changed to the lambda expression. When the lambda expression is a novice, it is changed to a type with t => tname.
The specific code to be converted is restricted by your context, so it cannot be written for you to run directly. But the principles are the same. Join, anonymous (select), entitiyfunction (if you use entitiy framework), or sqlmethods (if you use linq to SQL ).