ADO .NET ??

1
ADO .NET ??
2
ADO .NET Objects

• ADO .NET is a data abstraction layer that
  smoothes over difference between data providers
  and includes objects and functions for easy
  access to data
• A standard set of .NET objects
• Connection For connection to and managing
  transactions against a database.
• Command For issuing SQL commands against a
  database.
• DataReader For reading a forward-only stream of
  data records from a SQL Server data source.
• DataSet For storing, remoting and programming
  against flat data, XML data and relational data.
• DataAdapter For pushing data into a DataSet, and
  reconciling data against a database.

3
ADO .NET Architecture
4
?????

• ??ADO .NET????
• System.Data
• ??ADO.NET????
• System.Data.OleDb
• ??OLE DB????????????????Access?Excel?SQL
  Server????.?,????????,?????OleDbConnection?OleDbC
  ommand?OleDbDataReader?
• lt_at_ Import Namespace"System.Data" gt
• lt_at_ Import Namespace"System.Data.OleDb" gt
• System.Data.SqlClient
• ??SQL Server????????????????SQL Server
  7.0?????,????????,?????SqlConnection?SqlCommand?Sq
  lDataReader?
• lt_at_ Import Namespace"System.Data" gt
• lt_at_ Import Namespace"System.Data.SqlClient" gt
• System.IO
• ??XML?????

5
?????

• Connection ??(??System.Data.OleDb????)
• ??OleDbConnection???????????????OleDbConnection??
  
• Dim conn As OleDbConnection
• ??Server.MapPath????????????????
• Dim DBPath As String Server.MapPath("?????")
• ?????????????????????????????
• Dim connStr As String
• connStr "ProviderMicrosoft.Jet.OLEDB.4.0Data
  Source" _ DBPath
• ??OleDbConnection??????????????
• conn New OleDbConnection( connStr )
• ??Open???????
• conn.Open() '?????
• ???????????Close???????
• conn.Close() '?????

6
??DataReader??????

• DataReader?????
• ???????????????????
• ???????????
• ????
• ??System.Data.OleDb????,?????OleDbDataReader
• ??System.Data.Sql????,?????SqlDataReader
• Command??
• ?????SQL??
• ??Command???ExecuteNonQuery??,?????????????????
• ??Command???ExecuteReader????????DataReader????

7
??DataReader??

• ??DataReader?Command??
• Dim cmd As OleDbCommand
• Dim reader As OleDbDataReader
• ??Command???????SQL??
• cmd New OleDbCommand (SQL??, _
• OleDbConnection??)
• ??Command???ExecuteReader????SQL?????????????DataR
  eader????
• reader Cmd.ExecuteReader()

8
DataReader????????

• Read ??????????EOF(??????),?????False,??????True?
  ?????????????????????,?????????????
• FieldCount ????DataReader?????????
• GetName(i) ???i??????
• Item(i) ???i??????
• Item(????) ????DataReader????????
• GetDataTypeName(i) ???i????????
• GetOrdinal(????) ???????????
• GetValue(i) ???i????????
• IsNull(i) ???i????????

9
???????????

• ??DataReader??????????
• For i 0 To DataReader??.FieldCount - 1
• Response.Write( DataReader??.GetName(i) )
• Next
• ??DataReader???????
• Do While DataReader??.Read()
• For i 0 To DataReader??.FieldCount - 1
• Response.Write(DataReader??.Item(i))
• Next
• Loop
• Eg aspxexec

10
??DataSet???????

• DataSet??
• ????????????
• ???IIS?(??aspx?????????)
• ????????????
• Data set ??XML??????
• DataSet????DataReader????
• ???DataReader?

11
Six Classes

• DataSet
• memory-resident database
• contains DataTable and DataRelation
• DataTable
• memory-resident database table
• DataRelation
• to define relationship between DataTables
• DataView
• to filter and sort the contents of a DataTable
• SqlDataAdapter
• to build a DataTable from a SQL Server database
  table
• OleDbDataAdapter
• to build a DataTable from other types of
  databases

12
DataSet Properties and Methods

• DefaultViewManger Gets a view of the data
• HasErrors Gets a value indicating if there are
  any errors
• Relations Gets the relations collection
• Tables Gets the tables collection
• AcceptChanges Accepts all the changes made since
  loaded or since
• last time AcceptChanges was called
• Clear Clears the dataset of any data
• GetChanges Returns a copy of the dataset
  containing all the
• changes made since loaded or since
• AcceptChanges was called
• GetXML Gets the XML representation of the data
  in the
• dataset
• GetXMLSchema Gets the XSD schema for the XML
  representation
• Merge Merges the data in the dataset with
  another dataset
• ReadXML Reads an XML schema and data into the
  dataset
• ReadXMLSchema Reads an XML schema into the
  dataset
• RejectChanges Rolls back to the state since lase
  AcceptChanges
• WriteXML Writes out the XML schema and data from
  the
• dataset

13
DataTable Properties and Methods

• ChildRelations Gets the collection of child
  relations
• Columns Gets the columns collection
• Constraints Gets the constraints collection
• DataSet Gets the dataset this table belongs to
• DefaultView Gets a view of the table for
  filtering
• ParentRelations Gets the parent relations
  collection
• PrimaryKey Gets or sets an array of columns as
  primary key for
• this table
• Rows Gets the rows collection
• AcceptChanges Commits all the changes since last
  AcceptChanges
• Clear Clears the table of all data
• GetChanges Gets a copy of the DataTable with all
  the changes
• since last AcceptChanges
• NewRow Creates a new DataRow with the same
  schema as
• the table
• RejectChanges Rolls back changes since last
  AcceptChanges
• Select Gets an array of DataRow objects

14
DataRow Properties and Methods

• Item Gets or sets the data stored in a
• specific column
• ItemArray Gets or sets all the values for the
• row using an array
• Table Gets the table this row is owned by
• AcceptChanges Accepts all the changes since the
• last time AcceptChanges was
• called
• GetChildRows Gets the child rows for this row
• GetParent Row Gets the parent row of this row
• RejectChanges Rejects all the changes since the
• last time AcceptChanges was
• called

15
DataAdapter Properties and Methods

• AcceptChangesDuringFill
• Indicates whether or not to call AcceptChanges on
  a DataRow after adding it ot a DataTable
• Fill
• Fills a DataTable by adding or updating rows in
  the DataSet
• FillSchema
• Adds DataTable object to the specified DataSet
• Update
• Updates all the modified rows in the specified
  table of the DataSet

16
??DataAdapter??

• ??DataAdapter?DataSet??
• Dim DataSet?? As DataSet new DataSet()
• Dim DataAdapter?? As OleDbDataAdapter
• ??DataAdapter??????????SQL??
• ??????????DataTable??
• Selectcmd "Select from SCORE" ??SCORE???
• ??DataAdapter???Fill????????????DataSet???
• DataAdapter?? New OleDbDataAdapter(SelectCmd,
  conn)
• DataAdapter??.Fill(DataSet??, "???")
• Eg aspxexec
• ?????SQL??,???DataAdapter???Fill???????????DataSet
  ??,?????DataTable??,???????????????????

17
??DataTable???????

• DataTable??.Columns.Count
• ??DataTable??????
• DataTable??.Columns(j).ColumnName
• ??DataTable??j??????,??????0?
• DataTable??.Rows.Count
• ??DataTable???????
• DataTable??.Rows(i).Item(????)
• ??DataTable??i????????????
• DataTable??.Rows(i).Item(j)
• ??DataTable??i??j??????,??????0

18
??DataTable??????

• ??DataTable???????
• For i 0 To DataTable??.Columns.Count - 1
• Response.Write( _
• DataTable??.Columns(i).ColumnName)
• Next
• ???????????DataTable???i??j????
• For i 0 To DataTable??.Rows.Count - 1
• For j 0 To DataTable??.Columns.Count - 1
• Response.Write(DataTable??.Rows(i).Item(j
  ))
• Next
• Next
• Eg aspxexec

19
(No Transcript)