module 3: using ado.net to access data

Module 3: Using ADO.NET to Access

Data

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

Overview of ADO.NET

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

The ADO.NET Object Model

DataSetCommandConnection

DatabaseDatabase

Command

.ASPX Page

List-Bound

Control

DataReader

Company: Northwind Traders

.ASPX Page

DataView

DataSet

Animation: Using ADO.NET to Access Data

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

Using Namespaces

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

<%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SQL" %>

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)

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

Using DataSets to Read Data

Dim cmdAuthors As SQLDataSetCommand cmdAuthors = New SQLDataSetCommand _

("select * from Authors", conn)

Create the Database Connection Store the Query in a DataSetCommand

Create and Populate the DataSet with DataTablesDim ds As DataSetds = New DataSet()cmdAuthors.FillDataSet(ds, "Authors")

Storing Multiple Tables in a DataSet

Add the First Table

Add the Subsequent Table(s)

command = New SQLDataSetCommand _ ("select * from Authors", conn)command.FillDataSet(ds, "Authors")

command.SelectCommand = New SQLCommand _ ("select * from Books", conn)command.FillDataSet(ds, "Books")

Authors

Books

DataSet

Data Tables

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 DataSetDim dv as DataViewdv = New DataView (ds.Tables("Authors"))dv.RowFilter = "state = 'CA'"

Dim dv as DataViewdv = ds.Tables("Authors").DefaultView

Displaying Data in the DataGrid Control

Create the Control

Bind to a DataViewdgAuthors.DataSource=ds.Tables("Authors").DefaultViewdgAuthors.DataBind()

<asp:DataGrid id="dgAuthors" runat="server" />

Demonstration: Displaying Data in a DataGrid

Using Templates

FooterTemplate

HeaderTemplate

SeparatorTemplate

AlternatingItemTemplate

ItemTemplate

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>

Demonstration: Displaying Data in a Repeater Control

Using Stored Procedures

Calling Stored Procedures Passing Parameters Calling Action Stored Procedures Demonstration: Calling Stored Procedures

Calling Stored Procedures

Stored Procedures Provide Security for Database Set up the DataSetCommand

Run the Stored Procedure and Store Returned Records

Dim cmd as SQLDataSetCommandcmd = New SQLDataSetCommand()With cmd.SelectCommand

.ActiveConnection = conn

.CommandText = "ProductCategoryList"

.CommandType = CommandType.StoredProcedureEnd With

cmd.FillDataSet(ds, "Categories")

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.InputworkParam.Value = CInt(txtCatID.Text)

cmd.SelectCommand.Parameters.Add (workParam)

ds = new DataSet()cmd.FillDataSet(ds, "Products")

Calling Action Stored Procedures

Use SQLCommand Object

Call the ExecuteNonQuery Method

Retrieve Output ParameterscurSales = myCmd.Parameters("@ItemCount").Value

conn.Open()myCmd.ExecuteNonQuery()conn.Close()

Dim myCmd As SQLCommand = New SQLCommand _("OrdersCount", conn)

Demonstration: Calling Stored Procedures

Lab 3: Using ADO.NET to Access Data

Accessing Data with DataReaders

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

Creating a DataReader

Create and Open the Database Connection

Create the DataReader From a Command Object

Close the Connection

Dim cmdAuthors As SQLCommand = New SQLCommand _("select * from Authors", conn)

Dim dr As SQLDataReadercmdAuthors.Execute(dr)

Dim conn As SQLConnection = New SQLConnection _ ("server=localhost;uid=sa;pwd=;database=pubs")conn.Open()

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()

Demonstration: Accessing Data Using DataReaders

Using DataSets vs. DataReaders

DataSet1. Create a database

connection

2. Store query in DataSetCommand

3. Populate DataSet with FillDataSet method

4. Create DataView

5. Bind DataView to list-bound control

DataReader1. Create a database connection

2. Open the database connection

3. Store query in Command

4. Populate DataReader with Execute method

5. Call Read for each record, and Get for each field

6. Manually display data

7. Close the DataReader and the connection

Binding to XML Data

Overview of XML Reading XML Data into a DataSet Demonstration: Reading XML Data into a DataSet

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>

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)

Demonstration: Reading XML Data into a DataSet

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