/// <summary> /// 执行SQL,返回第一行第一个元素的值 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql"></param> /// <param name="param"></param> /// <returns></returns> public static T ExecuteSql <T>(string sql, object param) { try { using (var con = SqlConnectionFactory.CreateSqlCon()) { T result = con.Query <T>(sql, param).FirstOrDefault <T>(); return(result); } } catch (Exception ex) { return(default(T)); } }
//------------------------------- /// <summary> /// 执行SQL语句,返回受影响的行数 /// </summary> /// <param name="sql">需要执行的sql语句</param> /// <param name="param">参数</param> /// <returns>受影响的行数</returns> public static int ExecuteSql(string sql, object param) { try { using (var con = SqlConnectionFactory.CreateSqlCon()) { int i = con.Execute(sql, param); return(i); } } catch (Exception ex) { return(-1); } }
/// <summary> /// 执行无返回结果集的存储过程 /// </summary> /// <param name="proName"></param> /// <param name="param"></param> /// <returns></returns> public static int ExecuteSP(string proName, object param) { try { using (var con = SqlConnectionFactory.CreateSqlCon()) { int result = con.Execute(proName, param, commandType: CommandType.StoredProcedure); return(result); } } catch (Exception ex) { return(-1); } }
/// <summary> /// 执行存储过程,并返回结果集 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="proName"></param> /// <param name="param"></param> /// <returns></returns> public static IList <T> ExecuteSP <T>(string proName, object param, out int recordCount) { recordCount = 0; try { using (var con = SqlConnectionFactory.CreateSqlCon()) { IEnumerable <T> result = con.Query <T>(proName, param, commandType: CommandType.StoredProcedure); return(result.ToList <T>()); } } catch (Exception ex) { return(null); } }
/// <summary> /// 使用事务批量添加数据 /// </summary> /// <param name="entityList">要添加的数据</param> /// <returns></returns> public void InsertBatch(IEnumerable <T> entityList) { if (entityList == null || entityList.Count() <= 0) { return; } using (var DbCon = SqlConnectionFactory.CreateSqlCon()) { DBHelper.TryOpen(DbCon); var trans = DbCon.BeginTransaction(); try { DbCon.Insert <T>(entityList, trans); trans.Commit(); } catch (System.Data.SqlClient.SqlException ex) { trans.Rollback(); DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (MySql.Data.MySqlClient.MySqlException ex) { trans.Rollback(); DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (System.Data.SqlServerCe.SqlCeException ex) { trans.Rollback(); DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (System.Data.SQLite.SQLiteException ex) { trans.Rollback(); DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (Exception ex) { trans.Rollback(); DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } finally { DbCon.Close(); DbCon.Dispose(); } } }
/// <summary> /// 分页获取 /// </summary> /// <param name="pageIndex">页索引</param> /// <param name="pageSize">页大小</param> /// <param name="allRowsCount">全部记录数</param> /// <param name="predicate">查询条件</param> /// <param name="sort">排序</param> /// <param name="buffered">是否缓存</param> /// <returns></returns> public List <T> GetPage(int pageIndex, int pageSize, out int allRowsCount, object predicate = null, IList <ISort> sort = null, bool buffered = true) { using (var DbCon = SqlConnectionFactory.CreateSqlCon()) { try { var result = DbCon.GetPage <T>(predicate, sort, pageIndex, pageSize, buffered: buffered); allRowsCount = DbCon.Count <T>(predicate); if (result != null) { return(result.ToList <T>()); } else { return(null); } } catch (System.Data.SqlClient.SqlException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (MySql.Data.MySqlClient.MySqlException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (System.Data.SqlServerCe.SqlCeException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (System.Data.SQLite.SQLiteException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (Exception ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } finally { DbCon.Close(); DbCon.Dispose(); } allRowsCount = 0; return(null); } }
/// <summary> /// 执行SQL,返回数据实体 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql"></param> /// <param name="param"></param> /// <returns></returns> public static IList <T> ExecuteSql <T>(string sql, object param, out int recordCount) { recordCount = 0; try { using (var con = SqlConnectionFactory.CreateSqlCon()) { IEnumerable <T> result = con.Query <T>(sql, param); recordCount = result.Count(); return(result.ToList <T>()); } } catch (Exception ex) { return(null); } }
/// <summary> /// 使用事务按条件批量删除数据 /// </summary> /// <param name="predicate">条件</param> /// <returns></returns> public bool DeleteList(object predicate) { using (var DbCon = SqlConnectionFactory.CreateSqlCon()) { DBHelper.TryOpen(DbCon); var trans = DbCon.BeginTransaction(); bool isOK = false; try { isOK = DbCon.Delete <T>(predicate, trans); trans.Commit(); } catch (System.Data.SqlClient.SqlException ex) { trans.Rollback(); DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (MySql.Data.MySqlClient.MySqlException ex) { trans.Rollback(); DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (System.Data.SqlServerCe.SqlCeException ex) { trans.Rollback(); DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (System.Data.SQLite.SQLiteException ex) { trans.Rollback(); DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (Exception ex) { trans.Rollback(); DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } finally { DbCon.Close(); DbCon.Dispose(); } return(isOK); } }
/// <summary> /// 获取全部数据 /// </summary> /// <returns></returns> public List <T> GetAll() { using (var DbCon = SqlConnectionFactory.CreateSqlCon()) { try { var result = DbCon.GetList <T>(); if (result != null) { return(result.ToList <T>()); } else { return(new List <T>()); } } catch (System.Data.SqlClient.SqlException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (MySql.Data.MySqlClient.MySqlException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (System.Data.SqlServerCe.SqlCeException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (System.Data.SQLite.SQLiteException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (Exception ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } finally { DbCon.Close(); DbCon.Dispose(); } return(new List <T>()); } }
/// <summary> /// 获取满足条件的记录 /// </summary> /// <param name="predicate">查询条件</param> /// <param name="sort">排序</param> /// <param name="buffered">是否缓存</param> /// <returns></returns> public List <T> GetList(object predicate = null, IList <ISort> sort = null, bool buffered = false) { using (var DbCon = SqlConnectionFactory.CreateSqlCon()) { try { var result = DbCon.GetList <T>(predicate, sort, buffered: buffered); if (result != null) { return(result.ToList <T>()); } else { return(null); } } catch (System.Data.SqlClient.SqlException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (MySql.Data.MySqlClient.MySqlException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (System.Data.SqlServerCe.SqlCeException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (System.Data.SQLite.SQLiteException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (Exception ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } finally { DbCon.Close(); DbCon.Dispose(); } return(null); } }
/// <summary> /// 添加一条数据 /// </summary> /// <param name="entity">数据实体</param> /// <returns></returns> public dynamic Insert(T entity) { using (var DbCon = SqlConnectionFactory.CreateSqlCon()) { try { var result = DbCon.Insert <T>(entity); return(result); } catch (System.Data.SqlClient.SqlException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (MySql.Data.MySqlClient.MySqlException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (System.Data.SqlServerCe.SqlCeException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (System.Data.SQLite.SQLiteException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (Exception ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } finally { DbCon.Close(); DbCon.Dispose(); } return(null); } }
/// <summary> /// 通过ID获取记录 /// </summary> /// <param name="primaryId">动态类型ID</param> /// <returns>单条满足条件的记录</returns> public T GetById(object primaryId) { using (var DbCon = SqlConnectionFactory.CreateSqlCon()) { try { var result = DbCon.Get <T>(primaryId); return(result as T); } catch (System.Data.SqlClient.SqlException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (MySql.Data.MySqlClient.MySqlException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (System.Data.SqlServerCe.SqlCeException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (System.Data.SQLite.SQLiteException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (Exception ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } finally { DbCon.Close(); DbCon.Dispose(); } return(null); } }
/// <summary> /// 满足条件的数据条数 /// </summary> /// <param name="predicate">查询条件</param> /// <returns>满足条件的记录数</returns> public int Count(object predicate) { using (var DbCon = SqlConnectionFactory.CreateSqlCon()) { try { var result = DbCon.Count <T>(predicate); return(result); } catch (System.Data.SqlClient.SqlException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (MySql.Data.MySqlClient.MySqlException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (System.Data.SqlServerCe.SqlCeException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (System.Data.SQLite.SQLiteException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (Exception ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } finally { DbCon.Close(); DbCon.Dispose(); } return(0); } }
/// <summary> /// 删除一条数据 /// </summary> /// <param name="primaryId"></param> /// <param name="transaction"></param> /// <returns></returns> public bool Delete(object predicate) { using (var DbCon = SqlConnectionFactory.CreateSqlCon()) { try { return(DbCon.Delete <T>(predicate)); } catch (System.Data.SqlClient.SqlException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (MySql.Data.MySqlClient.MySqlException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (System.Data.SqlServerCe.SqlCeException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (System.Data.SQLite.SQLiteException ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (Exception ex) { DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } finally { DbCon.Close(); DbCon.Dispose(); } return(false); } }
public bool DeleteBatch(IEnumerable <object> ids) { if (ids == null || ids.Count() <= 0) { return(false); } using (var DbCon = SqlConnectionFactory.CreateSqlCon()) { int successCount = 0; DBHelper.TryOpen(DbCon); var trans = DbCon.BeginTransaction(); try { foreach (object one in ids) { if (DbCon.Delete <T>(one, trans)) { successCount++; } } if (successCount == ids.Count()) { trans.Commit(); return(true); } else { trans.Rollback(); return(false); } } catch (System.Data.SqlClient.SqlException ex) { trans.Rollback(); DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (MySql.Data.MySqlClient.MySqlException ex) { trans.Rollback(); DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (System.Data.SqlServerCe.SqlCeException ex) { trans.Rollback(); DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (System.Data.SQLite.SQLiteException ex) { trans.Rollback(); DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } catch (Exception ex) { trans.Rollback(); DBHelper.WriteLog(typeof(RepositoryBase <T>), ex); } finally { DbCon.Close(); DbCon.Dispose(); } return(false); } }