/// <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);
}
}
