LINQ to SQL¸üÐÂÊý¾Ý¿â²Ù×÷
ʹÓÃLINQ to SQL½¨Ä£NorthwindÊý¾Ý¿â
ÔÚÕâ֮ǰһÆðѧ¹ýLINQ to SQLÉè¼ÆÆ÷µÄʹÓã¬ÏÂÃæ¾ÍʹÓÃÈçϵÄÊý¾ÝÄ£ÐÍ£º
µ±Ê¹ÓÃLINQ to
SQLÉè¼ÆÆ÷Éè¼ÆÒÔÉ϶¨ÒåµÄÎå¸öÀࣨProduct£¬Category£¬Customer£¬OrderºÍOrderDetail£©µÄʱºò£¬Ã¿¸öÀàÖеÄÊôÐÔ
¶¼Ó³ÉäÁËÏàÓ¦Êý¾Ý¿âÖбíµÄÁУ¬Ã¿¸öÀàµÄʵÀýÔò´ú±íÁËÊý¾Ý¿â±íÖеÄÒ»Ìõ¼Ç¼¡£ÁíÍ⣬µ±¶¨ÒåÊý¾ÝÄ£ÐÍʱ£¬LINQ to
SQLÉè¼ÆÆ÷ͬÑù»á´´½¨Ò»¸ö×Ô¶¨ÒåDataContextÀ࣬À´×÷ΪÊý¾Ý¿â²éѯºÍÓ¦ÓøüÐÂ/±ä»¯µÄÖ÷ÒªÇþµÀ¡£ÒÔÉÏÊý¾ÝÄ£ÐÍÖж¨ÒåµÄDataContext
ÀàÃüÃûΪ“NorthwindDataContext”¡£¸ÃÀàÖаüº¬ÁË´ú±íÿ¸ö½¨Ä£Êý¾Ý¿â±íµÄÊôÐÔ¡£
ʹÓÃLINQÓï·¨±í´ïʽ¿ÉÒÔÊ®·Ö¼òµ¥µÄʹÓÃNorthwindDataContextÀàÀ´²éѯºÍ¼ìË÷Êý¾Ý¿âÖеÄÊý¾Ý¡£LINQ to
SQL»áÔÚÔËÐÐʱ×Ô¶¯µÄת»»LINQ±í´ïʽµ½Êʵ±µÄSQL´úÂëÀ´Ö´ÐС£ÀýÈ磬±àдÒÔÏÂLINQ±í´ïʽÀ´¸ù¾ÝProduct
Name¼ìË÷µ¥¸öProduct¶ÔÏó£º
»¹¿ÉÒÔʹÓÃLINQ±í´ïʽÀ´¼ìË÷ËùÓв»´æÔÚÓÚOrder DetailsÖеģ¬²¢ÇÒUnitPrice´óÓÚ100µÄËùÒÔProduct£º
±ä»¯¸ú×ÙºÍDataContext.SubmitChanges£¨£©
µ±Ö´ÐвéѯºÍ¼ìË÷ÏñProductʵÀýÕâÑùµÄ¶ÔÏóʱ£¬LINQ to SQL»á×Ô¶¯±£³Ö¶ÔÕâЩ¶ÔÏóÈκα仯»ò¸üеĸú×Ù¡£ÎÒÃÇ¿ÉÒÔ½øÐÐÈÎÒâ´ÎÊýµÄ²éѯ£¬ÒÔ¼°Ê¹ÓÃLINQ to SQLµÄDataContextÀà×÷³ö¸üУ¬¶øÕâЩ±ä»¯¶¼»á±»È«²¿¸ú×Ù¡£
×¢Ò⣺LINQ to SQLµÄ±ä»¯¸ú×Ù·¢ÉúÓÚµ÷ÓÃÕ߶˗—¶ø²»ÊÇÔÚÊý¾Ý¿âÖС£Õâ¾ÍÒâζ×ÅʹÓøú×Ù²»»áÏúºÄÈκÎÊý¾Ý¿â×ÊÔ´£¬Ò²²»ÐèÒªÔÚÊý¾Ý¿âÖиıä/°²×°ÈκÎ×é¼þÄ£¿é¡£
µ±¶Ô´ÓLINQ to
SQLÖмìË÷µÄ¶ÔÏó×÷³ö¸ü¸ÄÖ®ºó£¬ÎÒÃÇ¿ÉÒÔÑ¡Ôñµ÷ÓÃDataContextÉϵÄSubmitChange£¨£©·½·¨À´Ó¦Óñ仯·µ»Øµ½Êý¾Ý¿â¡£Õ⽫»áµ¼ÖÂ
LINQ to SQL¶¯Ì¬¼ÆËã²¢Ö´ÐÐÊʵ±µÄSQL´úÂëÀ´¸üÐÂÊý¾Ý¿â¡£ÀýÈ磬±àдÒÔÏ´úÂë¸üÐÂÊý¾Ý¿âÖÐProduct
NameΪ“Chai”µÄProductÉϵÄUnitPriceºÍUnitsInStock£º
µ±ÔÚÒÔÉÏ´úÂëÖе÷ÓÃnorthwind.SubmitChanges£¨£©·½·¨Ê±£¬LINQ to SQL»á¶¯Ì¬¹¹½¨²¢Ö´ÐÐÒ»¸ö¸üÐÂÕâÁ½¸öProductÊôÐÔÖµµÄSQL“UPDATE”´úÂëÄ£¿é¡£
ÔÚÏÂÃæ´úÂëÖÐÎÒÃÇÀ´±éÀú²»Á÷Ðеģ¬°º¹óµÄProduct£¬²¢°ÑËüÃǵÄReorderLevelÊôÐÔÉèΪ0£º
µ±ÔÚÒÔÉÏ´úÂëÖе÷ÓÃnorthwind.SubmitChanges£¨£©·½·¨Ê±£¬LINQ to SQL»á¼ÆËã²¢Ö´ÐÐÒ»×éÊʵ±µÄUPDATE´úÂëÄ£¿éÀ´ÐÞ¸ÄRecorderLevelÊôÐÔÒѱ仯µÄProduct¡£
×¢Ò⣬Èç¹ûÒ»¸öProductµÄÊôÐÔûÓÐͨ¹ýÊôÐÔÖ¸¶¨¶ø·¢Éú±ä»¯£¬Ôò¸Ã¶ÔÏ󲻻ᱻÈÏΪÊÇ·¢Éú±ä»¯µÄ£¬²¢ÇÒLINQ to
SQLÒ²²»
Ïà¹ØÎĵµ£º
syscolumns
ÿ¸ö±íºÍÊÓͼÖеÄÿÁÐÔÚ±íÖÐÕ¼Ò»ÐУ¬´æ´¢¹ý³ÌÖеÄÿ¸ö²ÎÊýÔÚ±íÖÐÒ²Õ¼Ò»ÐС£¸Ã±íλÓÚÿ¸öÊý¾Ý¿âÖС£
ÁÐÃûÊý¾ÝÀàÐÍÃèÊö
name
sysname
ÁÐÃû»ò¹ý³Ì²ÎÊýµÄÃû³Æ¡£
id
int
¸ÃÁÐËùÊôµÄ±í¶ÔÏó ID£¬»òÓë¸Ã²ÎÊý¹ØÁªµÄ´æ´¢¹ý³Ì ID¡£
xtype
tinyint
systypes ÖеÄÎïÀí´æ´¢ÀàÐÍ¡£
typestat
tinyint
½öÏÞÄÚ²¿Ê¹ ......
»úÆ÷Çé¿ö
p4: 2.4
ÄÚ´æ: 1 G
os: windows 2003
Êý¾Ý¿â: ms sql server 2000
Ä¿µÄ: ²éѯÐÔÄܲâÊÔ,±È½ÏÁ½ÖÖ²éѯµÄÐÔÄÜ
SQL²éѯЧÂÊ step by step
-- setp 1.
-- ½¨±í
create table t_userinfo
(
userid int identity(1,1) primary key nonclustered,
nick varchar(50) not null default '',
classid int not nul ......
1 Âß¼Êý¾Ý¿âºÍ±íµÄÉè¼Æ
Êý¾Ý¿âµÄÂß¼Éè¼Æ¡¢°üÀ¨±íÓë±íÖ®¼äµÄ¹ØϵÊÇÓÅ»¯¹ØϵÐÍÊý¾Ý¿âÐÔÄܵĺËÐÄ¡£Ò»¸öºÃµÄÂß¼Êý¾Ý¿âÉè¼Æ¿ÉÒÔΪ
ÓÅ»¯Êý¾Ý¿âºÍÓ¦ÓóÌÐò´òÏÂÁ¼ºÃµÄ»ù´¡¡£
±ê×¼»¯µÄÊý¾Ý¿âÂß¼Éè¼Æ°üÀ¨ÓöàµÄ¡¢ÓÐÏ໥¹ØϵµÄÕ±íÀ´´úÌæºÜ¶àÁеij¤Êý¾Ý±í¡£ÏÂÃæÊÇһЩʹÓñê×¼»¯
±íµÄһЩºÃ´¦¡£
A:ÓÉÓÚ±íÕ£¬Òò´Ë¿ÉÒÔʹŠ......
SELECT DISTINCT '['+user_name(b.uid)+'].['+b.name+']' AS ¶ÔÏóÃû,b.type AS ÀàÐÍ
from sysdepends a,sysobjects b
WHERE b.id=a.depid
AND a.id=OBJECT_ID('¹ý³ÌÃû');
EXEC SP_DEPENDS '¹ý³ÌÃû'; ......
±àÂë¹ý³ÌÖÐÓöµ½µÄSQL·ÖÒ³Çé¿ö£¬×ܽ᣺
´ÓÊý¾Ý¿â±íÖеÚMÌõ¼Ç¼¿ªÊ¼¼ìË÷NÌõ¼Ç¼
MySQL£º
ÏȲéѯ·ÖÒ³£¬È»ºóÅÅÐò£º
select * from (select * from student limit 5,2) pageTable order by id desc ;
ÏÈÅÅÐò£¬È»ºó²éѯ·ÖÒ³£ºselect * from student order by id desc limit 5,2 ;
Oracle£º
SELECT * fro ......