Transcript Unit 11 ASP.NET - Accessing the Database (Presentation)

ASP.NET
- accessing the database
- datareader vs dataset
- datagrid vs datalist
- brief look at web matrix
Accessing the database
1.
2.
3.
4.
5.
6.
Open a connection to a database
Create a recordset to store retrieved data
Send SQL query to database
Store answer to SQL query in recordset
Close connection to database
Retrieve data from recordset fields to display
onscreen
7. Close recordset
Open connection to database (1)
• Work out which connection driver you need
Dim strConn as String
strConn =
“Provider=Microsoft.Jet.OLEDB.4.0;Data
Source = “ &
Server.MapPath(“recruitment.mdb”)
Open connection to database (2)
• Give the string containing driver name &
database location to the OleDbConnection
Dim objConn as OleDbConnection
objConn = New OleDbConnection(strConn)
objConn.Open
• You could do both these steps on one line
Create a recordset to store retrieved data
Dim objRS as OleDbDataReader
• Store SQL query in a String
Dim querysql as String
querysql = “SELECT * FROM JobList”
Send SQL query to database &
store answer in recordset
Dim strQuery as OleDbCommand
strQuery = New OleDbCommand(querysql,
objConn)
objRS = strQuery.ExecuteReader
• Close connection to database
objConn.Close
Retrieve data from recordset to display
onscreen
JobDataGrid.DataSource()=objRS
JobDataGrid.DataBind()
• Close recordset
strQuery.Dispose
objRS.Close
DataReader vs DataSet
DataReader vs DataSet
• Extremely simplified summary:
• DataReader
– Conveyer belt of data from database to .NET
application (bridge)
• DataSet
– Miniature in-memory copy of the database
(usually just part of it)
DataReader
• Remains connected to the database
– Only contains one row at a time
– Can only go forwards
– Can’t edit the data directly
• Different types for each provider
– Microsoft uses OleDbConnection
– SQL uses SQLConnection
• 30 (ish) times faster than a dataset
• Can be looped so all data is retrieved and stored in a
table in memory
DataSet
• Disconnected
– Retrieves all info in one go & stores in memory
• Editable
• Provider neutral
– can also be used to access XML files
• Can be sorted or searched
• Very good for basic data-entry applications
• Useful for caching information to be shown on
several pages across your website
DataGrid vs DataList
• Extremely simplified summary:
• DataGrid
– table
• DataList
– List
– You design the layout yourself
DataGrid
•
•
•
•
•
Most versatile, but least flexible
Built-in paging, sorting & editing facilities
Each row displays 1 record
Can be nested
Slowest to display on screen
DataGrid column types
• Bound Column
– Value from datasource as plain text
• Button Column
• Edit Column
• HyperLink Column
– Hyperlink, text & url may come from datasource
• Template Column
– Customise html output of the column (allows
nested grids)
DataList
• No built-in support for paging, sorting &
editing
• Can display more than 1 record on each row
• Much more customisation of data layout is
possible
• Can be nested (not as easy as datagrid)
DataList templates
• Header template
• Item template
– Displayed once per row from recordset
– Can be set to repeat using <table> or <span> HTML tags
•
•
•
•
•
Alternating Item Template
Edit Item Template
Selected Item Template
Separator Template
Footer Template
Initial View
Data Tab – click here to establish connection to database
DataGrid
All code at once
Code tab
- Shows ASP.NET code only
HTML tab
- Shows HTML code only
Drag & drop
Drag & drop
basic query builders
This menu only visible in code tab
Auto-generated SELECT function
Links datagrid to function when page is loaded
TO DO
• Oasisplus
– ASP.NET : Activities 1 & 2