Introduction to ADO.

NET
 What we cover…
 [Link]
 Benefits of [Link]
 [Link] Core Concepts and Architecture
 The [Link] Object Model
 The DataSet and Data Views
 Managed Providers

1
 [Link] and the .NET Framework

Microsoft .NET Framework

Web Services User Interface

Data and XML

[Link] XML ... ...

Base Classes

Common Language Runtime

2
 [Link] Overview
What Is [Link]?

 ADO .NET is a collection of classes, interfaces,

structures, and enumerated types that manage
data access from relational data stores within
the .NET Framework
 These collections are organized into namespaces:
 [Link], [Link], [Link],
etc.

 ADO .NET is an evolution from ADO.

 Does not share the same object model, but shares
many of the same paradigms and functionality!

3
 [Link] Overview
Managed Providers

 Merges ADO and OLEDB into one layer

 Each provider contains a set of classes that
implement common interfaces
 Initial managed provider implementations:
 ADO Managed Provider: provides access to any
OLE DB data source
 SQL Server Managed Provider: provides optimal
performance when using SQL Server
 Exchange Managed Provider: retrieve and update
data in Microsoft Exchange

4
 [Link] Overview
Managed Providers

Your Application

[Link] Managed Provider

OLE DB
Provider
SQL Server
Database Database

SQL Managed Provider ADO Managed Provider

5
 Benefits of [Link]
 Interoperability through use of XML
 Open standard for data that describes itself
 Human readable and decipherable text
 Used internally but accessible externally
 Can use XML to read and write and move data

 Scalability through the disconnected DataSet

 Connections are not maintained for long periods
 Database locking does not occur
 Locking support with ServiceComponents
 Optimistic locking otherwise
 Works the way the Web works: “Hit and Run!”
 Maintainability
6  Separation of data logic and user interface
 Core Concepts and Architecture

 The [Link] Object Model

 Objects of [Link]
 .NET data providers
 [Link] namespace hierarchy
 Organizes the object model
 Includes:
 [Link]
 [Link]
 [Link]
 [Link]
 [Link]

7
 [Link]-related Namespaces

[Link]

[Link]

.SqlTypes .SqlClient .Common .OleDb

Class Browser for [Link] and [Link]

8
 The (ADO).NET Data Providers
A collection of classes for accessing data sources:
 Microsoft SQL Server™ 2000, SQL Server 7, and MSDE
 Any OLE Database (OLE DB) providers
 Including: Oracle, JET, and SQL OLE DB Providers
 Establish connection between DataSets and data stores
 Two .NET data providers:
 ADO: via the [Link] namespace
 SQL Server: via the [Link] namespace
 [Link] is the .NET data provider

9
 .NET Data Providers Hierarchy
.Common
Contains classes
shared by both
[Link]
.SqlClient .OleDb
SqlCommand OleDbCommand
SqlConnection OleDbConnection
SqlDataReader OleDbDataReader
SqlDataAdapter OleDbDataAdapter

10
 General Steps for Using Web Databases
1. Build your database tables and queries
2. Create a connection to the database
 The connection identifies the location of the database
(the data source) and the connection method (an
ODBC driver, OLE-DB provider, or an [Link]
data provider), along with any other settings such as
username or password
3. Create an [Link] Web page
4. Add an [Link] connection object that connects to
the database, executes commands, and returns data
from the database
5. Create code that will interact with the data, display
the data in an [Link] control, perform calculations
11 on the data, or upload changes to the database
 [Link] – Introducing the objects
 Connection
 used to talk to DB;properties include dataSource,
username and password
 SQLConnection and OleDbConnection
 Command
 An SQL statement or Stored Procedure
 SQLCommand and OleDbComand
 DataReader-
read only, forward only view of data
CF ADO Recordset
 DataSet - main object for DB access
 DataView - filtered view of DataSet
12  DataAdapter - Initialises DataSet tables
 Introducing the Objects cont.

 Connections. For connection to and managing

transactions against a database.
 Commands. For issuing SQL commands against
a database.
 DataReaders. For reading a forward-only stream
of data records from a SQL Server data source.
 DataSets. For storing, remoting and programming
against flat data, XML data and relational data.
 DataAdapters. For pushing data into a DataSet,
and reconciling data against a database.

13
 Introducing the Objects cont.

[Link]  Contains the “main” classes of [Link]

DataSet  In-memory cache of data
DataTable  In-memory cache of a database table
DataRow  Used to manipulate a row in a DataTable
DataColumn  Used to define the columns in a DataTable
DataRelation  Used to relate 2 DataTables to each other
DataViewManager  Used to create views on DataSets

[Link] Namespace Contains the basis and bulk of

14
[Link]
 OleDbConnection and SqlConnection
 Represent a unique session with a data source
 Create, open, close a connection to a data source
 Functionality and methods to perform transactions
 OleDbConnection example:
String conStr="Provider=[Link].4.0;" +
"Data Source=NWIND_RW.MDB";
OleDbConnection aConn = new OleDbConnection(conStr);
[Link]();
// Execute Queries using OleDbDataAdapter Class
[Link]();

15
 Data Connection Properties
 SQL Server Name
 Default name of the MSDE version of SQL
Server is MachineName\NetSDK
 MachineName is the name of your local
computer
 Also referred to as (local)\NetSDK or
localhost
 Not required in the Connection String –
assumed to be SQL Server if it uses the
SQLClient class

16
 Dataset object
 DataSet object represents a cache of data, with database-like
structures such as tables, columns, relationships, and
constraints.
 DataSet can and does behave much like a database, it is
important to remember that DataSet objects do not interact
directly with databases, or other source data.
 Allows the developer to work with a programming model that
is always consistent, regardless of where the source data
resides. Data coming from a database, an XML file, from
code, or user input can all be placed into DataSet objects.
 Changes made to the DataSet can be tracked and verified
before updating the source data. The GetChanges method of
the DataSet object actually creates a second DatSet that
contains only the changes to the data. This DataSet is then
used by a DataAdapter (or other objects) to update the
original data source.
 For long-running applications this is often the best approach.
17
 DataAdapter
 Toperform a select query to a SQL database, you create
a SqlConnection to the database passing the
connection string, and then construct a SqlDataAdapter
object that contains your query statement. To populate a
DataSet object with the results from the query, you call
the command's Fill method.
Dim myConnection As New
SqlConnection("server=(local)\NetSDK;database=pubs;Truste
d_Connection=yes")
Dim myCommand As New SqlDataAdapter("select * from
Authors", myConnection)
Dim ds As New DataSet() [Link](ds, "Authors")
18
 DataReader Object
 For Web applications, you are usually performing short
operations with each request (commonly to simply display the
data). You often don't need to hold a DataSet object over a
series of several requests. For situations like these, you can
use a SqlDataReader.
 A SqlDataReader provides a forward-only, read-only pointer
over data retrieved from a SQL database.
 To use a SqlDataReader, you declare a SqlCommand
instead of a SqlDataAdapter.
 The SqlCommand exposes an ExecuteReader method that
returns a SqlDataReader.
 Note also that you must explicitly open and close the
SqlConnection when you use a SqlCommand. After a call
to ExecuteReader, the SqlDataReader can be bound to an
[Link] server control.
19
 DataReader cont.
Dim myConnection As SqlConnection = New
SqlConnection("server=(local)\NetSDK;database=pubs;Trusted_Connection=yes")
Dim myCommand As SqlCommand = New SqlCommand("select * from Authors",
myConnection)
[Link]()
Dim dr As SqlDataReader = [Link]() ...
[Link]()
 When performing commands that do not require data to be returned, such as
inserts, updates, and deletes, you also use a SqlCommand.
 The command is issued by calling an ExecuteNonQuery method, which
returns the number of rows affected. Note that the connection must be
explicitly opened when you use the SqlCommand ( the SqlDataAdapter
automatically handles opening the connection for you).
Dim myConnection As New
SqlConnection("server=(local)\NetSDK;database=pubs;Trusted_Connection=yes")
Dim myCommand As New SqlCommand( "UPDATE Authors SET phone='(800)
555-5555' WHERE au_id = '123-45-6789'", myConnection)
[Link]()
[Link]()
[Link]()
20
 DB Connection Example-1
<%@ Page Language="vb" %>
<%@ Import Namespace="[Link]" %>
<%@ Import Namespace="[Link]" %>

<HTML>
<body>

21
 DB Connection Example-2
<SCRIPT language="VB" runat="Server">
Sub Page_Load(Src As Object, E As EventArgs)
Dim ds As DataSet
Dim conn As SQLConnection
Dim cmdAuthors As SQLDataAdapter
Dim dv As DataView
'create a connection to the Pubs database'
conn = New SQLConnection _
("server=localhost;uid=sa;pwd=super;database=pubs")
'create a dataset with information from the authors table'
cmdAuthors = New SQLDataAdapter _
("select * from Authors", conn)
ds = new DataSet()
[Link](ds,
22
"Authors") ‘Authors is the DataTable name in ds
 DB Connection Example-3
'bind the first datagrid to the DefaultView of the dataset'
dv = [Link]("Authors").DefaultView
[Link] = dv
[Link]()
'create a new DataView that is authors from California'
'and bind the second datagrid to it'
dv = New DataView([Link]("Authors"))
[Link] = "state = 'CA'"
[Link] = dv
[Link]()
End Sub
23
</SCRIPT>
 DB Connection Example-4
<h2>All Authors</H2>
<ASP:DataGrid id="dgAuthors" runat="server"
Width="700"
BackColor="#ccccff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"/>
<H2>California Authors</H2>
<ASP:DataGrid id="dgCAAuthors" runat="server" />
</body>
24
 DB Connection Example-5
 Demo the previous code.
 [Link]
 NOTE:
 Namespaces included in page directives
 Objects used:
 SqlConnection ; SqlDataAdapter; Dataset; DataView;
 Web Form Controls used:
 <ASP:DataGrid>
 [Link]() moves data from memory (dataview) to web page
 DataGrid does not have to be bound to dataset; can be bound to
a hashtable say
 [Link]
 For source see
25 [Link]
 Working Data - The DataSet
 An in-memory cache of data from a data source
 Common way to represent and manipulate data
 Universal data container
 Not just for use with databases
 Logical or physical representation of data
 Designed to be disconnected from the data source
 Connect, execute query, disconnect
 Can use XML
 To read and write data
 To read and write XMLSchema
26
 Properties & Methods of Interest
 Collections are used to add & remove tables & relations
 Properties of Interest:
 Tables: Returns the collection of DataTable objects
 Relations: Returns the collection of DataRelations
 Namespace: Gets or sets the namespace of the DataSet
 Using Properties Samples:
 [Link]( myTable );
 myDataTableCollection = [Link]

27
 The DataTable
 May be mapped to a physical table in the data source
 Can be related to one another through DataRelations
 Optimistic concurrency or locking - model
 Properties of Interest:
 Columns: Returns ColumnsCollection of DataColumns
 Rows: Returns DataRow objects as a RowsCollection
 ParentRelations: Returns the RelationsCollection
 Constraints: Returns the table’s ConstraintsCollection
 DataSet: Returns the DataSet of the DataTable
 PrimaryKey: Gets the DataColumns that make up the
table’s primary key
28
 [Link]—DataSet and DataTable
 Create a DataTable and add it to a DataSet

DataSet ds = new DataSet();

// Create DataTable object: “Customers”.

DataTable dt= new DataTable( “Customers” );

// Create and add columns to the table

// 1. Explicitly create and Add a DataColumn
DataColumn dc;
dc = new DataColumn( “CustID”, [Link]("System.Int16"));
[Link]( dc );

// 2. Implicitly Create and Add columns (DataColumn).

[Link]( “First_Name”,[Link]("System String”));
[Link]( “Last_Name”, [Link]("System String”));

// Add the DataTable object to the DataSet

[Link]( dt );
29
 Viewing Data - The DataView
 Create multiple views on DataTable objects
 Bindable to user interface controls
 Properties of Interest:
 Table: Retrieves or sets the associated DataTable
 Sort: Gets or sets the table’s sort columns and sort order
 RowFilter: Gets or sets the expression used to filter rows
 RowStateFilter: Gets or sets the row state filter
 None, Unchanged, New, Deleted, ModifiedCurrent, and others

30
 Creating a DataView by Example
// Code for myTable “Customers” with “Name” column not shown
DataView view1 = new DataView( myTable );
DataView view2 = new DataView( myTable );

// Creates Ascending view of Customers by “Name”

[Link] = “Name ASC”;

// Set the view to show only modified (original) rows

[Link]= [Link];

// Bind to UI element(s)...
DataGrid myGrid = new DataGrid();
[Link]( view1, “Customer”);

//...

31
 Relational Databases
Stored Procedure Example

CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)

AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD,
Orders O, Customers C
WHERE [Link] = @CustomerID
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
GROUP BY ProductName

32
 [Link] - Data Binding
 Key component of Web Forms framework
 Flexible and easy to use
 Bind a control’s property to information in any type
of data store
 Provides control over how data moves back and
forth
 Simple controls for displaying a single value eg
below using binding tags <%# %>
 Complex controls for displaying a data structure eg
datagrid
<asp:Label id=“SelectedValue”runat=server
Text='<%# [Link] %>'/>
33
 [Link] Classes
DataSet Example

string sConnString = “Persist Security Info=False;” +

“User ID=sa;Initial Catalog=Northwind;” +
“Data Source=MYSERVER”;
SqlConnection conn = new SqlConnection(sConnString);
[Link]();
string sQueryString = “SELECT CompanyName FROM Customers”;
SqlDataAdapter myDSAdapter = new SqlDataAdapter();
DataSet myDataSet = new DataSet();
[Link] = new SqlCommand(sQueryString, conn);
[Link](myDataSet);
[Link]();

34
 Stored Procedure Example
<%@ Page Language="vb" %>
<%@ Import Namespace="[Link]" %>
<%@ Import Namespace="[Link]" %>
<HTML> <body>
<SCRIPT language="VB" runat="Server">
Dim conn as SQLConnection
Sub Page_Load(Src As Object, E As EventArgs)
conn = New
SQLConnection("server=localhost;uid=sa;pwd=1Aspt;database=conf")
displayCategories()
displayProducts()
displayOrderCount()
35
'the ProductCategoryList storedprocedure has no parameters and
returns ‘records. display the returned records in a datagrid'
Sub displayCategories()
Dim cmd As SQLDataAdapter
Dim ds As DataSet
Dim workParam As SQLParameter = Nothing
'call the ProductCategoryList stored procedure'
cmd = New SQLDataAdapter("ProductCategoryList", conn)

'fill dataset with results of stored procedure'

ds = new DataSet()
[Link](ds, "Categories")

'bind dataset to datagrid'

[Link] = [Link]("Categories").DefaultView
[Link]()
End Sub
36
'the ProductsByCategory storedprocedure has an input parameter which is the categoryID
'and returns all items from that category'
'read the input parameter from a text box and display the results in a datagrid'
Sub displayProducts()
Dim cmd As SQLDataAdapter
Dim ds As DataSet
Dim workParam As SQLParameter = Nothing
'call the ProductCategory stored procedure'
cmd = New SQLDataAdapter("ProductsByCategory", conn)
[Link] = [Link]
'add the CategoryID input parameter from the txtCatID textbox'
workParam = New SQLParameter("@CategoryID", [Link])
[Link] = [Link]
[Link] = CInt([Link])
[Link] (workParam)
'run the stored procedure and fill a dataset with the results'
ds = new DataSet()
[Link](ds, "Products")
'bind the dataset to a datagrid'
[Link] = [Link]("Products").DefaultView
[Link]()
37
End Sub
'the OrdersCount storedprocedure has an input parameter which is the
customerID’ 'and an output parameter which is the number of orders
for that customer.''read the input parameter from a text box and
display the output value in a label'

Sub displayOrderCount()
Dim cmd As SQLCommand
Dim workParam As SQLParameter = Nothing

'call OrdersCount stored procedure'

cmd = New SQLCommand()
With cmd
.Connection = conn
.CommandText = "OrdersCount"
.CommandType = [Link]
End With
38
'add the CustomerID input parameter from txtCustID textbox'
workParam = New SQLParameter("@CustomerID", [Link])
[Link] = [Link]
[Link] = CInt([Link])
[Link](workParam)
'add the ItemCount output parameter'
workParam = New SQLParameter("@ItemCount", [Link])
[Link] = [Link]
[Link](workParam)
'open the connection so you can call execute on the SelectCommand'
[Link]()
[Link]()
[Link]()
'display the output parameter in a SPAN element'
[Link] =
[Link]("@ItemCount").Value
End Sub
</SCRIPT>
39
<h2>Categories</h2>
<asp:datagrid id="dgCategories" runat="server"/>
<br><br>
<form runat="server">
<P>Enter category: <asp:textbox id="txtCatID" runat="server"
Text="14"/>
<asp:button runat="server" text="Get Products"/>
<h2>Products in Category</h2>
<P><asp:datagrid id="dgProducts" runat="server"/>
<br><br>
<h2>Number of Current Orders for a Customer</h2>
<P>Customer ID <asp:textbox id="txtCustID" runat="server"
Text="31"/>
<asp:button runat="server" text="Get Order Count"/>
<br>has <span id="spnOrderCount" runat="server"></span>
outstanding order(s)
</form>
40 </body> </html>
 Demo Stored Procedure example
 democode/mod03/[Link]

 Repeater Example
 Simple List -repeater Example
 Uses templates for formatting output…to be
discussed next lecture

41
 Accessing XML-based Data

 TheDataSet was designed to abstract data in a

way that is independent of the actual data source.
 Change the focus of your samples from SQL to
XML. The DataSet supports a ReadXml method
that takes a FileStream object as its parameter.
 Thefile you read in this case must contain both a
schema and the data you wish to read.
 Datagrid example17 – read XML data

42
 DataGrid control – some examples
 Datagrid properties
 UpdatingData in a SQL Database -
[Link] to [Link]

43