c# - 我没有得到所有数据
问题描述
我正在尝试使用这样的存储过程从我的数据库中获取一些数据
[getAllRecordsForSalaryCalculation]
@year NCHAR(10),
@Bruger NCHAR(20)
AS
BEGIN
IF (@year = 2018)
BEGIN
SELECT SUM(Overtid1) AS overtid1Before
FROM timer
WHERE DateForQuery >= CONVERT(DATETIME, '2018-12-01')
AND DateForQuery <= CONVERT(DATETIME, '2019-04-30')
AND Bruger LIKE '%@Bruger%'
SELECT SUM(Overtid1) AS overtid1after
FROM timer
WHERE DateForQuery >= CONVERT(DATETIME, '2019-05-01')
AND DateForQuery <= CONVERT(DATETIME, '2019-11-30')
AND Bruger LIKE '%@Bruger%'
SELECT SUM(Overtid2) AS overtid2Before
FROM timer
WHERE DateForQuery >= CONVERT(DATETIME, '2018-12-01')
AND DateForQuery <= CONVERT(DATETIME, '2019-04-30')
AND Bruger LIKE '%@Bruger%'
SELECT SUM(Overtid2) AS overtid2after
FROM timer
WHERE DateForQuery >= CONVERT(DATETIME, '2019-05-01')
AND DateForQuery <= CONVERT(DATETIME, '2019-11-30')
AND Bruger LIKE '%@Bruger%'
SELECT SUM(Vagt) AS vagtBefore
FROM timer
WHERE DateForQuery >= CONVERT(DATETIME, '2018-12-01')
AND DateForQuery <= CONVERT(DATETIME, '2019-04-30')
AND Bruger LIKE '%@Bruger%'
SELECT SUM(Vagt) AS vagtafter
FROM timer
WHERE DateForQuery >= CONVERT(DATETIME, '2019-05-01')
AND DateForQuery <= CONVERT(DATETIME, '2019-11-30')
AND Bruger LIKE '%@Bruger%'
END
END
我的代码看起来像这样
con.Open();
SqlDataAdapter da = new SqlDataAdapter("getAllRecordsForSalaryCalculation", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.AddWithValue("@year", year);
da.SelectCommand.Parameters.AddWithValue("@Bruger", bruger);
da.Fill(ds);
con.Close();
但我只得到第一个选择并且它是空的。那里有数据,当我将它作为查询运行时它可以工作。我不确定我在哪里做错了。有人可以帮忙吗?
解决方案
我对程序的建议:
CREATE PROC [getAllRecordsForSalaryCalculation] (@year INT, @Bruger NVARCHAR(20)) AS
BEGIN
SET DATEFORMAT YMD
IF (@year = 2018)
BEGIN
WITH tm AS (
SELECT Overtid1 AS Ov1, Overtid2 AS Ov2, Vagt AS Vag, DateForQuery AS Dt
FROM timer
WHERE Bruger LIKE '%' + @Bruger + '%'
AND DateForQuery BETWEEN '2018-12-01' AND '2019-11-30'
)
SELECT
overtid1Before =(SELECT SUM(Ov1) FROM tm WHERE Dt BETWEEN '2018-12-01' AND '2019-04-30')
,overtid1After =(SELECT SUM(Ov1) FROM tm WHERE Dt BETWEEN '2019-05-01' AND '2019-11-30')
,overtid2Before =(SELECT SUM(Ov2) FROM tm WHERE Dt BETWEEN '2018-12-01' AND '2019-04-30')
,overtid2After =(SELECT SUM(Ov2) FROM tm WHERE Dt BETWEEN '2019-05-01' AND '2019-11-30')
,vagtBefore =(SELECT SUM(Vag) FROM tm WHERE Dt BETWEEN '2018-12-01' AND '2019-04-30')
,vagtAfter =(SELECT SUM(Vag) FROM tm WHERE Dt BETWEEN '2019-05-01' AND '2019-11-30')
END
END
推荐阅读
- c# - 如何在所有场景中运行一个方法——Unity?
- javascript - React Native - json - [未处理的承诺拒绝:TypeError:未定义不是对象(评估'key.time.date')]
- python - 随机字母生成器,直到生成 D,Python
- snowflake-cloud-data-platform - 雪花行数“G”的含义
- typescript - 动物区系查询语言关系
- javascript - Add key value to an array elements in JS
- c++ - 初始化地狱是什么意思?
- javascript - React 切换按钮只能工作一次?
- javascript - Handlebars.js -- 将平面列表转换为分层列表
- python - Python:打印包括0的偶数