检查SQL表中是否存在值。

I have produced a small program that uses a database created in MS Access. To learn something new, I decided to improve it by migrating the DB to SQL Server.

我已经编写了一个小程序，它使用在MS Access中创建的数据库。为了学习新的东西，我决定通过将DB迁移到SQL Server来改进它。

While working with Access DB in C#, I have used this code to check if the value exists in particular column of particular table:

在使用c#访问DB时，我使用此代码来检查该值是否存在于特定表的特定列中:

//if the scanned tag already exists in the Student table...
var foundID = autoRegDataSet.Student.Select("TagID = '" + tagNo + "'");
if (foundID.Length != 0)
//do something

After changing to SQL Server, this piece of code returns "null" result, despite the fact, that the value I'm looking for DOES exists in Student table (after execution, value 4820427 should be assigned to foundID)

更改到SQL Server后，这段代码返回“null”结果，尽管事实是，我正在查找的值确实存在于Student表中(在执行后，值4820427应该分配给foundID)。

I'm totally new to SQL Server (and still quite new to programming). What is the proper way to check if the value exists in the table?

对于SQL Server来说，我是全新的(对编程来说还是很陌生的)。检查表中是否存在值的正确方法是什么?

2 个解决方案

#1

There's a lot of unclear things in the question, but I suspect the biggest question here is whether TagID is a numeric type vs a string type. But there's also the big question of why it is going into a data-set.

在这个问题中有很多不清楚的东西，但是我怀疑这里最大的问题是TagID是否是一个数字类型和字符串类型。但还有一个大问题，为什么它会进入一个数据集。

But; getting back to the actual question - checking if a value exists; I would do something like (using dapper to make correct parameterization simple):

但;回到实际的问题——检查是否存在一个值;我将做一些类似的事情(使用dapper以使正确的参数化简单):

// where tagNo is a local variable...
int? id = connection.Query<int?>(
    "select Id from Student where TagID=@tagNo", new { tagNo }
).FirstOrDefault();
if(id == null) {
    // doesn't exist
} else {
    // exists, plus you now know the id - it is: id.Value
}

But ultimately, if your original code worked in a DataSet, it should continue working - if it isn't, I suspect you have simply messed up the schema (maybe a column that was string is now int or similar).

但是，最终，如果您的原始代码在数据集中工作，它应该继续工作——如果不是，我怀疑您只是简单地打乱了模式(可能是一个字符串现在是int或类似的)。

#2

How did you convert your Access DB to MSSQL ?

如何将访问数据库转换为MSSQL ?

CHeck what is the dataType of TagID, it might be integer,
then it will be useful to drop the '' quotes.

检查TagID的数据类型，它可能是整数，然后删除“引号”将会很有用。

object foundID = autoRegDataSet.Student.Select("TagID = " + tagNo);

对象foundID = autoRegDataSet.Student。选择(“TagID =”+ tagNo);

Cheers.

欢呼。

#1