Transcript Specifying Specific Data Rows for Retrieval Contd.

SQL for SQL Server
Bijoy Bordoloi and Douglas Bock
Chapter 12: Embedded SQL
Prentice Hall © 2004
1
Objectives
• Learn the benefits of embedding SQL in a host
language.
• Use ActiveX Data Object (ADO) control
properties to specify a SQL statement in Visual
Basic that retrieves data rows.
• Use ADO control properties to store (update) data
rows.
• Use ADO.NET to connect a Visual Basic.NET
program to a database.
• Use ADO.NET to execute a SELECT statement.
Prentice Hall © 2004
2
Benefits of Embedding SQL in a
Procedural Language
•
•
SQL is often embedded in languages such
as C++ and Visual Basic.NET in order to
combine the nonprocedural capabilities of
SQL to the rich command set of
procedural languages.
Simplifies writing queries to generate
recordsets that are then processed with
procedural languages.
Prentice Hall © 2004
3
Embedding SQL in Visual Basic 6.0
• Visual Basic 6.0 has a wide base of installed
computer applications.
• The ActiveX Data Objects (ADO) control
of VB 6.0 enables the easy creation of
recordsets. This control is used to store SQL
statements as a property of the control.
Prentice Hall © 2004
4
ADO Control—Connection String Property
•
ConnectionString property stores all information
needed by an ADO control to make a database
connection – includes the database name, user
identification, and password.
REM VB6 Example 12.1
ConnectionString = "Provider=SQLOLEDB;
Server=OurSQLServer;"
Database=Company; User ID=dbock;
Password=mypassword"
Prentice Hall © 2004
5
ADO Control—CommandType Property
•
•
•
CommandType Property – specifies how data
will be retrieved from a database.
Data is retrieved and stored to a recordset in
memory. Generally a recordset is a subset of
rows in a table.
A value of “1-adCmdText” for this property
specifies that data rows for the creation of a
recordset will be retrieved through use of an
SQL statement.
Prentice Hall © 2004
6
•
•
ADO Control—RecordSource Property
The RecordSource property of an ADO control
stores the actual SQL statement used to create a
recordset.
The SQL statement is exactly like those you’ve
already learned – here the recordset will be based
on the employee zip code.
REM VB6 Example 12.2
SELECT emp_last_name, emp_first_name,
emp_middle_name, emp_ssn, emp_address,
emp_city, emp_state, emp_zip
FROM employee
WHERE emp_zip = '62025';
Prentice Hall © 2004
7
DataSource and DataField Properties
•
•
•
Each control that will display data on a form,
such as a textbox control, has a DataSource and
DataField property.
DataSource – this property is set to the name of
the ADO control, for example, adoEmployee –
this links the ADO control’s recordset to the
form control that will display the data.
DataField – this property is set to the column
name from the table for which data is to be
displayed .This binds the control to a specific
data column.
Prentice Hall © 2004
8
DataSource and DataField Properties
Contd.
•
The DataField and DataSource properties can be
set visually with the Property Window that displays
properties of a control such as a textbox.
Prentice Hall © 2004
9
Specifying Specific Data Rows for Retrieval
•
An InputBox control can be used to enter specific
values to be used in an SQL statement’s WHERE
clause. This figure shows the entry of an employee
social security number.
Prentice Hall © 2004
10
Specifying Specific Data Rows for Retrieval
Contd.
• The VB 6.0 program generates the InputBox and
stores the employee SSN value to a string variable
as shown in this example:
REM VB6 SQL Example 12.4
Dim strSSN As String
strSSN = InputBox("Enter Employee SSN:",
"Employee SSN Search", vbOKCancel)
Prentice Hall © 2004
11
Specifying Specific Data Rows for Retrieval
Contd.
• The SQL SELECT statement is parameterized and
stored to a second string variable as shown here.
Note the use of the first string variable (strSSN) as
a parameter.
REM VB6 Example 12.5
'Store SQL statement to a string variable
strSQL = "SELECT emp_last_name,
emp_first_name, emp_middle_name, emp_ssn,
emp_address, emp_city, emp_state, emp_zip
FROM employee
WHERE emp_ssn = " & strSSN
Prentice Hall © 2004
12
Specifying Specific Data Rows for Retrieval
Contd.
• The SQL statement is executed by storing the
value of the string variable (strSQL) that holds the
SQL statement to the RecordSource property of
the ADO control, then by executing a Refresh
method to create the desired recordset.
REM VB6 Example 12.6
'Update the recordset retrieved by
'the ADO control
adoEmployee.RecordSource = strSQL
adoEmployee.Refresh
Prentice Hall © 2004
13
Embedding SQL in Visual Basic.Net
• Visual Basic.NET is a core programming
language in Microsoft’s new .NET
framework.
• VB.NET supports both windows-based and
web-based applications development.
• Data can be stored/retrieved over the
Internet.
Prentice Hall © 2004
14
The SYSTEM.DATA Namespace
• In the .NET framework, all objects are members
of classes.
• A namespace is used to group classes together.
• The System.Data namespace stores classes needed
to access most databases.
– System.DataSQLClient supports connection to
Microsoft’s SQL Server
– System.Data.OleDB supports connection to databases
with an OLE DB provider such as Oracle or Microsoft
Access.
Prentice Hall © 2004
15
ADO.NET
• ADO has evolved to become ADO.NET in Visual
Basic.NET
• ADO.NET supports the following database
programming tasks:
– Configuring database components that create a
connection and dataset (datasets replace the
recordsets used in VB 6.0).
– Executing SQL statements to add, delete,
modify, or retrieve table rows.
– Working with datasets.
Prentice Hall © 2004
16
Configuring Database Components
• The easiest approach to creating a database
connection and configuring the various
controls used to manage the connection is
through the Data Adapter Configuration
Wizard.
• This wizard launches when you add a data
adapter control to a Visual Basic.NET
project.
Prentice Hall © 2004
17
Data Adapter Configuration Wizard
Welcome Screen
Prentice Hall © 2004
18
Choose Your Data Connection
• This enables you to select from an existing
database connection.
Prentice Hall © 2004
19
Data Link Properties Window – Connection Tab
• Use this
window to
create a
new
database
connection.
Prentice Hall © 2004
20
Query Builder – Used to Write a SELECT Statement
Prentice Hall © 2004
21
Data Adapter Configuration Wizard
• The Wizard generates the SELECT
statements as well as the corresponding
INSERT, UPDATE, and DELETE
statements for the specified dataset.
• Each SQL statement is stored as a separate
property of the SQLDataAdapter control.
• You can also preview the dataset as shown
in the figure on the next slide.
Prentice Hall © 2004
22
Preview a Dataset
Prentice Hall © 2004
23
Controls Added to the VB.NET
Form by the Wizard
Prentice Hall © 2004
24
Data Displayed on the Employee Form
Prentice Hall © 2004
25
DataBindings Property
• Data are
bound to
each textbox
control by
setting the
value of the
DataBindings
property
visually.
Prentice Hall © 2004
26
Summary
• Both VB 6.0 and VB.NET support the use
of embedded SQL statements.
• Embedding SQL makes it easier for a
programmer to develop applications rapidly
by taking advantage of the nonprocedural
power of SQL.
• Various configuration wizards support the
programmer by simplifying the
programming process.
Prentice Hall © 2004
27