Identify Duplicate Rows From a SQL Server Table in C#

Rohatash Kumar
12y
22.7k
0
0

Article

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]

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')

select * from employeetab

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

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

First you have to create a web site.

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

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

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

</head>

<body>

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

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

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

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

onclick="Button2_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 [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.