chapter 5 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Chapter 5 PowerPoint Presentation
Download Presentation
Chapter 5

Loading in 2 Seconds...

play fullscreen
1 / 76

Chapter 5 - PowerPoint PPT Presentation


  • 120 Views
  • Uploaded on

Chapter 5. Configuring Connections and Connecting to Data. Overview of ADO.NET with the .NET Framework. Introdution to ADO.NET. Universal Data Access (UDA). Microsoft’s strategy for accessing data for multiple providers

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

PowerPoint Slideshow about 'Chapter 5' - hamlet


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
chapter 5

Chapter 5

Configuring Connections and Connecting to Data

universal data access uda
Universal Data Access (UDA)
  • Microsoft’s strategy for accessing data for multiple providers
  • Goal is to access any type of data from any application on any type of computer

Introdution to ADO.NET

oledb
OLEDB
  • Technology designed to implement the UDA concept
  • Provides an standardized object-oriented interface
  • Allows access data from any source
  • Library functions
  • Hard to use

Introdution to ADO.NET

ado net
ADO.NET

ActiveX Data Objects  ADO

  • Important Core of the .NET Framework
  • Microsoft’s latest database object model
  • Data access solution in .NET
  • Allows VB programmers to use a standard set of objects to refer to data from any source: SQL Server, Oracle, OLE DB & ODBC (UDA…)
net data provider objects

.NET Data Provider

Connection

Command

Data Adapter

SelectCommand

InsertCommand

UpdateCommand

DeleteCommand

DataReader

.NET Data Provider Objects
generic classes
Generic classes
  • Connection
  • Command.
  • DataReader
  • DataAdapter
  • DataSet
  • Core ADO.NET Namespaces

System.Data

System.Data.OleDb

System.Data.SqlClient

importing the ado net namespaces
Importing the ADO.NET Namespaces

Needed to build a data access application

  • For OLE DB: (MS Access)

Imports System.Data

Imports System.Data.OleDB

  • For SQL Server:

Imports System.Data

Imports System.Data.SQLClient

what is a connection object
What Is a Connection Object?
  • Representation of an open connection to a data source.
  • Does not fetch, update data, , execute queries
  • the pipeline that commands and queries use to send
how to create connection
How to create connection?
  • Creating Connections in Server Explorer
      • P206
  • Creating Connections Using Data Wizards
      • P207
  • Creating Connection Objects Programmatically
    • SQL Server
      • SQL / Integrated Security
    • MS Access
connection objects
Connection Objects
  • Connection namespace
chapter 6

Chapter 6

Working with Data in a Connected Environment

lesson 0 connected disconnectd environment in ado net

Data

Source

Connection

Data Adapter

DataSet

Web

Form

Windows

Form

Lesson 0: Connected & Disconnectd Environment in ADO.NET
  • 2 ways using with ADO.NET

Disconnectd

Connected

Web

Form

Data

Source

Connection

Command

DataReader

Windows

Form

disconnected data access
Disconnected Data Access

Advantages

Disadvantages

  • Single database server can support many users
    • reduced server's resources
  • Data using more flexible
  • Data not 'tied' to a connection
    • easy to pass between tiers or persist to file
  • Highly suited to Web and n-tier Apps
  • Expensive when open, close connections
  • Retrieving large result sets can be very slow
  • Places demand on client memory and CPU
system data architecture
System.Data Architecture

Program

DataReader

DataSet

DataProvider

DataAdapter

Database

details system data architecture

Program

Action SQL

DataReader

Maintain data

DataSet

Get data

Maintain data

Command Object

Get data

Connection Object

DataAdapter

Provider

Database

Details: System.Data Architecture
main difference dataset datareader
Main Difference:DataSet-DataReader

DataSet

    • Data structure to store schema and data in a disconnected fashion
    • Useful for editing data offline and later update to data source
  • DataReader
    • Like Phone connection.
    • Doesn’t need to store data in memory
    • Object to access data in a connected, forward-only, read-only fashion
    • When performance is your chief concern, especially with large amounts of data, use a DataReader class
lesson 1 creating and executing command objects
Lesson 1: Creating and Executing Command Objects
  • 1.What Are Command Objects?
  • 2.Creating and Configuring Command Objects
  • 3.Creating SQL Commands (SQL Statements) with the Query Designer
lesson 1 creating and executing command objects2
Lesson 1: Creating and Executing Command Objects
  • 1.What Are Command Objects?
    • To execute SQL statements,stored procedures
    • Contain the necessary information to execute SQL statements
lesson 1 creating and executing command objects3
Lesson 1: Creating and Executing Command Objects
  • 1.What Are Command Objects?
    • Depend on Data Providers
lesson 1 creating and executing command objects4
Lesson 1: Creating and Executing Command Objects
  • 1.What Are Command Objects?
    • Common properties (p.254)
      • CommandText (SQL statement or the name of any valid stored procedure)
      • CommandType (Text, TableDirect, StoredProceduce)
      • CommandTimeout(The time in seconds before terminating the attempt to execute a command. 30s)
      • Connection
lesson 1 creating and executing command objects6
Lesson 1: Creating and Executing Command Objects
  • 1.What Are Command Objects?
    • Common Command Object Methods (p.255)
      • ExecuteNonQuery (Executes SQL statements or stored procedures that return excuted number)
      • ExecuteReader (Executes commands that return tabular (or rows) of data.)
      • ExecuteScalar (return object, often use to get value of a column or total of record )
      • ExecuteXmlReader (Returns XML formatted data)
lesson 1 creating and executing command objects7
Lesson 1: Creating and Executing Command Objects
  • 2.Creating and Configuring Command Objects
    • Creating a Command Object That Executes a SQL Statement
    • Creating a Command Object That Executes a Stored Procedure
    • Creating a Command Object That Performs Catalog Operations
    • Creating a Command Object That Returns a Single Value
lesson 1 creating and executing command objects8
Lesson 1: Creating and Executing Command Objects
  • 2.Creating and Configuring Command Objects
    • Creating a Command Object That Executes a SQL Statement (p.256)
lesson 1 creating and executing command objects9
Lesson 1: Creating and Executing Command Objects
  • 2.Creating and Configuring Command Objects
    • Creating a Command Object That Executes a Stored Procedure (p.257)
lesson 1 creating and executing command objects10
Lesson 1: Creating and Executing Command Objects
  • 2.Creating and Configuring Command Objects
    • Creating a Command Object That Performs Catalog Operations (p.257)
lesson 1 creating and executing command objects11
Lesson 1: Creating and Executing Command Objects
  • 2.Creating and Configuring Command Objects
    • Creating a Command Object That Returns a Single Value (p.258)
lesson 1 creating and executing command objects12
Lesson 1: Creating and Executing Command Objects
  • 2.Creating and Configuring Command Objects
    • Creating a Command Object That Returns a Single Value (p.258)
lesson 1 creating and executing command objects13
Lesson 1: Creating and Executing Command Objects
  • 3.Creating SQL Commands (SQL Statements) with the Query Designer
    • Creating SQL Commands (SQL Statements) with the Query Designer
    • Performing Database Operations Using Command Objects
lesson 1 creating and executing command objects14
Lesson 1: Creating and Executing Command Objects
  • 3.Creating SQL Commands (SQL Statements) with the Query Designer
    • Creating SQL Commands (SQL Statements) with the Query Designer
      • We can use the Query Designer to assist in creating SQL for Command objects
      • Select database in Server Explorer-> select New Query from the Data menu.
lesson 1 creating and executing command objects15
Lesson 1: Creating and Executing Command Objects
  • 3.Creating SQL Commands (SQL Statements) with the Query Designer
    • Performing Database Operations Using Command Objects (p 260)
how to receive datareader
How to receive DataReader

Connectionn

Open

Commandn

Which data?

DataReadern

Result

datareader class
DataReader Class
  • Datareader and MS Access
  • Store the information obtained by the command
  • In stateless stream type object
  • Very efficient
  • Forward-only cursor
  • Read-only cursor
datareaders
DataReaders
  • You can’t access anything until you call Read()the first time
core datareader method property
Core DataReader method/property
  • Read:Reads, and set pointer to the next record.
  • Close
  • IsClosed
  • HasRows:Returns true if DataReader contains rows
  • FiledCount: Number of columns
  • GetName(i): returns the label of the ith column in the current row
  • GetString(i) :returns the value of the ith column as the specified type
note datareader
Note: DataReader
  • ADO.NET does not provide all the server-side cursor
    • Don’t keep DataReaders open longer than necessary
  • For flexible updates & client-side manipulation…
    • Use DataSets and DataAdapters
  • Only one DataReader use at a time
  • Tie to Connection=> cannot used other DataReader.
  • To reuse connection=>call DataReader.Close.
  • Don’t depend on the garbage collector-> explicitly close.
  • Tie the connection ‘ life to DataReader
    • CommandBehavior.CloseConnection in ExecuteReader.
note about datareader
Note about DataReader
  • The first row of data is not available until you call the Read method.
  • Using with stored procedure uses a return or output parameter, must close DataReader before get parameter
  • DataReader cannot be used for data binding
  • System.DBNull.value
lesson 1 creating and executing command objects16
Lesson 1: Creating and Executing Command Objects
  • 3.Creating SQL Commands (SQL Statements) with the Query Designer
    • Lab: P265
lesson 2 working with parameters in sql commands
Lesson 2: Working with Parameters in SQL Commands
  • 1.What Is a Parameter and Why Should I Use Them?
  • 2.Types of Parameters
  • 3.Creating Parameters
  • 4.Adding Parameters to Command Objects
lesson 2 working with parameters in sql commands1
Lesson 2: Working with Parameters in SQL Commands
  • 1.What Is a Parameter and Why Should I Use Them?
    • parameter can be thought of as a type of variable
    • use to pass and return values between your application and a database.
    • Parameter data types are assigned using the types defined in the System.Data.SqlDbType enumeration.
    • pass parameter values to SQL statements when we want to change the criteria of your queries quickly.
lesson 2 working with parameters in sql commands2
Lesson 2: Working with Parameters in SQL Commands
  • 2.Types of Parameters (p 274)
    • Input parameter. (default)
    • Output parameter.
    • InputOutput parameter.
      • InputOutput parameters are used to both send and receive data when executing a command.
    • The type of parameter is designated in the Direction property of the parameter
    • With a parameter, we ou can set its Direction property to Input, Output, InputOutput, or ReturnValue.
lesson 2 working with parameters in sql commands4
Lesson 2: Working with Parameters in SQL Commands
  • 3.Creating Parameters (p 274)
    • Create parameters:instance of the Parameter class
    • setting its name and data type
    • Choose ParameterDirection
lesson 2 working with parameters in sql commands5
Lesson 2: Working with Parameters in SQL Commands
  • 4.Adding Parameters to Command Objects
    • Command objects have a Parameters property that represents a collection of parameters
    • After you create a parameter, you must add it to the Parameters collection of the Command object
lesson 2 working with parameters in sql commands6
Lesson 2: Working with Parameters in SQL Commands
  • Lab: Lab: Working with Parameters page 275
module8 2 select in listview
Module8_2: Select in ListView

Không tạo đối tượng

Parameters

lesson 2 summary
Lesson 2: Summary
  • Update Database with connect mode
    • Client
      • Type 1: Using client dynamic Sql
      • Type 2: Using client SQL with parameters
    • Server
      • Using server Query Parameters
      • Using server Stored Procedured
    • Example: find student
using dynamic sql
Using Dynamic SQL
  • cmd.CommandText = "select * from sinhvien where ten like ‘%tuan%' “;
  • cmd.CommandText = "select * from sinhvien where ten like ‘%” + txt.Text + “%' “;
  • cmd.CommandText = "select * from sinhvien where ten = ‘” + txt.Text + “' “;
notes
Notes
  • Datareader: New?????
  • Open Connection before ExecuteReader
  • Execute only one for one command
exer 1

Exit

Exer 1
  • 1. Create MA access database
    • Table Student(ID,FirstName,Lastname,Phone)
    • Using Client dynamic SQL to put data in Listview
    • Using Client dynamic SQL to detail data to Textboxes
access
Access
  • Insert into sv(ma,lname,fname,phone) Values (?,?,?,?)
    • Cmd.parameters.add(p1)
    • Cmd.parameters.add(p2)
    • Cmd.parameters.add(p3)
    • Cmd.parameters.add(p4)
  • Insert into sv(ma,lname,fname,phone) Values (@ma,@lname,@fname,@phone)
  • insert into sv(ma,[last name],[first name],phone) values (?,?,?,?)
exer 2
Exer 2
  • Copy Exer 1 to new folder
  • Change the way to get data
    • Client Dynamic SQL -> Client Parameters SQL
exer 3
Exer 3
  • Copy Exer 2 to new folder
  • Change the way to get data
    • Client Parameters SQL ->server Parameters SQL in querry
exer 4

0904981234

Exer 4
  • Using Student data file
  • Display in treeview
  • Display detail
slide73
BT1
  • LOP(MaLop,TenLop)
  • SINHVIEN(MaSV,Ten,DiaChi,SDT,Lop)
slide74
BT2
  • SINHVIEN(MaSV,Ten,DiaChi,SDT,Lop)
slide75
LOP(MaLop,TenLop)
  • SINHVIEN(MaSV,Ten,DiaChi,SDT,Lop)