Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .Net - P1

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

138
lượt xem 30
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tìm hiểu làm thế nào để chuyển dữ liệu vào các giải pháp với SQL Server 2000, Visual Basic NET,. Và XML. Lấy một nắm bắt cơ bản của SQL Server 2000 truy cập dữ liệu, thao tác dữ liệu, và dữ liệu kỹ thuật lập trình định nghĩa T-SQL, Visual Basic NET ngôn ngữ. Cải tiến, Microsoft Visual Studio NET tích hợp. Môi trường phát triển tiến bộ, và các công nghệ nhà nước-of-the-art của NET framework.. Nếu bạn đang tìm kiếm những hiểu biết chuyên gia về làm thế nào để xây dựng mạnh mẽ, các giải pháp...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .Net - P1

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Program m ing Microsoft ® SQL Server™ 2000 wit h Micr osoft Visual Basic® .NET Foreword Acknow ledgm ent s I nt roduct ion Who’s t he Book For? What ’s Special About This Book? How’s t he Book Organized? Syst em Requirem ent s Sam ple Files Support 1. Get t ing St art ed w it h Visual Basic .NET for SQL Serv er 2000 Visual St udio .NET, t he Visual Basic .NET I DE An Overv iew of ADO.NET Capabilit ies A St art er ADO.NET Sam ple Using Query Analyzer 2. Tables and Dat a Types Chapt er Resources Dat a Types for Tables Scr ipt ing Tables 3. Pr ogram m ing Dat a Access wit h T- SQL I nt r oduct ion t o Dat a Access wit h T- SQL Aggregat ing and Grouping Rows Processing Dat es Joins and Subquer ies 4. Pr ogram m ing Views and St ored Procedures I nt r oduct ion t o Views Creat ing and Using Views Views for Rem ot e and Het erogeneous Sources I nt r oduct ion t o St ored Procedur es Creat ing and Using St or ed Procedures Processing St or ed Procedur e Out put s I nsert ing, Updat ing, and Delet ing Rows Program m ing Condit ional Result Set s 5. Pr ogram m ing User - Defined Funct ions and Tr iggers I nt r oduct ion t o User - Defined Funct ions Creat ing and I nv ok ing Scalar UDFs Creat ing and I nv ok ing Table- Valued UDFs I nt r oduct ion t o Tr iggers Creat ing and Managing Tr iggers 6. SQL Serv er 2000 XML Funct ionalit y Overv iew of XML Support XML Form at s and Schem as URL Access t o SQL Serv er Tem plat e Access t o SQL Server 7. SQL Serv er 2000 Secur it y Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Overv iew of SQL Serv er Secur it y I nt r oduct ion t o Special Securit y I ssues Sam ples for Logins and Users Sam ples for Assigning Perm issions 8. Overv iew of t he .NET Fram ework An I nt r oduct ion t o t he .NET Fram ework An Overv iew of ASP.NET XML Web Ser v ices 9. Cr eat ing Windows Applicat ions Get t ing St art ed w it h Windows For m 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. Progr am m ing Windows Solut ions w it h ADO. NET An Overv iew of ADO.NET Obj ect s Making Connect ions Wor k ing w it h Com m and and Dat aReader Obj ect s Dat aAdapt ers , Dat a Set s, Form s, and Form Cont rols Modify ing, I nsert ing, and Delet ing Rows 11. Progr am m ing ASP.NET Solut ions Rev iew of ASP.NET Design I ssues Creat ing and Running ASP.NET Solut ions Session St at e Managem ent Dat a on Web Pages Validat ing t he Dat a on a Web Page 12. Managing XML wit h Visual Basic .NET SQL Ser ver Web Releases Overv iew of XML Technologies Generat ing XML Docum ent s wit h t he .NET Fram ework Dy nam ically Set t ing an XML Result Set The I nt erplay Bet w een XML and Dat a Set s Creat ing HTML Pages wit h XSLT 13. Cr eat ing Solut ions wit h XML Web Ser v ices Overv iew of Web ser v ices A Web Serv ice t o Ret ur n a Com put ed Result A Web Serv ice t o Ret ur n Values from Tables The SQL Ser ver 2000 Web Ser v ices Toolk it Mor e on Populat ing Cont rols wit h Web Ser vices About t he Aut hor Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
For e w or d During m y five years at Micr osoft , I ’v e been helping developers underst and t echnologies such as Microsoft Visual St udio, Microsoft SQL Ser ver, and Micr osoft Office Developer. Dur ing t he past t wo years, I hav e w orked on t he Microsoft Office XP Visual Basic Language Refer ence, and now, t he MSDN Office Dev eloper Cent er. I n t he m ont hly colum n on MSDN, Office Talk, I have wr it t en art icles t o help Office developers underst and t he .NET plat for m and how it affect s t heir curr ent and fut ure developm ent effort s. As I wr it e t his for ew ord t o Rick Dobson’s book on program m ing Microsoft SQL Ser ver solut ions wit h Microsoft Visual Basic .NET, I t hink back t o m y own experiences dev eloping soft war e applicat ions w it h Visual Basic. My first experience wit h Visual Basic was lear ning t he language using v ersion 3.0. I rem em ber pick ing up m y first Visual Basic beginner’s book and being excit ed as I dev eloped m y first few “ Hello, Wor ld” applicat ions. I couldn’t believ e how quick and easy it was t o dev elop soft ware applicat ions t hat operat ed sim ilar ly t o ot her popular sharewar e program s of t hat t im e. Howev er, dur ing t hat t im e I also discov er ed som e of t he short com ings of Visual Basic as an ent erpr ise- lev el developm ent language. I t was t hen t hat I t ur ned m y at t ent ion t o C+ + . I r em em ber being v ery frust rat ed at t r y ing t o lear n t he language, t ry ing t o underst and concept s such as point ers, m em ory allocat ion, and t rue obj ect - or ient ed program m ing. I t ook classes on C+ + at t he local universit y , but I got even m or e fr ust rat ed hav ing t o wait m ont hs unt il I was t aught 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 t ry ing t o lear n C+ + and hav e been using Visual Basic t o dev elop soft ware applicat ions ever since. As each new v ersion of Visual Basic was r eleased, I readied m yself t o learn new soft war e developm ent t echnologies. First it was Act iveX cont rol developm ent . Then it was calling t he Windows API . Next it was DHTML Applicat ions. Then it was dat abase dev elopm ent using Micr osoft SQL Ser ver. I t alw ays seem ed as t hough I had t o lear n a new language and a new developm ent paradigm for ev er y new t echnology t hat cam e along. I kept t hink ing t hat t her e had t o be an easier and m or e unified appr oach. Well, now w e’ve r eached t he adv ent of t he Micr osoft .NET plat form , and wit h it , a revolut ion in t he Visual Basic language, Microsoft Visual Basic .NET. I believ e t hat Visual Basic .NET will pr ov ide soft ware dev elopers wit h new opport unit ies for quickly and easily designing int egrat ed soft war e applicat ions t hat connect businesses and indiv iduals anyt im e, anyw her e, and on v irt ually any soft ware dev ice. Wit h advances in t he Visual Basic .NET language, Visual Basic . NET dev elopers will finally be on a par w it h t heir C+ + and C# count erpart s, part icipat ing in m any high- end dev elopm ent pr oj ect s. Wit h Visual St udio .NET feat ur es such as cross- language debugging, along w it h Visual Basic .NET conform ance t o t he com m on t ype syst em and t he com m on language r unt im e, organizat ions can drive down t heir developm ent cost s by t apping int o t he w ide range of sk ills t hat Visual Basic .NET dev eloper s now possess. Tr ue obj ect - orient ed pr ogram m ing is now available in Visual Basic .NET, including feat ur es such as inher it ance and m et hod ov er loading. I t ’s now sim pler t o call t he Windows API by using t he .NET Fram ew or k Class Libraries. Web applicat ion dev elopm ent is now as easy as dev eloping Windows form s–based applicat ions. Dat abase applicat ion developm ent is m ade easier by unit ing disparat e dat a obj ect libraries such as DAO, RDO, OLE DB, and ADO under ADO. NET, ut ilizing t he power of XML t o consum e and t ransm it r elat ional dat a ov er com put er net w orks. And a new t echnology, XML Web serv ices, allows Visual Basic .NET developers t o host t heir soft ware applicat ions’ logic ov er t he Web. Addit ionally , a big issue for Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
soft war e developers t oday is t hat of soft war e applicat ion deploym ent and versioning. I f y ou don’t agree, j ust ask any soft ware dev eloper about “ DLL hell,” and y ou’re bound t o get an earful. For m any .NET applicat ions, t he .NET plat form feat ur es “ copy and past e” or XCOPY deploym ent . ( Users sim ply copy y our applicat ion files from t he source m edia t o any single direct ory and r un t he applicat ion. ) And because .NET no longer r elies on t he r egist ry , virt ually all DLL com pat ibilit y issues go away. Wit h t his book, Rick aim s t o give you t he sk ills y ou need t o pr ogram SQL Serv er solut ions wit h Visual Basic .NET. I k now y ou w ill find Rick’s book helpful. Rick brings his exper ience t o bear from t hree prev ious books: Pr ogram m ing Micr osoft Access Version 2002 ( Microsoft Press, 2001) , Pr ogram m ing Micr osoft Access 2000 ( Micr osoft Press, 1999) , and Pr ofessional SQL Serv er Developm ent wit h Access 2000 ( Wr ox Pr ess I nc., 2000) . Rick also br ings his exper ience of leading a successful nat ionwide sem inar t our. Mor e im port ant , I know y ou w ill enj oy Rick ’s book because of his deep int erest in Visual Basic .NET and SQL Ser ver, and in helping y ou, t he pr ofessional dev eloper, underst and and apply t hese t echnologies in y our daily soft ware applicat ion dev elopm ent proj ect s. Paul Cornell MSDN Office Dev eloper Cent er ht t p: / / m sdn.m icr osoft .com / officeMicrosoft Corporat ion February 2002 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Ack now le dgm e nt s This sect ion offers m e a chance t o say t hank you t o all w ho helped m ak e t his book possible. I wish t o offer special recognit ion t o five support r esources. First , t he folks at Microsoft Press have been fant ast ic. Dave Clar k, an acquisit ions edit or , select ed m e t o w rit e t he book j ust m ont hs aft er I com plet ed anot her book for Microsoft Press. Dick Br ow n, m y pr oj ect edit or, st aunchly st ood up for his percept ion of how t o m ake t he book ’s organizat ion and cont ent clear t o y ou wit hout being pet t y or bor ing t o m e. Dick also light ened m y load subst ant ially by show ing a real k nack for edit ing m y t ext w it hout dist ort ing t he or iginal int ent . When Dick was especially busy, he handed off som e of his load t o Jean Ross, who also did an adm irable j ob. Ot hers at Micr osoft Press who cont r ibut ed t o m y w ell- being in one way or anot her include Aar on Lavin and Anne Ham ilt on. Second, I had excellent wor k ing r elat ions wit h sev eral professionals w it hin Micr osoft . Paul Cor nell, a widely k now n t echnical edit or at Micr osoft , w as kind enough t o share his insight s on how t o pr esent .NET concept s com pellingly. I want t o t hank Paul especially for writ ing t he For eword t o t his book. Kart hik Rav indran serv ed as t he MSXML Bet a Pr oduct Lead Engineer at Microsoft Pr oduct Support Serv ices dur ing t he t im e t hat I wrot e t his book. He provided valuable t echnical cont ent about t he SQL Serv er 2000 Web releases. Ot her Microsoft represent at iv es pr ov iding m oral and t echnical support for t his book include Richard Waym ir e and Jan Shanahan. Third, I want t o express m y appreciat ion t o t he m any r eaders, sem inar at t endees, and sit e v isit ors w ho t ook t he t im e t o t ell m e what I did right or wrong for t hem , and also t o t hose w ho shared t heir t echnical support quest ions wit h m e. I t is t hr ough t his k ind of feedback t hat I am able t o k now w hat ’s im port ant t o pract icing developers. I encourage y ou t o visit m y m ain Web sit e ( ht t p: / / www .pr ogram m ingm saccess.com ) and sign t he guest book. The ent ry for m includes space for you t o leave your evaluat ion of t his book or y our quest ion about a t opic cov er ed in t he book . I prom ise t o do m y best t o r eply per sonally. I n any ev ent , I definit ely r ead all m essages and use t hem so t hat I can serv e y ou bet t er w it h fut ur e edit ions of t his, and ot her, books. Fourt h, I want t o t ell t he w orld how grat eful I am t o m y w ife, Virginia. Wit hout Virginia’s warm support , lov e, and care, t his book w ould be less professional. She reliev es m e of near ly ev er y r esponsibilit y around t he house w hen I undert ak e a book proj ect . I n addit ion, she offers st rat egic advice on t he issues t o address and t heir st yle of cov erage. When I r un out of t im e, she ev en pit ches in w it h t he proofreading. Fift h, it is im port ant for m e t o give praise and glory t o m y Lord and Sav ior, Jesus Chr ist , w ho I believe gave m e t he st rengt h and wisdom t o w r it e t his book. I n addit ion, He gav e m e healt h dur ing t he long gest at ion per iod t hat result ed in t he birt h of t his book . I t is m y prayer t hat t he book pr ov e t o be a blessing t o y ou. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
I nt r odu ct ion Any one w ho buys a book —or considers buy ing it —want s t o k now w ho t he book is for , w hat set s it apart fr om ot hers lik e it , and how t he book is organized. This int r oduct ion cov ers t hose t hr ee quest ions, and it also discusses syst em requir em ent s, sam ple files, and support . • First , w h o is t h e b ook for ? Ther e ar e at least t wo answers t o t his quest ion. One answ er is t hat t he book t arget s professional dev elopers ( and ot hers aspir ing t o be pr ofessional dev elopers) . The second group t he book addr esses is t hose who want t o build full- feat ured, secure SQL Serv er solut ions wit h Visual Basic .NET. • Se con d, w h a t ’s sp ecia l a bou t t h e b ook ? I hope you com e t o believ e t hat t he m ost im port ant answer t o t his quest ion is t hat t he book consider ed qualit y and dept h of coverage m or e im port ant t han r ushing t o m arket . The book w ill arr iv e on bookshelv es m or e t han t hr ee m ont hs aft er t he official release of t he . NET Fram ew ork. I t is m y w ish t hat you der iv e value from t he ext ra t im e t ak en t o develop t he m any code sam ples and t he in- dept h discussions of advanced t opics, such as class inher it ance, ASP.NET, and XML Web serv ices. • Th ird , h ow is t h e b ook org an iz e d? The short answer is t hat t her e ar e t wo m ain sect ions. One sect ion int roduces SQL Ser ver concept s as it dem onst rat es T- SQL ( Tr ansact SQL) pr ogram m ing t echniques. Aft er conv ey ing SQL Ser ver basic building blocks in t he first part , t he second part rev eals how t o put t hose part s t oget her w it h Visual Basic .NET and relat ed t echnologies int o SQL Ser ver solut ions for handling com m on dat abase chores. The t hr ee support it em s include a br ief descr ipt ion of t he book’s com panion CD and how t o use it , Micr osoft Pr ess Support I nfor m at ion for t his book, and a sum m ary of syst em and soft war e requirem ent s for t he sam ple code pr esent ed in t he book. W ho’s t h e Book For ? This book t arget s pr ofessional Visual Basic and Visual Basic for Applicat ions dev elopers. From m y sem inar t ours and Web sit es ( ht t p: / / www .pr ogram m ingm saccess.com and ht t p: / / www.cabinc.net ) , I know t hat t hese professionals ar e dr iven by a passion t o deliv er solut ions t o t heir client s t hrough applying t he m ost innov at iv e t echnologies t heir client s w ill accept . I n- house dev elopers are t he go- t o persons for get t ing result s fast — part icular ly for cust om in- house sy st em s and dat abases. I ndependent developers specialize in serv ing niche sit uat ions t hat can include under - ser ved business needs and w or k ov erflows. I n bot h cases, t hese pr ofessionals need t raining m at erials t hat addr ess pract ical business requir em ent s w hile showcasing innov at ive t echnologies wit hout wast ing t heir t im e. This book st rives t o ser ve t his broad need in t w o specific areas. This book is for developers look ing for code sam ples and st ep- by - st ep inst ruct ions for building SQL Ser ver 2000 solut ions wit h Visual Basic .NET. The book focuses on t he int egrat ion of SQL Serv er 2000 w it h .NET t echnologies t apped v ia Visual Basic .NET. I t is m y fir m belief t hat y ou cannot creat e gr eat SQL Ser v er solut ions in any program m ing language w it hout k nowing SQL Server. Therefore, t his book Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
goes beyond t radit ional coverage of SQL Serv er for Visual Basic dev elopers. You’ll lear n T- SQL pr ogram m ing t echniques for dat a access, dat a m anipulat ion, and dat a definit ion. A whole chapt er equips you t o secur e your SQL Ser ver solut ions. I n addit ion, t her e’s plent y of cont ent in t his book on Visual Basic .NET and relat ed t echnologies, such as ADO.NET, ASP.NET, XML ( Ex t ensible Mark up Language) , and XML Web serv ices. The present at ion of t hese t echnologies dem onst rat es coding t echniques and ex plor es concept s t hat equip y ou t o build bet t er solut ions wit h SQL Ser ver 2000 dat abases. I n addit ion, t he book highlight s innovat ions int r oduced t hrough t he Web releases for SQL Serv er 2000 t hat int egrat e SQL Ser ver 2000 t ight ly w it h Visual Basic .NET. This isn’t a book about XML, but t hr ee of t he book ’s 13 chapt ers focus in w hole or in part on XML. Therefore, t hose seek ing pract ical dem onst rat ions of how t o use XML w it h SQL Ser ver and Visual Basic .NET w ill deriv e value fr om t his book . I f you hav e look ed at any of t he com put er m agazines ov er t he past couple of y ears, you k now t hat XML is com ing t o a solut ion near you. However, t he rapid pace of XML innovat ion m ay have dissuaded som e fr om j um ping on t he bandwagon w hile t hey wait t o see w hat ’s going t o last and what ’s j ust a fad. I n t he book’s t hr ee chapt ers on XML t echnology, you’ll learn about XML docum ent s, fragm ent s, and for m at t ing as w ell as r elat ed t echnologies, such as XPat h ( XML Pat h Language) quer ies, XSLT ( Ext ensible St y lesheet Language Transform at ion) , and WSDL ( Web Ser vices Descript ion Language) . W ha t ’s Spe cia l Abou t Th is Book ? Ther e ar e sev eral feat ur es t hat m ake t his book st and apart from t he flood of books on .NET. One of t he m ost im port ant of t hese is t hat t his book didn’t rush t o m ark et but rat her shipped m ont hs aft er t he r elease of t he .NET Fram ew ork . This allowed m e enough t im e t o filt er, exam ine, and uncov er w hat w er e t he m ost useful and innovat iv e feat ur es for Visual Basic .NET developers building SQL Ser ver solut ions. For ex am ple, t he book includes a whole chapt er on creat ing solut ions wit h XML Web serv ices. That chapt er includes t wo m aj or sect ions on t he SQL Ser ver 2000 Web Serv ices Toolk it , w hich didn’t ship unt il t he day of t he .NET Fram ew ork release. The .NET Fram ew ork cont ent is at a professional lev el, but it isn’t j ust for t echies. This book doesn’t assum e any pr ior know ledge of t he .NET Fr am ew ork . I t does assum e t hat you get paid for building solut ions program m at ically and t hat at least som e of t hose solut ions are for SQL Serv er dat abases. Ther efor e, t he book explains basic .NET concept s and dem onst rat es how t o achiev e pract ical result s wit h t hose concept s t hr ough a huge collect ion of .NET code sam ples. This book is about building solut ions for SQL Ser ver 2000. I include coverage of t he m any special feat ur es t hat t ie Visual Basic .NET and SQL Serv er 2000 closely t o one anot her. Alt hough t here is coverage of general .NET dat abase t echniques, t his book div es deeply int o T- SQL pr ogr am m ing t echniques so t hat y ou can creat e your own cust om dat abase obj ect s, such as t ables, st or ed pr ocedur es, views, t riggers, and user - defined funct ions. I n addit ion, t her e is separat e cov erage of t he XML feat ures released w it h SQL Serv er 2000 as well as separat e coverage of t he XML feat ures in t he first t hr ee Web releases t hat shipped for SQL Ser ver 2000. There ar e num er ous code sam ples t hroughout t he book. These will equip you t o build solut ions w it 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 aut hor, Rick Dobson. I have t rained professional dev elopers in Aust ralia, England, Canada, and t hr oughout t he Unit ed St at es. This is m y fourt h book in four years, and you can find m y art icles in popular publicat ions and Web sit es, such as SQL Serv er Magazine and MSDN Online. As a Webm ast er , m y m ain sit e Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
( ht t p: / / www .pr ogram m ingm saccess.com ) serv es hundr eds of t housands of sessions t o dev elopers each y ear. I const ant ly ex am ine t heir v iewing habit s at t he sit e t o det erm ine w hat int erest s t hem . I n addit ion, m y sit e feat ures scores of answers t o t echnical support quest ions subm it t ed by professional developers. My goal in offer ing answers t o t hese quest ions is t o st ay in t ouch w it h pract icing dev elopers wor ldw ide so t hat m y new book s address t he needs of pract icing, professional dev elopers. H ow ’s t h e Book Or ga n ized? Ther e ar e t wo m ain part s t o t his book t ied t oget her by an int roduct ory part . Part I I , t he first m ain part , dwells on SQL Ser ver t echniques. Part I I I builds on t he SQL Ser ver background as it lays a firm foundat ion in .NET t echniques for Visual Basic .NET dev elopers. Part I , t he int r oduct ory part , dem onst rat es way s t o use SQL Ser ver and Visual Basic .NET t oget her. Pa r t I , I nt r odu ct ion Part I , w hich includes only Chapt er 1, has t hree m ain goals. First , it acquaint s y ou wit h t he basics of Visual Basic .NET w it hin Visual St udio .NET. You can t hink of Visual Basic .NET as a m aj or upgrade t o t he Visual Basic 5 or 6 t hat you are probably using cur rent ly . This first sect ion int r oduces som e concept s t hat you w ill find useful as y ou init ially learn t he landscape of Visual Basic .NET. The second goal of Chapt er 1 is t o int r oduce ADO.NET. I f y ou t hink of Visual Basic .NET as a m aj or upgrade t o Visual Basic 6, ADO. NET is m or e lik e a m aj or overhaul of ADO. I n t wo sect ions, you get an int roduct ion t o ADO.NET classes— part icular ly as t hey relat e t o SQL Serv er— and y ou get a chance t o see a couple of beginner sam ples of how t o creat e SQL Serv er solut ions w it h Visual Basic .NET and ADO.NET. The t hird goal of t he int roduct ory part is t o ex pose y ou t o Query Analy zer . This is a SQL Ser ver client t ool t hat ships w it h all com m ercial edit ions of SQL Ser ver 2000. You can t hink of it as an I DE for T- SQL code. Most of t he book’s first par t relies heav ily on T- SQL, and t her efor e hav ing a conv enient env ir onm ent for debugging and running T- SQL code is helpful. The final sect ion of Chapt er 1 addresses t his goal. Pa r t I I , SQL Se r v e r Part I I consist s of six r elat iv ely short chapt ers t hat focus subst ant ially on program m ing SQL Serv er 2000 w it h T- SQL. Chapt er 2 and Chapt er 3 int r oduce T- SQL and SQL Serv er dat a t ypes. I f y ou ar e going t o program SQL Ser ver and creat e efficient , fast solut ions, y ou m ust lear n SQL Ser ver dat a t ypes, which is one of t he m ain point s conveyed by Chapt er 2. Many r eaders w ill grav it at e t o Chapt er 3 because it int roduces cor e T- SQL pr ogram m ing t echniques for dat a access. You’ll apply t he t echniques covered in t his chapt er oft en as y ou select subset s of r ows and colum ns in dat a sources, group and aggregat e row s from a t able, pr ocess dat es, and j oin dat a fr om t w o or m or e t ables. Chapt er 3 also considers special dat a access t opics, such as out er j oins, self j oins and subquer ies. The next pair of chapt er s in Part I I , Chapt er 4 and Chapt er 5, t ak e a look at program m ing dat abase obj ect s t hat you w ill use for dat a access and dat a m anipulat ion, such as v iews, st ored procedures, user - defined funct ions, and t riggers. These dat abase obj ect s are im port ant for m any reasons, but one of t he m ost im port ant is t hat t hey bundle T- SQL st at em ent s for t heir easy r euse. I t is Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
widely k now n t hat t he best code is t he code t hat you don’t hav e t o wr it e. Howev er, if y ou do have t o w rit e code, y ou should definit ely wr it e it j ust onc, and t hen reuse it w henev er you need it s funct ionalit y. St or ed pr ocedur es are part icular ly desirable dat abase obj ect s because t hey save com piled T- SQL st at em ent s t hat can deliv er significant speed advant ages ov er r esubm it t ing t he sam e T- SQL st at em ent for com pilat ion each t im e y ou want t o perform a dat a access or dat a m anipulat ion t ask. Chapt er 4 and Chapt er 5 ar e also im port ant because t hey conv ey T- SQL sy nt ax for using param et ers and condit ional logic t hat support dy nam ic r un- t im e behav ior and user int eract iv it y. One of t he m ost im port ant feat ur es of SQL Ser ver 2000 is it s XML funct ionalit y . Because XML as a t opic is changing so rapidly, Micr osoft adopt ed a st rat egy of upgrading t he SQL Serv er 2000 XML funct ionalit y t hrough Web releases. Alt hough t hose w it h SQL Serv er 2000 can dow nload t he Web releases w it hout charge from t he Microsoft Web sit e, t he Web r eleases ar e fully support ed. Chapt er 6 int r oduces core XML funct ionalit y int roduced w it h SQL Ser ver 2000 as w ell as funct ionalit y fr om t he fir st t wo Web r eleases. I n part icular, y ou can learn in t his chapt er about I I S v irt ual direct ories as well as form at s for XML docum ent s and schem as. You also learn about t em plat es in virt ual dir ect or ies t hat facilit at e dat a access and dat a m anipulat ion t asks over t he Web. Chapt er 7 closes out t he SQL Ser ver part of t he book w it h an in- dept h look at program m ing SQL Serv er secur it y . I n t hese t im es, secur it y has grown int o a m onum ent al t opic, and t his chapt er can k eep y ou out of t rouble by blocking hackers from get t ing int o or corr upt ing y our dat abase. You learn such t opics as how t o creat e and m anage differ ent t y pes of login and user account s and how t o cont r ol t he perm issions available t o indiv idual account s as well as gr oups of account s. By learning how t o script account s and perm issions w it h T- SQL, y ou sim plify r ev ising and updat ing secur it y as condit ions change ( for exam ple, w hen users leav e t he com pany or w hen new , sensit iv e dat a get s added t o a t able) . Pa r t I I I , .N ET Chapt er 8 st art s t he .NET part of t he book w it h a r ev iew of select ed .NET t opics t hat ar e cover ed in t he init ial look Chapt er 1 offer ed at t he .NET Fram ewor k. This chapt er pr ov ides an ov erv iew of t he archit ect ur e for .NET solut ions, and it dr ills down on t wo t opics: ASP.NET and XML Web ser vices. The general purpose of t his chapt er is t he sam e as Chapt er 1, w hich is t o int r oduce concept s. The em phasis in Chapt er 8 isn’t how y ou do som et hing, but rat her w hat are t he m aj or t echnologies enabling y ou t o do som et hing. Chapt er 1 and Chapt er 8 are bot h relat iv ely short chapt ers, but you m ay find t hem invaluable if y ou are t he k ind of person w ho benefit s from high- lev el ov er views of a collect ion of t opics. Chapt er 9 st art s wit h a close exam inat ion of how t o use Windows Form s wit h Visual Basic .NET. I t t hen shift s it s focus t o a review of t radit ional class processing concept s via Visual Basic .NET as an int r oduct ion t o class inherit ance, a new obj ect - or ient ed feat ure t hat m ak es it s fir st appearance in Visual Basic w it h Visual Basic .NET. Next t he t r eat m ent of classes progr esses t o t he handling of built - in ev ent s as w ell as t he raising of cust om ev ent s. Finally t he chapt er closes wit h an exam inat ion of t he new except ion handling t echniques for pr ocessing r un- t im e errors. Chapt er 10 is a how - t o guide for solut ions t o t y pical pr oblem s w it h ADO.NET. Befor e launching int o it s progr ession of sam ples show ing how t o perform all k inds of t asks, t he chapt er st art s wit h an ov er v iew of t he ADO.NET obj ect m odel t hat covers t he m ain obj ect s along w it 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 fr om SQL Ser ver dat abase obj ect s, as well as dat a m anipulat ion t asks, such as insert ing, updat ing, and delet ing rows in a t able. Work ing t hr ough Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
t he sam ples in t he how - t o guide offers a hands- on feel for using t he Syst em .Dat a.SqlClient nam espace elem ent s t o per form t ypical t asks. Chapt er 11 swit ches t he focus t o t he Web by addressing t he cr eat ion and use of ASP.NET solut ions. This chapt er st art s by int r oducing basic elem ent s t hat you need t o k now in order t o use ASP.NET t o cr eat e great Web solut ions wit h Visual Basic .NET. These include lear ning w hat happens as a page does a r ound- t r ip from a browser t o a Web serv er and back t o t he br owser— part icularly for dat a associat ed wit h t he page. Ot her pr elim inary t opics t hat equip y ou for building professional Web solut ions include running t he sam e page in m ult iple br owser t ypes and sniffing t he browser for cases in w hich y ou want 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 m aj or t opic in t he chapt er , and y ou lear n how t o use enhancem ent s t o Session variables for Web farm s as well as t he new v iew st at e var iables, a non- ser ver - based t echnique for m anaging st at e in ASP.NET solut ions. The last t wo sect ions in t he chapt er deal w it h ADO.NET t opics in ASP.NET solut ions and t he new aut om at ic dat a validat ion feat ur es built r ight int o ASP.NET. The last t wo chapt ers in t he book explor e how XML int erplays wit h Visual St udio .NET and SQL Ser ver 2000. For exam ple, Chapt er 12 exam ines special t ools in Visual St udio .NET t o facilit at e t he design and edit ing of XML docum ent s and schem as. I n addit ion, y ou learn how t o designat e XPat h queries t hat accept run- t im e input for r et ur ning SQL Ser ver result set s inside Visual Basic .NET program s. The chapt er dem onst rat es t echniques for processing t he XML docum ent associat ed wit h all ADO. NET dat a set obj ect s. I n t he chapt er’s last sect ion, I present a couple of code sam ples t hat illust rat e how t o pr ogram st at ic HTML pages based on XML docum ent s wit h XSLT. Ch a pt e r 1 3 dr ills dow n on XML Web serv ices by dem onst rat ing several differ ent approaches for cr eat ing Web serv ices as well as consum ing XML out put from Web serv ices. Web serv ices behave som ewhat like COM obj ect s in t hat y ou can set up serv er applicat ions for client applicat ions. The ser ver applicat ions expose m et hods t o w hich t he client applicat ions can pass param et ers. XML com es int o play wit h Web serv ices in a couple of areas. First , Web ser vices repr esent t heir input s and out put s v ia WSDL, an XML- based language t hat form ally describes an XML Web serv ice. Second, Web ser vices ret urn dat a t o t heir client s as XML docum ent s or docum ent fragm ent s. Syst e m Re qu ir e m en t s The requir em ent s for t his book var y by chapt er. I developed and t est ed all sam ples t hroughout t his book on a com put er equipped wit h Windows 2000 Ser ver, SQL Serv er Ent erprise Edit ion, and t he Ent erprise Dev eloper Edit ion of Visual St udio .NET, w hich includes Visual Basic .NET. To use t his book, y ou’ll need t o have Visual Basic .NET or Visual St udio .NET inst alled on your com put er . ( See Chapt er 1 for m or e inform at ion on v ersions of Visual Basic .NET and Visual St udio .NET.) I n addit ion, y ou’ll need SQL Serv er 2000, and for som e of t he chapt ers, you’ll need SQL Serv er 2000 updat ed wit h Web r eleases 1, 2, and 3. Chapt er 6 giv es t he URLs for downloading Web r eleases 1 and 2. Chapt er 12 giv es t wo different URLs for downloading Web Release 3— one w it h t he SQL Serv er 2000 Web Ser v ices Toolk it and t he ot her wit hout it . For select ed chapt ers, y ou can run t he sam ples wit h less soft ware or different operat ing syst em s t han t he one t hat I used. For exam ple, chapt ers 2 t hrough 5 will run on any operat ing syst em t hat support s a com m ercial version of SQL Ser ver 2000, such as Windows 98 or a m or e recent Windows operat ing syst em . Chapt er 7 r equires an operat ing syst em t hat support s Windows NT secur it 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.
Ch a pt e r 1 3 r equir e Microsoft I nt ernet I nform at ion Serv ices ( I I S) . I n addit ion, Chapt er 6 r equires t he inst allat ion of Web r eleases 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 Relat e t o ASP?” sect ion of Chapt er 8.) Several of t he sam ples in Ch ap t er 1 3 require Web Release 3 and it s associat ed SQL Ser ver 2000 Web Serv ices Toolkit . Sa m ple File s Sam ple files for t his book can be found at t he Microsoft Press Web sit e, at ht t p: / / www .m icrosoft .com / m spress/ books/ 5792.asp. Click ing t he Com panion Cont ent link t ak es you t o a page fr om w hich y ou can dow nload t he 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 t hose files, insert t he com panion CD int o y our com put er’s CD- ROM dr ive and m ake a select ion fr om t he m enu t hat appears. I f t he Aut oRun feat ure isn’t enabled on y our sy st em ( if a m enu doesn’t appear when you insert t he disc in y our com put er ’s CD- ROM drive) , r un St art CD.ex e in t he r oot folder of t he com panion CD. I nst alling t he sam ple files on y our hard disk requir es approx im at ely 15.3 MB of disk space. I f y ou have t rouble r unning any of t hese files, r efer t o t he t ext in t he book t hat describes t hese pr ogr am s. Aside from t he sam ple files t hat t his book discusses, t he book ’s supplem ent al cont ent includes a st and- alone eBook inst allat ion t hat w ill allow y ou t o access an elect ronic v ersion of t he pr int book direct ly from your deskt op. Su ppor t Ev er y effort has been m ade t o ensure t he accur acy of t his book and t he cont ent s of t he com panion CD. Microsoft Press pr ov ides cor rect ions for books t hr ough t he World Wide Web at t he follow ing address: ht t p: / / www .m icrosoft .com / m spress/ support To connect dir ect ly t o t he Micr osoft Pr ess Know ledge Base and ent er a query regarding a quest ion or an issue t hat you m ay have, go t o: ht t p: / / www .m icrosoft .com / m spress/ support / search.asp I f you have com m ent s, quest ions, or ideas r egarding t his book or t he com panion cont ent , or quest ions t hat are not answered by query ing t he Know ledge Base, please send t hem t o Microsoft Pr ess via e- m ail t o: m spinput @m icrosoft .com Or v ia post al m ail t o: Micr osoft Pr ess At t n: Pr ogram m ing Micr osoft SQL Ser ver 2000 w it h Microsoft Visual Basic .NET Edit or One Microsoft Way Redm ond, WA 98052- 6399 Please not e t hat product support is not offer ed t hr ough t he above m ail address. For product support inform at ion, please visit t he Microsoft Support Web sit e at : ht t p: / / support .m icr osoft .com Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Cha pt e r 1 . Ge t t ing St a r t e d w it h Visua l Ba sic .N ET for SQL Se r v e r 2 0 0 0 This book aim s t o giv e professional dev elopers t he background t hat t hey need t o program SQL Ser ver applicat ions w it h Micr osoft Visual Basic .NET. This ov erall goal im plies t hree guidelines: • First , t he book t arget s pract icing dev elopers. I n m y exper ience, t hese ar e busy pr ofessionals who need t he det ails fast . These indiv iduals alr eady know how t o build applicat ions. They buy a book t o lear n how t o build t hose applicat ions wit h a specific set of t ools. • Second, t he book is about building applicat ions for SQL Ser ver 2000. This focus j ust ifies in- dept h coverage of SQL Serv er program m ing t opics— in part icular, T- SQL, Micr osoft ’s ext ension of t he St ruct ured Query Language ( SQL) . • Third, t he book illust rat es how t o pr ogr am in Visual Basic .NET, but w it h part icular em phasis on dat abase issues for SQL Ser ver 2000. Special at t ent ion goes t o relat ed .NET t echnologies, such as t he .NET Fram ew ork, ADO.NET, ASP.NET, and XML Web ser vices. My goal in t his chapt er is t o equip y ou concept ually for t he r est of t he book . Ther efor e, t his chapt er includes m at er ial t hat acquaint s y ou w it h applicat ion dev elopm ent t echniques and t opics for SQL Serv er 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 Chapt er 8, anot her concept ual chapt er , hav e sam ples w it h inst ruct ions aim ed at professional dev elopers. I believe t hat t he overw helm ing m aj or it y of pr ofessional Visual Basic developers hav e no hands- on fam iliarit y wit h Visual Basic .NET and it s relat ed t echnologies. I f you already knew Visual Basic .NET, it wouldn’t m ake any sense t o buy a book describing how t o use it . This chapt er t herefore focuses on how t o get st art ed wit h Visual Basic .NET and one of it s cor e r elat ed t echnologies for t hose building SQL Ser ver applicat ions— ADO. NET. I also believ e t hat m ost Visual Basic dev elopers don’t have an int im at e know ledge of SQL Serv er — especially for creat ing user - defined obj ect s, such as t ables, v iews, and st or ed procedur es. This capabilit y can em pow er you t o build m or e pow erful and m or e secure applicat ions. As y ou lear n about dat abase obj ect s and how t o creat e t hem in Chapt er 2 t hr ough Chapt er 7, reflect back on t he Visual Basic .NET cov erage in t his chapt er and how t o m ar ry dat abase creat ion t echniques and Visual Basic .NET dev elopm ent t echniques. One of t he best t ools t o build dat abase obj ect s is SQL Ser ver 2000 Query Analyzer . This chapt er’s closing sect ion conveys t he basics of Query Analyzer t hat you need t o follow t he sam ples in Chapt er 2 t hrough Chapt er 7. Visua l St u dio .N ET, t h e V isu a l Ba sic .N ET I D E Visual St udio .NET is t he new m ult ilanguage int egrat ed developm ent env ir onm ent ( I DE) for Visual Basic, C# , C+ + , and JScr ipt developers. I f you are dev eloping solut ions for Visual Basic .NET, I definit ely r ecom m end t hat you use Visual St udio .NET as y our dev elopm ent envir onm ent . This sect ion dem onst rat es how t o get st art ed using Visual St udio .NET for dev eloping solut ions wit h Visual Basic .NET. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Visual Basic .NET is available as part of Visual St udio .NET in four edit ions: • Professional • Ent erpr ise Dev eloper • Ent erpr ise Archit ect • Academ ic All four edit ions of Visual St udio .NET include Visual Basic .NET, Micr osoft Visual C# .NET, Microsoft Visual C+ + .NET, and support for ot her languages. I n addit ion, Microsoft offer s Visual Basic .NET St andard, w hich doesn’t include Visual C# .NET or Visual C+ + .NET. Because t his book t arget s professional Visual Basic dev elopers creat ing SQL Ser ver applicat ions, it uses t he Ent erpr ise Developer Edit ion of Visual St udio .NET. You m ay not ice som e differ ences if y ou’r e using anot her edit ion. Visual St udio .NET can be inst alled on com put ers r unning one of five operat ing syst em s: Windows 2000, Windows NT, Window s XP, Windows ME, and Windows 98. Not all t he .NET Fram ework feat ures are av ailable for each operat ing syst em . For exam ple, Windows 98, Windows Me, and Windows NT don’t support dev eloping ASP.NET Web applicat ions or XML Web serv ices applicat ions. The sam ples for t his book ar e t est ed on a com put er running Windows 2000 Ser ver, which does support all .NET Fram ew or k feat ur es. St a r t ing V isu a l St udio .N ET To open Visual St udio .NET, click t he St art but t on on t he Windows t ask bar, choose Program s, and t hen choose Micr osoft Visual St udio .NET. Visual St udio displays it s int egrat ed dev elopm ent env ironm ent , including t he St art Page ( unless you prev iously configur ed Visual St udio t o open different ly ) . Fr om t he St art Page, you can configure Visual St udio t o w ork according t o y our dev elopm ent preferences, and y ou can st art new solut ions as well as open ex ist ing pr oj ect s. Con figu r ing V isu a l St udio .N ET f or Visua l Ba sic .N ET Use t he links on t he left side of t he St art Page t o begin configur ing Visual St udio .NET for developing solut ions in Visual Basic .NET. Click t he My Pr ofile link t o open a pane in w hich you can specify an overall profile as w ell as indiv idually indicat e y our preferences for Keyboard Schem e, Window Lay out , and Help Filt er. 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 conv enient t o choose Show St art Page. As a Visual Basic dev eloper who has work ed w it h Visual Basic 6, y ou m ight feel m ost fam iliar w it h a lay out t hat reflect s y our pr ior developm ent env ir onm ent . Figure 1- 1 shows t hese My Pr ofile select ions. Figu re 1 - 1 . M y Profile se le ct ion s for st a r t in g V isu al St u dio .N ET for a V isu a l Ba sic de velop e r. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Usin g t h e St a r t Pa ge Aft er set t ing your pr ofile, you can r et urn t o t he init ial St art Page pane by choosing t he Get St art ed link from t he m enu on t he left border. I f you had creat ed pr ev ious solut ions, t he last four m odified proj ect s would appear on t he Proj ect s t ab of t he St art Page. The t ab shows pr oj ect nam es along w it h dat e last m odified. I f a pr oj ect y ou want t o v iew doesn’t appear on t he list , you can click t he Open Proj ect link t o display t he Open Proj ect dialog box and t hen navigat e t o a direct ory cont aining t he pr ev iously cr eat ed solut ion. Select t he proj ect ’s folder t hat y ou want t o open in t he I DE, and double- click t he solut ion file ( .sln) for t he proj ect . The next sect ion illust rat es t his pr ocess in t he cont ext of a sam ple proj ect . To cr eat e a new solut ion, click t he New Pr oj ect link t o open t he New Pr oj ect dialog box . I f y ou saved prefer ences such as t hose show n in Figure 1- 1, t he dialog w ill aut om at ically select Visual Basic Proj ect s in t he Proj ect Types pane of t he New Proj ect dialog box. On t he r ight , y ou can select a t em plat e for launching a proj ect . Table 1- 1 shows t he pr oj ect t em plat e nam es along w it h a br ief descript ion av ailable from t he Ent erpr ise Dev eloper Edit ion of Visual St udio .NET. Choosing a t em plat e ( by clicking OK aft er select ing a t em plat e) opens a proj ect ready for creat ing t he t ype of solut ion t hat y ou want t o dev elop. When Visual St udio .NET sav es t he t em plat e t o st art a new proj ect , it specifies eit her a file folder or a Web sit e for t he t em plat e’s files; y ou can overr ide t he default nam es for t he file folder and Web sit e. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
N ot e Not all t he pr oj ect t em plat e t ypes in Table 1- 1 are available wit h t he non- Ent er prise ( or St andard) edit ions of Visual St udio .NET. I n addit ion t o t he em pt y proj ect s, t he St andar d edit ions m ak e available t he Windows Applicat ion, ASP.NET Web Applicat ion, ASP.NET Web Serv ice, and Console Applicat ion t em plat es. 7DEOH 9LVXDO %DVLF 1(7 3URMHFW 7HPSODWH 7\SHV 7HPSODWH 1DPH &UHDWHV $ Windows Windows applicat ion w it h a form Applicat ion Class Library Windows applicat ion suit able for a library of classes wit hout a for m Windows Cont r ol Proj ect for dev eloping cust om reusable form cont r ols for Librar y Windows applicat ions ASP.NET Web Web applicat ion on a Web server Applicat ion ASP.NET Web XML Web serv ice on a Web serv er Ser vice Web Cont r ol Librar y Proj ect for dev eloping cust om reusable cont r ols for Web applicat ions Console Applicat ion Com m and line applicat ion t hat operat es in an MS- DOS–st y le window ( t he Console) Windows Serv ice Windows serv ice, form erly NT serv ice, applicat ion t hat runs in t he background w it hout it s ow n cust om user int erface Em pt y Pr oj ect Local pr oj ect wit h no cust om st yle Em pt y Web Pr oj ect Web proj ect wit h no cust om st yle New Pr oj ect I n Blank proj ect in an ex ist ing folder Ex ist ing Folder Ther e ar e t wo m ain cat egor ies of t em plat es: Web pr oj ect s and local proj ect s. Web proj ect s perm it a browser t o serv e as t he client for a proj ect . Web pr oj ect s are opt im ized for form processing on t he Web ser v er . Local pr oj ect s offer cust om for m user int erfaces wit h t he capabilit y of processing on a local w orkst at ion. Local proj ect s can pr ov ide richer env ir onm ent s m or e conduciv e t o client - side program m ing, but local proj ect s don’t offer t he wide accessibilit y of solut ions running fr om a Web ser ver. Cr e a t in g a n d Run n in g a Con sole Applica t ion When y ou select a Console Applicat ion t em plat e and click OK t o launch a new proj ect , Visual St udio .NET r esponds by opening a pr oj ect w it h a blank m odule. I n addit ion t o t he Module window, Visual St udio displays Solut ion Explorer and t he Propert ies w indow. You can ent er code dir ect ly int o t he Module w indow, which appears as a t ab t hat y ou can select alt er nat ely w it h t he St art Page. Figur e 1- 2 shows a code sam ple in t he Main subrout ine t hat prom pt s for a first and second nam e before com bining t hem and display ing t hem in t he Console ( t he com put er’s m onit or ) . The code is also available as MyNam eI sFrom Console in t he Chapt er 1 folder on t he com panion CD for t his book . Alt hough Visual Basic dev elopers didn’t prev iously have Console applicat ions rout inely available, t his sam ple should be Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
very easy t o follow. The final t w o lines present an inst ruct ion and cause t he window t o r em ain open unt il t he user responds t o t he inst ruct ion. This allows t he user t o v iew t he full nam e in t he Console w indow. Figu r e 1 - 2 . A Con sole a p plicat ion for displayin g a fu ll n a m e b a se d on u se r inpu t for fir st a n d se con d n am e s. To t he r ight of t he Module w indow are t w o ot her windows. The t op one of t hese is Solut ion Explorer. I t shows t he file st r uct ure for t he solut ion. Solut ion Explor er indicat es in it s first line t hat t he solut ion consist s of j ust one proj ect . Below t hat line appears t he nam e of t he proj ect , MyNam eI sFrom Console. Wit hin t he proj ect are t hree ent ries: one each for t he Refer ences, Assem blyI nfo.v b, and Module1.vb elem ent s wit hin t he solut ion’s proj ect . By default , t he Propert ies window is below Solut ion Explorer. I n t he Full Pat h pr opert y t ext box is an excerpt show ing t he pat h t o Module1.vb on m y com put er . When y ou click t he proj ect nam e in Solut ion Explor er, t he Pr oj ect Folder t ext box in t he Propert ies window displays t he pat h of t he dir ect ory holding t he solut ion’s files. I t is t his direct ory t hat you copy t o deploy your solut ion on anot her com put er w it h t he .NET Fram ew or k inst alled. The solut ion w on’t run wit hout t he com m on language runt im e on t he com put er t o which you copy t he direct ory cont aining t he .NET Fram ew or k solut ion. See Chapt er 8 for m or e det ailed cov erage of t he .NET Fram ew or k, 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 r un t he applicat ion by choosing St art from t he Debug m enu, or by pressing F5. This opens t he Console w indow w it h a prom pt t o ent er a first nam e. Aft er y ou close y our applicat ion and sav e any changes t o it , y our solut ion appears Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
on t he St art Page for r ecent solut ions. I f you st art Visual St udio .NET and t he solut ion y ou want t o open doesn’t appear on t he Proj ect s t ab of t he St art Page, you can also open t he solut ion by click ing Open Pr oj ect . I n t he Open Pr oj ect dialog box , choose t he file w it h t he .sln ext ension and t he solut ion’s nam e ( My Nam eI sFrom Console) . A solut ion can cont ain j ust one .sln file, but it can cont ain m ult iple pr oj ect s. You also can r un t he solut ion and open t he Console w indow dir ect ly from Windows Explor er w it hout using Visual St udio .NET. Open t he bin subdirect ory wit hin t he dir ect ory cont aining t he assem bly folder for t he solut ion. Then double- click t he My Nam eI sFr om Console.ex e file. This opens t he Console w indow w it h t he prom pt for a first nam e. An Ove r view of AD O.N ET Ca pa bilit ie s ADO.NET encapsulat es t he dat a access and dat a m anipulat ion for t he .NET Fram ew ork . This sect ion giv es y ou an overv iew of t he t opic t hat equips you for a st art er sam ple in t he next sect ion. Microsoft chose t he nam e ADO.NET for t he .NET Fr am ew ork dat a access com ponent t o indicat e it s associat ion w it h t he ear lier ADO t echnology for dat a access. While t her e ar e som e sim ilarit ies in sy nt ax bet ween ADO.NET and ADO ( part icular ly for connect ion st r ings) , m any will find t he differ ences ar e m or e obv ious t han t he sim ilarit ies. These differences subst ant ially upgrade ADO.NET over ADO in t w o k ey ar eas— scalabilit y and XML ( Ext ensible Mark up Language) int er operabilit y. As a result , y ou w ill be able t o creat e dat abase applicat ions w it h ADO.NET t hat serv e m or e users and shar e m ore dat a t han y ou did w it h ADO. See Chapt er 10 for a m or e int ensive exam inat ion of ADO.NET. Chapt er 12 explicit ly explores int eroperabilit y bet ween ADO. NET and XML. .N ET D a t a Pr ovide r Type s Your .NET Fram ework solut ions requir e .NET dat a providers t o connect t o dat a sources. These pr ov iders are differ ent fr om t hose used wit h ADO, but t her e are dist inct sim ilar it ies in som e of t he ways y ou use t hem . Wit h .NET dat a prov iders, your solut ions can connect , r ead, and ex ecut e com m ands against dat a sources. The .NET prov iders also offer select ed ot her funct ions, such as t he m anagem ent of input and out put par am et ers, securit y, t ransact ions, and dat abase ser ver errors. Visual St udio .NET ships wit h t wo .NET dat a providers— t he SQL Serv er .NET dat a prov ider and t he OLE DB .NET dat a pr ov ider. I n addit ion, y ou can dow nload an ODBC .NET dat a prov ider fr om t he Micr osoft MSDN dow nload sit e ( ht t p: / / m sdn.m icr osoft .com / downloads/ default .asp) . N ot e As I writ e t his chapt er , t he ODBC .NET dat a pr ov ider j ust becam e av ailable wit h t he r ollout of t he shipping version of Visual St udio .NET. You can download it fr om ht t p: / / m sdn.m icrosoft .com / downloads/ default .asp?url= / downloads/ s am ple.asp?url= / m sdn- files/ 027/ 001/ 668/ m sdncom posit edoc.x m l. The URLs for resour ces som et im es change. You can alway s sear ch for t he ODBC .NET dat a pr ovider at t he MSDN download sit e t o obt ain it s curr ent download locat ion. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The t hr ee pr ov iders t ak en t oget her offer fast , highly focused access t o select ed dat a sources as well as general access t o a w ide range of possible dat a sources. The SQL Ser ver .NET dat a provider is opt im ized for SQL Ser ver 7.0 and SQL Ser ver 2000. This dat a prov ider connect s dir ect ly t o a SQL Serv er inst ance. The OLE DB .NET dat a prov ider connect s t o OLE DB dat a sources t hr ough t w o int erm ediat e lay ers— t he OLE DB Serv ice Com ponent and t he classic OLE DB prov ider int roduced along w it h ADO. The OLE DB Serv ice Com ponent m anages connect ion pooling and t ransact ion serv ices. The classic OLE DB prov ider , in t urn, dir ect ly connect s t o a dat abase serv er . Micr osoft explicit ly t est ed t he OLE DB .NET dat a provider w it h SQL Ser ver, Or acle, and Jet 4.0 dat abases. 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 pr ov ider is also good for connect ing t o y our Micr osoft Access solut ions based on t he Jet 4.0 engine. The OLE DB .NET dat a prov ider definit ely doesn’t wor k w it h t he OLE DB prov ider for ODBC dat a sources ( MSDASQL) . Because t he .NET OLE DB dat a pr ovider doesn’t connect t o ODBC dat a sources, y ou requir e t he ODBC .NET dat a prov ider for connect ing t o ODBC dat a sources fr om y our .NET Fr am ew ork solut ions. Ther e ar e four m ain .NET dat a pr ov ider classes for int eract ing w it h a rem ot e dat a source. The nam es of t hese classes change slight ly for each t ype of pr ov ider, but each .NET dat a prov ider has t he sam e four kinds of classes. The nam es for t he SQL Ser ver .NET dat a prov ider classes for int eract ing wit h SQL Server inst ances are SqlConnect ion, SqlCom m and, SqlDat aReader, and SqlDat aAdapt er. You can use t he SqlDat aReader class for read- only applicat ions fr om a SQL Serv er dat a source. Two especially convenient ways t o display r esult s w it h a SqlDat aReader class are in a m essage box or t he Visual St udio .NET Out put w indow. The SqlDat aAdapt er class act s as a bridge bet w een a r em ot e SQL Ser ver dat a source and a Dat aSet class inst ance inside a Visual Basic .NET solut ion. A dat a set in a Visual St udio solut ion is a fift h t y pe of ADO.NET class. A dat a set can cont ain m ult iple t ables. A sixt h ADO.NET class is t he Dat aView class, w hich act s lik e a v iew based on a t able w it hin a Dat aSet obj ect . Windows For m s in Visual Basic .NET applicat ions can bind only t o t ables w it hin a Dat aSet obj ect and Dat aView obj ect s. I exam ine t he Dat aSet obj ect lat er in t his sect ion. Chapt er 10 includes a syst em at ic sum m ary of all six ADO.NET classes t hat r ev iews select ed propert ies and m et hods of each class. The ov er view of ADO.NET classes in Chapt er 10 is support ed by num erous code sam ples t hat illust rat e how t o m anipulat e inst ances of t he classes program m at ically . N ot e I n or der t o use abbreviat ed nam es, such as t hose list ed in t his sect ion for t he SQL Serv er .NET dat a pr ovider class inst ances, y our applicat ion needs a refer ence t o t he SqlClient nam espace. You can cr eat e such a reference wit h an I m port s Sy st em .Dat a.SqlClient st at em ent j ust befor e a Module declarat ion. SqlCon n e ct ion Cla ss An inst ance of t he SqlConnect ion class can int erface direct ly w it h a SQL Serv er dat a source. Use a const ruct or st at em ent t o inst ant iat e a SqlConnect ion obj ect from t he SqlConnect ion class. The const ruct or st at em ent is a new t ype of sy - nt ax for .NET Fram ew or k solut ions. This t ype of st at em ent perm it s y ou t o declare, inst ant iat e, and pass st art up param et ers t o an obj ect based on a class. Wit h t he SqlConnect ion const ruct or st at em ent , y ou can specify a connect ion st r ing as an argum ent for t he const r uct or st at em ent . Alt er nat iv ely, you can assign t he Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
connect ion st r ing t o t he SqlConnect ion obj ect aft er it s inst ant iat ion w it h a propert y assignm ent st at em ent for t he Connect ionSt ring propert y. The follow ing line shows t he synt ax t o inst ant iat e a new SqlConnect ion obj ect , My SQLCnn1, wit h a connect ion st r ing designat ing int egrat ed secur it y t o t he m ydb dat abase on t he m yserv er inst ance of SQL Serv er. You don’t hav e t o explicit ly indicat e a prov ider because t he const ruct or st at em ent r ev eals t he t ype of prov ider t hr ough it s r efer ence t o t he SqlConnect ion class. 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 it s Open m et hod befor e t he obj ect can link anot her obj ect based on one of t he ot her SQL Ser ver .NET dat a provider classes, such as SqlCom m and, SqlDat aAdapt er, or SqlDat aReader , t o a SQL Serv er inst ance. I nv oke t he Close m et hod t o recover t he resources for a SqlConnect ion obj ect w hen your solut ion no longer needs it . The Close m et hod rolls back any pending t ransact ions and releases t he connect ion t o t he connect ion pool. The Dispose m et hod is also available for r em ov ing connect ions, but it inv okes t he Close m et hod and perform s ot her .NET adm inist rat iv e funct ions. Microsoft r ecom m ends t he Close m et hod for r em oving a connect ion. Unclosed connect ions ar en’t r et urned t o t he connect ion pool. SqlCom m a n d a n d SqlD a t a Re a de r Cla sse s One w ay t o put a connect ion t o use is t o em ploy it along w it h t he SqlCom m and and SqlDat aReader obj ect s. A SqlDat aReader obj ect can m aint ain an open for ward- only, read- only connect ion w it h a SQL Ser ver dat abase. While t he SqlDat aReader using a SqlConnect ion obj ect is open, you cannot use t he SqlConnect ion obj ect for any ot her 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 st at em ent and assign a result set from a SqlCom m and obj ect t o a SqlDat aReader wit h t he Ex ecut eReader m et hod of t he SqlCom m and obj ect . Finally, invoke t he SqlDat aReader obj ect Read m et hod t o open a r ow fr om t he result set in t he SqlDat aReader . The SqlCom m and obj ect can ser ve m ult iple funct ions, including processing a T- SQL st at em ent against a connect ion. When used in t his fashion, t he SqlCom m and can t ake t wo argum ent s. The first can be a T- SQL dat a access st at em ent , such as SELECT * FROM MyTable . The second SqlCom m and argum ent designat es t he source connect ion for t he T- SQL st at em ent . For exam ple, y ou can use t he nam e of a SqlConnect ion obj ect , such as My SQLCnn1. Figur e 1- 3 shows t he rout e fr om a SQL Serv er dat a source t o a SqlDat aReader obj ect . Alt hough t he SqlConnect ion and SqlCom m and obj ect s support t wo- way int eract ion w it h a dat a source, t he SqlDat aReader obj ect allows read- only access t o t he result set from t he T- SQL st at em ent ser v ing as an argum ent for a SqlCom m and const r uct or. Because a SqlDat aReader obj ect cannot specify it s ow n dat a source, a SqlDat aReader obj ect m ust link t o a SqlConnect ion obj ect t hr ough an int erm ediat e SqlCom m and obj ect . Figu re 1 - 3 . A sch e m a t ic illu st ra t ing t h e r ou t e b y w h ich a SqlDa t a Rea de r ob j e ct re t u r n s va lu e s t o a n a pplicat ion . Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.