topics
1.Choosing a Data Provider
1.Choosing a Data Provider
Ø
2.Defining a Connection
Ø
3.Using a Connection
Ø4.Managing a Connection
What is a .NET Data Provider?
A .NET data provider is a set of classes that you use to connect
What is a .NET Data Provider?
A .NET data provider is a set of classes that 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
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
Ø
InfoMessage
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
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 Pooling?
Ø
Parameters in the Connection String that controls
connection pooling:
Max Pool Size (default = 100)
比如: Server=(local); Integrated Security=SSPI; Database=Northwind;
Max Pool Size=75; Min Pool Size=5
Ø
Min Pool Size (default = 0)
Ø
Pooling (default = true)
比如: 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 connection
pooling strategy
Ø
Open database connections late, and close them early