Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Program m ing Micr osoft ® SQL Server™ 2000 with Micr osoft Visual
Basic® .NET

Foreword

Acknow ledgm ent s

I ntroduct ion
Who’s the Book For?
What’s Special About This Book?
How’s the Book Organized?
Syst em Requirem ent s
Sam ple Files
Support

1. Get t ing St arted with Visual Basic .NET for SQL Server 2000
Visual St udio .NET, t he Visual Basic .NET I DE
An Overview of ADO.NET Capabilit ies
A St arter ADO.NET Sam ple
Using Query Analyzer

2. Tables and Data Types
Chapt er Resources
Data Types for Tables
Script ing Tables

3. Program m ing Dat a Access wit h T- SQL

Ov erview of SQL Server Secu rit y
I ntroduct ion t o Special Securit y I ssues
Sam ples for Logins and Users
Sam ples for Assigning Perm issions

8. Overview of t he .NET Fram ework
An I ntroduct ion t o the .NET Fram ework
An Overview of ASP.NET
XML Web Services

9. Creat ing Windows Applicat ions
Get t ing St arted w ith Windows Form s
Creat ing and Using Class References
I nher it ing Classes
Program m ing Event s
Except ion Handling for Run-Tim e Err ors

10. Program m ing Windows Solut ions with ADO.NET
An Overview of ADO.NET Obj ect s
Making Connect ions
Working with Com m and and Dat aReader Obj ect s
DataAdapt ers , Dat a Set s, Form s, and Form Controls
Modifying, I nserting, and Delet ing Rows

11. Program m ing ASP.NET Solutions
Review of ASP.NET Design I ssues
Creat ing and Running ASP.NET Solut ions
Session St at e Managem ent
Data on Web Pages
Validating the Data on a Web Page

experience with Visual Basic was lear ning t he language using version 3.0. I
rem em ber picking up m y first Visual Basic beginner’s book and being excit ed as I
developed m y first few “ Hello, Wor ld” applications. I couldn’t believe how quick
and easy it was t o develop soft ware applicat ions that operat ed sim ilarly t o ot her
popular sharew are program s of that t im e.
How ev er, during that t im e I also discov er ed som e of t he shortcom ings of Visual
Basic as an ent erprise- level developm ent language. I t was then t hat I turned m y
at tent ion t o C+ + . I r em em ber being very frustrat ed at t r ying t o learn t he
language, trying to underst and concept s such as point ers, m em ory allocat ion,
and true obj ect -orient ed program m ing. I t ook classes on C+ + at t he local
universit y, but I got even m or e frust rat ed hav ing t o wait m onths unt il I was
taught how t o cr eat e t he sim plest Micr osoft Windows form , som et hing I did in
j ust a couple of m inut es using Visual Basic. I n m y frust rat ion, I gave up try ing t o
learn C+ + and have been using Visual Basic t o develop soft ware applicat ions ever
since.
As each new v ersion of Visual Basic was released, I readied m yself to learn new
soft w are developm ent t echnologies. First it w as Act iveX cont rol developm ent .
Then it was calling t he Windows API . Next it was DHTML Applicat ions. Then it w as
dat abase developm ent using Micr osoft SQL Ser ver. I t always seem ed as t hough I
had to learn a new language and a new developm ent paradigm for ev er y new
technology t hat cam e along. I kept t hink ing t hat there had to be an easier and
m ore unified appr oach.
Well, now we’ve reach ed the advent of the Micr osoft .NET plat form , and wit h it , a
revolut ion in the Visual Basic language, Microsoft Visual Basic .NET. I believe t hat
Visual Basic .NET will pr ovide software developers wit h new opportunit ies for
quick ly and easily designing int egrat ed software applicat ions t hat connect
businesses and individuals anyt im e, anywhere, and on virt ually any soft ware
device. Wit h advances in t he Visual Basic . NET language, Visual Basic .NET
developers w ill finally be on a par wit h t heir C+ + and C# counterparts,
participat ing in m any high-end developm ent proj ect s. Wit h Visual St udio .NET

in y our daily soft ware applicat ion developm ent proj ect s.
Paul Cornell MSDN Office Dev eloper Center
htt p: / / m sdn.m icr osoft.com / officeMicr osoft Corporat ion February 2002 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Acknow ledgm ent s
This sect ion offers m e a chance to say t hank you to all who helped m ak e this
book possible. I wish t o offer special recognit ion to five support resources.
First, the folks at Microsoft Press have been fant ast ic. Dave Clark, an acquisit ions
edit or, select ed m e t o writ e t he book j ust m onths aft er I com plet ed anot her book
for Microsoft Press. Dick Br own, m y pr oject edit or, staunchly st ood up for his
percept ion of how t o m ake t he book’s organization and cont ent clear to you
wit hout being pett y or boring t o m e. Dick also light ened m y load subst ant ially by
showing a real knack for edit ing m y t ext without dist orting the original int ent .
When Dick was especially busy, he handed off som e of his load to Jean Ross, w ho
also did an adm irable j ob. Ot hers at Microsoft Press who contribut ed t o m y well-
being in one way or anot her include Aaron Lavin and Anne Ham ilt on.
Second, I had excellent wor king relat ions wit h several professionals wit hin
Micr osoft . Paul Cor nell, a widely know n t echnical editor at Microsoft , was kind
enough t o share his insight s on how to present .NET concept s com pellingly. I
want t o t hank Paul especially for writ ing the Foreword t o t his book. Kart hik
Ravindran served as t he MSXML Bet a Product Lead Engineer at Microsoft Product
Support Services during t he t im e t hat I wrot e t his book. He provided valuable
technical cont ent about the SQL Server 2000 Web releases. Ot her Microsoft
represent atives pr oviding m oral and technical support for t his book include
Richard Waym ire and Jan Shanahan.
Third, I want t o express m y appreciation t o t he m any readers, sem inar
at tendees, and sit e visit ors who took t he t im e t o t ell m e what I did right or wrong
for t hem , and also to those w ho shared t heir t echnical support quest ions with m e.

• Se con d, w hat ’s special a bou t t h e book? I hope you com e t o believ e
that the m ost im portant answ er t o t his quest ion is t hat t he book
considered qualit y and depth of coverage m or e im portant t han rushing to
m arket . The book will arrive on bookshelves m ore than t hree mont hs aft er
the official release of t he .NET Fram ework. I t is m y w ish t hat you deriv e
value from t he ext ra t im e t aken to develop t he m any code sam ples and
the in-depth discussions of advanced topics, such as class inherit ance,
ASP.NET, and XML Web services.
• Third, h ow is t h e b ook orga n iz e d? The short answ er is t hat there are
two m ain sect ions. One section int roduces SQL Server concept s as it
dem onst rat es T- SQL ( Tr ansact SQL) pr ogram m ing techniques. After
conv eying SQL Server basic building block s in t he first part, t he second
part reveals how t o put t hose parts t ogether w it h Visual Basic .NET and
relat ed technologies into SQL Server solut ions for handling com m on
dat abase chores.
The t hree support item s include a brief descript ion of t he book’s com panion CD
and how t o use it , Micr osoft Press Support I nform at ion for t his book, and a
sum m ary of syst em and software requirem ent s for t he sam ple code pr esent ed in
the book. W ho’s t he Book For ?
This book t arget s professional Visual Basic and Visual Basic for Applications
developers. From m y sem inar t ours and Web sit es
(ht t p: / / www.program m ingm saccess.com and ht t p: / / www.cabinc. net ) , I know
that these professionals are driven by a passion t o deliver solut ions to t heir
clients through applying t he m ost innovat ive t echnologies their client s will accept .
I n-house developers are t he go- t o persons for get t ing result s fast — particularly for
cust om in- house sy st em s and dat abases. I ndependent developers specialize in
serving niche sit uat ions t hat can include under-served business needs and work

Ser vices Descript ion Language) . W ha t ’s Specia l About This Book?
Ther e are sev eral feat ures that m ake t his book st and apart from the flood of
books on .NET. One of the m ost im portant of t hese is that t his book didn’t rush t o
m arket but rat her shipped m ont hs aft er t he release of t he .NET Fram ework. This
allow ed m e enough t im e t o filt er, exam ine, and uncover what w ere t he m ost
useful and innovat ive feat ures for Visual Basic .NET developers building SQL
Ser ver solutions. For exam ple, the book includes a whole chapt er on creat ing
solut ions wit h XML Web services. That chapt er includes two m aj or sect ions on t he
SQL Server 2000 Web Services Toolk it , w hich didn’t ship unt il t he day of the .NET
Fram ework release.
The .NET Fram ework content is at a professional lev el, but it isn’t just for t echies.
This book doesn ’t assum e any prior know ledge of t he .NET Fram ework. I t does
assum e that you get paid for building solut ions program m atically and that at least
som e of those solut ions are for SQL Server dat abases. Therefore, t he book
explains basic .NET concept s and dem onst rat es how t o achieve pract ical result s
wit h those concept s t hrough a huge collect ion of .NET code sam ples.
This book is about building solut ions for SQL Server 2000. I include coverage of
the m any special feat ures t hat t ie Visual Basic .NET and SQL Server 2000 closely
to one anot her. Alt hough t here is coverage of general .NET database t echniques,
this book dives deeply int o T- SQL pr ogram m ing t echniques so that you can creat e
your own cu st om dat abase obj ect s, such as t ables, st or ed procedur es, view s,
triggers, and user-defined funct ions. I n addit ion, t here is separat e coverage of
the XML feat ures released wit h SQL Server 2000 as w ell as separat e coverage of
the XML feat ures in t he first t hr ee Web releases t hat shipped for SQL Server
2000. There are num erous code sam ples t hroughout t he book. These will equip
you to build solutions wit h Visual Basic .NET, T- SQL, and com binat ions of t he t wo.
Finally, t his book is special because of t he unique exper iences of it s author, Rick

of how to create SQL Server solut ions wit h Visual Basic .NET and ADO.NET. Th e
third goal of t he int roductory part is to expose you to Query Analyzer . Th is is a
SQL Server client tool t hat sh ips wit h all com m ercial edit ions of SQL Server 2000.
You can t hink of it as an I DE for T- SQL code. Most of t he book’s first part relies
heavily on T- SQL, and t herefore having a conv enient environm ent for debugging
and running T- SQL code is helpful. The final sect ion of Chapt er 1 addresses this
goal.
Part I I , SQL Ser ve r
Part I I consist s of six r elat ively sh ort chapt ers that focus subst ant ially on
program m ing SQL Server 2000 wit h T- SQL. Chapt er 2 and Chapt er 3 int roduce T-
SQL and SQL Serv er dat a t ypes. I f you are going t o program SQL Ser ver and
create efficient , fast solut ions, you m ust learn SQL Server dat a t ypes, which is
one of t he main point s conveyed by Chapter 2. Many readers w ill grav it ate t o
Chapt er 3 because it int roduces core T- SQL pr ogram m ing t echniques for dat a
access. You’ll apply the t echniques covered in this chapt er often as you select
subsets of rows and colum ns in dat a sources, group and aggregat e row s from a
table, pr ocess dat es, and j oin dat a from t wo or m ore t ables. Chapt er 3 also
considers special dat a access t opics, such as outer j oins, self joins and
subquer ies.
The next pair of chapt ers in Part I I , Chapt er 4 and Chapter 5, t ake a look at
program m ing database obj ect s t hat you will use for dat a access and dat a
m anipulat ion, such as view s, st ored procedures, user- defined funct ions, and
triggers. These dat abase obj ect s are im port ant for m any reasons, but one of t he
m ost im portant is t hat they bundle T- SQL st at em ent s for t heir easy reuse. I t is
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
widely know n that t he best code is the code t hat you don’t have t o writ e.
How ev er, if you do have t o write code, you should definit ely writ e it j ust onc, and
then reuse it whenever you need its funct ionalit y. St ored pr ocedures are
particu larly desirable dat abase obj ect s because t hey save com piled T- SQL
st at em ent s t hat can deliver significant speed advantages over resubm it t ing the

technologies enabling you t o do som ething. Chapt er 1 and Chapter 8 are bot h
relat ively sh ort chapt ers, but you m ay find them invaluable if y ou are t he k ind of
person who benefits from high-level overviews of a collect ion of t opics.
Chapt er 9 st arts w ith a close exam inat ion of how to use Windows Form s wit h
Visual Basic .NET. I t t hen sh ift s its focus to a review of t raditional class
processing concept s via Visual Basic .NET as an int r oduct ion t o class inherit ance,
a new obj ect -orient ed feat ure t hat mak es it s first appearance in Visual Basic wit h
Visual Basic .NET. Next t he t r eat m ent of classes progresses to t he handling of
built - in event s as well as the raising of cust om event s. Finally the chapt er closes
wit h an exam inat ion of the new exception handling techniques for processing run-
tim e errors.
Chapt er 10 is a how - t o guide for solutions t o t y pical problem s wit h ADO.NET.
Before launching into it s progression of sam ples show ing how to perform all k inds
of task s, t he chapt er st arts with an ov er view of the ADO.NET object m odel that
covers t he main obj ect s along wit h select ed propert ies and m et hods for each
obj ect . The how- t o guide focuses on dat a access t asks, such as select ing rows
and colum ns from SQL Ser ver database obj ect s, as well as data m anipulat ion
tasks, such as insert ing, updat ing, and delet ing rows in a table. Working t hr ough
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
the sam ples in t he how - to guide offers a hands-on feel for using t he
System .Dat a.SqlClient nam espace elem ents to perform t ypical t asks.
Chapt er 11 sw itches t he focus t o the Web by addressing t he cr eat ion and use of
ASP.NET solut ions. This chapt er st arts by int r oducing basic elem ent s that you
need t o know in order to use ASP.NET t o cr eat e great Web solut ions wit h Visual
Basic .NET. These include learning what happens as a page does a round-trip
from a browser t o a Web server and back to the browser— part icularly for data
associated with the page. Ot her prelim inary t opics t hat equip you for building
professional Web solutions include running t he sam e page in m ult iple browser
types and sniffing t he browser for cases in w hich you w ant t o send a page
opt im ized for a specific kind of browser t ype. Managem ent of session st at e is a

to have Visual Basic .NET or Visual St udio .NET installed on your com put er. (See
Chapt er 1 for m or e inform at ion on versions of Visual Basic .NET and Visual St udio
.NET.) I n addit ion, you’ll need SQL Server 2000, and for som e of the chapt ers,
you’ll need SQL Server 2000 updated wit h Web releases 1, 2, and 3. Chapt er 6
giv es the URLs for downloading Web releases 1 and 2. Chapt er 12 gives t wo
different URLs for downloading Web Release 3— one w it h t he SQL Server 2000
Web Ser vices Toolkit and t he ot her wit hout it .
For select ed chapt ers, you can run the sam ples wit h less software or different
operat ing sy st em s than the one t hat I used. For exam ple, chapt ers 2 through 5
will run on any operat ing sy stem that supports a com m ercial version of SQL
Ser ver 2000, such as Windows 98 or a m ore recent Windows operat ing syst em .
Chapt er 7 requires an operat ing syst em that support s Windows NT securit y, such
as Windows 2000 or Windows XP Professional. Chapt er 6, Chapt er 11, and
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapt er 1 3 r equir e Micr osoft I nternet I nform at ion Services (I I S) . I n addit ion,
Chapt er 6 requires t he installat ion of Web releases 1 and 2. For Chapt er 11, your
syst em needs t o m eet t he m inim um requirem ent s for ASP.NET. (See a not e in t he
“How Does ASP.NET Relate t o ASP?” section of Chapt er 8.) Several of t he
sam ples in Ch ap t e r 1 3 require Web Release 3 and its associated SQL Server
2000 Web Services Toolkit . Sa m ple Files
Sam ple files for t his book can be found at the Microsoft Press Web sit e, at
htt p: / / www.m icrosoft .com / m spress/ books/ 5792.asp. Clicking the Com panion
Cont ent link t akes you t o a page from w hich you can dow nload the sam ples.
Supplem ent al cont ent files for t his book can also be found on t he book’s
com panion CD. To access those files, insert t he com panion CD into y our
com put er’s CD- ROM drive and m ake a select ion fr om t he menu t hat appears. I f
the Aut oRun feat ure isn’t enabled on your sy st em ( if a m enu doesn’t appear when

Cha pt er 1 . Get t ing St ar t e d w it h Visual
Basic .N ET for SQL Serve r 2 0 0 0
This book aim s t o give professional developers t he background t hat they need t o
program SQL Server applications w it h Micr osoft Visual Basic .NET. This overall
goal im plies t hree guidelines:
• First, the book target s pract icing developers. I n m y experience, t hese ar e
busy professionals who need the det ails fast . These individuals already
know how t o build applicat ions. They buy a book t o learn how to build
those applicat ions wit h a specific set of tools.
• Second, the book is about building applicat ions for SQL Server 2000. This
focu s j ust ifies in-dept h coverage of SQL Server program m ing t opics— in
particu lar, T- SQL, Micr osoft ’s extension of t he St ruct ured Qu ery Language
(SQL) .
• Third, the book illust rat es how t o pr ogram in Visual Basic .NET, but wit h
particu lar em phasis on database issues for SQL Ser ver 2000. Special
at tent ion goes t o relat ed .NET t echnologies, such as the .NET Fram ew ork,
ADO.NET, ASP.NET, and XML Web ser vices.
My goal in t his chapt er is to equip you concept ually for t he rest of t he book .
Ther efore, t his chapter includes m at erial that acquaint s you w it h applicat ion
developm ent t echniques and t opics for SQL Server 2000 and Visual Basic .NET.
The discussion of t he sam ples in t his chapt er generally aim s t o convey broad
approaches inst ead of how t o r un t he sam ple. All t he r em aining chapt ers except
for Chapter 8, anot her conceptual chapt er, have sam ples with inst ruct ions aim ed
at professional developers.
I believe t hat t he overwhelm ing m aj ority of pr ofessional Visual Basic developers
hav e no hands-on fam iliarity wit h Visual Basic .NET and its relat ed t echnologies.
I f you already knew Visual Basic .NET, it wouldn’t m ake any sense t o buy a book
descr ibing how t o use it . This ch apt er t herefore focuses on how t o get st art ed
wit h Visual Basic .NET and one of its core relat ed technologies for t hose building
SQL Server applicat ions— ADO. NET. I also believe t hat m ost Visual Basic

.NET. You m ay not ice som e differences if you’re using anot her edit ion.
Visual St udio .NET can be inst alled on com put ers running one of five operat ing
syst em s: Windows 2000, Windows NT, Windows XP, Windows ME, and Windows
98. Not all the .NET Fram ework features are available for each operat ing syst em .
For exam ple, Windows 98, Windows Me, and Windows NT don’t support
developing ASP.NET Web applicat ions or XML Web services applications. The
sam ples for t his book ar e test ed on a com puter running Windows 2000 Ser ver,
which does support all .NET Fram ework feat ures.
St art ing Visual St udio .N ET
To open Visual St udio .NET, click t he St art butt on on t he Windows t ask bar,
choose Program s, and then choose Micr osoft Visual St udio .NET. Visual Studio
displays it s int egrat ed developm ent environm ent , including t he Start Page ( unless
you previously configured Visual St udio t o open different ly). Fr om the St art Page,
you can configure Visual St udio to work according to y our developm ent
preferences, and you can st art new solut ions as well as open exist ing proj ect s.
Con figuring Visua l St udio .N ET for Visual Ba sic .N ET
Use the links on the left side of t he St art Page t o begin configur ing Visual Studio
.NET for developing solut ions in Visual Basic .NET. Click the My Profile link t o
open a pane in w hich you can specify an overall profile as well as individually
indicat e your preferences for Keyboard Schem e, Window Layout, and Help Filter.
You also can designat e t he init ial page t hat Visual Basic .NET displays. When y ou
are beginning, it m ay be part icular ly convenient t o choose Show St art Page. As a
Visual Basic developer who has worked w it h Visual Basic 6, you m ight feel m ost
fam iliar wit h a lay out t hat reflect s your pr ior developm ent env ironm ent . Figure 1-
1 shows t hese My Profile select ions.
Figure 1 - 1 . M y Profile se lections for st a r t ing Visual St u dio .NET for a
Visu a l Basic developer.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Using t he Start Page

7HPSODWH1DPH &UHDWHV$
Windows
Applicat ion
Windows applicat ion w it h a form
Class Library Windows applicat ion suitable for a library of classes without a
form
Windows Control
Librar y
Proj ect for developing cust om reusable form controls for
Windows applicat ions
ASP.NET Web
Applicat ion
Web applicat ion on a Web server
ASP.NET Web
Service
XML Web service on a Web server
Web Cont rol Library Proj ect for dev eloping custom reusable cont rols for Web
applications
Console Applicat ion Com mand line application that operates in an MS- DOS–style
window (the Console)
Windows Service Windows service, form erly NT service, application that runs
in t he background w it hout it s ow n cust om user int erface
Em pt y Proj ect Local pr oj ect with no cust om st yle
Em pt y Web Proj ect Web proj ect wit h no custom st yle
New Proj ect I n
Ex ist ing Folder
Blank proj ect in an exist ing folder
Ther e are t wo main cat egories of tem plat es: Web proj ect s and local proj ect s. Web
proj ect s perm it a browser to serve as the client for a proj ect . Web pr oj ect s are
opt im ized for form processing on t he Web server. Local project s offer cust om

Explorer, t he Proj ect Folder t ext box in t he Propert ies window displays t he path of
the dir ect ory holding the solut ion’s files. I t is t his direct ory that you copy to
deploy your solution on anot her com puter w it h t he .NET Fram ework inst alled. The
solut ion won’t run wit hout the com m on language runt im e on t he com puter t o
which you copy t he direct ory cont aining t he .NET Fram ew ork solut ion. See
Chapt er 8 for m or e det ailed cov erage of the .NET Fram ework, including t he
runt im e and dist ribut ing .NET Fram ework solut ions as assem blies of files in
folders.
You can t est run t he applicat ion by ch oosing St art from t he Debug m enu, or by
pressing F5. This opens t he Console w indow wit h a prom pt t o ent er a first nam e.
Aft er you close y our applicat ion and save any changes to it , y our solut ion appears
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
on the St art Page for recent solutions. I f you st art Visual St udio .NET and the
solut ion you w ant t o open doesn’t appear on the Proj ect s tab of t he St art Page,
you can also open t he solut ion by click ing Open Proj ect . I n t he Open Proj ect
dialog box, ch oose t he file wit h t he .sln extension and t he solut ion’s nam e
(MyNam eI sFrom Console) . A solut ion can contain j ust one .sln file, but it can
cont ain m ult iple proj ect s.
You also can run t he solut ion and open t he Console window direct ly from
Windows Explorer wit hout using Visual St udio .NET. Open t he bin subdirect ory
wit hin t he direct ory cont aining the assem bly folder for t he solut ion. Then double-
click the MyNam eI sFrom Console.exe file. This opens t he Console w indow wit h the
prom pt for a first nam e. An Overview of AD O.N ET Capabilit ies
ADO.NET encapsulat es t he dat a access and dat a m anipulat ion for t he .NET
Fram ework. This sect ion gives you an overview of t he t opic that equips you for a
st arter sam ple in t he next sect ion. Microsoft ch ose t he nam e ADO.NET for t he
.NET Fram ew ork dat a access com ponent to indicat e its association w it h the earlier

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The three prov iders t aken together offer fast , highly focused access t o select ed
dat a sources as well as general access t o a wide range of possible data sources.
The SQL Server .NET data provider is opt im ized for SQL Ser ver 7.0 and SQL
Server 2000. This dat a provider connect s direct ly t o a SQL Server instance.
The OLE DB .NET dat a prov ider connect s t o OLE DB dat a sources through t wo
interm ediat e layers— t he OLE DB Service Com ponent and the classic OLE DB
provider int roduced along wit h ADO. The OLE DB Service Com ponent m anages
connect ion pooling and t ransact ion services. The classic OLE DB provider, in turn,
dir ect ly connect s t o a dat abase server. Micr osoft explicitly t est ed t he OLE DB .NET
dat a provider wit h SQL Server, Oracle, and Jet 4.0 databases. Use t he OLE DB
.NET dat a provider t o connect t o t he SQL Serv er 6.5 v ersion and earlier ones.
This provider is also good for connect ing t o y our Micr osoft Access solutions based
on the Jet 4.0 engine.
The OLE DB .NET dat a prov ider definit ely doesn’t work wit h t he OLE DB provider
for ODBC dat a sources (MSDASQL) . Because t he .NET OLE DB dat a pr ovider
doesn’t connect to ODBC data sources, you require t he ODBC .NET dat a provider
for connect ing t o ODBC dat a sources fr om y our .NET Fram ew ork solut ions.
Ther e are four m ain .NET dat a pr ovider classes for int eract ing w it h a rem ot e dat a
source. Th e nam es of t hese classes change slightly for each t ype of prov ider, but
each .NET dat a provider has the sam e four kinds of classes. The nam es for the
SQL Server .NET dat a provider classes for int eract ing wit h SQL Server instances
are SqlConnect ion, SqlCom m and, SqlDat aReader, and SqlDataAdapt er. You can
use t he SqlDataReader class for read-only applicat ions fr om a SQL Server dat a
source. Tw o especially convenient ways t o display result s wit h a SqlDat aReader
class are in a m essage box or the Visual St udio .NET Out put w indow. The
SqlDat aAdapt er class act s as a bridge between a r em ot e SQL Server dat a source
and a DataSet class inst ance inside a Visual Basic .NET solution.
A dat a set in a Visual St udio solut ion is a fift h t ype of ADO.NET class. A data set
can contain m ult iple t ables. A sixt h ADO.NET class is t he DataView class, w hich

Dim MySQLCnn1 As New _
SqlConnection(“Integrated Security=SSPI;" & _
"Data Source=myserver;Initial Catalog=mydb")

Aft er inst ant iat ing a SqlConnect ion obj ect , you need t o invoke its Open m et hod
befor e the obj ect can link anot her object based on one of t he ot her SQL Server
.NET dat a provider classes, such as SqlCom m and, SqlDat aAdapt er, or
SqlDat aReader, to a SQL Server inst ance. I nvoke t he Close m et hod t o recover t he
resources for a SqlConnection obj ect when your solution no longer needs it . The
Close m et hod rolls back any pending transactions and releases the connect ion t o
the connect ion pool. The Dispose m et hod is also available for rem oving
connect ions, but it invokes t he Close m et hod and perform s other .NET
adm inist rat ive funct ions. Microsoft recom m ends t he Close m et hod for rem oving a
connect ion. Unclosed connect ions aren’t ret urned t o t he connect ion pool.
SqlCom m and and SqlDa t aReader Cla sses
One way to put a connect ion t o use is t o em ploy it along w it h the SqlCom m and
and SqlDat aReader obj ect s. A SqlDataReader object can maint ain an open
for ward- only, read-only connect ion w it h a SQL Server database. While t he
SqlDat aReader using a SqlConnect ion object is open, you cannot use the
SqlConnect ion obj ect for any other purpose except t o close t he connect ion.
Closing a SqlDat aReader obj ect releases it s associat ed SqlConnect ion obj ect for
ot her uses. The SqlDat aReader class doesn’t have a const ruct or st at em ent . You
declare t he SqlDat aReader obj ect w it h a Dim stat em ent and assign a result set
from a SqlCom mand obj ect t o a SqlDataReader wit h the Ex ecut eReader m et hod
of the SqlCom m and obj ect . Finally, invoke t he SqlDat aReader obj ect Read
m ethod t o open a r ow from t he resu lt set in t he SqlDataReader.
The SqlCom m and obj ect can ser ve m ult iple functions, including processing a T-
SQL st atem ent against a connect ion. When used in t his fashion, t he SqlCom m and
can take t wo argum ent s. The first can be a T- SQL dat a access stat em ent , such as
SELECT * FROM MyTable . The second SqlCom m and argum ent designat es t he