PPT ON ADO.NET

CLICK HERE TO DOWNLOAD  PPT ON ADO.NET

ADO.NET  Presentation Transcript

1.Introduction to ADO.NET

2.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

3.ADO.NET Basic principles (1)
All data providers implement the same set of standard interfaces:
DataConnection
DataCommand
DataParameter
DataReader
DataAdapter

4.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)

5.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

6.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.

7.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

8.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

9.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

10.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

11.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

12.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

13.ADO.NET Data Connection
Represents an open connection to a data source
IDbConnection ? NsfConnection
Properties
ConnectionString
Methods
Open()
Close()
BeginTransaction()  *

14.Represents a command statement that is executed while connected to a data source
IDbCommand ? NsfCommand
Properties
Connection
CommandType, CommandText
Parameters
Methods
ExecuteNonQuery()
ExecuteReader()

15.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()

16.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

17.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

18.Sample: Data Access Layers
Create a new data access component
Implement data access method
Use the component in a Web Service

19.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

20.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

21.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()

22.Properties
TableName
Columns    //DataColumn
Constraints    //Constraint
Rows        //DataRow
DefaultView    //DataView
ParentRelations, ChildRelations
Methods
NewRow(), ImportRow()
Select(“expression”),  Compute(“expression”)

23.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)

24.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

25.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

26.Sample: Schemas and Typed DataSets
Generating schema and Typed DataSet
Using a Typed DataSet in your code
Binding to Typed DataSets

27.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

28.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() …

29.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)

30.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

31.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()

32.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

33.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

34.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