Tài liệu Programming Microsoft ® SQL Server™ 2000 with Microsoft Visual Basic® .NET pptx

Programming Microsoft® SQL Server™ 2000 with Microsoft Visual

Basic® .NET

Foreword

Acknowledgm ents

I ntroduction

Who’s the Book For?

What’s Special About This Book?

How’s the Book Organized?

System Requirem ents

Sam ple Files

Support

1. Getting Started with Visual Basic .NET for SQL Server 2000

Visual Studio .NET, the Visual Basic .NET I DE

An Overview of ADO.NET Capabilities

A Starter ADO.NET Sam ple

Using Query Analyzer

2. Tables and Data Types

Chapter Resources

Data Types for Tables

Scripting Tables

3. Program m ing Data Access with T-SQL

I ntroduction to Data Access with T-SQL

Aggregating and Grouping Rows

Processing Dates

Joins and Subqueries

4. Program m ing Views and Stored Procedures

I ntroduction to Views

Creating and Using Views

Views for Rem ote and Heterogeneous Sources

I ntroduction to Stored Procedures

Creating and Using Stored Procedures

Processing Stored Procedure Outputs

I nserting, Updating, and Deleting Rows

Program m ing Conditional Result Sets

5. Program m ing User-Defined Functions and Triggers

I ntroduction to User-Defined Functions

Creating and I nvoking Scalar UDFs

Creating and I nvoking Table-Valued UDFs

I ntroduction to Triggers

Creating and Managing Triggers

6. SQL Server 2000 XML Functionality

Overview of XML Support

XML Form ats and Schem as

URL Access to SQL Server

Tem plate Access to SQL Server

7. SQL Server 2000 Security

Overview of SQL Server Security

I ntroduction to Special Securit y I ssues

Sam ples for Logins and Users

Sam ples for Assigning Perm issions

8. Overview of the .NET Fram ework

An I ntroduction to the .NET Fram ework

An Overview of ASP.NET

XML Web Services

9. Creating Windows Applications

Getting Started with Windows Form s

Creating and Using Class References

I nheriting Classes

Program m ing Events

Exception Handling for Run-Tim e Errors

10. Program m ing Windows Solutions with ADO.NET

An Overview of ADO.NET Obj ects

Making Connections

Working with Com m and and DataReader Object s

DataAdapters , Data Sets, Form s, and Form Controls

Modifying, I nserting, and Deleting Rows

11. Program m ing ASP.NET Solutions

Review of ASP.NET Design I ssues

Creating and Running ASP.NET Solutions

Session State Managem ent

Data on Web Pages

Validating the Data on a Web Page

12. Managing XML with Visual Basic .NET

SQL Server Web Releases

Overview of XML Technologies

Generating XML Docum ents with the .NET Fram ework

Dynam ically Setting an XML Result Set

The I nterplay Between XML and Data Sets

Creating HTML Pages with XSLT

13. Creating Solutions with XML Web Services

Overview of Web services

A Web Service to Return a Com puted Result

A Web Service to Return Values from Tables

The SQL Server 2000 Web Services Toolkit

More on Populating Controls with Web Services

About the Author

Forew ord

During m y five years at Microsoft, I ’ve been helping developers understand

technologies such as Microsoft Visual Studio, Microsoft SQL Server, and Microsoft

Office Developer. During the past two years, I have worked on the Microsoft

Office XP Visual Basic Language Reference, and now, the MSDN Office Developer

Center. I n the m onthly colum n on MSDN, Office Talk, I have written articles to

help Office developers understand the .NET platform and how it affects their

current and future developm ent efforts.

As I write this foreword to Rick Dobson’s book on program m ing Microsoft SQL

Server solutions with Microsoft Visual Basic .NET, I think back to m y own

experiences developing software applications with Visual Basic. My first

experience with Visual Basic was learning the language using version 3.0. I

rem em ber picking up m y first Visual Basic beginner’s book and being excited as I

developed m y first few “Hello, World” applications. I couldn’t believe how quick

and easy it was to develop software applications that operated sim ilarly to other

popular shareware program s of that tim e.

However, during that tim e I also discovered som e of the shortcom ings of Visual

Basic as an enterprise-level developm ent language. I t was then that I turned m y

attention to C+ + . I rem em ber being very frustrated at trying to learn the

language, trying to understand concepts such as pointers, m em ory allocation,

and true object-oriented program m ing. I took classes on C+ + at the local

university, but I got even m ore frustrated having to wait m onths until I was

taught how to create the sim plest Microsoft Windows form , som ething I did in

just a couple of m inutes using Visual Basic. I n m y frustration, I gave up trying to

learn C+ + and have been using Visual Basic to develop software applications ever

since.

As each new version of Visual Basic was released, I readied m yself to learn new

software developm ent technologies. First it was ActiveX control developm ent.

Then it was calling the Windows API . Next it was DHTML Applications. Then it was

database developm ent using Microsoft SQL Server. I t always seem ed as though I

had to learn a new language and a new developm ent paradigm for every new

technology that cam e along. I kept thinking that there had to be an easier and

m ore unified approach.

Well, now we’ve reached the advent of the Microsoft .NET platform , and with it, a

revolution in the Visual Basic language, Microsoft Visual Basic .NET. I believe that

Visual Basic .NET will provide software developers with new opportunities for

quickly and easily designing integrated software applications that connect

businesses and individuals anytim e, anywhere, and on virtually any software

device. With advances in the Visual Basic .NET language, Visual Basic .NET

developers will finally be on a par with their C+ + and C# counterparts,

participating in m any high-end developm ent projects. With Visual Studio .NET

features such as cross-language debugging, along with Visual Basic .NET

conform ance to the com m on t ype system and the com m on language runtim e,

organizations can drive down their developm ent costs by tapping into the wide

range of skills that Visual Basic .NET developers now possess.

True object-oriented program m ing is now available in Visual Basic .NET, including

features such as inheritance and m ethod overloading. I t’s now sim pler to call the

Windows API by using the .NET Fram ework Class Libraries. Web application

developm ent is now as easy as developing Windows form s–based applications.

Database application developm ent is m ade easier by uniting disparate data object

libraries such as DAO, RDO, OLE DB, and ADO under ADO.NET, utilizing the

power of XML to consum e and transm it relational data over com puter networks.

And a new technology, XML Web services, allows Visual Basic .NET developers to

host their software applications’ logic over the Web. Additionally, a big issue for

software developers today is that of software application deploym ent and

versioning. I f you don’t agree, j ust ask any software developer about “DLL hell,”

and you’re bound to get an earful. For m any .NET applications, the .NET platform

features “ copy and paste” or XCOPY deploym ent. (Users sim ply copy your

application files from the source m edia to any single directory and run the

application.) And because .NET no longer relies on the registry, virtually all DLL

com patibility issues go away.

With this book, Rick aim s to give you the skills you need to program SQL Server

solutions with Visual Basic .NET. I know you will find Rick’s book helpful. Rick

brings his experience to bear from three previous books: Program m ing Microsoft

Access Version 2002 (Microsoft Press, 2001), Program m ing Microsoft Access 2000

(Microsoft Press, 1999), and Professional SQL Server Developm ent with Access

2000 (Wrox Press I nc., 2000). Rick also brings his experience of leading a

successful nationwide sem inar tour. More im portant, I know you will enjoy Rick’s

book because of his deep interest in Visual Basic .NET and SQL Server, and in

helping you, the professional developer, understand and apply these technologies

in your daily software application developm ent projects.

Paul Cornell MSDN Office Developer Center

http: / / m sdn.m icrosoft.com / officeMicrosoft Corporation February 2002

Acknow ledgm ents

This section offers m e a chance to say thank you to all who helped m ake this

book possible. I wish to offer special recognition to five support resources.

First, the folks at Microsoft Press have been fantastic. Dave Clark, an acquisitions

editor, selected m e to write the book just m onths after I com pleted another book

for Microsoft Press. Dick Brown, m y project editor, staunchly stood up for his

perception of how to m ake the book’s organization and content clear to you

without being pett y or boring to m e. Dick also lightened m y load substantially by

showing a real knack for editing m y text without distorting the original intent.

When Dick was especially busy, he handed off som e of his load to Jean Ross, who

also did an adm irable job. Others at Microsoft Press who contributed to m y well￾being in one way or another include Aaron Lavin and Anne Ham ilton.

Second, I had excellent working relations with several professionals within

Microsoft. Paul Cornell, a widely known technical editor at Microsoft, was kind

enough to share his insights on how to present .NET concepts com pellingly. I

want to thank Paul especially for writing the Foreword to this book. Karthik

Ravindran served as the MSXML Beta Product Lead Engineer at Microsoft Product

Support Services during the tim e that I wrote this book. He provided valuable

technical content about the SQL Server 2000 Web releases. Other Microsoft

representatives providing m oral and technical support for this book include

Richard Waym ire and Jan Shanahan.

Third, I want to express m y appreciation to the m any readers, sem inar

attendees, and site visitors who took the tim e to tell m e what I did right or wrong

for them , and also to those who shared their technical support questions with m e.

I t is through this kind of feedback that I am able to know what’s im portant to

practicing developers. I encourage you to visit m y m ain Web site

(http: / / www.program m ingm saccess.com ) and sign the guest book. The entry

form includes space for you to leave your evaluation of this book or your question

about a topic covered in the book. I prom ise to do m y best to reply personally. I n

any event, I definitely read all m essages and use them so that I can serve you

better with future editions of this, and other, books.

Fourth, I want to tell the world how grateful I am to m y wife, Virginia. Without

Virginia’s warm support, love, and care, this book would be less professional. She

relieves m e of nearly every responsibility around the house when I undertake a

book project. I n addition, she offers strategic advice on the issues to address and

their style of coverage. When I run out of tim e, she even pitches in with the

proofreading.

Fifth, it is im portant for m e to give praise and glory to m y Lord and Savior, Jesus

Christ, who I believe gave m e the strength and wisdom to write this book. I n

addition, He gave m e health during the long gestation period that resulted in the

birth of this book. I t is m y prayer that the book prove to be a blessing to you.

I ntroduction

Anyone who buys a book—or considers buying it—wants to know who the book is

for, what sets it apart from others like it, and how the book is organized. This

introduction covers those three questions, and it also discusses system

requirem ents, sam ple files, and support.

• First, w ho is the book for? There are at least two answers to this

question. One answer is that the book targets professional developers

(and others aspiring to be professional developers). The second group the

book addresses is those who want to build full-featured, secure SQL

Server solutions with Visual Basic .NET.

• Second, w hat’s special about the book? I hope you com e to believe

that the m ost im portant answer to this question is that the book

considered qualit y and depth of coverage m ore im portant than rushing to

m arket. The book will arrive on bookshelves m ore than three m onths after

the official release of the .NET Fram ework. I t is m y wish that you derive

value from the extra tim e taken to develop the m any code sam ples and

the in-depth discussions of advanced topics, such as class inheritance,

ASP.NET, and XML Web services.

• Third, how is the book organized? The short answer is that there are

two m ain sections. One section introduces SQL Server concepts as it

dem onstrates T-SQL (Transact SQL) program m ing techniques. After

conveying SQL Server basic building blocks in the first part, the second

part reveals how to put those parts together with Visual Basic .NET and

related technologies into SQL Server solutions for handling com m on

database chores.

The three support item s include a brief description of the book’s com panion CD

and how to use it, Microsoft Press Support I nform ation for this book, and a

sum m ary of system and software requirem ents for the sam ple code presented in

the book.

W ho’s the Book For?

This book targets professional Visual Basic and Visual Basic for Applications

developers. From m y sem inar tours and Web sites

(http: / / www.program m ingm saccess.com and http: / / www.cabinc.net), I know

that these professionals are driven by a passion to deliver solutions to their

clients through applying the m ost innovative technologies their clients will accept.

I n-house developers are the go-to persons for getting results fast— particularly for

custom in-house system s and databases. I ndependent developers specialize in

serving niche situations that can include under-served business needs and work

overflows. I n both cases, these professionals need training m aterials that address

practical business requirem ents while showcasing innovative technologies without

wasting their tim e. This book strives to serve this broad need in two specific

areas.

This book is for developers looking for code sam ples and step-by-step instructions

for building SQL Server 2000 solutions with Visual Basic .NET. The book focuses

on the integration of SQL Server 2000 with .NET technologies tapped via Visual

Basic .NET. I t is m y firm belief that you cannot create great SQL Server solutions

in any program m ing language without knowing SQL Server. Therefore, this book

goes beyond traditional coverage of SQL Server for Visual Basic developers. You’ll

learn T-SQL program m ing techniques for data access, data m anipulation, and

data definition. A whole chapter equips you to secure your SQL Server solutions.

I n addition, there’s plenty of content in this book on Visual Basic .NET and related

technologies, such as ADO.NET, ASP.NET, XML (Extensible Markup Language),

and XML Web services. The presentation of these technologies dem onstrates

coding techniques and explores concepts that equip you to build better solutions

with SQL Server 2000 databases. I n addition, the book highlights innovations

introduced through the Web releases for SQL Server 2000 that integrate SQL

Server 2000 tightly with Visual Basic .NET.

This isn’t a book about XML, but three of the book’s 13 chapters focus in whole or

in part on XML. Therefore, those seeking practical dem onstrations of how to use

XML with SQL Server and Visual Basic .NET will derive value from this book. I f

you have looked at any of the com puter m agazines over the past couple of years,

you know that XML is com ing to a solution near you. However, the rapid pace of

XML innovation m ay have dissuaded som e from jum ping on the bandwagon while

they wait to see what’s going to last and what’s just a fad. I n the book’s three

chapters on XML technology, you’ll learn about XML docum ents, fragm ents, and

form atting as well as related technologies, such as XPath (XML Path Language)

queries, XSLT (Extensible Stylesheet Language Transform ation), and WSDL (Web

Services Description Language).

W hat’s Special About This Book?

There are several features that m ake this book stand apart from the flood of

books on .NET. One of the m ost im portant of these is that this book didn’t rush to

m arket but rather shipped m onths after the release of the .NET Fram ework. This

allowed m e enough tim e to filter, exam ine, and uncover what were the m ost

useful and innovative features for Visual Basic .NET developers building SQL

Server solutions. For exam ple, the book includes a whole chapter on creating

solutions with XML Web services. That chapter includes two m ajor sections on the

SQL Server 2000 Web Services Toolkit, which didn’t ship until the day of the .NET

Fram ework release.

The .NET Fram ework content is at a professional level, but it isn’t just for techies.

This book doesn’t assum e any prior knowledge of the .NET Fram ework. I t does

assum e that you get paid for building solutions program m atically and that at least

som e of those solutions are for SQL Server databases. Therefore, the book

explains basic .NET concepts and dem onstrates how to achieve practical results

with those concepts through a huge collection of .NET code sam ples.

This book is about building solutions for SQL Server 2000. I include coverage of

the m any special features that tie Visual Basic .NET and SQL Server 2000 closely

to one another. Although there is coverage of general .NET database techniques,

this book dives deeply into T-SQL program m ing techniques so that you can create

your own custom database object s, such as tables, stored procedures, views,

triggers, and user-defined functions. I n addition, there is separate coverage of

the XML features released with SQL Server 2000 as well as separate coverage of

the XML features in the first three Web releases that shipped for SQL Server

2000. There are num erous code sam ples throughout the book. These will equip

you to build solutions with Visual Basic .NET, T-SQL, and com binations of the two.

Finally, this book is special because of the unique experiences of its author, Rick

Dobson. I have trained professional developers in Australia, England, Canada,

and throughout the United States. This is m y fourth book in four years, and you

can find m y articles in popular publications and Web sites, such as SQL Server

Magazine and MSDN Online. As a Webm aster, m y m ain site

(http: / / www.program m ingm saccess.com ) serves hundreds of thousands of

sessions to developers each year. I constantly exam ine their viewing habits at the

site to determ ine what interests them . I n addition, m y site features scores of

answers to technical support questions subm itted by professional developers. My

goal in offering answers to these questions is to stay in touch with practicing

developers worldwide so that m y new books address the needs of practicing,

professional developers.

How ’s the Book Organized?

There are two m ain part s to this book tied together by an introductory part. Part

II , the first m ain part, dwells on SQL Server techniques. Part I I I builds on the

SQL Server background as it lays a firm foundation in .NET techniques for Visual

Basic .NET developers. Part I , the introductory part, dem onstrates ways to use

SQL Server and Visual Basic .NET together.

Part I , I ntroduction

Part I , which includes only Chapter 1, has three m ain goals. First, it acquaints you

with the basics of Visual Basic .NET within Visual Studio .NET. You can think of

Visual Basic .NET as a m ajor upgrade to the Visual Basic 5 or 6 that you are

probably using currently. This first section introduces som e concepts that you will

find useful as you initially learn the landscape of Visual Basic .NET. The second

goal of Chapter 1 is to introduce ADO.NET. I f you think of Visual Basic .NET as a

m ajor upgrade to Visual Basic 6, ADO.NET is m ore like a m ajor overhaul of ADO.

I n two sections, you get an introduction to ADO.NET classes— particularly as they

relate to SQL Server— and you get a chance to see a couple of beginner sam ples

of how to create SQL Server solutions with Visual Basic .NET and ADO.NET. The

third goal of the introductory part is to expose you to Query Analyzer. This is a

SQL Server client tool that ships with all com m ercial editions of SQL Server 2000.

You can think of it as an I DE for T-SQL code. Most of the book’s first part relies

heavily on T-SQL, and therefore having a convenient environm ent for debugging

and running T-SQL code is helpful. The final section of Chapter 1 addresses this

goal.

Part I I , SQL Server

Part I I consists of six relatively short chapters that focus substantially on

program m ing SQL Server 2000 with T-SQL. Chapter 2 and Chapter 3 introduce T￾SQL and SQL Server data types. I f you are going to program SQL Server and

create efficient, fast solutions, you m ust learn SQL Server data t ypes, which is

one of the m ain points conveyed by Chapter 2. Many readers will gravitate to

Chapter 3 because it introduces core T-SQL program m ing techniques for data

access. You’ll apply the techniques covered in this chapter often as you select

subsets of rows and colum ns in data sources, group and aggregate rows from a

table, process dates, and join data from two or m ore tables. Chapter 3 also

considers special data access topics, such as outer j oins, self joins and

subqueries.

The next pair of chapters in Part I I , Chapter 4 and Chapter 5, take a look at

program m ing database objects that you will use for data access and data

m anipulation, such as views, stored procedures, user-defined functions, and

triggers. These database objects are im portant for m any reasons, but one of the

m ost im portant is that they bundle T-SQL statem ents for their easy reuse. I t is

widely known that the best code is the code that you don’t have to write.

However, if you do have to write code, you should definitely write it just onc, and

then reuse it whenever you need its functionality. Stored procedures are

particularly desirable database objects because they save com piled T-SQL

statem ents that can deliver significant speed advantages over resubm itting the

sam e T-SQL statem ent for com pilation each tim e you want to perform a data

access or data m anipulation task. Chapter 4 and Chapter 5 are also im portant

because they convey T-SQL syntax for using param eters and conditional logic

that support dynam ic run-tim e behavior and user interactivity.

One of the m ost im portant features of SQL Server 2000 is its XML functionality.

Because XML as a topic is changing so rapidly, Microsoft adopted a strategy of

upgrading the SQL Server 2000 XML functionality through Web releases. Although

those with SQL Server 2000 can download the Web releases without charge from

the Microsoft Web site, the Web releases are fully supported. Chapter 6

introduces core XML functionality introduced with SQL Server 2000 as well as

functionality from the first two Web releases. I n particular, you can learn in this

chapter about I IS virtual directories as well as form ats for XML docum ents and

schem as. You also learn about tem plates in virtual directories that facilitate data

access and data m anipulation tasks over the Web.

Chapter 7 closes out the SQL Server part of the book with an in-depth look at

program m ing SQL Server security. I n these tim es, securit y has grown into a

m onum ental topic, and this chapter can keep you out of trouble by blocking

hackers from getting into or corrupting your database. You learn such topics as

how to create and m anage different types of login and user accounts and how to

control the perm issions available to individual accounts as well as groups of

accounts. By learning how to script accounts and perm issions with T-SQL, you

sim plify revising and updating security as conditions change (for exam ple, when

users leave the com pany or when new, sensitive data gets added to a table).

Part I I I , .NET

Chapter 8 starts the .NET part of the book with a review of selected .NET topics

that are covered in the initial look Chapter 1 offered at the .NET Fram ework. This

chapter provides an overview of the architecture for .NET solutions, and it drills

down on two topics: ASP.NET and XML Web services. The general purpose of this

chapter is the sam e as Chapter 1, which is to introduce concepts. The em phasis

in Chapter 8 isn’t how you do som ething, but rather what are the m ajor

technologies enabling you to do som ething. Chapter 1 and Chapter 8 are both

relatively short chapters, but you m ay find them invaluable if you are the kind of

person who benefits from high-level overviews of a collection of topics.

Chapter 9 starts with a close exam ination of how to use Windows Form s with

Visual Basic .NET. I t then shifts its focus to a review of traditional class

processing concepts via Visual Basic .NET as an introduction to class inheritance,

a new object-oriented feature that m akes its first appearance in Visual Basic with

Visual Basic .NET. Next the treatm ent of classes progresses to the handling of

built-in events as well as the raising of custom events. Finally the chapter closes

with an exam ination of the new exception handling techniques for processing run￾tim e errors.

Chapter 10 is a how-to guide for solutions to typical problem s with ADO.NET.

Before launching into its progression of sam ples showing how to perform all kinds

of tasks, the chapter starts with an overview of the ADO.NET object m odel that

covers the m ain obj ects along with selected properties and m ethods for each

object. The how-to guide focuses on data access tasks, such as selecting rows

and colum ns from SQL Server database objects, as well as data m anipulation

tasks, such as inserting, updating, and deleting rows in a table. Working through

the sam ples in the how-to guide offers a hands-on feel for using the

System .Data.SqlClient nam espace elem ents to perform typical tasks.

Chapter 11 switches the focus to the Web by addressing the creation and use of

ASP.NET solutions. This chapter starts by introducing basic elem ents that you

need to know in order to use ASP.NET to create great Web solutions with Visual

Basic .NET. These include learning what happens as a page does a round-trip

from a browser to a Web server and back to the browser— particularly for data

associated with the page. Other prelim inary topics that equip you for building

professional Web solutions include running the sam e page in m ultiple browser

types and sniffing the browser for cases in which you want to send a page

optim ized for a specific kind of browser type. Managem ent of session state is a

m ajor topic in the chapter, and you learn how to use enhancem ents to Session

variables for Web farm s as well as the new view state variables, a non-server￾based technique for m anaging state in ASP.NET solutions. The last two sections in

the chapter deal with ADO.NET topics in ASP.NET solutions and the new

autom atic data validation features built right into ASP.NET.

The last two chapters in the book explore how XML interplays with Visual Studio

.NET and SQL Server 2000. For exam ple, Chapter 12 exam ines special tools in

Visual Studio .NET to facilitate the design and editing of XML docum ent s and

schem as. I n addition, you learn how to designate XPath queries that accept run￾tim e input for returning SQL Server result sets inside Visual Basic .NET program s.

The chapter dem onstrates techniques for processing the XML docum ent

associated with all ADO.NET data set objects. I n the chapter’s last section, I

present a couple of code sam ples that illustrate how to program static HTML

pages based on XML docum ents with XSLT.

Chapter 1 3 drills down on XML Web services by dem onstrating several different

approaches for creating Web services as well as consum ing XML output from Web

services. Web services behave som ewhat like COM objects in that you can set up

server applications for client applications. The server applications expose m ethods

to which the client applications can pass param eters. XML com es into play with

Web services in a couple of areas. First, Web services represent their inputs and

outputs via WSDL, an XML-based language that form ally describes an XML Web

service. Second, Web services return data to their clients as XML docum ents or

docum ent fragm ents.

System Requirem ents

The requirem ents for this book vary by chapter. I developed and tested all

sam ples throughout this book on a com puter equipped with Windows 2000

Server, SQL Server Enterprise Edition, and the Enterprise Developer Edition of

Visual Studio .NET, which includes Visual Basic .NET. To use this book, you’ll need

to have Visual Basic .NET or Visual Studio .NET installed on your com puter. (See

Chapter 1 for m ore inform ation on versions of Visual Basic .NET and Visual Studio

.NET.) I n addition, you’ll need SQL Server 2000, and for som e of the chapters,

you’ll need SQL Server 2000 updated with Web releases 1, 2, and 3. Chapter 6

gives the URLs for downloading Web releases 1 and 2. Chapter 12 gives two

different URLs for downloading Web Release 3— one with the SQL Server 2000

Web Services Toolkit and the other without it.

For selected chapters, you can run the sam ples with less software or different

operating system s than the one that I used. For exam ple, chapters 2 through 5

will run on any operating system that supports a com m ercial version of SQL

Server 2000, such as Windows 98 or a m ore recent Windows operating system .

Chapter 7 requires an operating system that supports Windows NT security, such

as Windows 2000 or Windows XP Professional. Chapter 6, Chapter 11, and

Chapter 1 3 require Microsoft I nternet I nform ation Services (I IS). I n addition,

Chapter 6 requires the installation of Web releases 1 and 2. For Chapter 11, your

system needs to m eet the m inim um requirem ents for ASP.NET. (See a note in the

“How Does ASP.NET Relate to ASP?” section of Chapter 8.) Several of the

sam ples in Chapter 1 3 require Web Release 3 and its associated SQL Server

2000 Web Services Toolkit.

Sam ple Files

Sam ple files for this book can be found at the Microsoft Press Web site, at

http: / / www.m icrosoft.com / m spress/ books/ 5792.asp. Clicking the Com panion

Content link takes you to a page from which you can download the sam ples.

Supplem ental content files for this book can also be found on the book’s

com panion CD. To access those files, insert the com panion CD into your

com puter’s CD-ROM drive and m ake a selection from the m enu that appears. I f

the AutoRun feature isn’t enabled on your system (if a m enu doesn’t appear when

you insert the disc in your com puter’s CD-ROM drive), run StartCD.exe in the root

folder of the com panion CD. I nstalling the sam ple files on your hard disk requires

approxim ately 15.3 MB of disk space. I f you have trouble running any of these

files, refer to the text in the book that describes these program s.

Aside from the sam ple files that this book discusses, the book’s supplem ental

content includes a stand-alone eBook installation that will allow you to access an

electronic version of the print book directly from your desktop.

Support

Every effort has been m ade to ensure the accuracy of this book and the contents

of the com panion CD. Microsoft Press provides corrections for books through the

World Wide Web at the following address:

http: / / www.m icrosoft.com / m spress/ support

To connect directly to the Microsoft Press Knowledge Base and enter a query

regarding a question or an issue that you m ay have, go to:

http: / / www.m icrosoft.com / m spress/ support/ search.asp

I f you have com m ents, questions, or ideas regarding this book or the com panion

content, or questions that are not answered by querying the Knowledge Base,

please send them to Microsoft Press via e-m ail to:

m spinput@m icrosoft.com

Or via postal m ail to:

Microsoft Press Attn: Program m ing Microsoft SQL Server 2000 with Microsoft

Visual Basic .NET Editor One Microsoft Way Redm ond, WA 98052-6399

Please note that product support is not offered through the above m ail address.

For product support inform ation, please visit the Microsoft Support Web site at:

http: / / support.m icrosoft.com

Chapter 1 . Getting Started w ith Visual

Basic .NET for SQL Server 2 0 0 0

This book aim s to give professional developers the background that they need to

program SQL Server applications with Microsoft Visual Basic .NET. This overall

goal im plies three guidelines:

• First, the book targets practicing developers. I n m y experience, these are

busy professionals who need the details fast. These individuals already

know how to build applications. They buy a book to learn how to build

those applications with a specific set of tools.

• Second, the book is about building applications for SQL Server 2000. This

focus justifies in-depth coverage of SQL Server program m ing topics— in

particular, T-SQL, Microsoft’s extension of the Structured Query Language

(SQL).

• Third, the book illustrates how to program in Visual Basic .NET, but with

particular em phasis on database issues for SQL Server 2000. Special

attention goes to related .NET technologies, such as the .NET Fram ework,

ADO.NET, ASP.NET, and XML Web services.

My goal in this chapter is to equip you conceptually for the rest of the book.

Therefore, this chapter includes m aterial that acquaints you with application

developm ent techniques and topics for SQL Server 2000 and Visual Basic .NET.

The discussion of the sam ples in this chapter generally aim s to convey broad

approaches instead of how to run the sam ple. All the rem aining chapters except

for Chapter 8, another conceptual chapter, have sam ples with instructions aim ed

at professional developers.

I believe that the overwhelm ing m ajority of professional Visual Basic developers

have no hands-on fam iliarit y with Visual Basic .NET and its related technologies.

I f you already knew Visual Basic .NET, it wouldn’t m ake any sense to buy a book

describing how to use it. This chapter therefore focuses on how to get started

with Visual Basic .NET and one of its core related technologies for those building

SQL Server applications— ADO.NET. I also believe that m ost Visual Basic

developers don’t have an intim ate knowledge of SQL Server— especially for

creating user-defined objects, such as tables, views, and stored procedures. This

capability can em power you to build m ore powerful and m ore secure applications.

As you learn about database objects and how to create them in Chapter 2

through Chapter 7, reflect back on the Visual Basic .NET coverage in this chapter

and how to m arry database creation techniques and Visual Basic .NET

developm ent techniques. One of the best tools to build database objects is SQL

Server 2000 Query Analyzer. This chapter’s closing section conveys the basics of

Query Analyzer that you need to follow the sam ples in Chapter 2 through Chapter

7.

Visual Studio .NET, the Visual Basic .NET I DE

Visual Studio .NET is the new m ultilanguage integrated developm ent environm ent

(I DE) for Visual Basic, C# , C+ + , and JScript developers. I f you are developing

solutions for Visual Basic .NET, I definitely recom m end that you use Visual Studio

.NET as your developm ent environm ent. This section dem onstrates how to get

started using Visual Studio .NET for developing solutions with Visual Basic .NET.

Visual Basic .NET is available as part of Visual Studio .NET in four editions:

• Professional

• Enterprise Developer

• Enterprise Architect

• Academ ic

All four editions of Visual Studio .NET include Visual Basic .NET, Microsoft Visual

C# .NET, Microsoft Visual C+ + .NET, and support for other languages. I n

addition, Microsoft offers Visual Basic .NET Standard, which doesn’t include Visual

C# .NET or Visual C+ + .NET.

Because this book targets professional Visual Basic developers creating SQL

Server applications, it uses the Enterprise Developer Edition of Visual Studio

.NET. You m ay notice som e differences if you’re using another edition.

Visual Studio .NET can be installed on com puters running one of five operating

system s: Windows 2000, Windows NT, Windows XP, Windows ME, and Windows

98. Not all the .NET Fram ework features are available for each operating system .

For exam ple, Windows 98, Windows Me, and Windows NT don’t support

developing ASP.NET Web applications or XML Web services applications. The

sam ples for this book are tested on a com puter running Windows 2000 Server,

which does support all .NET Fram ework features.

Starting Visual Studio .NET

To open Visual Studio .NET, click the Start button on the Windows taskbar,

choose Program s, and then choose Microsoft Visual Studio .NET. Visual Studio

displays its integrated developm ent environm ent, including the Start Page (unless

you previously configured Visual Studio to open differently). From the Start Page,

you can configure Visual Studio to work according to your developm ent

preferences, and you can start new solutions as well as open existing projects.

Configuring Visual Studio .NET for Visual Basic .NET

Use the links on the left side of the Start Page to begin configuring Visual Studio

.NET for developing solutions in Visual Basic .NET. Click the My Profile link to

open a pane in which you can specify an overall profile as well as individually

indicate your preferences for Keyboard Schem e, Window Layout, and Help Filter.

You also can designate the initial page that Visual Basic .NET displays. When you

are beginning, it m ay be particularly convenient to choose Show Start Page. As a

Visual Basic developer who has worked with Visual Basic 6, you m ight feel m ost

fam iliar with a layout that reflects your prior developm ent environm ent. Figure 1-

1 shows these My Profile selections.

Figure 1 -1 . My Profile selections for starting Visual Studio .NET for a

Visual Basic developer.

Using the Start Page

After setting your profile, you can return to the initial Start Page pane by

choosing the Get Started link from the m enu on the left border. I f you had

created previous solutions, the last four m odified projects would appear on the

Proj ects tab of the Start Page. The tab shows project nam es along with date last

m odified. I f a project you want to view doesn’t appear on the list, you can click

the Open Project link to display the Open Project dialog box and then navigate to

a directory containing the previously created solution. Select the project’s folder

that you want to open in the I DE, and double-click the solution file (.sln) for the

project. The next section illustrates this process in the context of a sam ple

project.

To create a new solution, click the New Project link to open the New Project

dialog box. I f you saved preferences such as those shown in Figure 1-1, the

dialog will autom atically select Visual Basic Projects in the Project Types pane of

the New Project dialog box. On the right, you can select a tem plate for launching

a project. Table 1-1 shows the project tem plate nam es along with a brief

description available from the Enterprise Developer Edition of Visual Studio .NET.

Choosing a tem plate (by clicking OK after selecting a tem plate) opens a project

ready for creating the t ype of solution that you want to develop. When Visual

Studio .NET saves the tem plate to start a new project, it specifies either a file

folder or a Web site for the tem plate’s files; you can override the default nam es

for the file folder and Web site.