/// <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> /// 执行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)); } }
//public static bool Delete<T>(string sql,object predicate, IDbTransaction transaction = null, int? commandTimeout = null) where T : class //{ // try // { // using (var con = SqlConnectionFactory.CreateSqlCon()) // { // return con. // } // } // catch (Exception ex) // { // return false; // } //} //------------------------------- /// <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> /// <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.OracleClient.OracleException 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.OracleClient.OracleException 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> /// <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="pageIndex">页索引页索引(从0开始)</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> /// 插入一条数据并返回该记录ID /// </summary> /// <param name="entity">数据实体</param> /// <returns>该记录ID</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.OracleClient.OracleException 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.OracleClient.OracleException 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 bool Update(T entity) { using (var DbCon = SqlConnectionFactory.CreateSqlCon()) { try { return(DbCon.Update <T>(entity)); } 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.OracleClient.OracleException 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); } }
/// <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); } }
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.OracleClient.OracleException 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); } }