• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 597
  • Last Modified:

how to compare dataGridView items with sql server table column in C#

Hey Experts,
I have a dataGridView that contains one column that contains numbers, and it's possible to edit data, and i have a bottun that saves data.
Then i have a table in an sql server that have a column of numbers.
what i need is before i save (update) data from my dataGridView, i want to check if all numbers exists in the column that i have in my sql server table, if not, then show message else save.
how could i do that?

my dataGridView table witch only have one column is like this
col
---------
1
2
3

and my sql server table column is like this:
column
---------
1
2
4
7
3
8
6
9

0
delmandiyar
Asked:
delmandiyar
  • 5
  • 4
1 Solution
 
Gene_CypCommented:
What you asked, the only way for it to make sense is that those numbers are not saved on that same table you check on, right?
Anyhow, to do that, you can create a LIST of numbers as data extracted from that table and check your DGV data against that list.
private List<int> GetListOfNumbers()
{
List<int> tempList = new List<int>();
// method / code to get the data from that look up
tempList = "that data";
return tempList;
}
 
private bool DoAllNumbersExist(List<int> list, DataGridView dgv)
{
bool allfound = true;
for(int row = 0; row < dgv.rows.count; row++)
{
if(!list.Contains((int)dgv.rows[row].Cell[COLUMN_NAME].Value)
      allfound = false;
}
return allfound;
}
0
 
Gene_CypCommented:
If that bool method returns false, it means at least one number does not exist in your lookup table.
So, in your original code, when it returns false, show the Message you want to show, else if true Save.
 
 
0
 
delmandiyarAuthor Commented:
Yes right, and thanks, i will give it a try.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
delmandiyarAuthor Commented:
Hey Gene_Cyp

I'm sure that your solution will work, but i will be happy if you also tell me how to get data in my tempList? (// method / code to get the data from that look up).
I have really tried, but i can't really do it by my self. I have a dataset, table adapter, and the table.
0
 
delmandiyarAuthor Commented:
I fixet taht, but now i need to show(return) the number that not contains in the list, do you know how i can do that? :)
0
 
Gene_CypCommented:
Sorry been busy today.
This should do the trick for you:
 

private List<int> GetNumbersThatDontExist(List<int> list, DataGridView dgv)
{
 List<int> listOfDontExist = new List<int>();
for(int row = 0; row < dgv.rows.count; row++)
{
if(!list.Contains((int)dgv.rows[row].Cell[COLUMN_NAME].Value)
 {
int newNumber = (int)dgv.rows[row].Cell[COLUMN_NAME].Value;
listOfDontExist .Add(newNumber);
}
}
return listOfDontExist ;
}
private void ViewNumbersThatDontExist(List<int> list)
{
for(int i = 0; i < list.Count; i++)
{
// code to show ;) (your choice here) - MessageBox, or adding them to a List control, or TextArea  etc.
}
}
0
 
Gene_CypCommented:
Example:

private void ViewNumbersThatDontExist(List<int> list)
{
string myoutput = "";
for(int i = 0; i < list.Count; i++)
{
    myoutput += list[i].ToString() + ", ";
}
if(myoutput.length > 0)
           myoutput.RemoveAt(myoutput.length-1); // to remove the last comma
MessageBox.Show(myoutput, "Numbers that don't exist");
}
0
 
Gene_CypCommented:
BTW, the efficiency of the very first method in an earlier post further above can be improved by adding one small change to it:
 

private bool DoAllNumbersExist(List<int> list, DataGridView dgv)
{
bool allfound = true;
for(int row = 0; row < dgv.rows.count; row++)
{
if(!list.Contains((int)dgv.rows[row].Cell[COLUMN_NAME].Value)
{
     allfound = false;
break; // this ensures your search stops at the first number you found not to be in
}
}
return allfound;
}
0
 
delmandiyarAuthor Commented:
Hey Gene_Cyp

I did exact the same, i just instead save my not matched numbers in arraylist.

Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now