. NET data access architecture Guide

. NET data access architecture guide in msdn China

Some tips from this article:

No matter which type of. NET data you useProgram, You must always follow the following principles:

•	Open the database connection as late as possible.
•	Use the connection as short as possible.
•	Close the connection as early as possible.

To disable the connection before returning the method, consider using the following twoCodeOne of the methods described in the example. Use the first methodFinallyBlock. The second method uses C #UsingStatement, which ensures thatDisposeMethod.

If you useSqldataadapterTo generateDatasetOrDatatableNote the following:

•	You do not need to enable or disable the database connection explicitly.SqldataadapterFillMethod to open the database connection, and then close the connection before it returns. If the connection is enabled,FillThe connection remains open.
•	If you want to use this connection for other purposes, consider callingFillMethod. In this way, you can avoid unnecessary open/close operations to improve performance.
•	Although you can use the sameSqlcommandObject To execute the same command multiple times. Please do not reuse the sameSqlcommandObject To execute different commands.

If you useSqldatareaderNote the following:

•	When the data reader is active, the basic connection to the database remains open and cannot be used for any other purpose. Call as early as possibleSqldatareaderOnClose.
•	Each connection can have only one data reader.
•	After using the data reader, you can explicitly close the connection orCommandbehavior. closeconnectionThe enumerated value is passedExecutereaderMethod.SqldatareaderObject. This indicates thatSqldatareaderAnd close the connection.
•	When using a reader to access data, if you know the basic data type of the column, you should use the typed accesser method (suchGetint32AndGetstring), Because they can reduce the number of type conversions required to read column data.
•	To avoid extracting unnecessary data from the server to the client, if you want to disable the reader and discard any other resultsClosePreviouslyCancelMethod.CancelMake sure that the results are discarded on the server, instead of being extracted to the client without any need. Otherwise,CloseThe reader does not need to extract other results to clear the data stream.
•	if you want to obtain the output or return the value from the stored procedure, and you are using the executereader method of the sqlcommand object, the close method on the reader must be called before the output and return values are available.