C# .NET - how to avoid duplicate email id's in EMAIL ID column in datatable

Asked By deepi singh on 14-Feb-12 05:51 AM
i have a datable with 3 columns as GUID,FIRSTNAME,EMAIL ID........iam getting duplicate email id's in EMAIL ID column..how to get dsitinct email id's  in EMAIL ID  column in datatable.

example:
GUID  FIRSTNAME EMAIL ID
xyz001    ravi      ravi&gmail.com
abc001    raj       ravi&gmail.com

from above example iam getting dulpicate email id's in email id column in datatable..how to avoid duplicate email id's in email id column in datatable
Web Star replied to deepi singh on 14-Feb-12 06:00 AM
If you are getting all three column than that is unique GUID so it will return duplicate email id also if you don't want to duplicate email id than just select FirstName and EmailId with distinct.

in your sample the firstname also different than this is ok with result set if you are trying to get only one email id from that than how can decide which Guid and firstname will select from that table
Venkat K replied to deepi singh on 14-Feb-12 06:26 AM
First create a DataView on your datatable and pass the column names to check for duplicates as array in ToTable method of DataView.


DataView dView = new DataView(dtRemoveDuplicate);
string[] arrColumns = { "Email" };
dtRemoveDuplicate = dView.ToTable(true, arrColumns);

Thanks


dipa ahuja replied to deepi singh on 14-Feb-12 06:41 AM
ALTER PROCEDURE dbo.spWithReturnValue    
    (
    @Email varchar(50),
    @exists int=1 output
    )
    
AS
    /* SET NOCOUNT ON */    
 
     IF EXISTS (SELECT Email FROM users WHERE Email = @Email)  
     select @exists       
       
    ELSE
    set @exists=0
    select @exists        
RETURN
 
 
Use in .CS Code:
 
protected void btnCheck_Click(object sender, EventArgs e)
{
  string email = TextBox1.Text;
  int exist = 0;
  string ConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
  SqlConnection conn = new SqlConnection(ConnString);
 
  conn.Open();
  SqlCommand comm = new SqlCommand("spWithReturnValue", conn);
  comm.Parameters.AddWithValue("Email", email);
  comm.CommandType = CommandType.StoredProcedure;
 
  //returning the output
 
  exist = int.Parse(comm.ExecuteScalar().ToString());
  if (exist == 1)
  {
    LblResult.Text = "<font color='green'>Email Exist in table</font>";
  }
  else
  {
    LblResult.Text = "<font color='red'>Email Not Exist</font>";      
  }
}
 
Somesh Yadav replied to deepi singh on 14-Feb-12 06:56 AM
Hi,

here is a sample example for you.


I have a DataTable which I get by an upload of CSV document from the user and has columns, rows like that:

Email      Age      Team
x@x
.com    25       BarcelonaFC
y@y
.com    32       BesiktasJK
z@z
.com    18       Napoli
y@y
.com    19       Boca Juniors
x@x
.com    36       Internazionale

I need to filter that datatable before I insert it into the database. Email column should be unique. So I need to filter that datatable so that I get as a result eliminating 2 rows. I do not want to use LINQ, but if it is only solution, its ok.

Email      Age      Team
x@x
.com    25       BarcelonaFC
y@y
.com    32       BesiktasJK
z@z
.com    18       Napoli
You can create a HashSet<string> holding email addresses, then loop backwards through the table, add the email address for each row to the hashset, and, if it's already there (if Add returns false), remove the row.

You can try something like this....

var dt = new DataTable();
dt
= yourCurrentDataTable.DefaultView.ToTable(true, "Email", "Age", "Team");

You can use Dictionary for it:

First of all, you can create simple class:

class Data
{
 
public int Age;
 
public string Team;
}

After that you can insert your data to dictionary:

Dictionary<string, MyData> dic = new Dictionary<string, MyData>();
foreach(DataRow row in YourDataTable)
{
 
if(!dic.ContainsKey(row[.. email ..]))
 
{
    dic
.Add(..., ...);
 
}
}
Now your dictionary ready to go..

I hope it helps you.