1 / 43

.NET Data Access

.NET Data Access. 台南市教育局網路中心 在職教師校務行政系統程式開發 進階班 行政網路組 asp@mail.tn.edu.tw. Design Goals for ADO.NET. Leverage Current ADO Knowledge Support the N-Tier Programming Model Integrate XML Support. Why ADO.NET? ADO vs. ADO.NET. ADO.NET Disconnected access Logical data model

marcy
Download Presentation

.NET Data Access

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. .NET Data Access 台南市教育局網路中心在職教師校務行政系統程式開發進階班行政網路組 asp@mail.tn.edu.tw

  2. Design Goals for ADO.NET • Leverage Current ADO Knowledge • Support the N-Tier Programming Model • Integrate XML Support

  3. Why ADO.NET?ADO vs. ADO.NET • ADO.NET • Disconnected access • Logical data model • The DataSet based data • DataSet is multi-table • > 1 table or source does not require a JOIN • Relationships remain: navigation is relational • XML schema Data types • No data type conversions required • XML is plaintext: ‘Firewall friendly’ • ADO • Connected access • Physical data model • RecordSet based data • RecordSet is ‘one’ table • > 1 table requires a database JOIN • Data is “flattened”: lose relationships • COM/COM+ data types • Data via COM marshalling • Blocked by firewalls (DCOM, binary)

  4. ADO.NET Architecture() System.Data DataSet System.Data.SqlClient System.Data.OleDb System.Data.SQLTypes .NET Data Providers

  5. ADO.NET Architecture(2) • XML and ADO.NET • ADO.NET Components

  6. .NET Data Provider • Retrieve data from a data source and reconcile changes made to it with the data source • The link between data source and application • Two Providers out of the box: • .NET SQL Data Provider: SqlClient • .NET OLE DB Data Provider: OleDb

  7. ADO.NET Data ProviderCore Classes System.Data.<Provider> Connection Connect to data source Execute Command against data source Command Reads a forward-only read-only stream of data from a data source Data Reader Populates a DataSet and resolves updates with data source Data Adapter

  8. Connection Class • Represent a unique session with a data source • Create, open, close a connection to a data source • Functionality and methods to perform transactions • Connection example: String conStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=NWIND_RW.MDB";OleDbConnection aConn = new OleDbConnection(conStr);aConn.Open(); // Execute Queries using OleDbCommand Class aConn.Close();

  9. Connection • OleDbConnection Provider=MSDAORA; Data Source=ORACLE8i7; User ID=OLEDB; Password=OLEDB Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\bin\LocalAccess40.mdb; Provider=SQLOLEDB;Data Source=MySQLServer;Integrated Security=SSPI; • SqlConnection user id=sa;password=aU98rrx2;initial catalog=northwind;data source=mySQLServer;Connect Timeout=30

  10. Command Class • Represents a query to execute on the data source • Methods of interest: • ExecuteNonQuery: Executes a SQL statement that doesn’t return any rows • ExecuteReader: Returns a DataReader • ExecuteScalar: Executes the query and returns the first column of the first row String SqlStr = “INSERT INTO Customers (CustId,FName,Lname) ;” + “Values (‘Matt’,’Stephen’)”; SqlCommand myCommand = new SqlCommand(SqlStr , myConnection); myCommand.Connection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close();

  11. DataAdapter Class • Bridge between the DataSet and the data store • Means to modify the DataSet and data source • Properties of Interest: DeleteCommand, InsertCommand, SelectCommand, UpdateCommand • TableMappings: Maps source and DataTable • Inherits methods from DbDataAdapter class • public virtual int Fill(DataSet,”Tablename”); • public virtual int update(DataSet,”Tablename”); DataAdapter DataSet Data Store

  12. DataReader • Forward-only data access • “Lightweight” programming model • Less overhead than using Command • Instantiated by the Command class Execute method • Ties up the Command until it is finished reading • Methods to retrieve data: • By index and optionally column type: GetString, GetInt32, and so on • Read: Advances reader to next record

  13. DataReader Sample // Code for creating the Connection “Conn” not shown String myQuery = “SELECT * FROM Customers”; Conn.Open(); OleDbCommand myCmd = new OleDbCommand( myQuery, Conn ); // Declare the DataReader... OleDbDataReader myDataReader; // Instantiate the DataReader with ExecuteReader(...) ... myDataReader = myCmd.ExecuteReader(); // Always call Read before accessing data. while(myDataReader.Read()) { Console.WriteLine(myDataReader.GetString(0)); }// Always Close the reader and the connection when done myDataReader.Close(); Conn.Close();

  14. System.Data Principal Classes Contains the ‘main’ classes of ADO.NET System.Data DataSet In-memory cache of database DataTable In-memory cache of database table Used to manipulate a row in a DataTable DataRow Used to define the columns in a DataTable DataColumn DataRelation Used to relate two DataTables to each other DataView Used to create a view on a DataSet

  15. DataSet Tables DataTable DataRow(s) DataColumn Constraint(s) Relations DataRelation DataRelation System.Data: DataSetOverview DataView DataView DataTable DataTable

  16. System.Data: DataSet • An in-memory cache of data from a data source via DataAdapter • XML used to read and write data and schema • Common way to represent and manipulate data • Universal data container • Not just for use with databases • Logical or physical representation of data, depending on: • The query/result set • Whether DataTables and Relations exist • Designed to be disconnected from the data source • Connect, execute query, disconnect

  17. System.Data: DataTable • May be mapped to a physical table in the data source • Can be related to one another through DataRelations • Optimistic concurrency / locking • Properties of Interest: • Columns: Returns DataColumnCollection of DataColumns • Rows: Returns DataRow objects as a DataRowCollection • Constraints: Returns the table’s ConstraintCollection

  18. System.Data: DataSet & DataTable DataSet ds = new DataSet(); // Create DataTable object: “Customers”. DataTable dt = new DataTable( “Customers” ); // Create and add columns to the table // 1. Explicitly create and Add a DataColumn DataColumn dc = new DataColumn( “CustID”, typeof(Int16) ); dt.Columns.Add( dc ); // 2. Implicitly Create and Add columns (DataColumn). dt.Columns.Add( “First_Name”, typeof(String) ); dt.Columns.Add( “Last_Name”, typeof(String) ); // Add the DataTable object to the DataSet ds.Tables.Add( dt ); • Create a DataTable and add it to a DataSet

  19. 如何存取資料庫 • ADO .NET 資料存取模型 • 資料庫連線 (Connection) • SqlConnection 與 OleDbConnection • 執行 SQL 語法 (Commands) • SqlCommand 與 OleDbCommand • ExecuteNonQuery (不傳回資料列) • ExecuteScalar (傳回單一列資料) • ExecuteReader (傳回多列資料) • 取得資料 • DataReader • DataAdapter 與 DataSet

  20. 如何使用資料庫連線 • 建立資料庫連線物件 • 設定連線字串 • 開啟資料庫連線 • : • 關閉資料庫連線 • 使用資料庫連線的步驟 • OleDbConnectionconn = new OleDbConnection() ; • conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin; Password=123; Data Source=C:\Northwind.mdb" ; • conn.Open() ; • conn.Close() ;

  21. Connection • 只能夠連接 Microsoft SQL Server • 效能最佳化 • 需引用 System.Data.SqlClient • 可以透過 OLE DB 連接不同的資料庫 • 應用範圍最廣 • 需引用 System.Data.OleDb • 一樣可以連接 Microsoft SQL Server • SqlConnection • conn.ConnectionString = "User ID=sa; Password=; Server=localhost;Database=Northwind" ; • OleDbConnection • conn.ConnectionString = "Provider=SQLOLEDB;User ID=sa; Password=; Data Source=localhost;Initial Catalog=Northwind" ;

  22. Command • SqlCommand 與 OleDbCommand • 支援多種 SQL 命令 • DCL : GRANT, REVOKE, DENY • DDL : CREATE, ALTER, DROP • DML : SELECT, INSERT, DELETE, UPDATE • 其它 : Stored Procedure, CURSOR, … • 允許加入參數 (Parameters)

  23. 執行 SQL 語法 • 建立一個 Command 物件 • 建立參數物件 • 設定參數值 • 將參數加入 Command 物件 • 執行 SQL 語法 • 執行非查詢式 SQL 語法 • SqlCommand cmd = new SqlCommand("update… where OrderID=@ID", conn) ; • SqlParameter param = new SqlParameter("@ID", typeof(int)) ; • param.Value = 12 ; • cmd.Parameters.Add(param) ; • int rowEffects = cmd.ExecuteNonQuery() ;

  24. 如何執行 SQL 語法 • 建立一個 Command 物件 • 建立參數物件 • 設定參數值 • 將參數加入 Command 物件 • 執行 Command 並取得結果 • 執行查詢式 (單筆記錄) SQL 語法 • stringsql = "select cid from… where OrderID=@ID" ; • SqlCommand cmd = new SqlCommand(sql, conn) ; • SqlParameter param = new SqlParameter("@ID", typeof(int)) ; • param.Value = 12 ; • cmd.Parameters.Add(param) ; • int myCID = (int) cmd.ExecuteScalar() ;

  25. 如何處理多筆記錄 • 建立一個 Command 物件 • 執行 Command 並取得 DataReader • 讀取下一筆記錄 (如果 true 表示有資料){取得第一個欄位 取得第二個欄位} • 執行查詢式 (多筆記錄) SQL 語法 • stringsql = "select PName, Qty from MyOrders" ; • SqlCommand cmd = new SqlCommand(sql, conn) ; • SqlDataReader dr= cmd.ExecuteReader() ; • while ( dr.Read() ){stringpname = dr.GetString(0) ;intqty = dr.GetInt32(1) ; Console.WriteLine("{0}, {1}", pname, qty) ;}

  26. 如何執行 Stored Procedure • 建立一個 Command 物件 • 設定使用連線 • 設定命令名稱 • 設定 CommandType • 建立參數 • 執行 Stored Procedure • 執行 Stored Procedure • SqlCommand cmd = new SqlCommand() ; • cmd.Connection = conn ; • cmd.ComandText = "DeleteOrder" ; • cmd.CommandType = CommandType.StoredProcedure ; • cmd.Parameters.Add("@ID", orderID) ; • int rowEffects = cmd.ExecuteNonQuery() ;

  27. Dim sqlconn As SqlConnection = New SqlConnection(connectionstring)a Dim sqlcomm As SqlCommand = New SqlCommand() sqlcomm.CommandType = CommandType.StoredProcedure sqlcomm.CommandText = "DemoProc“ ‘Open Connection sqlconn.Open() sqlcomm.Connection = sqlconn 'Create parameters sqlcomm.Parameters.Add(New SqlParameter("@input1", SqlDbType.Int)) sqlcomm.Parameters.Add(New SqlParameter("@input2", SqlDbType.Int)) sqlcomm.Parameters.Add(New SqlParameter("@output1", SqlDbType.Int)).Direction = ParameterDirection.Output ‘Set Parameter Values sqlcomm.Parameters.Item("@input1").Value = 123 sqlcomm.Parameters.Item("@input2").Value = 456 sqlcomm.ExecuteNonQuery() ‘Access Output Response.write (sqlComm.Parameters.Item(“@output1”).Value.ToString()) Data Access Layer Calling stored procedures

  28. WebForms 控制項資料繫結 <%@ Import Namespace="System.Data.SqlClient" %> <html><head><script language="C#" runat=server> public void Page_Load(Object sender, EventArgs args) { // 建立資料庫連線，並取得 DataReader SqlConnection cnn = new SqlConnection("server=localhost;uid=sa;"); conn.Open(); SqlCommand cmd = new SqlCommand("select * from customers", conn); SqlDataReaderdr = cmd.ExecuteReader(); // 將資料來源繫結到控制項 dgCustomers.DataSource = dr; dgCustomers.DataBind(); } </script></head><body> <asp:DataGrid id="dgCustomers" runat="server"/> </body></html>

  29. 自訂 Data Provider • ADO .NET 共有那些 Data Provider • SQL Data Provider • OLE DB Data Provider • ODBC Data Provider (外掛) • Oracle Data Provider (未來)

  30. SelectCommand DataSet 資料庫 InsertCommand UpdateCommand DeleteCommand DataAdapter 與 DataSet • DataSet 與資料庫之間透過 DataAdapter 進行存取動作 DataAdapter Fill  Update 

  31. 如何取得 DataSet • 建立一個 Command用來執行 SELECT 語法 • 建立 DataAdapter 物件 • 設定 SelectCommand • 建立一個 DataSet 物件 • 使用 DataAdapter 物件的 Fill 方法填滿 DataSet • SqlDataAdapter da = new SqlDataAdapter("select * from Customers", conn) ; • 透過 DataAdapter 取得 DataSet 資料 • SqlCommand cmd = new SqlCommand() ;cmd.Connection = conn ;cmd.ComandText = "select * from Customers" ; • SqlDataAdapter da = new SqlDataAdapter() ; • da.SelectCommand = cmd; • DataSet ds = new DataSet("Customers") ; • da.Fill(ds) ;

  32. Tables DataTable DataRow Rows 0 1 2 3 姓名 性別 電話 血型 王小明 男 (04)2222-2222 AB DataRowrow = ds.Tables[0].Rows[3]; string name = row[0]; string phone = row["電話"]; DataSet 的結構 • 透過欄位號碼或欄位名稱來存取 DataRow 的資料 • 每一個 DataRow (每一筆記錄) 中包含了多個欄位 • DataTable 可以包含多筆資料列 (Row)，組成 Rows 集合 • 使用 Tables[表格號碼] 或 Tables["表格名稱"] 來引用 Tables 中的任何一個表格。(Tables.Count可以取得表格數目) • Rows 集合中的每一筆資料記錄都是一個 DataRow 物件 • 使用 Rows[列號碼] 來引用 Tables 中的任何一個表格。(Rows.Count可以取得資料總筆數) • 每一個 DataSet 中的表格都是一個 DataTable 物件 DataSet • DataSet 可以包含多個表格 (Table)，組成 Tables 集合 Tables[0] Tables[1] Tables[2] Tables[3] : : : : : Rows[0] Rows[1] Rows[2] : : : :

  33. 如何瀏覽 DataSet 中的資料 • 使用 for 迴圈瀏覽 DataSet 資料for(intc=0;c < ds.Tables[0].Columns.Count; c++) { DataColumn dc = ds.Tables[0].Columns[c] ; Console.Write("{0}\t", dc.ColumnName) ;}for(intr = 0;r < ds.Tables[0].Rows.Count;r++) {stringcName = ds.Tables[0].Rows[r]["客戶名稱"] ;stringcAddress = ds.Tables[0].Rows[r]["地址"] ;Console.WriteLine("\n{0}\t{1}", cName, cAddress) ;} • 使用 foreach 迴圈瀏覽 DataSet 資料foreach(DataRowcust in ds.Tables[0].Rows){ Console.WriteLine("\n客戶: {0}", cust["Name"]);foreach(DataRoword in cust.GetChildRows("odr")) { Console.Write("訂單編號: {0}", ord["id"]); Console.Write("\t數量:{1}\n", ord["qty"]); }}

  34. Data, XML, And .NETXML Recap • XML declaration • Document element • Elements • Attributes • Text content • Namespaces <?xml version="1.0"?> <employees xmlns="urn:…"> <employee id="123"> <name>Philips</name> <salary>145000</salary> </employee> … </employees>

  35. Plus… .NET XML Classes Namespaces and Classes System.Xml namespace XmlDocument XmlReader XmlWriter XmlElement XmlNavigator XmlAttribute etc. XPath engine System.Xml.XPath XSLT transforms System.Xml.Xsl

  36. .NET XML Classes Reading XML Data • Use XmlReader class • Fast, forwards-only, read-only cursor • “Pull” model (not the “push” model of SAX) • XmlReader is an abstract class • Several concrete subclasses XmlReader { Abstract class } XmlNodeReader Read an in-memory DOM tree XmlTextReader Read a text-based stream

  37. .NET XML Classes Using XmlReader • Example • Capabilities • Pull nodes you want, skip those you don't • Get detailed node information • Get typed data XmlReader reader; reader = new XmlTextReader("MyFile.xml"); while(reader.Read()) { …process nodes… }

  38. .NET XML Classes Writing XML Data XmlWriter • Use XmlWriter • Alternatively… • Create content using XmlDocument • Serialise data using XmlWriter XmlNodeWriter XmlTextWriter XmlWriter writer = new XmlTextWriter(…); writer.WriteStartDocument(); writer.WriteStartElement("name", "Mike");

  39. .NET XML Classes Using XPath • XPath maps an XML document to a tree of nodes • Use XPath expressions to identify, select, and manipulate nodes in an XML hierarchy • Use location paths to select nodes • XPath operators and functions

  40. .NET XML Classes XML .NET Support for XPath • XmlNavigator class provides XPath support • Evaluate XPath expressions • Select nodes using XPath XmlNavigator nav; nav = new DocumentNavigator(document); nav.Evaluate("count(//employee)"); nav.Select("//employee[salary > 75000]");

  41. XML document HTML document (XML-conformant) + XSLT processor XSLT style sheet XML document XML document (different grammar) + XSLT processor XSLT style sheet .NET XML Classes Using XSLT

  42. .NET XML Classes XML .NET Support for XSLT • XslTransform class represents an XSLT style sheet // Load style sheet XslTransform xsl = new XslTransform(); xsl.Load("MyStylesheet.xsl"); // Perform transformation xsl.Transform(aNavigator, null, anXmlWriter);

  43. Controls,Designers,Code-gen, etc. XSL/T, X-Path,Validation, etc. DataSet XmlData-Document Sync DataReader XmlReader DataAdapter XmlText- Reader XmlNode- Reader SqlData- Reader SqlData- Adapter OleDbData- Reader OleDbData- Adapter XML and ADO.NET Unified Architecture

More Related