7.4 Inserting and Updating Data

Reading and binding data is all very well, but for most applications, it's only part of what the application needs to do. Another important feature is the ability to insert new rows and/or update existing rows of data. As with reading data, the DataSet and SqlDataAdapter (or OleDbDataAdapter) classes come in handy. Another class that is extremely useful is the SqlCommandBuilder (or OleDbCommandBuilder) class, which is discussed later in this section.

Example 7-6, while more complicated than previous examples, adds a relatively small amount of code to support adding and updating rows to the Pubs Titles table.

Example 7-6. InsertUpdateTitles.aspx
<%@ Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> <head>    <title>Insert/Update Example</title>    <script runat="server">       Dim Titles As New DataSet( )       Dim TitlesAdpt As New SqlDataAdapter( )          Sub Page_Load(Sender As Object, e As EventArgs)          If Not IsPostBack Then             GetTitleData("")             BindGrid( )          End If       End Sub          Sub Add_Click(Sender As Object, e As EventArgs)          Page.RegisterHiddenField("EditMode", "Add")          title_id.ReadOnly = False          Display.Visible = False          InsertUpdate.Visible = True               End Sub          Sub Cancel_Click(Sender As Object, e As EventArgs)          Response.Redirect("InsertUpdateTitles.aspx")       End Sub          Sub Edit_Click(sender As Object, e As DataGridCommandEventArgs)          GetTitleData("WHERE title_id = '" & e.Item.Cells(1).Text & "'")          title_id.Text = Titles.Tables(0).Rows(0)(0)          title.Text = Titles.Tables(0).Rows(0)(1)          type.Text = Titles.Tables(0).Rows(0)(2)          pub_id.Text = Titles.Tables(0).Rows(0)(3)          price.Text = String.Format("{0:c}", Titles.Tables(0).Rows(0)(4))          advance.Text = Titles.Tables(0).Rows(0)(5)          royalty.Text = Titles.Tables(0).Rows(0)(6)          ytd_sales.Text = Titles.Tables(0).Rows(0)(7)          notes.Text = Titles.Tables(0).Rows(0)(8)          pubdate.Text = Titles.Tables(0).Rows(0)(9)          Page.RegisterHiddenField("EditMode", "Update")          Display.Visible = False          InsertUpdate.Visible = True       End Sub          Sub BindGrid( )          TitleGrid.DataSource = Titles.Tables(0).DefaultView          TitleGrid.DataBind( )       End Sub          Sub GetTitleData(WhereClause As String)          Dim ConnStr As String = "Data Source=(local)\NetSDK;" & _             "Initial Catalog=Pubs;Trusted_Connection=True;"          Dim SQL As String = "SELECT * FROM titles " & WhereClause          Dim PubsConn As New SqlConnection(ConnStr)          Dim TitlesCmd As New SqlCommand(SQL, PubsConn)          TitlesAdpt.SelectCommand = TitlesCmd          Dim TitlesCB As New SqlCommandBuilder(TitlesAdpt)          ' No need to open or close connection,          '   since the SqlDataAdapter will do this automatically.          TitlesAdpt.Fill(Titles)       End Sub          Sub Submit_Click(Sender As Object, e As EventArgs)          Select Case Request.Form("EditMode")             Case "Add"                GetTitleData("")                Dim NewRow As DataRow = Titles.Tables(0).NewRow                NewRow(0) = title_id.Text                NewRow(1) = title.Text                NewRow(2) = type.Text                NewRow(3) = pub_id.Text                NewRow(4) = Convert.ToDecimal(price.Text.Replace("$", ""))                NewRow(5) = advance.Text                NewRow(6) = royalty.Text                NewRow(7) = ytd_sales.Text                NewRow(8) = notes.Text                NewRow(9) = pubdate.Text                Titles.Tables(0).Rows.Add(NewRow)                TitlesAdpt.Update(Titles)                                 Case "Update"                GetTitleData("WHERE title_id = '" & title_id.Text & "'")                Titles.Tables(0).Rows(0)(0) = title_id.Text                Titles.Tables(0).Rows(0)(1) = title.Text                Titles.Tables(0).Rows(0)(2) = type.Text                Titles.Tables(0).Rows(0)(3) = pub_id.Text                Titles.Tables(0).Rows(0)(4) = _                   Convert.ToDecimal(price.Text.Replace("$", ""))                Titles.Tables(0).Rows(0)(5) = advance.Text                Titles.Tables(0).Rows(0)(6) = royalty.Text                Titles.Tables(0).Rows(0)(7) = ytd_sales.Text                Titles.Tables(0).Rows(0)(8) = notes.Text                Titles.Tables(0).Rows(0)(9) = pubdate.Text                TitlesAdpt.Update(Titles)                              End Select          Response.Redirect("InsertUpdateTitles.aspx")       End Sub    </script> </head> <body>    <h1>Insert/Update Example</h1>    <form runat="server">       <asp:panel  runat="server">          <asp:datagrid              oneditcommand="Edit_Click"             runat="server">             <columns>                <asp:editcommandcolumn                    buttontype="PushButton" edittext="Edit"/>             </columns>          </asp:datagrid>          <asp:button               text="Add New Title" onclick="Add_Click" runat="server"/>       </asp:panel>       <asp:panel  visible="False" runat="server">          <table border="0">             <tr>                <td>Title ID</td>                <td>                   <asp:textbox                        readonly="True" runat="server"/>                </td>             </tr>             <tr>                <td>Title</td>                <td>                   <asp:textbox  runat="server"/>                </td>             </tr>             <tr>                <td>Type</td>                <td>                   <asp:textbox  runat="server"/>                </td>             </tr>             <tr>                <td>Publisher ID</td>                <td>                   <asp:textbox  runat="server"/>                </td>             </tr>             <tr>                <td>Price</td>                <td>                   <asp:textbox  runat="server"/>                </td>             </tr>             <tr>                <td>Advance</td>                <td>                   <asp:textbox  runat="server"/>                </td>             </tr>             <tr>                <td>Royalty</td>                <td>                   <asp:textbox  runat="server"/>                </td>             </tr>             <tr>                <td>Year-to-date Sales</td>                <td>                   <asp:textbox  runat="server"/>                </td>             </tr>             <tr>                <td>Notes</td>                <td>                   <asp:textbox                        textmode="MultiLine"                       rows="5"                      columns="20"                      runat="server"/>                </td>             </tr>             <tr>                <td>Publishing Date</td>                <td>                   <asp:textbox  runat="server"/>                </td>             </tr>             <tr>                <td>                   <asp:button                       text="Submit" onclick="Submit_Click" runat="server"/ >                </td>                <td>                   <asp:button                       text="Cancel" onclick="Cancel_Click" runat="server"/ >                </td>             </tr>          </table>               </asp:panel>    </form> </body> </html>

The discussion of the code begins with the <body> section of the page. This section contains a server-side <form> element, which provides support for page postbacks and adds automatic support for such things as control state management. Contained within the form are two Panel controls, which render as <div> elements on the client. Panel controls are very useful when you want to provide more than one set of user interface elements on a page, but only want to display one at a given time.

Inside the first Panel control, which will display items from the Titles table, we declare a DataGrid control, to which we add a ButtonColumn control to provide access to the edit mode of the page and a Button control that will allow us to add a new item. To enable handling of the Edit button in the DataGrid, we set the DataGrid's onEditCommand attribute to the name of the event handler for the Edit button.

The second Panel control contains the form fields that will be used to edit or add a new item, as well as Submit and Cancel buttons. It makes sense for the default mode for the page to be displayed, so we set the Visible property of the second panel control to False. Note that we also set the ReadOnly property of the title_id textbox to True to prevent this field from being edited for existing data, since the Title ID field is what uniquely identifies a title in the table.

Turning to the code, note that the example declares both the DataSet and SqlDataAdapter classes at the page level so that they will be available to all procedures.

In the Page_Load event handler, we check to see if the current request is the result of a postback. If not, we call the GetTitleData method (passing an empty string). The GetTitleData method, which allows us to pass a Where clause argument to be appended to the SQL string, uses the techniques demonstrated previously to retrieve the desired set of rows from the Titles table in the Pubs database.

The main difference between Example 7-5 and the previous examples is that the code in Example 7-5 declares a new SqlCommandBuilder instance, passing it a SqlDataAdapter instance whose SelectCommand property is already set. Here's where ADO.NET magic really happens. The SqlCommandBuilder will automatically generate appropriate Insert, Update, and Delete commands for the Select statement set on the data adapter and populate the InsertCommand, UpdateCommand, and DeleteCommand properties of the SqlDataAdapter with these values. This step saves us the trouble of having to create these statements manually.

If you want to construct Insert, Update, and Delete statements yourself or use stored procedures for these commands, you are free to do so. You can do so by creating separate SqlCommand objects with the desired properties and then setting the InsertCommand, UpdateCommand, or DeleteCommand property of the SqlDataAdapter to the newly created SqlCommand instance.

Once we've filled the dataset with data from the Titles table, we call BindGrid from Page_Load. Calling BindGrid sets the DataSource property of the DataGrid control to the DefaultView property of the first table in the dataset, which returns a DataView containing all the data in the table. At this point, the output of the page should look like Figure 7-5.

Figure 7-5. Display mode output of InsertUpdateTitles.aspx
figs/anet2_0705.gif

The user viewing the page has two options: click the Edit button for one of the rows or scroll down to the bottom of the page and click the Add New Title button (not shown in Figure 7-5).

Clicking the Edit button invokes the Edit_Click event handler, which calls GetTitleData, passing a WHERE clause that causes it to retrieve only the selected row. Next, it sets the form fields in the second panel control to the values returned from GetTitleData, and then registers a hidden form field that indicates that we're updating a row (as opposed to adding a new row). This will become important later, when we submit our changes. Finally, we set the Visible property of the first panel to False and the second to True, which displays the form fields for editing.

If the Add New Title button is clicked, we register a hidden form field (indicating that the Add mode is enabled), set the ReadOnly property of the title_id textbox to False (since we'll need a title ID for the new row), and then reverse the visibility properties of the panel controls again to display the blank form fields. At this point, the output of the page should look like Figure 7-6.

Figure 7-6. Add mode output of InsertUpdateTitles.aspx
figs/anet2_0706.gif

In Edit or Add mode, if the user clicks the Cancel button, we simply call Response.Redirect and redirect back to the original page, essentially starting the whole process over again.

If the user clicks Submit, we use a Select Case statement to evaluate whether we're adding a new row or updating an existing one. If we're adding a new row, we call GetTitleData, call the NewRow method of the first table object to create a new DataRow instance, and then set the item values of the new row to the values in the form fields. Once all values have been set, we add the row to the DataTable and (outside of the Select Case statement) call the SqlDataAdapter's Update method, which updates the backend database with the new row.

If we're updating an existing row, we call GetTitleData with a WHERE clause for that specific row, set its items to the values in the form fields, and call Update again to save the changes to the backend database. Once we've called Update, we call Response.Redirect to redirect the user back to the original page, which again clears the decks and starts from scratch (with the new data, of course).

Example 7-5 demonstrates "last-in-wins" data concurrency. Be aware that using this type of concurrency control can result in overwriting changes made by another user between the time data was queried and when it was updated. In a multi-user environment, you should always carefully consider the potential costs and effects of multiple users attempting to update the same data simultaneously and design your applications accordingly. Strategies can include locking data from the time it is read until the update is complete, or using a timestamp before updating to ensure that the data was not modified from its last known state.



ASP. NET in a Nutshell
ASP.NET in a Nutshell, Second Edition
ISBN: 0596005202
EAN: 2147483647
Year: 2003
Pages: 873

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net