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..”.
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.
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.
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>
<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>
<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.
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.
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.
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.
Categories: ASP.NET
chsakell's Blog 
Leave a Reply