Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö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


Ïà¹ØÎĵµ£º

º½¿Õ¹«Ë¾¹ÜÀíϵͳ(VC++ ÓëSQL 2005)

ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
      ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃæ£¬ºóÃæ ......

SQL¡¡Êý¾Ý¿âÉú³É×Ô¶¯±àºÅ´úÂë

--exec  [P_AutoGenerateNumber]  'reception_apply','generate_code','',7
/*
 ¹ý³Ì˵Ã÷:Éú³É×Ô¶¯±àºÅ 
 ´´½¨Ê±¼ä:2010Äê1ÔÂ12ÈÕ
 ×÷Õß:feng
 debug:ÉÐδ¿¼ÂDZàºÅÒç³öÇé¿ö
*/ 
ALTER proc [P_AutoGenerateNumber]
  (
  @table ......

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


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

sql ²é±í¿Õ¼äÓï¾ä

SELECT UPPER(F.TABLESPACE_NAME) "±í¿Õ¼äÃû",
       D.TOT_GROOTTE_MB "±í¿Õ¼ä´óС(M)",
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "ÒÑʹÓÿռä(M)",
       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_G ......

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

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