Transcript The ADO .NET DataReader Object (continued)

Managing MS SQL Server®
Data Sources in VS.NET
ASP.NET
Data Source Processing Requirements
• In today's computer networks there needs to be the capability
to share incompatible data from differing file types:
– Data from different software companies such as
Microsoft, Oracle, Sybase, Informix, etc.
– Formatted data (i.e. currency format with 1000's
separators), graphics, audio, video, etc.
– Data from a variety of applications, i.e. word
processing documents, spreadsheets, etc.
– Data stored on intranets and the Internet including
XML data sources
Universal Data Access
• The solution to this need to share data is called
universal data access (UDA)
• At core to universal access is OLE (object linking and
embedding) DB (database) object interface
• Data from a variety of sources can be shared across
different applications and platforms
• Accesses data stored in different formats , i.e.
– I.e., UDA can treat data stored in databases and
spreadsheets as if they all were in the same format,
even within the same application
Overview of the ADO.NET Framework
• ActiveX Data Object Model (ADO)
– Provides objects that interface with the database
– Separates the process of connecting to the data source
from the manipulation of the data
– Ability to write code that can transfer between
applications
Visual Studio .NET and Databases
• Visual Studio .NET can operate on databases from
different software companies:
– Microsoft, Oracle, Sybase, Informix, etc.
• SQL commands are embedded into code enabling
communication with the DBMS
• Additionally ASP.NET Toolbox controls may be used
to provide database connectivity
General Steps for Using Web Databases
1.
2.
Build your database tables and queries/stored procedures
Create a connection to the database
– The connection identifies the location of the database
(the data source) and the connection method (ODBC
driver, OLE-DB provider, or an OLE-DB.NET data
provider), and any other settings, i.e. username/password
3. Create an ASP.NET Web page
4. Add ADO.NET connection object that connects to database,
executes commands, and returns data from the database
5. Create code that will interact with the data, display the data
in an ASP.NET control, perform calculations on the data, or
upload changes to the database
.NET Data Provider
Downloading Microsoft SQL Server
Desktop Engine (MSDE) Files
1. Create folder
–
md c:\MSDETempDE
–
Download files and extract files
2. Command Window
–
–
cd C:\sql2ksp3\MSDE
setup INSTANCENAME="NetSDK"
SECURITYMODE=SQL SAPWD="password"
3. Verify files and folders installed
–
C: cd\Program Files\Microsoft SQL
Server\MSSQL$NETSDK
– Binn, Data, Install, and Log folder should be there
Installing .NET Framework DSK
Samples Databases
1. Click Start and select "All Programs"  "Microsoft
.NET Framework SDK v1.1"  "Samples and
Quickstart Tutorials"
2. Select "Setup the Quickstarts" and in the download
window click <Open> button; click <Open> again
(ConfigSamples.exe will not harm your computer)
3. When "Congratulations!" window opens, click the
<Launch> button, then the <Connect> button (if
the page loads, installation was successful)
Visual Studio .NET Built-In Database
Tools
• Visual tools to create and manipulate databases
– Server Explorer—established database connections,
creates database, tables, and stored procedures
– Table Designer—create columns in tables, view and
edit table data
– Query and View Editor—graphically create database
queries, and view results
– SQL Editor—create and edit SQL scripts and stored
procedures, and view results
– Data Diagram—graphically define relationships
between columns in two or more tables
Creating a SQL Server Database in
Visual Studio .NET
• Authentication is required for access
– Windows NT
– SQL Server
• User ID identifies users and access to objects
– Each user has roles - create or modify data object
– Needs permission to create database
• See Appendix B for working with the MSDE and
troubleshooting data connection problems
The Table Designer
(Page 1)
• Table Design view
– Create schema or table structure in the Design pane
• Column Name (fieldname)—do not use blank spaces
or special characters other than an underscore
• Data Type—convert to .NET data types when you
retrieve your data using ADO.NET
• Allow Nulls—no value, a null value is returned
– Properties Pane—below the Design pane; available
options depend upon field's data type
• Table Data view
– Edit table data (create new row, modify or delete rows)
The Table Designer ("Products" Table)
The Table Designer
(Page 2)
• Properties Pane entries for the int data type
– Identity property, when set to a value of "Yes",
automatically provides a unique value for each record
to locate a specific row of data
– Identity Seed—shows the initial value of first row
– Identity Increment—value by which to increment the
seed each time a new row is added
– Therefore:
• If seed is 1 and increment is 1, field values for the first
three records are 1, 2, 3…
• If seed is 1 and increment is 2, field values are 1, 3, 5…
The Properties Pane
The Table Designer
(Page 3)
• Primary Key
– Each value must be unique within this column
• All rows must have a value for this column
• No record contains a null value for column
– Click the Row selector box; then click the "Set
Primary Key" icon on Table toolbar
– A yellow "key" icon appears at the side of the
column name(s)
The Table Designer ("Products" Table)
The Table Designer (Products Table)
Creating a View with Query and View
Editor
(Page 1)
• Used to create a query in the database
• When one pane changes, the other panes updated
– Table Pane—add tables and select columns (displays
the query visually with icons )
– Grid Pane—select columns and criteria, just when
using Microsoft Access; column name and table
indicate where to retrieve the values for column
– SQL Pane—generates and displays corresponding
SQL statement
– Preview Pane—test run queries and displays results
Creating a View with Query and View
Editor
Creating a View with Query and View
Editor
(Page 2)
• The Grid pane elements:
– Alias—display an alternate column name (useful for
calculated or concatenated columns)
– Output—should column be visible or not (some
columns may be used only for criteria conditions)
– Sort type—ascending or descending
– Sort order—sorting hierarchy (indicate one or more
columns to sort the results)
– Criteria—conditional statement (more than one entry
indicates an AND condition)
– Or—indicates an alternative condition
Setting Relationships with Data Diagram
• Defines relationships (links) between tables
– A line is drawn from a field from one table to another
which indicates the fields that define the relationship
– Referential integrity
• The value of any row in the linked column of one table
must match a primary key value in the other, or be null
• Endpoints indicate type of relationship
– One-to-many relationship
• Key at one endpoint and infinity symbol at the other
– One-to-one relationship
• Key at each endpoint
Setting Relationships with Data Diagram
Foreign key
Primary key
Building Stored Procedures
(Page 1)
• A stored procedure is a SQL script (command) that
is stored within the database:
– Runs a SQL query (SELECT, INSERT, etc)
– Because stored procedures already have been parsed
and compiled by the server, they are more efficient
than a SQL statement in a View
– Values can be replaced with input parameters
(represent variable values entered by user)
Building Stored Procedures
(Page 2)
• When a new procedure is begun, the editor inserts a
skeletal stored procedure (template)
• Color codes the SQL keywords in blue
• Comments are contained within /* and */ characters
and displayed in green text
• The default behaviors of the editor may be changed in
the Options… dialog window on the Tools menu
– Tab size, word wrapping, line numbers, etc.
Building Stored Procedures
(Page 3)
The CREATE and ALTER Procedures
• When new procedure is being created, first statement
is CREATE PROCEDURE
• The first line changes from CREATE PROCEDURE
to ALTER PROCEDURE after the procedure has
been created and saved (compiles when saved)
• Developer may change or alter the procedure using
the ALTER PROCEDURE command
Modifying Stored Procedures with the
SQL Query Builder
• The SQL Query Builder is a tool within the Stored
Procedure application
• It functions just like the Query and View Editor
used for creating and editing views
• To use the SQL Query Builder
1. Select the Edit Stored Procedure command for an
existing procedure
2. Right-click around the block of SQL code enclosed
within the blue lines and select Design SQL Block
3. Use the Query and View editor as before
Data Retrieval in SQL
• The keyword SELECT is used in SQL to create
queries that retrieve sets of rows (records) from
one or more related tables in a relational database
• Basic format:
SELECT columnName(s)
FROM tableName(s)
WHERE relationCondition;
– SELECT clause is a comma-delimited list that limits
columns returned (required)
– WHERE clause is a condition that limits rows returned
(optional)
The WHERE Clause
• Base on a truth condition which limits which rows
are returned in the query
• Relational operators are =, >, <, >=, <=, <>
• Examples:
SELECT *
FROM Payables
WHERE [PO number] = "10003";
SELECT *
FROM [Purchase Order]
WHERE Subtotal >= 38.15;
Building Stored Procedures
Input Parameters in Stored Procedures
(Page 1)
• Variable values passed to the stored procedure
– Parameters are input parameters by default
– Must match data type and length of the field that it
references in the SQL query
– Name always begins with the symbol "@"
– The input parameter is often compared to a value in a
SQL WHERE clause
• A default value can be provided
– Must be a constant (not a variable) or it can be NULL
– Wildcard characters (*) are permitted
Input Parameters in Stored Procedures
(Page 2)
Input Parameters in Stored Procedures
Output Parameters in Stored Procedures
• Send values back to the object that called stored the
procedure
– ReturnValue—a return value is passed back
– Called with RETURN keyword
– Retrieves identity column value (the primary key)
Return @@Identity
• Information such as number of records affected
– Values can be integer, money, varchar - not text
The SQL INSERT Statement
(Page 1)
• The INSERT command adds a row (record) to a table
• Format
INSERT INTO tableName
[(columnNames)]
VALUES (value_list …);
• Example (values must match the number of items and
data types in the original table):
INSERT INTO Payables
VALUES ("10004", ”650", 1000, 3, .F.);
The SQL INSERT Statement
(Page 2)
• Example (null values may be indicated by leaving
space between commas where they occur in table):
INSERT INTO Payables
VALUES ("10004", "701", , 8, .F.);
• Example (a columnName list may be used if not all
columns will be assigned a value):
INSERT INTO Payables,
([PO number], [Product number], Quantity,
[Back ordered?])
VALUES ("10004", "701", 8, .F.);
Insert Stored Procedure with Parameter
Insert Stored Procedure with Parameter
WHERE with the LIKE Operator
(Page 1)
• Uses the symbol "%" as a wildcard to substitute for
any other character(s) in a WHERE clause
• Format:
SELECT columnNames
FROM tableName
WHERE columnName LIKE "%criteriaString%";
– One or more wildcard symbols may be inserted at
beginning middle or end of criteriaString
WHERE with the LIKE Operator
(Page 2)
• Example 1 (begins with the letter "P"):
SELECT *
FROM Product
WHERE Product LIKE "P%";
• Example 2 (contains the letter "R"):
SELECT *
FROM Product
WHERE Product LIKE "%R%";
WHERE with the LIKE Operator
(Page 3)