用于检查表中一列数据的C＃代码已存在于其他表中 [英] C# code to check one columns data from table has exist in other tables
问题描述
我真的很感激，如果有人帮助我，我很困惑，我想检查表A中的一条记录，检查它是否存在于另一张表B.
我计算表B中输入了多少数据。
我尝试了很多次但是没有用。
例如：
AB
------------------------- -----
John John
Joon Jorge
Jorge Joon
Elizabet Elizabet
Suzan Suzan
Elizabet
Joon
Suzan
John
Elizabet
答案应该是这样的：
John = 2
Joon = 2
jorge = 1
Elizabet = 3
Suzan = 2
我的尝试：
使用（ImportInvoiceMasterForm.ApplicationSqlConnection2 = 新的 SqlConnection（MainForm.ApplicationDataBase））
{
ImportInvoiceMasterForm.ApplicationSqlConnection2.Open（）;
使用（ImportInvoiceMasterForm.ApplicationSqlCommand1 = new SqlCommand（ SELECT * FROM TBL_Stock_Item，ImportInvoiceMasterForm.ApplicationSqlConnection2））
使用 （ImportInvoiceMasterForm.ApplicationSqlDataReader1 = ImportInvoiceMasterForm.ApplicationSqlCommand1.ExecuteReader（））
{
while （ImportInvoiceMasterForm.ApplicationSqlDataReader1.Read（））
{
ImportInvoiceMasterForm.GetImportQuantity = 0 ;
#region获取导入数量
使用（ImportInvoiceMasterForm.ApplicationSqlConnection1 = new SqlConnection（MainForm.ApplicationDataBase））
{
ImportInvoiceMasterForm.ApplicationSqlConnection1.Open（）;
使用（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获取导入数量
}
}
}
作为埃里克林奇 [ ^ 已经提到过，你必须按名称加入表和组。
SELECT b.Name，COUNT（b.Name） AS 计算
FROM TableA AS a
JOIN TableB AS b ON a.Name = b.Name
GROUP BY b .Name
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 = 来自 dtA.AsEnumerable（）
join b 在 dtB.AsEnumerable（）on a.Field< string>（ name）等于b.Field< string>（ name）
group b by b.Field< string>（ name） into name
选择 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 => xbField< string>（ name））
。选择（grp = > new
{
Name = grp.Key，
Count = grp.Count（）
}）;
// 两种方法都返回相同的结果：
< span class =code-comment> // John 2
// Joon 2
// Jorge 1
// Elizabet 3
// Suzan 2
注意：连接表的方法很少，因此结果可能会有所不同。有关详细信息，请参阅： SQL联接的可视化表示 [ ^ ]
由于您的样本数据和预期结果，此时我使用了INNER JOIN。
只需要使用连接应该解决你的问题
SqlCommand（ SELECT * FROM TBL_Import_Items WHERE ImportItemName =' + ImportInvoiceMasterForm.ApplicationSqlDataReader1.GetString（ 1 ）+ '，ImportInvoiceMasterForm.ApplicationSqlConnection1））
不是你问题的解决方案，但另一个问题你有。
永远不要通过concate构建SQL查询敲弦。迟早，您将使用用户输入执行此操作，这会打开一个名为SQL注入的漏洞，这对您的数据库很容易出错并且容易出错。用户输入的范围可以从不幸到恶意。
名称中的单引号和程序崩溃。如果用户输入像Brian O'Conner这样的名称可能会使您的应用程序崩溃，那么这是一个SQL注入漏洞，崩溃是最少的问题，恶意用户输入，并且它被提升为具有所有凭据的SQL命令。
SQL注入 - 维基百科 [ ^ ]
SQL注入 [ ^ ]
按示例进行SQL注入攻击 [ ^ ]
PHP：SQL注入 - 手册 [ ^ ]
SQL注入预防备忘单 - OWASP [ ^ ]
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:
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
}
}
}As Eric Lynch[^] already mentioned, you have to join tables and group by names.
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
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 usedINNER JOIN, due to your sample data and expected result.
Just need to use joins that should solve your problem
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＃代码已存在于其他表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
