Update Multiple Records with the DataGrid, DataList and Repeater

by Lee Chen


.NET provides us with rich data controls to update a database. Both the DataGrid and DataList controls have built-in support for updating data with EditItemTemplate. These controls make the process easy, powerful, and smooth. However, the .NET data controls' built-in database updating is a single-row-based updating. You update only one record at once, and the Repeater provides no updating capability at all. In our daily programming practice, we need a function to update more than one record at a time.

Here, I will show you a very simple way to update multiple data records using Repeater, DataList or DataGrid controls.

Let's take Repeater as an example first.

I here use SQL Server sample database Pubs as our database. Two tables will be involved: Titles and Publishers. First, I create four Stored Procedures:

  1. BookInformationUpdate -- displays book information on web;
  2. BookInformationUpdate -- updates book information;
  3. BookPublisher -- gets book publisher name, and displays them in a DropDownList control for update;
  4. BookType -- gets distinct book types, and displays them in a DropDownList control for update;

Then, create a web form to display and update book information:

<%@ Page Language="C#" %>

<%@ import Namespace = "UpdateRecord" %>
<%@ import Namespace = "System.Data" %>
<%@ import Namespace = "System.Data.SqlClient" %>

<script runat="server">

//Page load to call function getBookInformation
void Page_Load(Object send, EventArgs E){
  if (! IsPostBack){
     getBookInformation();
  }
}

//Bind book information data to a Repeater control
void getBookInformation(){
    pubDB getBooks = new pubDB();
   
    repeaterBooks.DataSource = getBooks.GetBooksTitle();
    repeaterBooks.DataBind();
}

void btnSubmit_Click(Object send, EventArgs E){
   //Update code will be here	
	
}

//Function to display book type in a DropDownList
DataSet getBookType(){
    pubDB getBooks = new pubDB();
	
	return getBooks.GetBooksType();
}

//Function to set a selected index for individual book's tpye
int getBookTypeIndex(string bookType){
    pubDB getBooks = new pubDB();
	
    return getBooks.getIndexForType(bookType.ToString().Trim());
}

//Function to display book publisher in a DropDownList
DataSet getBookPublisher(){
    pubDB getBooks = new pubDB();
	
    return getBooks.GetBooksPublisher();
}

//Function to set a selected index for individual book's publisher
int getBookPublisherIndex(string bookPub){
    pubDB getBooks = new pubDB();
	
    return getBooks.getIndexForPublisher(bookPub.ToString().Trim());
}

</script>

<html>
<body>
<form runat="server">
<table width="100%" border="0">

  <tr>
    <td colspan="4" align="center">
      <font face="Arial" size="2">

      <b>Update Multiple Records with Repeater, DataList, or DataGrid</b>
      </font>
    </td>
  </tr>
  <asp:Repeater ID="repeaterBooks" runat="server">

    <headertemplate>
      <table align="center" width="100%" border="0" cellpadding="2"
        cellspacing="0">

      <tr bgcolor="#E17546">
        <td width="40%"><font face="Arial" size="2"
          color="#FFFFFF"><b>Book Title</b></font></td>

        <td width="20%"><font face="Arial" size="2"
          color="#FFFFFF"><b>Book Type</b></font></td>

        <td width="20%"><font face="Arial" size="2"
          color="#FFFFFF"><b>Book Price</b></font></td>

        <td width="20%"><font face="Arial" size="2"
          color="#FFFFFF"><b>Book Publisher</b></font></td>

      </tr>
    </headertemplate>
    <itemtemplate>
      <tr bgcolor="#F9FFDF">
        <td width="40%" valign="top">

          <font face="Arial" size="2">
          <%# DataBinder.Eval(Container.DataItem,"Title").ToString() %>
          </font
        </td>
        <td width="20%" valign="top">

          <asp:DropDownList ID="ddlBookType" DataValueField="Type"
            DataTextField="Type"   
            DataSource='<%# getBookType() %>'
            SelectedIndex='<%# getBookTypeIndex(Convert.ToString(
              DataBinder.Eval(Container.DataItem,"Type"))) %>' 
            runat="server">

          </asp:DropDownList>
        </td>
        <td width="20%" valign="top">
          <asp:TextBox Id="txtBookPrice" Width="70"

            Text='<%#
              DataBinder.Eval(Container.DataItem,"price").ToString() %>'
            runat="server">
          </asp:TextBox>
        </td>
        <td width="20%" valign="top">

          <asp:DropDownList ID="ddlBookPublisher" DataValueField="pub_id" 
            DataTextField="pub_name" 
            DataSource='<%# getBookPublisher() %>'
            SelectedIndex='<%# getBookPublisherIndex(Convert.ToString(
              DataBinder.Eval(Container.DataItem,"pub_id"))) %>'
            runat="server">

          </asp:DropDownList>
        </td>
        <td width="0">
          <asp:TextBox ID="txtBookID" Visible="false" Text='<%# 
            DataBinder.Eval(Container.DataItem,"title_id").ToString() %>' 
            runat="server">

          </asp:TextBox>
        </td>
      </tr>
      <tr><td height="1" colspan="4" bgcolor="#E17546"></td></tr>

    </itemtemplate>
    <footerTemplate>
      <tr>
        <td colspan="4" align="center">
          <asp:Button ID="btnSubmit" OnClick="btnSubmit_Click"

            Text="Update Record" runat="server">
          </asp:Button>
        </td>
      </tr>
      </table>

    </footerTemplate>
	</asp:Repeater>
</table>
</form>

This web form uses a Repeater control to display book information. We introduce four web controls for data interaction. ddlBookType DropDownList - displays book type, txtBookPrice TextBox - displays book price, ddlBookPublisher DropDownList - displays book publisher, and txtBookID TextBox - hidden book ID.

We need to pay attention to two DropDownLists: Table Titles stores book type directly. But, for the sake of update, we need unique types for displaying. Thus, we call a function getBookType() to get DataSource. Then, using another function getBookTypeIndex() to set selected index for this book. This function passes individual book type, and returns selected index. Since table Titles only stores publishers ID, we need to get publisher name from table Publishers, and set a selected index for the individual book. getBookPublisher() and getBookPublisherIndex() functions do this job.

Finally, we put a button control in the FooterTemplate to fire a button click event.

The page displayed on web looks like following


Figure 1

When you click button "Update Record", the following code gets each book record's data, and updates it.

DropDownList typeSelected = new DropDownList();
DropDownList publisherSelected = new DropDownList();
TextBox priceText = new TextBox();
TextBox bookIdText = new TextBox(); 
   
string typeBook = null;
string publisherBook = null;
string priceBook = null;
string idBook = null;
   
double priceOfBook = 0.00;
   
foreach(RepeaterItem dataItem in repeaterBooks.Items)
{
   typeSelected = (DropDownList)dataItem.FindControl("ddlBookType");
   publisherSelected = (DropDownList)dataItem.FindControl("ddlBookPublisher");
   priceText = (TextBox)dataItem.FindControl("txtBookPrice");
   bookIdText = (TextBox)dataItem.FindControl("txtBookId");

   typeBook = typeSelected.SelectedItem.Value.ToString().Trim(); 
   publisherBook = publisherSelected.SelectedItem.Value.ToString().Trim();
   priceBook = priceText.Text.ToString().Trim();
   idBook =  bookIdText.Text.ToString().Trim();

   if (priceBook.Length == 0)
      priceOfBook = 0.00;
   else
      priceOfBook = Convert.ToDouble(priceBook);

   pubDB updateBookRecord = new pubDB();

   updateBookRecord.updateBookInformation(idBook,typeBook,priceOfBook,publisherBook);
}

Here, we use foreach loop to go through Repeater control items, get each book record data with its book ID, then update it.

I use application logic layer to process all data. Presentation page only creates instances. The application logic layer includes all methods to process data. I attach this file at the end of this article.

If you want to use DataList or DataGrid control instead, you need to alter two parts of code.

  1. In web form, use DataList or DataGrid replace Repeater. For DataList, it is really simple, just replace Repeater with DataList if you don't want to change its ID. For DataGrid, you need to do a little bit more.
  2. In btnSubmit_Click event, change one sentence foreach(RepeaterItem dataItem in repeaterBooks.Items) to foreach(DataListItem dataItem in repeaterBooks.Items) for DataList or to foreach(DataGridItem dataItem in repeaterBooks.Items) for DataGrid.

How to make this sample work? You need to follow these steps:

  1. Create a folder called something like multipleUpdate somewhere on your server, create an application on IIS upon this folder;
  2. Create a subfolder called bin;
  3. Copy web.config, pubData.cs, and updaterecord.aspx to folder multipleUpdate;
  4. Switch your command directory to multipleUpdate folder, run this command line:
    csc /t:library /out:bin\multipleUpdate.dll /r:System.dll /r:System.Web.dll /r:System.Data.dll /r:System.Xml.dll pubData.cs

That's all! Now, I put all files here.

Download

You can download a zip file containing all the files related to this article below.



About the Author

From ASP101

Articles originally posted on ASP101.com

Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Hybrid cloud platforms need to think in terms of sweet spots when it comes to application platform interface (API) integration. Cloud Velocity has taken a unique approach to tight integration with the API sweet spot; enough to support the agility of physical and virtual apps, including multi-tier environments and databases, while reducing capital and operating costs. Read this case study to learn how a global-level Fortune 1000 company was able to deploy an entire 6+ TB Oracle eCommerce stack in Amazon Web …

  • If you need new tools and tricks to make your meetings profitable and productive, then 5 Tips in 5 Minutes: A Quick Guide for More Profitable Sales Meetings is for you. Timely, practical tips that you can incorporate in just seconds will save you literally hours in travel and meeting time, not to mention help you to focus on what your sales prospects really want to know and how you can meet their needs. Get 5in5: A Quick Guide for More Profitable Sales Meetings and start building your sales the smarter, faster …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds