/// <summary> /// 分页查询 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql"></param> /// <param name="param"></param> /// <param name="currentindex"></param> /// <param name="pagesize"></param> /// <returns></returns> public static Pagination <T> PageQuery <T>(string sql, object param, int currentindex, int pagesize) where T : class, new() { using (MySqlConnection conn = DapperFactory.CreateMySqlConnection()) { if (conn.State != ConnectionState.Open) { conn.Open(); } var executeCountsql = $"SELECT COUNT(1) AS CountNum FROM ({sql}) t "; var totalcount = conn.Query <int>(executeCountsql, param).SingleOrDefault(); int start = (currentindex - 1) * pagesize; int end = currentindex * pagesize; var exectePagesql = $"{sql} limit {start},{pagesize}"; var models = conn.Query <T>(exectePagesql, param).ToList(); if (conn.State != ConnectionState.Closed) { conn.Close(); } var result = new Pagination <T> { TotalCount = totalcount, Models = models }; return(result); } }
/// <summary> /// 执行单个sql返回true or false /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t"></param> /// <param name="sql"></param> /// <returns></returns> public static bool Execute <T>(T t, string sql) { int result = -1; using (MySqlConnection conn = DapperFactory.CreateMySqlConnection()) { if (conn.State != ConnectionState.Open) { conn.Open(); } using (var trans = conn.BeginTransaction()) { result = conn.Execute(sql, t, trans, 30, CommandType.Text); if (result <= 0) { trans.Rollback(); } else { trans.Commit(); } } if (conn.State != ConnectionState.Closed) { conn.Close(); } } return(result > 0); }
public DbContext() { _conn = DapperFactory.CreateMySqlConnection(); if (_conn.State != ConnectionState.Open) { _conn.Open(); } }
public MySqlConnect(string sql, object param) { _sql = sql; _param = param; _cnn = DapperFactory.CreateMySqlConnection(); if (_cnn.State != ConnectionState.Open) { _cnn.Open(); } }
/// <summary> /// list返回 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql"></param> /// <param name="param">参数对象</param> /// <returns></returns> public static List <T> Query <T>(string sql, object param) { using (MySqlConnection conn = DapperFactory.CreateMySqlConnection()) { if (conn.State != ConnectionState.Open) { conn.Open(); } var result = conn.Query <T>(sql, param).ToList(); if (conn.State != ConnectionState.Closed) { conn.Close(); } return(result); } }
/// <summary> /// execute a Retrieve command with parameter, return a single result /// </summary> /// <typeparam name="T">Generic Type, to mapping the result</typeparam> /// <param name="sql">sql command</param> /// <param name="param">parameter</param> /// <returns></returns> public static T QuerySingle <T>(string sql, object param) { T t; using (MySqlConnection conn = DapperFactory.CreateMySqlConnection()) { if (conn.State != ConnectionState.Open) { conn.Open(); } t = conn.Query <T>(sql, param).SingleOrDefault(); if (conn.State != ConnectionState.Closed) { conn.Close(); } } return(t); }
/// <summary> /// 事务下操作多个sql返回true or false /// </summary> /// <returns></returns> public static bool Execute(List <KeyValuePair <string, object> > vals) { //ar result = -1; using (MySqlConnection conn = DapperFactory.CreateMySqlConnection()) { if (conn.State != ConnectionState.Open) { conn.Open(); } using (var trans = conn.BeginTransaction()) { foreach (var val in vals) { try { conn.Execute(val.Key, val.Value, trans, 30, CommandType.Text); } catch (Exception e) { //if (result <= 0) trans.Rollback(); return(false); } } trans.Commit(); } if (conn.State != ConnectionState.Closed) { conn.Close(); } } return(true); }