ado.net training.ppt

2. Introductions: Stephen Walch

Microsoft technologies developer 12 years

Notes developer - 11 years

Built 9 products

Presented at 2 Lotuspheres, 1 MEC, others

Proposion, Inc.

Started January 2002

Integrating Microsoft .NET with Notes and Domino

3. Proposion Products (Q1 2005)

4. Agenda

Basic principles of ADO.NET

Introduction to Proposion N2N

Programming with ADO.NET classes

Lab #1: Web Contact List

Programming with ADO.NET DataSets

Proposion N2N Specifics

Lab #2: Notes Data Integrator

5. ADO.NET Basic principles (1)

All data providers implement the same set of standard interfaces:

DataConnection

DataCommand

DataParameter

DataReader

DataAdapter

6. ADO.NET Basic principles (2)

Microsoft ships some data providers:

SQL Server

Oracle

OleDb

ODBC

But third parties can implement their own:

IBM (for DB2)

Proposion N2N (for Notes/Domino)

7. Basic principles (3)

ADO.NET includes common classes for representing in-memory data:

DataSet, DataTable, DataView

May include schema and constraints

Designed to work with .NET XML classes

Great for caching or transporting data

Able to track state changes

Typed DataSets may be auto-generated

8. Basic principles (4)

Data binding allows DataSets (etc.) to be plugged in to user interface components

Data grids, List boxes, Text boxes

Third-party components (charts, etc.)

Windows, Web, Mobile

Support for development tools part of the plumbing

Visual Studio.NET, etc.

9. Example: Basic SQL Server query

10. So what about Notes/Domino?

NSF databases are loosely structured collections of documents/objects (notes)

Notes have named data elements (items)

Usually typed (text, number, date)

Can be multi-valued or binary (attachments)

Rich text and MIME

Notes can be linked in as parent/child

Views & Folders select and index notes

11. More weird Notes stuff

No schema or guarantee of data consistency!

Forms allow entry/display of notes

Agents and actions

Full-text search

Security

Database ACLs, roles, groups, encryption

Per-note reader and author restrictions

12. Introducing

13. About Proposion N2N

Data driver for connecting to Lotus Notes/Domino data and services

Managed ADO.NET data provider

Use any .NET language

Plug into ADO.NET components

Leverage data binding

Direct access to Notes core interfaces

Managed C++ and Notes C API

Extremely fast and robust

14. Proposion N2N features

Read/write/delete/mail documents

Navigate views/folders, Use full-text search

Use Forms to select data or validate inputs

Access Rich text, Attachments, Images

Run LotusScript agents, Use @Formulas

Unread marks, parent/response hierarchies

Access to design information

15. Alternatives

COM interfaces and .NET COM Interop

Familiar to LotusScript developers

Good option for Windows apps

Not good for robust, scaleable, multi-threaded web apps or web services

NotesSQL data driver and ODBC data provider

Slow and buggy

Feature poor

Notes C API and PInvoke

Web Services

16. Agenda








17. ADO.NET Data Connection

Represents an open connection to a data source

IDbConnection -> NsfConnection

Properties

ConnectionString

Methods

Open()

Close()

BeginTransaction() *

18. ADO.NET Data Command

Represents a command statement that is executed while connected to a data source

IDbCommand -> NsfCommand

Properties

Connection

CommandType, CommandText

Parameters

Methods

ExecuteNonQuery()

ExecuteReader()

19. ADO.NET Data Reader

Provides a means of reading a forward-only stream of results from a command

IDataReader, IDataRecord -> NsfDataReader

Properties

FieldCount

Item[name], Item[index]

Methods

Read()

GetName(index)

GetValue(index), GetString(index)

GetEnumerator()

20. Sample: Query with Data Reader

Create a connection

Create an SELECT command

Navigate the data

Create an INSERT command

Also:

Try/Finally, Using

Using visual data components

21. Building a Data Access Layer

Best practice to separate raw data access from application logic

Encapsulate use of ADO.NET classes

Results in reusable classes that can be called from multiple applications

Optional: Encapsulate all business rules

Optional: Design to be remotable via COM+, XML Web Services, or .NET Remoting

22. Sample: Data Access Layers

Create a new data access component

Implement data access method

Use the component in a Web Service

23. LAB #1: Web Contact List

Create ASP.NET project

Add reference to Proposion N2N

Populate a data grid from Notes

Post a web form

Send an email

24. Agenda








25. ADO.NET DataSet

Represents an in-memory cache of data.

A class, not an interface!

Properties

DataSetName

Tables //Data and schema

Relations

Methods

Clear(), Copy(), Merge()

GetChanges(), AcceptChanges(), RejectChanges()

ReadXml(), WriteXml()

26. ADO.NET DataTable

Properties

TableName

Columns //DataColumn

Constraints //Constraint

Rows //DataRow

DefaultView //DataView

ParentRelations, ChildRelations

Methods

NewRow(), ImportRow()

Select(expression), Compute(expression)

27. ADO.NET Data Adapter

Represents a set of methods and mappings to read/write a DataSet from/to a data source

IDbDataAdapter -> NsfDataAdapter

Properties

SelectCommand, InsertCommand

UpdateCommand, DeleteCommand

TableMappings

Methods

Fill(DataSet)

Update(DataSet)

28. Sample: Data Adapters

Configure a DataAdapter

Fill a DataSet

Navigate a DataSet

Bind to a DataGrid

Using visual tools to create an adapter

Dealing with the fact that Notes database do not have schema!

Read/Write data

29. Schemas and Typed DataSets

DataSets can have schemas

Data types, constraints, relations

Rules are enforced when loading data

Typed DataSets can be generated

Classes derived from DataSet

Schema information compiled in

Adds type-safe properties and methods

Based on XSD standard

Tools can use schema at design time

30. Sample: Schemas and Typed DataSets

Generating schema and Typed DataSet

Using a Typed DataSet in your code

Binding to Typed DataSets

31. Agenda








32. N2N: Selecting columns (1)

Basic:

SELECT item1, ITEM(item2), ITEM(item3) AS name3

Strong typing:

SELECT ITEM(item1, STRING), ITEM(item2, DATE)

View/Folder Columns:

SELECT COLUMN(name), COLUMN(0, STRING)

Multi-valued items:

Select ITEM(item1, STRING, MULTI), ITEM(item2, FLAT)

Notes identifiers

SELECT NOTEID(), UNID(), ISREAD()

33. N2N: Selecting columns (2)

Parent documents:

SELECT PARENTNOTEID(), PARENTUNID(), PARENTITEM(item1)

Formulas

SELECT FORMULA(@Created), PARENTFORMULA(@Created)

Rich Text:

SELECT RICHTEXT(body), RICHTEXT(body, XML), RICHTEXT(body, MIME)

Render with form:

SELECT RENDER(form1, XML)

Attachments:

SELECT ATTACHMENT(body, 0), ATTACHMENTID(body, 0), ATTACHMENTNAME(body, 0)

Images:

SELECT IMAGE(body, 0), IMAGETYPE(body, 0)

34. N2N: Selecting rows

Use a View/Folder index:

SELECT FROM view

Lookups:

SELECT FROM view WHERE KEY(value)

SELECT FROM view WHERE BEGIN(val1) AND END(val2)

Lookup by ID

SELECT WHERE NOTEID(1234)

SELECT WHERE UNID(0ae462f6663)

Full-Text Search:

SELECT WHERE SEARCH(expression, FUZZY, VARIANTS)

Dynamic Selection Formula

SELECT WHERE FORMULA(@DocumentLength > 100)

Paging

SELECT SKIP 100 LIMIT 10

Unread documents

SELECT UNREADONLY

35. N2N: Updating data

Basic update:

UPDATE FROM view1 SET item1=value, item2=@param2, item3+=value, item4=value/ENCRYPT WHERE KEY(@param4)

Form validation

UPDATE VALIDATE

Unread marks

UPDATE MARKREAD

Response documents

UPDATE MAKERESPONSE(5678)

Insert

INSERT INTO form1 SET item1=value1

INSERT RETURN NOTEID()

36. N2N: Updating data (2)

Email

INSERT INTO Memo SET SendTo="Stephen Walch", Subject="Product SEND

UPDATE SENDONLY

Delete

DELETE FROM Discussion WHERE SEARCH(VIM)

Run an agent:

AGENT agent1

AGENT agent1 SET input=value RETURN output

37. N2N: Security issues

By default, N2N uses credentials using local Notes ID file

N2N allows .NET apps to validate Domino names and passwords

N2N allows .NET apps to impersonate other users (requires R6 if remote)

N2N allows .NET apps to create/validate Domino Sign-On (LTPA) Tokens

38. LAB #2: Notes Data Integrator

Create .NET Windows Forms project

Using Windows Grid and Tab controls

Visually creating DataAdapters for Notes and SQL Server

Typed DataSets

Implementing data transfer routines

39. For more information

Ask Steve

[email_address]

.NET development

http://www.msdn.com

Visual Studio.NET Help

Proposion N2N

Proposion web site http://www.proposion.com

ado.net training.ppt

Documents

agenda basic

net basic

proposion

basic principles

net apps

net introduction

selecting

rich text