Ado Dot Net

Microsoft ADO.NET: Programming ADO.NET Datasets Vijaya Lakshmi Byri Support Engineer Manisha Gupta Support Engineer (PSS) Microsoft Corporation

Agenda  

Review of ADO.NET architecture in the Microsoft® .NET Framework Working with datasets   

ADO.NET datasets XML and datasets Typed datasets

2

Microsoft .NET Framework XML Web Web Services Forms ASP.NET

Windows Forms

Data and XML Classes Base Framework Classes Common Language Runtime

3

ADO.NET Architecture

4

ADO.NET Datasets     

Architecture of dataset Creating a dataset Navigating the dataset hierarchy Copying a dataset Merging a dataset

5

Dataset Architecture Dataset DataTableCollection DataTable DataRowCollection DataColumnCollection ConstraintCollection DataRelationsCollection

6

Creating a Dataset    

Server Explorer Using DataAdapter Programmatically Using XML

7

Server Explorer

8

Generate a Dataset

9

Using DataAdapter   

Open connection Create a DataAdapter Fill the dataset

10

Sample Code Dim cnNwind As New SqlClient.SqlConnection("Data Source=(localhost); Integrated Security=SSPI; Initial Catalog=Northwind;") Dim daOrders As New SqlClient.SqlDataAdapter("Select * from Orders", cnNwind) Dim ds As New dataset() daOrders.Fill(ds, "Orders") 11

Create Dataset Programmatically It is possible to fabricate a dataset without loading in external data. •

Define some DataColumn objects.

DataColumn DataColumn

12

Create Dataset Programmatically (2) •

Add the DataColumn objects to a DataTable object.

DataTable DataColumn DataColumn

13

Create Dataset Programmatically (3) 1.

Add the DataTable object to a Dataset object.

Dataset DataTable DataColumn DataColumn

14

DataTable Dataset DataTable

DataColumn

DataRow

DataTable

DataColumn

DataRow

15

Sample Code Dim myCustomers As New Data.DataTable() myCustomers.TableName = "Customers" With myCustomers .Columns.Add("CustomerID", Type.GetType("System.String")) .Columns.Add("CompanyName", Type.GetType("System.String")) .Columns.Add("ContactName", Type.GetType("System.String")) End With Dim myDr As Data.DataRow myDr = myCustomers.NewRow() myDr("CustomerID") = "9876" myDr("CompanyName") = “Microsoft" myDr("ContactName") = “Jeff Smith" myCustomers.Rows.Add(myDr) Dim myDS As New Data.dataset("CustomerDS") myDS.Tables.Add(myCustomers) 16

Navigating Dataset Dataset TablesCollection

DataTable ColumnsCollection

DataColumn

RowsCollection

DataRow

ConstraintsCollection RelationsCollection

Constraint

DataRelation

17

Adding a DataRelation Use DataRelation objects to: •

Relate one table to another

•

Navigate through the tables

•

Return child or parent rows from a related table

custDS.Relations.Add("CustOrders", _ custDS.Tables("Customers").Columns("CustID"), custDS.Tables("Orders").Columns("CustID"))

18

Navigating a Relationship Dim custOrderRel As DataRelation = custDS.Relations.Add("CustOrders", _ custDS.Tables("Customers").Columns("CustomerID" ), custDS.Tables("Orders").Columns("CustomerID")) Dim custRow As DataRowDim orderRow As DataRow For Each custRow in custDS.Tables("Customers").Rows Console.WriteLine(custRow("CustomerID")) For Each orderRow in custRow.GetChildRows(custOrderRel) Console.WriteLine(orderRow("OrderID")) Next Next 19

Copying a Dataset   

Create an exact copy of the dataset. Create a copy of modified rows. Create a copy of the schema.

20

Creating an Exact Copy of the Dataset 

To create an exact copy of the dataset that includes both schema and data, use the Copy method of the dataset.

Dim copyDS As dataset = custDS.Copy()

21

Creating a Copy of Modified Rows 

Use GetChanges to return only modified rows with a specified row state.   

Added Updated Deleted

Dim changeDS As dataset = custDS.GetChanges() Dim addedDS As dataset = custDS.GetChanges(DataRowState.Added)

22

Creating a Copy of the Schema  

Use the Clone method to copy only the schema of the dataset. Use the ImportRow method of the DataTable to add the existing rows.

Dim custGermanyDS As dataset = custDS.Clone()Dim copyRows() As DataRow = custDS.Tables("Customers").Select("Country = 'Germany'") Dim custTable As DataTable = custGermanyDS.Tables("Customers") Dim copyRow As DataRow For Each copyRow In copyRows custTable.ImportRow(copyRow) Next

23

Merging a Dataset    

Merge the contents of a dataset Merge contents of the DataTable Merge contents of the DataRow array MergeFailed event

24

Merging Factors and Options    

Primary keys PreserveChanges MissingSchemaAction Constraints

25

Sample Code Dim ds As New Dataset("myDataset") Dim t As New DataTable("Items") ' Create variable for temporary Dataset. Dim xSet As Dataset ds.Tables.Add(t) ‘Add two columns ‘Add Rows ds.AcceptChanges() ‘Change row values ‘ Add one row. ‘Insert code for error checking. ‘ If the table has changes or errors, create a subset Dataset. xSet = ds.GetChanges(DataRowState.Modified Or DataRowState.Added) ‘Insert code to reconcile errors. xSet.Tables("Items").Columns.Add(New DataColumn("newColumn")) ‘ Add values to the rows for each column. ds.Merge(xSet, False, System.Data.MissingSchemaAction.Add)

26

XML and Datasets     

Loading a dataset with XML data Writing a dataset as XML data Loading a dataset with XSD schema Writing a dataset as XSD schema Synchronizing dataset and XMLDataDocument

27

Loading a Dataset with XML Data 

The method used to load XML data into a dataset:  

Public Function ReadXml(Stream) As XmlReadMode Public Function ReadXml(Stream, XmlReadMode) As XmlReadMode

28

Sample Code Dim ds As New Dataset() ds.ReadXml("c:\books.xml",XmlReadMode.Auto) Dim dr As DataRow For Each dr In ds.Tables(0).Rows Console.WriteLine(dr.Item(0)) Next

29

XmlReadMode      

Auto DiffGram Fragment IgnoreSchema InferSchema ReadSchema

30

Writing a Dataset as XML 

The method used to write XML data using a dataset:  

Overloads Public Sub WriteXml(Stream) Overloads Public Sub WriteXml(Stream,XmlWriteMode)

31

Sample Code Dim cnNwind As New SqlConnection( "Data Source=(localhost);integrated Security=SSPI;Initial catalog=Northwind;") Dim daOrders As New SqlDataAdapter( "Select * from customers", cnNwind) Dim ds As New Dataset() daOrders.Fill(ds, "Customers") ds.WriteXml("C:\Customers.xml",XmlWriteMode.IgnoreS chema) 32

Loading a Dataset with XSD Schema 

ReadXmlSchema  



Loads the XSD schema Does not load any data

InferXmlSchema   

Infers the XSD schema from the XML data Loads the XSD schema Loads the XML data

33

Writing XML Schema 

GetXmlSchema   



Returns an XSD schema Return type is String Dim xsdDS As String = DS.GetXmlSchema()

WriteXmlSchema  

Writes XSD schema to a file or stream DS.WriteXmlSchema("Customers.xsd")

34

Synchronizing XmlDataDocument and Dataset   

Synchronize XMLDataDocument and a dataset Perform XPath queries on a dataset Perform XSLT transformations on a dataset

35

Synchronizing XmlDataDocument and Dataset (2)  

Create a dataset Create an XmlDataDocument using a dataset Dim cnNwind As New SqlConnection("Data Source=(localhost);Integrated security=SSPI;Initial catalog=Northwind;") Dim daOrders As New SqlDataAdapter("Select * from Orders where 1=2", cnNwind) Dim ds As New Dataset() daOrders.FillSchema(ds, "Orders") Dim xmldoc As New Xml.XmlDataDocument(ds) xmldoc.Load("C:\Orders.xml") 36

Performing XPath Queries on a Dataset  

Create an XmlDataDocument based on a dataset Perform XPath queries on the XmlDataDocument Dim nodes As Xml.XmlNodeList nodes = xmldoc.SelectNodes("//OrderID") Dim node as Xml.XmlNode For each node in nodes Console.WriteLine(node.InnerText) Next

37

Performing XSL Transformations on Dataset  

Create an XMLDataDocument based on a dataset Using XslTransform object to transform the data in the dataset Dim xmldoc As New Xml.XmlDataDocument(ds) Dim xsldoc As New Xml.Xsl.XslTransform() xsldoc.Load("Orders.xsl") Dim xpathnav As Xml.XPath.XPathNavigator xpathnav = xmldoc.CreateNavigator xsldoc.Transform(xpathnav, Nothing)

38

Typed Datasets 

How to generate a typed dataset  



Using MSDatasetGenerator Using .NET command-line tools:  Xsd.exe  Language compiler

Using annotations with a typed dataset

39

Using MSDatasetGenerator

40

IntelliSense® for Typed Dataset

41

IntelliSense for Typed Dataset (2)

42

Using .NET Command-Line Tools 

Create a dataset using an XSD schema 





Creates a class file (depending upon the language specified) Example Class1.vb Syntax: xsd.exe /d /l:C# XSDSchemaFileName.xsd /n:XSDSchema.Namespace

Using language compiler compile the library from the generator code 

Syntax: csc.exe /t:library XSDSchemaFileName.cs /r:System.dll /r:System.Data.dll 43

Class1.vb  

Namespace Classes x(xsd schema name)  Inherits dataset class  xDataTable  Inherits DataTable Class  xRow  Inherits DataRow Class  xRowChangeEvent  Inherits EventArgs Class Note: x would be the XSD schema name. 

44

Using Annotations with a Typed Dataset 



Modify the names of the elements in the typed dataset without modifying the underlying schema. How to annotate 



Add the Annotations namespace Namespace = xmlns:codegen="urn:schemasmicrosoft-com:xml-msprop" Add the annotation <xs:element name="Customers" codegen:typedName="Customer" typedPlural="Customers">

45

Annotations     

TypedName TypedPlural TypedParent TypedChildren NullValue

46

Thank you for joining us for today’s Microsoft Support WebCast. For information on all upcoming Support WebCasts and access to the archived content (streaming media files, PowerPoint® slides, and transcripts), please visit:

http://support.microsoft.com/webcasts/ We sincerely appreciate your feedback. Please send any comments or suggestions regarding the Support WebCasts to [email protected]

47