ADO.NET & Data Persistence Frameworks

1. ADO.NET & Data Persistence Frameworks

2. Overview • Serialization • ADO.NET • Data Tier Approaches • Persistence Frameworks

3. Serialization • Overview • Serialization Process • MBV vs MBR

4. Serialization • What is Serialization? • The ability to persist an object’s state data to a given location (remote server, file, memory, etc.) • The ability to read persisted data from a given location and recreate a new type based on the preserved stateful values • Plays an important role with ADO.NET and distributed architectures

5. Serialization • Serialization Process Some Storage Device My Object Formatter Serialize XmlSerializer – xml BinaryFormatter – binary SoapFormatter - soap New Object Formatter Deserialize File, Memory, Buffer, Socket, etc.

6. Serialization • Serialization is used by .NET Remoting and .NET Web Services to send objects back and forth • The .NET Framework provides 3 built-in class for serialization: • XmlSerializer • BinaryFormatter • SoapFormatter

7. Serialization • Serialization in Context Machine B Machine A Serialize Deserialize Order <order> Order DB Save Process Deserialize Serialize Receipt Receipt <order>

8. Serialization • Differences: • XmlSerializer: only serializes public variables, serializes to standard XML • BinaryFormatter: serializes all variables (public, private, etc.), serializes to bytes • SoapFormatter: serializes all variables (public, private, etc.), serializes to SOAP • Demo

9. Serialization • Implications: • BinaryFormatter is the fastest • XmlSerializer is normally second fastest (depends on the amount of non-public variables) • SoapSerializer is normally the slowest • XmlSerializer should be used for multi-platform/language clients (used by Web Services) • BinaryFormatter / SoapFormatter is targeted for .NET environments

10. Serialization • How objects are passed • MBV – Marshal by Value • The caller receives a full copy of the object in its own application domain • Object code is executed in the local application • MBV objects are declared by using the [Serializable] attribute, or by inheriting from the ISerializable interface • [Serializable]public class MBVClass{…}ORpublic class MBVClass: ISerializable

11. Serialization • How objects are passed • MBR – Marshal by Reference • The caller receives a proxy to the remote object • Object code is executed in the remote application • MBR objects are declared by inheriting from MarshalByRefObject • public class MBRClass : MarshalByRefObject

12. Serialization • Visualization MBV Machine B Machine A 1. Request Order 3. Request Calculate Total Order Order 2. Receive Order 4. Display Total MBR 1. Request Order 3. Request Calculate Total Order Order 2. Receive Order Proxy 4. Send Calculate Request 5. Calculate Total 7. Display Total 6. Send Total

13. Serialization • MBV vs MBR demo

14. ADO.NET • Overview • ADO.NET Classes • XMLDataDocuments • What are the pros and cons of each? • Issues with ADO.NET

15. ADO.NET • What is ADO.NET? • Framework that allows you to interact with local and remote data stores • Major overhaul of ADO (few similarities) • Optimized libraries for SqlServer (+CE), Oracle • Generic libraries for ODBC, OleDb • Intrinsic support for Xml • Focused on both connected and disconnected systems

16. ADO.NET • High-Level View In-Memory Disconnected Client DataSet IDbDataAdapter IDbCommand IDataReader A managed provider implements these interfaces to provide access to a specific type of data store IDbConnection DB

17. ADO.NET • Object Model Connected Objects Disconnected Objects Connection DataSet DataTable DataView Transaction DataRow DataAdapter DataColumn Command Constraint Parameter DataRelation DataReader

18. ADO.NET • IDbConnection • Represents a network connection to a relational database • Resource intensive, so connections should be kept open as little as possible (pass through if possible) • Connection Pooling is automatically enabled for .NET IDbConnection implementations • a connection pool is created based on an exact matching algorithm that associates the pool with the connection string

19. ADO.NET • IDbCommand • Represents a SQL statement that is executed while connected to a data source • Provides 3 primary means of submitting a SQL statement: • ExecuteNonQuery – nothing returned • ExecuteScalar – 1 field returned • ExecuteReader – returns IDataReader implementation

20. ADO.NET • IDbCommand cont. • Used for standard SQL text and/or stored procedures • Allows for parameters to be passed in via an IDataParameter implementation

21. ADO.NET • IDataReader (Forest Gump) • Provides a means of reading one or more forward-only streams of result sets • DEMO

22. ADO.NET • IDbDataAdapter • Acts as a bridge between your database and the disconnected objects in ADO.NET • Object’s Fill method provides an efficient way to fetch the results of a query and place them into a DataSet or DataTable (which can then be used offline) • Reads cached changes from a DataSet or DataTable and submits them to the database

23. ADO.NET • DataTable • One of the central objects in ADO.NET • Used by DataSet, DataView, etc. • Represents one table of data in-memory • Allows you to: • Fetch data from a DB and store it in a DataTable • Disconnect from the DB and work with the DataTable offline • Reconnect and synchronize changes

24. ADO.NET • DataTable cont. • DataTable structure is similar to DB Table structure: • DataTable is composed of DataRows • DataRows are composed of DataColumns • Constraints can be set on a DataTable • DataTables can also be created and populated in code (they do note require a corresponding DB Table)

25. ADO.NET • DataTable cont. • Can be remoted (allows for both MBV and MBR)

26. ADO.NET • DataView • Represents a customized view of a DataTable • Allows for: • Sorting • Filtering • Editing • Searching

27. ADO.NET • DataView cont. • Allows multiple controls to bind to the same DataTable, but show different data

28. ADO.NET • DataSet (La Femme Nikita) • Major component of the ADO.NET architecture • Collection of DataTables • Allows for relationships between tables to be created via DataRelation • Allows constraints to be set on data

29. ADO.NET • DataSet cont. • Can read and write data and schema as XML documents • Can be remoted (allows for both MBV and MBR) • Strongly-typed DataSets can be generated • Can access tables and columns by name, instead of using collection-based methods • Allows for VS.NET Intellisense

30. ADO.NET • DataSet cont. • Ability to merge multiple DataSets • Ability to copy DataSets • Uses DiffGram (XML) to load and persist changes • DEMO

31. ADO.NET • CommandBuilder • Automatically generates single-table commands • DEMO

32. ADO.NET • XmlDataDocument • Solves problem of unsychronized access to relational / XML data • Example: • DataSet (relational) writes out XML file • XmlDocument reads in and manipulates XML • Two objects dealing with same data in unsynchronized manner • Result is a disconnect

33. ADO.NET • XmlDataDocument cont. • Synchronizes data between XmlDocument and DataSet • Allows both objects to work on the same set of data • Allows a single app, using a single set of data, to harness the power of: • DataSets (remoting, databinding, DataViews...) • Xml (XPath, XSL, XSLT…)

34. ADO.NET • Pros and Cons • Best Practices

35. ADO.NET • IDbConnection Best Practices • Pass through whenever possible • Use constant connectionstrings • Use multiple accounts (1 for read access, 1 for read/write access, etc.) • Use Windows Authentication • If stored in config file, encrypt • Avoid displaying error sensitive error information • Avoid using OleDbConnection.State • Use the “using” statement in C#

36. ADO.NET • IDbCommand Best Practices • Use parameters when possible to avoid SQL injections

37. ADO.NET • IDataReader Pros and Cons • Extremely fast performance (better than DataSet) • Forward-only, read-only • Can only operate in connected mode • Must explicitly close both the IDataReader and the IDbConnection • Not remotable

38. ADO.NET • IDataReader Best Practices • Use CommandBehavior.CloseConnection and CommandBehavior.SequentialAccess when possible • Use IDataReader.Get[Type]() whenever possible (performance boost) • Call IDataReader.Cancel() if you’re done w/ a DataSet but still have pending data • Keep connection time to a minimum • Use for large volumes of data to avoid memory footprint

39. ADO.NET • DataTable / DataSet Pros and Cons • Both MBV and MBR behavior • Ability to work with data offline • Ability to represent the same data in multiple ways via DataView • Data bindable • Decreased performance in comparison w/ IDataReader • Consumes machine memory • Developers must be careful when posting changes in a distributed environment

40. ADO.NET • DataSet Best Practices • Strongly-type when possible • Use for modifiable data and or data that will be navigated • Use for caching of frequently searched items • Use DataSet.GetChanges() prior to sending across the wire • Avoid the use of the DataAdapter.Fill overload that takes startRecord and maxRecords parameters • Use MBR behaviour sparingly

41. ADO.NET • CommandBuilder Pros / Cons • Removes the need to manually write code • Decreased performance, due to the need to hit a database twice to retrieve schema information • Decreased performance due to very verbose SQL statements • Better to use VS.NET’s built-in code generation • MS: Use of the CommandBuilder should be limited to design time or ad-hoc scenarios

42. ADO.NET • Performance Matrix • IDataReader w/ Get[Type] • IDataReader w/ GetOrdinal • IDataReader by column name • Strongly-Typed DataSet w/ custom code • DataSet w/ custom code • Strongly-Typed DataSet w/ CommandBuilder • DataSet w/ CommandBuilder

43. ADO.NET • Issues w/ ADO.NET • No bulk SQL execution (DataSet batch submissions aren’t done in bulk) • No asynchronous calls • Can’t write to interfaces…must use providers directly • Inability to get full SQL from IDbCommand.CommandText when using params • No object-relational mapping • No SQL API

44. Data Tier Approaches • Overview • Presentation – Data • Presentation – Business – Data • Presentation – Business – Service – Data

45. Data Tier Approaches • Presentation - Data Data Access Code DB Bus Logic Data Access Code Bus Logic

46. Data Tier Approaches • Presentation – Data • Often has best performance • Initially the fastest to write (but often requires the most code over time) • Inflexible to change • Business logic not available • Results in code duplication (business logic…) • Error prone (connection strings, closing connections, etc.) • Leaves architecture decisions to implementer (i.e. DataSet, DataReader, etc.) • Ties presentation layer to returned data format

47. Data Tier Approaches • Presentation – Data w/ DAL Bus Logic Data Access Layer DB Bus Logic

48. Data Tier Approaches • Presentation – Data w/ DAL • Requires less code • Somewhat flexible to change • Business logic not available • Results in code duplication (business logic…) • Ties presentation layer to returned data format

49. Data Tier Approaches • Presentation – Business – Data (3 Tier) Bus Logic Layer Data Access Layer DB

50. Data Tier Approaches • Presentation – Business – Data • Much more flexible to change • Business logic is centralized • Does not tie presentation layer to returned data format • More complex to design and build • Implicit changes may need to be cascaded through all layers • Does not clearly address remoting issues • Business Logic must still be aware of DA classes