ADO.NET Connect

topics
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

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

Ø    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

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

Ø     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