Chapter 導論
2.NETADO.NET.NET3.5LINQ ADO.NET ADO.NETMCTSExam 70-561: TS: Microsoft.NET Framework 3.5: ADO.NET ADO.NET MCTSMCPD Step-By-Step ADO.NET?.NETADO.NET WindowsASP.NET ADO.NET ADO.NET ADO.NET1ADO.NET Visual Studio 28 Windows10TableAdapter ASP.NET 3.5
ADO.NET 3.5 精研講座 18 LINQ to SQL19LINQ to DataSetLINQ C#Delegate (Generic) 基本心法篇第 1 章 ADO.NET 資料存取技術概論 ADO.NETADO.NET DataSet.NETADO.NET XML 第 2 章 使用 Visual Studio 28 管理資料 12ASP.NETADO.NET Visual Studio 28Microsoft Visual C# 28 (C# 3.0)Visual Basic 28 (9.0)Visual C++ Visual Studio SQL Server 25SQL Server 25 ExpressExcelAccess 第 3 章 連接到資料庫 ADO.NETConnection SQL Server 20/25SQL Server 25 ExpressOracle 10gMySQLAccessExcel 3
第 4 章 使用 Command 與預存程序 4 Command Command 第 5 章 使用 DataReader 讀取資料 DataReader DataReader (Read-only) (Forwardonly) (Stream) DataReaderDataReader Excel 第 6 章 離線的資料儲存體 -DataSet DataSetDataTableADO.NET DataSet/DataTable DataRowDataSet/DataTable 第 7 章 XML 結構描述與具型別資料集 ADO.NETXSD Visual Studio 28DataSet/ DataTableDataSet/DataTableXML 第 8 章 使用 DataAdapter 異動資料 DataAdapterADO.NETConnection DataReaderCommand Command
ADO.NET 3.5 精研講座 DataSetDataTableDataTable DataRelation DataRelationDataTable DataRelationForeignKeyConstraint DataRelationForeignKeyConstraint 第 10 章 使用 TableAdapter 開發資料存取程式 Visual Studio 28 Web 第 11 章 再談 DataTable 與 DataView 物件 第 9 章 DataRelation 與 ForeignKeyConstraint DataSetDataTable DataTableDataView 實戰主題篇第 12 章 建立 ASP.NET 資料存取網頁 Visual Studio 28ASP.NET SqlDataSourceObjectDataSource ASP.NET GridViewDetailsViewFormView 5
第 13 章 通用資料存取模型 6 ADO.NET (Provider Model) Provider Factory 第 14 章 離線資料存取策略 ADO.NET 第 15 章 MARS 非同步命令與 SqlBulkCopy (MARS).NET SqlClient SQL Server 第 16 章 ADO.NET 與交易處理 ADO.NET System.Transaction 第 17 章.NET CLR 整合 SQL Server 25.NET C# SQL Server.NETSQL
ADO.NET 3.5 精研講座.NET Framework 3.5LINQ (.NET Language Integrated Query)ADO.NET LINQLINQ to ADO.NETLINQ to SQLLINQ to DataSet LINQVisual StudioLINQ to SQL DataContextSQL LINQ to SQL Step-by-StepLINQASP.NET 3.5 DataListDataPagerLinqDataSource 第 19 章 與 LINQ to DataSet 共舞 LINQ to DataSetDataSetDataSet DataTable (join) LINQ to DataSetDataRowExtensionsDataTableExtensions DataSetLINQDataSet 第 18 章 與 LINQ to SQL 接軌 系統需求與軟體需求 SQL Server 20/SQL Server 25/SQL Server 25 ExpressVisual Studio 28 http://www.microsoft.com/taiwan/vstudio/ 軟體需求 7
SQL Server 2SQL Server 25 Express/SQL Server 25 Microsoft Visual Studio 28 8 My SQL 5.0 Oracle 10g Express/ Oracle 10g/11g Access Microsoft Office 27 建立資料庫 Database ADONETDB_Data.MDF NORTHWND.MDF PUBS.MDF ADONETDB_Data.MDF NORTHWNDPUBSSQL Server 20 ToolsSQL20SampleDb.msi 使用 SQL Server 25 /SQL Server 25 Express SQL Server 25SQL Server 25 Express SQL Server 25SQL Server 25 Management StudioSQL Server 25 ExpressMicrosoft SQL Server Management Studio ExpressSQL Server 25 Express Microsoft SQL Server Management Studio Express A D O N E T D B To o l s \ SQLServer25ExpressSQLServer25_SSMSEE.msi
ADO.NET 3.5 精研講座 http://www.microsoft.com/downloads/search.aspx?displaylang=zh-tw Step DatabaseADONETDB_Data.MDFADONETDB_ Log.LDFC:\Database Step Microsoft SQL Server Management Studio Express WindowsSQL Server 25 Express Step Microsoft SQL Server Management Studio Express 9
Step 10 Step05ADONETDB_Data.MDF
ADO.NET 3.5 精研講座 Step07 SamplesWindows Forms Visual Studio 28*.csproj*.sln Step06 ASP.NETIIS 以檔案型式開啟 ASP.NET 網站與 ASP.NET Web 服務 12 StepVisual Studio 28 11
Step(File)(Open)(Web Site) 12 Step Ch12
ADO.NET 3.5 精研講座 http://www.delightpress.com.tw.net Magazine http://www.netmag.com.tw Visual Studio 28 http://www.microsoft.com/taiwan/download/ 13
NOTE
Chapter 使用 Visual Studio 28 管理資料
Visual Studio 2840 Visual Studio 28Microsoft Visual C# 28 Visual Basic 28Visual C++.NET.NET.NET.NET Enterprise ServerVisual Studio.NETVisual Basic.NETVisual C#.NET.NET25.NETVisual Studio Visual Studio 28.NET Visual Basic 9.0C# 3.0Visual Basic 28 Visual Basic 9.0Visual C# 28C# 3.0 Visual Studio 28 2-1 Visual Studio 28 Visual Studio 2-1
滑鼠移動到 Visual Studio 伺服器總管上方自動顯示視窗 2-1 SQL Server Step By Step 2-1 建立 SQL Server 資料庫 SQL Server 25SQL Server 25 Express MyDB StepSQL Server2-2 -1 3.5 精研講座 ADO.NET SQL Server 1 按滑鼠右鍵開啟快速鍵功能表 2 建立資料庫 2-2SQL Server 41
Step2-3SQL Server (local)\sqlexpresssqlexpress SQL Server 25 (local)localhost.sql Server 25 Windows MyDB Visual Studio 2842 2 3 1 4 2-3SQL Server Step 2-2 SQL Server
SQL Server 25SQL Server 25 Expresslocalhost\Sqlexpress Step By Step 2-1 建立資料庫連結 SQL Server 25SQL Server 25 Express ADONETDB ADONETDB_Data.MDFADONETDB_Log.ldf SQL Server 25SQL Server 25 Express Step Step ADONETDB 2-4 2-4 -2 3.5 精研講座 SQL ServerADO.NET Step Microsoft SQL Server SQL Server 25SQL Server 25 ExpressMicrosoft SQL Server AccessOracle 2-5 Microsoft SQL Server 43
2-5 Step2-6 Visual Studio 2844 (local)\sqlexpresssql Server Express Windows ADONETDB 1 2 3 4 2-6
2-7 (Connect Timeout)15 Windows Integrated Security TrueData Source (local)\sqlexpress Initial Catalog ADONETDB Step05 2-8 2-7 -2 3.5 精研講座 SQL ServerADO.NET 2-8 2-9 2-9 45
2-3 Visual Studio 2846 Step By Step 2-3 建立 TaipeiRegion 資料表 Ta i p e i R e g i o n R e g i o n I D RegionDescription Step A D O N E T D B 2-10 Step2-11RegionID 2-10 RegionDescription RegionIDNull -(Is Identity) 2-11
Step2-12RegionID 1 選取 RegionID 2 設定主索引鍵 2-12 TaipeiRegion 2-13 2-13 -3 3.5 精研講座 ADO.NET StepTaipeiRegion Visual Studio 28 RegionDescription 5 2-14 RegionID 2-14 47
2-3-1 查詢 瀏覽, 與修改資料 TaipeiRegion (Table) (Stored Procedure) (Trigger) Visual Studio 2848 2-4 Visual Studio 28 AccessDBFExcel 2-4-1 連結到 SQL Server 25 資料庫檔案 SQL Server 2-15 StepVisual Studio StepMicrosoft SQL Server Step Step C:\Database\Pubs.MDFWindows
Step05 1 2 3 4 5 2-15 -4 3.5 精研講座 ADO.NET Visual Studio Visual Studio 2-4-2 連結到 Access 資料庫檔案 Access2-16 StepVisual Studio StepMicrosoft Access 49
Step StepMDB AccessAdmin Step05 1 2 3 Visual Studio 2850 5 2-16Access 4 2-4-3 連結到 Excel 檔案 ExcelOLE DBODBCOLE DB2-17 StepVisual Studio Step<>
Step.NET Framework Data Provider for OLE DB Step Step05OLE DB Microsoft Jet 4.0 OLE DB Provider Step06Excelc:\ database\employee.xls Step07 Step08Extended PropertiesExcel 8.0;HDR=Yes;IMEX=1 Step09 Step10 1 5 2 6 4 3-4 3.5 精研講座 ADO.NET 7 8 9 2-17OLE DBExcel 51
Excel 2-18 2-18Excel Visual Studio 2852 2-4-4 連結到文字檔案 Tab,CSV Databaseemp1.txtCDatabase,, 1,, 2,, 3,,... 2-19 Step StepMicrosoft ODBC Step StepDriver={Microsoft Text Driver (*.txt; *.csv)};dbq=c:\databasec DataBase
1 4 2 2-19 2-20 3-4 3.5 精研講座 ADO.NET 2-20 Step By Step 2-4 使用 ODBC 連結到各種資料來源 ODBCOLE DB ODBC ODBC 53
Step (ODBC) ODBC Visual Studio 2854 Step2-21 SQL Server SQL Server 25SQL Server 25 Express 1 2 3 2-21ODBC Step2-22 SQLExp SQL ServerSQL Server 25 Express(local)SQL Server 25(local)\ sqlexpresssql Server 25 Express
2-22SQL Server Step 2-23 SQL Sever 2-23Windows -4 3.5 精研講座 ADO.NET Step05 Northwind 2-24 2-24 55
Step06 ODBC SQLExp 2-25 Step07Visual Studio 28 2-26 2-25 Visual Studio 2856 Microsoft ODBC SQLExp 1 4 2 3 2-26ODBC
2-4-5 連結到 Oracle 伺服器 OracleOracleOracle Client8.1.7 Oracle Database 10g Express Client(OracleXEClient.exe) http://www.oracle.com/technology/software/products/ database/xe/htdocs/1xewinsoft.html Oracle StepVisual Studio2-27 Oracle.NET Framework Data Provider for Oracle orcl -4 3.5 精研講座 ADO.NET 1 4 2 5 3 2-27Oracle 57
StepOracle Visual Studio 2858 Visual Studio 28OracleOracle Oracle Developer Tools for Visual Studio.NET with Oracle10g Release 2 ODAC 10.2.0.2.21 http://www.oracle.com/technology/software/tech/windows/odpnet/index.html Oracle11gOracle Data Access Components (ODAC) ODAC 11g Beta 2 11.1.0.6.10 http://www.oracle.com/technology/tech/dotnet/index.html Oracle Developer Tools for Visual Studio.NETOracle Visual StudioOracle ExplorerOracle 2-5 Step 2-28
2-28 Step 2-29 Products 2-30 2-29 -5 3.5 精研講座 ADO.NET (Aggregate Function) SQLSQL 59
圖表窗格 準則窗格 SQL 窗格 結果窗格 Visual Studio 2860 2-5-1 建立 SQL 查詢 2-30 SQL NorthwindProductsProductIDProductName ProductsProductID ProductNameSQL SQL SQL 2-31SQL
Categories Categories 查詢設計工具 SQL2-32 2-32 -5 3.5 精研講座 ADO.NET 建立含關聯的查詢 ProductsCategoriesVisual Studio 2-33 2-33 61
ProductsProductIDProductNameCategories CategoryNameSQL SELECT Products.ProductID, Products.ProductName, Categories.CategoryName FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID 2-34 Visual Studio 2862 2-34 (LEFT OUTER JOIN) Products 2-35 設定左外部聯結 點選一次選取再點選一次取消 2-35 SQL SELECT Products.ProductID, Products.ProductName, Categories.CategoryName FROM Products LEFT OUTER JOIN Categories ON Products.CategoryID = Categories.CategoryID
Categories 2-36 2-36 SELECT Products.ProductID, Products.ProductName, Categories.CategoryName FROM Products RIGHT OUTER JOIN Categories ON Products.CategoryID = Categories.CategoryID CategoriesProducts 2-37 2-37 -5 3.5 精研講座 ADO.NET SQL SELECT Products.ProductID, Products.ProductName, Categories.CategoryName FROM Products FULL OUTER JOIN Categories ON Products.CategoryID = Categories.CategoryID 自訂彙總資料行 ProductsProducts UnitPriceUnitsInStock 63
UnitPrice * UnitsInStock Total2-38 Visual Studio 2864 2-38 SQL SELECT Products.ProductID, Products.ProductName, Categories.CategoryName, Products.UnitPrice * Products.UnitsInStock AS Total FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID 2-39 2-39 分組統計 SQLGroup By ProductsProductID ProductName 2-40TotalSum CountAvg
2-40 SQL SELECT Categories.CategoryName, SUM(Products.UnitPrice * Products.UnitsInStock) AS Total FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID GROUP BY Categories.CategoryName 2-41 2-41 建立參數 -5 3.5 精研講座 ADO.NET S Q L 2-42CategoryName @CategoryNameSQL Server.NET Framework Data Provider for OLE DB? 65
Visual Studio 2866 2-42 SQL SELECT Categories.CategoryName, SUM(Products.UnitPrice * Products.UnitsInStock) AS Total FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID GROUP BY Categories.CategoryName HAVING (Categories.CategoryName = @CategoryName) Seafood 2-43 SQLSQL 總結 2-43 Visual Studio SQL