Transcript document

ADO.NET in VB.NET 2005
ITE 370
4/12/2016
1
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:



4/12/2016
relational databases (MS-Access, SQL-Server, and Oracle)
Spreadsheets and other file formats
XML
2
Providers and Their Namespaces

What is a “provider”?



Optimized classes for SQL-Server


.NET data provider
Software for accessing a specific source of data, such as SQLServer
Namespace: System.Data.SqlClient
OLE DB client



4/12/2016
For MS-Access, Excel, and others
Also has support for Oracle and SQL-Server
Namespace: System.Data.Oledb
3
How clients, .NET data providers
&DBMSs fit together
client
4/12/2016
SQL
.NET Data
Provider
SQL
Server
OLE DB
.NET Data
Provider
MS
Access
other
.NET data
providers
other
DBMS
4
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
 TableAdapter – pulls data from one or more database tables and
passes it to your program
 DataSet – an in-memory copy of data pulled from the database
tables (by the Table Adapter)
 DataTable – stores a relation—eg. 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 5
4/12/2016

Working with .udl files to build
connection
strings
To create a UDL file:
1.
2.
3.
4.
5.
Open Windows Explorer or My Computer.
Select the folder in which you want to save the .UDL file.
On the File menu, click NEW and then click Text Document.
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
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:
1.
2.
3.
4.
5.
4/12/2016
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.tripod.com/VB/Tutorials/udl_file_tutorial.htm
6
Create Data Source String for
Connection Object
Example of how to create the Data Source String
4/12/2016
7
DataReader Class

The DataReader






4/12/2016
Provides forward-only, read-only access to data
Analogous to reading a sequential file
Read from beginning of file in order to the end of 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 (connection string from UDL file).
Public Const ConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Persist Security Info=False;Data Source=“
‘* notice we removed name of file
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
'* 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)
conTeamDb.Open()
'* query the team table, assigning result to a data reader
dbCommand = New OleDbCommand("SELECT Location, Nickname, Stadium, League FROM Team",
conTeamDb)
rdrTeams = dbCommand.ExecuteReader
If rdrTeams.Read() '* success reading next team
Console.WriteLine(rdrTeams.Item("Location") & " " & rdrTeams("Nickname"))
End if
rdrTeams.Close()
conTeamDb.Close()
End If
4/12/2016
9
View Sample Project Using Data
Reader



Create New VB.Net Application
Add ListTeams.VB
Project Properties  StartUP



Sub Main ( )
Run Sample Application Using Data Reader
Attempt to Add a Form and Have the Data Display
within the Form instead of to the Console
4/12/2016
10
Using Table Adapters / Data Set

Table Adapter – Retrieves data from the data source and updates
the data in the data source

Data Connection – Connecting to the Data Source


Data Set – In memory copy of records / data tables





Built into the Table Adapter Object
Separate from the data source
Data tables have columns and rows
Allows you to work with the data from the data source
Changes are not permanent in external data source unless directed by the
Table Adapter
Data is transferred from the data source to the data set and viceversa through the Table Adapter
4/12/2016
11
DataSet w/Multiple Tables &
Providers
1) Fill(DataSet, “TableX”)
3) Return DataTable
SQL
Server
SqlTableAdapter
DataSet
SelectCommand
TableX
2) Execute
query
TableY
OleDbTableAdapter
Oracle
6) Return DataTable
SelectCommand
4) Fill(DataSet, “TableY”)
4/12/2016
5) Execute
query
12
TableAdapter Class


Provides communication between the application and
database
Represents a set of data commands and a database
connection that are used to fill the DataSet and update
the data source based on queries


Can contain multiple queries – called like methods
Created with




4/12/2016
Code
Dataset Designer
During Creation of a New Dataset – Data Source Configuration Wizard
Server Explorer / Dataset Designer
13
TableAdapter Class

__Command properties (Created through Queries):


Select Query – Used in Fill Method
Insert Command, Update Command, Delete Command


Can be Created by default if enough information is available in the
Select query (Fill Method)
Methods:




4/12/2016
Fill – populated the Data Table with the result of the select command
Update – Sends changes back to the database
Insert – creates a new row in the data table
ClearBeforeFill – Clears the data table before executing a fill method
14
TableAdapter: __Command
Properties

Contain command objects to perform operations
against the underlying database

Select Command



Insert Command


4/12/2016
Populates a DataTable with the results of a query
Uses SQL SELECT statement typically
Inserts rows added to a DataTable into database
Uses SQL INSERT statement
15
TableAdapter: __Command
Properties (cont.)

Update



Delete Command


4/12/2016
Writes changes made to a DataTable into the underlying database
Uses SQL UPDATE statement
Makes deletions of rows in a DataTable permanent by deleting those
rows from the database
Uses SQL DELETE statement
16
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"
taEmployee.Fill(dsCompany.AllEmployees)
Table Adapter
4/12/2016
Method
Data Set
Name for
Data Table
17
TableAdapter: 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"
taEmployee.UpdateCommand(dsEmployee)
4/12/2016
18
Table Adapter – Through IDE

Server Explorer


Click on Connect to DB Icon
Add Connection




Provide Data Source Type (Access, SQL Server)
Provide Database File
Test the Connection
Add DataSet


Data Menu  Add New Data Source
Project  Add New Item  DataSet


4/12/2016
Select Items from Open Connection in Server Explorer
Creates the Data Set and Table Adapter
19
Binding Source



Data Aware Controls
Controls that can automatically display data from a
table cell in the DataSet (most VB.net controls are data
aware)
Controls must be Bound to the DataSet




4/12/2016
DataBindings Property of Control
Arrow next to Text Field (of DataBindings)
Add or Select Data Source
Complete Data Source Configuration Wizard
20
Currency Manager

Object that gives a way of navigating the rows of the
DataTable. One row of the DataTable is always the
current row




Data Aware controls display the data from the current row
Currency Manager objects are included in
BindingSource components
Position Property  Number of the Current Row
(zero based)
Count Property  How many rows are in the table
4/12/2016
21
Sample Code

Create a Visual Basic 2005 Application that will access
the Baseball3 database and display the player
information in bound textboxes. The program should
use a Table Adapter, Data Set , Binding Source and
Currency Manager to manipulate through the table
data.
4/12/2016
22
Visual Studio.Net Wisdom

Update Command
TableAdapter.Update (Dataset.DataTable)
taEmployee.Fill(dsCompany.AllEmployees)



Updates the database
Updates the “local” copy in the Bin directory
Copied over by Visual Studio the next time the program is
run


4/12/2016
Don’t want you to corrupt your databases
How to accomplish a True Update?????
23
Update Command


Cannot use the IDE to develop your application
Must create the code by hand (Within Class Code)







Connection Object
DataAdapter Object
DataSet Object
DataTable Object
BindingSource Object
CommandBuilder Object
Import System.Data.OleDb (will be System.Data.SQL
for SqlServer)
4/12/2016
24
Connection Object


Create the connection to the data source
Uses the Connection String Information
Dim myConnection as New OleDb.Connection (connectionString)
4/12/2016
25
Data Adapter Object

Table Adapter is Not an Option Here, we must use a
Data Adapter
Dim myDataAdapter as New OleDbDataAdapter (“sqlCommand”,
connectionObject)
4/12/2016
26
Data Set Object

Create the Data Set
Dim myDataSet as New DataSet (“DataSetName”)

Name of DataSet is a String, it must be in Quotation
Marks
4/12/2016
27
Data Table Object


Create the Data Table
Will Store the Data for the Data Set in Table form
Dim myDataTable as New DataTable (“DataTableName”)

Data Table Name is a string, it must be in Quotation
Marks
4/12/2016
28
Binding Source

You must create a Binding Source Object to bind your
data controls to the DataSet
Dim myBindingSource as New BindingSource
4/12/2016
29
Command Builder


Fill Command is automatically created for the Data
Adapter Object
In an OleDb Connection a Command Builder object is
needed to create the additional commands including
Update
Dim myBuilder as New OldDbCommandBuilder (NameOfDataAdapterObject)
4/12/2016
30
Sample Code
4/12/2016
31
Form Load Event

Open the Connection


Add the DataTable to the DataSet


dataSetObject.Tables.Add(dataTableObject)
Fill the DataSet through the DataAdapter


connectionObject.Open( )
dataAdapterObject.Fill(dataSetObject, “StringName of
DataTable”)
Bind the DataTable to the BindingSource

4/12/2016
bindingSourceObject.DataSource = dataTableObject
32
Form Load Events (Con’t)


Bind the Controls to the DataTable
Allows Bound Controls to Display Data from
DataTable
controlName.DataBindings.Add (“text”, bindingSourceObject,
“FieldName”)


“text” must be typed exactly as seen
“FieldName” is the name of the column from the Data
Table – typically the name of the field in the Database
(unless an alias is created by the SQL command)
4/12/2016
33
Sample Code
4/12/2016
34
BindingSource Object








BindingSource Object can manipulate through the
DataTable
CurrencyManager is NOT Needed
BindingSource.Position
BindingSource.Count
BindingSource.MoveNext( )
BindingSource.MovePrevious( )
BindingSource.MoveFirst( )
BindingSource.MoveLast( )
4/12/2016
35
Finalizing Update

EndEdit on the BindingSource

Stops the Editing through the Binding Source
bindingSourceObject.EndEdit ( )

Update the DataAdapter

Send Changes back to Original Data Source
dataAdapterObject.Update (dataSetObject, “stringNameOfDataTable”)

Close the Connection
connectionObject.Close ( )
4/12/2016
36
DataGridView class

The Windows
Forms
DataGridView
control



4/12/2016
Provides a user
interface to
ADO.NET
datasets
displays tabular
data
allows for updates
to the data source
37
Creating DataGridView Program






Connection Object
DataAdapter Object
DataSet Object
DataTable Object
CommandBuilder Object
No BindingSource object needed – Property of
DataGridView
4/12/2016
38
Sample Code
4/12/2016
39
Binding the DataGrid

The DataGrid can receive data from any of the
following data sources:




DataTable class
DataView class
DataSet class
DataViewManager class
dtgObject.DataSource=dataTable
dtgPlayer.DataSource = myPlayerTable
4/12/2016
40
Binding the DataGridView
4/12/2016
41
Current row and cell


Users can navigate on the DataGridViewer
CurrentRow.Index


Holds zero-based integer for highlighted row
CurrentCell property


Gets or sets which cell has the focus
Contains DataGridViewCell object


Has ColumnNumber and RowNumber properties
Item property holds cell content
‘* display cell contents of third column
Console.WriteLine(dtgTeam.Item(dtgTeam.CurrentRowIndex,
2))
4/12/2016
42
Data in DataGridViewer

Read Only
dtgObject.ReadOnly = True

Storing a column in the DataGridView that you don’t
want users to see (eg. Primary key field)
dtgObject.Columns(index).Visible = False
4/12/2016
43
Data Grid Example - Homework


Use Baseball Database (Baseball3.mdb)
DataGridView Example




Create Form with DataGridView Object
Create Needed Objects (in Code)
Hidden Column (Primary Key)
TeamID Column – Read Only

Update Database on Exit Button

Bonus: User OpenFileDialog box to Select Database
4/12/2016
44