时间戳

首页 > 解决方案 > c#中的MySql过程调用不返回任何行

c# - c#中的MySql过程调用不返回任何行

问题描述

我在这里看到了很多关于堆栈溢出的帖子，其中提到了与标题相同的问题。但是我的问题仍然没有解决，所以现在不要将其标记为重复，因为我真的需要了解原因并为此找到解决方案。我有一个在工作台中运行良好但从 C# 调用时运行良好的程序。

这是我的 C# 代码：

public List<object> GetScheduleTest(string building,int sem, int week, int day, string userid)
    {
        List<object> idata = new List<object>();
        DataTable dt = new DataTable();            
        using(MySqlConnection cn = new MySqlConnection(constring))
        {
            try
            {
                if (cn.State != ConnectionState.Open) { cn.Open(); }
                using (MySqlCommand cmd = new MySqlCommand("GetScheduleTest", cn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("build", building);
                    cmd.Parameters.AddWithValue("sem", sem);
                    cmd.Parameters.AddWithValue("weekno", week);
                    cmd.Parameters.AddWithValue("daynum", day);
                    cmd.Parameters.AddWithValue("userid", userid);                        
                    MySqlDataAdapter mySqlData = new MySqlDataAdapter(cmd);
                    mySqlData.Fill(dt);                        
                }
                idata.Add(dt);
            }
            catch(Exception ex)
            {

            }               
        }
        return idata;
    }

这是mysql程序。它有点大。

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetScheduleTest`(build varchar(45), weekno int, sem int,daynum int, userid varchar(45))
BEGIN
set @uid=userid;
set @semno = sem;
set @dayno = daynum;
set @daynumreserve = daynum+1;
set @weeknum = weekno;
set @building = build;
SET @sql := CONCAT('
 create or replace view  View_resultScheduleDayWeek as 
  SELECT sc.classname, ifnull(concat(case when section = 11 then coursename  end),'' '') AS `section11`,
ifnull(concat(case when section = 5 then coursename  end),'' '') AS `section5`,
ifnull(concat(case when section = 6 then coursename  end),'' '') AS `section6`,
ifnull(concat(case when section = 7 then coursename  end),'' '') AS `section7`,
ifnull(concat(case when section = 9 then coursename  end),'' '') AS `section9` ,
ifnull(concat(case when section = 1 then coursename end),'' '') AS `section1`,
ifnull(concat(case when section = 2 then coursename  end),'' '') AS `section2`,
ifnull(concat(case when section = 3 then coursename end),'' '') AS `section3`,
ifnull(concat(case when section = 4 then coursename end),'' '') AS `section4` ,
ifnull(concat(case when section = 8 then coursename end),'' '') AS `section8`,
ifnull(concat(case when section = 10 then coursename end),'' '') AS `section10`,
ifnull(concat(case when section = 12 then coursename  end),'' '') AS `section12` 
  FROM schedule sc where weekStart<=',@weeknum,' and weekend >=',@weeknum,' and 
  sc.classname in (select classname from classdetails where teachingBuilding=''',@building,''' and classname in
  (select classid from userlocationaccess where userserialnum =(select serialno from userdetails where loginid= ''',@uid,'''))) 
    and Sem =',@semno,' and dayno =',@dayno,' GROUP BY  section,sc.classname 
 union
SELECT classroom as classname, ifnull(concat(case when section = 11 then concat( purpose,'':reserve:'',Status)  end),'' '') AS `section11`,
ifnull(concat(case when section = 5 then concat( purpose,'':reserve:'',Status)  end),'' '') AS `section5`,
ifnull(concat(case when section = 6 then concat( purpose,'':reserve:'',Status)  end),'' '') AS `section6`,
ifnull(concat(case when section = 7 then concat( purpose,'':reserve:'',Status)  end),'' '') AS `section7`,
ifnull(concat(case when section = 9 then concat( purpose,'':reserve:'',Status)  end),'' '') AS `section9` ,
ifnull(concat(case when section = 1 then concat( purpose,'':reserve:'',Status) end),'' '') AS `section1`,
ifnull(concat(case when section = 2 then concat( purpose,'':reserve:'',Status)  end),'' '') AS `section2`,
ifnull(concat(case when section = 3 then concat( purpose,'':reserve:'',Status) end),'' '') AS `section3`,
ifnull(concat(case when section = 4 then concat( purpose,'':reserve:'',Status) end),'' '') AS `section4` ,
ifnull(concat(case when section = 8 then concat( purpose,'':reserve:'',Status) end),'' '') AS `section8`,
ifnull(concat(case when section = 10 then concat( purpose,'':reserve:'',Status) end),'' '') AS `section10`,
ifnull(concat(case when section = 12 then concat( purpose,'':reserve:'',Status)  end),'' '') AS `section12` 
 FROM schedulereserve where classroom in (select classname from classdetails where teachingbuilding=''',@building,''' and classname in
  (select classid from userlocationaccess where userserialnum =(select serialno from userdetails where loginid= ''',@uid,''')))
  and week =',@weeknum,' and Status !=''Rejected'' and semester =',@semno,' and weekday(date) =',@daynumreserve,' GROUP BY  section,classroom
  union
  SELECT newclass as classname, ifnull(concat(case when newsection = 11 then concat( courseid ,'':transfer:'',currentstatus) end),'' '') AS `section11`,
ifnull(concat(case when newsection = 5 then concat( courseid ,'':transfer:'',currentstatus)  end),'' '') AS `section5`,
ifnull(concat(case when newsection = 6 then concat(courseid ,'':transfer:'',currentstatus)  end),'' '') AS `section6`,
ifnull(concat(case when newsection = 7 then concat(courseid ,'':transfer:'',currentstatus)  end),'' '') AS `section7`,
ifnull(concat(case when newsection = 9 then concat(courseid ,'':transfer:'',currentstatus)  end),'' '') AS `section9` ,
ifnull(concat(case when newsection = 1 then concat(courseid ,'':transfer:'',currentstatus) end),'' '') AS `section1`,
ifnull(concat(case when newsection = 2 then concat(courseid ,'':transfer:'',currentstatus)  end),'' '') AS `section2`,
ifnull(concat(case when newsection = 3 then concat(courseid ,'':transfer:'',currentstatus) end),'' '') AS `section3`,
ifnull(concat(case when newsection = 4 then concat(courseid ,'':transfer:'',currentstatus) end),'' '') AS `section4` ,
ifnull(concat(case when newsection = 8 then concat(courseid ,'':transfer:'',currentstatus) end),'' '') AS `section8`,
ifnull(concat(case when newsection = 10 then concat(courseid ,'':transfer:'',currentstatus) end),'' '') AS `section10`,
ifnull(concat(case when newsection = 12 then concat(courseid ,'':transfer:'',currentstatus) end),'' '') AS `section12` 
 FROM scheduletransfer where newweek<=',@weeknum,' and newweek >=',@weeknum,' and newclass in 
 (select classname from classdetails where teachingbuilding=''',@building,''' and classname in
  (select classid from userlocationaccess where userserialnum =(select serialno from userdetails where loginid= ''',@uid,'''))) and 
 idref in (select id from schedule where sem=',@semno,') and newday =',@dayno,' GROUP BY  newsection,newclass');
PREPARE stmt FROM @sql;
EXECUTE stmt;

select row_number() OVER() AS RowNumber , classname, max(section1) as section1, max(section2) as section2,
max(section3)as section3,
max(section4)as section4,
max(section5)as section5,
max(section6)as section6,max(section7)as section7,
max(section8)as section8,
max(section9)as section9,max(section10)as section10,
max(section11)as section11,
max(section12)as section12 from View_resultScheduleDayWeek group by classname;
END

这些一直工作到昨天没有任何问题。我试图对过程中的分页进行一些更改。这在数据库方面也工作得很好。但是一旦我进行了更改，其中包括 c# 端的输出参数。此过程停止从 c# 给出结果。现在上面给出的它的早期版本也不能通过 c# 工作。我尝试了来自不同帖子的所有可能解决方案，但似乎没有任何效果。因为没有例外，我不明白原因。请提出一些建议。如果您需要更多详细信息，请告诉我。谢谢

标签： c#mysql

解决方案

我认为您选择做的这件事，即在 SP 内创建一个视图并从中进行选择是一个坏主意，但它似乎包含了您的问题的答案

您的抱怨是，当您在 WB 中运行此 SP 时，它会产生（并留下）一个确实产生结果的视图。从 C# 运行 SP 时，它会生成一个在查询时没有结果的视图。

因此，我建议您将此 SP 克隆为一个新名称，并调整克隆，以便它创建一个名称略有不同的视图。从 C# 运行一个，从 WB 运行另一个 ..

..然后将每个视图的代码从数据库中拉出并通过差异工具运行它们以查看差异。除了名称之外，两者之间必须存在一些关键区别

当您解决问题时，我会放弃每次运行 SP 时创建新视图的方法，而改用 WITH。

不要这样做：

@sql = 'CREATE VIEW x AS SELECT y FROM z;'
EXECUTE @sql;
SELECT * FROM x;

做就是了：

WITH x AS (SELECT y FROM z;)
SELECT * FROM x;

推荐阅读