Transcript ADO.NET in VB.NET ITE 370 ITE 370: ADO.NET in VB.NET

ITE 370: ADO.NET in VB.NET
ADO.NET in VB.NET
ITE 370
University of South Alabama School of CIS
Last Modified: 5/24/2016
1
ITE 370: ADO.NET in VB.NET
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
University of South Alabama School of CIS
Last Modified: 5/24/2016
2
ITE 370: ADO.NET in VB.NET
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
University of South Alabama School of CIS
Last Modified: 5/24/2016
3
ITE 370: ADO.NET in VB.NET
How clients, .NET data providers &DBMSs fit together
client
SQL
.NET Data
Provider
SQL
Server
OLE DB
.NET Data
Provider
MS
Access
other
.NET data
providers
other
DBMS
University of South Alabama School of CIS
Last Modified: 5/24/2016
4
ITE 370: ADO.NET in VB.NET
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 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
University of South Alabama School of CIS
Last Modified: 5/24/2016
5
ITE 370: ADO.NET in VB.NET
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.
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
University of South Alabama School of CIS
Last Modified: 5/24/2016
6
ITE 370: ADO.NET in VB.NET
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
University of South Alabama School of CIS
Last Modified: 5/24/2016
7
ITE 370: ADO.NET in VB.NET
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 = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Persist Security Info=False;Data 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)
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
University of South Alabama School of CIS
Last Modified: 5/24/2016
8
ITE 370: ADO.NET in VB.NET
DataSet w/Multiple Tables & Providers
1) Fill(DataSet, “TableX”)
3) Return DataTable
SQL
Server
SqlDataAdapter
DataSet
SelectCommand
TableX
2) Execute
query
TableY
OleDbDataAdapter
Oracle
6) Return DataTable
SelectCommand
4) Fill(DataSet, “TableY”)
University of South Alabama School of CIS
Last Modified: 5/24/2016
5) Execute
query
9
ITE 370: ADO.NET in VB.NET
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
University of South Alabama School of CIS
Last Modified: 5/24/2016
10
ITE 370: ADO.NET in VB.NET
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
University of South Alabama School of CIS
Last Modified: 5/24/2016
11
ITE 370: ADO.NET in VB.NET
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
University of South Alabama School of CIS
Last Modified: 5/24/2016
12
ITE 370: ADO.NET in VB.NET
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”)
University of South Alabama School of CIS
Last Modified: 5/24/2016
13
ITE 370: ADO.NET in VB.NET
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)
University of South Alabama School of CIS
Last Modified: 5/24/2016
14
ITE 370: ADO.NET in VB.NET
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
University of South Alabama School of CIS
Last Modified: 5/24/2016
15
ITE 370: ADO.NET in VB.NET
Binding the DataGrid
• The DataGrid can receive data from any of the
following data sources:
– DataTable class
– DataView class
– DataSet class
– DataViewManager class
dtgTeams.DataSource=fTeams.tblTeam
or
dtgTeams.SetDataBinding(dsTeams, _
“Teams”)
University of South Alabama School of CIS
Last Modified: 5/24/2016
16
ITE 370: ADO.NET in VB.NET
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))
University of South Alabama School of CIS
Last Modified: 5/24/2016
17
ITE 370: ADO.NET in VB.NET
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
University of South Alabama School of CIS
Last Modified: 5/24/2016
18
ITE 370: ADO.NET in VB.NET
Hiding a Column in DataGrid
• Sometimes, you will want to store a column in the
DataGrid that you don’t 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(TableStyle)
'* Hide the column with ColumnName by setting its width to zero
dtgPlayers.TableStyles(TableName).GridColumnStyles(ColumnName).Width = 0
End Sub
University of South Alabama School of CIS
Last Modified: 5/24/2016
19