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
 ADO.NET 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
 myConnection.ConnectionString = “…”
 myConnection.Open()
 myConnection.Close ()

 Create a Transaction Object synchronously

( VB+ SQL Server )
 Dim mySqlTransaction as SqlTransaction
 mySqlTransaction = myConnections.BeginTransaction
 mySqlTransaction.Commit ()
 mySqlTransaction.Rollback ()
 Using the Connection Object (two)

 Create a Command Object synchronously

 Dim myCommand as SqlCommand
 myCommand = myConnection.CreateCommand

 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 = myConnection.CreateCommand
 myCommand.CommandText = “…”

 B) myCommand = SqlCommand.Create ()
 myCommand.Connection = myConnection
 myCommand.CommandText = “…”

 C) myCommand = SqlCommand.Create(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?
 myCommand.CommandText = “Update permis set…”
 myCommand.ExecuteNonQuery ()
 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
 myCommand.CommandText = “select * from PermisUser"”
 myDataReader = myCommand.ExecuteReader
 While myDataReader.Read()
 Response.Write (myDataReader(0), myDataReader(1))
 End While
 reader.Close()
 Using the Command Object (fi ve)

 Read a field of data in the DataReader

 Dim myDataReader as SqlDataReader
 myCommand.CommandText = "select emp_na from
PermisUser"
 myDataReader = myCommand.ExecuteReader()
 myDataReader.Read()
 strEmpName = myDataReader.Item( "emp_na" ).ToString()

 Special operation:
 Dim sCount as String
 myCommand.CommandText = "select count(*) from
PermisUser”
 sCount = myCommand.ExecuteScalar.ToString()
 Using the Command Object (si x)

 Operation with Parameters ( VB + SQL Server )

 myCommand.CommandText =
"select emp_na from CtpPermisUser where emp_no =
@Param1”
 myCommand.Parameters.Add("@Param1",
SqlDbType.VarChar)
 myCommand.Parameters(0).Value = "92110015”
 myDataReader = myCommand.ExecuteReader()
 myDataReader.Read()
 sResult = myDataReader.Item("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 )
 myCommand.CommandText = "spGetCM”
 myCommand.CommandType =
CommandType.StoredProcedure
 myCommand.Parameters.Add("@PlayDate",
SqlDbType.VarChar)
 myCommand.Parameters.Add("@PlayDate2",
SqlDbType.VarChar)
 myCommand.Parameters("@PlayDate").Value = "2004/01/12"
 myCommand.Parameters("@PlayDate2").Value = "2004/01/13"
 myDataReader = myCommand.ExecuteReader()
 myDataReader.Read()
 sResult = myDataReader.Item("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 )
 myCommand.CommandText = "spGetCM"
 myCommand.CommandType = CommandType.StoredProcedure
 myCommand.Parameters.Add("@Return_Value", SqlDbType.Int)
 myCommand.Parameters.Add("@PlayDate", SqlDbType.VarChar)
 myCommand.Parameters.Add("@PlayDate2", SqlDbType.VarChar)
 myCommand.Parameters.Direction = ParameterDirection.RetuenValue
 myCommand.Parameters("@PlayDate").Value = "2004/01/12"
 myCommand.Parameters("@PlayDate2").Value = "2004/01/13"
 myCommand.ExecuteNonQuery()
 strResult =
myCommand.Parameters("@Return_Value").Value.ToString()

 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 = SqlDataAdapter.Create(strSQLSelect,
strConnection)
 This is not a good idea. It will establish another connection.

 B) myDataAdapter = SqlDataAdapter.Create(strSQLSelect,
myConnection)

myCommand = SqlCommand.Create(strSQLSelect, myConnection)

 C) myDataAdapter = SqlDataAdapter.Create()
 myDataAdapter.SelectCommand = myCommand

 D) myDataAdapter = SqlDataAdapter.Create(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 ADO.NET
 Using the DataAdapter Object (fou r)

 How to use the Fill? ( VB + SQL Server )

Dim myDataSet as DataSet
myDataSet = DataSet.Create()
myDataAdapter = SqlDataAdapter.Create(strSQLSelect,
myConnection)
 A) myDataAdapter.Fill(myDataSet)

 B) myDataAdapter.TableMappings.Add("Table", "Customers")

 myDataAdapter.Fill(myDataSet)
 C) myDataAdapter.Fill(myDataSet, "myTableName")

 D) myDataAdapter.Fill(myDataSet, nStartRow, nRows,

"myTableName")
 nStartRow is zero-base.
 E) myDataAdapter.Fill(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.
 myDataAdapter.FillSchema(myDataSet, SchemaType.Source)

 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 = DataSet.Create()

 B) myDataSet = DataSet.Create("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 )

myDataAdapter.Fill(myDataSet, "myTableName")

 A) myDataSet.Tables(0).Rows(0)(1).ToString
 B) myDataSet.Tables("myTableName").Rows(0)("Emp_Na").ToString
 C) myDataSet.Tables.Item(0).Rows.Item(0).Item(1).ToString
 D)
myDataSet.Tables.Item("myTableName").Rows.Item(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 )

myDataAdapter.Fill(myDataSet, "myTableName")

 A)
myDataSet.Tables("myTableName").Columns(1).ColumnName.ToString
 B)
myDataSet.Tables("myTableName").Columns("Emp_Na").Ordinal.ToStri
ng
 C)
myDataSet.Tables("myTableName").Columns("Emp_Na").DataType.ToS
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) myAdapter.Fill(myDataSet, "TableName")
 B) myAdapter.FillSchema(myDataSet, SchemaType.Source,
"TableName")
 C) Dim myDataTable as DataTable
Dim myColumn as DataColumn
myDataTable = myDataSet.Tables.Add("TableName")
myColumn = myDataTable.Add("Emp_No")
myColumn.MaxLength = 10
 Create a DataTable with a string type of Emp_No field.
myColumn = myDataTable.Add("HowOld", TypeOf(Integer))
 Using the DataTable Object (two)

 Add Constraints: ( VB + SQL Server )

 AllowDBNull
 myColumn.AllowDBNull = False

 ReadOnly
 myColumn.ReadOnly = True

 Unique
 myColumn.Unique = True

 Column of MaxLength
 myColumn.MaxLength = 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) = myDataTable.Columns.Add("Emp_No",
TypeOf(Integer))
 aryColumn(1) =
myDataTable.Columns.Add("Emp_Name")
 myDataTable.PrimaryKey = 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 =
ForeignKeyConstraint.Create(myMasterCol, myDetailCol)
 myDataTable.Constraints.Add(myForeignKey)
 Using the DataTable Object (fi ve)

 Can I add a calculated field? ( VB + SQL Server )

 Dim strSubtotal as String

 strSubtotal = "AmountFieldName * CostFieldName"

 myDataTable.Columns.Add("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 = myDataTable.NewRow()
 myRow("Emp_No") = "92090020"
 myRow("Emp_Name") = "Tom"
 myDataTable.Rows.Add(myRow) // don"t forget
 Maybe you use the myDataTable.LoadDataRow(…)
 Using the DataTable Object (sev en )

 Edit:
Dim theRow as DataRow
 theRow = myDataTable.Rows.Find("92010002")
 If theRow Is Nothing then
 ShowMessage("Primary-Key Value not found.")
 Else
 theRow("Emp_Salary") = 33000
or
 …
 Else begin
Reference
 theRow.BeginEdit
Method:
 theRow("Emp_Salary") = 33000 CancelEdit()

 theRow.EndEdit 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.

 theRow.Delete()
or
 myDataTable.Remove(theRow)

or
 myDataTable.Clear()
 Using the DataTable Object (nine)

 Verify the state of the row – RowState property

 myDataSet.Tables("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 =
myDataTable.Rows("Emp_Name",
DataRowVersion.Current).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 = DataRelation.Create("RelationName",

myCustTable.Columns("CustID"),

myOrderTable.Columns("CustID"))
 myDataSet.Relations.Add(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
theCustRow.GetChildRows("RelationName") do
ShowMessage(theOrderRow("ProdName").ToString)

Get the Master from one Detail?

 theCustRow = theOrderRow.GetParentRow("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 = myDataTable.Rows.Find("myProdId")

or, the primary key is established by two columns.

 Dim aryKey as object()

 aryKey(0) = nSerialNo

 aryKey(1) = "myProdId"

 theRow = myDataTable.Rows.Find(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 = myDataTable.Select(strCondition)

 Sorting: Select()
 Var strSort : String
 strSort = "Cost Desc"
 theRows = myDataTable.Select(strCondition, strSort)
 Searching, Sorting, Filtering (three)

 Searching: Select() with DataViewRowState

 Searching changed rows inside the DataTable.

Dim enuDvrs as DataViewRowState

 enuDvrs = DataViewRowState.Added or
DataViewRowState.Deleted
 theRows = myDataTable.Select("", "", 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 = myDataAdapter.Fill(myDataSet,
"myTableName")
 myDataView = DataView.Create(myDataTable)
 Searching, Sorting, Filtering (si x)

 Retrieve rows from a DataView object:

Dim enuDvrs as DataViewRowState

 enuDvrs = DataViewRowState.Added or DataViewRowState.Deleted
 myDataView = DataView.Create(myDataTable,
"ProdNo like ""S%"" ",
"Cost Desc",
enuDvrs)
or
 myDataView = DataView.Create
 myDataView.Table = myDataTable
 myDataView.RowFilter = "ProdNo like ""S%"""
 myDataView.Sort = "Cost Desc"
 myDataView.RowStateFilter = enuDvrs
 Searching, Sorting, Filtering (sev en)

 Browsing a row in a DataView:

 Use the Count property.
Dim theRowView : DataRowView
 For nRowIndex = 0 to myDataView.Count - 1

theRowView = myDataView(nRowIndex)
ShowMessage(theRowView("theFieldName").ToString)
Next
or
 For nRowIndex = 0 to myDataView.Count - 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 DataTable.Rows
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 = myDataView.FindRows(mySearchingValue)
 If ( Length(aryRows) > 0 ) then …
 Editing with DataRowView

 Add a new row.

Dim theRowView as DataRowView
 theRowView = myDataView.AddNew()
 theRowView("ProdName") = "xxx"
 theRowView.EndEdit() // update the DataTable object

 Modify a row.
 theRowView.BeginEdit()
 theRowView("ProdName") = "yyy"
 theRowView.EndEdit()

 Delete a row.
 theRowView.Delete()
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:
 SqlDataAdapter.InsertCommand
 SqlDataAdapter.DeleteCommand
 SqlDataAdapter.UpdateCommand
 SqlCommand.Connection
 SqlCommand.Parameters
 From DataSet to Database (two )

 InsertCommand:
 Initial
strSQL = "insert into CustTable(CustNo) values (@CustNo)"
 myDataAdapter.InsertCommand.Connection =

mySqlConnection
 myDataAdapter.InsertCommand.CommandText = strSQL

 myDataAdapter.InsertCommand.Parameters.Add("@CustNo",

SqlDbType.VarChar)

 Execute
Dim theRow as DataRow
 myDataAdapter.InsertCommand.Parameters("@CustNo").Value

=
theRow("CustNo").ToString()
 myDataAdapter.InsertCommand.ExecuteNonQuery()
 From DataSet to Database (thr ee)

 DeleteCommand:
 Initial
strSQL = "delete from CustTable where CustNo = @CustNo"
 myDataAdapter.DeleteCommand.Connection = mySqlConnection
 myDataAdapter.DeleteCommand.CommandText = strSQL
 myDataAdapter.DeleteCommand.Parameters.Add("@CustNo",

SqlDbType.VarChar)

 Execute
Dim theRow as DataRow
 myDataAdapter.DeleteCommand.Parameters("@CustNo").Value =
theRow("CustNo",
DataRowVersion.Original).ToString()
 myDataAdapter.DeleteCommand.ExecuteNonQuery()
 From DataSet to Database (four )
 UpdateCommand:
strSQL = "update CustTable set CustType = @CustType_New where
CustType =
@CustType"
 myDataAdapter.UpdateCommand.Connection = mySqlConnection
 myDataAdapter.UpdateCommand.CommandText = strSQL
 myDataAdapter.UpdateCommand.Parameters.Add("@CustType_New",
SqlDbType.VarChar)
 myDataAdapter.UpdateCommand.Parameters.Add("@CustType",
SqlDbType.VarChar)

 Execute
Dim theRow as DataRow
 myDataAdapter.UpdateCommand.Parameters("@CustType_New").Valu
e= theRow("CustType",
DataRowVersion.Current).ToString()
 myDataAdapter.UpdateCommand.Parameters("@CustType").Value =
theRow("CustType",
DataRowVersion.Original).ToString()
 myDataAdapter.UpdateCommand.ExecuteNonQuery()
 From DataSet to Database (fi ve)

 Updated the Database. Client?

 Only AcceptChanges()
 XML in ADO.NET

 strXML = myDataSet.GetXml()
 The strXML is a string list table by table.

 myDataSet.WriteXML(“C:\myXMLFile.xml”, XMLWriteMode.
WriteSchema)
End & Thank you.
Author : Benjamin
Editor : Albert