In this article I will explain how to remove (delete) duplicate rows (records) from DataTable using DataView in C# and VB.Net.
The idea is to convert the DataTable to DataView and then from DataView back to DataTable using the DataView ToTable method which has option to return distinct (unique) rows (records) of DataTable, thus ultimately it removes (deletes) duplicate rows (records).
In this article I will explain with help of ASP.Net GridView which will be initially populated using a DataTable with duplicate records but later the duplicate records will be removed (deleted).
HTML Markup
Here in the HTML Markup as said earlier I have a GridView and a Button which will display how the duplicate rows (records) are removed (deleted) from DataTable.
<asp:GridView ID="GridView1" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"
runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="Id" HeaderText="Id" ItemStyle-Width="30" />
<asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-Width="150" />
<asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="150" />
</Columns>
</asp:GridView>
<br />
<asp:Button ID="btnRemove" runat="server" Text="Remove Duplicates" OnClick="RemoveDuplicates" />
Namespaces
You will need to import the following namespace.
C#
VB.Net
Binding the GridView
In the page load event of ASP.Net page I am populating a DataTable with some dummy duplicate rows (records). Then the same DataTable is saved in ViewState variable and also bound to the GridView control. The DataTable is saved in ViewState so that same DataTable can be re-used to remove (delete) duplicate rows (records).
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id"), new DataColumn("Name"), new DataColumn("Country") });
dt.Rows.Add(1, "John Hammond", "United States");
dt.Rows.Add(1, "John Hammond", "United States");
dt.Rows.Add(2, "Mudassar Khan", "India");
dt.Rows.Add(2, "Mudassar Khan", "India");
dt.Rows.Add(3, "Suzanne Mathews", "France");
dt.Rows.Add(3, "Suzanne Mathews", "France");
dt.Rows.Add(3, "Suzanne Mathews", "France");
dt.Rows.Add(4, "Robert Schidner", "Russia");
dt.Rows.Add(4, "Robert Schidner", "Russia");
dt.Rows.Add(4, "Robert Schidner", "Russia");
dt.Rows.Add(4, "Robert Schidner", "Russia");
ViewState["dt"] = dt;
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Dim dt As New DataTable()
dt.Columns.AddRange(New DataColumn(2) {New DataColumn("Id"), New DataColumn("Name"), New DataColumn("Country")})
dt.Rows.Add(1, "John Hammond", "United States")
dt.Rows.Add(1, "John Hammond", "United States")
dt.Rows.Add(2, "Mudassar Khan", "India")
dt.Rows.Add(2, "Mudassar Khan", "India")
dt.Rows.Add(3, "Suzanne Mathews", "France")
dt.Rows.Add(3, "Suzanne Mathews", "France")
dt.Rows.Add(3, "Suzanne Mathews", "France")
dt.Rows.Add(4, "Robert Schidner", "Russia")
dt.Rows.Add(4, "Robert Schidner", "Russia")
dt.Rows.Add(4, "Robert Schidner", "Russia")
dt.Rows.Add(4, "Robert Schidner", "Russia")
ViewState("dt") = dt
GridView1.DataSource = dt
GridView1.DataBind()
End If
End Sub
Remove (Delete) Duplicate Rows (Records) from DataTable using DataView
On the click event handler of the Button, the DataTable is fetched from the ViewState and then the same DataTable is repopulated using the DataTable DefaultView ToTable method.
The ToTable method is a really helpful and easy to use method, where the first parameter is of Boolean type which when true means it will return distinct (unique) rows (records), while the next parameters are the name of the Columns whose data you want to be distinct (unique).
C#
protected void RemoveDuplicates(object sender, EventArgs e)
{
DataTable dt = (DataTable)ViewState["dt"];
dt = dt.DefaultView.ToTable(true, "Id", "Name", "Country");
GridView1.DataSource = dt;
GridView1.DataBind();
}
VB.Net
Protected Sub RemoveDuplicates(sender As Object, e As EventArgs)
Dim dt As DataTable = DirectCast(ViewState("dt"), DataTable)
dt = dt.DefaultView.ToTable(True, "Id", "Name", "Country")
GridView1.DataSource = dt
GridView1.DataBind()
End Sub
Demo
Downloads
