Tuesday, January 30, 2007

Starting to get more questions about GridViews and SqlDataSources on the forums I haunt.  On the surface, these two components seem to offer a quick path to application development with no coding. 

And sure enough, once you figure them out, you don't have to do much to get an updatable GridView on the page.

Here's an example of a fully updating GridView...

<asp:GridView ID="NoCodeGridView" runat="server" DataSourceID="TestSqlSource" AutoGenerateEditButton="True"   DataKeyNames="NameID">
</asp:GridView>

<asp:SqlDataSource ID="TestSqlSource" runat="server" ConnectionString="<%$ ConnectionStrings:TestConnectionString %>" SelectCommand="SELECT * FROM [BabyName]" UpdateCommand="UPDATE BabyName SET Name = @Name WHERE NameID = @NameID">
            <UpdateParameters>
                <asp:Parameter Name="Name" Size="50" Type="String" />
                <asp:Parameter Name="NameID" Type="Int32" />
            </UpdateParameters>
 </asp:SqlDataSource>

The code behind file is strangely empty, yet clicking on a row's edit button brings up an editable row with an update and cancel button.  And clicking on the update button saves the altered baby name.  This scares and frightens me, because I know it won't be this easy once I need to do something real. While I appreciate that the details of implementation have been hidden from me, the fact that I can't see them also worries me. I'll leave that aside for the moment. I will also ignore the fact that I have embedded SQL statements in an ASPX page, and we'll move on and examine what's needed so the snippet above works.

The key in the GridView is this attribute:

AutoGenerateEditButton="True" 

This tells the GridView to generate the Edit button and Update/Cancel button. There are other ways to do this, but we're looking for the quickest, dirtiest way at the moment.

The real keys are in the SqlDataSource.  Without a select command nothing would be bound to the grid. And it obviously needs an update command, otherwise you get this exception...

Updating is not supported by data source 'TestSqlSource' unless UpdateCommand is specified.

Reading the stack trace for the exception shows you what ASP.NET is doing behind the scenes. 

   System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +1828259
   System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues,    DataSourceViewOperationCallback callback) +78
   System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1215
  
System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +858
   System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +95
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
   System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +117
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
   System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115
   System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +163
   System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +174
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

The last three are the interesting ones (the stack reads down from the last method called to the first).  The GridView's HandleEvent figures out an Update is going on and calls HandleUpdate, which in turn calls the Update method on the DataSourceView, which in turn calls the ExecuteUpdate on the SqlDataSourceView which does the real work. 

Let's take a little side trip and see what we can find out about these methods.

GridView.HandleUpdate is not a public method and hence does not appear in the documentation.

DataSourceView.Update is public.  DataSourceView is an abstract base class that alll the DataSourceView implementations are derived from.  This method calls the protected ExecuteUpdate method on that has been implemented by the inheriting class, in this case SqlDataSourceView.ExecuteUpdate. All pretty straightforward, and the SqlDataSourceView has some event handlers so it looks like there are places to hook in when actual programming is required.

Let's end our little side trip and get back into seeing what's needed for the little snippet to work.

One thing I noted was the documentation frequently mentions parameters, so they must be needed to make this work.  Let's pull out the <UpdateParameters> element and see what happens.

Begorra! The GridView does not throw an error and still updates.  It seems everything will be peachy if the update command parameter names correspond to columns in the select command. Let's run a wee test and change a parameter name in the update command. @Name will become @Name1

Didn't like that all

Must declare the variable '@Name1'.

So this is all you actually need...

     <asp:GridView ID="NoCodeGridView" runat="server" DataSourceID="TestSqlSource" AutoGenerateEditButton="True" DataKeyNames="NameID">
     </asp:GridView>
     <asp:SqlDataSource ID="TestSqlSource" runat="server" ConnectionString="<%$ ConnectionStrings:TestConnectionString %>" SelectCommand="SELECT * FROM [BabyName]" UpdateCommand="UPDATE BabyName SET Name = @Name WHERE NameID = @NameID">
        
     </asp:SqlDataSource>

As long as the SqlDataSource's SelectCommand returns column names that correspond to the parameter names used in the UpdateCommand it all works.

Next time we'll see what happens when you need to do something a little fancier.

Tuesday, January 30, 2007 10:18:17 PM (Eastern Standard Time, UTC-05:00)   #     Comments [1]  | 

Theme design by Dean Fiala

Pick a theme: