MYSQL连接数据库

web.config

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

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