Introduction to ADO'NET - PowerPoint PPT Presentation

1 / 33
About This Presentation
Title:

Introduction to ADO'NET

Description:

Data Relation. Connection. Table. Data Columns. Data Rows. ADO.NET Architecture ... database complete with tables, rows, columns, relationships, and constraints ... – PowerPoint PPT presentation

Number of Views:59
Avg rating:3.0/5.0
Slides: 34
Provided by: jimd151
Category:

less

Transcript and Presenter's Notes

Title: Introduction to ADO'NET


1
Introduction to ADO.NET
  • Rod Paddock
  • Dash Point Software, Inc.

2
About Box
  • Rod Paddock
  • rodpaddock_at_dashpoint.com
  • President, Dash Point Software
  • Editor, CoDe Magazine
  • Microsoft VB.NET MVP
  • VB.NET, C, SQL Server Architect/Developer

3
ADO.NET Fundamentals Section Objectives
  • ADO.NET Overview
  • Data Set Objects
  • SQL Sever Data Provider
  • Connection Objects
  • Command Objects
  • Data Reader Objects
  • Data Adapter Objects
  • CRUD Operations

4
What is ADO.NET?
  • ADO.NET is the primary data access methodology in
    VB.NET
  • ADO.Net is set of classes that expose data access
    services to the .NET programmer
  • In English that means ADO.Net is what you use to
    retrieve and update data

5
ADO.NET Overview
6
Data Sets
ADO.NET Architecture
Data Adapter
Data Set
Connection
Data Relation
DeleteCommand
InsertCommand
Tables
Data Provider Components
Table
Table
SelectCommand
Data Rows
Data Rows
UpdateCommand
Data Columns
Data Columns
Your Code
Database
7
Data Sets Overview
  • A DataSet acts as a virtual database complete
    with tables, rows, columns, relationships, and
    constraints
  • You reference a DataSet and its associated tables
    in your VB.NET forms and code
  • Typically populated with the Fill method on a
    DataAdapter

8
Data Set Architecture
  • DataSets Contain DataTables
  • DataTables Contain DataRows
  • DataRows Contain DataColumns


DataTable
DataTable
DataRow DataRow DataRow
DataRow DataRow DataRow
Data Relation
DataColumn
9
Data Set Architecture
  • Tables Collection contains a reference to each
    table in the Data Set
  • Rows Collection contains a reference to each row
    in a DataTable
  • Item Collection contains a reference to each
    DataColumn in a DataRow

10
Data Set Code
Dim oDS As New DataSet() '-- create a DataTable
object Dim oTable As DataTable
oDS.Tables.Add(Students") '-- add Column
definitions to the table oTable.Columns.Add("LastN
ame", Type.GetType("System.String")) oTable.Col
umns.Add("Age", Type.GetType("System.Decimal"))
'-- create a new DataRow object Dim oRow As
DataRow oDS.Tables("Students").NewRow '--
update the values in the DataRow oRow.Item("LastNa
me") "Duffy" oRow.Item("Age") 27 -- add the
DataRow to the DataTable oDS.Tables("Students").Ro
ws.Add(oRow)
11
Data Set Demo
12
Data Providers
  • SQL Server Data Provider
  • SQL Prefix
  • OLEDB Data Provider
  • OLE Prefix
  • ODBC Data Provider
  • ODBC Prefix
  • Oracle Data Provider

13
Using Data Providers
  • Imports System.Data.SQLClient
  • Imports System.Data.OLEDB
  • Imports System.Data.ODBC
  • Imports Etc..

14
Provider Classes
15
Connection Class
ADO.NET Architecture
Data Adapter
Data Set
Connection
Data Relation
DeleteCommand
InsertCommand
Data Provider Components
Tables
Table
Table
SelectCommand
Data Rows
Data Rows
UpdateCommand
Data Columns
Data Columns
Your Code
Database
  • A connection talks to a data source (database)

16
Connection Code
Sample 1 'Define and open a connection object
Dim oConn As New SqlConnection
oConn.ConnectionString "user idsapasswordcool
"_ "data source(local)initial
catalogNorthWind" oConn.Open()
Sample 2 Dim oConn As New SqlConnection("user
idsapasswordcool"_ "data
source(local)initial catalogNorthWind") oConn.O
pen()
17
Connection Demo
18
Command Objects
  • Used to execute a data source-specific command
    against a data source
  • In English that means that its the command
    objects job to pass to the data source the
    command/stored procedure to be run/called

19
Significant Command Object Properties
  • CommandText
  • Actual code to be run
  • Command Type
  • What type of code to execute (SQL code, stored
    procedure call, etc)
  • Connection
  • Connection object to use to connect to the data
    source

20
Significant Command Object Properties (continued)
  • The Parameters collection stores parameter
    objects that contain information to pass to the
    data source

21
Significant Command Object Methods
  • ExecuteNonQuery
  • Executes a non-row returning SQL statement and
    returns the number of rows affected
  • ExecuteReader
  • Creates a DataReader by executing the command in
    the CommandText property
  • ExecuteScalar
  • Executes the command and returns the value stored
    in the first column of the first row in the
    result set (extra columns or rows are ignored)

22
Command Object Parameters
  • Object used to pass values into the data source
  • Passing values into a stored procedure is a good
    example

23
Command Object Parameters (continued)
  • Typical stored procedure accepting a parameter

CREATE PROCEDURE CustOrderHist _at_CustomerID
nchar(5) AS SELECT ProductName,
TotalSUM(Quantity) FROM Products P, Order
Details OD, Orders O, Customers C WHERE
C.CustomerID _at_CustomerID AND C.CustomerID
O.CustomerID AND O.OrderID OD.OrderID AND
OD.ProductID P.ProductID GROUP BY ProductName
24
Command Object Parameters (continued)
oCommand.CommandText " CustOrderHist
" oCommand.CommandType CommandType.StoredProcedu
re oCommand.Parameters.Add("_at_CustomerID",
ALFKI) 'Create and populate a data reader Dim
oDataReader As SqlDataReader _
oCommand.ExecuteReader(CommandBehavior.CloseConnec
tion) 'Loop through the results While
oDataReader.Read() Debug.WriteLine(oDataReader.
GetName(0).ToString()) Debug.WriteLine(oDataRea
der.GetValue(0).ToString()) End While
25
Data Adapters
ADO.NET Architecture
Data Adapter
Data Set
Connection
Data Relation
DeleteCommand
InsertCommand
Tables
Data Provider Components
Table
Table
SelectCommand
Data Rows
Data Rows
UpdateCommand
Data Columns
Data Columns
Your Code
Database
26
Data Adapters
  • Acts as a bridge between a data source and a data
    set
  • Transfer the data from the data source down to
    the data set and then back again if necessary
  • Translates the data from its native format into
    XML for the data set to understand and then back
    from XML into the native format

27
Populating a Data Set From a Data Adapter
Dim oDS As New DataSet Dim oDataAdapter As New
SqlDataAdapter Dim oSelectCmd As New
SqlCommand oSelectCmd.CommandType
CommandType.StoredProcedure oSelectCmd.CommandText
Select from Customers" oSelectCmd.Connection
oConn oDataAdapter.SelectCommand
oSelectCmd oDataAdapter.Fill(oDS,
Customers) Me.grdCustomers.DataSource
oDS.Tables(Customers)
28
Updating Records from a Data Adapter
  • Create Command Object and assign properties
  • Assigning a Command Object to the UpdateCommand
    property
  • Call Update method

29
Deleting Records from a Data Adapter
  • Create Command Object and assign properties
  • Assigning a Command Object to the DeleteCommand
    property
  • Call Update method

30
Inserting Records from a Data Adapter
  • Create Command Object and assign properties
  • Assigning a Command Object to the insertCommand
    property
  • Call Update method

31
Working with DataTables
  • Find
  • Returns DataRow
  • Contains
  • Returns T/F
  • Select subset of records from DataTable

32
Working With DataViews
  • DataViews are logical representations of data in
    a data table.
  • All tables have a DefaultView property.
  • You can sort, filter, bind to DataViews

33
ADO.NET Fundamentals Section Wrap Up
  • Questions?
  • Answers?
  • rodpaddock_at_dashpoint.com
  • www.dashpoint.com
  • www.officezealot.com/rod
  • Thank you.
Write a Comment
User Comments (0)
About PowerShow.com