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

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

Thêm vào BST

Báo xấu

99
lượt xem 9
download

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

Tham khảo tài liệu 'programming microsoft sql server 2000 with microsoft visual basic .net - p7', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

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 - P7

labels v isible by passing t he argum ent True t o ShowLabelsBox es. As a result , a user can ent er a SQL Serv er login and password so t hat t he form can at t em pt t o m ake a connect ion based on a SQL Serv er inst ead of a Windows login. Finally, by click ing t he Windows NT radio but t on, t he user inv ok es t he RadioBut t on1_CheckedChanged ev ent pr ocedure. This procedure m ak es t he cont r ols for SQL Server login credent ials invisible if t hey ar e show ing. When t he user clicks t his RadioBut t on1, it indicat es he or she want s t o m ak e a connect ion wit h a Windows login. Ther efore, Form 3 doesn’t need t o show t he cont r ols for a SQL Ser ver login. Private Sub Form3_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ’Set RadioButton1 to Checked for connection via ’Windows NT login. RadioButton1.Checked = True ’Hide login and password controls because they ’aren’t necessary with Windows NT login. ShowLabelsBoxes(False) End Sub Private Sub RadioButton1_CheckedChanged _ (ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles RadioButton1.CheckedChanged ’Hide login and password controls because they ’aren’t necessary with Windows NT login. ShowLabelsBoxes(False) End Sub Private Sub RadioButton2_CheckedChanged _ (ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles RadioButton2.CheckedChanged ’Show login and password controls because they ’are necessary for a SQL Server login. ShowLabelsBoxes(True) End Sub Sub ShowLabelsBoxes(ByVal bolShowEm As Boolean) ’Set the visibility of the second and third text ’boxes and their labels according to the value ’of bolShowEm. Label2.Visible = bolShowEm TextBox2.Visible = bolShowEm Label3.Visible = bolShowEm TextBox3.Visible = bolShowEm End Sub The follow ing excerpt fr om t he Form 3 m odule shows t he code devot ed t o m ak ing t he connect ion based on t he radio but t on select ion and t ext box ent r ies. The excerpt st art s wit h a m odule- lev el declarat ion of t he cnn1 obj ect refer ence as a SqlConnect ion obj ect . A m odule- level declarat ion isn’t st rict ly necessary in t he Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
cont ext of t his sam ple, but t his t ype of declarat ion m ak es t he SqlConnect ion obj ect available t o ot her procedures t hat could use it . I n any ev ent , not ice t hat t he declarat ion specifies t he full nam e for t he nam espace cont aining t he SqlConnect ion obj ect r eference. This is because t he m odule doesn’t include an I m port s st at em ent for t he Syst em .Dat a.SqlClient nam espace. By not using t he I m port s st at em ent at t he t op of t he Form 3 m odule, t he Cat ch clause in t he excerpt m ust reference a Syst em except ion inst ead of t he m ore specific SqlClient except ion. I n spit e of t his dev iat ion from t he sam ple in t he “Cat ching SqlConnect ion Except ions” sect ion, SqlClient ex cept ions st ill percolat e up t hr ough t he m ore general Syst em except ion specificat ion. Aside from t he declarat ion issues for cnn1, t he balance of t he code excerpt is a st raight forward m ixt ur e of t he code sam ples developed prev iously in t his chapt er. Based on w het her RadioBut t on1 is checked, t he But t on1_Click ev ent procedure com poses a connect ion st ring for eit her a Windows or a SQL Serv er login. Then t he procedure inst ant iat es a connect ion based on t he connect ion st r ing. Wit hin a Try…Cat ch…Finally st at em ent , t he procedure at t em pt s t o open t he connect ion. I f t he at t em pt succeeds, t he pr ocedur e displays a m essage confirm ing t he at t em pt was successful and nam ing t he dat abase. Ot her wise, cont rol flows t o t he Cat ch clause, and t he procedure displays t he error m essage associat ed wit h t he except ion. Because SqlClient except ions percolat e up t hrough t he Syst em except ion, t he m essage is lik ely t o be specific and helpful for diagnosing any problem s. ‘Using the full namespace name removes the need to ‘start module with Imports System.Data.SqlClient. Dim cnn1 As System.Data.SqlClient.SqlConnection Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click ’Make local variable declarations. Dim strDBName As String = TextBox1.Text Dim strConnect As String ’Compose a connection string for either a Windows ’or a SQL Server login. If RadioButton1.Checked = True Then strConnect = “Data Source=(local);” & _ “Initial Catalog=“ & strDBName & _ “;Integrated Security=SSPI" Else Dim strLogin As String = TextBox2.Text Dim strPassword As String = TextBox3.Text strConnect = “Data Source=(local);” & _ “Initial Catalog=“ & strDBName & “;” & _ “user id=“ & strLogin & _ “; password=“ & strPassword End If ’Instantiate a SqlConnection object based on the ’connection string. cnn1 = _ New System.Data.SqlClient.SqlConnection(strConnect) ’Embed the attempt to open the cnn1 object inside a ’Try...Catch...Finally statement, and display a ’message for the exception if there is one. Try cnn1.Open() MsgBox(“Successfully connected to “ & cnn1.Database & _ “ database on local server.”) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Catch er As System.Exception MsgBox(er.Message) End Try End Sub W or k in g w it h Com m a nd a n d Da t a Rea der Obj ect s One of t he m ost com m on uses for Com m and obj ect s is t o cont ain t he SQL st r ing t hat defines t he v alues cont ained in a Dat aReader obj ect . Therefore, t his sect ion drills down on t hat use for Com m and obj ect s. I n t his sect ion, you learn how t o for m at t he display of values in a Dat aReader obj ect as well as how t o populat e a Dat aReader w it h eit her a SQL st ring or a st ored procedure. Bey ond t hese t ypical applicat ions for Com m and obj ect s wit h Dat aReader obj ect s, t he sect ion also includes a sam ple t hat dem onst rat es how t o use t he Com m and obj ect for dat a definit ion t asks, such as creat ing a user- defined funct ion. The present at ion of t he t opic cov ers a special m et hod for Com m and obj ect s t hat is appropriat e when t he Com m andText propert y for a Com m and obj ect doesn’t r et ur n any values. D ispla ying Re su lt s in a M e ssa ge Box or t h e Ou t put W indow I t ’s easy t o put SqlCom m and and SqlDat aReader obj ect s t o use for report ing result s from a SQL Ser v er dat abase. St art by connect ing t o t he r em ot e dat a source from which y ou want t o display result s. Nex t declar e a Com m and obj ect as a SqlCom m and t ype. The Com m and obj ect r equir es t wo input s: a dat abase connect ion and a source of SQL st at em ent s t o ex ecut e. You can link a Com m and obj ect t o a Connect ion obj ect when y ou inst ant iat e t he Com m and obj ect . Specify a dat a source for t he Com m and obj ect t o r et urn wit h eit her a SQL st ring or a st ored procedure. This capabilit y of com m ands t o t ak e SQL st at em ent s and st ored procedures allows you t o draw on all dat a access t opics cover ed in Chapt ers 3 t hr ough 5. Dat aReader obj ect s read t he result set r et urned by Com m and obj ect s. Use t he Ex ecut eReader m et hod on a Com m and obj ect t o conv ey it s r esult set t o a Dat aReader obj ect . Aft er t he inv ocat ion of t he Execut eReader m et hod, you can ext ract sequent ial rows from a result set w it h t he Read m et hod for t he Dat a- Reader obj ect . Use one of t he Dat aReader Get m et hods t o ext ract t he value for a specific colum n int o a dat a t ype designat ed by t he Get m et hod. Colum ns ar e designat ed w it h index num bers of 0 t hr ough 1 less t han t he num ber of colum ns in a result set . The Enum erat eCat egories procedure, w hich appears next , dem onst rat es t he applicat ion of t hese guidelines for using Com m and and Dat aReader obj ect s. You can inv ok e t his pr ocedure fr om Module1 in t he MyADODOTNETSam ples solut ion by adapt ing t he inst r uct ions for running ot her procedur es from Module1. The procedur e enum erat es Cat egoryI D and Cat egor yNam e v alues from t he Cat egories t able in t he Nort hw ind dat abase. A com piler const ant , bolOut put Window , perm it s you t o dir ect t he cont ent s of a Dat aReader obj ect t o eit her a m essage box or t he Out put window in t he Visual St udio .NET design env ir onm ent . The default value for bolOut put Window direct s t he Dat aReader cont ent s t o a m essage box. Aft er assigning a v alue t o t he com piler const ant , t he Enum erat eCat egories list ing begins by declaring and inst ant iat ing cnn1 as a Connect ion obj ect befor e invok ing t he obj ect ’s Open m et hod. Next t he procedur e declares cm d1 as a Com m and obj ect and specifies cnn1 as it s Connect ion propert y w it h t he Creat eCom m and m et hod for cnn1. The list ing proceeds t o assign a SQL st ring t o t he Com m andText propert y for cm d1. Wit h an Ex ecut eReader m et hod in a declarat ion for t he drd1 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Dat aReader, t he pr ocedur e generat es a r esult set for drd1 based on t he SQL st ring used t o define cm d1. N ot e Throughout t his chapt er , and elsewhere in t he book , I use generic t er m s int erchangeably when r eferencing specific classes in t he Syst em .Dat a.SqlClient nam espace. For exam ple, I use t he t erm Dat aReader t o reference t he m ore specific class nam e SqlDat aReader . Using t he gener ic t erm rem inds y ou t hat SqlClient classes hav e par allel classes in ot her .NET dat a provider s, nam ely t he OLE DB .NET dat a provider and t he ODBC .NET dat a pr ovider . Aft er t he conclusion of t he Execut eReader m et hod, t he Dat aReader obj ect is ready t o expose it s cont ent s t o a Visual Basic .NET applicat ion. The balance of t he procedur e int r oduces you t o t wo differ ent st rat egies for achiev ing t his. A com piler I f…Then…Else st at em ent based on a com piler const ant adds one of t wo st at em ent s t o t he com piled v ersion of t he pr ocedur e. Eit her st at em ent r et urns a row from t he Dat aReader obj ect , but t hey display t he row in different ways. Alt hough t he list ing shows bot h t he Then and Else clauses, t he com piled procedur e cont ains only one or t he ot her clause based on t he com piler const ant value for bolOut put Window. Before encount er ing t he com piler I f…Then…Else st at em ent , t he procedur e declares a st r ing const ant t hat can serv e as a t it le for t he enum erat ed values in a m essage box. The const ant ends w it h a St r Dup funct ion t hat can duplicat e a st r ing const ant any num ber of t im es. I n t his case, t he funct ion appends t w o carr iage ret urns t o t he end of t he t ext for t he t it le. The int r insic const ant , vbCr, denot es t he st ring equivalent of a car r iage r et urn. Next t he pr ocedur e st ar t s a Do…While st at em ent wit h t he condit ion drd1.Read() . This condit ion w ill ret urn t he value Tr ue as long as t here ar e r em aining rows in t he Dat aReader. Aft er t he Read m et hod passes t hr ough all t he rows from t he drd1 obj ect , t he condit ion r et urns t he value False, w hich causes cont r ol t o pass t o t he first st at em ent aft er t he Loop st at em ent for t he Do…While st at em ent . The com piler I f…Then…Else st at em ent com piles one of t wo possible st at em ent s depending on t he value of bolOut put Window. When bolOut put Window equals it s default v alue ( False) , t he st at em ent appends Cat egor yI D and Cat egoryNam e values for t he cur r ent r ow t o a st r ing value. The values for each row end wit h a carriage r et urn ( vbCr) . I f bolOut put Window equals True, Visual Basic .NET com piles a differ ent st at em ent t hat sim ply echoes t he r ow values t o t he Out put window w it h t he Wr it eLine m et hod for a Console obj ect . Not ice t hat t he t wo com piled st at em ent s use slight ly different t echniques for capt ur ing t he first colum n value for Cat egoryI D. Bot h st at em ent s use a Get I nt 32 m et hod because t he SQL Serv er dat a t ype of int for Cat egor yI D is consist ent w it h t he .NET value t ype of I nt 32, a 32- bit signed int eger. However, t he pat h for adding t he v alues t o a st ring for display in a m essage box invok es t he ToSt ring m et hod t o conv ert explicit ly t he I nt 32 num ber t o a st ring. This k ind of conv ersion is prefer red because it sav es t he t im e for a run- t im e det erm inat ion of how t o finally represent a r et urned value. Sub EnumerateCategories() ’Compiler constant directing output to Output window ’or a message box. Default value is False. #Const bolOutputWindow = False ’Declare and open connection to Northwind. Dim cnn1 As SqlConnection = New _ SqlConnection(“Data Source=(local);” & _ “Integrated Security=SSPI;Initial Catalog=northwind”) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
cnn1.Open() ’Declare a command and assign a SQL string to it. Dim cmd1 As SqlCommand = cnn1.CreateCommand() cmd1.CommandText = _ “SELECT CategoryID, CategoryName FROM Categories" ’Declare a data reader and copy result set from SQL string ’for cmd1 to drd1. Dim drd1 As SqlDataReader = cmd1.ExecuteReader() ’Loop through data reader and display in Output ’window or message box. Dim str1 As String = _ “Summary of CategoryID and Category Names” _ & StrDup(2, vbCr) Do While drd1.Read() #If bolOutputWindow = True Then Console.WriteLine(“Category “ & drd1.GetInt32(0) & _ “ is “ & drd1.GetString(1)) #Else str1 = str1 & “Category “ & _ drd1.GetInt32(0).ToString & _ “ is “ & drd1.GetString(1) & vbCr #End If Loop ’Conditionally display results in a message box. #If bolOutputWindow = False Then MsgBox(str1) #End If ’Close data reader and connection object references. drd1.Close() cnn1.Close() End Sub Aft er cont rol passes from t he Do…While st at em ent , cont r ol can flow opt ionally t o a MsgBox funct ion st at em ent for display ing t he st ring com put ed in t he loop. A com piler I f…Then st at em ent insert s t he MsgBox funct ion int o t he com piled procedur e if bolOut put Window equals False. Figur e 10- 5 shows t he out com e from t he procedure w hen t he value of bolOut put Window is False, and Figur e 10- 6 is an excerpt from t he Out put window generat ed when bolOut put Window is True. No m at t er which pat h t he procedur e t akes t o generat e r esult s, it ends by closing t he drd1 and cnn1 obj ect r eferences. You should always per form t hese t asks when you no longer need a Dat aReader obj ect so t hat SQL Serv er can m ak e t he connect ion available for ot her requir em ent s. Figu r e 1 0 - 5 . Re t u r n for t h e En u m e ra t eCat e gories pr oce du re w h e n b olOu t p u t W ind ow equ a ls Fa lse . Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Figu re 1 0 - 6 . An ex ce r p t from t h e re t u r n for t h e En u m e ra t e Cat e gories pr oced u r e w h e n bolOu t pu t W in dow e qu als Tru e. D ispla ying Row s in Block s fr om a D a t a Re a de r The preceding sam ple dem onst rat es how conv enient a m essage box can be for display ing t he cont ent s of a Dat aReader obj ect . However, a single m essage box can be filled t o it s charact er lim it before it com plet es display ing r esult s from a Dat aReader obj ect . A w ork ar ound t o t his sit uat ion is t o display y our result s fr om t he Dat aReader obj ect s in blocks of x rows at a t im e. When y our applicat ion displays rows in blocks, users can sequent ially page t hr ough a result set t o find an it em , or it em s, of int erest . Because t he Dat aReader prov ides forward- only dat a access, you cannot page back , but you can prov ide your users a forward- only look at som e dat a. The Enum erat eCust om erI DNam es procedure allows a user t o specify t he num ber of rows t o show in a m essage box. The procedure r et ur ns t he Cust om er I D and Com panyNam e colum n values fr om t he Cust om ers t able in t he Nort hw ind dat abase. You can invoke t he Enum erat eCust om erI DNam es procedur e fr om t he m ain procedur e in Module1. Launching t his procedur e is slight ly differ ent t han wit h pr eceding sam ples from Module1. I n t his case, y ou m ust pass along an argum ent value as you inv ok e t he pr ocedure. The argum ent is for t he m axim um num ber of rows t o show in a t ext box . The result set fr om t he Com m and obj ect for a Dat aReader obj ect m ay ext end ov er sev er al blocks and requir e m ult iple m essage box es. Each m essage box , except t he final one, m ust hold t he m ax im um num ber of rows per block passed as an argum ent t o t he Enum er at eCust om erI DNam es pr ocedur e. The final m essage box w ill display fr om one row up t o t he m axim um num ber of rows. The Enum erat eCust om erI DNam es procedure st art s in t he sam e general fashion as t he preceding one in t hat it m ak es a connect ion t o t he Nort hw ind dat abase and t hen populat es a Dat aReader, drd1, w it h t he result s of a Com m and obj ect , cm d1. The sole dist inct ion in how t he t wo pr ocedur es st art is t hat t his one has a different SQL st r ing for t he Com m and obj ect t hat ret urns m or e rows t han t he one in t he ear lier sam ple. This larger num ber of r ows in t he Dat aReader for t his sam ple calls for special t reat m ent because a single m essage box cannot display all it s r ows. The balance of t he pr ocedur e dem onst rat es one solut ion for t he problem of t oo m any r ows t o display in a m essage box. Tw o code blocks facilit at e t he solut ion. The first block it erat es t hr ough t he rows in drd1 in blocks of int Size. The procedur e obt ains a value for int Size as a passed argum ent from t he procedur e Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
t hat calls t he Enum erat eCust om erI DNam es pr ocedur e. A user can specify a block size t hat does fit w it hin a single m essage box no m at t er how m any r ows are in t he Dat aReader. By click ing OK on each m essage box , t he user can v iew successive blocks of r ows from t he Dat aReader . The second code block capt ures any r em aining rows at t he end of a Dat aReader obj ect t hat don’t fill a com plet e block . The first code block uses int 1 as a variable t o count t he cum ulat iv e num ber of rows read fr om t he drd1 Dat aReader. A st ring var iable, st r1, accum ulat es rows in successive blocks of size int Size. The first code block uses a Do…While st at em ent wit h a condit ion of drd1.Read() t o pass successively t hrough all t he r ows in t he drd1 Dat aReader . As t he code block reads each new row, it recom put es st r1 so t hat t he new row appears at t he bot t om of t he st ring variable. When t he rem ainder of int 1 div ided by int Size equals 0, t he pr ocedur e accum ulat es a new block of r ows ( of size int Size) t o display in a m essage box. The expression int1 mod intSize ret urns t he rem ainder of int 1 div ided by int Size. When t he first code block det ect s t he end of a block of rows, t he st r ing variable st oring row values is passed t o a MsgBox funct ion as t he m essage argum ent . Aft er print ing t he m essage, t he pr ocedur e reset s t he st ring v ariable st r1 t o st art a new block of rows. Then t he w hole process st art s over again. When no m ore r ows r em ain in t he Dat aReader , t he procedure passes cont r ol t o t he second code block. This second block st art s by t est ing t o see w het her any rows rem ain t hat didn’t appear since t he display of t he last m essage box. Any rem ainder of int 1 div ided by int Size signals undisplayed r ows. I f t her e are any of t hese r ows, t he second code block passes t he v alue of st r1 t o a MsgBox funct ion as t he m essage argum ent t o show t hem . The pr ocedur e concludes in t he st andard way by closing t he Dat aReader obj ect and it s Connect ion obj ect . Sub EnumerateCustomerIDNames(ByVal intSize As Integer) ’Declare and open connection to Northwind. Dim cnn1 As SqlConnection = _ New SqlConnection(“Data Source=(local);” & _ “Integrated Security=SSPI;Initial Catalog=northwind”) cnn1.Open() ’Declare command and assign a SQL string to it, and then ’declare a data reader and copy result set from cmd1 to drd1. Dim cmd1 As SqlCommand = cnn1.CreateCommand() cmd1.CommandText = _ “SELECT CustomerID, CompanyName FROM Customers" Dim drd1 As SqlDataReader = cmd1.ExecuteReader() ’Loop through data reader in blocks of intSize and sequentially ’display the contents of successive blocks. Dim int1 As New Integer() Dim str1 As String = _ “CustomerID and matching CompanyName column values” _ & StrDup(2, vbCr) Do While drd1.Read() str1 = str1 & drd1.GetString(0) & vbTab & _ drd1.GetString(1) & vbCrLf int1 += 1 If (int1 Mod intSize) = 0 Then str1 = str1 & StrDup(2, vbCr) & _ “Click OK for next “ & _ intSize.ToString & “ customers." MsgBox(str1, , “CustomerID and Customer Name”) str1 = _ “CustomerID and matching CompanyName “ & _ “column values” & StrDup(2, vbCr) End If Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Loop ’If a partial block remains at end of data reader contents, ’display partial block. If (int1 Mod intSize) > 0 Then str1 = str1 & StrDup(2, vbCr) _ & “Click OK to close message box." MsgBox(str1, , “CustomerID and Customer Name”) End If ’Close data reader and connection object references. drd1.Close() cnn1.Close() End Sub Figur e 10- 7 shows t he first and last m essage boxes t hat r esult from r unning t he Enum er at eCust om erI DNam es pr ocedur e w it h an int Size argum ent value of 25. The first m essage box cont ains 25 r ows, as do all t he int er vening m essage boxes up unt il t he last one. The last m essage box shows t he r ows r em aining at t he end t hat don’t fill an ent ir e block of 25 rows. Figu re 1 0 - 7 . Th e first a n d last m e ssa ge box es displa yed b y t h e En u m er a t e Cu st om e r I D N a m e s p roce du r e . I n vok in g a St or e d Pr oce du r e w it h a Pa r a m e t e r by a SQL St r in g I n addit ion t o using SQL st rings t o designat e t he dat a for t he Com m and obj ect s t hat populat e Dat aReader obj ect s, y ou can also specify a st ored procedur e as t he source for a Com m and obj ect . Ther e are t wo m ain advant ages t o using st ored procedur es. First , st ored procedures are com piled. This saves t he ser ver t he t im e Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
of com piling a SQL st r ing before it can st art t o r et urn dat a for y our Dat aReader obj ect . Second, st or ed procedur es accept param et ers. This allows t he users of your applicat ions t o change t he result set r et ur ned at r un t im e. Ther e ar e t wo approaches t o set t ing param et er values for st or ed procedur es. Many developers pr efer t o specify a SQL st ring t hat invokes t he st or ed procedure and passes t he value. Chapt er 4 illust rat es t he synt ax for accom plishing t his, and we dem onst rat e t he use of t he t echnique in a .NET Fram ework applicat ion w it h t he sam ple for t his sect ion. A second approach is t o add param et ers w it h t he .NET Fr am ew ork sy nt ax . This approach allows y ou t o explicit ly specify t he dat a t ype as y ou pass a param et er. I w ill dem onst rat e t his second approach in t he next sect ion. The sam ple for t his sect ion and t he next one depends on t he Cust Order Hist st ored procedure in t he Nort hw ind dat abase. This pr ocedur e r et urns t he quant it y of each product order ed by a cust om er. The procedur e t ak es a five- char act er st ring param et er t o designat e t he Cust om erI D v alue. The result set cont ains a single r ow for each product ev er order ed by a cust om er. Each r ow cont ains t he product nam e and quant it y order ed by t he cust om er specified in t he param et er when y ou inv ok e t he st or ed procedur e. For y our conv enience in underst anding t he logic of t he Cust OrderHist st ored pr ocedur e, her e’s t he T- SQL code for t he st ored procedure: CREATE PROCEDURE CustOrderHist @CustomerID nchar(5) AS SELECT ProductName, Total=SUM(Quantity) FROM Products P, [Order Details] OD, Orders O, Customers C WHERE C.CustomerID = @CustomerID AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID GROUP BY ProductName Two sub pr ocedur es m ake up t he solut ion for displaying t he result s from r unning t he Cust OrderHist st ored procedure w it h a SQL st ring. The first sub pr ocedur e, RunCust OrderHist Wit hSt ring, inv ok es t he SQL st ring for t he st or ed pr ocedur e and creat es a Dat aReader obj ect based on it s result set . RunCust OrderHist Wit hSt r ing t akes t wo argum ent s— one for t he Cust om erI D v alue and a second for specify ing t he m ax im um num ber of r ows t o display as a block in a m essage box. This init ial Visual Basic .NET procedur e: • Creat es a Connect ion obj ect . • I nst ant iat es a Com m and obj ect t hat ex ecut es t he Cust OrderHist st ored procedur e while passing a Cust om erI D value as a param et er. • Populat es a Dat aReader based on t he r esult set fr om Cust OrderHist . Because t he sam ple uses a SQL st r ing t o invoke t he st or ed procedur e and pass a param et er, t he pr ocess of running a st ored procedur e w it h a param et er is sim ilar t o j ust specifying a SQL st ring as t he source for t he Com m and obj ect . This sim ilar it y is t he chief advant age of using t he SQL st ring t o inv ok e t he st or ed procedur e. One disadvant age of t he approach is t hat t he ser ver has t o com pile t he T- SQL st at em ent in t he st ring t o invoke t he st ored procedure. Anot her disadvant age is t hat you don’t get t he benefit of explicit dat a t yping for t he param et er value at t he client end of t he solut ion. This explicit t yping can allow you t o cat ch inappropriat e param et er values ear lier in t he solut ion and save serv er t im e dev ot ed t o det ect ing er r oneous par am et er values as well as passing back feedback on t he er ror t o t he client . The solut ion’s second sub pr ocedur e, drdToMessageBox, displays t he r ows in t he Dat aReader creat ed by RunCust OrderHist Wit hSt ring. The drdToMessageBox procedur e requires four argum ent s. The first t w o ar e passed by refer ence inst ead of in t he nor m al Visual Basic .NET way of by value. These argum ent s are for t he Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Dat aReader obj ect and it s associat ed Connect ion obj ect . The second t w o argum ent s ar e passed by value. These are t he Cust om erI D param et er value and t he value for t he m ax im um num ber of rows t o display in a m essage box . The design of t his second sub procedure is a direct ext ension of pr ior sam ples wit h specific adj ust m ent s, such as for t he t it le w it hin a m essage box. A specific benefit of div iding t he solut ion across t wo sub procedures is t hat w e w ill be able t o r euse t his second sub pr ocedure in t he nex t sect ion’s sam ple. Sub RunCustOrderHistWithString(ByVal CustomerID As String, _ ByVal intSize As Integer) ’Declare and open connection to Northwind. Dim cnn1 As SqlConnection = _ New SqlConnection(“Data Source=(local);” & _ “Integrated Security=SSPI;Initial Catalog=northwind”) cnn1.Open() ’Declare command with T-SQL for a stored proc with a parameter. Dim cmd1 As SqlCommand = _ New SqlCommand(“EXEC CustOrderHist “ & CustomerID, cnn1) ’Declare data reader and populate with result set ’from stored procedure. Dim drd1 As SqlDataReader = cmd1.ExecuteReader() ’Display result set. drdToMessageBox(drd1, cnn1, CustomerID, intSize) End Sub Sub drdToMessageBox(ByRef drd1 As SqlClient.SqlDataReader, _ ByRef cnn1 As SqlClient.SqlConnection, _ ByVal CustomerID As String, _ ByVal intSize As Integer) ’Declare header for report in message box and counter for rows ’showing within a message box. Dim str1 As String = _ “Quantities for Products Ordered by “ & _ CustomerID & StrDup(2, vbCr) Dim int1 As Integer ’Loop through data reader in blocks of intSize and ’sequentially display the contents of successive blocks. Do While drd1.Read() str1 = str1 & drd1.GetInt32(1) & vbTab _ & drd1.GetString(0).ToString & vbCrLf int1 += 1 If (int1 Mod intSize) = 0 Then str1 = str1 & StrDup(2, vbCr) _ & “Click OK for next “ & _ intSize.ToString & “ customers." MsgBox(str1, , “From CustOrderHist Stored Proc”) str1 = _ “Quantities for Products Ordered by “ & _ CustomerID & StrDup(2, vbCr) End If Loop ’If a partial block remains at end of data reader contents, ’display partial block. If (int1 Mod intSize) 0 Then Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
str1 = str1 & StrDup(2, vbCr) _ & “Click OK to close message box." MsgBox(str1, , “From CustOrderHist Stored Proc”) End If ’Close data reader and connection object references. drd1.Close() cnn1.Close() End Sub You can r un t he sam ple defined by t he pr eceding t wo sub pr ocedur es fr om Module1 in t he My ADODOTNETSam ples solut ion. The sam ple pr ocedur e call in t he m ain procedur e for inv oking t he first pr ocedur e follows. I t passes t wo ar gum ent s t o t he RunCust OrderHist Wit hSt r ing pr ocedur e. The first argum ent is a Cust om erI D value, and t he second argum ent designat es t he m ax im um num ber of rows t o display in a m essage box. You can obt ain a r esult set t o display for any Cust om erI D in t he Cust om ers t able t hat has or ders associat ed wit h it . ( Tw o Cust om erI D values don’t hav e any orders.) The solut ion aut om at ically populat es t he argum ent list for t he second sub procedure t hat print s t he rows in t he Dat aReader creat ed by t he RunCust OrderHist Wit hSt r ing pr ocedur e. RunCustOrderHistWithString(“TORTU", 10) I n vok in g a St or e d Pr oce du r e w it h a Pa r a m e t e r by I t s N a m e I t is possible t o inv ok e a st ored procedure and pass it param et er values wit hout using a SQL st ring. Som e developers w ould count t his as an advant age. The approach has t he ext ra advant age of st rong dat a t yping for param et er values on t he client side of a dat abase solut ion. Ther efor e, illegit im at e values can be det ect ed befor e encount er ing t im e for a r ound- t rip t o t he serv er and wit hout div ert ing any valuable ser ver t im e t o error pr ocessing. As t he scale of an applicat ion grows r elat ive t o serv er pr ocessing power and net work t hroughput , t hese considerat ions gain significance. The solut ion t o invoke a st ored procedure w it hout a SQL st r ing requir es you t o assign t he nam e of t he st ored procedure as t he Com m andText propert y for a Com m and obj ect . You m ust also designat e Com m andType.St or edProcedur e as t he Com m andType pr opert y set t ing for t he Com m and obj ect . I f t he st or ed procedur e requires param et ers, y ou can inv ok e t he Add m et hod for t he Param et ers collect ion of t he Com m and obj ect t o declare t he param et ers. As wit h m any Visual Basic . NET m et hods, t he specificat ion for t he Add m et hod of t he Param et ers collect ion has m ult iple over loaded specificat ions. The one used in t he sam ple for t his sect ion uses @Cust om erI D t o designat e t he param et er ’s nam e. The second and t hird ar gum ent s for t he Add m et hod designat e t he @Cust om erI D param et er as a Unicode fixed lengt h t ext field of 5 charact ers. The sam ple follows t he param et er declarat ion w it h t he sy nt ax for assigning an act ual value t o t he param et er. As y ou can see, you use t he param et er’s Value propert y t o per form t his t ask. Aside from t he except ions not ed pr ev iously, t he solut ion for running t he Cust OrderHist st or ed pr ocedur e w it h or w it hout a SQL st r ing is t he sam e. You creat e t he Connect ion obj ect ident ically , and y ou pass t he ret ur n set fr om t he Com m and obj ect t o t he Dat aReader obj ect in t he sam e way . Furt herm ore, t his second- solut ion appr oach uses exact ly t he sam e second sub procedure, drdToMessageBox, t o display t he result set from t he Cust OrderHist st ored procedur e in a ser ies of m essage box es. Sub RunCustOrderHistWithParameter(ByVal CustomerID As String, _ ByVal intSize As Integer) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
’Declare and open connection to Northwind. Dim cnn1 As SqlConnection = _ New SqlConnection(“Data Source=(local);” & _ “Integrated Security=SSPI;Initial Catalog=northwind”) cnn1.Open() ’Instantiate a command reference pointing at the ’CustOrderHist stored proc. Dim cmd1 As SqlCommand = _ New SqlCommand(“CustOrderHist", cnn1) cmd1.CommandType = CommandType.StoredProcedure ’Declare the parameter with a SqlDbType to eliminate ’the need for conversion, then assign the parameter a value. Dim prm1 As SqlParameter = _ cmd1.Parameters.Add(“@CustomerID", SqlDbType.NChar, 5) prm1.Value = CustomerID ’Declare data reader and populate with its result ’set from the stored proc. Dim drd1 As SqlDataReader = cmd1.ExecuteReader() ’Display result set. drdToMessageBox(drd1, cnn1, CustomerID, intSize) End Sub You can inv ok e t he RunCust OrderHist Wit hParam et er procedur e fr om t he m ain procedur e in Module1 for t he My ADODOTNETSam ples solut ion. Sim ply r em ov e it s com m ent m ar ker and ensure t hat all ot her pr ocedur e calls hav e a com m ent m ark er preceding t hem . Cr e a t in g a D a t a ba se Obj e ct w it h a Com m a n d Obj e ct The Com m and obj ect pr ov ides m or e flex ibilit y t han j ust ret ur ning r esult set s. For exam ple, y ou can use a Com m and obj ect t o adm inist er a dat abase obj ect on a SQL Ser ver inst ance. This sect ion dem onst rat es t he capabilit y by adding a new user - defined funct ion t o t he Nort hw ind dat abase, using it , and t hen r em ov ing t he user - defined funct ion. For t his dem onst rat ion t o work , your connect ion m ust be based on a login w it h perm ission t o cr eat e w hat ever user - defined obj ect s you at t em pt t o creat e or drop. See Chapt er 5 for t he T- SQL synt ax on adding and rem ov ing user - defined funct ions and Chapt er 7 for a discussion of t he secur it y associat ed wit h logins t o a SQL Serv er inst ance. I f your login is t he adm inist rat or for y our local inst ance of SQL Serv er, you hav e appropriat e perm ission t o r un t he sam ple. The user - defined funct ion udfDaysDiffLessx in t his sam ple com put es t he difference bet w een t w o dat es m inus an offset . You can use t he funct ion t o r eport how m any days lat e an ev ent occur red. For exam ple, if t he st andard for shipping an order is wit hin 3 day s of t he order dat e, y ou can use t his user - defined funct ion t o r eport how m any day s aft er t he st andard an order ships. The Creat eAndI nvok eUDF procedur e in Module1 illust rat es t he Visual Basic .NET synt ax for creat ing, using, and finally dropping a user - defined funct ion lik e t he one described. The Creat eAndI nv okeUDF procedur e connect s t o t he Nor t hw ind dat abase. The pr ocedur e t ak es t wo opt ional argum ent s. ( I f t he user doesn’t supply v alues for t he ar gum ent s w hen calling t he pr ocedur e, t he pr ocedur e assigns default values t o t he argum ent s.) The int OrderNo argum ent denot es t he OrderI D value for t he or der about which y ou seek shipping inform at ion, and t he Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
st rx argum ent is a st r ing repr esent ing t he offset in days bet ween t w o dat et im e values. While som ewhat lengt hy, t he Cr eat eAndI nv okeUDF procedur e design is st raight forward. I n act ual pract ice, you ar e lik ely t o ext ract t he code for creat ing a user - defined funct ion int o a separat e sub pr ocedur e. The procedure begins by m aking a connect ion t o t he Nort hw ind dat abase. Next t he pr ocedur e defines a SQL st r ing for dr opping any pr ior version of t he udfDaysDiffLessx user- defined funct ion. The pr ocedure r uns t his st ring from a Com m and obj ect wit h t he Ex ecut eNonQuery m et hod. I n t he next code block, t he pr ocedur e runs wit h t he Ex ecut eNonQuery m et hod a second SQL st ring t o creat e a new version of t he udfDaysDiffLessx user- defined funct ion. Not ice t hat t he user- defined funct ion includes a param et er t o specify t he offset for t he difference bet w een t w o dat es. Aft er ensuring t hat t he code for t he user- defined funct ion is t he second SQL st ring, t he procedure r uns a t hird SQL st r ing t hat inv ok es t he user - defined funct ion wit hin a quer y st at em ent . The design of t he SQL st r ing for t he query uses t he st rx argum ent as a var iable so t hat a procedur e calling t he Creat eAndI nvok eUDF pr ocedur e can dynam ically set t he offset bet w een t wo dat es. I n addit ion, t he int OrderNo argum ent is a var iable in t he SQL st ring so t hat a calling procedure can specify t he order via an OrderI D value on w hich t o r eport . The procedur e uses t he Ex ecut eReader m et hod t o r un t he SQL st r ing in a Com m and obj ect and passes t he result t o a Dat aReader. Aft er ex ecut ing t he Read m et hod for t he Dat aReader, a m essage box displays t he shipping inform at ion for t he order. The procedure concludes by perform ing var ious cleanup chor es, including r est oring t he Nort hw ind dat abase so t hat t he dat abase no longer has a user - defined funct ion nam ed udfDaysDiffLessx. I n pract ice, y ou m ay v er y w ell decide t o keep a user- defined funct ion aft er creat ing it , but t he sam ple runs t his st ep t o r est or e your init ial copy of t he Nort hw ind dat abase. Sub CreateAndInvokeUDF( _s Optional ByVal intOrderNo As Integer = 10248, _ Optional ByVal strx As String = “1”) ’Declare and open connection to Northwind. Dim cnn1 As SqlConnection = _ New SqlConnection(“Data Source=(local);” & _ “Integrated Security=SSPI;Initial Catalog=northwind”) cnn1.Open() ’Define SQL string to drop prior version of user-defined ’function, then run the T-SQL batch with ExecuteNonQuery ’method for a command. Dim str1 As String = _ “IF EXISTS “ & _ “(SELECT * “ & _ “FROM INFORMATION_SCHEMA.ROUTINES “ & _ “WHERE ROUTINE_NAME = ’udfDaysDiffLessx’) “ & _ “DROP FUNCTION udfDaysDiffLessx" Dim cmd1 As SqlCommand = New SqlCommand(str1, cnn1) cmd1.ExecuteNonQuery() ’Define SQL string to create a new user-defined function, ’then run the T-SQL batch with ExecuteNonQuery method ’for a command. str1 = “CREATE FUNCTION udfDaysDiffLessx” & _ “(@date1 as datetime, @date2 as datetime, “ & _ “@x as Integer) “ & _ “RETURNS int “ & _ “AS “ & _ “BEGIN “ & _ “Return(DATEDIFF(day,@date1,@date2)-@x) “ & _ Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
“END" cmd1.CommandText = str1 cmd1.ExecuteNonQuery() ’Define a SQL string to use the preceding user-defined ’function and accept variables for SQL string ’(strx and intOrderNo), then assign SQL string to ’CommandText property of command(cmd1). Dim strSQL As String strSQL = “SELECT LEFT(OrderDate,11) AS ’Order Date’, “ & _ “LEFT(ShippedDate,11) AS ’Shipped Date’, “ & _ “dbo.udfDaysDiffLessx(OrderDate, ShippedDate, “ & _ strx & “) AS ’Days Late’ “ & _ “FROM Orders “ & _ “WHERE OrderID = “ & intOrderNo.ToString cmd1.CommandText = strSQL ’Store result set from SQL string in a data reader and ’format its contents for display via a MsgBox function. Dim drd1 As SqlDataReader = cmd1.ExecuteReader() drd1.Read() str1 = “For Order “ & intOrderNo.ToString & vbCr & _ “OrderDate is “ & drd1.GetString(0) & vbCr & _ “ShippedDate is “ & drd1.GetString(1) & vbCr & _ “Days to ship after “ & strx & “ days is “ _ & drd1.GetInt32(2).ToString MsgBox(str1, , _ “SQL string with a scalar user-defined function”) ’Restore the Northwind database by removing the udf. str1 = _ “IF EXISTS “ & _ “(SELECT * “ & _ “FROM INFORMATION_SCHEMA.ROUTINES “ & _ “WHERE ROUTINE_NAME = ’udfDaysDiffLessx’) “ & _ “DROP FUNCTION udfDaysDiffLessx" cmd1.CommandText = str1 ’Close the data reader so the command can use it. drd1.Close() ’Execute the SQL string to drop the user-defined function. cmd1.Connection = cnn1 cmd1.ExecuteNonQuery() ’Finally, close the connection to the Northwind database. cnn1.Close() End Sub The line in t he m ain pr ocedur e of Module1 invoking t he Cr eat eAndI nv okeUDF procedur e specifies an OrderI D of 10249 w it h int OrderNo and an offset of 3 days wit h st rx . I n r esponse t o invok ing t he Creat eAndI nv ok eUDF procedur e wit h t his line, t he procedure pr esent s a m essage box lik e t he one in Figur e 10- 8. I f you were int er est ed in t rack ing perform ance on a next - day deliv er y prom ise, y ou could replace t he value 3 in t he calling pr ocedur e wit h 1. Figu r e 1 0 - 8 . Th e m e ssa ge box displaye d for ru nn in g t h e Cre at e An d I n vok e UD F pr ocedu r e w it h t h e ar gu m en t s spe cifie d for it in t h e m a in pr ocedu r e of M od ule1 . Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
D a t a Ada pt er s, D a t a Se t s, For m s, a n d For m Cont r ols This sect ion covers how t o place a dat a set behind a Windows form and allow users t o int er act wit h t he dat a set t hr ough form cont r ols. You will lear n how t o bind SQL Ser ver dat a t o t he cont r ols on a Windows form . This sect ion cov ers several t ypical design applicat ions such as at t aching dat a t o t ext boxes, com bo boxes, and dat a gr ids. The code sam ples and form designs illust rat e how t o m anage parent - child relat ionships pr ogram m at ically in t he dat a set behind a form as well as int eract ively for a user t hrough form cont r ols. The sect ion closes wit h a sam ple t hat dem onst rat es how t o dy nam ically configure a Windows for m based on t he dat a t hat it has t o show. Addin g a D a t a Se t t o a For m A t y pical way of int eract ing w it h dat a from Visual Basic .NET will be fr om Windows Form s. While you can r eadily present m essage box es t hat show t he Dat aReader cont ent s, m any applicat ions will r equir e a richer form of dat a int e- ract iv it y t han t he forwar d- only , r ead- only m odel support ed by t he Dat aReader. The k ey t o get t ing t o a richer m odel of dat a int eract iv it y is t o place one or m ore dat a set s in t he m odule behind a form . The dat a set obj ect let s users navigat e backward and forward in a dat a set . I n addit ion, users can updat e t he dat a for local use only or at a rem ot e dat a source. Any one dat a set can cont ain m ult iple t ables, and t he dat a set obj ect perm it s t he ex ist ence of hierarchical r elat ionships bet ween t he t ables w it hin it . The k ey t o populat ing a dat a set behind a form wit h dat a fr om a SQL Ser ver inst ance is t o cr eat e a Dat aAdapt er obj ect t hat point s t o a dat a source on a SQL Ser ver inst ance. You can represent t he dat a source on t he ser ver w it h a SQL st ring, a t able nam e, a view, or a st or ed pr ocedur e. As wit h t he Dat aReader obj ect , y ou can repr esent a SQL st r ing for t he Dat aAdapt er obj ect wit h a Com m and obj ect . The Dat aAdapt er obj ect has t wo m ain roles. First , it can fill a dat a set behind a form . That ’s t he focus of t his sect ion. Second, y ou can use a Dat aAdapt er t o updat e a rem ot e dat a source fr om t he dat a set behind a form . That ’s t he focus of t he last m aj or sect ion in t his chapt er . Use t he Dat aAdapt er obj ect ’s Select Com m and propert y t o r efer ence t he Com m and obj ect specifying t he r em ot e dat a source for a Dat aAdapt er . Recall t hat one im port ant role for a Dat aAdapt er is t o copy t o t he dat a set behind a form . Make t he rem ot e dat a source available t hr ough t he Dat aAdapt er by opening t he connect ion for t he Com m and obj ect . Copy t he dat a fr om t he r em ot e dat a source t o t he dat a set by inv ok ing t he Fill m et hod of t he Dat aAdapt er. I n t his t ype of applicat ion, t he Dat aAdapt er r equires t w o argum ent s— one refer encing t he nam e of t he dat a set behind t he form and t he ot her nam ing t he t able in t he dat a set . You can designat e t he t ables w it hin a dat a set eit her by an index num ber indicat ing t he order in which you added t hem t o t he dat a set or by t he nam e t hat you specify as an argum ent t o t he Fill m et hod. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The Populat e procedure t hat follows illust rat es t he sy nt ax for copy ing a rem ot e dat a source t o t he dat a set behind a form . This procedur e is in t he m odule behind Form 4, w hich I will discuss in m or e det ail in t he next sam ple discussion. For now, j ust underst and t hat t he Populat e pr ocedur e is in a m odule behind a Windows for m . I ’ll be using sev er al sam ples t hr oughout t he balance of t his chapt er t hat are variat ions of t his procedure, so I decided t o give t he pr ocedure a sect ion of it s own t o help y ou focus on it . N ot e The code for t he Populat e procedure assum es t he exist ence of an I m por t s st at em ent at t he t op of t he m odule for t he Sy st em .Dat a.SqlClient nam espace. I t ’s com m on t o describe t he Dat aAdapt er as a bridge bet ween a r em ot e dat a source and t he dat a set behind a form . Ther efor e, t he Populat e pr ocedure st art s by declaring a Connect ion obj ect , cnn1. The cnn1 obj ect r efer ence point s t o t he Nort hwind dat abase on t he local inst ance of SQL Serv er . Next t he pr ocedur e declares and inst ant iat es a Com m and obj ect , cm d1. A SQL st r ing specifies t he Cat egoryI D, Cat egory Nam e, and Descript ion colum ns fr om t he Cat egor ies t ables t o designat e t he r esult set from cm d1. The Com m and obj ect cm d1 links t o t he Cat egor ies t able t hrough t he Connect ion obj ect cnn1. Aft er indir ect ly specify ing t he Com m andText pr opert y for a Com m and obj ect , t he procedur e inst ant iat es a Dat aAdapt er obj ect and uses t he dap1 obj ect r eference t o point t o it . I n order for t he dap1 Dat aAdapt er t o fill t he dat a set behind t he form , t wo condit ions m ust hold. First , t he Dat aAdapt er needs a Com m and obj ect assigned t o it s Select Com m and propert y . Assigning cm d1 t o t he Select Com m and propert y of dap1 sat isfies t his condit ion. Second, t he Dat aAdapt er requires an open connect ion t o t he Cat egor ies t able in t he Nort hwind dat abase. I nvoking t he Open m et hod for t he cnn1 obj ect m eet s t his requirem ent . Aft er m eet ing t hese t w o condit ions, t he procedure invokes t he Fill m et hod for dap1. The argum ent s for t he m et hod in t he pr ocedur e designat e Cat egor ies as t he nam e of t he Dat aTable obj ect t hat holds t he result set from cm d1 in t he das1 dat a set . The m odule behind Form 4 declar es and inst ant iat es das1 as a dat a set at t he m odule level. This m ak es t he das1 dat a set available for use in all t he pr ocedur es behind a for m . Of course, it also m eans t hat y ou cannot see t he declarat ion in t he list ing for t he Populat e pr ocedur e. For y our easy refer ence, I include t he st at em ent declaring and inst ant iat ing das1 j ust before t he list ing for t he Populat e procedure. Not ice t hat t he Populat e procedure concludes by closing t he Connect ion obj ect cnn1. I n cont rast t o t he Dat aReader obj ect , t he dat a set obj ect operat es while disconnect ed fr om a r em ot e dat a source. Recall t hat t his abilit y t o oper at e w hile disconnect ed adds t o t he scalabilit y of Visual Basic .NET applicat ions for SQL Ser ver. ’Module-level declaration of data set object. Dim das1 As DataSet = New DataSet() Sub Populate() ’Specify a connection for a data adapter that ’fills the data set used on the form. Dim cnn1 As SqlConnection = _ New SqlConnection _ (“Data Source=(local);” & _ “Integrated Security=SSPI;” & _ “Initial Catalog=northwind”) ’Specify the command and data adapter that serves ’as the source for the data set on the form. Dim cmd1 As SqlCommand = _ Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
New SqlCommand _ (“SELECT CategoryID, CategoryName, Description “ & _ “FROM Categories", _ cnn1) Dim dap1 As SqlDataAdapter = New SqlDataAdapter() dap1.SelectCommand = cmd1 cnn1.Open() ’Fill the data set (das1) with the data adapter dap1; ’the Fill method populates the data set with a table ’named Categories. dap1.Fill(das1, “Categories”) ’Close the connection because a data set is a ’disconnected data source. cnn1.Close() End Sub Bin din g Con t r ols on a For m t o D a t a Aft er populat ing t he dat a set behind a for m , y ou’ll want t o reference t he dat a set wit h t he cont rols on t he form . One way t o accom plish t his is t o bind t he cont r ols t o t he dat a set . Ther e are t wo st y les of dat a binding for cont rols. Sim ple dat a binding m aps a colum n in a local dat a source, such as a Dat aTable in a dat a set , t o a propert y of a cont r ol, such as t he Text pr opert y of a t ext box. Use t he Dat aBindings collect ion of a cont rol t o bind cont rol propert ies t o a colum n of values in a local dat a source. Com plex dat a binding is a second way of binding a cont r ol t o dat a. For t his st yle of dat a binding, a cont r ol— such as a com bo box, list box, or dat a grid— binds t o a collect ion of colum ns, such as a Dat aTable in a dat a set . The sam ple in t his sect ion dem onst rat es bot h approaches for binding cont r ols t o t he Cat egor ies Dat aTable. The preceding sect ion described t he code t hat creat ed t he Cat egories Dat aTable in t he das1 dat a set for Form 4. N ot e One int erest ing new dev elopm ent w it h Visual Basic .NET is t he abilit y t o bind any propert y of a visible cont rol, such as it s BackColor or ForeColor propert y, t o a colum n of dat a. This feat ur e opens t he possibilit y for a local dat a sour ce dynam ically cont rolling t he for m at t ing of a form as well as t he dat a t he form shows. Figur e 10- 9 shows Form 4. At t he left is t he form in Design v iew . At t he t op r ight of t he figur e is t he form aft er it init ially opens. The bot t om r ight of t he figur e shows t he form aft er I select ed Confect ions from t he com bo box. Open Form 4 in Design v iew by double- click ing Form 4.vb in Solut ion Explor er for t he MyADODOTNETSam ples solut ion. Right - click t he solut ion’s nam e in Solut ion Explor er, choose Pr opert ies, and select Form 4 as t he st art up obj ect t o m ake t he for m easy t o launch ( for exam ple, by pr essing t he F5 k ey ) . The Design v iew of For m 4 r ev eals t hat t he for m cont ains a com bo box w it h a label, t w o t ext box es w it h labels, and a but t on. As shown in Chapt er 1, you can graphically bind cont rols at design t im e. How ev er , Form 4 program m at ically set s t he dat a binding for t he com bo box and t he t wo t ext box es. On t he ot her hand, I set several cont r ol feat ures at design t im e. For exam ple, t he Mult iline propert y of Text Box2 is set t o Tr ue, while t he sam e propert y for Text Box1 has t he default Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
set t ing, False. The Mult iline propert y set t ing facilit at es Text Box2 show ing Descript ion colum n values t hat ext end ov er m or e t han one line. Figu r e 1 0 - 9 . A de sign- t im e view an d t w o r u n - t im e vie w s of For m 4 . Th e t w o t e x t boxe s a r e p rogr a m m e d t o u pd at e t h e ir con t en t s ba se d on t h e se le ct ion fr om t h e com bo b ox . The init ial v iew of Form 4 shows t hat when it opens it displays t he first cat egory . Bev erages appears in t he com bo box, and t he t wo t ext boxes show 1 as t he Cat egoryI D and t he descript ion for t he bev er ages product cat egory. Ther e is not hing m andat ory about opening t he form for t he first cat egory — any ot her cat egory w ill w ork equally well. The form synchr onizes t he t wo t ext box es wit h t he com bo box . For exam ple, select ing Confect ions from t he com bo box r ev ises t he cont ent display ed in t he t wo t ext boxes t o 3 and t he descript ion for t he confect ions cat egory . To bind t he form cont r ols t o dat a set colum ns and m ak e t he t ext boxes dependent on t he com bo box select ion t akes j ust a few lines of code. I used five lines of code t o bind t he cont r ols t o dat a set colum n values and set t he cat egory t hat appears w hen t he form opens. This code appears in a form Load event procedur e for Form 4 t hat st art s by calling Populat e t o cr eat e t he das1 dat a set described in t he preceding sect ion. The ev ent procedure put s das1 t o use by binding t he Text propert y of Text Box1 t o t he Cat egoryI D colum n in t he Cat egor ies Dat aTable. You bind a colum n of values t o a t ext box propert y by inv ok ing t he Add m et hod for t he Dat aBindings collect ion of a cont rol. The Add m et hod t ak es a Binding obj ect as an argum ent . The argum ent s for t he Binding obj ect specify t he Tex t Box propert y t o bind ( Text ) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
and t he colum n of values t o bind t o t he pr opert y. This sam ple r equir es t wo argum ent s t o specify t he dat a source t hat binds t o t he t ext box propert y. First designat e t he dat a set nam e— das1. Second indicat e t he t able nam e and colum n nam e w it hin t he dat a set t hat y ou want t o bind t o t he propert y. Use a per iod delim it er t o separat e t he t wo nam es, as in Cat egor ies.Cat egoryI D. The Load ev ent procedure uses t he sam e sy nt ax t o bind t he Descript ion colum n in t he Cat egor ies Dat aTable t o t he Text propert y of Text Box 2. Bot h dat a bindings dem onst rat e t he approach for sim ple dat a binding. I t t akes a couple of lines t o bind t he com bo box t o t he Cat egor ies Dat aTable. Act ually, one line does t he binding, but a second line specifies t he v alues t hat t he com bo box displays for t he user t o m ak e a select ion. Assign a Dat aTable t o t he Dat aSource propert y of a com bo box t o bind t he com bo box t o t he Dat aTable. The sy nt ax for specify ing t he Cat egor ies t able used a nam ed argum ent for denot ing t he t able in t he dat a set . I could also hav e indicat ed t he Cat egor ies t able by indicat ing it s t able index value, such as das1.Tables(0) . This sy nt ax depends on t he t able index values not changing. Aft er set t ing t he Dat aSource propert y for t he com bo box, t he pr ocedur e assigns t he Cat egoryNam e colum n from t he Cat egories Dat aTable as t he value for t he com bo box t o display w hen t he user clicks t he cont r ol t o m ak e a select ion. The final line of t he for m Load event procedur e designat es t he posit ion in a colum n t hat t he cont r ols on Form 4 bound t o t he first t able in t he das1 dat a set are t o show when t he form init ially opens. Posit ion 0 point s t o t he first r ow in a Dat aTable ( for ex am ple, t he Cat egories Dat aTable in t his sam ple) . The Posit ion propert y belongs t o t he BindingCont ext obj ect associat ed w it h a form . The keyw ord Me denot es Form 4 in t he last line of t he form Load ev ent pr ocedur e. Private Sub Form4_Load(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ’Call the routine for creating the data set ’for the form. Populate() ’Bind each text box to a different column in the ’Categories table of the data set (das1)on the form. TextBox1.DataBindings.Add _ (New Binding(“Text", das1, “Categories.CategoryID”)) TextBox2.DataBindings.Add _ (New Binding(“Text", das1, “Categories.Description”)) ’Bind combo box to Categories table in the ’data set (das1) on the form. Because the data set ’includes just one table, its index is 0. ComboBox1.DataSource = das1.Tables(“Categories”) ComboBox1.DisplayMember = “CategoryName" Me.BindingContext(das1.Tables(0)).Position = 0 End Sub The Select edI ndex Changed event procedure for t he com bo box t ak es j ust one line t o synchr onize t he cont ent s of t he t ext box es wit h t he cat egory nam e a user select s from t he com bo box. The index values for a com bo box st art at 0 for t he first it em in t he list for a com bo box. By set t ing t he com bo box’s Select edI ndex propert y t o t he Posit ion propert y of t he form ’s BindingCont ext obj ect , t he line posit ions all cont rols on t he form t o t he sam e row a user select ed indir ect ly when pick ing a cat egory nam e from t he com bo box. Private Sub ComboBox1_SelectedIndexChanged _ (ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles ComboBox1.SelectedIndexChanged Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
’Use selected combo item as basis for text boxes. Me.BindingContext(das1, “Categories”).Position _ = Me.ComboBox1.SelectedIndex End Sub Re por t ing D a t a Bin din gs When work ing wit h a com plex form wit h m any cont rols w it h sim ple dat a bindings or a form t hat y ou didn’t dev elop, y ou m ay find it conv enient t o pr int a report on t he Dat aBindings collect ions for t he cont rols on a form . A but t on on For m 4 inv okes a pr ocedur e t hat generat es such a r eport . The Click event for t his but t on inv okes a pr ocedur e nam ed Pr int BindingMem berI nfo in Module1. You can also r un t his procedure fr om out side a form t o report on t he Dat aBindings collect ions for t he cont rols on a form . As y ou can see fr om t he follow ing list ing, t he Click event for t he but t on m er ely calls t he Pr int BindingMem berI nfo procedure. Howev er , t he call also passes a reference t o Form 4 by using t he k eyw ord Me as an argum ent . The Print BindingMem ber I nfo procedure in t his sam ple is adapt ed from an ex am ple in t he Visual Basic .NET Help file. While t he adapt at ion is subt le, it subst ant ially enhances t he applicabilit y of t he procedure. First , t he adapt at ion works for any for m r eference passed t o it . The sam ple in t he Help file had t o be copied int o t he m odule for any form on which you sought a report . Second, you can run t he adapt ed pr ocedur e ev en if y ou ar en’t in t he form for w hich you seek a report . The sam ple in t he Help file wor ks only fr om a form t hat a user has open w it h t he focus. The Print BindingMem berI nfo pr ocedur e accept s a form r efer ence as an argum ent . For t he r eferenced form , t he procedure st art s a loop t o pass t hr ough all t he cont r ols on t he form . Wit hin t he loop for t he cont rols on a form , t he procedur e runs a second loop t o report any dat a binding for t he cur rent ly select ed cont rol in t he loop t hr ough t he cont r ols. I f t her e are no dat a bindings for a cont r ol, t he inner loop m er ely ret ur ns cont rol t o t he out er loop for t he cont rols. When all t he cont r ols on a form are looped t hr ough, t he Pr int BindingMem berI nfo procedur e ret ur ns cont r ol t o it s calling pr ocedur e, which is t he Click ev ent for But t on1 on Form 4 in t he follow ing list ing. ’From module for Form4. Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click ’Display run-time binding settings specified in this module. Module1.PrintBindingMemberInfo(Me) End Sub ‘From Module1. ‘Adapted from Visual Basic .NET Help; the adaptation accommodates ‘any form as a passed argument and facilitates displaying run-time ‘bindings from outside a form. Sub PrintBindingMemberInfo(ByRef MyForm As Form) Dim thisControl As Control For Each thisControl In MyForm.Controls Dim thisBinding As Binding For Each thisBinding In thisControl.DataBindings ’Print the control’s name and Binding information. Console.WriteLine(ControlChars.Cr + thisControl.ToString( )) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.