7 Replies - 2979 Views - Last Post: 07 June 2013 - 01:55 PM Rate Topic: -----

#1 optix212  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 30
  • View blog
  • Posts: 540
  • Joined: 10-October 09

Foreach loop to prevent duplicates in Database

Posted 07 June 2013 - 12:37 PM

I have a database set up that takes account names. Whenever an account is added, I want it to run a foreach loop through all of the rows in the database and see if a specified cell in each row is equal to the account trying to be added. This was working flawlessly a few minutes ago, and it seems as if the logic is written perfectly as well, but I can't seem to find the problem.. Each time I try to add an account with the same name as another, it still adds the account.. >.<

Here's my code:

                foreach (DataGridViewRow row in accountsDataGridView.Rows)
                {
                    if(row.Cells[1].ToString().Equals(Name.ToString()))
                    {
                        MessageBox.Show("Account already Exists, try using a different name");
                        break;
                    }
                    else if(row.Cells[1].ToString() != Name.ToString())
                    {
                        MessageBox.Show(Name.ToString());
                    var dir = new DirectoryInfo(myOptions.ScanSavePath + @"\" + Name);
                    //dir.Attributes = dir.Attributes & ~FileAttributes.ReadOnly;
                    dir.Create();

                    CreateAccounts(Name.ToString());

                    if (myOptions.ScanSavePath != null)
                    {
                        ListDirectory(treeView1, myOptions.ScanSavePath);
                    }
                    else
                    {
                        MessageBox.Show("Save Path null");
                    }
                    
                    }
                    break;
                }



Is This A Good Question/Topic? 0
  • +

Replies To: Foreach loop to prevent duplicates in Database

#2 Witchking  Icon User is offline

  • D.I.C Head

Reputation: 68
  • View blog
  • Posts: 189
  • Joined: 17-February 13

Re: Foreach loop to prevent duplicates in Database

Posted 07 June 2013 - 01:11 PM

I'm no expert, but it seems to me like you shouldn't use a foreach loop on a database.
else if(row.Cells[1].ToString() != Name.ToString())
    {
        MessageBox.Show(Name.ToString());
    var dir = new DirectoryInfo(myOptions.ScanSavePath + @"\" + Name);
    //dir.Attributes = dir.Attributes & ~FileAttributes.ReadOnly;
    dir.Create();

    CreateAccounts(Name.ToString());

You're calling the CreateAccounts method after only checking if the first row is not a match?
Also there's a break statement at the end of your loop so it only ever runs once?
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is online

  • the case is sol-ved
  • member icon

Reputation: 6376
  • View blog
  • Posts: 25,764
  • Joined: 12-December 12

Re: Foreach loop to prevent duplicates in Database

Posted 07 June 2013 - 01:33 PM

View PostWitchking, on 07 June 2013 - 08:11 PM, said:

You're calling the CreateAccounts method after only checking if the first row is not a match?
Also there's a break statement at the end of your loop so it only ever runs once?

I think the break at the bottom is still within the else-if clause, but it is difficult to tell because of the indenting.

Yes, the logic is flawed. I would set a boolean variable such as nameExists to false. Loop through your rows and only set this to true (and break) if the name is found. After the loop, you can examine this value using an if-statement.

Quote

I'm no expert, but it seems to me like you shouldn't use a foreach loop on a database.

Yes, in general, looping through the rows of a database-table isn't necessary (because of the WHERE clause) but looping through the rows of a DataGridView is quite common. (It could be avoided using LINQ, although some might consider this as still being a kind of loop ;).)

[I wonder if there is a method of the DGV such as Contains() that would avoid the loop? I haven't checked.]

This post has been edited by andrewsw: 07 June 2013 - 01:43 PM

Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • the case is sol-ved
  • member icon

Reputation: 6376
  • View blog
  • Posts: 25,764
  • Joined: 12-December 12

Re: Foreach loop to prevent duplicates in Database

Posted 07 June 2013 - 01:41 PM

Additional info: If, as you've indicated, the DGV is databound, then its DataSource (DataSet) could be queried. Otherwise, yes, it is necessary to loop through the rows.
Was This Post Helpful? 0
  • +
  • -

#5 Curtis Rutland  Icon User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5101
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: Foreach loop to prevent duplicates in Database

Posted 07 June 2013 - 01:44 PM

I'd say the logic is flawed no matter what. The point of a database is being able to query it. If all you're going to do is dump the table to memory and scan over it yourself, why not use XML, a CSV file, or maybe Excel?

Use the power of the database. First, you can set your column to be unique. That simply won't let you insert a duplicate, or update an existing row to be a duplicate.

Then, for good measure, write a query that will select count of all rows where name = @name, and pass the name you're looking for in. If the count is 0, insert. If not, it's a duplicate.
Was This Post Helpful? 1
  • +
  • -

#6 Witchking  Icon User is offline

  • D.I.C Head

Reputation: 68
  • View blog
  • Posts: 189
  • Joined: 17-February 13

Re: Foreach loop to prevent duplicates in Database

Posted 07 June 2013 - 01:45 PM

foreach (DataGridViewRow row in accountsDataGridView.Rows)
{
	if(row.Cells[1].ToString().Equals(Name.ToString()))
	{
	    MessageBox.Show("Account already Exists, try using a different name");
		break;
	}
	else if(row.Cells[1].ToString() != Name.ToString())
	{
		MessageBox.Show(Name.ToString());
		var dir = new DirectoryInfo(myOptions.ScanSavePath + @"\" + Name);
		//dir.Attributes = dir.Attributes & ~FileAttributes.ReadOnly;
		dir.Create();
		
		CreateAccounts(Name.ToString());
		
		if (myOptions.ScanSavePath != null)
		{
			ListDirectory(treeView1, myOptions.ScanSavePath);
		}
		else
		{
			MessageBox.Show("Save Path null");
		}
	}
	break;
}
Cleaned up the indenting. The break really is outside the else-if clause.
Was This Post Helpful? 1
  • +
  • -

#7 optix212  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 30
  • View blog
  • Posts: 540
  • Joined: 10-October 09

Re: Foreach loop to prevent duplicates in Database

Posted 07 June 2013 - 01:51 PM

It wasn't originally outside of the clause, but after an hour of head scratching I started moving everything around randomly in hopes that it would work properly... I'll look into LINQ and making custom queries.. For now, it doesn't really matter. I have a bunch of other features that are in need of programming, although I would like to see a good example of LINQ, Queries, etc.. and how they would relate to my program.

Thank you to everyone who helped!
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is online

  • the case is sol-ved
  • member icon

Reputation: 6376
  • View blog
  • Posts: 25,764
  • Joined: 12-December 12

Re: Foreach loop to prevent duplicates in Database

Posted 07 June 2013 - 01:55 PM

View PostCurtis Rutland, on 07 June 2013 - 08:44 PM, said:

Use the power of the database. First, you can set your column to be unique. That simply won't let you insert a duplicate, or update an existing row to be a duplicate.

Then, for good measure, write a query that will select count of all rows where name = @name, and pass the name you're looking for in. If the count is 0, insert. If not, it's a duplicate.

Thank you. This was my first instinct actually ;) but I got side-tracked.

View Postoptix212, on 07 June 2013 - 08:51 PM, said:

It wasn't originally outside of the clause, but after an hour of head scratching I started moving everything around randomly in hopes that it would work properly... I'll look into LINQ and making custom queries.. For now, it doesn't really matter. I have a bunch of other features that are in need of programming, although I would like to see a good example of LINQ, Queries, etc.. and how they would relate to my program.

Thank you to everyone who helped!

LINQ should be secondary to Curtis's advice about the sensible use of the database (setting a unique key, etc.).

The "moving everything around randomly in the hopes it would work properly" is really disturbuing :helpsmilie: :dontgetit: but good luck. Andy.

This post has been edited by andrewsw: 07 June 2013 - 01:57 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1