C#编程之C#操作MySql数据库帮助类(Dapper,T-Sql)
小标
2018-12-14
来源 :
阅读 2138
评论 0
摘要:本文主要向大家介绍了C#编程之C#操作MySql数据库帮助类(Dapper,T-Sql),通过具体的内容向大家展示,希望对大家学习C#编程有所帮助。
本文主要向大家介绍了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 List
DapperQuery(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 { List queryList = con.Query(sql , param , null, true , null , cmdType).ToList(); return queryList; } catch(Exception e) { throw; } } } /// /// TSQL查询 /// /// /// /// /// /// public List TSqlQuery(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(); List list = 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 , 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= 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 < 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 = 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 List DataReaderToList(MySqlDataReader reader) where T : new() { List list = 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频道!
本文由 @小标 发布于职坐标。未经许可,禁止转载。
看完这篇文章有何感觉?已经有0 人表态,0% 的人喜欢
快给朋友分享吧~
评论(0)