Module 3: Working with Local Data

Module 3: Working with Local Data

Overview

Using DataSets

Using XML

Using SQL Server CE

Lesson: Using DataSets

ADO.NET Model

Creating a DataSet

Filling the DataSet

Persisting the DataSet as an XML File

Binding to a DataSet

Using a DataGrid

ADO.NET Model

DataSet DataSet

.NET Data Provider .NET Data Provider

Tables Tables

Connection Connection

DataAdapter DataAdapter

DataTable DataTable

Transaction Transaction

SelectCommand SelectCommand

DataRowCollection DataRowCollection

InsertCommand InsertCommand

Command Command

DataColumnCollection DataColumnCollection

Parameters Parameters

UpdateCommand UpdateCommand

ConstraintCollection ConstraintCollection

DeleteCommand DeleteCommand

DataReader DataReader

DataRelationCollection DataRelationCollection

Database Database

XMLXML

Creating a DataSet

D i m   m y D S   A s   N e w   D a t a S e t ( " P r o j e c t " ) D i m   m y D S   A s   N e w   D a t a S e t ( " P r o j e c t " ) D i m   m y D T   A s   D a t a T a b l e   =   _ D i m   m y D T   A s   D a t a T a b l e   =   _ m y D S . T a b l e s . A d d ( " T a s k " ) m y D S . T a b l e s . A d d ( " T a s k " ) m y D T . C o l u m n s . A d d ( " N a m e " ,   _ m y D T . C o l u m n s . A d d ( " N a m e " ,   _             S y s t e m . T y p e . G e t T y p e ( " S y s t e m . S t r i n g " ) ) S y s t e m . T y p e . G e t T y p e ( " S y s t e m . S t r i n g " ) ) m y D T . C o l u m n s . A d d ( " S t a r t " ,   _ m y D T . C o l u m n s . A d d ( " S t a r t " ,   _             S y s t e m . T y p e . G e t T y p e ( " S y s t e m . S t r i n g " ) ) S y s t e m . T y p e . G e t T y p e ( " S y s t e m . S t r i n g " ) ) m y D T . C o l u m n s . A d d ( " D u r a t i o n " ,   _ m y D T . C o l u m n s . A d d ( " D u r a t i o n " ,   _             S y s t e m . T y p e . G e t T y p e ( " S y s t e m . S t r i n g " ) ) S y s t e m . T y p e . G e t T y p e ( " S y s t e m . S t r i n g " ) )

DataSet DataSet

DataTable DataTable

Filling the DataSet

D i m   m y D R   A s   D a t a R o w   =   _ D i m   m y D R   A s   D a t a R o w   =   _       m y D S . T a b l e s ( " T a s k " ) . N e w R o w ( )       m y D S . T a b l e s ( " T a s k " ) . N e w R o w ( ) m y D R ( " N a m e " )   =   " D e s i g n   C o d e "   m y D R ( " N a m e " )   =   " D e s i g n   C o d e "   m y D R ( " S t a r t " )   =   " 2 / 1 / 2 0 0 3 "   m y D R ( " S t a r t " )   =   " 2 / 1 / 2 0 0 3 "   m y D R ( " D u r a t i o n " )   =   " 2   d a y s "   m y D R ( " D u r a t i o n " )   =   " 2   d a y s "   m y D S . T a b l e s ( " T a s k " ) . R o w s . A d d ( m y D R ) m y D S . T a b l e s ( " T a s k " ) . R o w s . A d d ( m y D R )

Name

Start

Duration

Design UI

1/1/2003

I day

Design Code

2/1/2003

2 days

Practice: Using DataSets to Access Data

Creating and filling a DataSet Creating and filling a DataSet

1

Adding to a DataSet from a form Adding to a DataSet from a form

2

Persisting the DataSet as an XML File

DataSet provides volatile storage

Use the WriteXml method to save data

m y D a t a S e t . W r i t e X m l ( " w i n \ t m p . x m l " ) m y D a t a S e t . W r i t e X m l ( " w i n \ t m p . x m l " )

Use the ReadXml method to populate data from the file

D i m   m y D a t a S e t   A s   N e w   D a t a S e t ( ) D i m   m y D a t a S e t   A s   N e w   D a t a S e t ( ) m y D a t a S e t . R e a d X m l ( " w i n \ t m p . x m l " ) m y D a t a S e t . R e a d X m l ( " w i n \ t m p . x m l " )

Practice: Persisting the DataSet as XML

Save a DataSet as an XML file Save a DataSet as an XML file

1

Verify the XML file Verify the XML file

2

Binding to a DataSet

DataSource property

 Binds a control to the data source  Provides link from mobile application to DataSet

D i m   d t   A s   D a t a T a b l e   =   _ D i m   d t   A s   D a t a T a b l e   =   _       t m p D S . T a b l e s ( " I n f o " )       t m p D S . T a b l e s ( " I n f o " ) ' B i n d   t o   t h e   l i s t   b o x ' B i n d   t o   t h e   l i s t   b o x l i s t B o x 1 . D a t a S o u r c e   =   d t l i s t B o x 1 . D a t a S o u r c e   =   d t ' S e t   c o l u m n   t o   b i n d   t o ' S e t   c o l u m n   t o   b i n d   t o l i s t B o x 1 . D i s p l a y M e m b e r   =   " N a m e " l i s t B o x 1 . D i s p l a y M e m b e r   =   " N a m e "

Using a DataGrid

Provides a user interface for entire tables in a DataSet

Rich formatting capabilities

DataGrid is bound to a data source at run time (not design time)

Practice: Binding a Control to a DataSet

Binding a control to a DataSet Binding a control to a DataSet

1

Verifying the binding Verifying the binding

2

Lesson: Using XML

Supported XML Classes

Building an XmlDocument

Reading an XmlDocument

Supported XML Classes

XmlTextReader and XmlTextWriter

 Forward-only parsers of XML data

 Better performance because there is no in-memory

caching

XmlDocument

 Data can be read into the object

 After modification, data can be read from the object back

to a stream

Building an XmlDocument

P r i v a t e   F u n c t i o n   B u i l d X m l D o c u m e n t ( )   A s   P r i v a t e   F u n c t i o n   B u i l d X m l D o c u m e n t ( )   A s   X m l D o c u m e n t X m l D o c u m e n t       D i m   m y X m l D o c   A s   N e w   X m l D o c u m e n t ( )       D i m   m y X m l D o c   A s   N e w   X m l D o c u m e n t ( )       D i m   N e w N o d e   A s   X m l N o d e       D i m   N e w N o d e   A s   X m l N o d e       N e w N o d e   =         N e w N o d e   =   m y X m l D o c . C r e a t e E l e m e n t ( " P r o j e c t " ) m y X m l D o c . C r e a t e E l e m e n t ( " P r o j e c t " )       m y X m l D o c . A p p e n d C h i l d ( N e w N o d e )       m y X m l D o c . A p p e n d C h i l d ( N e w N o d e )       N e w N o d e   =   m y X m l D o c . C r e a t e E l e m e n t ( " T a s k " )       N e w N o d e   =   m y X m l D o c . C r e a t e E l e m e n t ( " T a s k " )       N e w N o d e . I n n e r T e x t   =   " W r i t e   C o d e "       N e w N o d e . I n n e r T e x t   =   " W r i t e   C o d e "             m y X m l D o c . D o c u m e n t E l e m e n t . A p p e n d C h i l d ( N e w N o d e m y X m l D o c . D o c u m e n t E l e m e n t . A p p e n d C h i l d ( N e w N o d e ) )       R e t u r n   m y X m l D o c       R e t u r n   m y X m l D o c E n d   F u n c t i o n E n d   F u n c t i o n

< P r o j e c t > < P r o j e c t >

< T a s k >   W r i t e   C o d e   < / T a s k > < T a s k >   W r i t e   C o d e   < / T a s k >

< / P r o j e c t > < / P r o j e c t >

Reading an XmlDocument

XmlDocument is an in-memory DOM tree

Navigate DOM using properties and methods of XmlNode class

Values of nodes can be extracted and manipulated

P r i v a t e   S u b   D i s p l a y X m l D o c u m e n t ( m y X m l D o c   A s   P r i v a t e   S u b   D i s p l a y X m l D o c u m e n t ( m y X m l D o c   A s   X m l D o c u m e n t ) X m l D o c u m e n t )       D i m   o N o d e s   A s   X m l N o d e L i s t   =   _       D i m   o N o d e s   A s   X m l N o d e L i s t   =   _             m y X m l D o c . D o c u m e n t E l e m e n t . C h i l d N o d e s             m y X m l D o c . D o c u m e n t E l e m e n t . C h i l d N o d e s       D i m   s b X M L D i s p l a y   A s   N e w   S t r i n g B u i l d e r ( )       D i m   s b X M L D i s p l a y   A s   N e w   S t r i n g B u i l d e r ( )       D i m   T a s k N o d e   A s   X m l N o d e       D i m   T a s k N o d e   A s   X m l N o d e       F o r   E a c h   T a s k N o d e   I n   o N o d e s       F o r   E a c h   T a s k N o d e   I n   o N o d e s             D i m   P r o p e r t y N o d e   A s   X m l N o d e             D i m   P r o p e r t y N o d e   A s   X m l N o d e             F o r   E a c h   P r o p e r t y N o d e   I n   T a s k N o d e             F o r   E a c h   P r o p e r t y N o d e   I n   T a s k N o d e                   s b X M L D i s p l a y . A p p e n d ( ( P r o p e r t y N o d e . N a m e   +   " :   "                     s b X M L D i s p l a y . A p p e n d ( ( P r o p e r t y N o d e . N a m e   +   " :   "   +   _ +   _                         P r o p e r t y N o d e . I n n e r T e x t   +   C o n t r o l C h a r s . L f ) )                         P r o p e r t y N o d e . I n n e r T e x t   +   C o n t r o l C h a r s . L f ) )             N e x t   P r o p e r t y N o d e             N e x t   P r o p e r t y N o d e       N e x t   T a s k N o d e       N e x t   T a s k N o d e       M e s s a g e B o x . S h o w ( s b X M L D i s p l a y . T o S t r i n g ( ) )       M e s s a g e B o x . S h o w ( s b X M L D i s p l a y . T o S t r i n g ( ) ) E n d   S u b E n d   S u b

Practice: Adding an Element to an XmlDocument

Add an element to an XmlDocument Add an element to an XmlDocument

1

Verify that the element is added Verify that the element is added

2

Lesson: Using SQL Server CE

SQL Server CE Storage Architecture

Working with SQL Server CE

Using SQL Server CE Query Analyzer

Using a SQL Server CE Data Connector

Filling a DataSet from SQL Server CE

Using Parameterized Queries

Reading Data

Updating SQL Server CE from the DataSet

SQL Server CE Storage Architecture

.NET Compact Framework Managed Stack .NET Compact Framework Managed Stack

SQL Server 2000

Visual Studio .NET (Visual Basic .NET, C#) Visual Studio .NET (Visual Basic .NET, C#)

ADO.NET ADO.NET

SQL Server CE Data Provider SQL Server CE Data Provider

Server

.NET Compact Framework runtime .NET Compact Framework runtime

OLE DB for Windows CE OLE DB for Windows CE

SQL Server CE SQL Server CE

Data Provider Data Provider

IIS

HTTPHTTP

QP/Cursor Engine/ES QP/Cursor Engine/ES

Server Agent: Replication and RDA

Client Agent: Client Agent: Replication Replication and RDA and RDA

Storage Engine/ Storage Engine/ Replication tracking Replication tracking

Client Client

Working with SQL Server CE

Available database storage in Pocket PC is limited SQL Server CE 2.0 Features (see list in Student Notes) Visual Studio .NET automatically configures development environment for use with SQL Server CE  SQL Server CE 2.0 is included with the installation of

Visual Studio .NET

 Must still configure IIS and Windows CE-based device

Installing SQL Server CE on the client device

 Add a reference to System.Data.SqlServerCe

– or –

 Manually copy and extract core platform CAB files

Using SQL Server CE Query Analyzer

A

Tap to execute a SELECT * FROM Tap to execute a SELECT * FROM Employees statement Employees statement

B

Tap to add a column to the Tap to add a column to the Employees table Employees table

C

Tap to create an index on the Tap to create an index on the Employees table Employees table

Tap to drop the Employees table Tap to drop the Employees table

D

A B C D

Demonstration: Using the SQL Server CE Query Analyzer

Using a SQL Server CE Data Connector

Connection string to SQL Server requires a database provider

S q l C o n n S t r i n g   =   " P r o v i d e r = s q l o l e d b ; S q l C o n n S t r i n g   =   " P r o v i d e r = s q l o l e d b ;       D a t a   S o u r c e = L o n d o n ;       D a t a   S o u r c e = L o n d o n ;       I n i t i a l   C a t a l o g = N o r t h w i n d "       I n i t i a l   C a t a l o g = N o r t h w i n d "

Connection string to SQL Server CE is similar, but a database provider is not specified

S q l C e C o n n S t r i n g   = S q l C e C o n n S t r i n g   =       " D a t a   S o u r c e = M y         " D a t a   S o u r c e = M y   D o c u m e n t s \ N o r t h w i n d . s d f " D o c u m e n t s \ N o r t h w i n d . s d f "

Filling a DataSet from SQL Server CE

Establish a connection

Create a data adapter

Call the Fill method

D i m   m y A d a p t e r   A s   N e w   S q l C e D a t a A d a p t e r ( ) D i m   m y A d a p t e r   A s   N e w   S q l C e D a t a A d a p t e r ( ) m y A d a p t e r . T a b l e M a p p i n g s . A d d ( " T a b l e " ,   " T i t l e s " ) m y A d a p t e r . T a b l e M a p p i n g s . A d d ( " T a b l e " ,   " T i t l e s " ) c n . O p e n ( ) c n . O p e n ( ) D i m   m y C o m m a n d   A s   N e w   S q l C e C o m m a n d (   _ D i m   m y C o m m a n d   A s   N e w   S q l C e C o m m a n d (   _       " S E L E C T   *   F R O M   T i t l e s " ,   c n )       " S E L E C T   *   F R O M   T i t l e s " ,   c n ) m y C o m m a n d . C o m m a n d T y p e   =   C o m m a n d T y p e . T e x t m y C o m m a n d . C o m m a n d T y p e   =   C o m m a n d T y p e . T e x t m y A d a p t e r . S e l e c t C o m m a n d   =   m y C o m m a n d m y A d a p t e r . S e l e c t C o m m a n d   =   m y C o m m a n d D i m   d s   A s   N e w   D a t a S e t ( ) D i m   d s   A s   N e w   D a t a S e t ( ) m y A d a p t e r . F i l l ( d s ) m y A d a p t e r . F i l l ( d s )

Using Parameterized Queries

Parameterized queries

 Have built-in input validation

 Execute quicker and are more secure

'   I n s e r t   d a t a   i n t o   t h e   t a b l e . '   I n s e r t   d a t a   i n t o   t h e   t a b l e . S Q L   =   " I N S E R T   I N T O   T i t l e s   ( T i t l e I D , T i t l e N a m e ) S Q L   =   " I N S E R T   I N T O   T i t l e s   ( T i t l e I D , T i t l e N a m e )

V A L U E S   ( ? , ? ) " V A L U E S   ( ? , ? ) "

c m d . C o m m a n d T e x t   =   S Q L c m d . C o m m a n d T e x t   =   S Q L c m d . P a r a m e t e r s . A d d ( " @ T i t l e I D " , c m d . P a r a m e t e r s . A d d ( " @ T i t l e I D " ,

S y s t e m . D a t a . S q l D b T y p e . N C h a r ,   5 ) S y s t e m . D a t a . S q l D b T y p e . N C h a r ,   5 )

c m d . P a r a m e t e r s . A d d ( " @ T i t l e N a m e " , c m d . P a r a m e t e r s . A d d ( " @ T i t l e N a m e " ,

S y s t e m . D a t a . S q l D b T y p e . N V a r C h a r ,   4 0 )   S y s t e m . D a t a . S q l D b T y p e . N V a r C h a r ,   4 0 ) c m d . P a r a m e t e r s [ " @ T i t l e I D " ] . V a l u e   =   " M S C F 1 " c m d . P a r a m e t e r s [ " @ T i t l e I D " ] . V a l u e   =   " M S C F 1 " c m d . P a r a m e t e r s [ " @ T i t l e N a m e " ] . V a l u e   =   " C o m p a c t   c m d . P a r a m e t e r s [ " @ T i t l e N a m e " ] . V a l u e   =   " C o m p a c t   F r a m e w o r k " F r a m e w o r k " c m d . E x e c u t e N o n Q u e r y ( ) c m d . E x e c u t e N o n Q u e r y ( )

Demonstration: Creating a Local Data Store

Create a SQL Server CE table

Populate the table

Reading Data

The ExecuteReader method runs the SQL or stored procedure and returns a DataReader object

The Read method moves the DataReader to the next record

Read must be called before data access methods are used

D i m   r e a d e r   A s   _ D i m   r e a d e r   A s   _       S y s t e m . D a t a . S q l S e r v e r C e . S q l C e D a t a R e a d e r         S y s t e m . D a t a . S q l S e r v e r C e . S q l C e D a t a R e a d e r   =   _ =   _       c m d T x t . E x e c u t e R e a d e r ( )       c m d T x t . E x e c u t e R e a d e r ( ) W h i l e   r e a d e r . R e a d ( ) W h i l e   r e a d e r . R e a d ( )       M e s s a g e B o x . S h o w ( r e a d e r . G e t S t r i n g ( 0 ) )       M e s s a g e B o x . S h o w ( r e a d e r . G e t S t r i n g ( 0 ) ) E n d   W h i l e E n d   W h i l e

Updating SQL Server CE from the DataSet

Save new or modified DataSet data to SQL Server CE

Update method updates the SQL Server CE table with changes made in the DataSet

m y C o n n . O p e n ( ) m y C o n n . O p e n ( ) D i m   c u s t D S   A s   N e w   D a t a S e t ( ) D i m   c u s t D S   A s   N e w   D a t a S e t ( ) m y D a t a A d a p t e r . F i l l ( c u s t D S ) m y D a t a A d a p t e r . F i l l ( c u s t D S ) ' c o d e   t o   m o d i f y   d a t a   i n   d a t a s e t   h e r e ' c o d e   t o   m o d i f y   d a t a   i n   d a t a s e t   h e r e m y D a t a A d a p t e r . U p d a t e ( c u s t D S ,   m y T a b l e N a m e ) m y D a t a A d a p t e r . U p d a t e ( c u s t D S ,   m y T a b l e N a m e ) m y C o n n . C l o s e ( ) m y C o n n . C l o s e ( )

Review

Using DataSets

Using XML

Using SQL Server CE

Lab 3: Working with Local Data

Exercise 1: Reading an XML File into a DataSet

Exercise 2: Appending Data to the XML File

Exercise 3: Saving Data to a SQL Server CE Table