ADO.NET in VB.NET

1
ADO.NET in VB.NET

• ITE 370

2
What is ADO.NET?

• An acronym for the .NET version of ActiveX Data
  Objects
• A .NET Framework Class Library
• A group of types that reside in System.Data
  namespace
• A technology used by client applications to work
  with stored data
• Supported data formats include
• relational databases, such as MS-Access,
  SQL-Server, and Oracle
• Spreadsheets and other file formats
• XML

3
Providers and Their Namespaces

• What is a provider?
• .NET data provider
• Software for accessing a specific source of data,
  such as SQL-Server
• Optimized classes for SQL-Server
• Namespace System.Data.SqlClient
• OLE DB client
• For MS-Access, Excel, and others
• Also has support for Oracle and SQL-Server
• Namespace System.Data.Oledb

4
How clients, .NET data providers DBMSs fit
together
SQL Server
SQL .NET Data Provider
MS Access
client
OLE DB .NET Data Provider
other DBMS
other .NET data providers
5
Types of ADO.NET objects

• Connection establishes links to data sources
• Command stores and executes commands (i.e.
  queries and stored procedures with parameters)
• DataReader provides sequential, read-only
  access to data
• DataAdapter creates and populates DataSets
• DataSet an in-memory cache for storing data in
  various formats
• DataTable stores a relationeg. the result of a
  query
• DataRelation defines a relationship between two
  DataTables
• DataRow stores a single row/record of a data
  table
• DataColumn represents schema in a column of a
  DataTable
• DataView -- customized view of a DataTable for
  sorting, filtering, searching, editing, and
  navigation.
• DataGrid tabular control for displaying/editing
  a data source

6
Working with .udl files to build connection
strings

• To create a UDL file
• 1.      Open Windows Explorer or My Computer.
• 2.      Select the folder in which you want to
  save the .UDL file.
• 3.      On the File menu, click NEW and then
  click Text Document.
• 4.      Right-click on the text file you created
  in step 3, then click Rename. Type the new file
  name using a .udl file extension. Press Enter.
  For example. Myudl.UDL
• 5.      You may get a warning, message box,
  explaining that changing file extensions may
  cause files to become unusable. This is fine,
  click OK.
•  
• Once you have created the UDL file, you must now
  configure it
• Double click on the UDL file to bring up the Data
  Link Properties Dialog Box.
• Click on the Providers tab and select the driver
  you wish to use.
• Click on the Connection tab and enter the
  connection properties, each driver will require
  different settings, so I will not go into much
  details. All drivers will require a user name and
  password.
• Click on the Test Connection button to verify
  your connection is working correctly. Change
  accordingly if you get an error.
• Select the advanced tab only if you require
  advanced settings.

Source K K Consulting, http//kandkconsulting.t
ripod.com/VB/Tutorials/udl_file_tutorial.htm
7
DataReader Class

• The DataReader
• Provides forward-only, read-only access to data
• Analogous to reading a sequential file
• Fast access to data
• Uses little memory
• Requires connection and command objects to use

8
DataReader Example

• Imports System.Data.Oledb
• Public Class ConnDb
• ' Declare constant to hold base connection
  string information
• ' for connecting to an MS-Access baseball
  database.
• Public Const ConnStr As String
  "ProviderMicrosoft.Jet.OLEDB.4.0" _
• "Persist Security InfoFalseData
  Source"
• End Class
• ' Declare data access objects
• Dim conTeamDb As OleDbConnection ' connects to
  baseball database
• Dim dbCommand As OleDbCommand ' executes
  database query for team
• Public rdrTeams As OleDbDataReader ' provides
  forward-only, read-only access to team relation
• Dim OpenDbDialog As New OpenFileDialog
• If the user selects a database in the open
  dialog box...
• If OpenDbDialog.ShowDialog() DialogResult.OK
  Then
• ' connect to the database
• conTeamDb New OleDbConnection(ConnDb.ConnStr
  OpenDbDialog.FileName)

9
DataSet w/Multiple Tables Providers
1) Fill(DataSet, TableX)
SQL Server
3) Return DataTable
SqlDataAdapter
DataSet








SelectCommand
2) Execute query
TableX
TableY
Oracle
OleDbDataAdapter
6) Return DataTable
SelectCommand
5) Execute query
4) Fill(DataSet, TableY)
10
DataAdapter Class

• Represents a set of data commands and a database
  connection that are used to fill the DataSet and
  update the data source
• Appears on the component tray
• __Command properties
• Select, Insert, Update, Delete
• Methods
• Fill, Update

11
DataAdapter __Command Properties

• Contain command objects to perform operations
  against the underlying database
• SelectCommand
• Populates a DataTable with the results of a query
• Uses SQL SELECT statement typically
• InsertCommand
• Inserts rows added to a DataTable into database
• Uses SQL INSERT statement

12
DataAdapter __Command Properties (cont.)

• UpdateCommand
• Writes changes made to a DataTable into the
  underlying database
• Uses SQL UPDATE statement
• DeleteCommand
• Makes deletions of rows in a DataTable permanent
  by deleting those rows from the database
• Uses SQL DELETE statement

13
DataAdapter Fill Method

• Adds or refreshes rows in the DataSet to match
  those in the data source using the DataSet name
• Creates a DataTable named "Table"

daEmployee.Fill(dsCompany, AllEmployees)
14
DataAdapter Update Method

• Used to persist changes to a DataTable (i.e. save
  them in the database)
• Calls the respective INSERT, UPDATE, or DELETE
  statements for each inserted, updated, or deleted
  row in the specified DataSet from a DataTable
  named "Table"

daEmployee.Update(dsEmployee)
15
DataGrid class

• The Windows Forms DataGrid control
• Provides a user interface to ADO.NET datasets
• displays tabular data
• allows for updates to the data source

16
Binding the DataGrid

• The DataGrid can receive data from any of the
  following data sources
• DataTable class
• DataView class
• DataSet class
• DataViewManager class
• dtgTeams.DataSourcefTeams.tblTeam
• or
• dtgTeams.SetDataBinding(dsTeams, _ Teams)

17
Current row and cell

• Users can navigate on the DataGrid
• CurrentRowIndex
• Holds zero-based integer for highlighted row
• CurrentCell property
• Gets or sets which cell has the focus
• Has ColumnNumber and RowNumber properties
• Item collection holds cell content
• display cell contents of third column
• Console.WriteLine(
• dtgTeam.Item(dtgTeam.CurrentRowIndex, 2))

18
DataGrid properties

• CaptionText defines a title on top bar
• ReadOnly true means no edits in grid
• AllowSorting users can toggle asc/desc sorts of
  data for each column

19
Hiding a Column in DataGrid

• Sometimes, you will want to store a column in the
  DataGrid that you dont want users to see (eg.
  Primary key field)
• The following code hides a column of the
  DataGrid

' Declare a new DataGridTableStyle in the
declarations area of your form. Dim TableStyle As
DataGridTableStyle New DataGridTableStyle Sub
HideColumn(ByVal TableName As String, ByVal
ColumnName As String) ' Set the
DataGridTableStyle.MappingName property '
to the table in the data source to map to.
TableStyle.MappingName dtgPlayers.DataMember
' Add it to the datagrid's TableStyles
collection dtgPlayers.TableStyles.Add(Tabl
eStyle) ' Hide the column with
ColumnName by setting its width to zero
dtgPlayers.TableStyles(TableName).GridColumnStyles
(ColumnName).Width 0 End Sub