Chapter 6: ASP.NET Managing Data Sources

1
Chapter 6 ASP.NET Managing Data Sources
Original slides by Kathleen Kalata Dave
Turton Modified by Meyer Tanuan
2
Objectives

• Learn about the ADO.NET model
• Build a database connection
• Create a SQL Server database using Visual Studio
  .NET
• Manage a database using the Visual Studio .NET
  database tools
• Create SQL stored procedures using Visual Studio
  .NET

3
Universal Data Access Model

• Data can be shared across different platforms
• Low-level interfaces
• ODBC (Open DataBase Connectivity 1992)
• Access a database without a vendor-specific
  interface
• OLE-DB (Object Linking and Embedding)
• Access more data stores as if they were databases
• High-Level interface
• ADO.NET (ActiveX Data Object model)
• Provides objects to manipulate data
• Works through ODBC and OLE-DB
• i.e. application code same, regardless of RDBMS
  used

4
.NET Managed Data Providers
5
SQL Server vs OLE-DB Data Providers

• SQL Server has a native .NET interface
• Has a performance improvement doesn't have to
  interpret OLE-DB COM objects
• Has a separate set of objects
• SqlConnection vs OleDbConnection
• SqlCommand vs OleDbCommand
• SqlDataReader vs OleDbDataReader
• SqlDataAdapter vs OleDbDataAdapter
• Objects function similarly, however.

6
ADO.NET Core Objects
7
ADO.NET Object ModelConnection Object

• Provides connection to database and connection
  string
• Provider - OLE-DB data provider
• Data Source - database server
• User ID - server uses authority of this user
• Password - password of user
• Initial Catalog - name of the database (SQL Svr
  only)
• CS "Providermsdaora Data SourceOracleDBName
  " _
• "User IDMyUser PASSWORDmyPassword
• CS "ProviderSQLOLEDB.1 Data
  SourceWindamereServer " _
• "User IDsa PASSWORD Initial
  CatalogmyDatabase"

8
ADO.NET Object ModelConnection Object (cont'd)

• Properties
• ConnectString
• Data connection string (prior page)
• Database
• Name of the database
• Server
• Name of the database server
• Type
• Database app eg Microsoft Access or SQL Server
  2000
• Driver
• ODBC driver or OLE-DB provider
• State
• If database currently connected (server could
  fail in use)
• User
• User that created the database

9
ADO.NET Object ModelTransaction Object

• Can group data commands together
• If any command fails, can back out entire
  transaction

10
ADO.NET Object ModelCommand Object

• To identify an SQL command or stored procedure
• Stored procedure SQL commands stored on database
• Properties
• Parameters
• Parameters for a stored procedure
• CommandText
• SQL command text
• Connection
• Connection object used to access the database
• CommandType
• Text (default) use the SQL text string
• TableDirect name of a table to return
• StoredProcedure use a stored procedure
• Methods
• Execute pass results to the DataReader object
• ExecuteNonQuery return of rows modified/deleted

11
ADO.NET Object ModelDataReader Object

• Delivers a stream of data
• Needs continual connection to db
• Connection broken when response sent to browser
• So browser cannot react to user action, like
  scrolling
• Can't go backwards or modify data
• Faster data retrieval
• Methods
• Read return a row move data pointer
• CloseConnection close connection release ref's
• Can be bound to ASP.NET data controls

12
Using ADO.NET Objects to Access a DataSet Object
13
ADO.NET Object ModelDataAdapter Object

• Provides access to a DataSet object
• DataSet
• A local copy of part of a database
• Inserts, updates and deletes applied to this
• Results must be uploaded to database
• Can be bound to ASP.NET data controls
• Methods
• SelectCommand retrieve data from db
• Fill Inserts data into DataSet
• InsertCommand
• DeleteCommand Modify records in DataSet
• UpdateCommand
• Update Upload DataSet changes to database

14
Using the DataSet Object toAccess the
DataTableCollection
15
ADO.NETNamespaces

• System.Data
• Data objects DataSet, DataTable, DataRelation
• System.Data.SqlClient
• SQL Server OLE-DB .NET data provider
• Connection, Command, DataReader, DataAdapter
• System.Data.OleDb
• Same as above, for all other databases

16
The ADO.NET DataView Object

• Contains the data from the DataSet for a single
  DataTable or subset of records from a table
• DataTable object has a DefaultView property that
  returns all the records in the DataTable.
  However, you can select a subset of records from
  a table
• You an add columns to the DataColumnCollection as
  well as to filter and sort the data in a DataView
  
• RowFilter property to filter a subset of the
  DataView
• Sort property to sort the data based upon a
  specific criteria in one or more of the columns

17
Using VS.NET to access Databases
Under Server Explorer, select the server SQL
Services. Walk through the service instance to
locate your database. You can view tables,
display field properties, add, delete or modify
records
18
Using VS.NET to access Databases
or use VS.NET to examine/manage field (column)
properties.
19
Creating a New Database
20
Adding Tables to Database
Note you will be prompted for a table name when
you close the view
21
Adding field/column Definitions
Defining a primary key must have values unique
to the table could have it automatically built
from seed and increment values.
22
Extended properties vary by field type
23
Populating a Table (after saving from design mode)
24
Query View Editor

• Used to test queries
• Right-click Views in Server Explorer
• Select New View
• 4 panes a table-select pop-up
• Table pane
• Add tables select columns for query

• Grid pane
• Column selector
• Display name (if output
• Sort order
• Selection criteria
• SQL pane
• SQL code for above
• Output pane
• Results of query

25
Table Pane
Grid Pane
SQL Pane
Output Pane
26
Database Diagrams

• To define relationships between tables
• Drag primary key of one table to corresponding
  column in another table
• Symbols indicate 1-to-many or 1-to-1
• Will enforce referential integrity to all new
  data
• If data already entered
• Can specify new relationships
• Cannot specify relationships that invalidate
  current data

27

• Click and drag column from one table to
  corresponding column on other table
• key infinity symbol indicate 1-to-many
  relationship direction
• Create Relationship window allows you to modify
  the relationship

28
Stored Procedures

• SQL commands that are stored on database
• Already parsed compiled, so are faster
• Input parameters
• Value passed to procedure when it's run
• Parameter name prefixed by "_at_" in procedure
• Output Parameters
• Send values back to calling object
• InputOutput parameters
• Value passed to and returned from procedures
• ReturnValue Parameter
• Value returned by keyword Return

29
Input Parameter (definition)
Using Input Parameter
30
Stored procedure to insert a record

• On run
• Requests input parameters for each field in
  record
• Use Insert command
• List fields to be placed in new record
• Do not mention identity (autonumber)
• Values list parameter in same order as fields are
  listed in Insert
• Use RETURN _at__at_Identity
• To see identity (autonumber) field value

31
Note becomes name of procedure in index