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
Ïà¹ØÎĵµ£º
--exec [P_AutoGenerateNumber] 'reception_apply','generate_code','',7
/*
¹ý³Ì˵Ã÷:Éú³É×Ô¶¯±àºÅ
´´½¨Ê±¼ä:2010Äê1ÔÂ12ÈÕ
×÷Õß:feng
debug:ÉÐδ¿¼ÂDZàºÅÒç³öÇé¿ö
*/
ALTER proc [P_AutoGenerateNumber]
(
@table ......
ÉÁ»Ø
1 ÌØÕ÷
ÉÁ»ØÊý¾Ý¿âÖ»ÊÇÂß¼Êý¾ÝÔâµ½ÆÆ»µÊ±µÄÊֶΡ£
ÉÁ»ØÊý¾Ý¿âÈÕÖ¾±ØÐëÔÚÊý¾Ý¿â»Ö¸´ÇøÖд´½¨¡£
ÉÁ»ØÈÕÖ¾²»ÐèÒª¹éµµ£¬²»ÄÜÓÃÓÚÎïÀí»Ö¸´¡£
Ö´ÐÐÉÁ»ØÊý¾Ý¿â²Ù×÷ʱ£¬±³¾°½ø³ÌRVWR¾ÍÆô¶¯ÁË¡£
2 ʹÓÃÉÁ»ØÊý¾Ý¿â
3ÖÖ¹¤¾ß£¬SQL*Plus£¬RMAN£¬OEM¡£
£¨1£©ÒªÇó
ΪÁËʹÓÃÉÁ»ØÊý¾Ý¿â£¬Êý¾Ý¿â±ØÐë´¦Óڹ鵵ÈÕ־ģʽ¡ ......
/*
´¥·¢Æ÷»ñÈ¡SQLÓï¾äÔöÁ¿´«Êä
¹¦ÄÜ£º²¶×½Ð޸ıíµÄSQLÓï¾ä
ʹÓÃ˵Ã÷£º 1¡¢ÏÈн¨Ò»±íÊÖ¶¯Ð´ÈëÖ÷¼üÐÅÏ¢»òÕßΨһË÷Òý
Create table prmary_key
(tab_name varchar(255),
key_name varchar(255))
--´Ë±í½öÔÚ½¨Á¢´¥·¢Æ÷ʱʹÓ㬽¨ÍêËùÓд¥·¢Æ÷ºó ¼ÇµÃ ......
ÓÃJavaÁ¬½ÓSQL Server2000Êý¾Ý¿âÓжàÖÖ·½·¨£¬ÏÂÃæ½éÉÜÆäÖÐ×î³£ÓõÄÁ½ÖÖ£¨Í¨¹ýJDBCÇý¶¯Á¬½ÓÊý¾Ý¿â£©¡£
1. ͨ¹ýMicrosoftµÄJDBCÇý¶¯Á¬½Ó¡£´ËJDBCÇý¶¯¹²ÓÐÈý¸öÎļþ£¬·Ö±ðÊÇmssqlserver.jar¡¢msutil.jarºÍmsbase.jar£¬¿ÉÒÔµ½Î¢ÈíµÄÍøÕ¾È¥ÏÂÔØ£¨http://www.microsoft.com/downloads/details.aspx?FamilyId=07287B11-0502-461A-B ......
Ïò±íÖÐÌí¼ÓÒ»¸öмǼ£¬ÄãҪʹÓÃSQL INSERT Óï¾ä¡£
ÕâÀïÓÐÒ»¸öÈçºÎʹÓÃÕâÖÖÓï¾äµÄÀý×Ó£º
INSERT mytable (mycolumn) VALUES (‘some data’)
Õâ¸öÓï¾ä°Ñ×Ö·û´®’some data’²åÈë±ímytableµÄmycolumn×Ö¶ÎÖС£½«Òª±»²åÈëÊý¾ÝµÄ×ֶεÄÃû×ÖÔÚµÚÒ»¸öÀ¨ºÅÖÐÖ¸¶¨£¬Êµ¼ÊµÄÊý¾ÝÔÚµÚ¶þ¸öÀ¨ºÅÖиø³ö¡£
I ......