Identify Duplicate Rows From a SQL Server Table in C#


This article shows how to identify duplicate records. 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 identify those duplicate rows. So this topic will help us to identify those duplicate rows from the table in ASP.NET

Creating Table in SQL Server Database
 
Now create a table named EmployeeTab. This table doesn't have a primary key (identity) column. The table looks as below.

CREATE TABLE [dbo].EmployeeTab

(

[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 EmployeeTab VALUES('Manoj','Delhi')

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

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

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

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

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

go

select * from employeetab 

img1.gif

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

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

img2.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 two 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>

    <script src="Scripts/jquery-1.4.1.min.js" type="text/javascript"></script>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <asp:GridView ID="GridView1" runat="server">

        </asp:GridView>

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

            height: 133px; width: 187px; right: 468px;">

        </asp:GridView>

        <br />

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

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

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&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;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

        </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 [EmployeeTab]", 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.EmployeeTab 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();

}


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 [EmployeeTab]", 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.EmployeeTab 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 run the application and test it.


img4.gif


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

img3.gif


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


 img7.gif


Some Helpful Resources

Up Next
    Ebook Download
    View all
    Learn
    View all