存储过程:CREATE PROCEDURE workdbase.proc_get_cjdwsqtree(IN p_dwbm VARCHAR(255), OUT p_errmsg VARCHAR(4000))
SQL SECURITY INVOKER
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND SET p_errmsg = '查询数据错误!';
IF(p_dwbm='100000')THEN
SELECT NULL AS COUNT, -1 AS CJBM,NULL AS CJFBM,"所有" AS CJMC FROM dual
UNION ALL
SELECT NULL AS COUNT, "BS" AS CJBM,-1 AS CJFBM,"BS插件" AS CJMC FROM dual
UNION ALL
SELECT NULL AS COUNT, "CS" AS CJBM,-1 AS CJFBM,"CS插件" AS CJMC FROM dual
UNION ALL
SELECT count(*) as COUNT, t.CJFBLJ AS CJBM,(CASE t.CJLX WHEN "0" THEN "BS" ELSE "CS" END)AS CJFBM, t.CJMC FROM xt_cj_cjqxfp p inner join
xt_cj_cjxx t on p.CJFBLJ=t.CJFBLJ where p.SFSQ='Y' GROUP BY t.CJMC;
ELSE
SELECT NULL AS COUNT, -1 AS CJBM,NULL AS CJFBM,"所有" AS CJMC FROM dual
UNION ALL
SELECT NULL AS COUNT, "BS" AS CJBM,-1 AS CJFBM,"BS插件" AS CJMC FROM dual
UNION ALL
SELECT NULL AS COUNT, "CS" AS CJBM,-1 AS CJFBM,"CS插件" AS CJMC FROM dual
UNION ALL
SELECT IFNULL(COUNT(p.SFSQ),0) AS COUNT ,t.CJFBLJ AS CJBM,(CASE t.CJLX WHEN "0" THEN "BS" ELSE "CS" END)AS CJFBM, t.CJMC
FROM xt_cj_cjxx t
LEFT JOIN xt_cj_cjqxfp p
ON t.CJFBLJ=p.CJFBLJ
AND p.DWBM LIKE p_dwbm
AND p.SFSQ='Y'
GROUP BY t.cjfblj;
END IF;
END
C#调用存储过程的方法 public DataTable DoExecuteDataTable(string strProcName)
{
Database db = DataAccessor.CreateDatabase();
DataTable rtnDt = new DataTable(); try
{
AddLogDebug(strProcName);
DataSet ds = db.ExecuteDataSet<KeyValueItem>(strProcName, this.KeyValue); if (ds != null && ds.Tables.Count > 0)
{
rtnDt = ds.Tables[0];
}
}
catch (Exception ex)
{
AddLogError(strProcName, ex);
throw new Exception("数据库处理出错:" + ex.Message);
}
finally
{
this.ThrowException(strProcName);
}
return rtnDt;
}
执行存储过程的结果C#获取到的结果
SQL SECURITY INVOKER
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND SET p_errmsg = '查询数据错误!';
IF(p_dwbm='100000')THEN
SELECT NULL AS COUNT, -1 AS CJBM,NULL AS CJFBM,"所有" AS CJMC FROM dual
UNION ALL
SELECT NULL AS COUNT, "BS" AS CJBM,-1 AS CJFBM,"BS插件" AS CJMC FROM dual
UNION ALL
SELECT NULL AS COUNT, "CS" AS CJBM,-1 AS CJFBM,"CS插件" AS CJMC FROM dual
UNION ALL
SELECT count(*) as COUNT, t.CJFBLJ AS CJBM,(CASE t.CJLX WHEN "0" THEN "BS" ELSE "CS" END)AS CJFBM, t.CJMC FROM xt_cj_cjqxfp p inner join
xt_cj_cjxx t on p.CJFBLJ=t.CJFBLJ where p.SFSQ='Y' GROUP BY t.CJMC;
ELSE
SELECT NULL AS COUNT, -1 AS CJBM,NULL AS CJFBM,"所有" AS CJMC FROM dual
UNION ALL
SELECT NULL AS COUNT, "BS" AS CJBM,-1 AS CJFBM,"BS插件" AS CJMC FROM dual
UNION ALL
SELECT NULL AS COUNT, "CS" AS CJBM,-1 AS CJFBM,"CS插件" AS CJMC FROM dual
UNION ALL
SELECT IFNULL(COUNT(p.SFSQ),0) AS COUNT ,t.CJFBLJ AS CJBM,(CASE t.CJLX WHEN "0" THEN "BS" ELSE "CS" END)AS CJFBM, t.CJMC
FROM xt_cj_cjxx t
LEFT JOIN xt_cj_cjqxfp p
ON t.CJFBLJ=p.CJFBLJ
AND p.DWBM LIKE p_dwbm
AND p.SFSQ='Y'
GROUP BY t.cjfblj;
END IF;
END
C#调用存储过程的方法 public DataTable DoExecuteDataTable(string strProcName)
{
Database db = DataAccessor.CreateDatabase();
DataTable rtnDt = new DataTable(); try
{
AddLogDebug(strProcName);
DataSet ds = db.ExecuteDataSet<KeyValueItem>(strProcName, this.KeyValue); if (ds != null && ds.Tables.Count > 0)
{
rtnDt = ds.Tables[0];
}
}
catch (Exception ex)
{
AddLogError(strProcName, ex);
throw new Exception("数据库处理出错:" + ex.Message);
}
finally
{
this.ThrowException(strProcName);
}
return rtnDt;
}
执行存储过程的结果C#获取到的结果
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货