Bulk Insert ,Update and Delete with ASP.Net ListView control

ASP.Net Listview is a Template Driven Grid control from Microsoft. It combines the features of DataGrid, GridView and Repeater. There is lot of web resources available about ListView. I had a scenario where the grid control has to act like an Excel control. Excel allows us to enter data in each of the cell. Then a submit button can be used to perform the Update or Delete operation on the Bulk.


At first I thought of doing this so easily. But when started doing that I felt the pain. I started with a sample project "ListViewDemo". Use the "DemoList" table from the following script.

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DemoList](     [DepartmentID] [smallint] IDENTITY(1,1) NOT NULL,     [Name] [dbo].[Name] NOT NULL,     [GroupName] [dbo].[Name] NOT NULL,     [ModifiedDate] [datetime] NOT NULL, CONSTRAINT [PK_DemoList] PRIMARY KEY CLUSTERED (     [DepartmentID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]

 
Once the DB is ready I tried adding a LINQ Data Context control to my web application. I named that "AdventureWorks.dbml" since I used that database for testing purpose. Now browse the server explorer and drag and drop the table into the DBML layer.

The Web.Config file will now have the ConnectionStrings updated due to LINQ DataContext. The User's of this demo can customize this according to their server settings.

<connectionStrings>         <add name="AdventureWorksConnectionString"connectionString="Data Source=MAXSTEEL\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=True"providerName="System.Data.SqlClient"/>     </connectionStrings>

 
Now let's start coding the ListView control. Make use of the "Defualt.aspx" page available. Place the Listview control and LinqDataSource control from the DataControls section in the toolbox.

The ListView control as is said previously is a templatedriven control. Following are the templates that one may need with the ListView Control.

ItemTemplate AlternatingItemTemplate EmptyDataTemplate InsertItemTemplate LayoutTemplate EditItemTemplate SelectedItemTemplate

Of these ItemTemplate, EditItemTemplate and Layout templates are very important. If we enable the paging options in the ListView it creates a DataPager control inside the layout template. The remaining are optional and used only when required. I got into problem while preparing this demo since I missed the EditItemTemplate (this template is responsible to take the values from controls and mapping them to the datasource). So make sure you don't miss them.
The LinqDataSource objects will point to the AdventureWorks dbml that we have created. The listview control will have the datasource id pointing to the LinqDataSurce and their templates are defined with their controls as follows.

<div>   <asp:ListView ID="ListView1" runat="server"DataKeyNames="DepartmentID" DataSourceID="LinqDataSource1"InsertItemPosition="FirstItem"> <ItemTemplate> <tr style="background-color: #E0FFFF;color: #333333;"> <td> <asp:CheckBox ID="CheckBox1" runat="server" /> </td> <td> <asp:Label ID="DepartmentIDLabel1" runat="server" Text='<%# Eval("DepartmentID") %>' /> </td> <td> <asp:TextBox ID="NameTextBox" runat="server" Text='<%# Bind("Name") %>' /> </td> <td> <asp:TextBox ID="GroupNameTextBox" runat="server" Text='<%# Bind("GroupName") %>' /> </td> <td> <asp:TextBox ID="ModifiedDateTextBox" runat="server" Text='<%# Bind("ModifiedDate") %>' /> </td> </tr> </ItemTemplate>   <EmptyDataTemplate> <table runat="server" style="background-color: #FFFFFF;border-collapse:collapse;border-color: #999999;border-style:none;border-width:1px;"> <tr> <td> No data was returned.</td> </tr> </table> </EmptyDataTemplate> <InsertItemTemplate> <tr style=""> <td> <asp:Button ID="InsertButton" runat="server"CommandName="Insert" Text="Insert" /> <asp:Button ID="CancelButton" runat="server"CommandName="Cancel" Text="Clear" /> </td> <td> &nbsp;</td> <td> <asp:TextBox ID="NameTextBox" runat="server" Text='<%# Bind("Name") %>' /> </td> <td> <asp:TextBox ID="GroupNameTextBox" runat="server" Text='<%# Bind("GroupName") %>' /> </td> <td> <asp:TextBox ID="ModifiedDateTextBox" runat="server" Text='<%# Bind("ModifiedDate") %>' /> </td> </tr> </InsertItemTemplate> <LayoutTemplate> <table runat="server"> <tr runat="server"> <td runat="server"> <table ID="itemPlaceholderContainer" runat="server"border="1" style="background-color: #FFFFFF;border-collapse:collapse;border-color: #999999;border-style:none;border-width:1px;font-family: Verdana, Arial, Helvetica, sans-serif;"> <tr runat="server" style="background-color: #E0FFFF;color:#333333;"> <th runat="server"> </th> <th runat="server"> DepartmentID</th> <th runat="server"> Name</th> <th runat="server"> GroupName</th> <th runat="server"> ModifiedDate</th> </tr> <tr ID="itemPlaceholder" runat="server"> </tr> </table> </td> </tr> <tr runat="server"> <td runat="server" style="text-align: center;background-color: #5D7B9D;font-family: Verdana, Arial, Helvetica, sans-serif;color:#FFFFFF"> <asp:DataPager ID="DataPager1" runat="server"> <Fields> <asp:NextPreviousPagerField ButtonType="Button"ShowFirstPageButton="True" ShowNextPageButton="False" ShowPreviousPageButton="False"/> <asp:NumericPagerField /> <asp:NextPreviousPagerField ButtonType="Button"ShowLastPageButton="True" ShowNextPageButton="False" ShowPreviousPageButton="False"/> </Fields> </asp:DataPager> </td> </tr> </table> </LayoutTemplate> <EditItemTemplate> <tr style="background-color: #999999;"> <td> <asp:Button ID="UpdateButton" runat="server"CommandName="Update" Text="Update" /> <asp:Button ID="CancelButton" runat="server"CommandName="Cancel" Text="Cancel" /> </td> <td> <asp:Label ID="DepartmentIDLabel1" runat="server" Text='<%# Eval("DepartmentID") %>' /> </td> <td> <asp:TextBox ID="NameTextBox" runat="server" Text='<%# Bind("Name") %>' /> </td> <td> <asp:TextBox ID="GroupNameTextBox" runat="server" Text='<%# Bind("GroupName") %>' /> </td> <td> <asp:TextBox ID="ModifiedDateTextBox" runat="server" Text='<%# Bind("ModifiedDate") %>' /> </td> </tr> </EditItemTemplate> <SelectedItemTemplate> <tr style="background-color: #E2DED6;font-weight:bold;color: #333333;"> <td> <asp:Button ID="DeleteButton" runat="server"CommandName="Delete" Text="Delete" /> <asp:Button ID="EditButton" runat="server"CommandName="Edit" Text="Edit" /> </td> <td> <asp:Label ID="DepartmentIDLabel" runat="server" Text='<%# Eval("DepartmentID") %>' /> </td> <td> <asp:Label ID="NameLabel" runat="server" Text='<%# Eval("Name") %>' /> </td> <td> <asp:Label ID="GroupNameLabel" runat="server" Text='<%# Eval("GroupName") %>' /> </td> <td> <asp:Label ID="ModifiedDateLabel" runat="server" Text='<%# Eval("ModifiedDate") %>' /> </td> </tr> </SelectedItemTemplate> </asp:ListView> <asp:LinqDataSource ID="LinqDataSource1" runat="server" ContextTypeName="ListViewDemo.AdventureWorksDataContext"EnableDelete="True" EnableInsert="True" EnableUpdate="True"TableName="DemoLists"> </asp:LinqDataSource>   <asp:Button ID="cmdUpdate" runat="server"onclick="Update_Click" Text="Update Records" />   <asp:Button ID="cmdDelete" runat="server"onclick="Delete_Click" Text="Delete Records" />   </div>

 
The output screen will look like this.

The users can enjoy editing the data directly as excel files and can select multiple records and delete them. You can download the demo here.
So enjoy using this wonderful Grid control and let me know your thoughts on this.