1. technology and computing
2. software
3. databases

10/5/2008
Objectives
Chapter 14
Applied
• Use a data source to get the data that an application requires.
• Use a DataGridView control to present the data that’s retrieved by
a data source.
How to work with
data sources
and datasets
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
• Use other controls like text boxes to present the data that’s
retrieved by a data source.
• Write the Visual Basic code for handling any data errors that result
from the use of the data source or the controls that are bound to it.
• Use the Dataset Designer to (1) view the schema for the dataset of
a data source, (2) modify a query using the Query Builder, (3)
preview the data for a query, or (4) review the SQL statements that
are generated for a data source.
Slide 1
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 2
An empty Data Sources window
Objectives (continued)
Knowledge
• Describe the use of a connection string in an app.config file.
• Describe the use of the Fill and UpdateAll methods of the
TableAdapter object.
• Describe the use of the EndEdit method of the BindingSource
object.
• Describe the two categories of data errors that can occur when you
run an application that uses a data source.
• Describe the use of the DataError event for a DataGridView
control.
• In general terms, describe the way the SQL statements that are
generated for a data source (1) prevent concurrency errors, and (2)
refresh a dataset when the database generates the keys for new
rows.
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 3
© 2008, Mike Murach & Associates, Inc.
© 2008, Mike Murach & Associates, Inc.
Slide 4
The first step of the
Data Source Configuration Wizard
A Data Sources window after a data source has
been added
Murach’s Visual Basic 2008, C14
Murach’s Visual Basic 2008, C14
Slide 5
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 6
1
10/5/2008
How to start the Data Source Configuration Wizard
• Click on the Add New Data Source link that’s available from the
Data Sources window when a project doesn’t contain any data
sources.
• To get your data from a database, select the Database option. This
option lets you create applications like the ones described in this
chapter.
• Click on the Add New Data Source button at the top of the Data
Sources window.
• To get your data from a web service, select the Web Service
option. This option lets you browse the web to select a web service
that will supply data to your application.
• Select the Add New Data Source command from Visual Studio’s
D t menu.
Data
• To get your data from a business object, select the Object option.
• Add a SQL Server (.mdf) or Access (.mdb) data file to the project
using the ProjectÆAdd Existing Item command. Then, the wizard
will let you choose the database objects you want to include.
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 7
The second step of the
Data Source Configuration Wizard
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
How to choose a data source type
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 8
The Add Connection and Change Data Source
dialog boxes
Slide 9
In the Express Edition…
• The Change Data Source dialog box provides only three options:
Microsoft Access Database File, Microsoft SQL Server Compact
3.5 (the default), and Microsoft SQL Server Database File.
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 10
The third step of the
Data Source Configuration Wizard
• The Add Connection dialog box is simpler, and it includes a
Database File Name text box that you use to specify the database.
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 11
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 12
2
10/5/2008
The information that’s stored in the app.config file
The last step of the
Data Source Configuration Wizard
<connectionStrings>
<add name=
"ProductMaintenance.My.MySettings.MMABooksConnectionString"
connectionString="Data Source=localhost\sqlexpress;
Initial Catalog=MMABooks;
Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 13
How to work with columns that have default values
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 14
A project with a dataset defined by a data source
• If a column in a database has a default value, that value isn’t
included in the column definition in the dataset.
• Because of that, you may want to omit columns with default values
from the dataset unless they’re needed by the application. Then,
when a row is added to the table, the default value is taken from the
database.
• If you iinclude
l d a column
l
that’s
th t’ defined
d fi d with
ith a default
d f lt value,
l you
must provide a value for that column whenever a row is added to
the dataset. One way to do that is to let the user enter a value.
Another way is to display the Dataset Designer and use the
Properties window for the column to set the DefaultValue property.
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 15
A form after the Products table has been dragged
onto it
Murach’s Visual Basic 2008, C14
BindingNavigator control
BindingSource object
DataSet object
© 2008, Mike Murach & Associates, Inc.
Slide 17
Slide 16
The controls and objects that are created when
you drag a data source to a form
Control/object
DataGridView control
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Murach’s Visual Basic 2008, C14
Description
Displays the data from the data source in
a grid.
Defines the toolbar that can be used to
navigate, add, update, and delete rows in
the DataGridView control
control.
Identifies the data source that the
controls on the form are bound to and
provides functionality for working with
the data source.
Provides access to all of the tables,
views, stored procedures, and functions
that are available to the project.
© 2008, Mike Murach & Associates, Inc.
Slide 18
3
10/5/2008
The controls and objects that are created when
you drag a data source to a form (continued)
The user interface for the Product Maintenance
application
Control/object
TableAdapter object
Description
Provides the commands that are used
to read and write data to and from the
specified table in the database.
TableAdapterManager
p
g object
j
Provides for writing
g data in related
tables to the database while
maintaining referential integrity.
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 19
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 20
The code that’s generated by Visual Studio
Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the
''MMABooksDataSet.Products' table.
'You can move, or remove it, as needed.
Me.ProductsTableAdapter.Fill( _
Me.MMABooksDataSet.Products)
End Sub
The syntax of the Fill method
TableAdapter.Fill(DataSet.TableName)
The syntax of the UpdateAll method
TableAdapterManager.UpdateAll(DataSet)
Private Sub ProductsBindingNavigatorSaveItem
ProductsBindingNavigatorSaveItem_Click(
Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles ProductsBindingNavigatorSaveItem.Click
Me.Validate()
Me.ProductsBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.MMABooksDataSet)
End Sub
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 21
How to change the default control for a data table
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 23
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 22
How to change the default control for a column in
a data table
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 24
4
10/5/2008
A form after the Customers table has been
dragged onto it
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
The user interface for the Customer Maintenance
application
Slide 25
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 26
The code for the Customer Maintenance application
.NET data provider exception classes
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the
''MMABooksDataSet.Customers' table.
'You can move, or remove it, as needed.
Me.CustomersTableAdapter.Fill( _
Me.MMABooksDataSet.Customers)
End Sub
Name
SqlException
OracleException
OdbcException
OleDbException
Thrown if a server error occurs…
When accessing a SQL Server database.
When accessing an Oracle database.
When accessing an ODBC database.
When accessing an OLE DB database.
Private Sub CustomersBindingNavigatorSaveItem_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles CustomersBindingNavigatorSaveItem.Click
Me.Validate()
Me.CustomersBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.MMABooksDataSet)
End Sub
End Class
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 27
Common members of the .NET data provider
exception classes
Property
Number
Message
Source
Errors
Method
GetType()
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 28
Code that catches a SQL exception
Description
An error number that identifies the type of error.
A message that describes the error.
The name of the provider that generated the error.
A collection of error objects that contain
information about the errors that occurred during a
database operation.
Description
Gets the type of the current exception.
© 2008, Mike Murach & Associates, Inc.
Murach’s Visual Basic 2008, C14
Slide 29
Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles MyBase.Load
Try
Me.CustomersTableAdapter.Fill( _
Me.MMABooksDataSet.Customers)
Catch ex As SqlException
MessageBox.Show("Database
g
(
error # " & ex.Number _
& ": " & ex.Message, ex.GetType.ToString)
End Try
End Sub
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 30
5
10/5/2008
Common ADO.NET exception classes
Class
Description
DBConcurrencyException
The exception that’s thrown if the number of
rows affected by an insert, update, or delete
operation is zero.
DataException
The general exception that’s thrown when an
ADO.NET error occurs.
ConstraintException The exception that’s thrown if an operation
violates a constraint. This is a subclass of the
DataException class.
NoNullAllowedException
The exception that’s thrown when an add or
update operation attempts to save a null value
in a column that doesn’t allow nulls. This is a
subclass of the DataException class.
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 31
Common members of the ADO.NET classes
Property
Message
Method
GetType()
Murach’s Visual Basic 2008, C14
Try
Me.CustomersBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.MMABooksDataSet)
Catch ex As DBConcurrencyException
MessageBox.Show("A concurrency error occurred. " _
& "Some rows were not updated.", _
"Concurrency Error")
Me.CustomersTableAdapter.Fill( _
Me.MMABooksDataSet.Customers)
Catch ex As DataException
MessageBox.Show(ex.Message, ex.GetType.ToString)
CustomersBindingSource.CancelEdit()
Catch ex As SqlException
MessageBox.Show("Database error # " & ex.Number _
& ": " & ex.Message, ex.GetType.ToString)
End Try
Event
DataError
Slide 32
Slide 33
Description
Raised when the DataGridView control detects a
data error such as a value that isn’t in the correct
format or a null value where a null value isn’t valid.
Three properties of the
DataGridViewDataErrorEventArgs class
Property
Exception
RowIndex
ColumnIndex
© 2008, Mike Murach & Associates, Inc.
© 2008, Mike Murach & Associates, Inc.
An event of the DataGridView control
Code that handles ADO.NET errors
Murach’s Visual Basic 2008, C14
Description
A message that describes the exception.
Description
Gets the type of the current exception.
Murach’s Visual Basic 2008, C14
Description
The exception that was thrown as a result of the
error. You can use the Message property of this
object to get additional information about the
exception.
The index for the row where the error occurred.
The index for the column where the error occurred.
© 2008, Mike Murach & Associates, Inc.
Slide 34
The schema displayed in the Dataset Designer
Code that handles a data error for a DataGridView
control
Private Sub ProductsDataGridView_DataError( _
ByVal sender As System.Object, _
ByVal e As _
System.Windows.Forms.DataGridViewDataErrorEventArgs) _
Handles ProductsDataGridView.DataError
Dim row As Integer = e.RowIndex + 1
Dim errorMessage As String = _
"A data error occurred." & vbCrLf _
& "Row: " & row & vbCrLf _
& "Error: " & e.Exception.Message
MessageBox.Show(errorMessage, "Data Error")
End Sub
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 35
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 36
6
10/5/2008
The Query Builder
The Preview Data dialog box
Diagram
pane
Grid
pane
SQL
pane
Results
pane
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 37
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 38
SQL that retrieves customer rows
SQL that updates a customer row and refreshes
the dataset
SELECT
FROM
UPDATE
SET
CustomerID, Name, Address, City, State, ZipCode
Customers
SQL that inserts a customer row and refreshes
the dataset
WHERE
INSERT INTO Customers
(Name, Address, City, State, ZipCode)
VALUES
(@N
(@Name,
@Add
@Address, @Cit
@City, @St
@State,
t
@Zi
@ZipCode);
C d )
SELECT
FROM
WHERE
CustomerID, Name, Address, City, State, ZipCode
Customers
(CustomerID = SCOPE_IDENTITY())
SELECT
FROM
WHERE
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 39
Customers
Name = @Name,
Address = @Address,
City = @City,
State = @State,
ZipCode = @ZipCode
(
(CustomerID = @Original_CustomerID) AND
(Name = @Original
@Original_Name)
Name) AND
(Address = @Original_Address) AND
(City = @Original_City) AND
(State = @Original_State) AND
(ZipCode = @Original_ZipCode)
);
CustomerID, Name, Address, City, State, ZipCode
Customers
(CustomerID = @CustomerID)
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 40
SQL that deletes a customer row
DELETE FROM Customers
WHERE
(CustomerID = @Original_CustomerID) AND
(Name = @Original_Name) AND
(Address = @Original_Address) AND
(City = @Original_City) AND
(State = @Original_State) AND
(ZipCode = @Original_ZipCode)
Murach’s Visual Basic 2008, C14
© 2008, Mike Murach & Associates, Inc.
Slide 41
7