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