Configuring Connections and Connecting to Data

1
Chapter 5

• Configuring Connections and Connecting to Data

2
Overview of ADO.NETwith the .NET Framework
Introdution to ADO.NET
3
Universal Data Access (UDA)

• Microsofts 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
4
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
5
ADO.NET
ActiveX Data Objects ? ADO

• Important Core of the .NET Framework
• Microsofts 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)

6
.NET Data Provider Objects
7
Generic classes

• Connection
• Command.
• DataReader
• DataAdapter
• DataSet
• Core ADO.NET Namespaces
• System.Data
• System.Data.OleDb
• System.Data.SqlClient

8
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

9
Lesson 1 Creating and Configuring Connection
Objects

• What Is a Connection Object?

10
(No Transcript)
11
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

12
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

13
Creating Connection Objects Programmatically

• MS Access
• SQL Server

14
Connection Objects

• Connection namespace

15
Connection properties
16
Connection methods
17
Chapter 6

• Working with Data in a Connected Environment

18
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
19
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

20
System.Data Architecture
Program
DataReader
DataSet
DataProvider
DataAdapter
Database
21
Details System.Data Architecture
22
Main DifferenceDataSet-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.
• Doesnt 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

23
Differences DataSet and DataReader
24
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

25
Lesson 1 Creating and Executing Command Objects

• 1.What Are Command Objects?

26
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

27
Lesson 1 Creating and Executing Command Objects

• 1.What Are Command Objects?
• Depend on Data Providers

28
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

29
Lesson 1 Creating and Executing Command Objects
30
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)

31
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

32
Lesson 1 Creating and Executing Command Objects

• 2.Creating and Configuring Command Objects
• Creating a Command Object That Executes a SQL
  Statement (p.256)

33
Lesson 1 Creating and Executing Command Objects

• 2.Creating and Configuring Command Objects
• Creating a Command Object That Executes a Stored
  Procedure (p.257)

34
Lesson 1 Creating and Executing Command Objects

• 2.Creating and Configuring Command Objects
• Creating a Command Object That Performs Catalog
  Operations (p.257)

35
Lesson 1 Creating and Executing Command Objects

• 2.Creating and Configuring Command Objects
• Creating a Command Object That Returns a Single
  Value (p.258)

36
Lesson 1 Creating and Executing Command Objects

• 2.Creating and Configuring Command Objects
• Creating a Command Object That Returns a Single
  Value (p.258)

37
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

38
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-gt select New
  Query from the Data menu.

39
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)

40
How to receive DataReader

• Connectionn

Open

• Commandn

Which data?

• DataReadern

Result
41
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

42
DataReaders

• You cant access anything until you call
  Read()the first time

43
Core DataReader method/property

• Read Reads, and set pointer to the next record.
• Close
• IsClosed
• HasRowsReturns 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

44
Note DataReader

• ADO.NET does not provide all the server-side
  cursor
• Dont 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 Connectiongt cannot used other DataReader.
  
• To reuse connectiongtcall DataReader.Close.
• Dont depend on the garbage collector-gt
  explicitly close.
• Tie the connection life to DataReader
• CommandBehavior.CloseConnection in
  ExecuteReader.

45
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

46
Lesson 1 Creating and Executing Command Objects

• 3.Creating SQL Commands (SQL Statements) with the
  Query Designer
• Lab P265

47
(No Transcript)
48
ExecuteSql
49
Ex _Module8_2
50
Load Data from Access
51
Sort
52
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

53
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.

54
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.

55
Lesson 2 Working with Parameters in SQL Commands
56
Lesson 2 Working with Parameters in SQL Commands

• 3.Creating Parameters (p 274)
• Create parametersinstance of the Parameter class
  
• setting its name and data type
• Choose ParameterDirection

57
Creates an Input parameter (p 274)
58
creates an Output parameter (p 275)
59
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

60
Lesson 2 Working with Parameters in SQL Commands

• Lab Lab Working with Parameters page 275

61
Module8_2 Select in ListView
Không t?o d?i tu?ng Parameters
62
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

63
Your Questions?
64
(No Transcript)
65
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 '

66
Notes

• Datareader New?????
• Open Connection before ExecuteReader
• Execute only one for one command

67
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

68
Ex. Insert new record
Save
69
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
  (_at_ma,_at_lname,_at_fname,_at_phone)
• insert into sv(ma,last name,first name,phone)
  values (?,?,?,?)

70
Exer 2

• Copy Exer 1 to new folder
• Change the way to get data
• Client Dynamic SQL -gt Client Parameters SQL

71
Exer 3

• Copy Exer 2 to new folder
• Change the way to get data
• Client Parameters SQL -gtserver Parameters SQL in
  querry

72
Exer 4

• Using Student data file
• Display in treeview
• Display detail

73
BT1

• LOP(MaLop,TenLop)
• SINHVIEN(MaSV,Ten,DiaChi,SDT,Lop)

74
BT2

• SINHVIEN(MaSV,Ten,DiaChi,SDT,Lop)

75

• LOP(MaLop,TenLop)
• SINHVIEN(MaSV,Ten,DiaChi,SDT,Lop)

76
(No Transcript)