C# 資料庫程式設計

1. C# 資料庫程式設計 鄧姚文 http://www.ywdeng.idv.tw

2. 課程簡介 • .NET Framework Versions and the Green Bit and Red Bit Assembly Model • Writing Database Queries • Manipulating Database Data • Using Stored Procedures • Using XML • Using Transactions • Using ADO.NET 3.5 • Using LINQ

3. 參考書 Vidya Vrat Agarwal, James Huddleston, Ranga Raghuram, Syed Fahad Gilani, Jacob Hammer Pedersen, and Jon Reid, Beginning C# 2008 Databases: From Novice to Professional,Apress, 2008

4. 範例資料庫 • AdvantureWorks • http://msftdbprodsamples.codeplex.com/ • 使用 SQL 2005 者，安裝 AdventureWorksDB.msi • 使用 SQL 2008 者，安裝 SQL2008.AdventureWorks_All_Databases.x86.msi • NorthWind • 到 http://www.microsoft.com/downloads搜尋 sample database • 安裝 SQL2000SampleDb.msi • 位於 C:\SQL Server 2000 Sample Databases

5. .NET FrameworkVersions • 多種版本的 .NET Framework 可以安裝在同一個作業系統之中 • 相容、共存的方式： • 分目錄，一個版本一個目錄 • 一層蓋一層

6. .NET Framework versions installed in Visual Studio 2008

7. Green Bit and Red Bit Assembly Model • Green Bit Assemblies • Additional assemblies that can be installed above other existing .NET Framework assemblies without affecting them • Red Bit Assemblies • The assemblies that ship as either part of the platform or part of a development tool.

8. .NET 3.5 green bit assemblies

9. Using Visual Studio 2008

10. 資料儲存的等級 • Spreadsheet 試算表 • MS Excel • Desktop Database • MS Access • Server Database • MS SQL Server

11. Why Use a Database? Compactness 精實: Databases help in maintaining large amounts of data, and thus completely replace voluminous paper files. Speed 速度: Searches for a particular piece of data or information in a database are much faster than sorting through piles of paper. Less drudgery 減少苦差事: It is a dull work to maintain files by hand; using a database completely eliminates such maintenance. Currency 即時資訊: Database systems can easily be updated and so provide accurate information all the time and on demand.

12. Benefits of Using a Relational DatabaseManagement System • Data integrity • Data atomicity • Data security • Transaction processing • Recovery • Storage management • Redundancy • Inconsistency • Access anomalies

13. The Database Life Cycle • Requirement analysis 需求分析 • Logical design 邏輯設計（ERD實體關係圖） • Physical design 實體設計（資料表與索引） • Database implementation 建立資料庫（Data Definition Language, DDL） • Data modification 處理資料（Data Manipulation Language, DML） • Database monitoring 監控 • Monitoring, Redesign, Modification

14. Mapping Cardinalities • One-to-One (1:1) • Separate data by frequency of use • One-to-Many (1:M) • Reduce redundancy • Primary key -> Foreign Key • Parent Table -> Child table • Many-to-Many (M:M) • Junction table, 兩對 One-to-Many

15. Keys Candidate Keys 候選鍵 Primary key 主鍵 Composite key 複合鍵 Surrogate key 代理鍵 Foreign key 外來鍵

16. Primary Keys Each record of the entity must have a not-null value. The value must be unique for each record entered into the entity. The values must not change or become null during the life of each entity instance. There can be only one primary key defined for an entity.

17. Data Integrity • Entity Integrity • Primary key value exists, can not be null • Unique primary key value • Referential Integrity (RI) • All foreign key values in a child table either match PK values in a parent table or (if permitted) be null • Foreign key constraint

18. SQL DDL and DML • Data definition language (DDL) statements • Used for creating tables, relationships, and other structures. • Data manipulation language (DML) statements. • Used for queries and data modification

19. Schemas（架構描述） For SQL Server 2005 and later Schemas are no longer equivalent to database users Each schema is now a distinct namespace that exists independently of the database user who created it A schema is simply a container of objects A schema can be owned by any user, and its ownership is transferable.

21. Writing Database Queries • SELECT {ColumnName(s)} FROM {TableName(s)} WHERE {Conditions} • All SQL statements end with a semi-colon (;) • SELECT * FROM Sales.SalesReason; • SELECT * FROM Sales.SalesReason WHERE ReasonType='Other'; • SELECT * FROM HumanResources.Employee ORDER BY BirthDate DESC;

23. The WHERE Clause 多個條件都必須成立時用 AND 眾多條件只需要一個成立即可時用 OR SELECT *FROM Purchasing.vVendorWHERE City='Lynnwood' AND [Name] like '%Bicycles'

24. Joins • Inner Joins • Returns only rows that satisfy the join specification • Natural joins • Left Outer Join • Right Outer Join • Full Outer Join • Cross Join • Union Join

25. Inner Join select o.orderid, o.customerid, e.lastname from orders o inner join employees e on o.employeeid = e.employeeid

26. Pattern Matching in Search Conditions • % • Matches any string of zero or more characters • _ • Matches any single character • [ ] • Any single character within the specified range (for example, [a-f]) or set (for example, [abcdef]) • [^] • Any single character NOT within the specified range (for example, [^a-f]) or set (for example, [^abcdef])

27. 練習 HumanResources.Employee.ManagerID 是員工上級主管的代碼 請寫一個 SQL 敘述列出 Marketing Manager David Bradley 的下屬

28. DISTINCT 去除重複的項目 SELECT DISTINCT ProductID FROM Production.ProductInventory

29. IN SELECT *FROM Purchasing.vVendorWHERE City IN ('Lynnwood', 'New York')ORDER BY City 也可以用 OR 實現 集合的概念 Subquery 的基礎

30. Ranges (BETWEEN and NOT BETWEEN) SELECT ProductID, Name FROM AdventureWorks.Production.Product WHERE ListPrice BETWEEN 100 AND 500 ORDER BY ListPrice 範圍起點（較小的值）必須在 AND 前面，終點（較大的值）在 AND 後面。

31. Null values (IS NULL and IS NOT NULL) SELECT s.Name FROM AdventureWorks.Sales.Customer c JOIN AdventureWorks.Sales.Store s ON c.CustomerID = S.CustomerID WHERE c.SalesPersonID IS NOT NULL ORDER BY s.Name IS NOT NULL 不是空的，有值！

32. 練習 • Person.Contact 為聯絡人資料 • 請列出 First Name 第一個字母為 A 且 Title 欄位有值的聯絡人清單如右： • 內容包括 Title, First Name, Last Name

33. GROUP BY Clause SELECT CardType, ExpYear, count(CardType) AS 'Total Cards' FROM Sales.CreditCard WHERE ExpYear in (2006,2007) GROUP BY ExpYear,CardType ORDER BY CardType,ExpYear Organize output rows into groups

34. Aggregate Functions select SalesOrderID,min(UnitPrice)as "Min", max(UnitPrice) as "Max",Sum(UnitPrice) as "Sum", Avg(UnitPrice)as "Avg" from Sales.SalesOrderDetail where SalesOrderID between 43659 and 43663 group by SalesOrderID

35. Choosing Rows with the HAVING Clause The WHERE clause is used to filter the rows that result from the operations specified in the FROM clause. The GROUP BY clause is used to group the output of the WHERE clause. The HAVING clause is used to filter rows from the grouped result.

36. SELECT ProductID, AVG(OrderQty) AS AverageQuantity, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING SUM(LineTotal) > $1000000.00 AND AVG(OrderQty) < 3 ; 列出產品平均數量與小計 SELECT ProductID FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING AVG(OrderQty) > 5 ORDER BY ProductID ;

37. Subquery SELECT Ord.SalesOrderID, Ord.OrderDate, (SELECT MAX(OrdDet.UnitPrice) FROM Sales.SalesOrderDetail AS OrdDet WHERE Ord.SalesOrderID = OrdDet.SalesOrderID) AS MaxUnitPrice FROM Sales.SalesOrderHeader AS Ord 列出訂單最高單價

38. Subquery Rules • The select list of a subquery introduced with a comparison operator can include only one expression or column name (except that EXISTS and IN operate on SELECT * or a list, respectively). • If the WHERE clause of an outer query includes a column name, it must be join-compatible with the column in the subquery select list. • The ntext, text, and image data types cannot be used in the select list of subqueries. • Because they must return a single value, subqueries introduced by an unmodified comparison operator (one not followed by the keyword ANY or ALL) cannot include GROUP BY and HAVING clauses.

39. Subquery Rules The DISTINCT keyword cannot be used with subqueries that include GROUP BY. The COMPUTE and INTO clauses cannot be specified. ORDER BY can only be specified when TOP is also specified. A view created by using a subquery cannot be updated. The select list of a subquery introduced with EXISTS, by convention, has an asterisk (*) instead of a single column name. The rules for a subquery introduced with EXISTS are the same as those for a standard select list, because a subquery introduced with EXISTS creates an existence test and returns TRUE or FALSE, instead of data.

40. 大部分的 Subquery 可以用 JOIN 替代 /* SELECT statement built using a subquery. */ SELECT Name FROM Production.Product WHERE ListPrice = (SELECT ListPrice FROM Production.Product WHERE Name = 'Chainring Bolts' ) /* SELECT statement built using a join that returns the same result set. */ SELECT Prd1.Name FROM Production.Product AS Prd1 JOIN Production.Product AS Prd2 ON (Prd1.ListPrice = Prd2.ListPrice) WHERE Prd2.Name = 'Chainring Bolts' 價錢和 Chainring Bolts 一樣

41. Subquery Types With aliases With IN or NOT IN In UPDATE, DELETE, and INSERT statements With comparison operators With ANY, SOME, or ALL With EXISTS or NOT EXISTS In place of an expression

42. Subquery with Aliases SELECT EmployeeID, ManagerID FROM HumanResources.Employee WHERE ManagerID IN (SELECT ManagerID FROM HumanResources.Employee WHERE EmployeeID = 12)

43. Subquery with NOT IN SELECT Name FROM Production.Product WHERE ProductSubcategoryID NOT IN (SELECT ProductSubcategoryID FROM Production.ProductSubcategory WHERE Name = 'Mountain Bikes' OR Name = 'Road Bikes' OR Name = 'Touring Bikes')

44. Subquery in UPDATE, DELETE, and INSERT Statements UPDATE Production.Product SET ListPrice = ListPrice * 2 WHERE ProductID IN (SELECT ProductID FROM Purchasing.ProductVendor WHERE VendorID = 51);

45. Subqueries with Comparison Operators SELECT Name FROM Production.Product WHERE ListPrice > (SELECT MIN (ListPrice) FROM Production.Product GROUP BY ProductSubcategoryID HAVING ProductSubcategoryID = 14) SELECT CustomerID FROM Sales.Customer WHERE TerritoryID = (SELECT TerritoryID FROM Sales.SalesPerson WHERE SalesPersonID = 276) SELECT Name FROM Production.Product WHERE ListPrice > (SELECT AVG (ListPrice) FROM Production.Product)

46. Comparison Operators Modified by ANY, SOME, or ALL • SOME is an ISO standard equivalent for ANY • >ALL means greater than every value • Greater than the maximum value • >ANY means greater than at least one value • Greater than the minimum • =ANY is equivalent to IN • <>ALL means the same as NOT IN

47. SELECT Name FROM Production.Product WHERE ListPrice >= ANY (SELECT MAX (ListPrice) FROM Production.Product GROUP BY ProductSubcategoryID) Finds the products whose list prices are greater than or equal to the maximum list price of any product subcategory. SELECT CustomerID FROM Sales.Customer WHERE TerritoryID <> ANY (SELECT TerritoryID FROM Sales.SalesPerson) Finds customers located in a territory not covered by any sales persons.

48. Subquery with EXISTS SELECT Name FROM Production.Product WHERE EXISTS (SELECT * FROM Production.ProductSubcategory WHERE ProductSubcategoryID = Production.Product.ProductSubcategoryID AND Name = 'Wheels') Finds the names of all products that are in the Wheels subcategory

49. Subquery Used in Place of an Expression SELECT Name, ListPrice, ( SELECT AVG(ListPrice) FROM Production.Product ) AS Average, ListPrice - ( SELECT AVG(ListPrice) FROM Production.Product ) AS Difference FROM Production.Product WHERE ProductSubcategoryID = 1 Finds the prices of all mountain bike products, their average price, and the difference between the price of each mountain bike and the average price.

50. 練習 Production.Product 為產品基本資料表 Production.ProductInventory 為庫存狀態表 請列出倉庫 Tool Crib 沒有的產品