Module 2: Connecting to Data Sources v
Lesson: Defining a Connection
This section describes the instructional methods for teaching each topic in this
lesson.
Technical Notes: Be careful not to get drawn into a detailed discussion about
security. This section is about how to connect to secure databases by using
ADO.NET. It is not designed to be a discussion of the merits of different
security options.
Discussion Questions: Personalize the following questions to the background
of the students in your class.
!
When would you use Microsoft Windows
®
Authentication and when would
you use Mixed Mode?
!
What are the disadvantages of using Mixed Mode security?
Discussion Questions: Personalize the following question to the background of
the students in your class.
• Out of the list of parameters for a connection string, which parameters are
required to establish a connection?
Technical Notes: Use the graphical tools to generate connection strings.
Because the graphical tools write code anyway, they are now the easiest and
best way to set connection strings.
Transition to Practice Exercise: The purpose of this practice is to practice
recognizing valid connection strings and to practice correcting broken
connection strings. Be sure to go over examples before letting students do the
practice.
Instruct students to turn to the practice exercise at the end of this topic in the
student workbook.
Practice Solution: The following are answers to the practice exercises, with
changes to the original code in bold.
Exercise 1
The name of the provider was wrong, and the database path is specified by
using the Data Source parameter, not the Initial Catalog.
Use OLE DB .NET Data Provider
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\MyDB\MyDB.mdb;
Exercise 2
We should be using Windows Authentication, not SQL standard security.
Use SQL Server .NET Data Provider
Data Source=ProdServ01;Initial Catalog=Pubs;Integrated
Security=SSPI;
Database Security
What Is a Connection
Strin
g
?
How to Set a Connection
Strin
g
vi Module 2: Connecting to Data Sources
Exercise 3
The names of the SQL Server and database should be switched.
Use SQL Server .NET Data Provider
Data Source=ProdServ01;Initial Catalog=Pubs;User
ID=JohnK;Password=JohnK;
Exercise 4
The SQL Server .NET Data Provider will not work with SQL Server 6.5. Use
the OLE DB .NET Data Provider instead.
Use OLE DB .NET Data Provider
Provider=SQLOLEDB;Data Source=ProdServ01;Initial
Catalog=Pubs;Integrated Security=True;
Exercise 5
This code is correct, so no changes are required.
Use SQL Server .NET Data Provider
Data Source=ProdServ02;Initial Catalog=Northwind;Integrated
Security=SSPI;
Exercise 6
You should be using Windows Authentication, not SQL standard security.
Use SQL Server .NET Data Provider
DataSource=ProdServ02;Initial Catalog=Pubs;Integrated
Security=SSPI;
Module 2: Connecting to Data Sources vii
Exercise 7
The connection timeout is measured in seconds, not minutes.
Use SQL Server .NET Data Provider
Data Source=ProdServ01;Initial Catalog=Pubs;Integrated
Security=True;Connection Timeout=60;
Exercise 8
This code is correct because 15 seconds is the default timeout, so no changes
are required.
Use SQL Server .NET Data Provider
Data Source=ProdServ01;Initial Catalog=Pubs;Integrated
Security=True;
Exercise 9
Add an additional option to save the password in the connection string.
Use SQL Server .NET Data Provider
Data Source=ProdServ02;Initial Catalog=Pubs;User
ID=JohnK;Password=JohnK;Persist Security Info=True;
viii Module 2: Connecting to Data Sources
Lesson: Managing a Connection
This section describes the instructional methods for teaching each topic in this
lesson.
Technical Notes: Make sure that you clearly explain the difference between
closing, disposing, and setting a connection object to Nothing or null. Closing
and disposing affect server resources. Setting the connection object to Nothing
or null affects client resources.
Technical Notes: If you are not familiar with Microsoft Visual C#
™
, make sure
that you know how to manually add code to handle a non-default event. The
default event can be handled by double-clicking the object on the graphical
designer. For example, to automatically write code to the handle a form’s Load
event, double-click the form’s caption bar.
Transition to Practice Exercise: The purpose of this practice is to gain
experience handling connection events. This practice uses the StatChange
event, but the process for handling events would be similar for the other
connection events.
Instruct students to turn to the practice exercise at the end of this topic in the
student workbook.
Practice Solution: The solution for this practice is located in
<install folder>\Practices\Mod02\Lesson3\CS\HandlingStateChange\
After the Practice:
• Discuss answers for the questions that are at the end of the practice.
Opening and Closing a
Connection
Handling Connection
Events
Module 2: Connecting to Data Sources ix
Lesson: Handling Connection Exceptions
This section describes the instructional methods for teaching each topic in this
lesson.
Technical Notes: The students should already be familiar with this topic, so do
not spend too much time describing structured exception handing in general
terms. Always use ADO.NET objects in any examples that you demonstrate.
Examples: The Visual Basic example showing connection exception handling
can be demonstrated by commenting out the line of code that instantiates the
connection object, which will cause a NullReferenceException, and by altering
the connection string to cause another exception.
Transition to Practice Exercise: The purpose of this practice is to help you
become more familiar with exception handling by reviewing the exceptions that
can occur when using specific methods within a class.
Instruct students to turn to the practice exercise at the end of this topic in the
student workbook.
After the Practice:
!
What are some of the exceptions that can occur when calling the Open
method?
!
What are some of the exceptions that can occur when calling the
ChangeDatabase method?
Technical Notes:
!
The key point here is that when a SqlException occurs, the Errors collection
could contain more than one error, and therefore must be looped through.
Usually there will only be one error, so the Errors collection could be
ignored and only the SqlException properties read. However, this would not
be the best practice.
!
SqlExceptions are raised when serious problems occur on the computer
running SQL Server.
Transition to Practice Exercise: The purpose of this practice is to:
Instruct students to turn to the practice exercise at the end of this topic in the
student workbook.
Practice Solution: The solution for this practice is located in
<install folder>\Practices\Mod02\Lesson4\xx\CatchingSqlExceptions\ where xx
is either VB or CS.
What Is Structured
Exception Handling?
How to Handle
SqlExceptions
x Module 2: Connecting to Data Sources
Technical Notes:
!
InfoMessage events are raised when minor or potential problems occur on
the SQL Server. The details are accessed through the SqlError class,
similar to SqlExceptions, but these objects should be treated as
informational messages rather than actual errors.
!
Stress the usefulness of using the Print T-SQL statement for debugging.
Discussion Questions: Personalize the following question to the background of
the students in your class.
• How could you use the InfoMessage event in your applications?
Transition to Practice Exercise: The purpose of this practice is to practice
using the InfoMessage event in an error-handling routine.
Instruct students to turn to the practice exercise at the end of this topic in the
student workbook.
Practice Solution: The solution for this practice is located in
<install folder>\Practices\Mod02\Lesson4\xx\HandlingInfoMessage\ where xx
is either VB or CS.
How to Handle the
InfoMessage Event
Module 2: Connecting to Data Sources xi
Lesson: Connection Pooling
This section describes the instructional methods for teaching each topic in this
lesson.
Technical Notes: Most students will have only a vague understanding of
connection pooling, because it was always difficult to manage in the past.
Discussion Questions: Personalize the following questions to the background
of the students in your class.
!
What are some other applications in which connection pooling could be
used?
!
Do the application users need to be aware of connection pooling?
!
In the examples of connection pooling, what is different about the third
connection string?
Technical Notes: ADO.NET has greatly improved management of SQL Server
connection pooling.
Discussion Questions: Personalize the following question to the background of
the students in your class.
• As this multimedia animation explained, what happens in connection
pooling when you use the Dispose method and what happens when you use
the Close method?
Technical Notes: Management of OLE DB connection pooling is the same
with ADO.NET as it was with ADO, so any existing documentation still
applies.
Technical Notes:
!
The most useful property is probably the Min Pool Size property, because
this allows the connection pool to be “warmed up” before use, and prevents
the pool from completely disappearing.
!
Set the Min Pool Size in order to have connections available immediately
for some security context for future pools.
Technical Notes: Make sure that you are familiar with SQL Profiler.
Discussion Questions: Personalize the following questions to the background
of the students in your class.
!
How many of you have experience with SQL Profiler?
!
What other tasks can you name for which you might use SQL Profiler?
What Is Connection
Pooling?
Multimedia: How SQL
Server Connection
Pooling Works
Controlling OLE DB
Connection Pooling
Controlling SQL Server
Connection Pooling
Demonstration:
Monitoring SQL Server
Activity
xii Module 2: Connecting to Data Sources
Review: Connecting to Data Sources
This section provides the answers to review questions at the end of this module.
1. What is a .NET data provider?
The .NET data providers are a core component within the ADO.NET
architecture that enables communication between a data source and a
component, an XML Web service, or an application. A data provider
allows you to connect to a data source, retrieve and manipulate data,
and update the data source.
2. What are the two security modes in SQL Server 2000?
SQL Server can operate in one of two authentication modes: Microsoft
Windows Authentication and Mixed Mode (Windows Authentication
and SQL Server authentication).
3. Which connection object methods do you use to manage a connection?
Use the Open and Close methods to manage a connection.
4. What is the difference between closing and disposing a connection?
You must always close the connection when you have finished using it.
To do this, you can use either the Close or Dispose methods of the
connection object.
The Close method rolls back any pending transaction. It then closes the
connection, or releases the connection to the connection pool if pooling
is enabled.
5. How are exceptions handled in the .NET Framework?
Exceptions are handled in the .NET Framework by using
the Try. . .Catch . . . Finally statement.
6. What is connection pooling?
Connection pooling is the process of keeping connections active and
pooled so that they can be efficiently reused. Connections with identical
connection strings are pooled together and can be reused without
reestablishing the connection.
Module 2: Connecting to Data Sources 1
Overview
!
Choosing a .NET Data Provider
!
Defining a Connection
!
Managing a Connection
!
Handling Connection Exceptions
!
Connection Pooling
*****************************
ILLEGAL FOR NON
-
TRAINER USE
******************************
This module explains the concepts and procedures necessary to create and
manage a Microsoft
®
ADO.NET connection to Microsoft SQL Server
™
or other
data sources.
After completing this module, you will be able to:
!
Choose a .NET data provider.
!
Connect to SQL Server.
!
Connect to OLE DB data sources.
!
Manage a connection.
!
Handle common connection exceptions.
!
Implement and control connection pooling.
Introduction
Objectives
2 Module 2: Connecting to Data Sources
Lesson: Choosing a .NET Data Provider
!
What Are .NET Data Providers?
!
The .NET Data Provider Classes
!
Which .NET Data Provider Should You Use?
*****************************
ILLEGAL FOR NON
-
TRAINER USE
******************************
When connecting to a data source, you must first choose a .NET data provider.
The data provider includes classes that enable you to connect to the data source,
read data efficiently, modify and manipulate data, and update the data source.
This lesson explains the various types of data providers, and enables you to
choose the appropriate provider for your application.
After completing this lesson, you will be able to:
!
Describe the different .NET data providers.
!
Choose a .NET data provider.
Introduction
Lesson objectives
Không có nhận xét nào:
Đăng nhận xét