本文主要向大家介绍了C#编程之C#操作MySql数据库帮助类(Dapper,T-Sql),通过具体的内容向大家展示,希望对大家学习C#编程有所帮助。
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using Dapper;
using System.Reflection;
namespace DbHelper
{
public class MySqlHelper
{
private string connectionStr = "server=localhost;database=fxy;User=root;password=cxk";
//public object connection = GetConnection(connectionStr);
// Dapper查询(包含存储过程及sql语句查询)
///
///实体类型
///
存储过程名称或者sql语句
///
参数化处理
///
是否存储过程查询
///
public ListDapperQuery(string sql , object param , bool? isStoredProcedure = false) where T : new()
{
using(IDbConnection con = new MySqlConnection(connectionStr))
{
CommandType cmdType = (isStoredProcedure ?? true) ? CommandType.StoredProcedure : CommandType.Text;
try
{
ListqueryList = con.Query(sql , param , null, true , null , cmdType).ToList();
return queryList;
}
catch(Exception e)
{
throw;
}
}
}
/// /// TSQL查询
///
///
///
///
///
///
public ListTSqlQuery(string sql,MySqlParameter[] param,bool? isStoredProcedure = false) where T:new()
{
using(MySqlConnection con = new MySqlConnection(connectionStr))
{
con.Open();
CommandType cmdType = (isStoredProcedure ?? true) ? CommandType.StoredProcedure : CommandType.Text;
MySqlCommand command = new MySqlCommand(sql , con );
command.CommandType = cmdType;
if(param != null )
{
command.Parameters.AddRange(param);
}
try
{
MySqlDataReader reader = command.ExecuteReader();
Listlist = DataReaderToList(reader);
return list;
}
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 = new MySqlConnection(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();
}
return result;
}
}
/// /// TSQL增删改操作
///
///
///
///
///
public bool TSqlExcute(string sql , MySqlParameter[] param , bool? isStoredProcedure=false)
{
bool result = false;
using(MySqlConnection con = new MySqlConnection(connectionStr))
{
con.Open();
MySqlTransaction tran = con.BeginTransaction();
CommandType cmdType = isStoredProcedure==true ? CommandType.StoredProcedure : CommandType.Text;
MySqlCommand command = new MySqlCommand(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();
}
return result;
}
}
/// /// 批量数据写入
///
///
///
///
///
private bool BulkInsert(string sql , ListdataList) where T:new()
{
bool result = false;
//获取T的公共属性
Type type = dataList[ 0 ].GetType();
PropertyInfo[] param = type.GetProperties();
ListproperotyList = param.Select(p => p.Name).ToList();
using(MySqlConnection con= new MySqlConnection(connectionStr))
{
con.Open();
StringBuilder sb = new StringBuilder();
sb.Append(sql);
sb.Append(" VALUES");
int i = 0;
foreach(var item in dataList)
{
sb.Append("(");
for(int j = 0 ; 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
{
sb.Append(",");
}
}
sb.Append(")");
if(i++
{
sb.Append(",");
}
}
sql = sb.ToString();
MySqlTransaction tran = con.BeginTransaction();
MySqlCommand commd = new MySqlCommand(sql , con , tran);
try
{
int query = commd.ExecuteNonQuery();
result = true;
}
catch(Exception e)
{
tran.Rollback();
throw;
}
return result;
}
}
/// /// DataReader To List
///
///
///
///
private static ListDataReaderToList(MySqlDataReader reader) where T : new()
{
Listlist = new List();
if(reader.HasRows)
{
while(reader.Read())
{
T t = new T();
Type type = t.GetType();
var properties = type.GetProperties();
foreach(var item in properties)
{
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);
}
}
return list;
}
}
}
本文由职坐标整理并发布,希望对同学们有所帮助。了解更多详情请关注职坐标编程语言C#.NET频道!