ADO.NET, VS, SQL Server

1
ADO.NET, VS, SQL Server
2
ODBC, OLE DB, ADO
OBDC (Open Database Connectivity) is the early
standard for relational databases. OLE DB is
Microsofts object-oriented interface for
relational and other databases. ADO (Active Data
Objects) is Microsofts standard providing easier
access to OLE DB data for the non-object-oriented
programmer.
3
ODBC
4
OLE DB Goals

• Create object interfaces for DBMS functionality
  pieces
• Query, update, transaction management, etc.
• Increase flexibility
• Allow data consumers to use only the objects they
  need.
• Allow data providers to expose pieces of DBMS
  functionality.
• Providers can deliver functionality in multiple
  interfaces.
• Interfaces are standardized and extensible.
• Provide object interfaces over any type of data
• Relational and non-relational database, ODBC or
  native, VSAM and other files, Email, etc.

5
OLE DB
6
ADO

• Active Data Objects (ADO) characteristics
• A simple object model for OLE DB data consumers
• It can be used from VBScript, JScript, Visual
  Basic, Java, C, C
• It is a single Microsoft data access standard
• Data access objects are the same for all types of
  OLE DB data

7
ADO
8
ADO .NET

• ADO.NET is a new, improved, and greatly expanded
  version of ADO that was developed for the
  Microsoft .NET initiative
• It incorporates all of the functionality of ADO
  and facilitates the transformation of XML
  documents to and from database data
• It uses datasets, which is an in-memory,
  fully-functioned, independent databases

9
ADO.NET

• ADO.NET is a new, improved, and greatly expanded
  version of ADO that was developed for the
  Microsoft .NET initiative
• It incorporates all of the functionality of ADO
  and facilitates the transformation of XML
  documents to and from database data
• It uses datasets, which is an in-memory,
  fully-functioned, independent databases

10
VS 03 VS 05

• -- VS 2003
• Dim fileAndPath As String
• fileAndPath "Complete path where you
  saved the XML file"
• dsAuthors.ReadXml(fileAndPath)
• With DataGrid1
• .DataSource dsAuthors
• .DataMember "authors"

• -- VS 2005
• Dim filePath As String
• filePath "Complete path where you saved the XML
  file"
• dsAuthors.ReadXml(filePath)
• With (DataGridView1)
• .DataSource dsAuthors
• .DataMember "authors

11
Relational DB XML Overview

• Relational Table -gt Read into a DataSet
• DataSet -gt Write to an XML file
• or
• XML File -gt Read into a DataSet
• Dataset -gt Write to a Relational Database
• or
• Merge one Dataset into another one

12
XML File -gt Data Source

• Read the contents of an XML file to a data
  source
• Dim dsAuthors As New DataSet("authors")
• Dim fileAndPath As String
• fileAndPath "Complete path where you
  saved the XML file"
• dsAuthors.ReadXml(fileAndPath)
• With DataGrid1
• .DataSource dsAuthors
• .DataMember "authors"
• .CaptionText .DataMember
• End With
• Write schema represenation of the data source
  to a textbox
• Dim swXML As New System.IO.StringWriter()
• dsAuthors.WriteXmlSchema(swXML)
• TextBox1.Text swXML.ToString

13
Merging 2 Datasets

• targetDataset.Merge (sourceDataSet)
• Add new records to one dataset by copying them
  from another dataset.
• Update the state of records in one dataset based
  on another. In this situation, the second dataset
  contains information not only about new records,
  but also changed records and deleted records, and
  you want your first dataset to reflect all these
  changes.
• Copy not only new records and changes, but
  specify what to do if the datasets do not have
  the same schemas.