Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

Linq to SQL Like Operator(×ªÔØ)


As a response for customer's question, I decided to write about using Like Operator in Linq to SQL queries.
Starting from a simple query from Northwind Database;
var query = from c in ctx.Customers
            where c.City == "London"
            select c;
The query that will be sent to the database will be:
SELECT CustomerID, CompanyName, ...
from    dbo.Customers
WHERE  City = [London]
There are some ways to write a Linq query that reaults in using Like Operator in the SQL statement:
1. Using String.StartsWith or String.Endswith
Writing the following query:
var query = from c in ctx.Customers
            where c.City.StartsWith("Lo")
            select c;
will generate this SQL statement:
SELECT CustomerID, CompanyName, ...
from    dbo.Customers
WHERE  City LIKE [Lo%]
which is exactly what we wanted. Same goes with String.EndsWith.
But, what is we want to query the customer with city name like "L_n%"? (starts with a Capital 'L', than some character, than 'n' and than the rest of the name). Using the query
var query = from c in ctx.Customers
            where c.City.StartsWith("L") && c.City.Contains("n")
            select c;
generates the statement:
SELECT CustomerID, CompanyName, ...
from    dbo.Customers
WHERE  City LIKE [L%]
AND      City LIKE [%n%]
which is not exactly what we wanted, and a little more complicated as well.
2. Using SqlMethods.Like method
Digging into System.Data.Linq.SqlClient namespace, I found a little helper class called SqlMethods, which can be very usefull in such scenarios. SqlMethods


Ïà¹ØÎĵµ£º

´¥·¢Æ÷»ñÈ¡Ð޸ıíµÄSQLÓï¾ä


/*
´¥·¢Æ÷»ñÈ¡SQLÓï¾äÔöÁ¿´«Êä
¹¦ÄÜ£º²¶×½Ð޸ıíµÄSQLÓï¾ä
ʹÓÃ˵Ã÷£º 1¡¢ÏÈн¨Ò»±íÊÖ¶¯Ð´ÈëÖ÷¼üÐÅÏ¢»òÕßΨһË÷Òý
   Create table prmary_key
   (tab_name varchar(255),
    key_name varchar(255))
   --´Ë±í½öÔÚ½¨Á¢´¥·¢Æ÷ʱʹÓ㬽¨ÍêËùÓд¥·¢Æ÷ºó ¼ÇµÃ ......

ASP.NET2.0·ÖÒ³¼¼Êõ֮ʹÓô¿sqlÓï¾äµÄË«top·Öҳƪ

RepeaterÔÚǰ̨ʹÓñȽÏÁé»î×ÔÓÉ£¬µ«ÓÐÒ»¸öÎÊÌâ¾ÍÊÇRepeater²»Ö§³ÖÖ±½Ó·ÖÒ³£¬Õâ¸öºÜ¶àÈË¿´ÆðÀ´¾ÍÓе㲻ÏëÓÃÁË£¬µ«ÎÒÏë´ó¼Ò¶¼ÖªµÀGridView¿Ø¼þ»òDataGrid¿Ø¼þÔÚÆôÓÃ×Ô´ø·ÖÒ³µÄʱºòÆäʵЧÂÊÊǷdz£µÍµÄ£¬´óµÄ²»Ëµ£¬Ò»µ«µ½Á˰ÙÍò¼¶Êý¾ÝÒԺ󣬾ͻá¸Ð¾õÊǶàôµÄÍ´¿àºÍÎÞÄÍÁË£¬ËùÒÔ¼´Ê¹ÊÇÓÃDataGrid(GridView)¿Ø¼þ£¬¸ßÊÖÃÇ»¹ÊÇÖ»»á ......

²Ù×÷ÈÕÆÚµÄSQLÓï¾ä´óÈ«

1.ÏÔʾ±¾ÔµÚÒ»Ìì
SELECT DATEADD(mm,DATEDIFF(mm,0,getdate()),0)
select convert(datetime,convert(varchar(8),getdate(),
120)+’01’,120)
2.ÏÔʾ±¾ÔÂ×îºóÒ»Ìì
select dateadd(day,-1,convert(datetime,convert
(varchar(8),dateadd(month,1,getdate()),120)+’01’,120))
SELECT ......

ms sqlµ½mysqlµÄÒÆÖ²£º¹ØÓÚÊý¾ÝÀàÐÍÒÆÖ²µÄһЩÎÊÌâ

   ms sqlÖеÄintÐÍĬÈÏÊÇ4룬mysqlµÄintÐÍĬÈÏÊÇ11λ£¬Ç°Õßµ½ºóÕßµÄÒÆÖ²²»»áÓÐʲôÒþ»¼£¬µ«ÊǺóÕßµ½Ç°ÕßµÄÒÆÖ²¾Í´æÔÚλÊý²»¹»£¬²»ÄÜ´æ´¢´óÊýµÄÒþ»¼¡£
   »¹ÓÐbigint£¬ms sqlÊÇ8룬mysqlĬÈÏÊÇ20λ¡£ ......

SQL Server±¸·ÝÊÂÎñÈÕÖ¾½áβ(Tail)

ÊÂÎñÈÕÖ¾½áβ¾­³£Ìá½»Êý¾Ý¿âδ±¸·ÝµÄÊÂÎñÈÕÖ¾ÄÚÈÝ¡£»ù±¾ÉÏ£¬Ã¿Ò»´ÎÄãÖ´ÐÐÊÂÎñÈÕÖ¾±¸·Ýʱ£¬Äã¶¼ÔÚÖ´ÐÐÊÂÎñÈÕÖ¾½áβµÄ±¸·Ý¡£
ÄÇΪʲô»áÕâôÉè¼ÆÄØ£¿ÒòΪҲÐíÓÉÓÚ½éÖʵÄË𻵣¬µ±Êý¾Ý¿âÒѾ­²»ÔÙ¿ÉÓÃʱ£¬Âé·³¾ÍÀ´ÁË¡£Èç¹ûÏÂÒ»¸öÂß¼­²½ÖèÕýºÃ¾ÍÊÇÒª±¸·Ýµ±Ç°ÊÂÎñÈÕÖ¾µÄ»°£¬¿ÉÒÔÓ¦ÓÃÕâ¸ö±¸·ÝÀ´Ê¹Êý¾Ý¿â´¦Óڵȴý(Standby)״̬¡£ÄãÉ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ