Topics1.choosing a data provider
Ø
2.defining a connection
Ø
3.USING a connection
Ø4.Managing a connectionwhat is a .NET DATA PROVIDER?
A .NET DATA Provider is a set of classes this you use to connect
To a data source, and retrieve and update data
The .NET DATA Provider Classes
Ø
XXXConnection
-
For example, SqlConnection
Ø
XXXTRANSACTION
-
For Example, SQLTRANSACTION
Ø
XXXEXCEPTION
-
For Example, SQLEXCEPTION
Ø
XXXERROR
-
For Example, SQLERROR
Ø
XXXCommand
-
For Example, SqlCommand
Ø
Xxxparameter
-
For Example, SQLParameter
Ø
XXXDataReader
-
For Example, SqlDataReader
Ø
XXXDataAdapter
-
For Example, SqldataAdapter
Ø
XXXPERMISSION
-
For Example, SqlClientPermission
Which .NET DATA Provider to use?
Ø
SQL Server .NET DATA Provider
Ø
SQL Server Version 7.0 and Later
Ø
OLE DB .NET DATA Provider
Ø
SQL Server 6.5, Microsoft Access, Oracle, Other Data Sources with OLE DB
Providers
Ø
ODBC .NET DATA Provider
Ø
Legacy Data Sources That Only Have ODBC Drivers
Ø
.NET DATA Provider for Oracle
Ø
Oracle
Version 8.1.7 and later
Ø
Custom .NET DATA Provider
Ø
SQLXML Managed Classes
Ø
SQL Server 2000 and Later for XML Functionality
Ø
What is a connection string?
Ø
A connection string is a list of attribute = value pairs Separated
By SEMICOLONS
Ø
Describes How To Connect To A Specific Data Store
Ø
Describes Configuration Information Used to "find" That Database and
SuccessFully Access It with the Proper Security
Ø
EXAMPLE:
Server = london; database = northwind; integrated
Security = sspi; persist security info = false
Connection String Parameters
Ø
Connection String Parameters
Ø
Provider (OLE DB ONLY)
Ø
Data Source (Server)
Ø
Initial Catalog (Database) Ø
INTEGRATED Security
Ø
User ID
Ø
Password
Ø
Persist security info
Ø
ETC.
Opening and closing a connection
Ø
Opening and Closing Connections Explicitly
Ø
Open and close methods
Ø
Opening and Closing Connections IMPLICITLY
Ø
Data Adapters Can Open and Close Connections Automatically When
Needed
Ø
Using the dispose method
Ø
Removes The Connection from the Connection Pool
Handling Connection Events
Ø
Connection Events
Ø
StateChange
Ø
InfubusSage
CNNorthWind.StateChange = New
StateChangeEventHandler (cnnorthwind_statechange);
Private void cnnorthwind_statechange
Object Sender, StateChangeeventargs E)
{
// Connection Might Be Closed, Handle THE CASE
}
Managing Database Connections
Ø
Database Connections Are A Critical, Expensive, And Limited Resource
The Way You Manage Connections CAN Affect
Ø
SCALABILITY
Ø
Performance
Ø
Security
Connection USAGE PATTERNS
Ø
Irrespective of the .NET Data Provider, You Must Always:
Ø
Open a Database Connection As Late As Possible.
Ø
Use the connection for as short a period as possible.
Ø
Close The Connection As Soon as Possible
How to config connection poibility?
Ø
Parameters in The Connection String That Controls
Connection pooling:
Max pool size (default = 100)
Ø
MIN pool size (default = 0)
Ø
Pooling (Default = true)
For example: server = (local); integrated security = SSPI; Database = northwind; Max pool size = 75; min pool size = 5
Summary
Ø
Use the appropriate .NET DATA Provider
Ø
Adopt a configurable and high-performance connect
Pooling Strategy
Ø
Open Database Connections Late, And Close Them Early
