|
|
 |
|
|
|
Programming Microsoft® SQL Server™ 2000 with Microsoft Visual Basic® .NET
|
|
|
Author |
|
Rick Dobson
|
|
|
Pages |
704
|
|
Disk |
1 Companion CD(s)
|
|
Level |
Int/Adv
|
|
Published |
05/29/2002
|
|
ISBN |
9780735615359
|
|
ISBN-10 |
0-7356-1535-7
|
|
Price(USD) |
$59.99
To see this book's discounted price, select a reseller below.
|
|
|
|
|
|
|
|
|
Index
A
Access, Microsoft
data provider for, 10-11
views using as data source, 122-25
accounts. See login security accounts
Active Scripting, 485
AddHandler statements, 327, 336-41
administration
Administrator account, 239
databases, by Command objects, 390-93
SqlConnection objects for, 359
ADO.NET
classes, six basic, 358
Command objects. See SqlCommand class
Connection objects. See SqlConnection class
data providers, 11-16, 358
data sets. See DataSet class
DataAdapter objects. See DataAdapter class
data-aware forms, 408-18
DataReader objects. See DataReaders
defined, 9-10
graphical object creation, 483-84, 489-90
optimistic concurrency, 362-63
stored procedures, invoking with parameters, 385-93
views. See DataView class
Web pages, object creation for, 503
Web server controls with, 484-89
Windows Forms, connecting to, 373-77
AFTER triggers, 177
aggregation
calculated columns, 91-92
COMPUTE clauses, 80-81
conditional result sets using, 149
COUNT function, 88-89, 93-96
dates, by, 93-96
DISTINCT keyword, 89
functions, table of, 88
grouping with, 79-80, 89-92
quarters of years, 98
standard deviations, 88
SUM function, 98
views for, 111
without grouping, 88-89
XML templates with, 228-30
aliases
Imports statements creating, 300-301
inner joins using, 101-2
self-joins using, 106
ALTER FUNCTION statements, 158
ALTER PROCEDURE statements, 129, 142
ALTER TABLE statements
computed columns using, 60
table constraints using, 63
ALTER TRIGGER statements, 179
ALTER VIEW statements, 112
annotated schemas
attributes, 543
creating graphically, 542-44
creating with text, 541-42
data sets created using, 566-70
defined, 205
designing, 540-44
field attribute, 205-6
formatting documents with, 538-40
joins using, 208-9
linking to row sources, 205-6
Microsoft mapping namespace, 205
namespaces for, 205
purpose of, 204, 533
querying with XPath, 207-10
relation attribute, 205-6
relationship elements, 209
returning documents based on databases, 207
sequence elements, 538
storing, 541
syntax for referring to, 207
tables as sources for, 542-44
templates to invoke, 207, 223
XML Designer, 541-42
XML documents, creating with, 538-40
XPath queries with, 538-40
Application objects, ASP.NET, 278, 411
Application property of Page class, 439
application roles, 244
Application variables, 463-64
approximate data types, 42-43
architecture of .NET Framework, 268-70
archiving changes to tables, 183-86
arrays, result sets in, 618
.asmx files, 583
ASP.NET
advantages of, 435
Application objects, 278
ASP, compared to, 276-78
Code Editor with, 280
code-behind file system, 439-40
ASP.NET,
compiled code, advantages of, 277-78
controls, adding to pages, 279, 440-41
controls, types of, 440-41. See also Web server controls
creating applications, 278-79
data manipulation from, 498-509
default application location, 278
defined, 276
deleting solutions, 278
design interfaces, 443-47
design issues, 436-43
development environment, 277
DiffGrams for database updates, 570-72
folders, 440, 444
form tags, 279
HTML Designer, 443, 445
IIS with, 276. See also IIS
layout properties, 279
migrating to, 277
Page class, 439-40
@Page directives, 443
pageLayout property, 279
Page_Load event procedure, 439
problems solved by, 277
project creation, 440
Redirect method, 462
requirements, 276
round-trip paradigm, 436-37
runat setting, 279
Session objects, 278
Session variables, 463-64, 466-71
storing data, 482-83. See also session state management
Toolbox, adding controls from, 279
view state, 465
Visual Basic .NET for, 277
Web Service template, 7, 583, 590
ASP.NET Web Application template, 7
ASP.NET Web Service template, 7, 583, 590
.aspx files, 279, 439
assemblies
attributes, viewing in Visual Studio .NET, 287
components of, 272-73
default location for Windows applications, 286
defined, 271, 272
manifests, 272
metadata, 272
attaching databases to server instances, 35
attributes, XML
attribute-centric format, 217
defined, 198-99
field, 205-6
querying for, 546
relation, 205-6
representing, 199-200
XSD, 202, 203
authentication, 24-25, 239-40
authorization. See permissions
AUTO mode argument, FOR XML clause, 214-21
autoincrementing series, 61
AutoPostBack property, ListBox, 485-86
averages, AVG function, 88
B
base classes, 314, 315, 318, 321
bcp facility, 195
BEGIN...END blocks, 149, 159, 162
bigint data type, 40-41
BINARY BASE64 argument, FOR XML clause, 215, 218
BINARY CHECKSUM function, 88
binary data type, 44-45
binding
BindingContext objects, 416, 428-29
data to controls, 20-21, 396-402
DataBindings collection, 396-402, 431
removing, 431
bit data type, 45-47
body tags, 444
BoundCheck compiler constant, 310
browsers
Browser object, 461-63
comparison of Microsoft and Netscape, 460-61
detecting types of, 438
discerning versions of, 461-62
Microsoft Internet Explorer. See Internet Explorer
Netscape, 457-58, 460-62
round-trip paradigm, 436-37
sniffing, 461-63
state maintenance, 438
version differences, 438
Build and Browse command, 586, 594
BULK INSERT statements, 195
bulkadmin role, tasks of, 241
business rules, enforcing with triggers, 186-91
buttons
Click event, raising, 326-27
navigation with, 21-23
query strings with, 474
visibility, 320
Windows Forms, 290-92
C
cabxli server, 121
calculated columns
aggregation of, 91-92
defined, 85
inner joins, using, 103-4
specifying, 85-86
calculations, creating classes for, 297-99
Calculator example, 453-61
Cancel event arguments, 330-31
cascading style sheets, 572
cascading updates, 68-69
CAST function
calculated columns with, 85-86
within CONVERT functions, 92
defined, 38
sql_variant data types, 58
Catch clauses, 348-55. See also exception handling
char data type, 37-38
character data, 37-38
check constraints, 62-64
CHECKSUM function, 88
ChildRelations property, DataTable objects, 365
children. See hierarchical data; relationships
CInt function, 309
Class Library template, 7, 297
class references
Add Reference command, 301
.dll referencing, 300-301
example of, 302-3
importance of, 296
from Windows applications, 299-303
classes
base, 314-15, 318, 321
calculations referencing, 297-99
creating, 297-99
derived, 314-15, 318, 321
DLLs, compiling into, 297
function procedures in, 298
inheritance. See inheritance
instantiating, 301
referencing. See class references
Click events, 326-27, 335
client applications
Web services. See Web service client applications
Windows. See Windows Forms
client-side scripting, validation controls with, 514
Close buttons, 330-31, 410-11, 415
Close events, 328
Closing events, 328, 330-31
CLR (Common Language Runtime). See runtime, .NET Framework
clustering indexes, 64, 65, 73, 75
Code Editor, Visual Studio, 280, 289
columns
binding to controls, 398
calculated. See calculated columns
COLUMNS view, 52-53
computed, 60-62, 143
constraints, 63
creating with CREATE TABLE, 49-51
DataColumnCollection class, 364
datetime data type, 55-57
IDENTITY property, 61
multiple data types in single, 57-60
null, allowing, 49, 60
rowversion data type, 55-57
specification with SELECT statements, 81-82
sql_variant data type, 57-60
timestamp data type, 45, 55-57
values, retrieving with DataReader, 361
COM (Component Object Model), 275
combo box controls
binding data to, 398-99
filtering rows with, 402-3
property assignment to DataTable objects, 414
Command class, ADO.NET
CommandText property, 359, 389
CommandType property, 359, 389
DataAdapter class SelectCommand property, 394
database object administration, 390-93
DataSets, populating, 395
displaying results from, 378-82
ExecuteNonQuery method, 360, 391
ExecuteReader method, 360, 378-79, 391
ExecuteScalar method, 360
linking to Connection objects, 378
parameters, 360, 424-25
purpose of, 359
SqlCommand objects representing, 359
stored procedures, invoking, 385-90
TableDirect property, 360
Web pages using, 496-98
common language runtime. See runtime, .NET Framework
CompareValidator control, 510
compiling
BoundCheck compiler constant, 310
Build and Browse command, 586, 594
Build Solution command, 292
classes into dlls, 297
conditional, 310-13
.NET Framework programs, 271
Start command, Debug menu, 292
Web applications, 448-49
complex data binding, 396
Component tray, 342
COMPUTE clauses, 80-81, 90, 113
computed columns, 60-62, 143
concurrency, 362-63
conditional compilation, 310-13
conditional result sets, 148-53
configuring Visual Studio .NET, 5-6
Connection class, ADO.NET
defined, 358-59
graphical creation of, 483-84
integrated security logins, 367-69
linking to Command objects, 378
SQL Server security logins, 369-70
Connection class, ADO.NET,
SqlConnection objects. See SqlConnection class
Windows Forms, with, 373-77
connection strings
Access views, 122-23, 125
ConnectionString argument, SqlConnection, 368
ODBC sources, 123-24
passwords, 370
SQL Server security logins, 369-70
SqlConnection objects using, 12, 359
SqlXmlCommand class with, 526, 535-37
userids, 370
views with remote sources, 120-21
connections
closing, 359
Connection objects. See Connection class, ADO.NET
databases within, metadata view, 52
exceptions, 371-76
importance of, 366-67
integrated security logins, 367-69, 373-77
opening, 359
Query Analyzer, creating with, 24-25
SQL Server security logins, 369-70, 373-77
SqlConnection objects. See SqlConnection class
timing out, 359
Windows Forms, from, 373-77
Console Application template, 7, 8
console applications, 8-9
Constraint objects, DataTable objects, 365
constraints
check, 62-64
column, 63
dropping, 64
foreign keys, adding as, 69
naming, 63
table, 63
constructor statements, 11-12
control-of-flow statements, 139-41
controls
binding data sets to, 20-21, 396-402
button controls. See buttons
DataBindings collection, 396, 398, 399-402, 431
HTML. See HTML controls
list boxes, 466-71, 483-98, 620-22, 626-30
properties, binding to DataSets, 396
properties, setting during runtime, 293-94
radio buttons, 348, 373-76, 440, 442
text boxes. See text box controls
validation. See validation controls
visibility, 320
Web. See Web server controls
Windows. See controls, Windows Forms
controls, Windows Forms
buttons, 290-92
event handler stubs, 327-28
event procedures with, 289
RadioButtons, 348
text boxes. See text box controls
Web services for populating, 623-33
CONVERT function, 38, 92
converting types
CAST function, 38, 58, 85-86, 92
CInt function, 309
CONVERT function, 38, 92
CType function, 309
function for, 309
hexadecimal, conversion to, 310-13
implicit, 38, 308
Long type, 310-11
Option Strict On, 309
cookies, 464-65
correlated subqueries, 107-8
COUNT function, 88-89, 93-96
CREATE DATABASE statements, 36, 60-62
CREATE FUNCTION statements, 158-60, 162, 174-75
CREATE PROCEDURE statements, 128, 131-32
CREATE TABLES statements, 49-51
CREATE TRIGGER statements, 178-80, 181
CREATE VIEW statements, 112, 114-16
creating solutions, 6
cropping date strings, 62
cross joins, 106-7
cryptography, 613
CType function, 309
CURRENT_USER function, 253
cursor data types, 47
custom data types, 47-48
CustomValidator control, 510
D
Data Adapter Configuration Wizard, 17-18
data adapters. See DataAdapter class
Data controls, 441
data grid controls. See DataGrid controls
data providers
ADO.NET, 11-16, 358
advantages of .NET, 10-11
classes, main, 11
Data Adapter Configuration Wizard, 17-18
data sets. See DataSet class
Jet 4.0 databases, 10-11, 122, 124-25
linked servers with, 244
managed, 269
MSDASQL, 123
ODBC, 10, 11, 123
OLE DB .NET, 10-11
sample application, 16-23
SQL Server .NET, 10, 358
SqlClient namespace, 11
SqlCommand objects. See SqlCommand class
SqlConnection objects. See SqlConnection class
SqlDataAdapter objects. See SqlDataAdapter class
SqlDataReader objects. See SqlDataReader class
sqloledb, 120-21, 526, 536
SqlXmlAdapter class, 523, 529
SqlXmlOleDb with, 196
types provided, 10
data sets
DataSet objects. See DataSet class
generating with Data Adapter Configuration Wizard, 18-19
graphical creation of ADO.NET object, 489-90
strongly typed, 533
Web page navigation of, 489-94
data types, .NET Framework. See types, .NET Framework
data types, SQL Server
approximate, 42-43
bigint, 40-41
binary, 44-45
bit, 45-47
char, 37-38
character data, 37-38
class behavior of, 296
converting. See CAST function; CONVERT function
cursor, 47
custom, 47-48
date and time group, 43-44
datetime, 44, 55, 86-87
decimal, 41-42, 43, 86
decimal data category, 41-42
float, 42-43
homogenous groups, list of, 37
image, 44-45
importance of specifying, 36-37
int, 40-41
integer data, 40-41
monetary, 43
money, 43, 86
nchar, 38-40
ntext, 38-40
numeric, 41-42
numeric data group, 40-43
nvarchar, 38-40
real, 42-43
rowversion, 55-57
smalldatetime, 44
smallint, 40-41
smallmoney, 43
special types, 45-47
sql_variant, 45-47, 57-60
stored procedure variables, 130
table, 47, 156
text, 37-38
time group, 43-44
timestamp, 45-47, 55-57
tinyint, 40-41
Unicode, 38-40
uniqueidentifier, 45-47
user-defined, 47-48
varbinary, 44-45
varchar, 37-38, 133
XSD (XML Schema Definition), 202
data types, Visual Basic .NET. See types, .NET Framework
DataAdapter class
Data Adapter Configuration Wizard, 17-18
DataGrid controls, populating, 487-88
DeleteCommand property, 362, 423, 425-26
Fill method, 362, 395
graphical creation of, 483-84
InsertCommand property, 362, 423, 425
optimistic concurrency, 362-63
purpose of, 361
SelectCommand property, 394-95
source specification, 423-24
SqlCommand objects with, 362
SqlConnection objects required, 362
SqlDataAdapter objects. See SqlDataAdapter class
SqlXmlAdapter class, 523, 529
Update method, 362, 429-30, 505, 566-68
UpdateCommand property, 362, 423-25
Windows Forms, using with, 393-96
data-aware form creation, 408-18
database objects, creating from Web pages, 494-98
Database property, SqlConnection, 359
DataBindings collection, 396, 398, 399-402, 431
DataColumn objects, 364
DataColumnCollection class, 364
DataGrid controls
DataSource properties, 484-85
DataView objects, assigning to, 405
populating, 487-88
updating, 407
Web forms using, 483-98
Web services, populating with, 626-30
Windows Forms using, 402-8
DataReaders
closing connections for, 361, 381
column values, retrieving, 361
Connection object required, 360-61
displaying results of, 378-82
DataReaders,
displaying rows in blocks, 382-85
extracting rows, 378-80
Get methods, 361
instantiating, 361
number of records selected, returning, 361
populating from Command class, 360-61
position in, 361
purpose of, 360
Read method, 361, 378-80
SqlDataReader class, 361
stored procedures, populating with, 385-90
DataRelation objects, 365, 555-56
DataRow objects, 364-65, 492
DataRowCollection class, 364-65
DataSet class
annotated schemas with, 566-70
binding controls to, 19-20, 396-402
Command objects to populate, 395
DataAdapters for populating, 393-96
data-aware form creation, 408-18
DataBindings collections of forms, 396, 398-402
DataColumn objects, 364-65
DataGrid controls, populating, 402-8, 487-88
DataRelation objects with, 555-56
DataRow objects, 364-65
DataTable objects with, 15-16, 364, 395, 402-8
defined, 14, 363
deleting with, 365, 420-22, 433-34
descendants, XPath queries of, 560-62
DiffGrams for updates, 566-70
disconnected operation of, 395
filling, 19-20
function of, 14-16
hierarchical XML document creation, 554-60
inserting with, 420-22, 430-32
Load events, filling on, 20
multiple data sources with, 14, 402-8
object model, 364
objects, defined, 14
parent-child relationships of, 402-8
previewing data, 19
row navigation, 21-23
scalability of, 14
Update method, 365, 505
updating data with, 420-21
Web services using, 589, 627
Windows Forms, adding to, 393-96
WriteXml method, 554-58
XML basis of, 554
XML documents behind, 563-65
XmlDataDocument class, 561
XmlDocument class, 561
DataSource property, SqlConnection, 359
DataTable collections. See DataTableCollection class
DataTable objects
binding to controls, 396-402
ChildRelations property, 365
Constraint objects, 365
data grid controls with, 15-16, 364, 395, 402-8
data-aware form creation, 408-18
DataColumn objects, 364-65
DataColumnCollection class, 364
DataRelation objects, 365
DataRow objects of, 364-65, 407-8
DataView class for viewing, 366
defined, 364
Delete method, 433
deleting with, 365, 420-22, 433-34
finding rows, 433
inserting with, 420-22, 430-32
multiple data sources with, 402-8
parent-child relationships of, 402-8
ParentRelations property, 365
PrimaryKey property, 364
Remove method, 433
row navigation, 419-20
updating data with, 420-21, 429-31, 505
DataTableCollection class
defined, 364
designating, 20
purpose of, 15-16
DataView class
DataGrid controls using, 487-88
declarations, 404, 405, 412
filtering rows, 366, 405, 407, 414
finding rows, 366, 433
purpose of, 366
Sort property for, 366
date and time data types, 43-44
DATEADD function, 97
DATEDIFF function, 86-87, 97-98, 165
DATEPART function, 93-96
dates
arithmetic with, 96-98
counting by month, 94-96
counting by year, 93
cropping time from, 62
data types for. See date and time data types; datetime data type
DATEADD function, 97
DATEDIFF function, 86-87, 97-98
DATEPART function, 93-96
difference between, computing, 86-87
months, listing by name, 95-96
parts of, table of, 94
quarters of years, 98
datetime data type
arithmetic with, 96-98
DATEDIFF function, 86-87
DATEPART function, 93-96
defined, 44
timestamp, compared to, 55-57
DBCC CHECKIDENT statements, 227
dbcreator role, 241
db_ fixed database roles, table of, 242
dbo user, 258-59
DBTS function, 130
debugging
stepping through code, 367
T-SQL code. See Query Analyzer
decimal data category, 41-42
Decimal data type, 41
decimal data type, 41-42, 43, 86
DECLARE keyword, 130
Delete method for rows in DataTables, 365
DELETE permissions, 257-58
DELETE statements
stored procedures, using in, 144-45, 147-48
XML templates with, 227
deleted tables of triggers, 178, 183-84
deleting
archiving changes to tables, 183-86
protecting against, 180-83
rows, 433-34, 503-5
Updategrams for, 231, 234
DENY statements, 243, 261-62
denying login permission, 254-55
deployment
.NET Framework, 273
Web services, 599-602
derived classes, 314, 315, 318, 321
descendants, querying, 560-65
Designer
HTML. See HTML Designer
Windows Forms. See Windows Forms Designer
determinism, 157
development environments. See Visual Studio .NET
dialog boxes, Windows Forms, 289
DiffGrams
automatic use of, 530
before section, 530
database updates with, 566-70
DataInstance section, 530
declarations, 530
defined, 529
direct manipulation of, 566
generating from DataSets, 554
SqlXmlCommandType execution of, 528
viewing, 568-69
Web applications with, 570-72
Dim statements, 301
directories, displaying for projects, 9
diskadmin role, 241
DISTINCT keyword, 89, 106
dlls
compiling classes into, 297
hell, 272
referencing, 300-301
.dlls, classes, creating for, 297
Double data type, 41, 304
DROP FUNCTION statements, 158
DROP INDEX statements, 73
DROP PROCEDURE statements, 128-29
DROP TABLE statements, 144, 178
DROP TRIGGER statements, 178-80
E
elements, XML
attributes. See attributes, XML
defined, 198
element-centric format, 217
minOccurs attribute, 202
relationship elements, 209
XSD schemas, 201-2
Empty Project template, 7
Empty Web Project template, 7
encryption
.NET Framework, 613
UDFs, ENCRYPTION attribute, 160
views, ENCRYPTION attribute, 113, 116-19
ERROR function, 130
error messages, custom, 350-51. See also exception handling
errors, run-time. See exception handling; run-time errors
event handlers
AddHandler statements, 327, 336-41
defined, 327
dynamic creation at runtime, 327
events, relation to, 327
Handles clause, 330
invoking inherited methods, 318-19
Load events, 331-34
RemoveHandler statements, 327
shadowing in, 322-23
stubs, 327-28, 335
subprocedures, 330
syntax for, 330
Windows Forms events, 289, 290-92, 328-31
WithEvents keyword, 327, 334-36
event procedures. See event handlers
events
AddHandler statements, 336-41
built-in Windows Forms, 328-31
Click, raising, 326-27
Click, stub generation, 335
Close, 328
Closing, 328, 330-31
declaring, 327, 334
defined, 326
handlers. See event handlers
limits to raising, 327
Load, 331-34
Mouse events, 329
order of, Windows Forms, 328-30
processing. See event handlers
Public keyword, 333
RaiseEvent statements, 327
raising, 333-34
Server timers, 342-46
sources of, 326-27
stubs, 327-28, 335
triggering, 327
WithEvents keyword, 327, 334-36
exception handling
best practices for, 355
custom error messages, 350-51
defined, 347
fixing errors automatically, 352-55
optimistic concurrency, 363
overview of, 347-48
Try...Catch...Finally statements, 347-48, 350-51, 353-55
exceptions
connections, 371-76
custom, 348
handling. See exception handling
listing all, 347
overflows, 352-55
SqlConnection class, 371-76
Throw statements, creating with, 348
thrown by run-time errors, 347-48
EXEC statements. See EXECUTE statements
EXECUTE statements, 129, 131-32
ExecuteNonQuery method, 13
ExecuteScalar method, 13
ExecuteXMLReader method, 14
existence, testing for, 50
F
field attribute, 205-6
FileStream objects, 527, 536
Finally clauses, 347-48. See also exception handling
firehose delivery model, 14
fixed database roles, 241-42
fixed server roles, 240-41
float data type, 42-43
FOR XML clause
arguments required, 213-14
AUTO mode argument, 214-21
BINARY BASE64 argument, 215, 218
binary data with, 218-19
blank spaces, 215
client-side processing, 220-21
column names, 220
ELEMENTS argument, 214-15, 217
ExecuteXMLReader method, 14
EXPLICIT mode argument, 214-15
GROUP BY clause with, 221-22
introduction of, 195
joins using, 216-17
mode arguments, 213-14
multiple SELECTs per connection, 222
NESTED mode argument, 214, 220-22
optional arguments for, 214-15
overview of, 213-15
RAW mode argument, 213-16
root element specification, 215
root-element, adding, 535
row identifiers, 220
spaces, 222
syntax, 213, 215
table names as identifiers, getting, 215
views with, 220-22
XMLDATA argument, 215
XPath queries with, 548-50
foreign keys
adding to tables, 68-69
cascading updates, 68-69
Constraint objects, DataTable objects, 365
constraints, adding as, 69
constraints on computed columns, 60-62
defined, 68
relationships, designating, 69
Form class, 285. See also Windows Forms
form tags, 444, 472-73
forms. See Web forms; Windows Forms
FROM clauses, 78-79, 82, 100, 103
full outer joins, 104
function procedures
defined, 298
overriding inherited methods, 315
read/write property specification, 306
UDF similarity to, 156
functions, stored procedure, 130
functions, user-defined. See UDFs
G
GAC (Global Assembly Cache), 272-73
garbage collector, 274
Get clause, 298
GETDATE function
calculated columns, 87
computed columns, 60-62
global variables, 130
GO keyword, 50, 162
GRANT statements, 243, 256-57, 259
GraphicalDataBind example, 16-23
group accounts, Windows, 253-55, 261-63
GROUP BY clauses
COUNT function with, 89-92
defined, 79-80
FOR XML clause, support for, 214, 221-22
XML templates with, 228
grouping
data in result sets, 79-80, 89-92
views, data within, 119-20
GROUPING function, 88
guarding table contents with triggers, 180-83
guest user account logins, 367-69
GUIDs, 45-47
H
handlers. See event handlers; exception handling
Handles clause, 330
HAVING clauses, 80, 91-92
head tag block, 444
heterogeneous data source views, 124-26
hexadecimal, conversion to, 310-13, 349-50
hidden fields, 465
hierarchical data
DataSets with, 402-8
DataTable children, 365
XML document creation, 554-60
XPath queries of data sets, 560-62
XPath queries of XML documents, 563-65
HTML controls
adding to forms, 440
disadvantages of, 442-43
MS_POSITIONING attribute, 444
positioning with tables, 458
Radio Buttons, 440
server controls. See HTML server controls; Web server controls
state maintenance, 442
types of, 441
validation controls, 442
HTML Designer
Design view, 445
FlowLayout mode, 444, 458
grid, 445-46
invoking, 443
layouts, copying, 471-72
pageLayout property, 444-46, 458
positioning controls with, 444
toolbars, 445-46
viewing code, 446
HTML method attribute, 441
HTML server controls
adding to forms, 440
advantages of, 442-43
attribute settings, 446
control syntax, 440
designating, 440
ID attribute, 440
MS_POSITIONING attribute, 444
positioning, 444-45
Radio Buttons, 440
state maintenance, 442
syntax, 443
validation controls, 442
HTML tags, 443-44
HTTP (Hypertext Transfer Protocol)
access to SQL Server, provision for, 194
headers, 474
hyperlinks, 494-98
I
IDENTITY function, 130, 162
IDENTITY property
defined, 61
with INSERT statements, 143
primary keys requiring, 64
IF EXISTS statements, 50
IF...ELSE statements
BEGIN...END blocks, 149
referencing UDFs in, 167-68
syntax, 139-41
TOP predicate, returning X items, 151-52
IIS (Microsoft Internet Information Server)
ASP.NET with, 276
caching options, 617
formatting rowsets by, 205
management of virtual directories, 211-13
naming virtual directories, 615
.NET Framework, relationship to, 269-70
soap objects, 625-26
Soap Virtual Name Configuration dialog, 615
SQL Server user designation, 613-14
stored procedures, exposing, 615
user-defined functions, exposing, 615
virtual directories for XML, 194, 195, 204-5
virtual directory creation, 614-17
IIS (Microsoft Internet Information Server),
Virtual Directory Management tool, 211-13, 589, 614-17
Web Services Toolkit with, 588-89, 612-17
image data type, 44-45
Imports statement, 275, 300-301
indexes
ON clauses, 72-73
clustering, 64, 65, 73, 75
creating, 72-73
defined, 64
design issues, 75
dropping, 73
limiting number per table, 64
overhead due to, 71-72
programmatic changes in, 72-73
sp_helpindex, 72
sysindexes table, 72, 73
table names, returning for, 72
testing, 75
unique, creating, 73
of views, 111, 113
INFORMATION_SCHEMA views
INFORMATION_SCHEMA.ROUTINES view, 131
INFORMATION_SCHEMA.TABLES view, 115
INFORMATION_SCHEMA.VIEWS view, 114-15, 118, 133-34
metadata, obtaining with, 52-54
stored procedures using, 133-34
UDF detection, 158, 162
inheritance
advantages of, 313
ArithmeticClass example, 316-20
base classes, 314, 315, 318, 321
code units needed for, 316
defined, 313
derived classes, 314, 315, 318, 321
Inherits statement, 314, 317
instantiation of classes, 316, 318
methods, changing in derived classes, 314-15
MyBase keyword, 315, 318
New method in base classes, 321
Overridable keyword, 314-15, 317-18
Overrides keyword, 314-15, 318
polymorphism, 314-15
Shadows keyword, 315, 321-23
Inherits statement, 314, 317
inline schemas, 558
inline table-valued UDFs
advantages of, 161, 169
containing scalar UDFs, 170-74
creating, 157-59
defined, 156
invoking, 169-70
parameters for, 169-70
inner joins
aliases within, 101-2
calculated columns with, 103-4
defined, 99
ON keyword for, 99-100
result sets, 100
specifying, 100
syntax for, 99
three-table joins, 102-4
inner queries, 107-8
input parameters. See also parameters
of stored procedures, 127, 146-48
of UDFs, 157, 158-59, 164
INSERT INTO statements
stored procedure with, 419
syntax, 50
XML templates with, 227
INSERT permissions, 257-58
inserted tables of triggers, 178, 183-84
inserting
archiving changes to tables, 183-86
permissions for, 257-58
protecting against, 180-83
rows, 143-47, 231-35, 420-22, 430-32, 503
stored procedures for, 141-43, 146-47
Updategrams for, 231-35
into views, 142
INSTEAD OF triggers, 178, 187, 189
int data type, 40-41
integer data types, 40-41
integrated security logins, 367-69
Internet Explorer
Active Scripting option, 485
comparison with Netscape, 460-61
recommended, 460
validation controls with, 511
Internet Information Server. See IIS
interoperability of .NET, 269, 583
IsPostBack property, 439, 452, 487
IsValid property of Web pages, 511, 514-17
J
Jet 4.0 databases
data provider for, 10-11
views of, 122
JIT (Just-In-Time) compilers, 271
jobs, 249
joins
annotated schemas for, 208-9
cross, 106-7
defined, 99
DISTINCT keyword, 106
FOR XML clause with, 216-17
heterogeneous data source views, 124-26
inner. See inner joins
outer, 104-5
self, 105-6
views with, 110, 124-26
JScript .NET, 270
K
keys
foreign. See foreign keys
metadata of, displaying, 53
primary. See primary keys
keyset cursors, 362
L
LEFT function, 62, 88-89
left outer joins, 104
LIKE operators, 84-85
linked servers, 244-45
list boxes. See ListBox controls
ListBox controls, 466-71, 483-98, 620-22, 626-30
ListToGrid example, 483-98
Load events
filling data sets on, 20
using for form settings, 331-34
Windows Forms, 289
Local projects, advantages of, 7
localhost, 448
login security accounts
adding with sp_addsrvrolemember, 240-41
creating with sp_addlogin, 247-48
creating with sp_grantlogin, 251-52
creating with Windows GUI, 592
defined, 238
denying permissions to, 254-55
integrated security, 367-69, 373-77
listing, 253
object ownership transfers, 260
passwords, changing with sp_password, 247
query string example, 478-81
removing, 249-52, 255, 260
SIDs, 248
SQL Server security, 369-70, 373-77
SYSTEM_USER function, 253
Windows group accounts, 253-55, 261-63
Windows user accounts, 251-52
Long type, 310-11
M
managed code, 270
MapPath function, 571
MAX function, 88
MaximizeBox controls, 415
MDAC, 276
Me keyword, 400
memory management, 274
MemoryStream objects, 527, 551
merges. See inner joins
message boxes
DataReader results, displaying in, 378-85
displaying with event procedures, 291-92
modal nature of, 294
metadata
assemblies, 272
columns in tables, 52
databases within connections, 52
keys, displaying, 53
.NET Framework, 271
viewing, 51-54
VIEW_METADATA attribute, views, 114
methods. See also specific methods
inheritance of. See inheritance
overloading, 315
shadowing, 322
Microsoft Access. See Access, Microsoft
Microsoft Intermediate Language (MSIL), 271, 292
Microsoft Internet Explorer. See Internet Explorer
Microsoft Internet Information Server. See IIS
Microsoft mapping namespace, 205
Microsoft SOAP Toolkit, 524
Microsoft.Data.SqlXml namespace, 535
migrating databases, 35
MIN function, 88
MinimizeBox controls, 415
Mixed Mode, 239-40
modal forms, 292-96
modeless forms, 292-96
Module window, 8
monetary data types, 43
money data type, 43, 86
months, counting by, 94-96
Mouse events, 329
MSDASQL data provider, 123
MSIL (Microsoft Intermediate Language), 271, 292
MS_POSITIONING attribute, 444
multistatement table-valued UDFs, 156, 158-60, 174-76
MyADODOTNETSamples, 358
MyBase keyword, 315, 318
N
namespaces
annotated schemas, 205
conflicts between, 300-301
Imports statement, 275
namespaces,
Microsoft mapping namespace, 205
Microsoft.Data.SqlXml, 535
.NET Framework, 274-75
prefixes, preferring, 526
reference types in .NET Framework, 304
SqlClient, 11
System, 304
templates, XML, 224
types, .NET, 303-4
Windows forms, 288
XML, 198
XSD, 201
NameValueCollection objects, 474, 477
navigating data sets on Web pages, 489-94
navigation bars, buttons for, 21-23
nchar data type, 38-40
nested SELECT statements, 107-8
.NET data providers. See data providers
.NET Framework
architecture of, 268-70
assemblies. See assemblies
bin subfolder, 273
compilation of programs, 271
cross-language functionality of, 271
database access with. See ADO.NET
defined, 267
deployment, 273, 599-602
folders for solutions, 273
GAC (Global Assembly Cache), 272-73
garbage collector, 274
interoperability of, 269
JIT compilers, 271
metadata, 271
MSIL (Microsoft Intermediate Language), 271
namespaces, 274-75
PE (portable execution) files, 271
programming languages available for, 270
runtime, 268, 272
Web access with. See ASP.NET
XML Web services. See Web services
Netscape browsers, 457-58, 460-62
New keyword
base classes using, 321
instantiating SqlConnection class with, 368
instantiating SqlXmlCommand class with, 526
New Project In Existing Folder template, 7
ntext data type, 38-40
NthRoot procedure, 594
Numbers Shopping Cart example, 466-71
numeric data type, 41-42
numeric data types, 40-43
nvarchar data type, 38-40
Next
Last Updated: May 29, 2002
|
