Edit GridView control using SqlDataSource and Template fields in ASP.NET 4.5

In a previous post we saw how easy is to bind data to a GridView control, without using a build in Data Sources. In this post we will use an SqlDataSource control which will retrieve some data from a database and will bind them to the GridView control. This time also, GridView control will support Editing, Updating and Deleting records. To start with, open VS2012 and create an empty ASP.NET website. In the Default.aspx page add a GridView control. From the GridView’s control “Smart Tag”, either in Design or Source view, select “New data source..”.

gridsource

Then select “database” leaving the default name for datasource’s ID and click OK. Now you have to specify which database the SqlDataSource control will get data from. I chose a database called “VideoGames”. Click Next two times, leaving the default connectionString name. At the end of this wizard, a “connectionStrings” element will be added to your Web.config file. In the next step, select the table and the specific columns you want to retrieve. Also, click the Advanced settings and check the “Generate INSERT, UPDATE and DELETE statements”. Click, OK, Next and Finish. From the GridView “Smart Tasks” arrow, check enable Paging and Sorting.

sqldatasourceconf

Your Default.aspx page should look similar to this.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="Id" DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:BoundField DataField="Id" HeaderText="Id" InsertVisible="False" ReadOnly="True" SortExpression="Id" />
                    <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                    <asp:BoundField DataField="SortOrder" HeaderText="SortOrder" SortExpression="SortOrder" />
                </Columns>
            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:VideoGamesConnectionString %>" DeleteCommand="DELETE FROM [Genre] WHERE [Id] = @Id" InsertCommand="INSERT INTO [Genre] ([Name], [SortOrder]) VALUES (@Name, @SortOrder)" SelectCommand="SELECT [Id], [Name], [SortOrder] FROM [Genre]" UpdateCommand="UPDATE [Genre] SET [Name] = @Name, [SortOrder] = @SortOrder WHERE [Id] = @Id">
                <DeleteParameters>
                    <asp:Parameter Name="Id" Type="Int32" />
                </DeleteParameters>
                <InsertParameters>
                    <asp:Parameter Name="Name" Type="String" />
                    <asp:Parameter Name="SortOrder" Type="Int32" />
                </InsertParameters>
                <UpdateParameters>
                    <asp:Parameter Name="Name" Type="String" />
                    <asp:Parameter Name="SortOrder" Type="Int32" />
                    <asp:Parameter Name="Id" Type="Int32" />
                </UpdateParameters>
            </asp:SqlDataSource>
        </div>
    </form>
</body>
</html>

If you try and run the page you will actually see the data in the GridView. But still, isn’t so fancy and moreover doesn’t support editing and updating records.

gridstep1

We are going to modify a little bit our gridview control. First, we don’t really need the Id column to be displayed, so go ahead and delete the BoundField with DataField=”Id” from the columns. Instead, add a Template field as follows. I will explain later it’s functionality.

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="Id" DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:TemplateField ShowHeader="False">
                        <EditItemTemplate>
                            <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True" CommandName="Update" Text="Update"></asp:LinkButton>
                            &nbsp;<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel"></asp:LinkButton>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" CommandName="Edit" Text="Edit"></asp:LinkButton>
                            &nbsp;<asp:LinkButton ID="DeleteLink" runat="server" CausesValidation="False" CommandName="Delete" Text="Delete"></asp:LinkButton>
                        </ItemTemplate>
                        <ItemStyle Width="100px" />
                    </asp:TemplateField>
                    <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                    <asp:BoundField DataField="SortOrder" HeaderText="SortOrder" SortExpression="SortOrder" />
                </Columns>
            </asp:GridView>

Run the Default.aspx page again. The gridview will look prettier this time.

gridstep2

We added a TemplateField with two templates. The “ItemTemplate” is what we see when the gridview is in Normal mode. So we added two link buttons, one for Editing and one for Deleting. The one for Editing has a CommandName attribute assigned to “Edit”. This actually tells the SqlDataSource we configured in previous step, to change the GridView in edit mode. If you press the LinkButton you will see that the “Name” and “SortOrder” columns will change to TextBox controls, waiting for new values. Because BoundFields Name and SortOrder are strings, their default EditItemTemplate are TextBox controls. That’s why they turned into textboxes. When you press Edit, notice also that not only the BoundFields switch to edit mode but the TemplateField too. We configured though the EditItemTemplate of the TemplateField, to contain two other LinkButton controls, with CommandName “Update” and “Cancel”. They work in similar and respective way as the “Edit” command. I changed the background color to display the textboxes better.

gridstep3

Now I want to go one step further. In my case, because the Id column of the Genre table i picked from the database, is a foreign key for another table, called Review, if i click the Delete linkbutton, i will receive an exception telling me a constraint is violated.

genrereviewexc

To be honest I dislike that kind of behavior. If it was to through an exception, I would like to know it somehow so I wouldn’t press the damn button. Well, there is a simple way to implement this. First of all, I want to know if and how many reviews are attached to a Genre. I will call that field “NumberOfReviews” and i can do that by modifying the SelectCommand of the SqlDataSource as follow.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:VideoGamesConnectionString %>"
                DeleteCommand="DELETE FROM [Genre] WHERE [Id] = @Id"
                InsertCommand="INSERT INTO [Genre] ([Name], [SortOrder]) VALUES (@Name, @SortOrder)"
                SelectCommand="SELECT Genre.Id, Genre.Name, Genre.SortOrder, COUNT(Review.Id) AS NumberOfReviews FROM Genre LEFT OUTER JOIN Review ON Genre.Id = Review.GenreId GROUP BY Genre.Id, Genre.Name, Genre.SortOrder"
                UpdateCommand="UPDATE [Genre] SET [Name] = @Name, [SortOrder] = @SortOrder WHERE [Id] = @Id">
                <DeleteParameters>
                    <asp:Parameter Name="Id" Type="Int32" />
                </DeleteParameters>
                <InsertParameters>
                    <asp:Parameter Name="Name" Type="String" />
                    <asp:Parameter Name="SortOrder" Type="Int32" />
                </InsertParameters>
                <UpdateParameters>
                    <asp:Parameter Name="Name" Type="String" />
                    <asp:Parameter Name="SortOrder" Type="Int32" />
                    <asp:Parameter Name="Id" Type="Int32" />
                </UpdateParameters>
            </asp:SqlDataSource>

Then, i configure the gridview’s “OnRowDataBound” event, to check if the number of the attached Reviews are greater than zero. If so, i just disable the linkbutton and finally, you can’t press it. Go to GridView’s code add a handler for the OnRowDataBound like this.

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False"
                DataKeyNames="Id" DataSourceID="SqlDataSource1" OnRowDataBound="GridView1_RowDataBound">
                <Columns>
                .
                .
                .

In the code behind file, add the code for the handler. What it does, is check for every row, if this row is type of “DataControlRowType.DataRow” (not a header or a footer) and if so, check the NumberOfReviews field retrieved for that record. If it is greater than 0 then disable the Delete button, if not do nothing. Notice how smoothly the DeleteLink linkbutton is retrieved programmaticaly.

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        switch (e.Row.RowType)
        {
            case DataControlRowType.DataRow:
                DataRowView myDataRowView = (DataRowView)e.Row.DataItem;
                if (Convert.ToInt32(myDataRowView["NumberOfReviews"]) > 0)
                {
                    LinkButton deleteLink = e.Row.FindControl("DeleteLink") as LinkButton;
                    if (deleteLink != null)
                    {
                        deleteLink.Enabled = false;
                    }
                }
                break;
        }
    }

Before running Default.aspx i added a new Genre record without Reviews attached. Check out the results. The newly inserted Genre (first record) can actually be deleted, since the Delete linkbutton is enabled. All other linkbuttons though, are disabled because they have Reviews attached.

gridlast

Advertisements


Categories: ASP.NET

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Chara Plessa

The purpose of this blog is to broaden my education, promote experimentation and enhance my professional development. Albert Einstein once said that “If you can’t explain it simply, you don’t understand it well enough” and I strongly believe him!

chsakell's Blog

Anything around ASP.NET MVC,WEB API, WCF, Entity Framework & AngularJS

Kumikoro

A Front End Developer's Blog

Muhammad Hassan

Full Stack developer with expertise in ASP.NET | MVC | WebAPI | Advanced Javascript | AngularJS | Angular2 | C# | ES6 | SQL | TypeScript | HTML5 | NodeJS, NUCES-FAST CS grad, MS candidate @LUMS, EX-Adjunct Faculty @NUCES-FAST, seasonal blogger & open-source contributor. Seattle, WA

Software Engineering

Web development

IEvangelist

.NET, ASP.NET, C#, MVC, TypeScript, AngularJS

leastprivilege.com

Dominick Baier on Identity & Access Control

Happy DotNetting

In Love with Technology

Knoldus

Knols of experience to your advantage

knowshnet

Search - Read - Request - Share

Rahul's space

Learn, Share and Grow with me !

Dhananjay Kumar

Developer Evangelist @Infragistics | MVP @Microsoft |

Journey to SQL Authority with Pinal Dave

SQL, SQL Server, MySQL, Big Data and NoSQL

Conficient Blog

Random bits of tech from @conficient

Code! Code! Code!

SOLID & KISS

Code Wala

Designing and coding

Microsoft Mentalist

A way to start with Microsoft Technologies

Tony Sneed's Blog

A glimpse into the lives of Tony & Zuzana Sneed

Sriramjithendra Nidumolu

Personal Notes of Sriramjithendra

%d bloggers like this: