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 Executing Commands Using Data Readers Using Datasets and Data Adapters Using LINQ Using ADO.NET 3.5 Entity Framework

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

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.

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

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

Understanding ADO.NET Architecture (1)

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.

Working with the SQL Server Data Provider

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

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

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.

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

Working with the ODBC Data Provider

Creating an ODBC Data Source (1)

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

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

Data Provider Connection Classes

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

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.

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 =;

Connection String Parameters for SqlConnection (1)

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

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

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

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

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

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

範例 Listing 11-5. CommandParameters.cs

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

C# 資料庫程式設計 Part 2

C# 資料庫程式設計 Part 2

Presentation Transcript

C/C

C C

C c

C, C++

C, C++