ADO'NET

1
ADO.NET

• Mark E. Davidson 2003
• mdavidson_at_cprgr.com

2
What is ADO.NET

• Classes to expose data access services
• Provides access to
• Relational data
• XML
• Application data
• XML enabled

3
Main advantages of ADO.NET over ADO

• ADO.NET
• Designed for disconnected data access
• Data can be modeled logically
• Dataset replaces Recordset

4
Advantages of Dataset over Recordset

• Dataset
• Can contain multiple tables
• Retrieving data from more than one table so
  source doesnt require a JOIN
• Relationships are preserved
• Navigation is relational

5

• ADO.NET Namespaces
• System.Data
• .Common
• .Odbc
• .OleDb
• .OracleClient
• .SqlClient
• .SqlServerCe
• .SqlTypes
• Other Native ADO.NET Clients are provided by
  various Database Vendors
• Oracle, TurboDB.NET, MySql, Teradata, DB2

6
System.Data

• Root ADO.NET namespace
• Provides
• Classes and methods to manipulate data
• Ability to create views of local data
• Ability to logically represent data
• Ability to use XML to view, share, and store data

7
Common ADO.NET Classes

• System.Data

8
All Together
Tables
DataView
DataTable
DataViewManager
DataViewSettings
DataViewSetting
DataViewSetting
DataTable
DataTable
9
DataSet

• In-memory cache of data from a data source
• May be used without a database
• Designed for disconnected data access
• Connect, execute query, disconnect
• XML enabled
• Read and write data (ReadXml, WriteXml)
• Read and write XML Schema (ReadXmlSchema,
  WriteXmlSchema)

10
DataSet Properties

• Collections to add/remove tables relations

11
DataTable

• Used by
• DataSet
• DataView
• Conditionally case-sensitive
• Max rows is 16,777,216
• Can be mapped to physical table in data source
• Optimistic concurrency or locking model

12
DataTable Properties
13
DataColumn

• Fundamental building block for DataTable schema
• Properties
• DataType
• Needs to match data source
• AllowDBNull
• Unique
• ReadOnly
• AutoIncrement
• Expression

14
DataRow

• Table DataTable for which this row has a schema
• RowState
• Added, Deleted, Detached, Modified, Unchanged
• Retains original values of attributes after
  modification
• To allow DataAdapter to find the original row in
  the database
• To allow reviewing the changes before accepting
  changes
• To allow the DataRows changes to be abandoned
  without refreshing from the database

15
DataRelation

• Used to relate two DataTable objects
• Using DataColumn objects
• The DataType value for both DataColumns must be
  identical
• Can be used to cascade changes
• Prevents any change that would invalidate
  DataRelation

16
DataView

• Bindable to user interface controls
• Presents a subset of data from DataTable
• Properties of Interest
• Table The associated DataTable
• Sort The tables sort columns and sort order
• RowFilter The expression used to filter rows
• RowStateFilter
• Allows rows to be filtered based on the source
  rows state
• None, Unchanged, New, Deleted, ModifiedCurrent
  and others

17
DataViewManager

• Manages DataViews oriented toward DataSets
• Used to create views on a DataSet
• Ability to automatically set filters on the
  tables
• Properties
• DataViewSettings - a DataViewSettingsCollection
  for each DataTable in the DataSet
• DataSet - the DataSet to be viewed
• CreateDataView method
• Creates a DataView on a DataTable

18
ADO.NET Data Providers

• A collection of classes for accessing data
  sources
• Establish connection between DataSets and data
  stores
• Some provided by Microsoft
• Some provided by Database Vendors
• Mattisse even provides one that is extended to
  provide additional functionality for their object
  database

19
Interfaces implemented by Data Providers

• IDataAdapter/IDbDataAdapter
• Bridge between a data source and a DataSet
• IDataReader/IDbDataReader
• Provides a means of reading one or more
  forwared-only streams of result sets
• IDbTransaction
• Represents the transaction to be performed at a
  data source

20
Interfaces implemented by Data Providers

• IDbConnection
• Represents a unique session with a data source
• IDataParameter/IDbDataParameter
• Represents a parameter to a Command object
• IDbCommand
• Represents an SQL statement that is excecuted at
  a data source

21
SqlConnection

• ConnectionString
• The connection string that includes the database
  name and other parameters needed to establish the
  intial connection
• Open
• Close
• CreateCommand
• Creates and returns a SqlCommand object
  associated with the SqlConnection

22
SqlCommand

• Represents a Transact-SQL statement or stored
  procedure to execute against a SQL Server
  database
• SqlCommand object can be reused
• Must close existing SqlDataReader before
  executing new or previous command
• If exception is thrown on execution the
  SqlConnection may close if the severity level is
  above 20, but can be reopened

23
SqlCommand Properties

• CommandText
• SQL statement
• If stored procedure then stored procedure name
• CommandType
• Text, StoredProcedure
• Connection
• SqlConnection used by this instance of SqlCommand
• Parameters
• Parameters of SQL statement or stored procedure

24
SqlCommand Methods

• ExecuteReader
• Executes commands that return rows.
• Returns SqlDataReader object
• ExecuteNonQuery
• Execute commands such as insert, delete, update,
  and set statements
• ExecuteScalar
• Retrieves a single value
• ExecuteXmlReader
• CommandText property usually specifies a SQL
  statement with a valid FOR XML clause.
• Returns an XmlReader object

25
SqlParameter

• ParameterName
• Direction
• Input-only, output-only, bidirectional, or a
  stored procedure return value parameter
• Value
• SqlDbType

26
SqlDataReader

• HasRows
• IsClosed
• Item Value of column in native format. Indexer
  in C
• RecordsAffected
• IsDBNull
• Read Advances reader to next record
• Close

27
SqlDataAdapter Properties

• SelectCommand the SqlCommand object used to
  retreive rows from the table in the source
  database
• InsertCommand the SqlCommand object used to
  insert new rows
• UpdateCommand the SqlCommand object use to
  updates rows
• DeleteCommand the SqlCommand object used to
  delete rows

28
SqlDataAdapter Methods

• Fill Adds or refreshes rows in the DataSet to
  match those in the data source
• FillSchema
• Adds a DataTable to a DataSet and configures the
  schema to match that in the data source
• Update
• Calls the respective Insert, Update, or Delete
  statements for each inserted, updated, or deleted
  row in the DataSet

29
SqlCommandBuilder

• Simplifies using SqlDataAdapter
• Creating Insert, Update, and Delete SqlCommands
  can be complicated for even a simple table.
• Given a Select command and a database connection,
  SqlCommandBuilder retrieves schema information
  and generates the other commands as needed.

30
Ways to display data in ASP.NET

• DataGrid
• DataList
• Repeater

31
Similarities

• All designed to display data
• Code needed to bind data to control is the same
• Set DataSource
• DataBind()
• DataSource can be a DataSet, DataReader, a
  collection, any object that implements
  IEnumerable
• DataBind method enumerates through each of its
  items adding Items to data Web Control
• Capable of using templates for highly
  customizable output

32
DataGrid

• Most versatile in features
• Least flexible in customizing the actual HTML
  generated
• Single table row for each record and single table
  column for each field
• Short development time
• Supports selection, editing, deleting, paging,
  and sorting
• Disadvantages
• Limited HTML customization
• Worst performance
• Large ViewState

33
DataGrid

• Column types
• BoundColumn
• ButtonColumn
• EditColumn
• HyperLinkColumn
• TemplateColumn

34
DataList

• Display defined via templates
• Specify both HTML syntax and databinding syntax
• lt gt delimiters for databinding syntax
• Can have multiple records per row
• RepeatColumns property
• Can have output rendered in table or span tags
• RepeatLayout

35
DataList

• Ability to edit records is more difficult than
  DataGrid
• Must manually add buttons to datalist
• Must explicitly specify the editing interface
• Significant development time required for sorting
  and paging of data
• Better performance than DataGrid

36
DataList

• Templates
• ItemTemplate
• AlternatingItemTemplate
• EditItemTemplate
• FooterTemplate
• HeaderTemplate
• SelectedItemTemplate
• SeperatorTemplate

37
Repeater

• Most flexibility in HTML rendered
• Must be used if you need to display data in some
  way other than table or span tags
• All formatting must be done the HTML
• HTML code clutters up other code making changes
  more difficult
• No built-in support for paging, editing, or
  sorting
• Best performance
• Worst usability

38
Repeater

• AlternatingItemTemplate
• FooterTemplate
• HeaderTemplate
• ItemTemplate
• SeparatorTemplate

39
Web Database Project

• Inventory tracking application
• Users can login and have rights based on their
  login.
• If a user doesnt have access to a portion of the
  site the corresponding tab is removed from their
  menu
• Ability to
• Enter inventory
• View Inventory
• Enter Shipment
• Receive Shipment

40
(No Transcript)
41
(No Transcript)
42
(No Transcript)
43
DataGrid Example

• ltaspdatagrid iddgComponents runat"server"
  Visible"False" CellPadding"2" BorderWidth"1px"
  BorderColor"Black" AutoGenerateColumns"False"
  DataSource"lt shipmentItems gt"
  DataKeyField"id"gt
• ltHeaderStyle Font-Bold"True"gtlt/HeaderStylegt
• ltColumnsgt
• ltaspTemplateColumn Visible"False"
  HeaderText"id"gt
• ltItemTemplategt
• ltaspLabel ID"id" runat"server" Text'lt
  DataBinder.Eval(Container, "DataItem.id") gt /gt
• lt/ItemTemplategt
• lt/aspTemplateColumngt
• ltaspTemplateColumn Visible"False"
  HeaderText"item_id"gt
• ltItemTemplategt
• ltaspLabel ID"item_id" runat"server"
  Text'lt DataBinder.Eval(Container,
  DataItem.item_id") gt /gt
• lt/ItemTemplategt
• lt/aspTemplateColumngt
• ltaspTemplateColumn HeaderText"Item/ECL/Descrip
  tion"gt
• ltItemTemplategt
• ltaspLabel id"item" runat"server" Text'lt
  DataBinder.Eval(Container, "DataItem.item").ToStri
  ng()"/" DataBinder.Eval(Container,
  "DataItem.ecl").ToString()"/"
  DataBinder.Eval(Container, "DataItem.item_desc").T
  oString()gt /gt
• lt/ItemTemplategt
• ltEditItemTemplategt

44
DataGrid Example

• ltaspTemplateColumn HeaderText"Quantity"gt
• ltItemTemplategt
• ltaspLabel runat"server" Text'lt
  DataBinder.Eval(Container, "DataItem.qty",
  "0,.") gt/gt
• lt/ItemTemplategt
• ltEditItemTemplategt
• ltaspTextBox ID"txtQty" runat"server"
  Text'lt DataBinder.Eval(Container,
  "DataItem.qty", "0.")gt /gt
• ltaspRequiredFieldValidator id"rfvQty"
  runat"server" ErrorMessage"You must enter a
  Quantity" Display"None"
• ControlToValidate"txtQty"gtlt/aspRequiredField
  Validatorgt
• ltaspRangeValidator id"rvQty" runat"server"
  ErrorMessage"You must enter a Quantity between
  0.00001 - 999999.99999 Display"None"
  ControlToValidate"txtQty" Type"Double"
  MinimumValue"0.00001" MaximumValue"999999.99999"
  gtlt/aspRangeValidatorgt
• lt/EditItemTemplategt
• lt/aspTemplateColumngt
• ltaspEditCommandColumn ButtonType"LinkButton"
  UpdateText"Update" CancelText"Cancel"
  EditText"Edit"gtlt/aspEditCommandColumngt
• ltaspButtonColumn Text"Delete"
  CommandName"Delete"gtlt/aspButtonColumngt
• ltaspTemplateColumn Visible"False"
  HeaderText"date_departed"gt
• ltItemTemplategt
• ltaspLabel id"date_departed" runat"server"
  Text'lt DataBinder.Eval(Container,
  "DataItem.date_departed", "0d") gt'gt
• lt/aspLabelgt
• lt/ItemTemplategt
• lt/aspTemplateColumngt

45
DataGrid Edit EventHandler

• private void dgComponents_EditCommand(object
  source, System.Web.UI.WebControls.DataGridCommandE
  ventArgs e)
• if(dgComponents.EditItemIndex gt 0)
• if(((Label)dgComponents.ItemsdgComponents.Edi
  tItemIndex.FindControl("item_id")).Text
  string.Empty)
• this.deleteRow(((Label)dgComponents.ItemsdgC
  omponents.EditItemIndex.FindControl("id")).Text)
  
• this.fillShipmentDS()
• dgComponents.EditItemIndex e.Item.ItemIndex
• this.populatePartsDropDown()
• dgComponents.DataBind()
• ((DropDownList)dgComponents.ItemsdgComponents.
  EditItemIndex.FindControl("ddlPart")).SelectedVal
  ue ((Label)e.Item.FindControl("item_id")).Text
• this.lbStartNewShipment.Visible false
• this.lbAddItem.Visible false

46
DataGrid Cancel Event Handler

• private void dgComponents_CancelCommand(object
  source, System.Web.UI.WebControls.DataGridCommandE
  ventArgs e)
• dgComponents.DataBind()
• if(((Label)e.Item.FindControl("item_id")).Text
  string.Empty)
• this.deleteRow(((Label)e.Item.FindControl("id"
  )).Text)
• this.fillShipmentDS()
• dgComponents.EditItemIndex -1
• dgComponents.DataBind()
• this.lbStartNewShipment.Visible true
• this.lbAddItem.Visible true

47
DataGrid Update EventHandler

• if(IsValid)
• SqlConnection sqlConn (SqlConnection)Context.Ca
  che"sqlConn"
• SqlCommand sqlCommand sqlConn.CreateCommand()
• sqlCommand.CommandText "UpdateShipmentItem"
• sqlCommand.CommandType CommandType.StoredProced
  ure
• sqlCommand.Parameters.Add("_at_id",
  ((Label)e.Item.FindControl("id")).Text)
• sqlCommand.Parameters.Add("_at_item_id",
  ((DropDownList)e.Item.FindControl("ddlPart")).Sele
  ctedValue)
• sqlCommand.Parameters.Add("_at_qty",
  decimal.Parse(((TextBox)e.Item.FindControl("txtQty
  ")).Text, new System.Globalization.CultureInfo("e
  n-US").NumberFormat))
• if(sqlConn.State ! ConnectionState.Open)
• sqlConn.Open()
• sqlCommand.ExecuteNonQuery()
• sqlCommand.Dispose()
• dgComponents.EditItemIndex -1
• this.fillShipmentDS()
• dgComponents.DataBind()
• this.lbStartNewShipment.Visible true

48
(No Transcript)
49
ADO.NET 2

• What is in store for ADO.NET 2
• System.Data.SqlResultSet provides server-side
  cursor capability
• Support for setting and receiving SQL Server
  notifications
• Multiple Active Result Sets (MARS)
• Asynchronous Query execution
• BulkCopy class
• DataAdapter bulk updates
• Object-Relational Mapping with ObjectSpaces