C# code to check one columns data from table has exist in other tables

Question

0.00/5 (No votes)

See more:

I really appreciate if some one help me I confused I want to check one record from table A to check if it exists in another table B.
I calculate how many data has entering in table B.

I tried many times but it is not working.

For Example:

   A                       B
------------------------------
John                     John
Joon                     Jorge
Jorge                    Joon
Elizabet                 Elizabet
Suzan                    Suzan
                         Elizabet
                         Joon
                         Suzan
                         John
                         Elizabet

Answer should be like this:

John = 2
Joon = 2
jorge = 1
Elizabet = 3
Suzan = 2

What I have tried:

C#

using (ImportInvoiceMasterForm.ApplicationSqlConnection2 = new SqlConnection(MainForm.ApplicationDataBase))
            {
                ImportInvoiceMasterForm.ApplicationSqlConnection2.Open();
                using (ImportInvoiceMasterForm.ApplicationSqlCommand1 = new SqlCommand("SELECT * FROM TBL_Stock_Item", ImportInvoiceMasterForm.ApplicationSqlConnection2))
                using (ImportInvoiceMasterForm.ApplicationSqlDataReader1 = ImportInvoiceMasterForm.ApplicationSqlCommand1.ExecuteReader())
                {
                    while (ImportInvoiceMasterForm.ApplicationSqlDataReader1.Read())
                    {
                        ImportInvoiceMasterForm.GetImportQuantity = 0;
                        #region Get Import Quantity
                        using (ImportInvoiceMasterForm.ApplicationSqlConnection1 = new SqlConnection(MainForm.ApplicationDataBase))
                        {
                            ImportInvoiceMasterForm.ApplicationSqlConnection1.Open();
                            using (ImportInvoiceMasterForm.ApplicationSqlCommand = new SqlCommand("SELECT * FROM TBL_Import_Items WHERE ImportItemName='" + ImportInvoiceMasterForm.ApplicationSqlDataReader1.GetString(1) + "'", ImportInvoiceMasterForm.ApplicationSqlConnection1))
                            using (ImportInvoiceMasterForm.ApplicationSqlDataReader = ImportInvoiceMasterForm.ApplicationSqlCommand.ExecuteReader())
                            {
                                while (ImportInvoiceMasterForm.ApplicationSqlDataReader.Read())
                                {
                                    GetImportQuantity += double.Parse(ImportInvoiceMasterForm.ApplicationSqlDataReader.GetValue(4).ToString());
                                }
                            }
                        }
                        #endregion Get Import Quantity
                    }
                }
            }

Posted 2-Jun-18 9:25am

Karam Ibrahim

Updated 7-Jun-18 12:36pm

Maciej Los

v3

Add a Solution

Comments

Eric Lynch 2-Jun-18 21:29pm

A more efficient means of achieving this same goal might be to use a JOIN and GROUP BY on the column(s) that are common between the two tables. Since you don't include many column names in your query, its difficult to provide a good example, but something like the following:

SELECT Categories.CategoryID, COUNT(Categories.CategoryID) AS RowCount FROM Products
LEFT OUTER JOIN Categories ON Categories.CategoryID=Products.CategoryID
GROUP BY Categories.CategoryID

If you don't want zero counts, you can use LEFT INNER JOIN instead.

Karam Ibrahim 7-Jun-18 18:44pm

Dear Eric, I want to use that statement about SELECT also I want to use RowCount later in C# code. where I can put it.

[no name] 3-Jun-18 13:13pm

Select from A where "name" is in B.
Then select count grouped by name from previous result.

Richard Deeming 4-Jun-18 8:50am

"SELECT * FROM TBL_Import_Items WHERE ImportItemName='" + ImportInvoiceMasterForm.ApplicationSqlDataReader1.GetString(1) + "'"

Don't do it like that!

If the users have any control over the contents of the stock table, your code will be vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

using (ImportInvoiceMasterForm.ApplicationSqlCommand = new SqlCommand("SELECT * FROM TBL_Import_Items WHERE ImportItemName=@ImportItemName", ImportInvoiceMasterForm.ApplicationSqlConnection1))
{
    ImportInvoiceMasterForm.ApplicationSqlCommand.Parameters.AddWithValue("@ImportItemName", ImportInvoiceMasterForm.ApplicationSqlDataReader1.GetString(1));
}

4 solutions

Solution 4

Thanks guys I solved my problem.

Posted 7-Jun-18 12:38pm

Karam Ibrahim

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2018-06-04T02:52:00

As Eric Lynch[^] already mentioned, you have to join tables and group by names.

SQL

SELECT b.Name, COUNT(b.Name) AS Count
FROM TableA AS a 
    JOIN TableB AS b ON a.Name = b.Name
GROUP BY b.Name

C#

DataTable dtA = new DataTable();
dtA.Columns.Add(new DataColumn("name", typeof(string)));
dtA.Rows.Add(new object[]{"John"});
dtA.Rows.Add(new object[]{"Joon"});
dtA.Rows.Add(new object[]{"Jorge"});
dtA.Rows.Add(new object[]{"Elizabet"});
dtA.Rows.Add(new object[]{"Suzan"});


DataTable dtB = new DataTable();
dtB.Columns.Add(new DataColumn("name", typeof(string)));
dtB.Rows.Add(new object[]{"John"});
dtB.Rows.Add(new object[]{"Jorge"});
dtB.Rows.Add(new object[]{"Joon"});
dtB.Rows.Add(new object[]{"Elizabet"});
dtB.Rows.Add(new object[]{"Suzan"});
dtB.Rows.Add(new object[]{"Elizabet"});
dtB.Rows.Add(new object[]{"Joon"});
dtB.Rows.Add(new object[]{"Suzan"});
dtB.Rows.Add(new object[]{"John"});
dtB.Rows.Add(new object[]{"Elizabet"});

//#standard version
var result = from a in dtA.AsEnumerable()
		join b in dtB.AsEnumerable() on a.Field<string>("name") equals b.Field<string>("name")
		group b by b.Field<string>("name") into names
		select new
		{
			Name = names.Key,
			Count = names.Count()
		};

//#lambda version
var result1 = dtA.AsEnumerable()
		.Join(dtB.AsEnumerable(),
			a => a.Field<string>("name"),
			b => b.Field<string>("name"),
			(a, b) => new {a, b})
		.GroupBy(x=>x.b.Field<string>("name"))
		.Select(grp=> new
		{
			Name = grp.Key,
			Count = grp.Count()
		});

//both methods return the same result:
//John 2 
//Joon 2 
//Jorge 1 
//Elizabet 3 
//Suzan 2

Note: there's few ways to join tables and therefore a result might be different. For details, please see: Visual Representation of SQL Joins[^]
At this moment i've used INNER JOIN, due to your sample data and expected result.

abishens · Accepted Answer · 2018-06-04T02:53:00

Solution 2

Just need to use joins that should solve your problem

Posted 4-Jun-18 2:53am

abishens

Patrice T · Accepted Answer · 2018-06-04T12:46:00

C#

SqlCommand("SELECT * FROM TBL_Import_Items WHERE ImportItemName='" + ImportInvoiceMasterForm.ApplicationSqlDataReader1.GetString(1) + "'", ImportInvoiceMasterForm.ApplicationSqlConnection1))

Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone. User input can range from unfortunate to malicious.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]

C# code to check one columns data from table has exist in other tables

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0