Module 3: Using ADO.NET to Access Data

1. Module 3: Using ADO.NET to Access Data

2. Overview • Overview of ADO.NET • Connecting to a Data Source • Accessing Data with DataSets • Using Stored Procedures • Accessing Data with DataReaders • Binding to XML Data

3. Overview of ADO.NET • The ADO.NET Object Model • Animation: Using ADO.NET to Access Data • RecordSets vs. DataSets • Using Namespaces

4. .ASPX Page DataReader Command Company: Northwind Traders Database Connection DataSetCommand DataView DataSet List-Bound Control .ASPX Page The ADO.NET Object Model

5. Animation: Using ADO.NET to Access Data

6. RecordSets vs. DataSets • DataSet • Multiple Tables • Includes Relationship • Navigate via Relationship • Disconnected • Transmit XML File • Recordset • One Table • Based on Join • Move Row by Row • Connected or Disconnected • COM Marshalling

7. Using Namespaces <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SQL" %> • Use the Import Construct to Declare Namespaces • Namespaces Used with ADO.NET Include: • System.Data • System.Data.ADO • System.Data.SQL • System.Data.XML • System.Data.SQLTypes

8. Connecting to a Data Source • Using SQLConnection • Using ADOConnection Dim strConn As String = _ "server=localhost; uid=sa;pwd=; database=northwind" Dim conn As SQLConnection = New SQLConnection(strConn) Dim strConn As String = "Provider= SQLOLEDB.1; " & _ "Data Source=localhost; uid=sa; pwd=; " & _ "InitialCatalog=northwind;" Dim conn As ADOConnection = New ADOConnection(strConn)

9. Accessing Data with DataSets • Using DataSets to Read Data • Storing Multiple Tables in a DataSet • Using DataViews • Displaying Data in the DataGrid Control • Demonstration: Displaying Data in a DataGrid • Using Templates • Using the Repeater Control • Demonstration: Displaying Data in a Repeater Control

10. Using DataSets to Read Data • Create the Database Connection • Store the Query in a DataSetCommand • Create and Populate the DataSet with DataTables Dim cmdAuthors As SQLDataSetCommand cmdAuthors = New SQLDataSetCommand _ ("select * from Authors", conn) Dim ds As DataSet ds = New DataSet() cmdAuthors.FillDataSet(ds, "Authors")

11. Storing Multiple Tables in a DataSet command = New SQLDataSetCommand _ ("select * from Authors", conn) command.FillDataSet(ds, "Authors") • Add the First Table • Add the Subsequent Table(s) command.SelectCommand = New SQLCommand _ ("select * from Books", conn) command.FillDataSet(ds, "Books") Books DataSet Authors Data Tables

12. Using DataViews • DataViews Can be Customized to Present a Subset of Data from a DataTable • The DefaultView Property Returns the Default DataView for the Table • Setting Up a Different View of a DataSet Dim dv as DataView dv = ds.Tables("Authors").DefaultView Dim dv as DataView dv = New DataView (ds.Tables("Authors")) dv.RowFilter = "state = 'CA'"

13. Displaying Data in the DataGrid Control • Create the Control • Bind to a DataView <asp:DataGrid id="dgAuthors" runat="server" /> dgAuthors.DataSource=ds.Tables("Authors").DefaultView dgAuthors.DataBind()

14. Demonstration: Displaying Data in a DataGrid

15. HeaderTemplate ItemTemplate SeparatorTemplate AlternatingItemTemplate FooterTemplate Using Templates

16. Using the Repeater Control • Create the Control and Bind to a DataView • Display Data in Templated Elements <asp:Repeater id="repList" runat="server"> <template name="ItemTemplate"> <%# Container.DataItem("au_lname") %> </template> </asp:Repeater>

17. Demonstration: Displaying Data in a Repeater Control

18. Using Stored Procedures • Calling Stored Procedures • Passing Parameters • Calling Action Stored Procedures • Demonstration: Calling Stored Procedures

19. Calling Stored Procedures • Stored Procedures Provide Security for Database • Set up the DataSetCommand • Run the Stored Procedure and Store Returned Records Dim cmd as SQLDataSetCommand cmd = New SQLDataSetCommand() With cmd.SelectCommand .ActiveConnection = conn .CommandText = "ProductCategoryList" .CommandType = CommandType.StoredProcedure End With cmd.FillDataSet(ds, "Categories")

20. Passing Parameters • Create Parameter, Set Direction and Value, Add to the Parameters Collection • Run Stored Procedure workParam = New SQLParameter("@CategoryID", _ SQLDataType.Int) workParam.Direction = ParameterDirection.Input workParam.Value = CInt(txtCatID.Text) cmd.SelectCommand.Parameters.Add (workParam) ds = new DataSet() cmd.FillDataSet(ds, "Products")

21. Calling Action Stored Procedures • Use SQLCommand Object • Call the ExecuteNonQuery Method • Retrieve Output Parameters Dim myCmd As SQLCommand = New SQLCommand _ ("OrdersCount", conn) conn.Open() myCmd.ExecuteNonQuery() conn.Close() curSales = myCmd.Parameters("@ItemCount").Value

22. Demonstration: Calling Stored Procedures

23. Lab 3: Using ADO.NET to Access Data

24. Accessing Data with DataReaders • Creating a DataReader • Reading Data from a DataReader • Demonstration: Accessing Data Using DataReaders • Using DataSets vs. DataReaders

25. Creating a DataReader • Create and Open the Database Connection • Create the DataReader From a Command Object • Close the Connection Dim conn As SQLConnection = New SQLConnection _ ("server=localhost;uid=sa;pwd=;database=pubs") conn.Open() Dim cmdAuthors As SQLCommand = New SQLCommand _ ("select * from Authors", conn) Dim dr As SQLDataReader cmdAuthors.Execute(dr)

26. Reading Data from a DataReader • Call Read for Each Record • Returns false when there are no more records • Call Get for Each Field • Parameter is the ordinal position of the field • Call Close to Free Up the Connection myReader.Read() lblName.Text = myReader.GetString(1) + ", " + _ myReader.GetString(2) myReader.Close()

27. Demonstration: Accessing Data Using DataReaders

28. Using DataSets vs. DataReaders DataReader Create a database connection Open the database connection Store query in Command Populate DataReader with Execute method Call Read for each record, and Get for each field Manually display data Close the DataReader and the connection DataSet • Create a database connection • Store query in DataSetCommand • Populate DataSet with FillDataSet method • Create DataView • Bind DataView to list-bound control

29. Binding to XML Data • Overview of XML • Reading XML Data into a DataSet • Demonstration: Reading XML Data into a DataSet

30. Overview of XML • Machine-Readable and Human-Readable Data • Defines the Data Content and Structure • Separates Structure From Presentation • Allows You to Define Your Own Tags and Attributes <employee> <name>Jake</name> <salary>25000</salary> <region>Ohio</region> </employee>

31. Reading XML Data into a DataSet • Read the XML File • Read the Contents of the File Stream • Read Data From the StreamReader into a DataSet fs = New FileStream _ (Server.MapPath("schemadata.xml"), _ FileMode.Open, FileAccess.Read) Reader = New StreamReader(fs) ds.ReadXml(Reader)

32. Demonstration: Reading XML Data into a DataSet

33. Review • Overview of ADO.NET • Connecting to a Data Source • Accessing Data with DataSets • Using Stored Procedures • Accessing Data with DataReaders • Binding to XML Data