用于Visual Studio的TableAdapter的查询生成器的NOT IN语句 [英] NOT IN statement for Visual Studio's Query Builder for TableAdapter
问题描述
我想在查询生成器中使用Visual Studio 2008构建的TableAdapter来实现对TableAdapter的查询,类似于以下内容(MSSQL 2008):
I want to realize a query with the Visual Studio 2008 build in Query Builder for a TableAdapter similar like following (MSSQL 2008):
select * from [MyDB].[dbo].[MyView] where UNIQUE_ID NOT IN ('MyUniqueID1','MyUniqueID2')
如何在查询中设置过滤器,以便使用myTableAdapter.GetDataExceptUniqueIds(...)
函数进行调用?
How do I have to set the Filter in my query in order to call it with the myTableAdapter.GetDataExceptUniqueIds(...)
function?
我试图将过滤器设置为NOT IN (@ids)
并用
I tried to set the filter to NOT IN (@ids)
and called it with
string[] uniqueIds = ...;
myTableAdapter.GetDataExceptUniqueIds(String.Join("','", uniqueIds));
和
StringBuilder sb = new StringBuilder("'");
sb.Append(String.Join("','", uniqueIds));
sb.Append("'");
return myTableAdapter.GetDataExceptUniqueIds(sb.ToString());
但都失败了
推荐答案
您不能在SELECT ...中使用动态参数,而[NOT] IN列表中
You cannot use dynamic parameters in a SELECT ... WHERE [NOT] IN list
解决方法:
创建一个表函数,类似于以下函数:
Create a table function, similar to the following function:
CREATE FUNCTION dbo.ParmsToTable(@Parameters varchar(4000))
returns @result TABLE (Value varchar(100))
AS
begin
DECLARE @pTable table
(
Value varchar(100)
)
DECLARE @Value varchar(100), @Pos int
SET @Parameters = LTRIM(RTRIM(@Parameters))+ ','
SET @Pos = CHARINDEX(',', @Parameters, 1)
IF REPLACE(@Parameters, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))
IF @Value <> ''
BEGIN
INSERT INTO @pTable (Value) VALUES (@Value)
END
SET @Parameters = RIGHT(@Parameters, LEN(@Parameters) - @Pos)
SET @Pos = CHARINDEX(',', @Parameters, 1)
END
END
INSERT @result
SELECT value
FROM @pTable
RETURN
END
并按如下所示更改TableAdapter:
and change your TableAdapter as follows:
select * from [MyDB].[dbo].[MyView] where UNIQUE_ID NOT IN
(select value from dbo.ParmsToTable(@p) )
这篇关于用于Visual Studio的TableAdapter的查询生成器的NOT IN语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!