ADO.NET provides several different methods to access SQL Server data, including OLE DB.NET, ODBC.NET, SQLXML, and the SQL Server .NET data provider. Of all of these, the SQL Server .NET data provider is the fastest, as much as 30-40% faster than the others. The SQL Server .NET provider uses TDS (Tabular Data Stream, which is the native SQL Server data format) to communicate with SQL Server. The SQL Server .NET provider can be used to connect to SQL Server 7.0, SQL Server 2000, and SQL Server 2005 databases, but not SQL Server 6.5 databases. If you need to connect to a SQL Server 6.5 database, the best overall choice is the OLE DB.NET data provider.
When using ADO.NET to make connections to SQL Server, always be sure you explicitly close any Connection, Recordset, or Command objects you have opened. While letting an object go out of scope will in affect close the object, it is not the same as explicitly closing an object. By explicitly closing these objects and setting them to nothing, you do two things. First, you remove the object sooner than later, helping to free up resources. Second, you eliminate the possibility of "connection creep". Connection creep occurs when connection or resource pooling is used and when connections are not properly closed and released from the pool. This helps to defeat the purpose of pooling and reduces SQL Server's performance.
When you specify a server in an ADO.NET connection string, use the server's IP address, not the server's DNS name. By using an IP address instead of a DNS name, name resolution does not have to occur, reducing the amount of time it takes for a connection to be made. A server's IP address can be used to specify either a default or named instance of a server running SQL Server. If you are running a cluster, use the virtual SQL Server IP address.
|
| Author: thebarrett27 13 Jun 2008 | Member Level: Bronze Points : 0 |
good one
|
| Author: Sebastian 13 Jun 2008 | Member Level: Gold Points : 1 |
This is very informative. Thanks for sharing the details.
|