ADO.NET Training

Introduction to ADO.NET Accessing Notes/Domino data using Proposion N2N:

Introduction to ADO.NET Accessing Notes/Domino data using Proposion N2N © Copyright 2004, Proposion, Inc. All rights reserved.

Introductions: Stephen Walch:

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

Proposion Products:

Proposion Products (Q1 2005)

Agenda:

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

ADO.NET Basic principles (1):

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

ADO.NET Basic principles (2):

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)

Basic principles (3):

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

Basic principles (4):

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.

Example: Basic SQL Server query:

Example: Basic SQL Server query

So what about Notes/Domino?:

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

More “weird” Notes stuff:

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

Introducing…:

Introducing…

About Proposion N2N:

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

Proposion N2N features:

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

Alternatives:

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

Agenda:

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

ADO.NET Data Connection:

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

ADO.NET Data Command:

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

ADO.NET Data Reader:

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

Sample: Query with Data Reader:

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

Building a Data Access Layer:

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

Sample: Data Access Layers:

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

LAB #1: Web Contact List:

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

Agenda:

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

ADO.NET DataSet:

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

ADO.NET DataTable:

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

ADO.NET Data Adapter:

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)

Sample: Data Adapters:

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

Schemas and Typed DataSets:

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

Sample: Schemas and Typed DataSets:

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

Agenda:

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

N2N: Selecting columns (1):

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

N2N: Selecting columns (2):

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)

N2N: Selecting rows:

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

N2N: Updating data:

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

N2N: Updating data (2):

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

N2N: Security issues:

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

LAB #2: Notes Data Integrator:

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

For more information:

For more information Ask Steve swalch@proposion.com .NET development http://www.msdn.com Visual Studio.NET Help Proposion N2N Proposion web site http://www.proposion.com