ADO'NET Intro

1
ADO.NET Intro Best Practices

• Hammad Rajjoub INETA Speakers Bureau.

2
public override string ToString()

• Technical Lead, Applied Business Applications,
  Dubai, UAE
• INETA UG Leader
• INETA Member Of Speakers Bureau
• IDotNetEvangelist

3
Agenda

• Introduction
• Performance Considerations
• Best Practices
• Designing DAL
• Passing Data Through Tiers
• Summary

4
Introduction

• Overview Of ADO.Net
• ADO.Net Architecture
• Data Sets and XML
• Writing Generic Code in DAL (Data Access Layer)
• ADO.Net 2.0 (wassup ? )

5
Overview Of ADO.Net

• ADO.NET is the data access model for .NET-based
  applications. It can be used to access relational
  database systems such as SQL Server 2000, Oracle,
  and many other data sources for which there is an
  OLE DB or ODBC provider
• Disconnected Data Access with Datasets.
• Intrinsic Support for XML
• Component Oriented Programming

6
ADO.Net Architecture

• N-Tier Architecture.
• Disconnected Data Access.
• XML Support (Intrinsic/Inherent).
• .Net Framework Data provider and Data Sets.
• Data source Independence. (Can connect to variety
  of Data Sources including but not limited to SQL
  Server, Oracle, DB2 , etc).
• Variety in Data Access Models. (Datasets vs. Data
  Readers).

7
ADO.Net Architecture
8
ADO.Net XML

• Disconnected Data Access
• XML Serializable
• Data source Independence
• XSD
• Data Set as a Relational Data Store
• Transaction Support
• Beware of Diff-Grams

9
Object Model
10
Generic Data Access Layer

• Using Interfaces
• System.Data.IDataAdapter
• System.Data.IDataReader
• System.Data.IDbCommand
• System.Data.IDbDataParameter
• System.Data.IDbTransaction
• Using OleDb Data Provider

11
(No Transcript)
12
(No Transcript)
13

• DEMO

14
ADO.NET 2.0

• The Base-Class-Based Provider Model.
• Connection Pooling Enhancements.
• Asynchronous Commands.
• Bulk Import
• Provider Statistics
• AttachDbFileName
• SQL Server 2005-Specific Features in SqlClient

15
Performance Considerations

• .NET Framework Data Providers
• Working with DataReaders, DataSets, DataAdapters,
  and DataViews
• Stored Procedures vs. Direct SQL
• Object Relational Mapping
• Misc

16
Best Practices (Designing DAL)

• Some Common Scenarios
• Retrieving a result set and iterating through the
  retrieved rows
• Retrieving a single row
• Retrieving a single item from a specified row
• Concurrency Issues
• Object Relational Mapping
• Data Reader vs. Data Set vs. XML Reader
• Stored Procedures vs. SQL Text

17
Retrieving result set iterating

• Use a SqlDataAdapter object to generate a DataSet
  or DataTable
• Use a SqlDataReader to provide a read-only,
  forward-only data stream
• Use an XmlReader to provide a read-only,
  forward-only data stream of XML data

18
Retrieving a single row

• Use DataSet only if you want to bind that record
  to a control. (remember the overheads associated
  with it)
• Use Data Reader when you want to fetch schema
  information along with fetched value
• Create Custom Object

19
Retrieving a single item from a specified row

• Use the CommandBehavior.SingleRow enumerated
  value when calling the ExecuteReader method of
  the SqlCommand object
• In case of parameters and outputs ensure that
  DataReader is closed
• Use ExecuteScalar

20
Handling Concurrency Issues

• Time Stamping
• Observer Pattern

21
Object Relational Mapping

• Pros Cons
• Existing products/frameworks
• More to come (Passing Data Through Tiers)

22
Data Reader vs. DataSet vs. XMLReader 1/3

• Use DataSet when
• You require a disconnected memory-resident cache
  of data, so that you can pass it to another
  component or tier within your application.
• You require a disconnected memory-resident cache
  of data, so that you can pass it to another
  component or tier within your application.
• You are working with data retrieved from multiple
  data sources, such as multiple databases, tables,
  or files.
• You want to update some or all of the retrieved
  rows and use the batch update facilities of the
  SqlDataAdapter.
• You want to perform data binding against a
  control that requires a data source that supports
  IList.

23
Data Reader vs. DataSet vs. XMLReader 2/3

• Use DataReader when
• Dealing with large volumes of datatoo much to
  maintain in a single cache.
• Reduce the memory footprint of your application.
• Want to avoid the object creation overhead
  associated with the DataSet
• Want to perform data binding with a control that
  supports a data source that implements
  IEnumerable
• Wish to streamline and optimize your data access
• Reading rows containing binary large object
  (BLOB) columns

24
Data Reader vs. DataSet vs. XMLReader 3/3

• Use XMLReader when
• processing the retrieved data as XML, without
  incurring performance overhead of creating a
  DataSet
• Only supported by SQL data Provider

25
Stored Procedures vs. SQL Text

• Stored Procedures are
• Pre-compiled code!
• Tightly Integrated with back end (Database)
  Server
• Looks neater with parameters
• Even better with CLR Integration in SQL Server
  2005
• SQL Text is
• (could be) ANSI compatible
• Interpreted at runtime
• Loosely coupled with the backend database server

26
Best Practices (Passing Data Through Tiers)

• Data Access Logic Components
• CRUD Operations
• Business Entities
• Abstract Data Types
• DALCs messaging format
• Could be
• XML (XML String or XML DOM)
• DataSet
• Typed DataSet
• Business Entity (with/without CRUD Ops)

27
Implementing DALC

• Reliance on DAL (DB Helper Classes)
• Acts as a façade to different applications/layers
• Passing data
• Scalar Values
• XML Strings
• DataSets
• DataReaders
• Custom Objects
• Handling Transactions

28
Implementing Business Entities

• Dont access database directly
• Can be passed as an IO/Message to the business
  process
• Can travel on the wire (Serializable)
• Do not initiate any transactions
• Can be represented as
• XML
• DataSet (typed/un-typed)
• Custom Object (with / without CRUD)

29
Summary

• DALs
• DALC
• Business Entities
• Best Practices
• Max Reusability
• Get ready for next release

30
Links Resources

• MSDN article "ADO.NET for the ADO Programmer," at
  http//msdn.microsoft.com/library/default.asp?url
  /library/en-us/dndotnet/html/adonetprogmsdn.asp.
• ADO.Net 2.0 Feature Matrix. (http//msdn.microsoft
  .com/data/default.aspx?pull/library/en-us/dnvs05/
  html/ado2featurematrix.asp)
• .Net Data Access Architecture Guide.(http//msdn.m
  icrosoft.com/library/default.asp?url/library/en-u
  s/dnbda/html/daag.asp)
• Best Practices for ADO.Net (http//msdn.microsoft.
  com/library/default.asp?url/library/en-us/dnadone
  t/html/adonetbest.asp)

31
Q A

• Ask your questions
• Comments and Feed back
• Mail hammad.rajjoub_at_ineta.org
• URLhttp//dotnetwizards.blogspot.com