Introduction to ADO.
NET
2.0
Agenda
Plain Talk
.NET Framework Data Provider
Operation without Connection to a Database
Applications
Plain Talk
Categories of Objects
Connected Objects:
Connecting a database
Read-only record set
Forward-only stream
Disconnected Objects:
No connection to a database
Off-line operation
Sorting, Searching, Filtering, Modifying
[Link] Architecture
.NET Framework DataSet
Data Provider
DataTableCollection
Connection
DataAdapter
Transaction
DataTable
SelectCommand
DataRowCollection
InsertCommand
Command
DataColumnCollection
Parameters UpdateCommand
ConstraintCollection
DeleteCommand
DataReader
DataRelationCollection
DB XML
Connected Objects –
Connection Object
A Connection for communicating with a
Database
As an ADO Connection Object
Connected Objects –
Command Object
Query
Execute a Stored Procedure
Any SQL Command
As an ADO Command Object
Connected Objects –
DataReader Object
Data Row is Read-Only
Keep one row only
Forward-only stream
Connected Objects –
DataAdapter Object
A Bridge for Disconnected Objects
One DataAdapter mapping to one DataTable
Disconnected Objects –
DataTable Object
Retrieving data from the Rows property
As an ADO Recordset Object
Disconnected Objects –
DataSet Object
A container for DataTable objects
Exchange easily with XML Docs.
As an ADO Recordset Object
Disconnected Objects –
DataRelation Object
Define a relation between DataTable objects
i.e. Matster-to-Detail
Define a rule. i.e. Delete the details when to
delete the master"s record.
Disconnected Objects –
DataView Object
Define a rule. i.e. Sorting or Filtering a
DataTable
.NET Framework Data Provider
Using the Connection Object (on e)
Using a Pooling Connection by default
( SQL Server)
Using a connection: ( VB + SQL Server )
Dim myConnection as SqlConnection
[Link] = “…”
[Link]()
[Link] ()
Create a Transaction Object synchronously
( VB+ SQL Server )
Dim mySqlTransaction as SqlTransaction
mySqlTransaction = [Link]
[Link] ()
[Link] ()
Using the Connection Object (two)
Create a Command Object synchronously
Dim myCommand as SqlCommand
myCommand = [Link]
Other usages:
Property: State
Methods: Open, Close
Event: StateChange
Using the Command Object (on e)
How to get a Command Object?
Operation without a result set.
Operation with a result set.
Operation with Parameters.
Operation with a Stored Procedure with
Parameters.
Operation with a Stroed Procedure with a return
value.
Using the Command Object (two)
How to get a Command Object?
( VB + SQL Server )
A) myCommand = [Link]
[Link] = “…”
B) myCommand = [Link] ()
[Link] = myConnection
[Link] = “…”
C) myCommand = [Link](mySqlStr,
myConnection)
Using the Command Object (thre e)
Operation without a result set:
DML (Data Manipulation Language)
Insert, Update, Delete
DDL (Data Definition Language)
Create Table, Alter View, Drop Table, and so on.
How to do?
[Link] = “Update permis set…”
[Link] ()
Using the Command Object (fo ur )
Operation with a result set
DataReader Object:
Call the ExecuteReader method of the Command object, rather
than directly using a constructor
Don"t open another DataReader object in the same connection
Forward-only stream
Read-only result set
Keep one row only
How to do?
Dim myDataReader as SqlDataReader
[Link] = “select * from PermisUser"”
myDataReader = [Link]
While [Link]()
[Link] (myDataReader(0), myDataReader(1))
End While
[Link]()
Using the Command Object (fi ve)
Read a field of data in the DataReader
Dim myDataReader as SqlDataReader
[Link] = "select emp_na from
PermisUser"
myDataReader = [Link]()
[Link]()
strEmpName = [Link]( "emp_na" ).ToString()
Special operation:
Dim sCount as String
[Link] = "select count(*) from
PermisUser”
sCount = [Link]()
Using the Command Object (si x)
Operation with Parameters ( VB + SQL Server )
[Link] =
"select emp_na from CtpPermisUser where emp_no =
@Param1”
[Link]("@Param1",
[Link])
[Link](0).Value = "92110015”
myDataReader = [Link]()
[Link]()
sResult = [Link]("emp_na").ToString()
SQL Server .NET Provider supports Parameters with the “@ + ParamName”
format.
Using the Command Object (seven )
Operation with a Stored Procedure with
Parameters ( VB + SQL Server )
[Link] = "spGetCM”
[Link] =
[Link]
[Link]("@PlayDate",
[Link])
[Link]("@PlayDate2",
[Link])
[Link]("@PlayDate").Value = "2004/01/12"
[Link]("@PlayDate2").Value = "2004/01/13"
myDataReader = [Link]()
[Link]()
sResult = [Link]("CM_Na").ToString()
CommandType: Text (default), StoredProcedure, TableDirect
Using the Command Object (ei ght)
Operation with a Stored Procedure with a return
value ( VB + SQL Server )
[Link] = "spGetCM"
[Link] = [Link]
[Link]("@Return_Value", [Link])
[Link]("@PlayDate", [Link])
[Link]("@PlayDate2", [Link])
[Link] = [Link]
[Link]("@PlayDate").Value = "2004/01/12"
[Link]("@PlayDate2").Value = "2004/01/13"
[Link]()
strResult =
[Link]("@Return_Value").[Link]()
Direction: Input (default), Output, InputOutput, ReturnValue
Using the DataAdapter Object (on e)
A bridge between Connected Objects and
Disconnected Objects. (DataSet, DataTable)
DataAdapter
DataSet
Database DataTable
Using the DataAdapter Object (two)
How to get a DataAdapter Object?
( VB + SQL Server )
Dim myDataAdapter as SqlDataAdapter
A) myDataAdapter = [Link](strSQLSelect,
strConnection)
This is not a good idea. It will establish another connection.
B) myDataAdapter = [Link](strSQLSelect,
myConnection)
myCommand = [Link](strSQLSelect, myConnection)
C) myDataAdapter = [Link]()
[Link] = myCommand
D) myDataAdapter = [Link](myCommand)
Using the DataAdapter Object
(three)
Move a result set from a DataAdapter to
DataSet: Fill method.
Auto open an established connection which had be
closed.
Auto create a DataTable object in the DataSet.
By default, the DataTable object name is Table.
Fill with a result set with ColumnName, Ordinal, and
DataType.
Paging through a query result.
Move a result set from ADO to [Link]
Using the DataAdapter Object (fou r)
How to use the Fill? ( VB + SQL Server )
Dim myDataSet as DataSet
myDataSet = [Link]()
myDataAdapter = [Link](strSQLSelect,
myConnection)
A) [Link](myDataSet)
B) [Link]("Table", "Customers")
[Link](myDataSet)
C) [Link](myDataSet, "myTableName")
D) [Link](myDataSet, nStartRow, nRows,
"myTableName")
nStartRow is zero-base.
E) [Link](myDataTable)
If you have a DataTable object
Using the DataAdapter Object (f ive)
Applications:
You will fill the same DataTable twice:
If your DataTable has no primary key:
The result set from the DataAdapter is appended.
If your DataTabel has a primary key:
DataTable is updated by the primary key.
Use the FillSchema method to let your DataTable get a primary
key as its mapping table in our database.
[Link](myDataSet, [Link])
Avoid that result set is duplicate in the DataTable.
First to clear the DataTable, then fill DataTable with a
new result set.
Using the DataAdapter Object (six)
Other usages:
Methods:
Fill
FillSchema
Update
Events:
FillError
RowUpdating
RowUpdated
Operation without Connection to a
Database
Using the DataSet Object (on e)
You can:
scroll, search, sort, and filter a DataSet object.
define a master-detail relation.
modify the DataSet of content.
integrated with XML Doc.
Using the DataSet Object (two)
How to get a DataSet object? ( VB + SQL Server )
Dim myDataSet as DataSet
A) myDataSet = [Link]()
B) myDataSet = [Link]("myDataSetName")
Using the DataSet Object (three)
Use the DataRow object
Read the 1st row, the 2nd column which name is
Emp_Na. ( VB + SQL Server )
[Link](myDataSet, "myTableName")
A) [Link](0).Rows(0)(1).ToString
B) [Link]("myTableName").Rows(0)("Emp_Na").ToString
C) [Link](0).[Link](0).Item(1).ToString
D)
[Link]("myTableName").[Link](0).Item("Emp_Na").To
String
Using the DataSet Object (fou r)
Use the DataColumn object
Read properties of the 2nd column which column
name is Emp_Na. ( VB + SQL Server )
[Link](myDataSet, "myTableName")
A)
[Link]("myTableName").Columns(1).[Link]
B)
[Link]("myTableName").Columns("Emp_Na").[Link]
ng
C)
[Link]("myTableName").Columns("Emp_Na").[Link]
tring
Using the DataSet Object (five)
Other usages
Methods:
AcceptChanges GetXml
RejectChanges GetXmlSchema
GetChanges ReadXml
Clear ReadXmlSchema
Clone WriteXml
Copy WriteXmlSchema
Merge
Reset
Using the DataTable Object (on e)
One DataTable exists in one DataSet only.
How to get it? ( VB + SQL Server )
A) [Link](myDataSet, "TableName")
B) [Link](myDataSet, [Link],
"TableName")
C) Dim myDataTable as DataTable
Dim myColumn as DataColumn
myDataTable = [Link]("TableName")
myColumn = [Link]("Emp_No")
[Link] = 10
Create a DataTable with a string type of Emp_No field.
myColumn = [Link]("HowOld", TypeOf(Integer))
Using the DataTable Object (two)
Add Constraints: ( VB + SQL Server )
AllowDBNull
[Link] = False
ReadOnly
[Link] = True
Unique
[Link] = True
Column of MaxLength
[Link] = 5
Using the DataTable Object (three)
Primary Key Constraint?
Combine two fields for a Primary Key. ( VB + SQL Server )
Dim aryColumn as DataColum()
aryColumn(0) = [Link]("Emp_No",
TypeOf(Integer))
aryColumn(1) =
[Link]("Emp_Name")
[Link] = aryColumn
Using the DataTable Object (fou r)
Foreign Key Constraint?
Use the ConstraintCollection. ( VB + SQL Server )
Dim myForeignKey as ForeignKeyConstraint
Dim myMasterCol, myDetailCol as DataColumn
myForeignKey =
[Link](myMasterCol, myDetailCol)
[Link](myForeignKey)
Using the DataTable Object (fi ve)
Can I add a calculated field? ( VB + SQL Server )
Dim strSubtotal as String
strSubtotal = "AmountFieldName * CostFieldName"
[Link]("myCalcFieldName",
TypeOf(Integer), strSubtotal)
Any functions can be put inside the strSubtotal.
Using the DataTable Object (si x)
Insert:
If your DataTable is… ( VB + SQL Server )
Emp_No Emp_Name Emp_Birth Emp_Salary
92010001 Andy 1965/01/21 32000
92010002 Bill 1970/08/04 29000
Dim myRow as DataRow // insertion
myRow = [Link]()
myRow("Emp_No") = "92090020"
myRow("Emp_Name") = "Tom"
[Link](myRow) // don"t forget
Maybe you use the [Link](…)
Using the DataTable Object (sev en )
Edit:
Dim theRow as DataRow
theRow = [Link]("92010002")
If theRow Is Nothing then
ShowMessage("Primary-Key Value not found.")
Else
theRow("Emp_Salary") = 33000
or
…
Else begin
Reference
[Link]
Method:
theRow("Emp_Salary") = 33000 CancelEdit()
[Link] Events:
RowChanging
End RowChanged
ColumnChanging
ColumnChanged
Using the DataTable Object (ei gh t)
Delete:
Use the Delete() to mark.
Use the Remove() / RemoveAt() to remove the row
truly.
Use the Clear() to remove all rows.
[Link]()
or
[Link](theRow)
or
[Link]()
Using the DataTable Object (nine)
Verify the state of the row – RowState property
[Link]("CtpPermisUser").Rows(nRowIndex).RowState
Constant Value Desc.
The row has been created but is not part of any
Detached 1 DataRowCollection.
DataRowCollection. A DataRow is in this state
immediately after it has been created and before it is
added to a collection, or if it has been removed from a
collection.
The row has not changed since AcceptChanges was
Unchanged 2 last called.
The row has been added to a DataRowCollection,
DataRowCollection, and
Added 4 AcceptChanges has not been called.
The row was deleted using the Delete method of the
Deleted 8 DataRow.
DataRow.
The row has been modified and AcceptChanges has
Modified 16 not been called.
Using the DataTable Object (ten )
DataRowVersion Enumeration. ( VB+ SQL Server )
sResult =
[Link]("Emp_Name",
[Link]).ToString
Using the DataTable Object (el ev en)
Other usages
Methods:
AcceptChanges BeginLoadData
RejectChanges EndLoadData
GetChanges ImportRow
Clear
Clone
Copy
Compute
Reset
DataColumn Objects
Other usages
Properties:
Caption
DefaultValue
Table
DataRow Objects
Other usages
Properties: Methods:
RowError AcceptChanges
Table RejectChanges
CancelEdit
GetChildRows
GetParentRow
GetParentRows
SetParentRow
IsNull
Use the DataRelation Object (on e)
Make a relation between DataTable-s.
How to get it?
Dim myRelation as DataRelation
Dim myCustTable, myOrderTable as DataTable
myRelation = [Link]("RelationName",
[Link]("CustID"),
[Link]("CustID"))
[Link](myRelation)
Maybe the 2nd & 3rd parameters are the "array of
DataColumn".
Use the DataRelation Object (two)
Get the Details:
Dim theCustRow, theOrderRow as DataRow
For theOrderRow in
[Link]("RelationName") do
ShowMessage(theOrderRow("ProdName").ToString)
Get the Master from one Detail?
theCustRow = [Link]("RelationName")
ShowMessage(theCustRow("CustName").ToString)
How about the GetParentRows()?
Use the DataRelation Object (three )
If you establish a DataRelation object, …
UniqueConstraint is added.
ForeignKeyConstraint is added.
Exist constraints are added.
Use the DataRelation Object (fo ur )
Other usages
Properties:
ChildTable
ParentTable
Nested
Applications
Searching, Sorting, Filtering (on e)
Searching: Find()
Find a row by the Primary Key.
The Primary Key included only one column.
theRow = [Link]("myProdId")
or, the primary key is established by two columns.
Dim aryKey as object()
aryKey(0) = nSerialNo
aryKey(1) = "myProdId"
theRow = [Link](aryKey)
Searching, Sorting, Filtering (two )
Searching: Select()
Find rows with conditions.
Dim theRows as DataRow()
Dim strCondition as String
strCondition = "ProdType = ""PO"" and Cost > 10000 and
ID like ""A%"""
theRows = [Link](strCondition)
Sorting: Select()
Var strSort : String
strSort = "Cost Desc"
theRows = [Link](strCondition, strSort)
Searching, Sorting, Filtering (three)
Searching: Select() with DataViewRowState
Searching changed rows inside the DataTable.
Dim enuDvrs as DataViewRowState
enuDvrs = [Link] or
[Link]
theRows = [Link]("", "", enuDvrs)
Searching, Sorting, Filtering (f ou r)
DataView objects?
Not a SQL command.
One DataView is mapping to one DataTable.
A DataTable must have a TableName.
Including all columns from one DataTable.
Searching, sorting, filtering, editing, and
navigation.
Searching, Sorting, Filtering (fi ve)
Get a DataView object:
Dim myDataTable as DataTable
Dim myDataView as DataView
myDataTable = [Link](myDataSet,
"myTableName")
myDataView = [Link](myDataTable)
Searching, Sorting, Filtering (si x)
Retrieve rows from a DataView object:
Dim enuDvrs as DataViewRowState
enuDvrs = [Link] or [Link]
myDataView = [Link](myDataTable,
"ProdNo like ""S%"" ",
"Cost Desc",
enuDvrs)
or
myDataView = [Link]
[Link] = myDataTable
[Link] = "ProdNo like ""S%"""
[Link] = "Cost Desc"
[Link] = enuDvrs
Searching, Sorting, Filtering (sev en)
Browsing a row in a DataView:
Use the Count property.
Dim theRowView : DataRowView
For nRowIndex = 0 to [Link] - 1
theRowView = myDataView(nRowIndex)
ShowMessage(theRowView("theFieldName").ToString)
Next
or
For nRowIndex = 0 to [Link] - 1
sResult =
myDataView(nRowIndex)("theFieldName").ToString
ShowMessage(sResult)
Next
Use the GetEnumerator() method.
Searching, Sorting, Filtering (ei ght)
Find() in the DataView object.
DataView Object [Link]
Searching By Sorting Fields By Primary Key
Condition
Found Row Index Fields
DataRow object
Not Found -1 Nil, Null
FindRows() ?
Return an array of DataRowView objects.
Dim aryRows as Object()
aryRows = [Link](mySearchingValue)
If ( Length(aryRows) > 0 ) then …
Editing with DataRowView
Add a new row.
Dim theRowView as DataRowView
theRowView = [Link]()
theRowView("ProdName") = "xxx"
[Link]() // update the DataTable object
Modify a row.
[Link]()
theRowView("ProdName") = "yyy"
[Link]()
Delete a row.
[Link]()
DataView & DataRowView Objeccts
Other usages:
DataView objects
Methods: CopyTo, Delete
Properties: AllowDelete, AllowEdit, AllowNew,
RowStateFilter
DataRowView objects
Methods: CancelEdit, CreateChildView
Properties: IsEdit, IsNew, Item
From DataSet to Database (on e)
Intermediate: SqlDataAdapter Objects
Requirement:
[Link]
[Link]
[Link]
[Link]
[Link]
From DataSet to Database (two )
InsertCommand:
Initial
strSQL = "insert into CustTable(CustNo) values (@CustNo)"
[Link] =
mySqlConnection
[Link] = strSQL
[Link]("@CustNo",
[Link])
Execute
Dim theRow as DataRow
[Link]("@CustNo").Value
=
theRow("CustNo").ToString()
[Link]()
From DataSet to Database (thr ee)
DeleteCommand:
Initial
strSQL = "delete from CustTable where CustNo = @CustNo"
[Link] = mySqlConnection
[Link] = strSQL
[Link]("@CustNo",
[Link])
Execute
Dim theRow as DataRow
[Link]("@CustNo").Value =
theRow("CustNo",
[Link]).ToString()
[Link]()
From DataSet to Database (four )
UpdateCommand:
strSQL = "update CustTable set CustType = @CustType_New where
CustType =
@CustType"
[Link] = mySqlConnection
[Link] = strSQL
[Link]("@CustType_New",
[Link])
[Link]("@CustType",
[Link])
Execute
Dim theRow as DataRow
[Link]("@CustType_New").Valu
e= theRow("CustType",
[Link]).ToString()
[Link]("@CustType").Value =
theRow("CustType",
[Link]).ToString()
[Link]()
From DataSet to Database (fi ve)
Updated the Database. Client?
Only AcceptChanges()
XML in [Link]
strXML = [Link]()
The strXML is a string list table by table.
[Link](“C:\[Link]”, XMLWriteMode.
WriteSchema)
End & Thank you.
Author : Benjamin
Editor : Albert
