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;
}
}
}
