/// <summary> /// SQL执行方法返回List /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dto"></param> /// <returns></returns> public List <T> SQLExecuteReturnList <T>(SQLExecuteParam dto) where T : new() { MySqlConnection connection = new MySqlConnection(this.connectString); List <T> list = new List <T>(); try { connection.Open(); if (dto.obj != null) { list = connection.Query <T>(dto.sql, dto.obj).ToList(); } else if (dto.dict.Count > 0) { list = connection.Query <T>(dto.sql, dto.dict).ToList(); } else { list = connection.Query <T>(dto.sql, null).ToList(); } connection.Close(); } catch (Exception ex) { connection.Close(); Log.WriteLog(ex.Message); return(null); } return(list); }
/// <summary> /// SQL执行方法返回影响行数 /// </summary> /// <param name="dto"></param> /// <returns></returns> public int SQLExecuteReturnRows(SQLExecuteParam dto) { DataTable dt = new DataTable(); MySqlConnection connection = new MySqlConnection(this.connectString); var rows = -1; try { connection.Open(); if (dto.obj != null) { rows = connection.Execute(dto.sql, dto.obj); } else if (dto.dict.Count > 0) { rows = connection.Execute(dto.sql, dto.dict); } else { rows = connection.Execute(dto.sql, null); } connection.Close(); } catch (Exception ex) { connection.Close(); Log.WriteLog(ex.Message); return(-1); } return(rows); }
/// <summary> /// SQL执行方法返回单一数据对象 /// </summary> /// <param name="dto"></param> /// <returns></returns> public T SQLExecuteSingleData <T>(SQLExecuteParam dto) where T : new() { T t = default(T); MySqlConnection connection = new MySqlConnection(this.connectString); try { connection.Open(); if (dto.obj != null) { t = connection.Query <T>(dto.sql, dto.obj).SingleOrDefault(); } else if (dto.dict.Count > 0) { t = connection.Query <T>(dto.sql, dto.dict).SingleOrDefault(); } else { t = connection.Query <T>(dto.sql, null).SingleOrDefault(); } connection.Close(); } catch (Exception ex) { connection.Close(); Log.WriteLog(ex.Message); return(default(T)); } return(t); }
/// <summary> /// SQL执行方法返回DataTable /// </summary> /// <param name="dto"></param> /// <returns></returns> public DataTable SQLExecuteReturnTable(SQLExecuteParam dto) { DataTable dt = new DataTable(); MySqlConnection connection = new MySqlConnection(this.connectString); try { IEnumerable <dynamic> rows = null; connection.Open(); if (dto.obj != null) { rows = connection.Query(dto.sql, dto.obj); } else if (dto.dict.Count > 0) { rows = connection.Query(dto.sql, dto.dict); } else { rows = connection.Query(dto.sql, null); } int flag = 0; foreach (IDictionary <string, object> row in rows) { ICollection <string> arrStr = row.Keys; if (flag == 0) { foreach (string str in arrStr) { dt.Columns.Add(str); } flag = 1; } DataRow dtrow = dt.NewRow(); foreach (string str in arrStr) { dtrow[str] = (object)row[str]; } dt.Rows.Add(dtrow); } connection.Close(); } catch (Exception ex) { connection.Close(); Log.WriteLog(ex.Message); return(null); } return(dt); }
/// <summary> /// 执行分页返回List 该方法**不支持**实体类参数赋值给obj /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dto"></param> /// <returns></returns> public PaginationResult <T> SQLExecuteReturnPaginationList <T>(SQLExecuteParam dto) where T : new() { const int defaultLimit = 10; PaginationResult <T> pResult = new PaginationResult <T>(); MySqlConnection connection = new MySqlConnection(this.connectString); try { if (dto.pagination == null) { throw new Exception("未初始化分页条件"); } pResult.limit = dto.pagination.limit == 0 ? defaultLimit : dto.pagination.limit; pResult.offset = dto.pagination.offset; connection.Open(); string countSql = "SELECT COUNT(*) FROM (" + dto.sql + ") AS countTable"; if (dto.dict.Count > 0) { pResult.total = connection.Query <int>(countSql, dto.dict).SingleOrDefault(); } else { pResult.total = connection.Query <int>(countSql, null).SingleOrDefault(); } dto.sql = "SELECT * FROM (" + dto.sql + ") AS Pagination WHERE Pagination.rowNo > @offset AND Pagination.rowNo <= @limit + @offset"; if (dto.dict.Count > 0) { dto.dict.Add("limit", dto.pagination.limit); dto.dict.Add("offset", dto.pagination.offset); pResult.rows = connection.Query <T>(dto.sql, dto.dict).ToList(); } else { dto.dict.Add("limit", dto.pagination.limit); dto.dict.Add("offset", dto.pagination.offset); pResult.rows = connection.Query <T>(dto.sql, new { limit = dto.pagination.limit, offset = dto.pagination.offset }).ToList(); } connection.Close(); } catch (Exception ex) { connection.Close(); Log.WriteLog(ex.Message); return(null); } return(pResult); }
/// <summary> /// 以dt作为参数执行sql /// </summary> /// <param name="dr"></param> /// <param name="sql"></param> /// <returns></returns> public bool SQLExecuteWithTransaction(SQLExecuteParam dto) { bool flag = false; int count = 0; MySqlConnection connection = new MySqlConnection(this.connectString); connection.Open(); IDbTransaction transaction = connection.BeginTransaction(); try { foreach (object item in dto.tranList) { if (connection.Execute(dto.sql, item, transaction) == 1) { count++; } } if (count == dto.tranList.Count) { if (dto.afterSql != null && dto.afterSql != "") { connection.Execute(dto.afterSql, dto.afterParam, transaction); } transaction.Commit(); flag = true; } else { transaction.Rollback(); flag = false; } connection.Close(); } catch (Exception ex) { transaction.Rollback(); connection.Close(); Log.WriteLog(ex.Message); return(flag); } return(flag); }