ADO'Net 3'5 for Web Applications

1
ADO.Net 3.5for Web Applications
2
Objectives

• Learn how to connect backend databases from a Web
  server
• Applying SQL Server using ADO.Net 3.5

3
Three-Tier Architecture Revisited
ODBC, OLE DB, ADO, or database vendors
proprietary protocol
4
Data Needs for Internet Application
Each one of these database products has different
programming interface that developer must learn.
5
Role of ADO.Net
6
ADO.Net Component Architecture
7
The Advantages of the ADO.Net

• ADO.Net is all about disconnected access. In
  general, you connect to the database only for the
  instant when you are retrieving or updating.
  Otherwise, the connection is closed.
• The ADO.NET model separates the process of
  connecting to the data source from the
  manipulation of the data.

8
DataSets Your Own Private Database

• In ADO.net, data will be stored at DataSet. The
  DataSet can act like the .Net in-memory database,
  with a full understanding of the relationship
  among tables.
• In design time, specify the tables to be used
• At run time, create a dataset to hold the data

9
The ADO.Net Class Libraries
10
DataSource Controls

• Extract data from data source
• Hold data in memory
• Specialized DataSources to data access
• SiteMapDataSource read site maps
• SqlDataSource represents data retrieved from a
  SQL relational database, including SQL Server,
  Oracle, or DB2
• AccessDataSource represents data retrieved from
  Microsoft Access Database
• ObjectDataSource represents data retrieved from
  a business object
• XmlDataSource represents data retrieved from an
  XML document

11
Configuring SqlDataSource

• Create a new connection (using the Server
  Explorer) or select an existing connection
  string
• Specify data
• SQL statement
• Query Builder
• A Stored procedure
• Create a Where Clause with parameters

12
Connection String Name

• Can find the name in the Web.config file

13
Configuring SqlDataSource From Code

• Add NameSpaces of
• Imports System.Data
• Imports System.Data.SqlClient
• Specify the connection string asdim cnn as New
  SqlConnection(ConfigurationManager.
  ConnectionStrings(StringName).ConnectionString)
• SQL Statement for data being retrived
• Dim MyCommand as New SqlCommandMyCommand.Conne
  ctioncnnMyCommand.CommandTextSelect from
  authors

14
Open and Close the Connection

• cnn.Open
• Cnn.Close

15
Execute SQL Statement

• Must use an appropriate Execute method of the
  command object- ExecuteNonQuery Just execute
  the SQL statement- ExecuteScalar execute
  the statement and return a single result-
  ExecuteReader - if a DataReader is to be
  returned

16
Common View Controls

• Containers for controls
• Bind to any DataSource
• Manage data for their controls
• GridView
• Display multiple records Formatting restricted
  to grid
• DataList
• Display multiple records flexible formatting
  options
• Repeater
• Display multiple records using a template
• DetailsView
• Display single record Limited customizable
  formatting
• FormView
• Displays single record Very customizable
  formatting

17
The App_Data Folder

• A folder within the web site for storing data
  resources
• Specific to the site
• Facilitates distributing the application
• Copying the site will copy database files in
  App_Data
• Databases added to App_Data are automatically
  added to Server Explorer

18
Lab6 Exercises