1 / 27

Session 8 : ADO.NET

Session 8 : ADO.NET. Overview. Overview of ADO.NET What is ADO.NET? Using Namespaces The ADO.NET Object Model What is a DataSet? Accessing Data with ADO.NET Creating a Connection to a Database Displaying a DataSet in a List-Bound Control Accessing Data with DataSets

eze
Télécharger la présentation

Session 8 : ADO.NET

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. Session 8:ADO.NET

  2. Overview • Overview of ADO.NET • What is ADO.NET? • Using Namespaces • The ADO.NET Object Model • What is a DataSet? • Accessing Data with ADO.NET • Creating a Connection to a Database • Displaying a DataSet in a List-Bound Control • Accessing Data with DataSets • Accessing Data with DataReaders • Using Multiple Tables

  3. What is ADO.NET? ADO.NET provides a set of classes for working with data. ADO.NET provides: • An evolutionary, more flexible successor to ADO • A system designed for disconnected environments • A programming model with advanced XML support • A set of classes, interfaces, structures, and enumerations that manage data access from within the .NET Framework

  4. Using Namespaces • Using the Imports statement to import namespaces • Namespaces used with ADO.NET include: • System.Data • System.Data.SqlClient • System.Data.OleDb Imports System.Data Imports System.Data.SqlClient

  5. The ADO.NET Object Model DataSet DataTable DataTable SqlDataAdapter OleDbDataAdapter SQL Server .NET Data Provider OLE DB .NET Data Provider OleDbConnection SqlConnection SQL Server 7.0 (and later) OLEDB sources(SQL Server 6.5)

  6. Using the ADO.NET Object Model

  7. What is a Dataset? DataSet DataTable DataTable DataTable SqlDataAdapter Web server memory Physical storage SqlConnection OleDbDataAdapter OleDbConnection SQL Server 2000 OleDb Database

  8. DataSet Accessing Data with ADO.NET Database 1 Client makes request Create the SqlConnection and SqlDataAdapter objects 2 Fill the DataSet from the DataAdapter and close the connection SqlConnection 3 Web server Return the DataSet to the Client 4 SqlDataAdapter Client manipulates the data 5 Update the DataSet 6 Use the SqlDataAdapter to open the SqlConnection, update the database, and close the connection 7 List-Bound Control Client

  9. The DataAdapter Object Model DataSet DataAdapter SelectCommand UpdateCommand InsertCommand DeleteCommand DataReader Command Command Command Command Connection sp_SELECT sp_UPDATE sp_INSERT sp_DELETE Database

  10. Generating a DataSet • You can generate a DataSet… • …through the UI… Creates a DataSet that allows you to access data as an object • …or through code… • and then fill… Dim ds As New DataSet() DataAdapter1.Fill(ds) DataAdapter2.Fill(ds)

  11. What are List-Bound Controls? • Controls that connect to a data source and display the data • List-bound controls include the following: • DropDownList • ListBox • CheckBoxList • RadioButtonList • DataGrid • DataList • Repeater

  12. Property Description DataSource • The DataSet containing the data DataMember • The DataTable in the DataSet DataTextField • The field in the DataTable that is displayed DataValueField • The field in the DataTable that becomes the value of the selected item in the list Displaying DataSet Data in List-Bound Controls • Set the properties • Fill the DataSet, then call the DataBind method DataAdapter1.Fill(ds) lstEmployees.DataBind()

  13. Using DataSets vs. DataReaders DataSet DataReader Read/write access to data Read-only Includes multiple tables from different databases Based on one SQL statement from one database Disconnected Connected Bind to multiple controls Bind to one control only Forward and backward scanning of data Forward-only Slower access Faster access Supported by Visual Studio .NET tools Manually coded

  14. What is a DataReader? • Forward-only, read-only • Fast access to data • Connected to a data source • Manage the connection yourself • Manage the data yourself, or bind it to a list-bound control • Uses fewer server resources

  15. Creating a DataReader • To use a DataReader: • Create and open the database connection • Create a Command object • Create a DataReader from the Command object • Call the ExecuteReader method • Use the DataReader object • Close the DataReader object • Close the Connection object • Use Try…Catch…Finally error handling 1 2 3 4 5 6 7

  16. Reading Data from a DataReader • Call Read for each record • Returns false when there are no more records • Access fields • Parameter is the ordinal position or name of the field • Get functions give best performance • Close the DataReader • Close the connection Do While myReader.Read() str &= myReader(1) str &= myReader("field") str &= myReader.GetDateTime(2) Loop

  17. Binding a DataReader to a List-Bound Control • Create the Control • Bind to a DataReader <asp:DataGrid id="dgAuthors" runat="server" /> dgAuthors.DataSource = dr dgAuthors.DataBind()

  18. SQL Server Security Send the username and password in clear text. Web ServerDefault ASP.NET settings Mixed modeauthentication Here is the username and password SQL ServerEach user account addedto SQL Server logins group Do not send the username and password. Just send that the user has been authenticated. or… Client Windows onlyauthentication SQL ServerOnly ASPNET accountis granted access Web ServerWindows authentication

  19. Creating the Connection • Using SqlConnection • Setting connection string parameters • Connection timeout • Data source • Initial catalog • Integrated security Dim strConn As String = "data source=localhost; " & _ "initial catalog=northwind; integrated security=true" Dim conn As New SqlConnection(strConn) • Password • Persist security info • Provider • User ID

  20. Creating a DataAdapter • Store the query in a DataAdapter • The DataAdapter constructor sets the SelectCommand property • Set the InsertCommand, UpdateCommand, and DeleteCommand properties if needed Dim da As New SqlDataAdapter _ ("select * from Authors", conn) da.SelectCommand.CommandText da.SelectCommand.Connection

  21. Creating a DataSet • Create and populate a DataSet with DataTables • Fill method executes the SelectCommand • Access a DataTable Dim ds As New DataSet() da.Fill(ds, "Authors") ds.Tables("Authors").Rows.Count Dim r As DataRow Dim str As String For Each r in _ ds.Tables("Authors").Rows str &= r(2) str &= r("au_lname") Next

  22. Using a DataView • A DataView can be customized to present a subset of data from a DataTable • The DefaultView property returns the default DataView of the table • Setting up a different view of a DataSet Dim dv As DataView = ds.Tables("Authors").DefaultView Dim dv As New DataView (ds.Tables("Authors")) dv.RowFilter = "state = 'CA'"

  23. Binding a DataSet to a List-Bound Control • Create the control • Bind to a DataSet or a DataView <asp:DataGrid id="dg" runat="server" /> dg.DataSource = ds dg.DataMember = "Authors" dg.DataBind()

  24. Storing Multiple Tables • Add the first table • Add the subsequent table(s) daCustomers = New SqlDataAdapter _ ("select * from Customers", conn1) daCustomers.Fill(ds, "Customers") daOrders = New SqlDataAdapter _ ("select * from Orders", conn2) daOrders.Fill(ds, "Orders") Customers conn1 conn2 DataSet Orders

  25. Creating Relationships • Identify parent column • Identify child column • Create DataRelation Dim parentCol As DataColumn = _ ds.Tables("Customers").Columns("CustomerID") Dim childCol As DataColumn = _ ds.Tables("Orders").Columns("CustomerID") parentCol DataRelation Customers table Dim dr As New DataRelation _ ("name", parentCol, _ childCol) ds.DataRelations.Add(dr) childCol DataSet Orders table

  26. Programmatically Navigating Between Tables Using Relationships ds.Tables(index).Rows(index).GetChildRows("relation") ds.Tables(index).Rows(index).GetParentRow("relation") Orders Customers GetChildRows GetParentRow DataSet

  27. Visually Navigating Between Tables Using Relationships Dim tableView As DataView Dim currentRowView As DataRowView tableView = New DataView(ds.Tables("Customers")) currentRowView = tableView(dgCustomers.SelectedIndex) dgChild.DataSource = currentRowView.CreateChildView("CustOrders") Customers Orders DataRowView DataView CreateChildView DataSet

More Related