.NET Mobile Application Development Data in Distributed Systems

Accessing Data with .NET

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

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

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

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 => conflict - conflict resolution mechanisms needed

Database Access Methods

 Traditional applications follow a layered model

Application front end Business Logic Database



Computer A Computer B Computer C

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

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)

.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

.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

Connected Data Access Example

using System.Data.SqlClient; ...

string connStr = "Initial Catalog=Northwind;Data Source=localhost;Integrated Security=SSPI;"; 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();

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

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

Disconnected Data Access Example

using System.Data; using System.Data.SqlClient; ...

string connStr = "Initial Catalog=Northwind;Data Source=localhost; Integrated Security=SSPI;"; 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"].Select(criteria); foreach (DataRow row in matchRows) { string forename = row["Forename"]; string forename = row["Surname"]; Console.WriteLine("Customer "+forename+" "+surname); }

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

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

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

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

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

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, 28000409

SQL Server Documentation

, http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=