根据petshop的习惯,他们还是把sql语句适时拆开,然后写成静态的字段,具体看下,
我就重点研究了Produce的GetProductsBySearch功能:
private
const
string
SQL_SELECT_PRODUCTS_BY_SEARCH1
=
"
SELECT ProductId, Name, Descn, Product.Image, Product.CategoryId FROM Product WHERE ((
"
;
private const string SQL_SELECT_PRODUCTS_BY_SEARCH2 = " LOWER(Name) LIKE '%' + {0} + '%' OR LOWER(CategoryId) LIKE '%' + {0} + '%' " ;
private const string SQL_SELECT_PRODUCTS_BY_SEARCH3 = " ) OR ( " ;
private const string SQL_SELECT_PRODUCTS_BY_SEARCH4 = " )) " ;
private const string SQL_SELECT_PRODUCT = " SELECT Product.ProductId, Product.Name, Product.Descn, Product.Image, Product.CategoryId FROM Product WHERE Product.ProductId = @ProductId " ;
private const string PARM_KEYWORD = " @Keyword "
private const string SQL_SELECT_PRODUCTS_BY_SEARCH2 = " LOWER(Name) LIKE '%' + {0} + '%' OR LOWER(CategoryId) LIKE '%' + {0} + '%' " ;
private const string SQL_SELECT_PRODUCTS_BY_SEARCH3 = " ) OR ( " ;
private const string SQL_SELECT_PRODUCTS_BY_SEARCH4 = " )) " ;
private const string SQL_SELECT_PRODUCT = " SELECT Product.ProductId, Product.Name, Product.Descn, Product.Image, Product.CategoryId FROM Product WHERE Product.ProductId = @ProductId " ;
private const string PARM_KEYWORD = " @Keyword "
为什么SQL_SELECT_PRODUCTS_BY_SEARCH1和SQL_SELECT_PRODUCTS_BY_SEARCH2不写在一起呢?假设我们把
他们写在一起也是无法实现一个查询的功能的因为少了两个")"。这就是他们的高明之处,这种方法到处可见。
原因是这样的:因为我的查询是传来一个string[]是数组,所以呢SQL_SELECT_PRODUCTS_BY_SEARCH2带有参数{0},
具体还是来看下代码吧,貌似自己也说不太清楚。
1
public
IList
<
ProductInfo
>
GetProductsBySearch(
string
[] keywords) {
2
3 IList < ProductInfo > productsBySearch = new List < ProductInfo > ();
4
5 int numKeywords = keywords.Length;
6
7 // Create a new query string
8 StringBuilder sql = new StringBuilder(SQL_SELECT_PRODUCTS_BY_SEARCH1);
9
10 // Add each keyword to the query
11 for ( int i = 0 ; i < numKeywords; i ++ ) {
12 sql.Append( string .Format(SQL_SELECT_PRODUCTS_BY_SEARCH2, PARM_KEYWORD + i));
13 sql.Append(i + 1 < numKeywords ? SQL_SELECT_PRODUCTS_BY_SEARCH3 : SQL_SELECT_PRODUCTS_BY_SEARCH4);
14 }
15
16 string sqlProductsBySearch = sql.ToString();
2
3 IList < ProductInfo > productsBySearch = new List < ProductInfo > ();
4
5 int numKeywords = keywords.Length;
6
7 // Create a new query string
8 StringBuilder sql = new StringBuilder(SQL_SELECT_PRODUCTS_BY_SEARCH1);
9
10 // Add each keyword to the query
11 for ( int i = 0 ; i < numKeywords; i ++ ) {
12 sql.Append( string .Format(SQL_SELECT_PRODUCTS_BY_SEARCH2, PARM_KEYWORD + i));
13 sql.Append(i + 1 < numKeywords ? SQL_SELECT_PRODUCTS_BY_SEARCH3 : SQL_SELECT_PRODUCTS_BY_SEARCH4);
14 }
15
16 string sqlProductsBySearch = sql.ToString();
行11、12、13、14这个for循环是最出彩的地方了。for循环执行完后你就可以看到一个完整的sql查询语句了,我在sqlserver2005
里进行了组合,最后得到了sql是这样的(假设string[]只传了两个值过来):
SELECT
ProductId, Name, Descn, Product.
Image
, Product.CategoryId
FROM Product
WHERE (( LOWER (Name) LIKE ' % ' + @Keyword0 + ' % ' OR LOWER (CategoryId) LIKE ' % ' + @Keyword0 + ' % ' )
OR ( LOWER (Name) LIKE ' % ' + @Keyword1 + ' % ' OR LOWER (CategoryId) LIKE ' % ' + @Keyword1 + ' % ' ));
FROM Product
WHERE (( LOWER (Name) LIKE ' % ' + @Keyword0 + ' % ' OR LOWER (CategoryId) LIKE ' % ' + @Keyword0 + ' % ' )
OR ( LOWER (Name) LIKE ' % ' + @Keyword1 + ' % ' OR LOWER (CategoryId) LIKE ' % ' + @Keyword1 + ' % ' ));
是不是很酷?我觉得很酷。