mysql 连接数据库_MYSQL连接数据库

web.config

providerName="MySql.Data.MySqlClient" />

//

usingMySql.Data.MySqlClient;usingSystem;usingSystem.Collections.Generic;usingSystem.Data;usingSystem.Linq;usingSystem.Reflection;usingSystem.Text;usingSystem.Threading.Tasks;namespaceService.Common

{public classDbMyHelp

{//连接字符串拼装//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;///

///数据库连接串///

public stringConnectionString

{set { config =value; }

}///

///构造///

public DbMyHelp(stringconnName)

{this.config =System.Configuration.ConfigurationManager.ConnectionStrings[connName].ToString();

}///

///查询返回List

///

///

///

///

public List QueryList(stringsql)

{///获取MYSQ看数据返回值

MySqlConnection mycon = newMySqlConnection(config);//连接

mycon.Open();//查询命令赋值，可以写多条语句，多条语句之间用;号隔开

MySqlCommand mycom = newMySqlCommand(sql, mycon);

MySqlDataReader myrec=mycom.ExecuteReader();

List list = new List();//一次次读，读不到就结束

while(myrec.Read())

{

T obj= ExecDataReader(myrec);

list.Add(obj);//string myInfo = myInfo + myrec["Name"] + " " + myrec["ID"];

}//关闭相关对象

myrec.Close();

mycom.Dispose();

mycon.Close();returnlist;

}///

///查询返回object///

///

///

public object QueryObject(stringsql)

{///获取MYSQ看数据返回值

MySqlConnection mycon = newMySqlConnection(config);//连接

mycon.Open();//查询命令赋值，可以写多条语句，多条语句之间用;号隔开

MySqlCommand mycom = newMySqlCommand(sql, mycon);object obj =mycom.ExecuteScalar();//关闭相关对象

mycom.Dispose();

mycon.Close();returnobj;

}///

///查询返回datatable///

///

///

public DataTable QueryTable(stringsql)

{

MySqlConnection mycon= newMySqlConnection(config);

mycon.Open();

MySqlCommand mycom= newMySqlCommand(sql, mycon);

DataSet dataset= new DataSet();//dataset放执行后的数据集合

MySqlDataAdapter adapter = newMySqlDataAdapter(mycom);

adapter.Fill(dataset);

mycom.Dispose();

mycon.Close();return dataset.Tables[0];

}///

///操作增删改///

///

///

public int ExecutSql(stringsql)

{int result = 0;

MySqlConnection mycon= newMySqlConnection(config);

mycon.Open();

MySqlCommand mycom= newMySqlCommand(sql, mycon);

result=mycom.ExecuteNonQuery();

mycom.Dispose();

mycon.Close();

mycon.Dispose();returnresult;

}///

///事务操作增删改///

///

///

public int ExcuteTran(stringsql)

{

MySqlConnection mycon= newMySqlConnection(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();

}returnresult;

}///

///IDataReader、MySqlDataReader 转T实体///

///

///

///

private T ExecDataReader(IDataReader reader)

{

T obj= default(T);try{

Type type= typeof(T);

obj= (T)Activator.CreateInstance(type);//从当前程序集里面通过反射的方式创建指定类型的对象

PropertyInfo[] propertyInfos = type.GetProperties();//获取指定类型里面的所有属性

foreach (PropertyInfo propertyInfo inpropertyInfos)

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

}returnobj;

}

}public static classDataHelper

{///

///DataTable 转List实体///

///

///

///

public static List ToEntity(this DataTable dt) where T : new()

{

List list = new List();

Type info= typeof(T);var props =info.GetProperties();foreach (DataRow dr indt.Rows)

{

T entity= newT();foreach (var pro inprops)

{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);

}returnlist;

}

}

}