Introduction To ADO'NET

1

• Introduction To ADO.NET

2
Objectives

• Learn about fundamental database concepts
• Understand the OleDbConnection class
• Work with the OleDbDataAdapter class
• Learn about the DataSet class
• Modify data in a DataTable
• Use input validation
• Learn about SQL query parameters
• Record DataSet changes to the database

3
Fundamental Database Concepts (1)

• Traditional file-processing system
• Separate data files typically exist for each
  application
• Database
• Contains a set of information related to a
  particular topic or purpose
• May also store all of the data for a particular
  organization

4
Fundamental Database Concepts (2)

• Databases contain one or more tables
• Tables consist of rows and columns
• Columns define the name and the
• characteristics of the stored information
• Columns in a table are called fields
• Rows in a table are called records

5
Proper Database Design

• Tables in a database should be organized to
  eliminate data redundancy
• Process of eliminating data redundancy is called
  normalization
• Each row (record) in a table should have a unique
  index, enabling a record to be located directly
• The unique index for a table is referred to as
  the primary key

6
Introducing ADO.NET (1)

• Two modes of ADO.NET
• Connected mode
• The developer establishes a connection to a
  database
• Commands are then sent over that connection
• Data is returned from the database into another
  object called a DataReader

7
Introducing ADO.NET (1)

• Disconnected mode
• The disconnected DataSet object allows a
  developer to retrieve data from a database
• Work with data offline
• Establish a new connection over which changes are
  sent back to the database

8
ADO.NET Namespaces and Classes (1)

• The System.Data namespace contains the other
  namespaces and classes used to manage data for
  several types of databases
• Jet, SQL Server, Oracle
• The System.Data.Common namespace contains
  additional classes shared by both Jet and SQL
  Server databases
• The System.Data.OleDb namespace contains classes
  to send and receive data between an application
  and the database using a generic provider

9
ADO.NET Namespaces and Classes (2)

• The System.Data.SqlClient namespace works only
  with SQL Server databases
• The System.Data.SqlTypes namespace contains
  classes that map native SQL Server data types to
  .NET Framework data types
• The System.XML class contains classes used to
  read, write, and parse XML data
• Not explicitly part of ADO.NET

10
Creating Database Connections

• Three ways to create database connections
• The Data Form Wizard will create a form to
  establish a connection, display data in control
  instances appearing on the form, and update that
  data
• You can use controls appearing on the Data tab of
  the Toolbox to establish a database connection
  and to update database data
• You can also create all of the necessary ADO.NET
  objects programmatically

11
.NET Framework Classes Related to ADO.NET

• The OleDbConnection class of the
  System.Data.OleDb namespace establishes a
  database connection over which commands are sent
  to retrieve and update data
• The OleDbDataAdapter class of the
  System.Data.OleDb namespace sends SQL statements
  over an OleDbConnection
• Loads any data returned into another object
  called a DataSet
• The DataSet class of the System.Data namespace
  stores an in-memory representation of data
  returned through the OleDbDataAdapter
• The DataTable corresponds to a database table
  appearing in a DataSet

12
OleDbConnection Class(Introduction)

• A connection is a pipeline between a client
  application and ADO.NET
• Use in both connected and disconnected mode
• Provider receives requests to retrieve or update
  data

13
OleDbConnection Class(Properties)

• The ConnectionString property contains a String
  that the OleDbConnection class uses to establish
  the database connection
• String consists of key/value pairs
• The ConnectionTimeout property contains an
  Integer that defines how long the provider will
  wait to establish a connection
• Default value is 15 seconds
• The State property gets the current state of the
  connection
• Open or closed

14
OleDbConnection Class (Methods)

• Close method closes an open connection
• Open method reads the contents of the
  ConnectionString property, sends a request to a
  provider, and then opens a connection
• Some classes open connections automatically
• BeginTransaction method starts a database
  transaction
• ChangeDatabase method operates on an open
  connection and changes the database for that
  connection

15
OleDbConnection Class (Events)

• The StateChanged event fires when the connection
  opens or closes
• Second argument of the StateChanged event is of
  type StateChangedEventArgs
• Its properties contain the current and previous
  connection state

16
OleDbConnection Class (Illustration)
17
Creating a Connection Programmatically

• First, create an instance of the OleDbConnection
  class
• Second, configure the ConnectionString property
• ConnectionString property contains a list of
  key/value pairs
• An equals sign separates each key from its
  corresponding value
• A semicolon separates each key/value pair

18
Creating an OleDbConnection (Example 1)

• Example
• Connect to the database named C\Rolodex.mdb
  using a Jet provider
• Private mconCurrent As New OleDbConnection()
• mconCurrent.ConnectionString _
• "ProviderMicrosoft.Jet.OLEDB.4.0" _
• "Password""""User IDAdmin" _
• "Data SourceC\Rolodex.mdb"

19
Creating an OleDbConnection (Example 2)

• The first of the preceding statements creates an
  instance of the OleDbConnection class named
  mconCurrent
• The second statement configures the
  ConnectionString property by defining the
  key/value pairs
• First key defines the provider that the
  connection will use
• Second and third keys are Password and User ID
• Fourth key is Data Source

20
Sending Requests Over a Connection

• ADO.NET supplies two means to retrieve data over
  a connection
• Using the DataReader class, data is read through
  an input stream
• Similar to reading data with the StreamReader
  class
• The OleDbDataAdapter class retrieves data from
  one or more database tables into a DataSet
• The DataSet contains an in-memory representation
  of the data contained in database tables

21
OleDbDataAdapter Class (Introduction)

• The OleDbDataAdapter works in conjunction with
  the DataSet class to read and write data
• Commands are sent over a connection
• Data is retrieved into a DataSet
• Commands to insert, update, or delete data return
  nothing

22
OleDbDataAdapter (Properties)

• SelectCommand property stores SQL SELECT
  statement
• InsertCommand property stores SQL INSERT
  statement that will insert a row into a database
  table
• UpdateCommand property stores a SQL UPDATE
  statement to update the contents of an existing
  row
• DeleteCommand property stores a SQL DELETE
  statement that will remove a row or rows

23
OleDbDataAdapter (Methods)

• The Fill method uses an OleDbConnection to
  retrieve database records
• Records are loaded into a DataSet
• The Update method examines the DataSet for any
  added, changed, or deleted records
• Changes are recorded to DataSet
• Call AcceptChanges method on DataSet to
  synchronize DataSet and database

24
OleDbDataAdapter (Events)

• FillError event fires if an error occurs while
  populating the DataSet
• RowUpdating event fires just before row is
  updated
• RowUpdated event fires just after a row in the
  data source is updated

25
OleDbDataAdapter Class
26
OleDbCommand Class

• Stores SQL statements
• These SQL statements are used by OleDbDataAdapter
  to select and update rows
• OleDbDataAdapter executes command automatically

27
OleDbCommand Class (Properties)

• CommandText property stores SQL statement that
  will be executed against a provider
• Connection property stores a reference to
  existing OleDbConnection object
• Parameters property stores a reference to a
  Parameters collection
• For each parameter in the Parameters collection,
  the OleDbDataAdapter dynamically fills in the
  data that is used to add, change, or delete a
  database record

28
OleDbCommand Class (Methods)

• ExecuteNonQuery method executes a SQL statement
• Statement does not return database rows
• ExecuteReader method creates a DataReader
• ExecuteScalar method executes a SELECT statement
• Method returns the first column of the first row
  retrieved by the SELECT statement
• Additional data is discarded and no exception
  will occur

29
OleDbCommand Class Example

• Example
• Private modbcmdSelect As New OleDbCommand()
• modbcmdSelect.CommandText "SELECT FROM
  tblNames"
• modbcmdSelect.Connection modbconCurrent
• First statement creates an instance of the
  OleDbCommand class
• Second statement stores the SQL SELECT statement
  in CommandText property
• Finally, the Connection property is set to
  modbconCurrent (an existing connection)

30
SQL SELECT Statement (1)

• The SQL SELECT statement selects one or more rows
  (records) from a database table or query
• Syntax
• SELECT fieldlist FROM tablename WHERE
  conditionlist ORDER BY fieldlist

31
SQL SELECT Statement (2)

• Syntax dissection
• fieldlist argument specifies the field(s) to
  select
• Commas separate field names
• tablename argument contains database table name
• WHERE conditionlist argument contains a condition
  used to restrict the rows selected
• Resembles the condition in an If statement
• ORDER BY fieldlist argument contains a
  comma-separated list to sort table data
• Data may be sorted in ascending or descending
  order

32
SQL Select Example

• Example
• SELECT FROM tblNames ORDER BY fldLastName
• SELECT FROM tblNames ORDER BY fldLastName,
  fldFirstName
• SELECT fldFirstName, fldLastName FROM tblNames

33
Table Mappings

• When populating a DataSet, the OleDbDataAdapter
  uses a default table name
• Use the DataTableMapping class to supply
  meaningful names
• The DataTableMapping class supports the following
  properties
• ColumnMappings property contains a reference to a
  DataColumnMappingCollection
• DataSetTable property contains a String
• String defines the new DataSet table name
• SourceTable property contains source (default)
  table name

34
Table Mappings (Example)

• Create a DataTableMapping changing default table
  name "Table" to "tblNames"

Dim dtmtblNames As DataTableMapping dtmtblNames
modbdaCurrent.TableMappings.Add( _ "Table",
"tblNames")
35
Filling the OleDbDataAdapter

• Once the OleDbDataAdapter has been configured it
  can be used to populate a DataSet
• Call the Fill method to populate the DataSet
• Syntax
• Overrides Overloads Public Function Fill(ByVal
  DataSet As DataSet) As Integer
• Overloads Public Function Fill(ByVal DataSet As
  DataSet, ByVal SrcTable As String) As Integer

36
OleDbDataAdapter(Fill Method Syntax)

• Syntax dissection
• First overloaded method accepts one argument
• The DataSet to fill
• Second overloaded method accepts a second
  argument containing the name of a particular
  table to fill

37
OleDbDataAdapter(Fill Method Example)

• Example
• Dim modbdaCurrent As New OleDbDataAdapter()
• Dim dsCurrent As New DataSet()
• ' Statements to configure the DataAdapter
• modbdaCurrent.Fill(dsCurrent)
• modbdaCurrent.Fill(dsCurrent, "tblNames")

38
Filling the OleDbDataAdapter (1)
39
Introduction to the DataSet Class

• The DataSet object represents a disconnected view
  of the data stored in one or more database tables
• Capabilities
• Sort the data appearing in a DataSet based on the
  contents of one or more columns
• Search through the rows in a DataSet searching
  for a particular row based on specific criteria
• The DataSet is designed to store and manage
  hierarchical data

40
DataSet Class (Types)

• DataSets are either typed or untyped
• Untyped DataSet
• Reference tables and fields through their
  corresponding collections
• Typed DataSet
• Typed DataSets derived from the
  System.Data.DataSet class
• Contains additional properties and methods that
  allow references to tables and fields directly by
  name

41
DataSet Properties

• The Boolean CaseSensitive property controls
  whether String comparisons are made in a case
  sensitive or case insensitive way
• The DataSetName property defines the name of the
  DataSet
• The EnforceConstraints property defines whether
  ADO.NET checks constraint rules when adding,
  changing, or deleting data
• The Tables property returns the collection of
  DataTable objects in the DataSet

42
DataSet Methods (1)

• The AcceptChanges method causes any changed
  records to be marked as unchanged records
• Use to synchronize database with the DataSet
• The Clear method removes all rows from all tables
  in the DataSet
• The Clone method makes a copy of the DataSet
  schema (structure)
• Data is not copied
• The Copy method copies both the schema and the
  data from a DataSet
• Creates an exact copy of the original DataSet

43
DataSet Methods (2)

• The GetChanges method (overloaded) examines an
  existing DataSet and returns a new DataSet
  containing only the changed records
• Returns Nothing if DataSet does not contain
  changes
• The HasChanges method returns a Boolean value
  indicating whether the DataSet has pending
  changes
• The RejectChanges method causes any pending
  changes to be cancelled, restoring any data to
  their original values

44
DataTable Class (Introduction)

• A DataSet object typically contains one or more
  DataTable objects
• A DataTable object contains one or more
  DataColumn objects representing each column

45
DataTable (Properties)

• CaseSensitive property of the DataTable has the
  same value as the CaseSensitive property of the
  parent DataSet
• Columns property contains a reference to a
  DataColumnCollection
• Use to reference each column
• HasErrors property (Boolean) is set to True if
  any of the rows contain errors
• PrimaryKey property contains an array of
  DataColumn objects
• Rows property is a collection having a data type
  of DataRowCollection

46
DataTable (Method)

• The NewRow method creates a new DataRow object
• Method uses schema information obtained from the
  DataTable itself
• Calling the NewRow method creates a new DataRow
  object
• DataRow contains the same number of DataColumn
  objects as exist in the underlying DataTable

47
DataTable (Events)

• ColumnChanging event fires just before a new
  value is stored in a particular column
• ColumnChanged event fires after a value is stored
  in a column
• ColumnChanged event fires after the
  ColumnChanging event
• RowChanging event fires before a new value is
  stored in a row
• RowChanged event fires after a new value has been
  stored in a row

48
DataSet, DataTable, DataColumn Relationships
49
DataTable (Example)

• Reference a DataTable using a numeric index and
  string key
• Private dtNames As DataTable
• dtNames dsCurrent.Tables(0)
• dtNames dsCurrent.Tables("tblNames")

50
DataRowCollection

• Contains the collection of rows in the DataTable
• Properties
• Count property gets the number of rows in the
  collection (records in the DataTable)
• Count property is 1-based
• Item property returns a row having the specified
  index
• Index is 0-based

51
DataRowCollection Methods

• Add method adds a new DataRow to the end of the
  Rows collection
• Clear method removes all of the DataRow objects
  from the Rows collection
• Removes all of the rows from the underlying
  DataTable
• Find method locates a single DataRow in the Rows
  collection
• Remove method removes a DataRow in the Rows
  collection
• RemoveAt method has the same purpose as the
  Remove method
• Accepts the numeric index of the row to remove as
  its one argument

52
DataTable and Rows Relationship
53
DataRow Class

• Each row in the Rows collection has a data type
  of DataRow
• Item property gets or sets the value for a
  particular column in a DataRow
• Item property accepts one argument
• RowState property indicates whether the DataRow
  is a new row, a deleted row, or a row whose
  contents have been modified
• Delete method of the DataRow class marks the row
  for deletion
• Row is not deleted until the AcceptChanges method
  of the DataSet or DataTable is called

54
Navigating the DataTable
55
Creating a Primary Key

• Using a primary key to navigate through the
  records in a DataTable is a two-step process
• First, define the column(s) that will comprise
  the primary key
• Second, search for a record based on that key
• Example
• Dim dcKeyfldID() As DataColumn _
• dtNames.Columns("fldID")
• dtNames.PrimaryKey dcKeyfldID

56
Modifying Data in a DataTable

• Modifying the records in a DataSet and its
  DataTable(s) and recording those changes back to
  the database is a two-step process
• First, add, change, and delete records in the
  DataTable
• Second, record those changes back to the database
  using the Update method of the OleDbDataAdapter

57
Adding a Row to a DataTable

• Create a new DataRow object by calling the NewRow
  method of the DataTable class
• The NewRow method creates a new DataRow object
  with the same schema as the underlying DataTable
• Call the Add method of the Rows collection to add
  the row to the DataTable
• Example
• Dim drCurrent As DataRow
• drCurrent dtNames.NewRow
• drCurrent.Item("fldID") txtID.Text
• ' Statements to copy other fields
• dtNames.Rows.Add(drCurrent)

58
Modifying a DataRow

• Using an index to the Rows collection, reference
  a field within that row
• Example
• Reference the field named fldID in the first row
  of the DataTable named dtNames
• dtNames.Rows(0).Item("fldID") txtID.Text

59
Deleting a Row from a DataTable

• To delete a row from a DataTable, call the Delete
  method of the DataRow class
• Example 1
• dtNames.Rows(0).Delete
• Example 2
• Dim drCurrent As DataRow
• drCurrent dtNames.Rows(0)
• drCurrent.Delete()

60
Input Validation (1)

• The DataTable class supports events that allow
  you to perform input validation
• RowChanging event fires just before recording a
  changed or newly added row to the DataTable
• RowChanged event fires after the row has been
  changed

61
Input Validation (2)

• ColumnChanging event fires just before changes
  are made to a particular column
• ColumnChanged event fires after the column has
  been changed
• RowDeleting event fires just before a row is
  deleted
• RowDeleted event fires after the row has been
  deleted

62
ColumnChanging Event

• ColumnChanging event fires for each column just
  before the columns value is changed
• The data type of the ColumnChanging events
  second argument is DataColumnChangeEventArgs
• This object supports the following properties
• Column property is of type DataColumn
• ProposedValue property contains the new value
  that will be stored in the column
• Row property contains the underlying DataRow
  object containing the column

63
SQL Query Parameters

• Use parameterized queries to select specific
  records
• Conceptually similar to a procedure accepting one
  or more arguments
• Syntax of parameters varies from provider to
  provider

64
OleDbParameter Class (Properties)

• Properties
• OleDbType property maps a database type to its
  corresponding .NET Framework type
• Size property defines the number of bytes
  required to store the data
• Size is inferred for numeric types
• Set explicit size for String types
• Value property stores the current value of the
  parameter

65
OleDbParameter Class (Constructor)

• Public Sub New (name As String, datatype As
  OleDbType, size As Integer, srccolumn As String)
• name argument contains the name of the parameter
• datatype argument contains a value that defines
  the data type of the argument
• size argument contains the maximum size of the
  data
• srccolumn argument contains the name of the
  column in the underlying DataTable

66
OleDbParameter Class (Example)

• Example
• odbcmdSelect.CommandText "SELECT FROM
  tblNames" _
• " WHERE fldID ? "
• Dim odbParam As New OleDbParameter("fldID", _
• OleDbType.Integer, 0, "fldID")
• odbcmdSelect.Parameters.Add(odbParam)
• odbcmdSelect.Parameters("fldID").Value 2
• odbcmdSelect.Connection mconCurrent
• odbdaCurrent.SelectCommand odbcmdSelect
• dsCurrent.Clear()
• odbdaCurrent.Fill(dsCurrent)

67
OleDbParameter Class (Illustration)
68
Recording DataSetChanges (1)

• Explicitly record any changes back to the
  database using the OleDbDataAdapter
• The following members are used to record changes
  back to the database
• The Update method of the OleDbDataAdapter records
  the changes made to the DataTable(s) in a DataSet
• The RowState property marks whether a particular
  DataRow was added, changed, or deleted

69
Recording DataSetChanges (2)

• InsertCommand, UpdateCommand, and DeleteCommand
  properties of the OleDbDataAdapter contain SQL
  statements used to add, change, and delete
  records, respectively
• OleDbDataAdapter sends the appropriate
  parameterized SQL statements, over the
  OleDbConnection, as necessary

70
Recording DataSetChanges (Illustration)
71
Using theOleDbDataAdapter (Example)

• Example
• Dim dsUpdates As DataSet
• Try
• dsUpdates dsCurrent.GetChanges()
• If Not (dsUpdates Is Nothing) Then
• odbdaCurrent.Update(dsUpdates)
• End If
• Catch ex As System.Exception
• Debug.WriteLine(ex.Message)
• End Try

72
Building a Parameterized Query