70-320%20XML%20Web%20Services

1
70-320 XML Web Services
Alistair Lawson, SoC
Bill Buchanan, SoC
2
Date Title Kalani
Monday 4 June 11-12 Unit 0 Introduction to .NET -
Monday 4 June 2-4pm Unit 0 Introduction to .NET -
Tuesday 5 June 10-12 Unit 1 Creating/Manipulating Datasets Unit 1
Tuesday 5 June 2-4pm Unit 1 Creating/Manipulating Datasets Unit 1
Tuesday 11 June Unit 2 Manipulating XML Data Unit 2
Tuesday 12 June Unit 2 Manipulating XML Data Unit 2
Tuesday 18 June Unit 3 .NET Remoting Unit 3
Tuesday 19 June Unit 3 .NET Remoting Unit 3
Tuesday 25 June Unit 4 Web Services Unit 4
Tuesday 26 June Unit 4 Web Services Unit 5
3
30-720 Consuming/Manipulating Data
Alistair Lawson, SoC
Bill Buchanan, SoC
4
Consuming and Manipulating Data

• Access and manipulate data from a Microsoft SQL
  Server database by creating and using ad hoc
  queries and stored procedures.
• Introduction to XML
• Create and manipulate DataSets.
• Manipulate a DataSet schema.
• Manipulate DataSet relationships.
• Create a strongly typed DataSet.

5
Introduction
XML
Alistair Lawson
6
WWW languages of the past, especially HTML, have
been fixed in their tags. A new format named XML
(eXtensible Markup Language) can be used to
create new tags, and provide a common platform
for transferring information between different
systems and packages. The first line of an XML
file typically contains an optional xml
processing instruction (known as the XML
declaration). This can contain pseudo-attributes
that indicate the XML language version, the
character set, and whether it can be used as a
standalone entity. An example is the XML
declaration that begins every valid XML
file lt?xml version"1.0" standalone"yes" ?gt
7
The XML document conforms to the XML
recommendations, and has a logical structure that
is composed of declarations, elements, comments,
character references, and processing
instructions. It also has a physical structure
which is composed of entities, starting with the
root, or document entity.
8
The XML object model defines a standard way in
which the elements of the XML structured tree are
defined. It is fully object-oriented and uses
properties, methods, and the actual content
(data) contained in an object. This model
controls how users interpret the trees, and
exposes all tree elements as objects, which can
be accessed without any return trips to the
server. The XML object model uses the W3C
standard know as Document Object Model.
9
XML-Data Reduced (XDR) is one of the first
languages defined which uses a schema (that is
one that is defined in an XML form. It defines
the Form of elements that are child elements of
others. Sequence in which the child elements can
appear. Number of child elements. It also
defines whether an element is empty or can
include text. XDR is now well established and
uses XML as its basic language. A new standard
known as XSD (XML Schema Definition) has been
standardized by the W3C XML Schema Working Group.
10
Root
Children
11
Root
Document root
Next Sibling
First Child
Child
Child
Child
12
Root
Document root
Child
Root
Child
First Child
Child
Child
Child
Next Sibling
13
Root
Document root
Child
Child
Root
Child
Child
Child
14
Introduction
XML Elements
Alistair Lawson
15

• An XML document can only have one document
  element. In XML a document element is a single
  element that contains all the content that is to
  be considered as past of the document itself. The
  document root is the first element that appears
  in the XML document.
• All XML elements must have end tags. In HTML,
  some tags do not require an end tag, such as ltPgt,
  whereas XML requires that every tag has an end
  tag.
• XML elements cannot overlap. The XML tags must be
  properly structured so that they do not overlap.
  For example the following is not allow
• lttag1gtBlah blah lttag2gtBlahlt/tag1gt Blahlt/tag2gt
• In this case the XML parser will stop after
  lt/tag1gt as the parser expects to find the lt/tag1gt
  next.

16

• Attribute values must contain quotes, whether or
  not they contain spaces. These quotes can either
  be a single invert comma, or a double one.
• lt, gt, or cannot be used within the text the
  document. For these characters the entities of
  lt, gt and amp can be used.
• For example If we wanted the contents of a tag
  to be My ltWebgt then the following would be used
• ltMyTaggtMy lt Webgtlt/MyTaggt

17
Attributes
An attribute is an XML structural construct. It
has a name-value pair, separated by an equals
sign, and is included inside a tagged element
that modifies the features of an element. Thus,
all attributes are string values. The following
shows an example of an attribute were id_value is
set to disk001. lt?xml version"1.0"?gt ltsoftware
gt ltdisk id_value"disk001"gt
ltauthorgtSmith, Fredlt/authorgt lttitlegtXML
Developer's Guidelt/titlegt ltfunctiongtDisk
toolslt/functiongt ltpricegt100lt/pricegt
ltversiongt1.02lt/versiongt ltdescriptiongtWindows
XP version lt/descriptiongt lt/diskgt lt/softwaregt
18
Entity
An entity is an XML structural construct. It can
be a file, a database record, or any other item
that contains data. Its main aim is to hold
content, and not structure, rules, or grammar. In
the XML document, each entity is has a unique
name and contains its own content.
19
Introduction
XML Format
Alistair Lawson
20

• Prolog. This part is at the start of the document
  (or root element). It contains information on the
  document as a whole. Typical references are to
  the character encoding, document structure, and
  style sheets. An example is
• lt?xml version"1.0" encoding"UTF-8"?gt
• lt?xml-stylesheet type"text/xsl"
  href"my_style.xsl"?gt
• lt!DOCTYPE catalog SYSTEM "mycatalog.dtd"gt
• lt!--This file was last updated 2002-12-10--gt
• The prolog can include processing instructions,
  such as the xml-stylesheet processing instruction
  (as shown in the about example). The lt!-- .
  --gt statement contains a comment.

21

• Document elements. The form the backbone of the
  XML documents, and creates the required
  structure. Within it there the elements identify
  named sections of information, and use markup
  tags that identify the name, start, and end of
  the element.
• All elements have names, and are case-sensitive
  and must start with a letter or an underscore.
• Tags establish boundaries around content. The
  start tags has the following format
• ltelementName attrib1"attrib1Value"
  attrib2"attrib2Value"...gt
• If an element name does not have any attributes
  then its format is
• ltelementName gt
• and the end tag has the following format (which
  cannot have any attributes)
• lt/elementNamegt

22

• Elements are contained between a start and an end
  tag. For example
• ltpetgt
• lttypegtCatlt/typegt
• ltnamegtHoneylt/namegt
• lt/petgt
• where the pet element contains two other
  elements
• lttypegt and ltnamegt. The lttypegt tags contains the
  text Cat, and the ltnamegt element contains
  Honey. An empty tag can be used when there is
  no textual content. An empty tag contains a slash
  (/) before the closing character, such as
• lthelp/gt
• which is the same as
• lthelpgtlt/helpgt

23

• Family and tree metaphors are used to describe
  the relationship between elements. All XML
  documents must contain a root element (which is
  also know as a document). In the following XML
  document, ltpetgt is the root document
• ltpetgt
• lttypegtCatlt/typegt
• ltnamegtHoneylt/namegt
• lt/petgt
• The following cannot be an XML document, as it
  has no root document
• lttypegtCatlt/typegt
• ltnamegtHoneylt/namegt
• With a tree structure, the leaves refer to
  elements that do not contain any other elements
  (such as in a real tree, where leaves are ends of
  branches. These leaf elements generally contain
  simple text or no textural information.

24

• The terms used in a family metaphors are such as
  parent, child, ancestor, descendant, and sibling.
  These are used to describe relationship between
  elements relative to each other (and not to the
  entire document). The following abstract sample
  document illustrates the relationships between
  elements.
• lttag1gt
• lttag2gt
• lttag3gt
• lttag4/gt
• lt/tag3gt
• lt/tag2gt
• lt/tag1gt

25
Introduction
Manipulate a Data schema
Alistair Lawson
26
User Interface
DataGrid
ListBox
CheckBox
DataView
DataRow
Data Model
DataSet
XmlNode
BinaryReader
StreamReader
SqlReader
XmlDocument
SqlCommand
BinaryWriter
StreamWriter
Data Store
XmlTextReader
FileStream
FileStream
SqlAdapter
SqlConnection
Binary file
Text file
XML file
Database
27

• The DataSet object thus provides a generalized
  abstraction of the data source, which gets rid of
  any differences in the methods used to store the
  data.
• The DataSet loaded into memory from the data
  source, and can thus be accessed faster than
  reading the data from its source.
• The DataSet is stored in a relational way which
  allows one-to-one and one-to-many relationships.

XmlNode
BinaryReader
StreamReader
SqlReader
XmlDocument
SqlCommand
BinaryWriter
StreamWriter
Data Store
XmlTextReader
FileStream
FileStream
SqlAdapter
SqlConnection
Binary file
Text file
XML file
Database
28

• The DataSet contains a schema, which defines the
  form of the stored data, such as its structure
  and its data types. This is defined as metadata.
  .NET provides an environment for the creation,
  editing and updating for this schema. An
  important element is that a strongly typed
  DataSet allows the data to be checked at any
  early stage, and should result in fewer errors.

XmlNode
BinaryReader
StreamReader
SqlReader
XmlDocument
SqlCommand
BinaryWriter
StreamWriter
Data Store
XmlTextReader
FileStream
FileStream
SqlAdapter
SqlConnection
Binary file
Text file
XML file
Database
29

• The core of ADO.NET is the DataSet object, which
  mimics a database store, and reduces the
  differences in various database types, and can
  thus interface with many types of databases, such
  as Oracle and Microsoft SQL Server.

XmlNode
BinaryReader
StreamReader
SqlReader
XmlDocument
SqlCommand
BinaryWriter
StreamWriter
Data Store
XmlTextReader
FileStream
FileStream
SqlAdapter
SqlConnection
Binary file
Text file
XML file
Database
30
Introduction
Creating an XML Schema
Alistair Lawson
31
Creating an XML Schema
.NET contains an XML graphical designer which can
create the DataSet schema. It can be created
using an editor. It can be generated from a data
source, such as from an XML file.
32
XML file
lt?xml version"1.0" encoding"utf-8"?gt ltdiarygt
ltweek number"1"gt ltmondaygt930 .NET
meetinglt/mondaygt lttuesdaygt1000 Group
meetinglt/tuesdaygt ltwednesdaygtHOLIDAYlt/wedn
esdaygt ltthursdaygtHOLIDAYlt/thursdaygt
ltfridaygtHOLIDAYlt/fridaygt lt/weekgt ltweek
number"2"gt ltmondaygt1000
Traininglt/mondaygt lttuesdaygt1500 Software
Installationlt/tuesdaygt ltwednesdaygt900
AGMlt/wednesdaygt ltthursdaygtHOLIDAYlt/thursda
ygt ltfridaygtHOLIDAYlt/fridaygt lt/weekgt
ltweek number"3"gt ltmondaygtHOLIDAYlt/monda
ygt lttuesdaygtNo meetingslt/tuesdaygt
ltwednesdaygtAll day Software Traininglt/wednesdaygt
ltthursdaygtHOLIDAYlt/thursdaygt
ltfridaygtHOLIDAYlt/fridaygt lt/weekgt lt/diarygt
XSD file
Defines the format of the data
33
Initially an element tag is dragged on the design
area, and its name is changed to week (to
correspond to the highest level of the tree).
34
Next the other elements can be added, along with
an attribute which is created by dragging the
attribute symbol onto the table.
35
(No Transcript)
36
lt?xml version"1.0" ?gt ltxsschema id"diary"
targetNamespace"http//tempuri.org/XMLFile1.xsd"
xmlnsmstns"http//tempuri.org/XMLFile1.xsd" xm
lns"http//tempuri.org/XMLFile1.xsd"
gt ltxselement name"diary" msdataEnforceConstra
ints"False"gt ltxscomplexTypegt ltxschoice
maxOccurs"unbounded"gt ltxselement
name"week"gt ltxscomplexTypegt ltxsseque
ncegt ltxselement name"monday"
type"xsstring" /gt ltxselement
name"tuesday" type"xsstring"
/gt ltxselement name"wednesday"
type"xsstring" /gt ltxselement
name"thursday" type"xsstring"
/gt ltxselement name"friday"
type"xsstring" /gt lt/xssequencegt ltx
sattribute name"number" type"xsstring"
/gt lt/xscomplexTypegt lt/xselementgt lt/x
schoicegt lt/xscomplexTypegt
While the XML schema provides an abstract
definition of the structure and type of the data,
there are some incompatibilities when importing
into other packages. For example Microsoft Access
does not interpret the attributes tags. Also the
element tag is used to define a hierarchy.
37
Tutorial Session 1 Page 13 and 14
38
Introduction
Manipulate DataSet Relationships
Alistair Lawson
39
Along with creating simple data definitions, the
DataSet can also create tables which have a
linked relationship, in the same way that
databases created relationships. These
relationships are defined with keys and can
either be a one-to-one or a one-to-many
relationship. A primary key is one which
identifies uniquely identifies a row in a table
of a database.
40
Figure shows an example of three tables
(Suppliers, Products and Orders). In the
Suppliers table there is a primary key for the
SupplierID, each of which has a Supplier Name
(SupplierName), Address, City and Country. In the
Products table, there is a unique Products ID
(ProductID), a Supplier ID (which relates to the
ID in the Suppliers table), a Product Description
(ProductDescription), a Unit Price (UnitPrice)
and a field which defines if product is
discontinued (Discontinued).
41
To create a one-to-many relation, a record in one
table must match to another in another table. The
one which has a key on it will be defined as the
one, and other is the many. For example, each
supplier will have many products, thus we can
drag the SupplierID label from the Suppliers
table onto the Products one.
42
(No Transcript)
43
(No Transcript)
44
(No Transcript)
45
Show XSD
46
Along with one-to-many relationships, it is
possible to created a nested relationship (a
one-to-one relationship), where complex
abstractions can be simplified with a nested
structure. Figure shows an example of a table
named Order Details which contains the details of
an order, which is a one-to-one relationship with
an order. A nested relationship is more in-tuned
with the XML format of a nested hierarchy, with a
parent-child structure.
47
Tutorial Session 2 Page 20 and 21
48
Introduction
Create a strongly typed DataSet
Alistair Lawson
49
A key element to any programming language is the
way that it checks for errors in the code. This
is normally achieved with a strong compiler and
with a strongly object-oriented approach to code
development. Unfortunately, a developer cannot
normally check all of the required run time
options, such as whether a certain table in the
database exists. For example SqlDataAdapter
daEmployees new SqlDataAdapter() daEmployees.Fi
ll(ds.Employees) might fill up a data adapter
from the Employees table within the DataSet
(ds). Unfortunately a column might be accessed
incorrectly, through a spelling mistake or if it
has not been named correctly. Thus a strongly
typed allows for data to be bound at an early
stage. It has the following advantages
50

• The IntelliSense can be used to guide the
  developer as to the range of options which can be
  used.
• It is faster in operation.
• It improves syntax, where data structured objects
  are automatically created.

51
This can be achieved by opening the data source,
and dragging it into the solution. The basic
operation is 1. Open up the data source on the
server. 2. Drag the table onto the form. 3.
Create a dataset by selecting the data adapters,
and select Generate DataSet.
52
dsSupplier.cs. This contains the code to
interface to the strongly typed
data. dsSuppliers.xsx. This contains details on
the layout of the objects on the designer.
53
(No Transcript)
54
The strongly typed dataset object can be used by
filling up the dataset with the database values,
and then assigning it to a data grid, such
as private void Form1_Load(object sender,
System.EventArgs e) oleDbDataAdapter1.Fill(
dsSupplier1,"Suppliers") dgSuppliers.DataS
ource dsSupplier1 dgSuppliers.DataMember
"Suppliers"
55
If can be seen in Figure where the IntelliSense
has picked up the columns of the table (with
AddressColumn, CityColumn, and so on). The result
is shown next.
56
(No Transcript)
57
Tutorial Session 3 Page 25
58
SQL/.NET test http//buchananweb.co.uk/agilenttes
t.html
59
Introduction
Introduction to Databases
Alistair Lawson
60
Data Layer
Business Logic Layer
Presentation Layer
61
Data Layer This manages the data used by the
application. An application that stores its data
in data files is said to implement the data layer
itself. Many applications use a database to
manage the storage of data. In these cases, the
database itself is considered to be the data
layer of the application.
Data Layer
Business Logic Layer
Presentation Layer
62
Business Logic This layer builds on the data
layer, and contains the various rules and
operations that the application performs on its
data. For example, when a user runs a query on a
database, the data layer stores the order details
while the business logic layer performs all the
generation of the query in the correct format,
validates the parameters and ensures all the
relevant information is present.
Data Layer
Business Logic Layer
Presentation Layer
63
Presentation This layer builds on the business
logic layer and presents and manages the display
which enables the user to interact with the
system. Graphical user interfaces (GUI) and web
pages are typical examples of a presentation
layer.
Data Layer
Business Logic Layer
Presentation Layer
64
User Interface
DataGrid
ListBox
CheckBox
DataView
DataRow
Data Model
DataSet
XmlNode
BinaryReader
StreamReader
SqlReader
XmlDocument
SqlCommand
BinaryWriter
StreamWriter
Data Store
XmlTextReader
FileStream
FileStream
SqlAdapter
SqlConnection
Binary file
Text file
XML file
Database
65
Introduction
SQL Server Access
Alistair Lawson
66
Create Connection. SqlConnection. Create
DataAdapter. SqlDataAdapter. Run Query.
SqlCommand. Create Reader/Writer. SqlDataReader.
SqlConnection cnn new SqlConnection() Sq
lDataAdapter da new SqlDataAdapter() SqlComm
and cmd cnn.CreateCommand() cmd.CommandType
CommandType.Text cmd.CommandText
"SELECT LastName, FirstName FROM Employees
" "ORDER BY LastName" SqlDataReader dr
cmd.ExecuteReader()
67
System.Data.SqlClient
System.Data.SqlClient
SqlConnection
Close()
SqlCommand
ConnectionString
SqlDataReader
Open()
SqlDataAdapter
SqlError
SqlParameter
68
System.Data.SqlClient
System.Data.SqlClient
SqlConnection
Close()
SqlCommand
ConnectionString
SqlDataReader
Open()
SqlDataAdapter
SqlError
SqlParameter
69
Introduction
SQL Server Queries
Alistair Lawson
70
Ad-hoc query
SqlCommand cmd cnn.CreateCommand() cmd.Comm
andType CommandType.Text cmd.CommandText
"SELECT LastName, FirstName FROM Employees
" "ORDER BY LastName"
Stored Procedure
CREATE PROCEDURE procCustomerSales (_at_CustomerID
char(5), _at_TotalSales money OUTPUT ) AS SELECT
_at_TotalSales SUM(Quantity UnitPrice) FROM
((Customers INNER JOIN Orders ON
Customers.CustomerID Orders.CustomerID) INNER
JOIN Order Details ON Orders.OrderID Order
Details.OrderID) WHERE Customers.CustomerID
_at_CustomerID
SqlCommand cmd sqlConnection1.CreateCommand()
cmd.CommandType CommandType.StoredProcedure cm
d.CommandText "procEmployeeSales" cmd.ExecuteN
onQuery()
71
The main methods to conduct Ad-hoc queries
are - Visual Studio .NET IDE. - OSQL. - SQL
Query Analyzer. - ASP.NET Application
72
Introduction
Queries using the .NET IDE
Alistair Lawson
73
Filtering
Add New View
74
Filtering
Diagram pane
Grid pane
SQL pane
Results pane
Diagram pane. This contains the tables which are
to be used in the query. Grid pane. This the
columns which are to be used in the query. SQL
pane. This shows the SQL code which has been
generated for the query. Results pane. This shows
the results of the query, after it has been
executed.
75
Filtering
Diagram pane
Grid pane
SQL pane
Results pane
76
Filtering
Whitespace, tabs and newlines have no effect on
the command
dbo is the default owner of the database.
SELECT TOP 100 PERCENT ProductName,
SupplierID, CategoryID FROM
dbo.Products ORDER BY SupplierID
77
Filtering
SELECT CompanyName, City, Country FROM
dbo.Customers WHERE (Country 'GERMANY')
AND (City ltgt 'BERLIN')
78
(No Transcript)
79
(No Transcript)
80
(No Transcript)
81
(No Transcript)
82
private void Page_Load(object sender,
System.EventArgs e) sqlDataAdapter1.Fill(ds
Customers1,"vwGermanyNotBerlin") DataGrid1.Dat
aBind()
83
(No Transcript)
84
Introduction
SQL Server Queries using osql
Alistair Lawson
85
C\ gtosql /? usage osql -U login
id -P password -S server
-H hostname -E trusted connection
-d use database name -l login timeout
-t query timeout -h headers -s
colseparator -w columnwidth -a
packetsize -e echo input -I
Enable Quoted Id -L list servers -c
cmdend -D ODBC DSN name -q
"cmdline query" -Q "cmdline query" and exit
-n remove numbering -m errorlevel -r
msgs to stderr -V severitylevel -i
inputfile -o outputfile -p print
statistics -b On error batch abort -X1
disable commands and exit with warning -O
use Old ISQL behavior disables the following
ltEOFgt batch processing Auto console width
scaling Wide messages default
errorlevel is -1 vs 1 -? show syntax summary
86
1gt select companyname, city, country 2gt from
customers 3gt where (country'GERMANY') and
(cityltgt'BERLIN') 4gt go companyname
city country
----------------------------------------
--------------- --------- Drachenblut
Delikatessen Aachen
Germany Königlich Essen
Brandenburg Germany QUICK-Stop
Cunewalde Germany Lehmanns
Marktstand Frankfurt a.M.
Germany Ottilies Käseladen
Köln Germany Morgenstern Gesundkost
Leipzig Germany Blauer
See Delikatessen Mannheim
Germany Frankenversand
München Germany Toms Spezialitäten
Münster Germany Die
Wandernde Kuh Stuttgart
Germany (10 rows affected)
87
Introduction
SQL Server Query Analyzer
Alistair Lawson
88
The SQL Query Analyzer is available from the SQL
Server, and allows for multiple queries, an
object browser, performance analysis tools and
templates for common queries.
89
Introduction
Using a .NET Form for the Query
Alistair Lawson
90
Create command
Run command
Ad-hoc query
private void btnCommand_Click(object sender,
System.EventArgs e) SqlCommand cmd
sqlConnection1.CreateCommand() cmd.CommandType
CommandType.Text cmd.CommandText
txtQuery.Text SqlDataAdapter da new
SqlDataAdapter() da.SelectCommand
cmd DataSet ds new DataSet() da.Fill(ds,"Re
sults") dgResults.DataSource
ds dgResults.DataMember "Results" dgResults
.DataBind()
91
(No Transcript)
92
Introduction
SQL Server Queries
Alistair Lawson
93
The outline format of the SELECT statement
is SELECT from_field FROM from_table WHERE
clause GROUP BY clause HAVING clause ORDER BY
clause
94
SELECT from Employees
95
SELECT LastName,FirstName from Employees
96
Server should cast the return value as an integer
SELECT ProductName,UnitsInStock,UnitsOnOrder,
CAST (UnitsInStockUnitsOnOrder AS INTEGER) AS
Stock FROM Products
97
SELECT ProductName,UnitsInStock,UnitsOnOrder
FROM Products WHERE UnitsInStockgt50
98
SELECT ProductName,UnitsInStock,UnitsOnOrder
FROM Products WHERE UnitsInStockgt20 AND
UnitsOnOrdergt5
99
SELECT ProductName,UnitsInStock,UnitsOnOrder
FROM Products WHERE UnitsInStockgt20 OR
UnitsOnOrdergt5
100
SELECT Employees.EmployeeID, LastName, FirstName,
OrderID, CustomerID FROM Employees INNER
JOIN Orders ON Employees.EmployeeID
Orders.EmployeeID
101
SELECT FROM Products WHERE ProductName LIKE
'Ch'
102
SELECT FROM Products WHERE ProductName LIKE
'Cha_'
103
SELECT FROM Products ORDER BY ProductName DESC
104
SELECT FROM Products ORDER BY Len(ProductName)
ASC
105
SELECT ProductID, Sum(Quantity) AS
TOTALSALES FROM Order Details GROUP BY ProductID
106
Tutorial Session Page 38 and 39
107
(No Transcript)
108
Introduction
SQL Server Stored Procedures
Alistair Lawson
109
Ad-hoc queries are useful in allowing users to
directly interface their SQL commands to the
database, and in testing the query, but, in most
cases, a stored procedure is used to store the
query. These stored procedures are generic in
their nature, and can be reused in other
applications.
CREATE PROCEDURE procGermanyCustomersNotBerlin AS
SELECT FROM Customers WHERE Country
'Germany' and Cityltgt'Berlin'
110
CREATE PROCEDURE procGermanyCustomersNotBerlin AS
SELECT FROM Customers WHERE Country
'Germany' and Cityltgt'Berlin'
111
(No Transcript)
112
private void Page_Load(object sender,
System.EventArgs e) SqlCommand cmd
sqlConnection1.CreateCommand() cmd.CommandType
CommandType.StoredProcedure cmd.CommandText
"procGermanyCustomersNotBerlin" SqlDataAdapter
da new SqlDataAdapter() da.SelectCommand
cmd DataSet ds new DataSet() da.Fill(ds,"Re
sults") dgResults.DataSource ds dgResults.Dat
aMember "Results" dgResults.DataBind()
113
(No Transcript)
114
_at_parameter1 datatype default value, _at_parameter2
datatype OUTPUT
ALTER PROCEDURE procEmployeeSales ( _at_EmployeeID
int, _at_TotalSales money OUTPUT ) AS SELECT
_at_TotalSales SUM(Quantity UnitPrice) FROM
((Employees INNER JOIN Orders ON
Employees.EmployeeID Orders.EmployeeID) INNER
JOIN Order Details ON Orders.OrderID Order
Details.OrderID) WHERE Employees.EmployeeID
_at_EmployeeID
115
(No Transcript)
116
Each table must have a single entry column, which
contains a unique value which has been assigned
by the server. Examples of this include
EmployeeID in the Employees table, and OrderID in
the Orders table. The _at__at_INDENTITY can be used to
generate a unique entry for a new row.
117
_at__at_IDENTITY
ALTER PROCEDURE procInsertEmployee ( _at_NewEmploy
ee char(20), _at_EmployeeID int OUTPUT ) AS INSER
T INTO Employees (LastName, FirstName) VALUES
(_at_NewEmployee, 'Fred') SELECT _at_EmployeeID
_at__at_IDENTITY RETURN
118
(No Transcript)
119
Tutorial Session Page 45
120
Cram Session
121
DataSets are made up of XML elements. Default
file extension for XML schema is .xsd XML schema
represents metadata that describes a DataSet
object's allowable content Creating XML schema
Drag and drop elements into the DataSet schema
designer. Simple types allow constraints on the
data in the DataSet. Schema created from an
existing table by dragging it into the DataSet
schema designer. ADO.NET Contains data-specific
data provider classes and database-independent
DataSet classes
122
SqlConnection Object provides the connection to
a database. SqlCommand Object represents the
command to be executed on a database
server. SqlParameter Object supports the
passing of a parameter to a stored procedure in a
database. SqlDataReader Object is a fast way to
retrieve a resultset from a command passed to a
database. SqlDataAdapter Object supports a
two-way pipeline between a database and the data
model DataSet Object represents the structure
and data of a relational database in
memory. DataView Object is used to provide a
filtered row of data from a DataTable.
123
DataTable, DataRelation, DataRow and DataColumn.
objects are contained within the DataSet
object. SQL statements are case-insensitive. Run
ning queries Within the Visual Studio
enviroment Using OSQL Using SQL Query Analyser
Using an ASP.NET application SELECT FROM
EMPLOYEES access all the columns in the
Employees table. dbo part of dbo.Employees. dbo
is the default database owner. Why are square
brackets used When a table name has spaces in
it. Microsoft SQL Server dialect used to
implement the ANSI SQL-92 language Transact-SQL
124
SELECT statement is used to retrieve data from
tables in a database. INSERT statement is used
to add data from tables in a database. UPDATE
statement is used to modify existing data from
tables in a database. DELETE statement is used
to remove data from tables in a database. WHERE
SQL clause is used to restrict the output of an
SQL statement. HAVING restricts the rows used
as input to an aggregate in an SQL
statement. Stored procedure Most prefer method
of invoking a query on a database.
125
_at__at_INDENTITY variable Create a unique value for a
primary key. First line of a stored procedure
which is named "MyProc CREATE PROCEDURE
MyProc use Northwind OSQL is used to load the
Northwind database. go OSQL is used to run a
query. OSQL command-line utility which is used
to run SQL queries. XML element Standalone XML
entity.
126
XML attribute further defines an
element. Simple data type Restriction in a data
range between 0 and 100 be set in the XML schema
designer. Facet in XML piece of information
describing an XML simple type. When is a
one-to-many relationship most useful in an XML
schema Used with a relational database. Differen
ce between ad-hoc queries and stored procedures
Ad-hoc queries are sent to the server, but stored
procedures are stored on the server.
127
Namespaces including in the ADO.NET model
System.Data System.Data.SqlTypes System.Data.SqlC
lient System.Data.OleDb