module 3 using ado net to access data n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Module 3: Using ADO.NET to Access Data PowerPoint Presentation
Download Presentation
Module 3: Using ADO.NET to Access Data

Loading in 2 Seconds...

play fullscreen
1 / 33

Module 3: Using ADO.NET to Access Data - PowerPoint PPT Presentation


  • 94 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

Module 3: Using ADO.NET to Access Data


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
overview
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
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

.ASPX Page

DataReader

Command

Company: Northwind Traders

Database

Connection

DataSetCommand

DataView

DataSet

List-Bound Control

.ASPX Page

The ADO.NET Object Model
recordsets vs datasets
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
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
connecting to a data source
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
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
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")

storing multiple tables in a dataset
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

using dataviews
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'"

displaying data in the datagrid control
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()

using templates

HeaderTemplate

ItemTemplate

SeparatorTemplate

AlternatingItemTemplate

FooterTemplate

Using Templates
using the repeater control
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>

using stored procedures
Using Stored Procedures
  • Calling Stored Procedures
  • Passing Parameters
  • Calling Action Stored Procedures
  • Demonstration: Calling Stored Procedures
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 SQLDataSetCommand

cmd = New SQLDataSetCommand()

With cmd.SelectCommand

.ActiveConnection = conn

.CommandText = "ProductCategoryList"

.CommandType = CommandType.StoredProcedure

End With

cmd.FillDataSet(ds, "Categories")

passing parameters
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")

calling action stored procedures
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

accessing data with datareaders
Accessing Data with DataReaders
  • Creating a DataReader
  • Reading Data from a DataReader
  • Demonstration: Accessing Data Using DataReaders
  • Using DataSets vs. DataReaders
creating a datareader
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)

reading data from a datareader
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()

using datasets vs datareaders
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
binding to xml data
Binding to XML Data
  • Overview of XML
  • Reading XML Data into a DataSet
  • Demonstration: Reading XML Data into a DataSet
overview of xml
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
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)

review
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