c# - query in c# returns false after getting values from the db
问题描述
I need to return true if this query returns value, (and it has values in the db)
SELECT * from AllMembers a
join Cards c on c.IDMember = a.MemberID
where LEN(a.EmployeeNum) = 9
and LEFT(c.CardNumber,3) = '777'
and a.Email is not null
and a.LastUpdate > DATEADD(YEAR, -1 , GETDATE())
and c.CardStatus = 1
and c.AddedTime > DATEADD(YEAR, -1 , GETDATE())
or
LEN(a.EmployeeNum) = 9
and LEFT(c.CardNumber,4) = '2010'
and a.Email is not null
and a.LastUpdate > DATEADD(YEAR, -1 , GETDATE())
and c.CardStatus = 1
and c.AddedTime > DATEADD(YEAR, -1 , GETDATE())
but writing this value return false, why?
string Query= string.Format(@"use Knowledge4All
IF EXISTS (
SELECT ID FROM {0}..AllMembers
JOIN Cards where IDMember = @memberID
and (where LEN(EmployeeNum) = 9
and LEFT(CardNumber,3) = '777'
and Email is not null
and LastUpdate > DATEADD(YEAR, -1 , GETDATE())
and CardStatus = 1
and AddedTime > DATEADD(YEAR, -1 , GETDATE())))
select 1
OR
IF exists(
(LEN(EmployeeNum) = 9
and LEFT(CardNumber,4) = '2010'
and Email is not null
and LastUpdate > DATEADD(YEAR, -1 , GETDATE())
and CardStatus = 1
and AddedTime > DATEADD(YEAR, -1 , GETDATE()) )
SELECT 1", DbName);
Am I writing this wrong? what is the best why to rum this ?
解决方案
您可以编写如下查询。
IF EXISTS(SELECT 1 from AllMembers a
join Cards c on c.IDMember = a.MemberID
where LEN(a.EmployeeNum) = 9
and LEFT(c.CardNumber,3) = '777'
and a.Email is not null
and a.LastUpdate > DATEADD(YEAR, -1 , GETDATE())
and c.CardStatus = 1
and c.AddedTime > DATEADD(YEAR, -1 , GETDATE())
or
LEN(a.EmployeeNum) = 9
and LEFT(c.CardNumber,4) = '2010'
and a.Email is not null
and a.LastUpdate > DATEADD(YEAR, -1 , GETDATE())
and c.CardStatus = 1
and c.AddedTime > DATEADD(YEAR, -1 , GETDATE())
)
BEGIN
SELECT 1 as Status
END
ELSE
BEGIN
SELECT 0 AS Status
END
如果您使用ExecuteScalar()
,您将获得适当的值。
您查询中的其他观察结果
1- 不需要 USE 语句。仅当连接字符串设置为不同的默认目录时才需要。
2-您正在使用string.format
而无需更换任何东西。
但是写这个值返回false,为什么?
这可能是由于条件与您表中的任何记录都不匹配。直接在 SSMS 中运行查询并检查输出。
推荐阅读
- html - Html 表格在 Internet Explorer 上不起作用
- node.js - Firebase 消息:应用程序/网络超时(发出请求时出错:超过 10000 毫秒的超时)
- c++ - C++20 中的 CTAD 和指定初始值设定项
- javascript - Vue.js,更改数组项的顺序并在 DOM 中进行更改
- php - 文件上传不工作,如果文件没有在 25-30 秒内上传
- python - 如何使用 3D 点、线和曲面数据绘制体积(.geo 文件)
- ruby - 在 ruby cmd 中将 scss 转换为 css 以编写 sass 代码
- generics - 在颤振中通过 Function(T) 传递泛型类型
- javascript - 无法使用 getElemenById + FileReader API 从 Input 标签中读取同一个文件两次
- javascript - 如何在反应中使用 for 循环,以便我可以循环两个数组并在两个数组中返回第二个数组中的对象(如果两者都匹配)