“VALUES(@CompanyName, @Phone) “ & _
“SET @Identity = SCOPE_IDENTITY()"
cmd1.ExecuteNonQuery()
cnn1.Close()
End Sub
Dat a Manipulat ion from ASP.N ET
After running the application in t he preceding section, you will have a new copy of
the ASPNETShippers table in the Northwind database as well as a stored
procedure, udpI nsert ANew ASPNETShipper, to facilitate inserting new rows into
the ASPNETShippers table. The sample application in this section illustrates how
to m odify, insert, and delet e rows in the ASPNETShippers table from an ASP.NET
application. The application builds on the browsing sample illustrated by the
WebForm 1.aspx page in the NavTextBoxes proj ect. The addition of data
manipulation to that earlier application is particularly interesting because that
application used an unbound form to display colum n values from the Shippers
table. I n other words, the form field values don’t bind t o any data source.
Therefore, all data manipulation tasks must be performed in code. The sam ple in
this section is im portant for another reason. The application enables users to
update a SQL Server database over the Web.
N ot e
As with the sample in the preceding section, users of this
application must have permission to perform a task before
the application autom ating that task will work. In this
section, the tasks are the classic three data manipulation
ones of m odifying, inserting, and deleting records.
W e b Page Layou t a nd Opera t ion
Figure 11-21 shows the Web page layout for this application. The page’s name is
WebForm 1.aspx in the UpdateWithWebForm project. Except for four new buttons
at the bottom of the page, the page layout looks similar to the one in Figure 11-
18 for browsing records with text box controls. The four new buttons facilitate
changes to the row currently appearing on t he Web page, delete the row
currently appearing, and clear t he fields and insert a new row int o the database
based on new form field values. The Web page in Figure 11-21 connects to the
ASPNETShippers, instead of the Shippers, table in the Northwind database. This
distinction enables application users of the sam ple in this section to m anipulate
the data appearing on the Web page without changing the original data in the
sample database. By contrasting the Design views of the two pages, you will also
notice that the new Web page has no ADO.NET objects created at design tim e.
You can tell t his because the system tray doesn’t appear in Figure 11-21.
Figure 1 1 - 2 1 . W ebForm 1 .a spx in t h e Upda t eW ithW ebForm proj ect .
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The entire reason for this sample is to dem onstrate that you can enable data
manipulation tasks from a Web page with ASP.NET. Figure 11-22 starts to
dem onstrate this capability by showing two views of the Web page in operation.
The top window shows the Web page aft er a click of the Clear button (its name is
cm dClear) and then the addition of new text in two of the text boxes. The
cm dClear button clears the form on the Web page so the user can enter a new
row of data. I n this top window, you can see that the Com panyNam e and Phone
values for a new shipper have been entered but that the top text box for
ShipperI D is em pty. That’s because the SQL Server instance assigns a value to
this field. Clicking the I nsert button (its nam e is cm dI nsert) changes the browser
to look like the window in the bottom port ion of Figure 11-22. Notice that the
Com panyNam e and Phone text boxes are the sam e. However, you now have a
ShipperID text box value. SQL Server generated this value on the server, and the
UpdateWithWebForm application retrieved the value by capturing the return
parameter value from the udpInsertANewASPNETShipper stored procedure.
Figure 1 1 - 2 2 . W ebForm 1 .a spx in t h e Upda t eW ith W ebForm proj ect before
and aft er com m itting an in sert t o the ASPN ETSh ippe rs t a ble.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Figure 11-23 shows the row entered in the preceding sample in the process of
being m odified. Notice that the user changed the last digit in the telephone
num ber from 7 to 8. The application doesn’t comm it this change until the user
clicks the Modify button. (I ts nam e is cm dModify.) After the user clicks
cm dModify, the application conveys the new value to the SQL Server instance and
refreshes the local copy of the data set on t he Web page with the table of values
from the SQL Server instance.
Figure 1 1 - 2 3 . W ebForm 1 .a spx in t h e Upda t e W ithW ebForm project j u st
befor e a m od ifica t ion t o the t eleph on e n um be r for t he r ow adde d in
Figure 1 1 - 2 2 .
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Figure 11-24 shows the Delete button (its name is cm dDelet e) in operation. I n
the top window, you can see the new row with its edited Phone value. The cursor
rests on the cm dDelet e button. Clicking this button removes the row appearing
on the Web page from the ASPNETShippers table in the SQL Server instance and
updates the data set on the Web page to reflect this result. Because the click to
the cm dDelet e button rem oved the former last row in the Shippers data set, the
Web page shows the new last row with a ShipperID column value of 3. The Click
event procedure behind the cm dDelet e button m anages the display of which row
appears on the page after the rem oval of a row. For exam ple, if a user rem oved
the first row (with a ShipperID colum n value of 1), the row appearing on the Web
page would be the new first row in the data set with a ShipperID value of 2.
Figure 1 1 - 2 4 . W ebForm 1 .a spx in t h e Upda t e W ithW ebForm project j u st
befor e and just after t h e rem ova l of t h e row t hat h ad it s Ph on e v alu e
edit ed in Figur e 1 1 - 2 3 .
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Code Be hind t h e W eb Page
The code for performing data manipulation on a Web page parallels that for
perform ing data manipulation in a Windows form. However, one important
difference is that in a Windows form, your code has to create most of the
ADO.NET obj ects, such as a SqlDat aAdapter and a data set, just once. I n the case
of a Web application, your code will typically have to re-create these objects each
time a Web page does a round-trip between a browser and a Web server— for
exam ple, every t ime the user clicks a butt on on the Web page. As mentioned
previously, you should keep the size of the data set on a Web page sm all for this
reason. In our case, the ASPNETShippers table is already a sm all table of j ust
three original rows. I f your original data source is large, consider pulling down
just a small portion of it on successive round-trips by using a stored procedure or
a user-defined function that ret urns a customizable result set based on input that
you pass it through a paramet er.
The following listing includes those parts of the code behind the Web page that
pertain directly to the data m anipulation part of the sample. The data display and
navigation code closely follow the code appearing in the section tit led “Navigating
Text Boxes Through a Data Set”. In order to conserve space in this book for fresh
information, I direct you to the code samples for this book, where the complete
listing is available for the code behind the Web page.
The listing starts by declaring and instantiating at the m odule level four ADO.NET
objects: a SqlConnection object (cnn1), a SqlCom m and object (cm d1), a
SqlDat aAdapter object (dap1), and a DataSet object (das1). The Page_Load
event procedure uses each of these objects, and the objects find selective use in
other event procedures throughout the code behind the page.
After m aking a connection to the Northwind dat abase, the Page_Load event
procedure declares the dap1 data adapter dependent on all the colum ns from all
the rows of the ASPNETShippers table. Next the procedure defines the
Updat eCom m and property for the data adapter. A SQL UPDATE statem ent with
parameters specifies the items to update. After the SQL statem ent for the
Updat eCom m and property, two additional statem ents add param eters to the
command for the Updat eCom m and property. These param eters allow the Web
page to pass values from the entries in its text boxes. The next parameter is for
the text box holding a ShipperID value on the Web page. Whereas the param eter
ties to the ShipperID value in the data set behind the Web page, it uses the row
currently appearing in the first text box on the Web page.
The next several lines of code in the Page_Load event procedure define the
I nsertCom m and property for the dap1 data adapter and its associated
parameters. I n this case, the procedure designates the perform ance of the insert
via the udpI nsertANew ASPNETShipper stored procedure. Recall that the
preceding section dem onstrated how to create this stored procedure in the code
behind a separate stand-alone Web page. The statem ents adding parameters
illustrate t he syntax for passing param eters to a stored procedure and capturing a
return value from a stored procedure. Notice that you designate a Direct ion
property for prm 2 with the Param et erDirect ion.Output enum eration value. This
parameter (prm2) returns the Identit y value for the inserted row by the SQL
Server instance.
The next block of code in the Page_Load event procedure defines the
Delet eCom m and property and its parameter for the dap1 object. This block of
code uses a SQL DELETE statem ent to designate which row to drop from the
ASPNETShippers table along wit h the row’s copy in the data set behind the Web
page. Because the ShipperID value is the primary key of the ASPNETShippers
table, t he code uniquely identifies a row to remove from the table by specifying a
value for this column.
After defining the dap1 data adapter and its data manipulation properties, the
Page_Load event procedure perform s two more essential tasks. First the
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
procedure fills the das1 data set with the values from the ASPNETShippers table.
Second the procedure populates the text boxes on the page with values from the
first row of t he das1 data set.
Users m ake a change by m odifying the CompanyNam e or Phone text box values
on the Web page and t hen clicking the cm dModify button. In the Click event for
this button, the procedure declares and instant iates a data view (dav1) based on
the ASPNETShippers Dat aTable in the das1 data set. The procedure defines a sort
key for dav1 based on the ShipperID column. Then t he procedure uses the data
view’s Find m ethod to return the row index for t he row with a ShipperID colum n
value matching the current ShipperI D value displayed on the Web page. Defining
the sort key is a necessary step for using the Find m ethod (because the
ASPNETShippers Dat aTable in das1 doesn’t have a primary key constraint). With
the index reflecting the row displayed on the Web page, the procedure creates a
DataRow based on the data table’s schema and data that points to the row from
the data table displayed on the Web page. Then the procedure updates the row’s
colum n values wit h those from the Web page. This modifies the data table. Finally
the procedure closes by invoking the Updat e method for the dap1 data adapter.
This method transfers the changes from the local table to the m atching one on
the SQL Server instance.
The cmdClear and cm dInsert buttons work together. I n general, a user should
click the cm dClear button before clicking the cm dI nsert button. The cm dClear
button’s Click event procedure blanks the text box controls on the Web page.
Next the user should insert values in those blank controls. The Click event
procedure for the cm dInsert button declares a new row to add to the
ASPNETShippers data table and then populates the row with values from text
boxes on the Web page. At this point, updating the local data table is as easy as
invoking the Add m ethod. This step appends the new row to the end of the
ASPNETShippers data table. Next the procedure invokes the Update m ethod to
upload the new row t o the ASPNETShippers table in the SQL Server instance and
to download the ShipperID value created on the SQL Server instance. The output
parameter statem ents for the dap1 I nsertCom m and property in the Page_Load
event procedure autom atically assure the proper handling of the return value
from the udpI nsertANewASPNETShipper stored procedure. Finally the procedure
updates the MyRowI D Session variable to point to the last row in t he data table
and shows this row (where ADO.NET perform s the insert) on the Web page.
N ot e
The cm dModify button Click event procedure doesn’t update
the MyRowI D Session variable or the values on the Web
page. At the end of the modify update, the correct row
already appears on the Web page. Therefore, there is no
need to show another record.
The cm dDelet e_Click event procedure borrows from the logic of the two preceding
event procedures and adds a new wrinkle or two. First the procedure defines a
data view to find an index value for the row displayed on the Web page, which is
the row a user wants to delete. The second step is new. I n this step, the
procedure invokes the Delete m et hod for the row in the data table that m atches
the row displayed on the Web page. This method doesn’t physically rem ove the
row from the local data table. I nstead, it m arks the row for delet ion. When the
procedure invokes the Updat e method in the third step, it finds all rows m arked
for delet ion and rem oves them from the SQL Server version of the
ASPNETShippers table. I n this application, there will always be just one such row.
After rem oving the row on the server, the Updat e method automatically removes
the row locally. This tim e the procedure physically removes the row from the local
data table. After completing the delet ion, t he procedure displays on the Web page
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
the previous row before the one just deleted. If that row (the one just deleted)
was the first row, the procedure shows the old second row, which is the new first
row.
Dim cnn1 As New SqlClient.SqlConnection()Dim cmd1 As New SqlClient.Sq
lCommand()
Dim dap1 As New SqlClient.SqlDataAdapter()
Dim das1 As New DataSet()
Private Sub Page_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
’Make connection to northwind database and
’point data adapter (dap1) at it
cnn1.ConnectionString = “Data Source=(local);” & _
“Initial Catalog=northwind;” & _
“Integrated Security=SSPI"
dap1 = _
New SqlClient.SqlDataAdapter( _
“SELECT * FROM ASPNETShippers", cnn1)
’Set the UpdateCommand property for dap1.
dap1.UpdateCommand = _
New SqlClient.SqlCommand _
(“UPDATE ASPNETShippers “ & _
“SET CompanyName = @CompanyName, “ & _
“Phone = @Phone “ & _
“WHERE ShipperID = @ShipperID", _
cnn1)
’Add two parameters that take source columns
’from the ASPNETShippers table in the dataset for the
’dap1 adapter and feed the parameters in the SQL
’string for the UpdateCommand property.
dap1.UpdateCommand.Parameters.Add _
(“@CompanyName", SqlDbType.NVarChar, 40, _
“CompanyName”)
dap1.UpdateCommand.Parameters.Add _
(“@Phone", SqlDbType.NVarChar, 24, _
“Phone”)
’Specify matching criterion values based on the
’original version of the ShipperID column in the
’local ASPNETShippers table.
Dim prm1 As SqlClient.SqlParameter = _
dap1.UpdateCommand.Parameters.Add _
(“@ShipperID", SqlDbType.Int)
prm1.SourceColumn = “ShipperID"
prm1.SourceVersion = DataRowVersion.Original
’Point InsertCommand at a SQL Server stored procedure;
’you must have the stored procedure on the server.
dap1.InsertCommand = New _
SqlClient.SqlCommand(“udpInsertANewASPNETShipper", cnn1)
dap1.InsertCommand.CommandType = CommandType.StoredProcedure
’Specify input parameters for the stored procedure.
dap1.InsertCommand.Parameters.Add _
(“@CompanyName", SqlDbType.NVarChar, 40, _
“CompanyName”)
dap1.InsertCommand.Parameters.Add _
(“@Phone", SqlDbType.NVarChar, 24, _
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
“Phone”)
’Designate an output parameter for the identity
’value assigned within SQL Server so that your
’local ASPNETShippers table can have a matching
’ShipperID column value.
Dim prm2 As SqlClient.SqlParameter = _
dap1.InsertCommand.Parameters.Add _
(“@Identity", SqlDbType.Int, 0, “ShipperID”)
prm2.Direction = ParameterDirection.Output
’Specify the SQL string for the DeleteCommand
’property of dap1.
dap1.DeleteCommand = _
New SqlClient.SqlCommand(“DELETE “ & _
“FROM ASPNETShippers “ & _
“WHERE ShipperID = @ShipperID", cnn1)
’Specify matching criterion values based on the
’original version of the ShipperID column in the
’local ASPNETShippers table.
Dim prm3 As SqlClient.SqlParameter = _
dap1.DeleteCommand.Parameters.Add _
(“@ShipperID", SqlDbType.Int)
prm3.SourceColumn = “ShipperID"
prm3.SourceVersion = DataRowVersion.Original
’Fill dataset.
das1 = New DataSet()
dap1.Fill(das1, “ASPNETShippers”)
’On initial page load move to first row and
’populate text boxes; this code segment must
’appear after you create the local ASPNETShippers
’table.
If Not Me.IsPostBack Then
Session(“MyRowID”) = 0
MoveToRow()
End If
End Sub
Private Sub cmdModify_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdModify.Click
’Use dav1 to find the row in ASPNETShippers
’that appears in the text boxes from the local
’ASPNETShippers table.
Dim dav1 As DataView = _
New DataView(das1.Tables(“ASPNETShippers”))
dav1.Sort = “ShipperID"
Dim rowindex As Integer = _
dav1.Find(TextBox1.Text)
’Create a DataRow object pointing at the row
’to update in the local table.
Dim IndexedRow As DataRow = _
das1.Tables(“ASPNETShippers”).Rows(rowindex)
’Update the local table with the text boxes.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
IndexedRow(“CompanyName”) = TextBox2.Text
IndexedRow(“Phone”) = TextBox3.Text
’Invoke Update method for dap1 to synchronize
’the local table with the one in northwind.
dap1.Update(das1, “ASPNETShippers”)
End Sub
Private Sub cmdClear_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdClear.Click
’Clear text boxes for data entry.
TextBox1.Text = “"
TextBox2.Text = “"
TextBox3.Text = “"
End Sub
Private Sub cmdInsert_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdInsert.Click
’Add text box values to new row in dataset Shippers table.
Dim newRow As DataRow = das1.Tables(“ASPNETShippers”).NewRow()
newRow(“CompanyName”) = TextBox2.Text
newRow(“Phone”) = TextBox3.Text
das1.Tables(“ASPNETShippers”).Rows.Add(newRow)
’Update method synchronizes inserted local row
’with its copy in northwind and returns the identity
’column value added by the northwind database.
dap1.Update(das1, “ASPNETShippers”)
’Move to last row and populate text boxes.
Session(“MyRowID”) = das1.Tables(“ASPNETShippers”).Rows.Count - 1
MoveToRow()
End Sub
Private Sub cmdDelete_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdDelete.Click
’Create a dataview based on the ASPNETShippers table
’in the dataset and find the row index that matches
’the current ShipperID.
Dim dav1 As DataView = _
New DataView(das1.Tables(“ASPNETShippers”))
dav1.Sort = “ShipperID"
Dim rowIndex As Integer = _
dav1.Find(TextBox1.Text)
’Mark the row for deletion in the dataset.
das1.Tables(“ASPNETShippers”).Rows(rowIndex).Delete()
’Invoke the Update method to complete the deletion
’in both the SQL Server and dataset Shippers tables.
dap1.Update(das1, “ASPNETShippers”)
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Không có nhận xét nào:
Đăng nhận xét