如何防止SQL数据库中类似的[重复]条目？ [英] How to prevent similar [duplicate] entries in SQL database?
问题描述
现在，我有这个：
For now, I have this:
//CHECK IF COMPLETE DUPLICATE
query = "SELECT * FROM TBL_FLAVORS WHERE flavor_name = @flavor_name AND flavor_supplierid = @supplier_id";
using (SqlConnection con = new SqlConnection(connstring))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(query, con))
{
SqlDataReader read;
cmd.Parameters.AddWithValue("@flavor_name", txtFlavorName.Text.ToString());
cmd.Parameters.AddWithValue("@supplier_id", supplierid);
read = cmd.ExecuteReader();
if (read.Read())
{
MessageBox.Show("This flavor for this supplier already exists.");
return;
}
read.Close();
}
}
//CHECK IF ALMOST SIMILAR
//IF RECORD FOUND, RETURN
//OTHERWISE, PROCEED TO UPDATE/INSERT
query = "SELECT * FROM TBL_FLAVORS WHERE (flavor_name LIKE @flavor_name OR flavor_name LIKE @flavor_name2 OR FLAVOR_NAME LIKE @flavor_name3) AND flavor_supplierid = @supplier_id";
using (SqlConnection con = new SqlConnection(connstring))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(query, con))
{
string flavorname = "%" + txtFlavorName.Text.ToString()+ "%";
string flavorname2 = "" + txtFlavorName.Text.ToString() + "%";
string flavorname3 = "%" + txtFlavorName.Text.ToString() + "";
SqlDataReader read;
cmd.Parameters.AddWithValue("@flavor_name", flavorname);
cmd.Parameters.AddWithValue("@flavor_name2", flavorname2);
cmd.Parameters.AddWithValue("@flavor_name3", flavorname3);
cmd.Parameters.AddWithValue("@supplier_id", supplierid);
read = cmd.ExecuteReader();
if (read.Read())
{
DialogResult dialog = MessageBox.Show("It is possible that this flavor for this supplier already exists. Do you want to continue?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialog == DialogResult.No)
{
return;
}
}
read.Close();
}
}
这是最终项目。小组成员询问，如果已经有英寸并且用户输入了in，该怎么办。它应该被认为是重复的。或者说数据库中有一个巧克力，如果用户输入巧克力，它应该要求确认。
我尝试过：
如上所述的SQL通配符运算符。
This is for a final project. The panelist asked what if there was already an 'inch' and the user entered an 'innch.' It should be recognized as a duplicate. Or say there is a 'chocolate' in the database, it should ask for confirmation if the user enters 'chocolates'.
What I have tried:
SQL wildcard operators as stated above.
推荐答案
为什么不使用或为您从用户那里获取输入的文本框实现自动完成功能？
Why don't you use or implement Autocomplete feature for textbox from where you take inputs from user?
这是一个最终项目。小组成员询问，如果已经有英寸并且用户输入了innch，那么它应该被认为是重复的。或者说数据库中有一个巧克力，如果用户输入'巧克力'，它应该要求确认。
This is for a final project. The panelist asked what if there was already an 'inch' and the user entered an 'innch.' It should be recognized as a duplicate. Or say there is a 'chocolate' in the database, it should ask for confirmation if the user enters 'chocolates'.
当然亲爱的 - 没有人对Panelist向你提出的要求感兴趣。
很难在内部决定哪个相似，哪个不相似。如果你想自动化这个，你需要为你的程序提供一些AI。
好吧，在SQL Server中有一些选项可以让你接近你想要达到的但是准确的需要AI来决定，正如我已经说过的那样。
在应用程序中你确实有很大的力量来构建你自己的逻辑并获得更准确的结果但是让我们看看我们如何快速使用一些SQL Server函数来获得微笑。
SOUNDEX（Transact-SQL） [ ^ ]
DIFFERENCE（Transact-SQL） [ ^ ]
我建议你仔细阅读这两个文件。
例子（取自abov e。文档链接）：
It's hard to decide internally which is similar and which is not. If you want to automate this, you need to give some AI to your program.
Well, in SQL Server there are some options which can take you close to the what you wanted to achieve but accurate as it requires AI to decide, as I already said.
In application you definitely got much power to build your own logic and achieve more accurate result but let's see how we can quicky use some SQL Server functions to get something smilar.
SOUNDEX (Transact-SQL)[^]
DIFFERENCE (Transact-SQL)[^]
I would suggest to read these two documentation carefully.
Example (taken from above documenation link):
-- Using SOUNDEX
SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe');
结果：
Result:
----- -----
S530 S530
我们得到的结果相同。现在让我们使用 DIFFERENCE 来比较这些值。
We got same value as the result for both. Now let's use DIFFERENCE to compare these values.
-- Using DIFFERENCE
SELECT DIFFERENCE ('Smith','Smythe');
结果：
Result:
-----
4
好的，那么这里的4是什么意思？
Ok, so what's "4" means here?
返回的整数是SOUNDEX值中相同的字符数。返回值的范围为0到4：0表示弱相似或无相似，4表示强相似或相同值。
The integer returned is the number of characters in the SOUNDEX values that are the same. The return value ranges from 0 through 4: 0 indicates weak or no similarity, and 4 indicates strong similarity or the same values.
-- Using DIFFERENCE
SELECT DIFFERENCE ('Smith','Jones');
结果：
Result:
-----
2
使用输入 -
Using your inputs-
SELECT DIFFERENCE ('chocolate','chocolates'),DIFFERENCE ('inch','innch');
结果：
Result:
----- -----
4 4
希望，你知道我想解释的是什么。
如果有任何疑问，请告诉我。
Hope, you get an idea about what I was trying to explain.
Please let me know in case of any further queries.
你好
如果你想从数据库读取并获取数据，你必须使用：
SELECT DISTINCT（FieldName）FROM ....如果你想得到一个英寸，你的FieldName与英寸值相关。
或者如果你想要那个用户不要两次插入巧克力，你必须将FielName设置为Primery Key。
希望ti适合你
最好的问候
Hello
if you want to read from database and fetch data, you have to use:
SELECT DISTINCT (FieldName) FROM ... . if you want to get one "inch", your FieldName is related to "inch" Value.
or if you want that user Not to insert "chocolate" twice, you have to set the FielName as Primery Key.
hope ti works for you
best regards
这篇关于如何防止SQL数据库中类似的[重复]条目？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
