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 />
<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();
}