该博客展示了如何在C#中通过`MySqlHelper`类实现与MySQL数据库的连接和交互，包括查询列表和查询数量的方法。`MySqlHelper`类依赖于`YourContext`上下文，并使用`MySqlConnection`打开和关闭连接，执行SQL命令，通过`MySqlDataReader`读取数据并将其映射为对象列表。
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;
}
}
扫一扫
154
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
