1 / 114

C# 資料庫程式設計 Part 2

C# 資料庫程式設計 Part 2. 鄧姚文 http://www.ywdeng.idv.tw. 參考書. 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. Agenda. Getting to Know ADO.NET Making Connections

jory
Download Presentation

C# 資料庫程式設計 Part 2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


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

  2. 參考書 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

  3. Agenda Getting to Know ADO.NET Making Connections Executing Commands Using Data Readers Using Datasets and Data Adapters Using LINQ Using ADO.NET 3.5 Entity Framework

  4. Getting to Know ADO.NET Understanding ADO.NET The motivation behind ADO.NET Understanding ADO.NET architecture Working with the SQL Server Data Provider Working with the OLE DB Data Provider Working with the ODBC Data Provider Data providers as APIs

  5. Understanding ADO.NET Before .NET, developers used data access technologies such as ODBC, OLE DB, and Active Data Objects (ADO). ADO.NET is a set of classes that exposes data access services to the .NET programmer, providing a rich set of components for creating distributed, data-sharing applications. ADO.NET is an integral part of the .NET Framework that provides access to relational, XML, and application data. ADO.NET classes are found in System.Data.dll.

  6. The Motivation Behind ADO.NET • Leverage for the Current ADO Knowledge • ADO remains available to the .NET programmer through .NET COM interoperability services • Support for the N-Tier Programming Model • working with a disconnected record set • Integration of XML Support

  7. ADO.NET and the .NET Base Class Library • A dataset (a DataSet object) can hold large amounts of data in the form of • tables (DataTable objects), • their relationships (DataRelation objects), and • constraints (Constraint objects) • in an in-memory cache, which can then be exported to an external file or to another dataset

  8. Understanding ADO.NET Architecture (1)

  9. Understanding ADO.NET Architecture (2) • A data provider connects to a data source and supports data access and manipulation • The OLE DB data provider supports access to older versions of SQL Server as well as to other databases, such as Access, DB2, MySQL, and Oracle. • A dataset supports disconnected, independent caching of data in a relational fashion, updating the data source as required • Data views are used primarily to bind data to Windows and web forms.

  10. Working with the SQL Server Data Provider

  11. 練習 寫一個 Console App. 以 SqlDataReader 列出 Northwind.Employees 資料表的內容,包括First Name 和 Last Name 欄位的資料 Listing9_1

  12. Working with the OLE DB Data Provider (1) To access MS Access databases

  13. Working with the OLE DB Data Provider (2) • The differences between SqlClient and OleDb • in their implementations are transparent, and the user interface is fundamentally the same.

  14. 練習 寫一個 Console App. 使用 OLE DB Data Provider 以 OleDbDataReader 列出 Northwind.Employees 資料表的內容,包括First Name 和 Last Name 欄位的資料 Listing9_2

  15. Working with the ODBC Data Provider

  16. Creating an ODBC Data Source (1)

  17. Creating an ODBC Data Source (2)

  18. Creating an ODBC Data Source (3)

  19. Creating an ODBC Data Source (4)

  20. Creating an ODBC Data Source (5)

  21. Creating an ODBC Data Source (6)

  22. Creating an ODBC Data Source (7)

  23. Creating an ODBC Data Source (8)

  24. Creating an ODBC Data Source (9)

  25. Creating an ODBC Data Source (10)

  26. Creating an ODBC Data Source (11)

  27. 練習 寫一個 Console App. 使用 ODBC Data Provider 以 OdbcDataReader 列出 Northwind.Employees 資料表的內容,包括First Name 和 Last Name 欄位的資料 Listing9_3

  28. Agenda Getting to Know ADO.NET Making Connections Executing Commands Using Data Readers Using Datasets and Data Adapters Using LINQ Using ADO.NET 3.5 Entity Framework

  29. Making Connections A connection is an instance of a class that implements the System.Data.IDbConnection interface for a specific data provider

  30. Data Provider Connection Classes

  31. Connecting to SQL Server Express with SqlConnection (1) // connection string string connString = @" server = .\sqlexpress; integrated security = true; "; Listing 10-1

  32. Connecting to SQL Server Express with SqlConnection (2) • server = .\sqlexpress; • In this statement, . (dot) represents the local server, and the name followed by the \ (slash) represents the instance name running on the database server • (local) is an alternative to the . (dot) to specify the local machine, so .\sqlexpress can be replaced with (local)\sqlexpress. • integrated security = true; • Use Windows Authentication (i.e., any valid logged-on Windows user can log on to SSE) • You could alternatively have used sspi instead of true, as they both have the same effect.

  33. Connecting to SQL Server Express with SqlConnection (3) thisConnection.ConnectionString = @" server = .\sqlexpress; user id = sa; password = x1y2z3 "; • Using SQL Server Security • Replace integrated security with user id and password • Empty password:password =;

  34. Connection String Parameters for SqlConnection (1)

  35. Connection String Parameters for SqlConnection (2)

  36. Connection String Parameters for SqlConnection (3)

  37. Connection Pooling Creating connections is expensive in terms of memory and time With pooling, a closed connection isn’t immediately destroyed but is kept in memory in a pool of unused connections If a new connection request comes in that matches the properties of one of the unused connections in the pool, the unused connection is used for the new database session

  38. Agenda Getting to Know ADO.NET Making Connections Executing Commands Using Data Readers Using Datasets and Data Adapters Using LINQ Using ADO.NET 3.5 Entity Framework

  39. Executing Commands (1) Exactly one scalar result ExecuteScalar (Returns object) Each data provider has a command class that implements System.Data.IDbCommand interface.

  40. Executing Commands (2) • ExecuteScalar Method • System.InvalidCastException if the returned object cannot be casted • select firstname from employees where lastname = 'Davolio' • ExecuteReader Method • Multiple Results • Returns a SqlDataReader • bool Read() 前進到下一個 row

  41. Executing Commands (3) • ExecuteNonQuery Method • 用於 INSERT, UPDATE, DELETE • 傳回受影響的 row 數目

  42. 練習 寫一個 Console App. 依據Northwind.Employees 計算並印出員工人數 select count(*) from employees Listing 11-2. CommandScalar.cs

  43. Command Parameters • A command parameter is a placeholder in the command text where a value will be substituted • In SQL Server, named parameters are used. They begin with @ followed by the parameter name with no intervening space • Use the Prepare method, which can make your code run faster because • The SQL in a “prepared” command is parsed by SQL Server only the first time it is executed

  44. 範例 Listing 11-5. CommandParameters.cs

  45. Agenda Getting to Know ADO.NET Making Connections Executing Commands Using Data Readers Using Datasets and Data Adapters Using LINQ Using ADO.NET 3.5 Entity Framework

  46. Using Data Readers • Looping Through a Result Set • bool SqlDataReader.Read() • Using Ordinal Indexers (Listing 12-2) • rdr[0] • rdr[1].ToString().PadLeft(20) • Using Column Name Indexers • rdr["companyname"].ToString().PadLeft(25) • Using Typed Accessor Methods • System.Data.SqlTypes

More Related