Download
1 / 16
160 likes | 276 Vues
This presentation by David Henson covers essential ADO.NET components for effective data access in ASP.NET applications. Learn to differentiate between DataReader and DataAdapter, manage connections efficiently, and grasp the importance of connection pooling. The session discusses various providers, connection methods, and security considerations. Get practical insights on structuring your connection strings, using stored procedures for performance, and leveraging the right data control for your web applications to ensure optimal performance and user experience.
E N D
ADO.NET Best Practices 5/6/2002 LA .NET Users Group Presented by David Henson dhenson@certifiednetworks.com
Topics • ADO.NET Components Used For ASP.NET Data Access • Providers • Connections • Data Retrieval Using DataReader vs. DataAdapter
ADO.NET Components • SqlDataAdapter • SqlDataSet • SqlDataTable • SqlDataReader • SqlCommand
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” %>
Providers • Providers Available: • SQL Server .NET Provider • OleDB .NET Provider • Example-AllRecordsBasicOleDB.aspx • ODBC .NET Provider • SQL XML .NET Provider
Connections • Connection Defined • Where to Store the Connection String • Connection Syntax Examples • Connection Pooling • Security • Close Your Connections! • Monitoring Connections
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
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>
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!
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
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
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
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
Controlling the HTML • Use Stylesheets if Possible! • Make Sure The Page Fails Gracefully If Needed • With DataGrids, Use TemplateColumns
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”
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
