public class MySqlHelper
{
private YourContext _context;
public MySqlHelper(YourContext context)
{
_context = context;
}
///
/// 查询列表
///
///
///
///
public List QueryForDatable(string sql)
{
string conn= _context.Database.Connection.ConnectionString;
using(var connection= new MySqlConnection(conn))
{
MySqlCommand cmd = new MySqlCommand(sql, connection);
//需要open才能执行Command
connection.Open();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
List data = DataReaderMapToList(reader);
connection.Close();
return data;
}
}
}
///
/// 查询数量
///
///
///
public int QueryForCount(string sql)
{
string conn = _context.Database.Connection.ConnectionString;
using (var connection = new MySqlConnection(conn))
{
MySqlCommand cmd = new MySqlCommand(sql, connection);
connection.Open();
//只取第一行第一个结果
Object result = cmd.ExecuteScalar();
if (result != null)
{
int count = int.Parse(result.ToString());
connection.Close();
return count;
}
connection.Close();
return 0;
}
}
public static List DataReaderMapToList(IDataReader dr)
{
List list = new List();
T obj = default(T);
List columnNames = new List();
//查询DataReader里面的列名集合
for (var f = 0; f < dr.FieldCount; f++)
{
columnNames.Add(dr.GetName(f));
}
while (dr.Read())
{
obj = Activator.CreateInstance();
foreach (PropertyInfo prop in obj.GetType().GetProperties())
{
//判断列名是否存在
var columnName = columnNames.Find(x => x == prop.Name);
if (!string.IsNullOrEmpty(columnName))
{
prop.SetValue(obj, dr[prop.Name]);
}
}
list.Add(obj);
}
return list;
}
}