How to remove duplicate value from dataset using c# code.

Posted by Jitendrasoft09 under C# on 4/11/2013 | Points: 10 | Views : 4589 | Status : [Member] [MVP] | Replies : 6
Hi, we having the duplicate value in dataset and need to delete the duplicate value from dataset and fatched to another gridview.

Jitendra Kumar
If my post helps you, plz mark as an answer.



Responses

Posted by: Sandeepmhatre on: 4/11/2013 [Member] Starter | Points: 25

Up
0
Down
use hash table to store a Row id or unique column.

while adding data to hash table, you check wheather value exists or not.




Sandeep M,
Software Developer
Follow me on :
http://sandeepmhatre.blogspot.in

Jitendrasoft09, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Satyapriyanayak on: 4/11/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
CODE:

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
RemoveDuplicatesRecords(GetData());
}
}

private DataTable RemoveDuplicatesRecords(DataTable dt)
{
//Returns just 5 unique rows
var UniqueRows = dt.AsEnumerable().Distinct(DataRowComparer.Default);
DataTable dt2 = UniqueRows.CopyToDataTable();
return dt2;
}

private static DataTable GetData()
{
//Assume this returns all rows although there are just 5 distinct rows.
SqlDataReader reader = null;
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnStr2"].ConnectionString;
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "SELECT col1,col2 FROM tableName ORDER BY col1";
cmd.Connection = conn;
conn.Open();
reader = cmd.ExecuteReader();
dt.Load(reader);
conn.Close();
}
}
return dt;
}



http://satindersinght.blogspot.in/2013/02/remove-duplicate-rows-from-datatable-c.html
http://www.dotnetspark.com/kb/94-remove-duplicate-rows-value-from-datatable.aspx
http://www.codeproject.com/Articles/540859/C-sharp-Removing-Duplicate-Records-From-DataTable

If this post helps you mark it as answer
Thanks

Jitendrasoft09, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: manoranjanguptaoutlook-19221 on: 4/11/2013 [Member] Starter | Points: 25

Up
0
Down
Hello
First of all create table
1>create table tblmanu(Id int, Name varchar(50));
===
2>insert into tblmanu values(1,'Manu');
2>insert into tblmanu values(2,'Jhon');
3>insert into tblmanu values(3,'Manu');
==========
select * from tblmanu;

=======
find the number of duplicate data::::
select name, count(*) Totalcount from tblmanu group by name having count(*) >1 order by count(*) desc;
==================
delete from tblmanu where id not in(select max(id) from tblmanu group by name);
duplicate data will delete
===============
select * from tblmanu;

Manoranjan Gupta Manu
9873306227

Jitendrasoft09, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: manoranjanguptaoutlook-19221 on: 4/11/2013 [Member] Starter | Points: 25

Up
0
Down
Hello
First of all create table
1>create table tblmanu(Id int, Name varchar(50));
===
2>insert into tblmanu values(1,'Manu');
2>insert into tblmanu values(2,'Jhon');
3>insert into tblmanu values(3,'Manu');
==========
select * from tblmanu;

=======
find the number of duplicate data::::
select name, count(*) Totalcount from tblmanu group by name having count(*) >1 order by count(*) desc;
==================
delete from tblmanu where id not in(select max(id) from tblmanu group by name);
duplicate data will delete
===============
select * from tblmanu;

Manoranjan Gupta Manu
9873306227

Jitendrasoft09, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Priyagoel_24 on: 7/26/2013 [Member] Starter | Points: 25

Up
0
Down
hello friend

i send u my code in asp.net. please seen it.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Configuration;
public partial class Default3 : System.Web.UI.Page
{
SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["invent"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{



string FileName = FileUpload1.PostedFile.FileName;
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
//string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
string FilePath =Server.MapPath("~") + "\\ExcelFile\\" + FileName;
// string filename = Guid.NewGuid().ToString().Substring(0, 10);
//filename = filename + Extension;
//FilePath = Path.Combine(FilePath, filename);
FileUpload1.SaveAs(FilePath);
DataTable dt1=new DataTable();
OleDbConnection conn =new OleDbConnection("Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + FilePath + "; Jet OLEDB:Engine Type=5;Extended Properties=\"Excel 12.0;\"");
conn.Open();
OleDbDataAdapter ad = new OleDbDataAdapter("select * from [Sheet1$]", conn);
ad.Fill(dt1);
try
{
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
for (int i = 0; i < dt1.Rows.Count; i++)
{
string insert = "insert into invent(Name,days,hdays)values('" + dt1.Rows[i]["Name"].ToString() + "'," + Convert.ToInt32(dt1.Rows[i]["Days"]) + "," + Convert.ToInt32(dt1.Rows[i]["HDays"]) + ")";
SqlCommand ad2 = new SqlCommand(insert, cn);
ad2.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
}
string conStr = "";


switch (Extension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
DataSet ds = new DataSet();
string sheetname = "";
conStr = String.Format(conStr, FilePath, "no");

OleDbConnection connExcel = new OleDbConnection();

connExcel.ConnectionString = conStr;
bool success = false;
#region get sheetname
try
{

OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
cmdExcel.Connection = connExcel;
connExcel.Open();

DataTable dt = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
int i = 0;
foreach (DataRow row1 in dt.Rows)
{

string excelsheetname = row1["invent"].ToString();

sheetname = "[" + excelsheetname + "]";
OleDbCommand ocmd = new OleDbCommand("select * from invent ");
ocmd.Connection = connExcel;

OleDbDataAdapter odaadmin = new OleDbDataAdapter();
odaadmin.SelectCommand = ocmd;

odaadmin.Fill(ds, sheetname);
}

connExcel.Close();
}
catch (Exception ex)
{

}
#endregion get sheetname

}
else
{

Response.Write("<SCRIPT LANGUAGE='javascript'>alert('Kindly Select File'); </script>");
return;

}

}




}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Configuration;
public partial class Default3 : System.Web.UI.Page
{
SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["invent"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{



string FileName = FileUpload1.PostedFile.FileName;
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
//string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
string FilePath =Server.MapPath("~") + "\\ExcelFile\\" + FileName;
// string filename = Guid.NewGuid().ToString().Substring(0, 10);
//filename = filename + Extension;
//FilePath = Path.Combine(FilePath, filename);
FileUpload1.SaveAs(FilePath);
DataTable dt1=new DataTable();
OleDbConnection conn =new OleDbConnection("Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + FilePath + "; Jet OLEDB:Engine Type=5;Extended Properties=\"Excel 12.0;\"");
conn.Open();
OleDbDataAdapter ad = new OleDbDataAdapter("select * from [Sheet1$]", conn);
ad.Fill(dt1);
try
{
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
for (int i = 0; i < dt1.Rows.Count; i++)
{
string insert = "insert into invent(Name,days,hdays)values('" + dt1.Rows[i]["Name"].ToString() + "'," + Convert.ToInt32(dt1.Rows[i]["Days"]) + "," + Convert.ToInt32(dt1.Rows[i]["HDays"]) + ")";
SqlCommand ad

Jitendrasoft09, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Priyagoel_24 on: 7/26/2013 [Member] Starter | Points: 25

Up
0
Down
prevent duplicate values in sql database and asp.net C#

Jitendrasoft09, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response