在VB.NET中检查数据库中是否存在值的最佳方法是什么？

I am familiar with connecting to databases in VB.NET to get data, but I have a slightly different task this time. In the past I have hit the database to get values from a table to use in my application (names, addresses, prices, etc.), but now I need to simply access the database to check and see if a value exists. I don't know the best way to go about this. Is there a good, efficient way to do this without actually getting any data from the database?

我熟悉在VB.NET中连接数据库来获取数据,但这次我的任务略有不同。在过去,我已经点击数据库从表中获取值以在我的应用程序中使用(名称,地址,价格等),但现在我只需要访问数据库来检查并查看是否存在值。我不知道最好的办法。有没有一个好的,有效的方法来实现这一点,而无需从数据库中获取任何数据?

To further clarify, I want to check and see if a certain code exists in the database when the user enters a value in a textbox (this is an ASP.NET web site project). If the code exists, I will inform the user via a popup box.

为了进一步说明,我想检查并查看当用户在文本框中输入值时数据库中是否存在某个代码(这是一个ASP.NET网站项目)。如果代码存在,我将通过弹出框通知用户。

EDIT:

If I create a stored procedure in SQL Server that returns true if the value exists or false if it doesn't (or 0 or 1), how can I use the return value in VB.NET?

如果我在SQL Server中创建存储过程,如果值存在则返回true,如果不存在(或0或1)则返回false,如何在VB.NET中使用返回值?

3 个解决方案

#1

If you know the Primary Key value or some other unique value of your table the fastest way I know to check the existence or not of a particular record is the following

如果您知道主键值或表的其他一些唯一值,我知道检查特定记录是否存在的最快方法如下

Dim cmdText = "IF EXISTS(SELECT 1 FROM yourTable WHERE idField = @value) " & _
              "SELECT 1 ELSE SELECT 0"

Using cnn = new SqlConnection(.....)
Using cmd = new SqlCommand(cmdText, cnn)
    cnn.Open()
    cmd.Parameters.Add("@value", SqlDbType.Int).Value = 9876
    Dim result = Convert.ToInt32(cmd.ExecuteScalar())
    Dim exists = IF result = 1, True, False
    ....
End Using
End Using

This approach is preferable to count the records that match your condition because the database engine is free to return immediately after checking if the condition has been matched instead of counting till the end of the table. But, of course, it is of the utmost importance that the field on which you execute the search is indexed.

这种方法比计算符合条件的记录更可取,因为数据库引擎在检查条件是否匹配后可以立即*返回,而不是计数直到表的末尾。但是,当然,执行搜索的字段已编入索引至关重要。

Some info on the EXISTS operator on MSDN

有关MSDN上EXISTS运算符的一些信息

#2

SELECT COUNT(*) FROM YourTableName WHERE CODE = @CODE

SELECT COUNT(*)FROM YourTableName WHERE CODE = @CODE

#3

I may be missing something here as it seems too easy! It sounds like all you want is to execute a simple SQL query along the lines of Select Field from Table where field = Value. The Command execution should return the number of rows selected so if Return is greater than 0 then the item exists.

我可能在这里遗漏了一些东西,因为它似乎太容易了!听起来你想要的就是沿着Table中Select Field的行执行一个简单的SQL查询,其中field = Value。 Command执行应返回所选行数,因此如果Return大于0则该项存在。

You feed the Value with the item entered in the textbox when the SQL Command is constructed at run time.

在运行时构造SQL命令时,使用在文本框中输入的项来提供值。

Apologies if I'm missing summit!

如果我错过峰会,请道歉!

VB.net KB on running stored procs from VB.net:-

从VB.net运行存储过程的VB.net KB :-

Running SPs from VB.net

从VB.net运行SP

#1