Develop high-performance ADO. Net Applications

Overview of developing high-performance ADO. Net Applications

 

Connection-oriented model

L ado.net 1.x must store stable connections when reading data, and only one datareader can be used for each connection.

L ado.net 2.0 does not have this restriction! Multiple datareader can be opened for one connection

 

Use the appropriate data provider for ADO. NET performance best practices

L SQL Server. NET data provider

SQL Server 7.0 or 2000

Access the database through TDS

L ole db. NET data provider

SQL Server 6.5, Microsoft Access, Oracle, or other

The database of the ole db Provider.

L ODBC. NET data provider

Some old systems

L. NET data provider for Oracle

Oracle 8.1.7 Or later

L custom. NET data provider

[Note] You can customize the data provider. This interface is public in. net. You can implement these classes by yourself.

 

The following figure shows the comparison of database connections:

Provider factories

L during application development, we often do not know what database the target environment is, especially when developing products.

L ADO. Net 1.x uses the interface (iconnection idataadapter, etc.) to realize database independence, but programmers still need to write a lot of code.

L ADO. NET 2.0 uses database-independent abstract classes and interfaces and provides "dbproviderfactory" to obtain the corresponding data provider.

Real dataprovider. This is an implementation layer that completes the specific database provider.

Dbproviderfactory Methods

Createconnection

Createcommand

Createcommandbuilder

Createconnection

Createconnectionstringbuilder

Createdataadapter

Createdatasourceenumerator

Createparameter

Createpermission

 

For more information, see msdn. We can use them to create the connection command adapter parameter permission data source required to access the database, etc.

Dbproviderfactories Methods

Dbproviderfactories Method	Purpose
Getfactoryclasses ()	Returns a datatable of provider Information from the information in machine. config
Getfactory (datarow)	Returns the correct dbproviderfactory instance given a datarow from the datatable produced by getfactoryclasses
Getfactory (string)	Returns the correct dbproviderfactory instance given a provider-invariant name string that identifies the provider

 

Getfactory (string): Here, you can write the string to the configuration file (machine. config)

 

Register a data provider

Ado. NET 2.0 can be used to register a data provider in machine. config. You can find the corresponding data provider by name in the program.

Data Provider. Through the registration below, when I use sqlclient data provider, I only know that this class is used for implementation.

<System. Data>

<Dbproviderfactories>

<Add name = "sqlclient data provider" invariant = "system. Data. sqlclient" support = "FF" Description = ". NET Framework data provider for sqlserver"

Type = "system. Data. sqlclient. sqlclientfactory, system. Data, version = 2.0.3600.0, culture = neutral, publickeytoken = B 77a 5C 561934e089 "/>

<! -- Other Provider entries elided -->

</Dbproviderfactories>

</System. Data>

 

Factory example (base)

Enum provider {sqlserver, Oracle, oledb, ODBC };

Public dbconnection getconnectionbaseclass ()

{

// Determine provider from configuration

Provider prov = getproviderfromconfigfile ();

Dbconnection conn = NULL;

Switch (PROV)

{

Case provider. sqlserver:

Conn = new sqlconnection ();

Break;

Case provider. Oracle:

Conn = new oracleconnection ();

Break;

// Add new providers as the application supports them

}

Return conn;

}

Why? This is because dbconenection abstract class in ADO. net2.0, and only the iconnenction interface in ADO. net1.1

Factory example (Adv)

// Get providerinvariantstring from configuration

String provstring = getproviderinvariantstring ();

Dbproviderfactory fact = dbproviderfactories. getfactory (provstring );

Idbconnection = fact. createconnection ();

 

Both of the above methods can implement dataprovider

 

Database Connection

Connection

L enable or disable a connection explicitly in the method.

L when datareaders is used, specify commandbehavior. closeconnection.

L do not open the connection manually when fill or update is used.

L avoid checking the state attribute of oledbconnection, resulting in additional consumption.

L use the connection pool

Connection Pool

By default, the connection pool is automatically opened. You can control the connection pool through the parameters of the connection string:

Max pool size (default = 100)

Min pool size (default = 0)

Pooling (default = true)

Conn. connectionstring = "Server = localhost; Integrated Security = sspi; database = northwind; Max pool size = 75; min pool size = 5; pooling = true ;";

 

Enable and disable a connection

L dataadapter can automatically open and close the connection as needed.

L when using the command object, you need to manually open and close the connection

 

SQL commands

L check SQL input and use parameters

L only return the required rows and Examples

L use the paging function for large datasets

L execute SQL statements in batches to reduce round-trips

L use the executenonquery method if no data is returned

L when a scalar is returned, use the executescalar Method

L do not use commandbuilder during running time

 

Batch execution SQL

Sqlcommand cmd = new sqlcommand ();

Cmd. commandtext = "readcustomerandorders ";

// The stored procedure returns multiple result sets.

Sqldatareader myreader = cmd. executereader ();

If (myreader. Read ())

//... Read first result set

Reader. nextresult ();

If (myreader. Read ())

//... Read

 

Stored Procedure

L use stored procedures whenever possible

Generally, SQL statements need to be executed in five steps: Explain parsing to optimize compilation and execution, so the stored procedure only needs to be executed in the last step.

L for oledbcommand, the command type is commandtype. Text

L when sqlcommand is used, the command type is commandtype. storedprocedure

L consider using command. Prepare () because it is compiled and stored in the background.

L use output parameters whenever possible

L set nocount on SQL Server. We can see from the query analyzer how many rows are modified for statistics when no execution is performed. we can disable it to make SQL statement execution faster.

Parameters

L use parameters in Stored Procedures

Sqldataadapter mycommand = new sqldataadapter ("authorlogin", Conn );

Mycommand. selectcommand. commandtyp

E = commandtype. storedprocedure;

Sqlparameter parm = mycommand. selectcommand. Parameters. Add ("@ au_id", sqldbtype. varchar, 11 );

Parm. value = login. text;

L use parameterized SQL statements

Sqldataadapter mycommand = new sqldataadapter ("select au_lname, au_fname from authors where au_id = @ au_id", Conn );

Sqlparameter parm = mycommand. selectcommand. Parameters. Add ("@ au_id", sqldbtype. varchar, 11 );

Parm. value = login. text;

L create parameters and specify the type

L parameter objects can be cached.

 

Transactions

L use database transactions (stored procedures)

Begin tran

Update orders set freight = @ freight where orderid = @ orderid

Update [Order Details] set quantity = @ quantity where orderid = @ orderid

If (@ error> 0)

Rollback transaction

Else

Commit transaction

[Note] if all your transaction judgment conditions are in the database, you should consider using database transactions, which is the most efficient.

L use ADO. net transactions (programming)

Sqlconnection conn = new sqlconnection (connstring );

Sqltransaction trans = conn. begintransaction ();

Try

{

Sqlcommand cmd = new sqlcommand ("mywriteproc", Conn, trans );

Cmd. commandtype = commandtype. storedprocedure;

Cmd. Parameters. Add (.......);

// Additional transactioned writes to database

Trans. Commit ();

}

Catch

{

Trans. rollback ();

}

L The use of distributed transactions, updated by multiple databases, considering distributed transactions, although inefficient.

L use short transactions as much as possible, because the resource is locked for a long time.

L use appropriate transaction isolation levels to avoid and release poor data.

L avoid deadlocks

 

Trackback: http://tb.blog.csdn.net/TrackBack.aspx? Postid = 908988