之前都是用EF，需要绑定模型，后来发现很多时候数据列都需要更改，又不好去改模型，肯定没有SQL语句来的方便，要是复杂点视图的话，那更是坑爹了，写都写不出来，后来就写了几个方法
public async Task<JArray> QueryScalarList(string sql) ???????{ ???????????JArray x = new JArray(); ???????????try ???????????{ ???????????????using (var con = Database.GetDbConnection()) ???????????????{ ???????????????????if (con.State == System.Data.ConnectionState.Closed) con.Open(); ???????????????????var cmd = con.CreateCommand(); ???????????????????cmd.CommandType = System.Data.CommandType.Text; ???????????????????cmd.CommandText = sql; ???????????????????var reader = await cmd.ExecuteReaderAsync(); ???????????????????while (reader.Read()) ???????????????????{ ???????????????????????JObject y = new JObject(); ???????????????????????for (var i = 0; i < reader.FieldCount; i++) ???????????????????????{ ???????????????????????????var name = reader.GetName(i); ???????????????????????????y[name] = reader[i].ToString(); ???????????????????????} ???????????????????????x.Add(y); ???????????????????} ???????????????????cmd.Dispose(); ???????????????????con.Close(); ???????????????????con.Dispose(); ???????????????} ???????????????return x; ???????????} ???????????catch (Exception e) ???????????{ ???????????????return x; ???????????} ???????}其他的服务调用
public async Task<JArray> GetList(string field, string view, string where, string orderby, int page = -1, int pagesize = int.MaxValue) ???????{ ???????????try ???????????{ ???????????????var p = (page - 1) * pagesize; ???????????????string sql = ""; ???????????????string q_where = "", q_orderby = ""; ???????????????if (!string.IsNullOrWhiteSpace(where)) q_where = " where " + where; ???????????????if (!string.IsNullOrWhiteSpace(orderby)) q_orderby = " order by " + orderby; ???????????????sql = "select " + field + " from " + view + q_where + q_orderby + " limit " + p.ToString() + "," + pagesize.ToString(); ???????????????string sql2 = TypeCheck.SqlExpr(sql); ???????????????var data = await _context.QueryScalarList(sql2); ???????????????return data; ???????????} ???????????catch (Exception ex) ???????????{ ???????????????return null; ???????????} ???????}哈，贼方便，放在API里，服务里还是可以做其他加工的，
还有一个是关于SP的，也做了下包装；
public async Task<JArray> QueryScalarSPList(string spname, MySqlParameter[] mySqlParameters = null) ???????{ ???????????JArray x = new JArray(); ???????????try ???????????{ ???????????????using (var con = Database.GetDbConnection()) ???????????????{ ???????????????????if (con.State == System.Data.ConnectionState.Closed) con.Open(); ???????????????????var cmd = con.CreateCommand(); ???????????????????cmd.CommandType = System.Data.CommandType.StoredProcedure; ???????????????????cmd.CommandText = spname; ???????????????????cmd.Parameters.AddRange(mySqlParameters); ???????????????????var reader = await cmd.ExecuteReaderAsync(); ???????????????????while (reader.Read()) ???????????????????{ ???????????????????????JObject y = new JObject(); ???????????????????????for (var i = 0; i < reader.FieldCount; i++) ???????????????????????{ ???????????????????????????var name = reader.GetName(i); ???????????????????????????y[name] = reader[i].ToString(); ???????????????????????} ???????????????????????x.Add(y); ???????????????????} ???????????????????cmd.Dispose(); ???????????????????con.Close(); ???????????????????con.Dispose(); ???????????????} ???????????????return x; ???????????} ???????????catch (Exception e) ???????????{ ???????????????return x; ???????????} ???????}服务里调用这个的时候，需要加下工
public async Task<JArray> GetSPList(string spname, object parameters) ???????{ ???????????try ???????????{ ???????????????Type type = parameters.GetType(); ???????????????PropertyInfo[] propertyInfo = type.GetProperties(); ???????????????var len = propertyInfo.Length; ???????????????MySqlParameter[] myparameters = new MySqlParameter[len]; ???????????????for (var i = 0; i < propertyInfo.Length; i++) ???????????????{ ???????????????????var x = propertyInfo[i].PropertyType.ToString(); ???????????????????if (x.Contains("int")) ???????????????????{ ???????????????????????myparameters[i] = new MySqlParameter(propertyInfo[i].Name, MySqlDbType.Int32); ???????????????????} ???????????????????else ???????????????????{ ???????????????????????myparameters[i] = new MySqlParameter(propertyInfo[i].Name, MySqlDbType.VarChar, 300); ???????????????????} ???????????????????myparameters[i].Value = propertyInfo[i].GetValue(parameters); ???????????????} ???????????????var data = await _context.QueryScalarSPList(spname, myparameters); ???????????????return data; ???????????} ???????????catch (Exception ex) ???????????{ ???????????????return null; ???????????} ???????}因为业务原因，就没做更多判断，只判断Int和varchar格式，有需要的自己增加判断吧；
调用服务的时候，特别爽快，
var data = await _serviceCommon.GetSPList("xxxxxx", new { userid_p = userid, searchValue_p = username, page, pageSize = pagesize });SQL语句调用服务
var data = await _serviceCommon.GetList("*", "(select a.* from xxxxa) m", "(xx like ‘%" + aa + "%‘ or xxxx like ‘%" + bb + "%‘)", "id", page, pagesize);返回的JArray是不是特别舒服呢，比原先那么一大坨的代码爽多了；
netcore获取数据
原文地址：https://www.cnblogs.com/huanyun/p/9157799.html