发表于 2009-1-5 19:48
| 
杩愮敤 ADO.NET 瀵硅薄浼樺寲鏁版嵁鏌ヨ浠ｇ爜
鍙戝竷: 2008-6-26 22:33 | 浣滆: admin | 鏌ョ湅: 15娆
姣棤鐤戦棶锛孉DO.NET 鍚戜汉浠彁渚涗簡涓绉嶅姛鑳藉己澶с佷豢鐪熸暟鎹簱鐨勫璞℃ā鍨嬶紝瀹冨彲浠ュ皢鏁版嵁璁板綍淇濆瓨鍒板唴瀛樹腑銆傚挨鍏舵槸ADO.net 鐨 DataSet 绫伙紝瀹冧笉浣嗗湪鍔熻兘涓婄浉褰撲簬鏁版嵁搴撹〃鐨勯泦涓瓨鍌ㄥ櫒(central repository)锛岃屼笖鏀寔琛ㄩ棿鐨勫悇绉嶇害鏉熷拰閫昏緫鍏崇郴銆傝繘涓姝ヨ鏉ワ紝DataSet 瀵硅薄鍏跺疄鏄竴绉嶇绾挎暟鎹鍣ㄣ
涔嶄竴鐪嬶紝鍙鎶 DataSet 绫荤殑鎵鏈夌壒鎬ц仈鍚堣捣鏉ワ紝灏辫兘娑堥櫎 SQL 鏌ヨ鍛戒护涓殑澶嶆潅瀛愬彞锛屾瘮濡傞偅浜涙硾婊ユ垚鐏句笖灞傚眰宓屽鐨 INNER JOIN瀛愬彞鎴栬 GROUP BY 瀛愬彞绛夈傚鏉傜殑瀛愬彞鍙互鍒嗚В鎴愪袱涓垨鏇村涓浉浜掔嫭绔嬬殑绠鍗曞瓙鍙ワ紝鑰屽皢姣忎釜绠鍗曞瓙鍙ョ殑鏌ヨ缁撴灉鍒嗗埆淇濆瓨鍦ㄤ笉鍚岀殑 DataTable 瀵硅薄涓紱浠ュ悗鍙鍒嗘瀽杩欎簺鍐呭瓨鏁版嵁涔嬮棿鐨勭害鏉熷拰閫昏緫鍏崇郴锛屽氨鑳介噸寤哄師鍏堣〃涔嬮棿蹇呰鐨勨滃弬鐓у畬鏁存р(referential integrity)銆
涓句釜渚嬪瓙锛氫綘鍙互鎶婂鎴(Customers)琛ㄤ笌璁㈠崟(Orders)琛ㄥ垎鍒繚瀛樺埌涓や釜涓嶅悓鐨 DataTable 瀵硅薄涓紝鐒跺悗閫氳繃 DataRelation 瀵硅薄杩涜缁戝畾 (bind) 銆傝繖鏍, SQL Server (鎴栧叾瀹 DBMS 绯荤粺) 灏卞厤闄や簡 INNER JOIN 瀛愬彞甯︽潵鐨勬矇閲嶈礋鎷咃紱鏇撮噸瑕佺殑鏄紝缃戠粶浼犺緭璐熻嵎涔熷洜姝よ屽ぇ澶у噺杞汇傝薄杩欐牱绠鍖 SQL 鏌ヨ鐨勬柟妗堝浐鐒惰涔嬫湁鏁堬紝鍗村苟涓嶄竴瀹氭绘槸鏈浣抽夋嫨锛屽挨鍏舵槸褰撲綘鐨勬暟鎹簱瑙勬ā搴炲ぇ鑰屼笖鏇存柊棰戠箒鏃躲
鏈枃灏嗕负澶у浠嬬粛鍙︿竴绉嶇敤浜庣畝鍖 SQL 鏌ヨ鐨勬妧鏈紝瀹冨厖鍒嗗埄鐢 ADO.NET 鐨勫唴瀛樻暟鎹璞″噺杞讳簡鐢ㄦ埛鍜 DBMS 绯荤粺鐨勮礋鎷呫
鍒嗚В SQL 鏌ヨ鍛戒护
璁稿鏈夊叧 ADO.NET 鐨勪功绫嶏紝姣斿 David Sceppa 鐨勫ぇ浣溿奝rogramming ADO.NET Core Reference銆(寰蒋鍑虹増绀)锛岄兘寤鸿鎶婂鏉傜殑 SQL 鏌ヨ鍛戒护鍒嗚В鎴愯嫢骞茬畝鍗曠殑瀛愭煡璇紝鐒跺悗鎶婂悇涓瓙鏌ヨ鐨勮繑鍥炵粨鏋滃垎鍒繚瀛樺埌鍚屼竴涓 DataSet 瀹瑰櫒鍐呴儴鐨勮嫢骞蹭釜 DataTable 瀵硅薄涓傝鐪嬩竴涓疄渚嬨
鍋囪浣犻渶瑕佽幏鍙栦竴浜涘鎴疯鍗曚俊鎭紝瑕佹眰璁㈠崟鏄彁浜や簬鎸囧畾骞翠唤鑰屼笖鎸夊鎴疯繘琛屽垎缁勶紝杩樿姹傝鍗曚腑鑷冲皯鍖呭惈 30 浠跺晢鍝併傚悓鏃讹紝浣犺繕甯屾湜鑾峰彇姣忎釜璁㈠崟鐨勬彁浜よ(employee)鍚嶅瓧浠ュ強瀹㈡埛(customer)鐨勫叕鍙稿悕銆備綘鍙互鐢ㄤ笅鍒 SQL 鏌ヨ璇彞鏉ュ疄鐜板畠锛
DECLARE @TheYear int
SET @TheYear = 1997
SELECT o.customerid, od.orderid, o.orderdate, o.shippeddate,
SUM(od.quantity*od.unitprice) AS price,
c.companyname, e.lastname FROM Orders AS o
INNER JOIN Customers AS c ON c.customerid=o.customerid
INNER JOIN Employees AS e ON e.employeeid=o.employeeid
INNER JOIN [Order Details] AS od ON o.orderid=od.orderid
WHERE Year(o.orderdate) = @TheYear AND od.orderid=o.orderid
GROUP BY o.customerid, c.companyname, od.orderid,
o.orderdate, o.shippeddate, e.lastname
HAVING SUM(od.quantity) >30
ORDER BY o.customerid
鏆備笖鎶涘紑浣犳墍鐢ㄧ殑 ADO 鎴栬 ADO.NET鍚с傜敤鏈鍘熷鐨勫懡浠ゆ彁浜ゆ柟寮忔墽琛屼笂杩 SQL 鏌ヨ锛屽彲浠ョ湅鍒板鍥 1 鎵绀虹殑缁撴灉闆嗭細
鍥 1. 绗竴涓 SQL 鏌ヨ鍛戒护鐨勮緭鍑虹粨鏋滐紝鐢 SQL Server Query Analyzer 鐢熸垚骞舵樉绀恒
鍦ㄦ湰娆℃煡璇腑锛屼互涓鏉″瓙鍙ヤ负鏍稿績锛岃屽彟澶栦袱鏉 INNER JOIN 瀛愬彞璧疯緟鍔╀綔鐢ㄣ傛牳蹇冨瓙鍙ョ殑鍔熻兘鏄粠鏁版嵁搴撲腑鏌ヨ鎵鏈夋彁浜や簬鎸囧畾骞翠唤銆佽嚦灏戝寘鍚 30 浠跺晢鍝佺殑璁㈠崟銆傛牳蹇冨瓙鍙ュ涓嬶細
SELECT o.customerid, o.orderid, o.orderdate,
o.shippeddate, SUM(od.quantity*od.unitprice) AS price, o.employeeid
FROM orders AS o
INNER JOIN [Order Details] AS od ON o.orderid=od.orderid
WHERE Year(o.orderdate) = @TheYear AND od.orderid=o.orderid
GROUP BY o.customerid, o.orderid, o.orderdate, o.shippeddate,
o.employeeid
HAVING SUM(od.quantity) >30
ORDER BY o.customerid
鍦ㄨ繑鍥炵粨鏋滈泦涓紝瀹㈡埛鍜屾彁浜よ呭潎鐢 ID 鏉ヨ〃绀恒傜劧鑰岋紝鏈緥闇瑕佺殑鏄鎴风殑鍏徃鍚(compayname)鍜屾彁浜よ呯殑鍚嶅瓧(lastname)銆傛湯灏剧殑 ORDER BY o.customerid 璇彞鏄惧緱鐗瑰埆绠鍗曪紝鍙槸鍏跺姛鑳藉嵈寰堥噸瑕侊細鐢变簬瀹㈡埛鍏徃鍚嶅拰鎻愪氦鑰呭悕瀛楁墍鍚殑瀛楃杈冨锛屼娇鐢ㄨ璇彞灏辫兘閬垮厤瀹冧滑鐨勯噸澶嶅嚭鐜帮紝浠庤屽緱鍒版洿绱у噾鐨勭粨鏋滈泦銆
缁间笂鎵杩帮紝鏁翠釜 SQL 鏌ヨ鍙互琚垎瑙ｆ垚 3 鏉″瓙鏌ヨ鍛戒护鈥曗 1 鏉℃牳蹇冨瓙鏌ヨ锛岀敤浜庤幏鍙栬鍗曡褰曪紱2 鏉¤緟鍔╁瓙鏌ヨ锛岀敤浜庡缓绔嬫彁浜よ匢D - 鎻愪氦鑰呭悕瀛楀拰瀹㈡埛ID - 瀹㈡埛鍏徃鍚嶄袱涓鐓ц〃锛屽嵆锛
SELECT employeeid, lastname FROM Employees
SELECT customerid, companyname FROM Customers
