62,046
社区成员
发帖
与我相关
我的任务
分享C#使用EF，MySqlConnection执行同一段SQL语句，返回的值却不一致，请问是什么情况？
EF执行SQL代码段
MySqlConnection执行SQL语句
DXJCYXL类
public static List<DXJCYXL> GetDXJCYXL(String StartDate = "", String EndDate = "", String DepartType = "", String ExaType = "")
{
#region 定义查询语句
/// 定义查询语句
StringBuilder sbSql = new StringBuilder(
@"SELECT DEPART_CODE, DEPART_NAME, COUNT(*) JCRS,
SUM(CASE WHEN CHECK_RESULT = '阳性' THEN 1 ELSE 0 END) YangXRS,
SUM(CASE WHEN CHECK_RESULT = '阴性' THEN 1 ELSE 0 END) YinXRS,
ROUND((SUM(CASE CHECK_RESULT WHEN '阳性' THEN 1 ELSE 0 END)/COUNT(*))*100,2) YangXL
FROM v_major_result
WHERE 1 =1");
#endregion
#region 加载查询条件
/// 加载查询条件
List<MySqlParameter> sqlParameters = new List<MySqlParameter>();
if (!String.IsNullOrEmpty(DepartType))
{
switch (DepartType)
{
case "门诊":
sbSql.Append(" AND SFMZ = 'Y'");
sqlParameters.Add(new MySqlParameter("@DepartType", DepartType));
break;
default:
sbSql.Append(" AND a.SFZY= 'Y'");
sqlParameters.Add(new MySqlParameter("@DepartType", DepartType));
break;
}
}
if(!String.IsNullOrEmpty(ExaType))
{
sbSql.Append(" AND EXA_TYPE = @EXA_TYPE");
sqlParameters.Add(new MySqlParameter("@EXA_TYPE", ExaType));
}
if (!String.IsNullOrEmpty(StartDate))
{
sbSql.Append(" AND EXAMAT >= @StartDate");
sqlParameters.Add(new MySqlParameter("@StartDate", StartDate));
}
if (!String.IsNullOrEmpty(EndDate))
{
sbSql.Append(" AND EXAMAT <= @EndDate");
sqlParameters.Add(new MySqlParameter("@EndDate", EndDate));
}
#endregion
sbSql.Append(" GROUP BY DEPART_CODE, DEPART_NAME ORDER BY YangXL");
try
{
#region 执行查询
using (var db = new FXFKEntities())
{
var list = db.Database.SqlQuery<DXJCYXL>(sbSql.ToString(), sqlParameters.ToArray()).ToList();
return list;
}
#endregion
}
catch (Exception ex)
{
throw (ex);
}
}MySqlConnection执行SQL语句
public JsonResult GetDXJCYXL2(String StartDate = "", String EndDate = "", String DepartType = "", String ExaType = "")
{
#region 定义查询语句
/// 定义查询语句
StringBuilder sbSql = new StringBuilder(
@"SELECT DEPART_CODE, DEPART_NAME, COUNT(*) JCRS,
SUM(CASE WHEN CHECK_RESULT = '阳性' THEN 1 ELSE 0 END) YangXRS,
SUM(CASE WHEN CHECK_RESULT = '阴性' THEN 1 ELSE 0 END) YinXRS,
ROUND((SUM(CASE CHECK_RESULT WHEN '阳性' THEN 1 ELSE 0 END)/COUNT(*))*100,2) YangXL
FROM v_major_result
WHERE 1 =1");
#endregion
#region 加载查询条件
/// 加载查询条件
List<MySqlParameter> sqlParameters = new List<MySqlParameter>();
if (!String.IsNullOrEmpty(DepartType))
{
switch (DepartType)
{
case "门诊":
sbSql.Append(" AND SFMZ = 'Y'");
sqlParameters.Add(new MySqlParameter("@DepartType", DepartType));
break;
default:
sbSql.Append(" AND a.SFZY= 'Y'");
sqlParameters.Add(new MySqlParameter("@DepartType", DepartType));
break;
}
}
if (!String.IsNullOrEmpty(ExaType))
{
sbSql.Append(" AND EXA_TYPE = @EXA_TYPE");
sqlParameters.Add(new MySqlParameter("@EXA_TYPE", ExaType));
}
if (!String.IsNullOrEmpty(StartDate))
{
sbSql.Append(" AND EXAMAT >= @StartDate");
sqlParameters.Add(new MySqlParameter("@StartDate", StartDate));
}
if (!String.IsNullOrEmpty(EndDate))
{
sbSql.Append(" AND EXAMAT <= @EndDate");
sqlParameters.Add(new MySqlParameter("@EndDate", EndDate));
}
#endregion
sbSql.Append(" GROUP BY DEPART_CODE, DEPART_NAME ORDER BY YangXL");
try
{
#region 执行查询
/// 执行查询，获取查询结果
DataSet ds = null;
using (MySqlConnection conn = MySQLHelper.GetConnection)
{
//打开连接
conn.Open();
//调用执行方法，因为没有参数，所以最后一项直接设置为null
ds = MySQLHelper.ExecuteDataSet(sbSql.ToString(), sqlParameters.ToArray());
}
#endregion
#region 组装数据
/// 组装数据
List<DXJCYXL> list = new List<DXJCYXL>();
foreach (DataRow row in ds.Tables[0].Rows)
{
list.Add(
new DXJCYXL
{
DEPART_CODE = row["DEPART_CODE"].ToString(),
DEPART_NAME = row["DEPART_NAME"].ToString(),
JCRS = Convert.ToDecimal(row["JCRS"]),
YangXRS = Convert.ToDecimal(row["YangXRS"]),
YinXRS = Convert.ToDecimal(row["YinXRS"]),
YangXL = Convert.ToDecimal(row["YangXL"])
});
}
#endregion
return Json(list, JsonRequestBehavior.AllowGet);
}
catch (Exception ex)
{
return Json(new JsonRes("error", null, "发生错误：" + ex.ToString()), JsonRequestBehavior.AllowGet);
}
}DXJCYXL类
public class DXJCYXL
{
/// <summary>
/// 科室编号
/// </summary>
public String DEPART_CODE { get; set; }
/// <summary>
/// 科室名称
/// </summary>
public String DEPART_NAME { get; set; }
/// <summary>
/// 检查人数
/// </summary>
public Decimal JCRS { get; set; }
/// <summary>
/// 阳性人数
/// </summary>
public Decimal YangXRS { get; set; }
/// <summary>
/// 阴性人数
/// </summary>
public Decimal YinXRS { get; set; }
/// <summary>
/// 阳性率
/// </summary>
public Decimal YangXL { get; set; }
}
...全文
请发表友善的回复…
发表回复
yangchun57 2015-12-04
- 打赏
- 举报
log记录的查询语句中，中文字符都变成了“??”了，应该是这个问题。
yangchun57 2015-12-04
- 打赏
- 举报
1、拼接起来是一样的SQL语句；
2、使用的是同一个数据库呢[/quote]
分别执行，用sql profiler监控下语句，看下到底是不是真的一样。[/quote]
log记录的查询语句中，中文字符都变成了“??”了，应该是这个问题。
yangchun57 2015-12-04
- 打赏
- 举报
log记录的查询语句中，中文字符都变成了“??”了，应该是这个问题。
正怒月神 版主 2015-12-03
- 打赏
- 举报
执行结果不一样，可能是 ef的sql语句不是你要的结果。
sql profiler看一下，应该就知道 sql语句的区别了。
江南小鱼 2015-12-03
- 打赏
- 举报
1、拼接起来是一样的SQL语句；
2、使用的是同一个数据库呢[/quote]
分别执行，用sql profiler监控下语句，看下到底是不是真的一样。
丰云 2015-12-03
- 打赏
- 举报
真没看出来这跟EF有毛线的关系。。。。。。
完全是拼装sql语句而已！！！！！
到数据库查查最终执行的sql语句，
看看到底有什么区别，
不就一清二楚的吗？
不要想当然的认定语句是一样的，就算是一样的语句，如果环境变了，拿的东西跟着有变化，也是正常的。
计算机只会严格执行命令而已，程序也是如此，有问题一定是我们的命令有问题。
BitCoffee 2015-12-02
- 打赏
- 举报
你的问题应该就是后面三个字段为0，实体没有赋值引起的，导致ef按照YangXL字段排序之后再按照其它规则排序了。
var list = db.Database.SqlQuery<DXJCYXL>(sbSql.ToString(), sqlParameters.ToArray()).ToList();
你具体断点看下这个里面怎么执行的。
yangchun57 2015-12-01
- 打赏
- 举报
1、SQL语句和参数值，都是一样的；
2、后面三个字段，是用的SUM、ROUND函数获得的值，应该是Decimal类型吧，也试过int、double类型，也都是返回0
yangchun57 2015-12-01
- 打赏
- 举报
1、拼接起来是一样的SQL语句；
2、使用的是同一个数据库呢
BitCoffee 2015-12-01
- 打赏
- 举报
1. var list = db.Database.SqlQuery<DXJCYXL>(sbSql.ToString(), sqlParameters.ToArray()).ToList();
ds = MySQLHelper.ExecuteDataSet(sbSql.ToString(), sqlParameters.ToArray());
这两处你自己打断点,比对下sql语句和参数值啊.
2.EF执行SQL的时候，最后三个值，总是0
检查下实体里面后面三个字段的名称和类型与数据库表对比.
chenyu112 2015-12-01
- 打赏
- 举报
看看根据相同的条件提交到数据库里的时候sql语句是一样的吗？
如果2个到最后提交到数据库里的sql是一样的话，应该出来的是一样的数据；还有必须连接一个数据库
yangchun57 2015-12-01
- 打赏
- 举报
EF执行SQL的时候，最后三个值，总是0。换用MySqlConnection执行的时候，就是正常的。请问该如何解决呢？
yangchun57 2015-12-01
- 打赏
- 举报
EF执行SQL返回的结果
[
{
"DEPART_CODE": "23",
"DEPART_NAME": "泌尿外科",
"JCRS": 3,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "160",
"DEPART_NAME": "体检中心",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "87",
"DEPART_NAME": "结石专科",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "32",
"DEPART_NAME": "17病室1区",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "163",
"DEPART_NAME": "感染科",
"JCRS": 2,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "56",
"DEPART_NAME": "儿科",
"JCRS": 3,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "64",
"DEPART_NAME": "21病室",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "184",
"DEPART_NAME": "骨二科",
"JCRS": 7,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "6",
"DEPART_NAME": "消化内科",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "31",
"DEPART_NAME": "4病室",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "15",
"DEPART_NAME": "普通内科",
"JCRS": 3,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "47",
"DEPART_NAME": "妇科",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "63",
"DEPART_NAME": "12病室",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "149",
"DEPART_NAME": "中医骨伤科",
"JCRS": 7,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "215",
"DEPART_NAME": "外科假日专家门诊",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "220",
"DEPART_NAME": "19病室1区",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "161",
"DEPART_NAME": "急诊科室",
"JCRS": 15,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "171",
"DEPART_NAME": "乳甲头颈",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "182",
"DEPART_NAME": "5病室",
"JCRS": 2,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "169",
"DEPART_NAME": "肿瘤放疗科",
"JCRS": 2,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "38",
"DEPART_NAME": "18病室",
"JCRS": 4,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "170",
"DEPART_NAME": "11病室",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "20",
"DEPART_NAME": "普外一科",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "168",
"DEPART_NAME": "13病室",
"JCRS": 4,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "35",
"DEPART_NAME": "10病室",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "7",
"DEPART_NAME": "呼吸内科",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "60",
"DEPART_NAME": "24病室",
"JCRS": 2,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "181",
"DEPART_NAME": "肾内血液科",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "34",
"DEPART_NAME": "9病室",
"JCRS": 2,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "58",
"DEPART_NAME": "耳鼻咽喉科",
"JCRS": 2,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "37",
"DEPART_NAME": "22病室1区",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "10",
"DEPART_NAME": "肿瘤内科",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "61",
"DEPART_NAME": "15病室",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "13",
"DEPART_NAME": "神经内科",
"JCRS": 6,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
},
{
"DEPART_CODE": "167",
"DEPART_NAME": "16病室",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 0,
"YangXL": 0
}
]yangchun57 2015-12-01
- 打赏
- 举报
MySqlConnection执行SQL返回的结果
[
{
"DEPART_CODE": "171",
"DEPART_NAME": "乳甲头颈",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 1,
"YangXL": 0
},
{
"DEPART_CODE": "170",
"DEPART_NAME": "11病室",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 1,
"YangXL": 0
},
{
"DEPART_CODE": "20",
"DEPART_NAME": "普外一科",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 1,
"YangXL": 0
},
{
"DEPART_CODE": "60",
"DEPART_NAME": "24病室",
"JCRS": 2,
"YangXRS": 0,
"YinXRS": 2,
"YangXL": 0
},
{
"DEPART_CODE": "167",
"DEPART_NAME": "16病室",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 1,
"YangXL": 0
},
{
"DEPART_CODE": "87",
"DEPART_NAME": "结石专科",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 1,
"YangXL": 0
},
{
"DEPART_CODE": "32",
"DEPART_NAME": "17病室1区",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 1,
"YangXL": 0
},
{
"DEPART_CODE": "64",
"DEPART_NAME": "21病室",
"JCRS": 1,
"YangXRS": 0,
"YinXRS": 1,
"YangXL": 0
},
{
"DEPART_CODE": "13",
"DEPART_NAME": "神经内科",
"JCRS": 6,
"YangXRS": 2,
"YinXRS": 4,
"YangXL": 33.33
},
{
"DEPART_CODE": "182",
"DEPART_NAME": "5病室",
"JCRS": 2,
"YangXRS": 1,
"YinXRS": 1,
"YangXL": 50
},
{
"DEPART_CODE": "38",
"DEPART_NAME": "18病室",
"JCRS": 4,
"YangXRS": 2,
"YinXRS": 2,
"YangXL": 50
},
{
"DEPART_CODE": "149",
"DEPART_NAME": "中医骨伤科",
"JCRS": 7,
"YangXRS": 4,
"YinXRS": 3,
"YangXL": 57.14
},
{
"DEPART_CODE": "161",
"DEPART_NAME": "急诊科室",
"JCRS": 15,
"YangXRS": 10,
"YinXRS": 5,
"YangXL": 66.67
},
{
"DEPART_CODE": "56",
"DEPART_NAME": "儿科",
"JCRS": 3,
"YangXRS": 2,
"YinXRS": 1,
"YangXL": 66.67
},
{
"DEPART_CODE": "15",
"DEPART_NAME": "普通内科",
"JCRS": 3,
"YangXRS": 2,
"YinXRS": 1,
"YangXL": 66.67
},
{
"DEPART_CODE": "47",
"DEPART_NAME": "妇科",
"JCRS": 1,
"YangXRS": 1,
"YinXRS": 0,
"YangXL": 100
},
{
"DEPART_CODE": "63",
"DEPART_NAME": "12病室",
"JCRS": 1,
"YangXRS": 1,
"YinXRS": 0,
"YangXL": 100
},
{
"DEPART_CODE": "215",
"DEPART_NAME": "外科假日专家门诊",
"JCRS": 1,
"YangXRS": 1,
"YinXRS": 0,
"YangXL": 100
},
{
"DEPART_CODE": "220",
"DEPART_NAME": "19病室1区",
"JCRS": 1,
"YangXRS": 1,
"YinXRS": 0,
"YangXL": 100
},
{
"DEPART_CODE": "169",
"DEPART_NAME": "肿瘤放疗科",
"JCRS": 2,
"YangXRS": 2,
"YinXRS": 0,
"YangXL": 100
},
{
"DEPART_CODE": "168",
"DEPART_NAME": "13病室",
"JCRS": 4,
"YangXRS": 4,
"YinXRS": 0,
"YangXL": 100
},
{
"DEPART_CODE": "35",
"DEPART_NAME": "10病室",
"JCRS": 1,
"YangXRS": 1,
"YinXRS": 0,
"YangXL": 100
},
{
"DEPART_CODE": "7",
"DEPART_NAME": "呼吸内科",
"JCRS": 1,
"YangXRS": 1,
"YinXRS": 0,
"YangXL": 100
},
{
"DEPART_CODE": "34",
"DEPART_NAME": "9病室",
"JCRS": 2,
"YangXRS": 2,
"YinXRS": 0,
"YangXL": 100
},
{
"DEPART_CODE": "181",
"DEPART_NAME": "肾内血液科",
"JCRS": 1,
"YangXRS": 1,
"YinXRS": 0,
"YangXL": 100
},
{
"DEPART_CODE": "58",
"DEPART_NAME": "耳鼻咽喉科",
"JCRS": 2,
"YangXRS": 2,
"YinXRS": 0,
"YangXL": 100
},
{
"DEPART_CODE": "37",
"DEPART_NAME": "22病室1区",
"JCRS": 1,
"YangXRS": 1,
"YinXRS": 0,
"YangXL": 100
},
{
"DEPART_CODE": "61",
"DEPART_NAME": "15病室",
"JCRS": 1,
"YangXRS": 1,
"YinXRS": 0,
"YangXL": 100
},
{
"DEPART_CODE": "10",
"DEPART_NAME": "肿瘤内科",
"JCRS": 1,
"YangXRS": 1,
"YinXRS": 0,
"YangXL": 100
},
{
"DEPART_CODE": "160",
"DEPART_NAME": "体检中心",
"JCRS": 1,
"YangXRS": 1,
"YinXRS": 0,
"YangXL": 100
},
{
"DEPART_CODE": "23",
"DEPART_NAME": "泌尿外科",
"JCRS": 3,
"YangXRS": 3,
"YinXRS": 0,
"YangXL": 100
},
{
"DEPART_CODE": "163",
"DEPART_NAME": "感染科",
"JCRS": 2,
"YangXRS": 2,
"YinXRS": 0,
"YangXL": 100
},
{
"DEPART_CODE": "184",
"DEPART_NAME": "骨二科",
"JCRS": 7,
"YangXRS": 7,
"YinXRS": 0,
"YangXL": 100
},
{
"DEPART_CODE": "6",
"DEPART_NAME": "消化内科",
"JCRS": 1,
"YangXRS": 1,
"YinXRS": 0,
"YangXL": 100
},
{
"DEPART_CODE": "31",
"DEPART_NAME": "4病室",
"JCRS": 1,
"YangXRS": 1,
"YinXRS": 0,
"YangXL": 100
}
]
