Transcript ADO.NET access to databases

Needs for Accessing Database
• To make your web site more dynamic and
maintainable, you can display information
on your web pages that are retrieved from
a database
• A number of web applications (e.g. ecommerce, on-line registration) require the
manipulation of database information
through the web
Accessing Database Using ASP
• Storing and retrieving information from a
relational database can be done with ADO.NET
(ActiveX Data Objects).
• ADO is a technology that allows connectivity to
any ODBC or OLE DB data source.
• ODBC (open database connectivity): common
API to access any relational database if it has an
ODBC driver.
• OLE DB: universal data access interface
specifications to provide a common layer
between data sources and data consumers
Accessing Database
Application
ActiveX Data Object (ADO)
OLE DB
OLE DB
provider
OLE DB
provider
OLE DB
provider
OLE DB
provider
Access
SQL
server
oracle
ODBC
driver
ADO: a component wrapper for OLE DB
interface
ADO.NET
• ADO.NET consists of a set of classes
defined by the .NET framework
• ADO.NET (ActiveX Data Objects.NET) is
primary data access API for .NET
• Data Source controls: represents different
kinds of data stores (e.g. databases and
XML files)
– No rendering on screen
– You have facilities such as (paging, filtering,
updating, inserting and deleting)
ADO.NET 2.0
• Two types of data providers for relational
databases:
– SQL server v7: System.Data.SqlClient
– OLEDB: System.Data.OleDb
• Any provider has 4 main classes to allow
access and manipulation of data
– Connection
– DataReader
-Command
-DataAdapter
ADO.NET 2.0
• Two methods to read data from database
– Connected (Use data Reader) only read data
as long as there is a connection
– Disconnected (Use data Adapter) store results
in a memory cash and you can still read and
update it despite there is no connection with
data source
ADO.NET
• Reading data from database (General algorithm)
– Create a connection object
– Create a command object
– Set the SQL select command
– Execute the command
– Read the result using data adaptor OR data
reader
Data Reader
• Read only, forward only object to read
data from a database source.
• Requires live connection with database
• You have to open and close connection
Example (Connected method)
Dim SQLConn As New OLedbConnection
SQLConn.ConnectionString =
ConfigurationManager.ConnectionStrings("ProductsConString").Con
nectionString
SQLConn.Open()
Dim SQLComm As New OLedbCommand
SQLComm.CommandText = "select * from product"
SQLComm.Connection = SQLConn
Dim SQLData As OleDbDataReader
SQLData = SQLComm.ExecuteReader
Label1.text = "SID Sname <br>"
While SQLData.Read()
Label1.text &= SQLData(0) & " " & SQLData(1) & "<br>"
End While
SQLConn.Close()
ADO.NET Features
• Data Can Be Cached in Datasets
– When retrieving data from a database, it is more
efficient to get the list of records once and during
update it is impractical to connect to the database to
work on each record
– Solution: temporarily store the records retrieved from
the database and work with this temporary set
(DataSet)
– Dataset is a passive container. To actually fetch data
from the database and write it back, you use data
adapters
Data adapter
• An object used to communicate between a data source
and a dataset
• Reading data from a database into a dataset, and then
writing changed data from the dataset back to the
database
• Using an adapter, you can add, update, and delete
records in a data source. To specify how each of these
operations should occur, an adapter supports the
following four properties:
– SelectCommand – reference to a command that retrieves rows
from the data store.
– InsertCommand – reference to a command for inserting rows
into the data store.
– UpdateCommand – reference to a command for modifying rows
in the data store.
– DeleteCommand – reference to a command for deleting rows
from the data store.
Example (Disconnected method)
Dim SQLConn As New OleDbConnection
SQLConn.ConnectionString =
ConfigurationManager.ConnectionStrings("ProductsConString").Con
nectionString
' No open connection
Dim SQLComm As New OleDbCommand
SQLComm.CommandText = "select * from product"
SQLComm.Connection = SQLConn
Dim SQLData As OleDbDataAdapter
Dim DS As DataSet
DS = New DataSet
SQLData = New OleDbDataAdapter()
SQLData.SelectCommand = SQLComm
SQLData.Fill(DS)
Gv.DataSource = DS
Gv.DataBind()
DataSet
• A dataset is a cache of records retrieved from a
data source.
• Works like a virtual data store: A dataset
includes one or more tables based on the tables
in the actual database, and it can include
information about the relationships between
those tables
• Similar structure as a relational database
(tables, rows, and columns)
• Used if you want to work with a set of tables and
rows while disconnected from the data source.
ADO.NET classes
Memory
Web Page
Data table
DataBind()
Rows
Relations
Grid View
Data Adaptor
Data Reader
ADO.NET
Command
Connection
Data Source
• Data adaptor Fill method: opens connection, executes
command, reads data and closes connection
Data Access models
• You can either use available controls to
perform and access data
• OR, you can write code
Data Access Model (1)
• Steps to include database access to your
ASP.NET application
– Design and implement your database
– Store the database file on the server
– Create a dataSource control on your web page
•
•
•
•
Specify the type of database provider
Locate database file and access information
Optionally, store connection string on web.config file
Create queries (select, update, insert, delete)
– Connect the datasource to any control on your
web page by binding the right fields
Update, Delete from ViewGrid
• Reconfigure datasource object to generate
Insert, Delete and update queries
• Check the queries generated in properties
window
• Add update, delete command buttons in
the Grid view
• Make sure that the DataKeyName=
Primary key field in your database
• Run application
Form View Control
• Used to display a single item (row) from
the data source
• Allows update, delete, insert and paging
Display Images from data source
• Add form View control
• Click “Edit template”
• Delete the image template (image file
name)
• Drag an image control in place
• Bind the ImageURL property to the
appropriate data source field
• (Make sure images are in the correct path)
Data Access Model (2)
• Performing database operations in code
– Add a connection object to the form and set its
connection string
– Open the connection using the “open” method
– Create a command object and set its “connection”
property
– Set the “commandtext” property of the command to
the SQL statement that you want to perform
– Execute the command
– Close the connection using the “close” method
Connection Object
• To move data between a data store and the
application, you must first have a connection to
the data store.
• In ADO.NET you can create and manage a
connection using one of two connection objects:
– SqlConnection - an object that manages a
connection to a SQL Server version 7.0 or later.
(optimized because it bypasses the OLE DB layer)
– OleDbConnection - an object that manages a
connection to any data store accessible via OLE DB.
• ConnectionString property, consists of a string
with information required to log on to a database
Updating data
Dim SQLConn As New OleDbConnection
SQLConn.ConnectionString =
ConfigurationManager.ConnectionStrings("ProductsConString").Con
nectionString
SQLConn.Open()
Dim SQLComm As New OleDbCommand
SQLComm.CommandText = "update [product] set
[productName]='" & TextBox4.Text & "' , [Price]=" &
Val(TextBox2.Text) & " , [image] = '" & TextBox3.Text & "' where
[ProductID]=" & Val(TextBox1.Text)
SQLComm.Connection = SQLConn
Response.Write(SQLComm.CommandText)
SQLComm.ExecuteNonQuery()
SQLConn.Close()
Inserting Data
Dim SQLConn As New OleDbConnection
SQLConn.ConnectionString =
ConfigurationManager.ConnectionStrings("ProductsCon
String").ConnectionString
SQLConn.Open()
Dim SQLComm As New OleDbCommand
SQLComm.CommandText = "Insert into product
values ('" & TextBox1.Text & "'," & TextBox2.Text & ")"
SQLComm.Connection = SQLConn
SQLComm.ExecuteNonQuery()
SQLConn.Close()
Grid View Object
• Access data in a selected row of a Grid View
Protected Sub GridView1_SelectedIndexChanged(ByVal
sender As Object, ByVal e As System.EventArgs) Handles
GridView1.SelectedIndexChanged
Response.Write("Values " &
GridView1.SelectedRow.Cells(1).Text & "<BR>")
Response.Write(GridView1.SelectedRow.Cells(2).Text)
End Sub
Deleting data
• Using GridView object to select a row
Dim SQLConn As New OleDbConnection
SQLConn.ConnectionString =
ConfigurationManager.ConnectionStrings("ProductsConString").Con
nectionString
SQLConn.Open()
Dim SQLComm As New OleDbCommand
SQLComm.CommandText = "Delete from product where
ProductID=" & GridView1.SelectedRow.Cells(2).Text
SQLComm.Connection = SQLConn
Response.Write(SQLComm.CommandText)
SQLComm.ExecuteNonQuery()
SQLConn.Close()