Remove Duplicate Rows From a SQL Server Table in ASP.NET


This article shows how to delete duplicate records and keep one record for each unique row. Usually we use a primary key or unique key to prevent insertion of duplicate rows in SQL Server. But if we don't use them, then duplicate rows can be entered by the user. After inserting duplicate rows into a table, it becomes a major issue to delete those duplicate rows. So this topic will help us to delete those duplicate rows from the table in ASP.NET.

Creating Table in SQL Server Database

Now create a table named EmployeeTable. This table doesn't have a primary key (identity) column. The table looks as below.

CREATE TABLE [dbo].EmployeeTable

(

[EMPLOYee_Name] [varchar](100) NOT NULL,

[EMPLOYEE_Address] [varchar](50) NOT NULL

) ON [PRIMARY]

go

Now insert duplicate rows data into the table. After that use a select statement on the table.

INSERT INTO EmployeeTable VALUES('Manoj','Delhi')

INSERT INTO EmployeeTable VALUES('Rohatash','Agra')

INSERT INTO EmployeeTable VALUES('Manoj','Delhi')

INSERT INTO EmployeeTable VALUES('MicalGray','johannesburg')

INSERT INTO EmployeeTable VALUES('Manoj','Delhi')

INSERT INTO EmployeeTable VALUES('Rohatash','agra')

go

select * from employeetable

image1.gif

After that we add a column named Employee_ID with identity property.

ALTER TABLE dbo.EmployeeTable ADD Employee_ID INT IDENTITY(1,1)

image2.gif

First you have to create a web site.

  • Go to Visual Studio 2010
  • New-> Select a website application
  • Click OK

img5.gif

Now add a new page to the website.

  • Go to the Solution Explorer
  • Right Click on the Project name
  • Select add new item
  • Add new web page and give it a name
  • Click OK

img6.gif

Now drag and drop two GridView and three Button controls onto the form. One is used to show the data with duplicate rows, the second one is used to show duplicate rows. Let's take a look at a practical example.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DeleteDuplicaterows.aspx.cs"

    Inherits="DeleteDuplicaterows" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<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">

        </asp:GridView>

        </asp:GridView>

        <asp:GridView ID="GridView2" runat="server" Style="top: 17px; left: 219px; position: absolute;

            height: 133px; width: 187px">

        </asp:GridView>

        <br />

        &nbsp;<asp:Button ID="Button1" runat="server" Text="Show data with DuplicateRows"

            Width="175px" onclick="Button1_Click" />

        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

        <asp:Button ID="Button2" runat="server" Text="ShowDuplicateRows"

            onclick="Button2_Click" />

        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

        <asp:Button ID="Button3" runat="server" Text="DeleteDuplecaterow"

            onclick="Button3_Click" />

    </div>

    </form>

</body>

</html>

 

Now add the following namespaces.

using System.Data.SqlClient;

using System.Data;

 

Now write the connection string to connect to the database.

 

string strConnection = "Data Source=.; uid=sa; pwd=wintellect;database=registration;";

 

Now double-click on the first Button control and write the following code for binding the data with the first GridView.

 

protected void Button1_Click(object sender, EventArgs e)

{

    SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=Master;User ID=sa;Password=wintellect");

    SqlCommand command = new SqlCommand("SELECT Employee_Name,Employee_Address from [EmployeeTable]", connection);

    SqlDataAdapter dpt = new SqlDataAdapter(command);

    DataTable dt = new DataTable();

    dpt.Fill(dt);

    GridView1.DataSource = dt;

    GridView1.DataBind();

}


Now double-click on the Second Button control and write the following code to show duplicate rows data in the table with the second GridView.

 

protected void Button2_Click(object sender, EventArgs e)

{

    SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=Master;User ID=sa;Password=wintellect");

    SqlCommand command = new SqlCommand("SELECT * FROM dbo.EmployeeTable WHERE Employee_ID NOT IN (SELECT MIN(Employee_ID) FROM dbo.EmployeeTable GROUP BY Employee_Name,Employee_Address)", connection);

    SqlDataAdapter daimages = new SqlDataAdapter(command);

    DataTable dt = new DataTable();

    daimages.Fill(dt);

    GridView2.DataSource = dt;

    GridView2.DataBind();

}

 

Now double-click on the third Button control and write the following code to show data without duplicate rows in the table with the third GridView.

 

protected void Button3_Click(object sender, EventArgs e)

{

    SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=Master;User ID=sa;Password=123");

    SqlCommand command = new SqlCommand("Delete FROM dbo.EmployeeTable WHERE Employee_ID NOT IN (SELECT MIN(Employee_ID) FROM dbo.EmployeeTable GROUP BY Employee_Name,Employee_Address)", connection);

    SqlDataAdapter daimages = new SqlDataAdapter(command);

    DataTable dt = new DataTable();

    daimages.Fill(dt);

}

 

In code-behind write the following code.

Code-behind

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.Data;

 

public partial class DeleteDuplicaterows : System.Web.UI.Page

{

  

    protected void Button1_Click(object sender, EventArgs e)

    {

        SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=Master;User ID=sa;Password=wintellect");

        SqlCommand command = new SqlCommand("SELECT Employee_Name,Employee_Address from [EmployeeTable]", connection);

        SqlDataAdapter dpt = new SqlDataAdapter(command);

        DataTable dt = new DataTable();

        dpt.Fill(dt);

        GridView1.DataSource = dt;

        GridView1.DataBind();

    }

    protected void Button2_Click(object sender, EventArgs e)

    {

        SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=Master;User ID=sa;Password=wintellect");

        SqlCommand command = new SqlCommand("SELECT * FROM dbo.EmployeeTable WHERE Employee_ID NOT IN (SELECT MIN(Employee_ID) FROM dbo.EmployeeTable GROUP BY Employee_Name,Employee_Address)", connection);

        SqlDataAdapter daimages = new SqlDataAdapter(command);

        DataTable dt = new DataTable();

        daimages.Fill(dt);

        GridView2.DataSource = dt;

        GridView2.DataBind();

    }

    protected void Button3_Click(object sender, EventArgs e)

    {

        SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=Master;User ID=sa;Password=123");

        SqlCommand command = new SqlCommand("Delete FROM dbo.EmployeeTable WHERE Employee_ID NOT IN (SELECT MIN(Employee_ID) FROM dbo.EmployeeTable GROUP BY Employee_Name,Employee_Address)", connection);

        SqlDataAdapter daimages = new SqlDataAdapter(command);

        DataTable dt = new DataTable();

        daimages.Fill(dt);       

    }

}

Now run the application and test it.

image3.gif

Now click on the Show data with DuplicateRows Button to show the data with duplicate rows.

image4.gif

Now click on the Button ShowDuplicateRows to show only duplicate rows of the table.

image5.gif

Now stop debugging and run again the application. Now click on the Button Deleteduplicaterows to delete rows. Now select the first Button.

image6.gif

Some Helpful Resources


Similar Articles