Module 9: Accessing Relational Data Using Microsoft Visual Studio .NET

1. Module 9:Accessing Relational Data Using Microsoft Visual Studio .NET

2. Overview • Overview of ADO.NET • Creating a Connection to a Database • Displaying a DataSet in a List-Bound Control

3. Lesson: Overview of ADO.NET • What is ADO.NET? • Using Namespaces • The ADO.NET Object Model • What is a DataSet? • Accessing Data with ADO.NET • Practice: Identifying ADO.NET Components

4. 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

5. Using Namespaces • Use the Imports or using 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 using System.Data; using System.Data.SqlClient;

6. 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)

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. Practice: Identifying ADO.NET Components • Students will: • Move icons of various components of ADO.NET to the correct locations • Time: 5 Minutes

10. Lesson: Creating a Connection to a Database • Using Server Explorer to Generate a Connection • The DataAdapter Object Model • Demonstration: Connecting to a Database • Generating a DataSet • Demonstration: Generating a DataSet

11. Using Server Explorer to Generate a Connection • Create a new data connection using the Data Links dialog box • Create a new data connection by dragging a Table from Server Explorer

12. 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

13. Demonstration: Connecting to a Database • Expand Server Explorer to a table in a SQL Server database • Drag the table to an ASP.NET Web Form • View the data using the SqlDataAdapter • View the source of the page that was created • Configure the DataAdapter object

14. 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() DataSet ds = new DataSet(); DataAdapter1.Fill(ds) DataAdapter2.Fill(ds) DataAdapter1.Fill(ds); DataAdapter2.Fill(ds);

15. Demonstration: Generating a DataSet • Create a typed DataSet from a DataAdapter • Add a second DataTable from a different DataAdapter • Show the schema of DataSet

16. Lesson: Displaying a DataSet in a List-Bound Control • What are List-Bound Controls? • Displaying DataSet Data in List-Bound Controls • Demonstration: Binding List-Bound Controls to a Database • Practice: Using a DataGrid • Demonstration: Customizing the DataGrid Control

17. 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

18. 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() DataAdapter1.Fill(ds); lstEmployees.DataBind();

19. Demonstration: Binding List-Bound Controls to a Database • Add a DataGrid to a Web Form • Set the DataSource and DataMember properties • Fill the DataSet • Call DataBind()

20. Practice: Using a DataGrid • Students will: • Create a SqlConnection • Create a SqlDataAdapter • Generate a DataSet • Place a DataGrid on a Web Form • Bind the DataGrid to the DataSet • Time: 5 minutes

21. Demonstration: Customizing the DataGrid Control • Using AutoFormat • Setting custom column headers • Paging • Sorting

22. Review • Overview of ADO.NET • Creating a Connection to a Database • Displaying a DataSet in a List-Bound Control

23. Lab 9: Accessing Relational Data Using Microsoft Visual Studio .NET Logon Page Login.aspx BenefitsHome PageDefault.aspx CohoWinery Page HeaderHeader.ascx ASPState Menu ComponentClass1.vb or Class1.cs Registration Register.aspx Web.config tempdb Life InsuranceLife.aspx RetirementRetirement.aspx MedicalMedical.aspx DentalDental.aspx XML Web ServicedentalService1.asmx ProspectusProspectus.aspx DoctorsDoctors.aspx User Controlnamedate.ascx Lab Web Application XML Files Doctors Dentists