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