dapper mysql通用类_C#操作MySql数据库帮助类（Dapper,T-Sql）

usingSystem.Text;usingMySql.Data.MySqlClient;usingSystem.Data;usingDapper;usingSystem.Reflection;namespaceDbHelper

{public classMySqlHelper

{private string connectionStr = "server=localhost;database=fxy;User=root;password=cxk";//public object connection = GetConnection(connectionStr);

///

///Dapper查询(包含存储过程及sql语句查询)///

/// 实体类型

/// 存储过程名称或者sql语句

/// 参数化处理

/// 是否存储过程查询

///

public List DapperQuery(string sql , object param , bool? isStoredProcedure = false) where T : new()

{using(IDbConnection con = newMySqlConnection(connectionStr))

{

CommandType cmdType= (isStoredProcedure ?? true) ?CommandType.StoredProcedure : CommandType.Text;try{

List queryList = con.Query(sql , param , null, true , null, cmdType).ToList();returnqueryList;

}catch(Exception e)

{throw;

}

}

}///

///TSQL查询///

///

///

///

///

///

public List TSqlQuery(string sql,MySqlParameter[] param,bool? isStoredProcedure = false) where T:new()

{using(MySqlConnection con = newMySqlConnection(connectionStr))

{

con.Open();

CommandType cmdType= (isStoredProcedure ?? true) ?CommandType.StoredProcedure : CommandType.Text;

MySqlCommand command= newMySqlCommand(sql , con );

command.CommandType=cmdType;if(param != null)

{

command.Parameters.AddRange(param);

}try{

MySqlDataReader reader=command.ExecuteReader();

List list = DataReaderToList(reader);returnlist;

}catch(Exception e)

{throw;

}finally{

con.Close();

}

}

}///

///Dapper增删改(包含存储过程及sql语句查询)///

/// 存储过程名称或者sql语句

/// 参数化处理

/// 是否存储过程查询

///

public bool DapperExcute(string sql , object param , bool? isStoredProcedure=false,int?commandTimeout=null)

{bool result = false;using(IDbConnection con = newMySqlConnection(connectionStr))

{

con.Open();

IDbTransaction tran=con.BeginTransaction();

CommandType cmdType= isStoredProcedure==true ?CommandType.StoredProcedure : CommandType.Text;try{int query =con.Execute(sql , param , tran , commandTimeout , cmdType);

tran.Commit();

result= true;

}catch(Exception e)

{

tran.Rollback();throw;

}finally{

con.Close();

}returnresult;

}

}///

///TSQL增删改操作///

///

///

///

///

public bool TSqlExcute(string sql , MySqlParameter[] param , bool? isStoredProcedure=false)

{bool result = false;using(MySqlConnection con = newMySqlConnection(connectionStr))

{

con.Open();

MySqlTransaction tran=con.BeginTransaction();

CommandType cmdType= isStoredProcedure==true ?CommandType.StoredProcedure : CommandType.Text;

MySqlCommand command= newMySqlCommand(sql , con , tran);

command.Parameters.AddRange(param);try{int query =command.ExecuteNonQuery();

tran.Commit();

result= true;

}catch(Exception e)

{

tran.Rollback();throw;

}finally{

con.Close();

}returnresult;

}

}///

///批量数据写入///

///

///

///

///

private bool BulkInsert(string sql , List dataList) where T:new()

{bool result = false;//获取T的公共属性

Type type = dataList[ 0].GetType();

PropertyInfo[] param=type.GetProperties();

List properotyList = param.Select(p =>p.Name).ToList();using(MySqlConnection con= newMySqlConnection(connectionStr))

{

con.Open();

StringBuilder sb= newStringBuilder();

sb.Append(sql);

sb.Append("VALUES");int i = 0;foreach(var item indataList)

{

sb.Append("(");for(int j = 0 ; j < properotyList.Count ; j++)

{

PropertyInfo properotyInfo= item.GetType().GetProperty(properotyList[ j ]); //属性的信息

object properotyValue = properotyInfo.GetValue(item , null);//属性的值

string cellValue = properotyValue == null ? "" : properotyValue.ToString();//单元格的值

sb.Append("\"");

sb.Append(properotyValue);

sb.Append("\"");if(j < properotyList.Count - 1)

{

sb.Append(",");

}

}

sb.Append(")");if(i++ < dataList.Count - 1)

{

sb.Append(",");

}

}

sql=sb.ToString();

MySqlTransaction tran=con.BeginTransaction();

MySqlCommand commd= newMySqlCommand(sql , con , tran);try{int query =commd.ExecuteNonQuery();

result= true;

}catch(Exception e)

{

tran.Rollback();throw;

}returnresult;

}

}///

///DataReader To List///

///

///

///

private static List DataReaderToList(MySqlDataReader reader) where T : new()

{

List list = new List();if(reader.HasRows)

{while(reader.Read())

{

T t= newT();

Type type=t.GetType();var properties =type.GetProperties();foreach(var item inproperties)

{string name =item.Name;

reader.GetSchemaTable().DefaultView.RowFilter= "ColumnName= ‘" + name + "‘";bool check = reader.GetSchemaTable().DefaultView.Count > 0;if(check)

{if(!item.CanWrite)

{continue;

}var value =reader[ name ];if(value !=DBNull.Value)

{

item.SetValue(t , value ,null);

}

}

}

list.Add(t);

}

}returnlist;

}

}

}