'NET Mobile Application Development Data in Distributed Systems

1
.NET Mobile Application DevelopmentData in
Distributed Systems
Accessing Data with .NET
2
Introduction

• In this session we will consider
• The use of databases in mobile and distributed
  applications
• Connected, client-server database access
• .NET connected data access features
• Disconnected database architecture
• ADO .NET and disconnected data access features
• Distributed data access from mobile and
  disconnected clients
• SQL Server CE

3
Database Terminology

• Relational database
• data and relations between them organised in
  tables. A table is collection of rows or records.
  Each row in a table contains the same fields.
• Query
• formal request to a database for information or
  updates to information
• Stored procedure
• set of SQL commands that can be stored in the
  server
• gives improved performance as less information
  needs to be sent between the server and the
  client
• Structured Query Language (SQL)
• industry-standard language for creating, updating
  and, querying relational databases

4
Databases in Distributed Systems

• Distributed applications often rely on databases
  for
• persistent storage
• synchronizing state and ensuring consistency
  between multiple clients
• Databases
• normally reside on a server
• accessed by clients via the network
• Clients interact with databases using explicit
  SQL queries
• active connection to database required for each
  query

5
Distributed Data Management

• Three key aspects of managing data in distributed
  applications
• Caching
• keeping local copies of required data eliminates
  need for continuous network connections
• Replication
• propagating changes in cached data back to main
  data store
• periodically refreshing cached data
• Conflict Resolution
• multiple clients operate simultaneously
• updates of a data item by several clients gt
  conflict
• conflict resolution mechanisms needed

6
Database Access Methods

• Traditional applications follow a layered model
• Business logic/client layers interact with the
  database in a connected client/server manner
• connection to database opened at start of session
  and maintained until session completes
• SQL queries sent individually or in batches to
  database when required by application
• application cannot operate if network connection
  not present
• not ideally suited to mobile / intermittently
  connected clients

7
Disconnected Database Access

• In a disconnected data architecture client
• opens temporary connection to database required
  data cached locally and connection closed
• operates using the cached data
• reconnects to database when connection available
  and merges any changes from the locally cached
  data
• Guaranteed network connection not required for
  application to function
• Ideally suited to
• mobile device applications
• intermittently connected devices
• clients with high network latencies (e.g.
  Internet)

8
.NET Data Access Features

• .NET supports
• Connected and disconnected data architectures
• Microsoft SQL Server data sources interaction
  from desktop and mobile platforms
• Interaction with other database servers through
  ODBC and OLE from desktop platforms only
• .NET data access classes contained in
• System.Data - Common data access types
• System.Data.SqlClient - Types used for
  interacting with MS SQL Server
• System.Data.Odbc - Types used for ODBC database
  interaction
• System.Data.OleDb - Types used for OLE database
  interaction

9
.NET Connected Data Objects

• Connection object (e.g. SqlConnection)
• Represents connection to data source used by
  other objects to interact with data source
• Command object (e.g. OdbcCommand)
• Represents queries to database or calls to stored
  procedures
• DataReader object (e.g. OleDbDataReader)
• Allows iteration through rows returned from a
  query
• Transaction object (e.g. SqlTransaction)
• Provides transactional support
• Parameter object (e.g. SqlParameter)
• Represents parameters to queries or stored
  procedures
• DataAdapter object (e.g. SqlDataAdapter)
• Bridge between database and the disconnected data
  objects of ADO .NET
• Used in passing data between ADO .NET objects and
  data source

10
Connected Data Access Example

• using System.Data.SqlClient
• ...
• string connStr "Initial CatalogNorthwindData
  SourcelocalhostIntegrated SecuritySSPI"
• string insertQuery "INSERT INTO Customers
  (CustomerID, CompanyName) Values('NWIND',
  'Northwind Traders')"
• SqlConnection conDB new SqlConnection(connStr)
• SqlCommand cmd new SqlCommand(insertQuery)
• cmd.Connection conDB
• conDB.Open()
• cmd.ExecuteNonQuery()
• conDB.Close()

11
ADO .NET Disconnected Data Objects

• DataTable object
• Stores data as collection of rows and columns
• DataColumn object
• Stores information about structure of a DataTable
  column
• Constraint object
• Enables constraints to be placed on data stored
  in a DataTable
• DataRow object
• Contains single record from a DataTable
• DataSet object
• Most important ADO .NET object
• Container for multiple DataTables and
  DataRelations
• DataRelation object
• Defines relationships between DataTables in a
  DataSet
• DataView object
• Provides sorting and filtering of data in a
  DataSet
• Enables binding of data to WinForms controls

12
Using ADO .NET Objects

• ADO .NET objects can
• be created and manipulated independently of a
  data source
• be constructed to mirror the relational structure
  of a data source
• Using a DataAdapter object, a DataSet can
• be filled with data from a data source
• merge changed values back in to a data source
• become an offline cache of the data required by
  an application
• Allows application to operate even when network
  connection is not present

13
Disconnected Data Access Example

• using System.Data
• using System.Data.SqlClient
• ...
• string connStr "Initial CatalogNorthwindData
  Sourcelocalhost Integrated SecuritySSPI"
• SqlConnection conDB new SqlConnection(connStr)
• SqlDataAdapter adapter new SqlDataAdapter()
• adapter.SelectCommand new SqlCommand("SELECT
  FROM Suppliers", conDB)
• conDB.Open()
• DataSet ds new DataSet("Customers")
• adapter.Fill(ds)
• conDB.Close()
• string criteria "Surname Smith"
• DataRow matchRows ds.Tables"Customers".Selec
  t(criteria)
• foreach (DataRow row in matchRows)
• string forename row"Forename"

14
Databases and Mobile Devices

• All the preceding techniques can be used with
  mobile devices and Compact Framework
• Mobile devices support three other techniques
• SQL Server CE
• SQL Server database engine for WinCE devices
• SQL Server CE Remote Data Access (RDA)
• enables two-way transfer of data between SQL CE
  database and networked SQL Server database
• SQL Server Merge Replication
• more powerful mechanism for two-way data transfer
  between SQL CE database and networked SQL Server
  database with automatic conflict resolution

15
SQL Server CE

• Lightweight version of MS SQL Server for Windows
  CE devices
• Supports
• creation, querying, updating and deleting of
  databases on device
• useful for local caching of data from network
  databases to enable offline operation
• Supports databases up to 2GB but size limited by
  available memory
• Accessible from Compact Framework using classes
  in System.Data.SQlServerCe namespace
• SqlCeEngine - used to creates new databases
• SqlCeConnection
• SqlCeCommand
• SqlCeDataAdapter
• SqlCeDataReader
• Use is similar to standard SQL client

16
SqlCE Remote Data Access

• Based around SqlCeRemoteDataAccess class
• Two primary methods
• Pull()
• retrieves data from networked SQL server and
  copies it to table in local SQL CE database
• pulled records not locked on networked database
• Push()
• writes modifications back to networked SQL server
  based on data in local SQL CE database table
• overwrites changes made by other users to
  networked database (optimistic concurrency
  control)
• may result in lost updates
• Networked SQL Server requires IIS Web server and
  specific configuration for RDA

17
SqlCe Merge Replication

• Extension of SQL Server Merge Replication to
  mobile devices
• Three databases used
• Publisher - makes data available for replication
• Distributor - manages replication and updates
• Subscriber - receives/uses replicated data (on CE
  device)
• Configurable resolvers used to automatically deal
  with multiple updates without loss of data
• Merge replication vs Remote Data Access
• requires much more configuration than RDA
• much easier to use automatically deals with
  conflicts

18
Summary

• In this session we have discussed
• Databases in distributed applications
• Connected database access with .NET
• ADO .NET and the disconnected database
  architecture
• Distributed data access from mobile and
  disconnected clients
• The accompanying practical exercises demonstrate
• Use of ADO .NET classes in manipulating data
• Using ADO .NET to retrieve data from a networked
  database using Web services

19
Reading and Resources

• Reading
• Thomas Connolly Carolyn Begg, Database Systems
  A Practical Approach to Design, Implementation,
  and Management, Addison-Wesley, 2002
• David Sceppa, Microsoft ADO .NET Core Reference,
  Microsoft Press, 2002
• Andy Wigley Stephen Wheelwright, Microsoft
  Compact Framework Core Reference, Microsoft
  Press, 2003
• Chapters 13 17, pp 429 586
• Resources
• Microsoft Developer Network, SQL Server
  Documentation, http//msdn.microsoft.com/library/d
  efault.asp?url/nhp/default.asp?contentid28000409