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.