ADO'NET Databases

1
ADO.NET Databases
2
Overview

• Introduction to ADO.NET
• Using Data-Bound Controls
• Navigating, Adding, and Removing Rows
• Selecting DataTable Rows

3
3.1 Introduction to ADO.NET

• Provides access to data sources
• Microsoft SQL Server
• Microsoft Access
• databases that support OLE DB and XML
• Earlier technologies
• ODBC (Open DataBase Connectivity)
• DAO (Data Access Objects)
• ADO (ActiveX Data Objects)

4
ADO.NET Features

• Loosely connected relationship between
  application and data source
• keeps database connection open only for brief
  periods of time
• Support for multi-tier applications
• Scalable to large numbers of users
• Supports XML (Extensible Markup Language)
• Uses strongly typed objects to represents
  database tables and relationships

5
Two ADO.NET Modes

• Connected
• database connection kept open while reading all
  data
• rows can only be accessed in forward direction
• read-only access to data
• Disconnected
• entire database table copied into memory
• connection closed
• program works with in-memory copy of data

6
Reading From a Database

• We use disconnect mode in Chapter 3
• Design-mode steps
• create a database connection object
• create a data adapter object
• create a dataset class and corresponding dataset
  object
• Use Visual Studio's Server Explorer to accomplish
  this

7
Database Basics

• Database collection of one or more tables, each
  containing data related to a particular topic
• Table logical grouping of related information
• Departments table example
• Each row can be called a record
• Each column can be called a field

 
8
Primary Key

• Primary key consists of one or more columns that
  uniquely identify each table row
• Required when performing table updates
• Example dept_id is the primary key

9
Clustered Primary Key

• Product Sales table example
• no column contains unique values
• we can combine date, product_id, and customer_id
  columns to form a clustered primary key

Also known as a composite key
10
Designing Database Tables

• Database schema
• design of tables, columns, and relationships
  between tables
• Principles
• avoid duplication of data
• create separate tables linked by a common column
• Database relationships
• one to one
• one to many
• many to many

11
One to Many Relationship

• Departments and Employee tables, linked by dept_id

Employee table contains integer dept_id
Table relationships will be explored more fully
in Chapter 5.
12
Microsoft Access Data Types
13
Data Sources and Connections

• ADO.NET data flow

14
Data Providers

• OLE DB .NET Data Provider
• most general
• use for almost any database, including MS-Access
• SQL Server .NET Data Provider
• use for SQL Server databases
• faster than OLE DB
• uses native SQL access methods

Chapters 3 and 4
Chapter 5 onward
15
ADO.NET Connection Classes

• OleDbConnection
• connects to any OLE DB database
• minimal connection string
• ProviderMicrosoft.Jet.OLEDB.4.0 _
• Data SourceSalesStaff.mdb
• SqlConection
• connects to SQL Server database

16
Database Connections

• Connection properties and methods

17
Data Adapter

• OleDbDataAdapter and SqlDataAdapter
• components in Visual Studio Toolbox
• Actions
• reads data from data source into a dataset
• writes data from dataset to a data source
• Retrieves data by executing an SQL query
• Two ways to store the SQL query
• in program code
• in a compiled stored procedure

18
Visual Studio Toolbox

• Each of the following components has a wizard

MS-Access
SQL Server
19
Datasets

• In-memory cache of records holding table data
  retrieved from one or more data sources (data
  adapters)
• Disconnected from data source
• updates written first to the dataset
• call the data adapter's Update method to write
  dataset changes to the underlying database

20
DataSet Class

• System.Data namespace
• Important properties
• Tables collection of DataTable objects
• Relations collection of DataRelation objects
• PrimaryKey collection of columns that make up
  the primary key

21
Other Classes

• DataTable
• holds one table in a DataSet
• DataRow
• holds a single row in a DataTable
• has properties corresponding to database columns
• DataColumn
• describes a single column in a DataTable

22
Application-Specific Classes

• Your program's data-related classes inherit from
  DataSet, DataTable, and DataRow
• Given a database table named SalesStaff, the
  following classes would be created by Visual
  Studio .NET
• dsSalesStaff inherits from DataSet
• SalesStaffDataTable inherits from DataTable
• SalesStaffRow inherits from DataRow

23
3.2 Using Data-Bound Controls

• Data binding
• permits controls to display and update dataset
  columns
• Data-aware control
• can bind itself to a dataset column
• DataBindings.Text property is assigned a column
  name

24
SalesStaff 1

• Hands-on tutorial
• Shows how to set up a database connection
• Uses data-bound controls
• Displays a single row of the SalesStaff table

25
Moving a Database

• (After having been attached to a program)
• Display the connection string in the app.config
  file
• select the Connection
• open the DynamicProperties group
• select ConnectionString
• map property to a key in the configuration file
• Select a new database location
• modify the Data Source property of the Provider
  key in the app.config file

26
Structured Query Language (SQL)

• Universal language for retrieving data and
  manipulating databases
• Two varieties in Visual Basic .NET
• Jet SQL (MS-Access)
• ANSI SQL (SQL Server)
• Most common statement SELECT

27
SQL SELECT Statement

• SalesStaff example
• SELECT
• FirstName, HireDate, IsFullTime, LastName,
• Salary, StaffId
• FROM
• SalesStaff
• Create alias for any column name
• SELECT
• FirstName, LastName, Salary,
• HireDate AS DateHired
• FROM
• SalesStaff

28
SQL Setting Row Order

• ORDER BY statement
• Identify columns on which to sort
• Specify ASC or DESC direction
• Examples
• ORDER BY LastName
• ORDER BY Salary ASC, HireDate DESC
• ASC is the default direction

29
3.3 Navigating, Adding, and Removing Rows
30
CurrencyManager Object

• Navigates between rows of a dataset bound to a
  form
• Properties Count, Current, Position, Refresh
• Obtained from the form's BindingContext
• Private WithEvents currManager As CurrencyManager
• currManager Me.BindingContext(dsSalesStaff1, _
• "SalesStaff")

31
Moving Between Rows

• Add or subtract an integer from the form's
  CurrencyManager object
• Examples
• move down one row
• currManager.Position 1
• move up one row
• currManager.Position - 1
• move to last row
• currManager.Position currManager.Count - 1

32
Adding a New Row

• NewRow method creates a row having same structure
  as the existing dataset
• Example
• Create the empty row
• Dim newRow As DsSalesStaff1.SalesStaffRow _
• DsSalesStaff1.SalesStaff.NewRow
• Fill in the columns
• newRow.StaffId "104"
• newRow.FirstName "Andrew"
• newRow.LastName "Chang"
• Add to the table's Rows collection
• DsSalesStaff1.SalesStaff.Rows.Add(newRow)

33
Catching Exceptions

• Call CancelEdit if an exception is thrown while
  trying to add a row to a dataset
• Try
• newRow.StaffId "104"
• newRow.FirstName "Andrew"
• newRow.LastName "Chang"
• '(assign other column values . . .)
• dsSalesStaff1.SalesStaff.Rows.Add(newRow)
• Catch except As Exception
• MessageBox.Show(except.Message)
• newRow.CancelEdit()
• End Try

34
Common Operations

• Remove a Table Row
• method 1 Call CurrencyManager.RemoveAt
• method 2 Call DataTable.Rows.Remove
• Update the Database
• general form
• DataAdapter.Update( DataSet )
• example
• daSalesStaff.Update(DsSalesStaff1)
• Reload the dataset
• daSalesStaff.Fill(DsSalesStaff1)

35
Checking for Null Values

• DataRow columns contain null if they have not
  been initialized
• Here's how to check for null using DbNull.Value
• Dim row As DataRow DsSalesStaff1.SalesStaff(0)
• If row("LastName") Is DbNull.Value Then
• 'LastName is null
• End If

(see page 196)
36
Updating the SalesStaff Table

• SalesStaff 2 example
• Adds, removes, and updates dataset rows
• Updates the underlying database

37
Column Names as DataTable Properties

• Visual Studio generates strongly typed dataset
  classes
• The specific DataTable class contains a property
  for every database table column
• SalesStaff example
• get contents of Salary column in row 0 of the
  SalesStaff table
• DsSalesStaff1.SalesStaff.Rows(0).Salary
• DsSalesStaff1.SalesStaff(0).Salary

38
Query Builder
39
SalesStaff 3

• Hands-on example
• iterates through a dataset table
• displays names in a list box
• With DsSalesStaff1
• Dim i As Integer
• For i 0 To .SalesStaff.Count - 1
• namesForm.lstNames.Items.Add( _
• .SalesStaff(i).LastName ", " _
• .SalesStaff(i).FirstName)
• Next
• End With

40
SalesStaff 3

• Alternative code to insert names in list box
• For-Each loop with specific DataRow type
• With DsSalesStaff1
• Dim row As dsSalesStaff.SalesStaffRow
• For Each row In .SalesStaff.Rows
• namesForm.lstNames.Items.Add( _
• row.LastName ", " _
• row.FirstName)
• Next
• End With

41
Filling List and Combo Boxes

• Design mode
• assign dataset name to the DataSource property
• asssign column name to the DisplayMember property
• At runtime
• fill the dataset object from the data adapter
• SelectedIndexChanged event
• fires when list box or combo box is filled from
  the dataset
• use special logic to prevent program from
  reacting before the user has made a selection

42
ValueMember Property

• ListBox and ComboBox
• References a table column that will be a lookup
  value
• At run time, get the SelectedValue property

43
3.4 Selecting DataTable Rows
44
SQL WHERE Clause

• Filters or selects rows from a dataset table
• Boolean expression
• can be compound
• String literals are surrounded by single quotes
• Date literals
• MS-Access surround with ...
• SQL Server surround with single quotes
• Examples
• WHERE LastName 'Gomez'
• WHERE Salary gt 30000
• WHERE LastName gt 'B'

45
SQL WHERE Clause

• LIKE and BETWEEN
• All values beginning with letter A
• WHERE LastName LIKE 'A'
• All values beginning with 1 and ending with 4
• WHERE StaffId LIKE '1_4'
• All values within a range
• WHERE (HireDate BETWEEN 1/1/1992 AND
  12/31/1999)

46
Selecting Karate School Members

• Hands-on tutorial
• Karate database, Members table
• User selects a name from a combo box
• Program fills in remaining form fields from the
  dataset

47
Parameterized Queries

• Certain elements of the query are assigned values
  at runtime (query parameters)
• add flexibility to static query
• Differences
• MS-Access, parameters identified by a ? in the
  query
• SQL Server, parameter names begin with _at_ character

next examples
48
Parameterized Queries

• MS-Access Example
• SELECT BirthDate, City, FirstName, LastName,
• MemberId, Phone, Street, Zip
• FROM Members
• WHERE (LastName ?)
• SQL Server Example
• SELECT BirthDate, City, FirstName, LastName,
• MemberId, Phone, Street, Zip
• FROM Members
• WHERE (LastName _at_LastName)

49
Assigning Parameter Values

• Identify parameter by name or index position
• MS-Access
• daMembers.SelectCommand.Parameters("LastName").Val
  ue _ txtLastName.Text
• daMembers.SelectCommand.Parameters(0).Value _
• txtLastName.Text
• SQL Server
• daMembers.SelectCommand.Parameters("_at_LastName").Va
  lue _ txtLastName.Text
• daMembers.SelectCommand.Parameters(0).Value _
• txtLastName.Text

50
Selecting Members Using Query Parameters

• Hands-On Tutorial (Karate2)
• Adds query parameter to Karate Members program
• Assigns MemberId parameter in combo box's
  SelectedIndexChanged event handler
• daMembers.SelectCommand.Parameters("MemberId").Val
  ue _
• cboLastName.SelectedValue
• DsMembers1.Clear()
• daMembers.Fill(DsMembers1)

51
The End