web.config
<connectionStrings>
<add name="MysqlDB" connectionString="Data Source=.;Initial Catalog=dbname;Persist Security Info=True;User ID=username;Password=password;" providerName="MySql.Data.MySqlClient" />
</connectionStrings>
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
namespace Service.Common
{
public class DbMyHelp
{
//连接字符串拼装
//mycon = new MySqlConnection("Host=127.0.0.1;UserName=root;Password=root;Database=score;Port=3306");
//private static string config = System.Configuration.ConfigurationManager.AppSettings["MysqlDB"].ToString();
private string config = string.Empty;
/// <summary>
/// 数据库连接串
/// </summary>
public string ConnectionString
{
set { config = value; }
}
/// <summary>
/// 构造
/// </summary>
public DbMyHelp(string connName)
{
this.config = System.Configuration.ConfigurationManager.ConnectionStrings[connName].ToString();
}
/// <summary>
/// 查询返回List<T>
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
public List<T> QueryList<T>(string sql)
{
///////////////////获取MYSQ看数据返回值////////////////////////////
MySqlConnection mycon = new MySqlConnection(config);
//连接
mycon.Open();
//查询命令赋值，可以写多条语句，多条语句之间用;号隔开
MySqlCommand mycom = new MySqlCommand(sql, mycon);
MySqlDataReader myrec = mycom.ExecuteReader();
List<T> list = new List<T>();
//一次次读，读不到就结束
while (myrec.Read())
{
T obj = ExecDataReader<T>(myrec);
list.Add(obj); //string myInfo = myInfo + myrec["Name"] + " " + myrec["ID"];
}
//////关闭相关对象
myrec.Close();
mycom.Dispose();
mycon.Close();
return list;
}
/// <summary>
/// 查询返回object
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public object QueryObject(string sql)
{
///////////////////获取MYSQ看数据返回值////////////////////////////
MySqlConnection mycon = new MySqlConnection(config);
//连接
mycon.Open();
//查询命令赋值，可以写多条语句，多条语句之间用;号隔开
MySqlCommand mycom = new MySqlCommand(sql, mycon);
object obj = mycom.ExecuteScalar();
//////关闭相关对象
mycom.Dispose();
mycon.Close();
return obj;
}
/// <summary>
/// 查询返回datatable
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataTable QueryTable(string sql)
{
MySqlConnection mycon = new MySqlConnection(config);
mycon.Open();
MySqlCommand mycom = new MySqlCommand(sql, mycon);
DataSet dataset = new DataSet();//dataset放执行后的数据集合
MySqlDataAdapter adapter = new MySqlDataAdapter(mycom);
adapter.Fill(dataset);
mycom.Dispose();
mycon.Close();
return dataset.Tables[0];
}
/// <summary>
/// 操作增删改
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int ExecutSql(string sql)
{
int result = 0;
MySqlConnection mycon = new MySqlConnection(config);
mycon.Open();
MySqlCommand mycom = new MySqlCommand(sql, mycon);
result = mycom.ExecuteNonQuery();
mycom.Dispose();
mycon.Close();
mycon.Dispose();
return result;
}
/// <summary>
/// 事务操作增删改
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int ExcuteTran(string sql)
{
MySqlConnection mycon = new MySqlConnection(config);
MySqlCommand mycom = null;
MySqlTransaction trans = null;
int result = 0;
try
{
mycon.Open();
mycom = mycon.CreateCommand();
mycom.CommandText = sql;
//创建事务
trans = mycon.BeginTransaction();
result = mycom.ExecuteNonQuery();
//事务提交
trans.Commit();
}
catch
{
//事务回滚
trans.Rollback();
}
finally
{
mycom.Dispose();
mycon.Close();
mycon.Dispose();
}
return result;
}
/// <summary>
/// IDataReader、MySqlDataReader 转T实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="reader"></param>
/// <returns></returns>
private T ExecDataReader<T>(IDataReader reader)
{
T obj = default(T);
try
{
Type type = typeof(T);
obj = (T)Activator.CreateInstance(type);//从当前程序集里面通过反射的方式创建指定类型的对象
PropertyInfo[] propertyInfos = type.GetProperties();//获取指定类型里面的所有属性
foreach (PropertyInfo propertyInfo in propertyInfos)
{
for (int i = 0; i < reader.FieldCount; i++)
{
string fieldName = reader.GetName(i);
if (fieldName.ToLower() == propertyInfo.Name.ToLower())
{
//object val = reader[propertyInfo.Name];//读取表中某一条记录里面的某一列
object val = reader[fieldName];//读取表中某一条记录里面的某一列
if (val != null && val != DBNull.Value)
{
propertyInfo.SetValue(obj, val);
}
break;
}
}
}
}
catch (Exception)
{
throw;
}
return obj;
}
}
public static class DataHelper
{
/// <summary>
/// DataTable 转List<T>实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dt"></param>
/// <returns></returns>
public static List<T> ToEntity<T>(this DataTable dt) where T : new()
{
List<T> list = new List<T>();
Type info = typeof(T);
var props = info.GetProperties();
foreach (DataRow dr in dt.Rows)
{
T entity = new T();
foreach (var pro in props)
{
var propInfo = info.GetProperty(pro.Name);
if (dt.Columns.Contains(pro.Name))
{
propInfo.SetValue(entity, Convert.ChangeType(dr[pro.Name], propInfo.PropertyType), null);
}
}
list.Add(entity);
}
return list;
}
}
}