netcore获取数据

之前都是用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是不是特别舒服呢，比原先那么一大坨的代码爽多了；