ADO.NET Best Practices

1. ADO.NET Best Practices 5/6/2002 LA .NET Users Group Presented by David Henson dhenson@certifiednetworks.com

2. Topics • ADO.NET Components Used For ASP.NET Data Access • Providers • Connections • Data Retrieval Using DataReader vs. DataAdapter

3. ADO.NET Components • SqlDataAdapter • SqlDataSet • SqlDataTable • SqlDataReader • SqlCommand

4. Required Namespaces • Namespace Defined • Two In Use This Evening: • System.Data – Provider Independent Like DataSet • System.Data.SqlClient – Provider Dependent Like SqlConnection • ASP.NET Syntax: <%@ Import Namespace=“System.Data” %>

5. Providers • Providers Available: • SQL Server .NET Provider • OleDB .NET Provider • Example-AllRecordsBasicOleDB.aspx • ODBC .NET Provider • SQL XML .NET Provider

6. Connections • Connection Defined • Where to Store the Connection String • Connection Syntax Examples • Connection Pooling • Security • Close Your Connections! • Monitoring Connections

7. Where to Store the Connection String • Options Available: • Front End App (.aspx file) • Web.Config • UDL File (OleDB Only) • Registry • Custom File • COM+ Catalog Using Connection Strings • Evaluation Terms: Security, Convenience, Performance

8. Two Connection String Syntax Examples • In the .aspx file: ConnString = “server=10.0.0.1;UID=sa;PWD=;” Dim Conn As New SqlConnection(ConnString) • In Web.Config XML file: <configuration> <appSettings> <add key=“ConnString” value=“server=10.0.0.1;UID=sa;PWD=;”/> </appSettings> </configuration>

9. Connection Pooling • Defined • Controlling Min/Max-Example6ShowConnectionStatePoolControl.aspx • Importance of “Exact String Match” • Pooling for SqlClient vs. OleDBClient • Effects of pooling on SQL security • Close Your Connections!

10. Performance Issues • Choose Providers Wisely • DataReader vs. DataAdapter • Repeater Control vs. DataGrid Control • Connection Pooling • Embedding SQL vs. Stored Procedures • Controlling The HTML • Typed Accessor Methods-Example7AdapterVsReaderUsingTypedAccessorMethods.asp

11. DataReader Vs. DataAdapter • DataReader • Forward Only • Only One Record At A Time In Memory • “Firehose” Functionality • Typed Accessor Methods Avoid Conversions • DataAdapter • More Overhead • More Flexible

12. Repeater Control vs. DataGrid(or DataList) Control • Repeat Control Simply Repeats • Low overhead • You Have To Do Everything • You Can Do It Better Than Microsoft Did! • DataGrid • Default HTML Behaviour • Higher Overhead, Most Functionality

13. Embedding SQL vs. Stored Procedures • Stored Proc Advantages: • Procedure Cache • Separate Security Model • Potentially Less Network Traffic • Output Params, Error Code & Result Set • Can Do Anything Server Side • Abstracts the Front End from Changes – Possible Disadvantage with Xcopy Deployment

14. Controlling the HTML • Use Stylesheets if Possible! • Make Sure The Page Fails Gracefully If Needed • With DataGrids, Use TemplateColumns

15. Final Recommendations • Use DataGrids Only When Updates Are Needed • Embed Connection In Code Behind File • Only “Select” What You Need • Call StoredProcs For Ultimate Performance When “Paging”

16. References • Book: “Programming Data-Driven Web Applications with ASP.NET” • Web: • http://www.asp.net • http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daag.asp • Http://www.certifiednetworks.com