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

Rohatash Kumar
12y
19k
0
0

Article

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]

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

select * from employeetable

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

ALTER TABLE dbo.EmployeeTable 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 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">

</head>

<body>

<div>

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

</asp:GridView>

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

height: 133px; width: 187px">

</asp:GridView>

<br />

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

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

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

onclick="Button2_Click" />

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