/// <summary>
/// 通过事务批量新增
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <returns></returns>
public Enums.TickLingEnum InsertModelsTransaction <T>(List <T> list)
{
Type typeFromHandle = typeof(T);
string strTableName = typeFromHandle.Name;
StringBuilder sbColumns = new StringBuilder();
StringBuilder sbParameters = new StringBuilder();
using (DbConnection Connection = new DBFactory().GetInstance())
{
Connection.Open();
IDbTransaction transaction = Connection.BeginTransaction();
try
{
foreach (var item in list)
{
InsertTransaction(item, transaction, Connection);
}
transaction.Commit();
return(Enums.TickLingEnum.Success);
}
catch (Exception ex)
{
transaction.Rollback();
return(Enums.TickLingEnum.Fail);;
}
}
}
/// <summary>
/// 查询数据(集合)
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="where">拼接的查询条件字符串</param>
/// <param name="topNumber">返回数据条数</param>
/// <returns>返回结果集</returns>
public List <T> Select <T>(string where, string orderby = "", int topNumber = -1)
{
try
{
Type typeFromHandle = typeof(T);
string strTableName = typeFromHandle.Name;
string sql = string.Empty;
if (!string.IsNullOrEmpty(where))
{
where = " where " + where;
}
if (topNumber != -1)
{
sql = string.Format("select top {0} * from [{1}] {2} {3}", topNumber, strTableName, where, orderby);
}
else
{
sql = string.Format("select * from [{0}] {1} ", strTableName, where, orderby);
}
using (DbConnection Connection = new DBFactory().GetInstance())
{
Connection.Open();
return(SqlMapper.Query <T>(Connection, sql, null, null, true, null, CommandType.Text).ToList());
}
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 批量删除(事务)
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="ids">主键集合</param>
/// <returns></returns>
public Enums.TickLingEnum DeleteModelsTransaction <T>(string[] ids)
{
using (DbConnection Connection = new DBFactory().GetInstance())
{
Connection.Open();
IDbTransaction transaction = Connection.BeginTransaction();
try
{
Type typeFromHandle = typeof(T);
string strTableName = typeFromHandle.Name;
string PrimaryKeyName = string.Empty;
List <PropertyInfo> list = typeFromHandle.GetProperties().Where(p => p.IsDefined(typeof(PrimaryKeyAttribute), false) == true).ToList();
if (list.Count == 1)
{
PrimaryKeyName = list[0].Name;
foreach (var item in ids)
{
DeleteTransaction <T>(string.Format(" {0} = '{1}' ", PrimaryKeyName, item), transaction, Connection);
}
transaction.Commit();
return(Enums.TickLingEnum.Success);
}
else
{
return(Enums.TickLingEnum.Fail);
}
}
catch (Exception ex)
{
transaction.Rollback();
return(Enums.TickLingEnum.Abnormity);
}
}
}
/// <summary>
/// 判断重复(根据主键)
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="id">主键</param>
/// <returns>是否存在</returns>
public Enums.TickLingEnum ExistsByID <T>(string id)
{
try
{
Type typeFromHandle = typeof(T);
string strTableName = typeFromHandle.Name;
var parems = new DynamicParameters();
string strwhere = string.Empty;
//捕获主键
foreach (PropertyInfo item in typeFromHandle.GetProperties())
{
if (item.IsDefined(typeof(PrimaryKeyAttribute), false) && !string.IsNullOrEmpty(id))
{
strwhere = string.Format("where {0} = '{1}'", item.Name, id);
parems.Add("@" + item.Name, id); //添加主键参数
}
}
string strSql = string.Format("select count(*) from [" + strTableName + "] {0}", strwhere);
using (DbConnection Connection = new DBFactory().GetInstance())
{
Connection.Open();
return(Convert.ToInt32(Connection.Query <int>(strSql, parems, null, false, null, CommandType.Text).ToList()[0]) > 0 ? Enums.TickLingEnum.Existence : Enums.TickLingEnum.NonExistence);
}
}
catch (Exception ex)
{
return(Enums.TickLingEnum.Abnormity);
}
}
/// <summary>
/// 判断重复(根据条件)
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="columnTable">条件集合</param>
/// <returns></returns>
public Enums.TickLingEnum CheckColumnValueIsExist <T>(Hashtable columnTable)
{
try
{
Type typeFromHandle = typeof(T);
string strTableName = typeFromHandle.Name;
var parems = new DynamicParameters();
StringBuilder sbwhere = new StringBuilder();
if (columnTable.Count > 0)
{
sbwhere.Append(" where ");
foreach (DictionaryEntry item in columnTable)
{
sbwhere.AppendFormat(" {0} = @{1} and", item.Key, item.Key);
parems.Add("@" + item.Key, item.Value.ToString().Trim()); //添加主键参数
}
sbwhere.Remove(sbwhere.Length - 3, 3);
}
string strSql = string.Format("select count(*) from [" + strTableName + "] {0}", sbwhere);
using (DbConnection Connection = new DBFactory().GetInstance())
{
Connection.Open();
return(Convert.ToInt32(Connection.Query <int>(strSql, parems, null, false, null, CommandType.Text).ToList()[0]) > 0 ? Enums.TickLingEnum.Existence : Enums.TickLingEnum.NonExistence);
}
}
catch (Exception ex)
{
return(Enums.TickLingEnum.Abnormity);
}
}
/// <summary>
/// 查询数据(集合)
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="sql">SQL 语句</param>
/// <returns>返回结果集</returns>
public List <T> SelectListBySql <T>(string sql)
{
using (DbConnection Connection = new DBFactory().GetInstance())
{
Connection.Open();
return(SqlMapper.Query <T>(Connection, sql, null, null, true, null, CommandType.Text).ToList());
}
}
/// <summary>
/// 修改数据(根据主键)
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="updateKeyValuePair">要修改字段的键值对</param>
/// <param name="id">主键值</param>
/// <returns>是否成功</returns>
public Enums.TickLingEnum UpdateMultiColumnByID <T>(Hashtable updateKeyValuePair, string id, IDbTransaction transaction = null, DbConnection sqlConnection = null)
{
if (string.IsNullOrEmpty(id))
{
return(Enums.TickLingEnum.Fail);
}
Type typeFromHandle = typeof(T);
string strTableName = typeFromHandle.Name;
StringBuilder sbColumns = new StringBuilder();
StringBuilder sbwhere = new StringBuilder();
var parems = new DynamicParameters();
//捕获主键
foreach (PropertyInfo item in typeFromHandle.GetProperties())
{
if (item.IsDefined(typeof(PrimaryKeyAttribute), false))
{
sbwhere.AppendFormat(" {0} = @{1} ", item.Name, item.Name);
parems.Add("@" + item.Name, id); //添加主键参数
}
}
//生成需要修改的字段和参数
foreach (DictionaryEntry item in updateKeyValuePair)
{
if (item.Value != null)
{
sbColumns.AppendFormat(" {0} = @{1} ,", item.Key, item.Key);
parems.Add("@" + item.Key, item.Value.ToString().Trim());
}
else
{
sbColumns.Append(item.Key + "=null,");
}
}
sbColumns.Remove(sbColumns.Length - 1, 1);
string strSql = string.Format("update [" + strTableName + "] set {0} where {1}", sbColumns, sbwhere);
using (DbConnection Connection = new DBFactory().GetInstance())
{
Connection.Open();
if (transaction == null)
{
//普通字段修改
return(Connection.Execute(strSql, parems, null, null, CommandType.Text) > 0 ? Enums.TickLingEnum.Success : Enums.TickLingEnum.Fail);
}
else
{
//执行事务
return(sqlConnection.Execute(strSql, parems, transaction, null, CommandType.Text) > 0 ? Enums.TickLingEnum.Success : Enums.TickLingEnum.Fail);
}
}
}
/// <summary>
/// 修改数据(根据条件)
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="updateKeyValuePair">要修改字段的键值对</param>
/// <param name="where">修改条件</param>
/// <returns>是否成功</returns>
public Enums.TickLingEnum UpdateMultiColumnByWhere <T>(Hashtable updateKeyValuePair, string where, IDbTransaction transaction = null, DbConnection sqlConnection = null)
{
try
{
if (string.IsNullOrEmpty(where))
{
return(Enums.TickLingEnum.Fail);
}
Type typeFromHandle = typeof(T);
string strTableName = typeFromHandle.Name;
StringBuilder sbColumns = new StringBuilder();
var parems = new DynamicParameters();
//生成需要修改的字段和参数
foreach (DictionaryEntry item in updateKeyValuePair)
{
if (item.Value != null)
{
sbColumns.Append(item.Key + "=@" + item.Key + ",");
parems.Add("@" + item.Key, item.Value.ToString().Trim());
}
else
{
sbColumns.Append(item.Key + "=null,");
}
}
sbColumns.Remove(sbColumns.Length - 1, 1);
string strSql = string.Format("update [" + strTableName + "] set {0} where {1}", sbColumns, where);
using (DbConnection Connection = new DBFactory().GetInstance())
{
Connection.Open();
if (transaction == null)
{
//普通字段修改
return(Connection.Execute(strSql, parems, null, null, CommandType.Text) > 0 ? Enums.TickLingEnum.Success : Enums.TickLingEnum.Fail);
}
else
{
//执行事务
return(sqlConnection.Execute(strSql, parems, transaction, null, CommandType.Text) > 0 ? Enums.TickLingEnum.Success : Enums.TickLingEnum.Fail);
}
}
}
catch (Exception)
{
return(Enums.TickLingEnum.Abnormity);
}
}
/// <summary>
/// 实体对象添加,返回主键
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="model">需要插入的实例</param>
/// <param name="ht">需要判重字段</param>
/// <returns></returns>
public Enums.TickLingEnum Insert <T>(T model, Hashtable columnTable)
{
try
{
Type typeFromHandle = typeof(T);
string strTableName = typeFromHandle.Name;
StringBuilder sbColumns = new StringBuilder();
StringBuilder sbParameters = new StringBuilder();
Enums.TickLingEnum isExis = CheckColumnValueIsExist <T>(columnTable);
if (isExis == Enums.TickLingEnum.Existence)
{
return(isExis);
}
//将对象的所有属性和值转换成SQL参数形式
foreach (PropertyInfo item in typeFromHandle.GetProperties())
{
// add by zfj 2015-7-6 注释，待测试
if (item.IsDefined(typeof(PrimaryKeyAttribute), false) && (item.GetValue(model, null) == null)) //如果包含该主键,但主键为空时
{
item.SetValue(model, Guid.NewGuid().ToString(), null);
}
//if (item.Name.ToLower() == "createdate" && item.GetValue(model, null) == null)
//{
// continue;
//}
//if (item.Name.ToLower() == "rowid")
// continue;
if (item.IsDefined(typeof(ExtensionAttribute), false))
{
continue;
}
sbColumns.Append(item.Name + ",");
sbParameters.Append("@" + item.Name + ",");
}
string strSql = string.Format("insert into [" + strTableName + "]({0}) values({1})", sbColumns.ToString().Trim(','), sbParameters.ToString().Trim(','));
using (DbConnection Connection = new DBFactory().GetInstance())
{
Connection.Open();
return(Connection.Execute(strSql, model, null, null, CommandType.Text) > 0 ? Enums.TickLingEnum.Success : Enums.TickLingEnum.Fail);
}
}
catch (Exception ex)
{
return(Enums.TickLingEnum.Abnormity);
}
}
/// <summary>
/// 删除数据（根据主键）
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="id">主键id值</param>
/// <returns>是否成功</returns>
public Enums.TickLingEnum Delete <T>(string id)
{
try
{
if (string.IsNullOrEmpty(id))
{
return(Enums.TickLingEnum.Fail);
}
Type typeFromHandle = typeof(T);
string strTableName = typeFromHandle.Name;
StringBuilder sbWhere = new StringBuilder();
var dynamicParameters = new DynamicParameters();
var flag = false;//标识是否找到主键
foreach (PropertyInfo item in typeFromHandle.GetProperties())
{
if (item.IsDefined(typeof(PrimaryKeyAttribute), false)) //如果包含该主键
{
//获取主键条件
sbWhere.Append(item.Name + "=@" + item.Name);
dynamicParameters.Add(item.Name, id);
flag = true;
}
}
if (!flag) //未获取到了主键
{
return(Enums.TickLingEnum.Fail); //没有获取到主键
}
string strSql = string.Format("delete from [" + strTableName + "] where {0}", sbWhere);
using (DbConnection Connection = new DBFactory().GetInstance())
{
Connection.Open();
return(Connection.Execute(strSql, dynamicParameters, null, null, CommandType.Text) > 0 ? Enums.TickLingEnum.Success : Enums.TickLingEnum.Fail);
}
}
catch (Exception ex)
{
return(Enums.TickLingEnum.Abnormity);
}
}
/// <summary>
/// 删除数据（根据条件）
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="Where">删除条件</param>
/// <returns></returns>
public Enums.TickLingEnum DeleteWhere <T>(string Where)
{
try
{
if (string.IsNullOrEmpty(Where))
{
return(Enums.TickLingEnum.Fail);
}
Type typeFromHandle = typeof(T);
string strTableName = typeFromHandle.Name;
string strSql = string.Format("delete from [" + strTableName + "] where {0}", Where);
using (DbConnection Connection = new DBFactory().GetInstance())
{
Connection.Open();
return(Connection.Execute(strSql, null, null, null, CommandType.Text) > 0 ? Enums.TickLingEnum.Success : Enums.TickLingEnum.Fail);
}
}
catch (Exception ex)
{
return(Enums.TickLingEnum.Abnormity);
}
}
/// <summary>
/// 查询数据(实体)
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="where">拼接的where条件字符串</param>
/// <returns>返回结果集</returns>
public T SelectEntity <T>(string where)
{
try
{
Type typeFromHandle = typeof(T);
string strTableName = typeFromHandle.Name;
if (!string.IsNullOrEmpty(where))
{
where = " where " + where;
}
string sql = string.Format("select * from [" + strTableName + "] {0}", where);
using (DbConnection Connection = new DBFactory().GetInstance())
{
Connection.Open();
return(SqlMapper.Query <T>(Connection, sql, null, null, true, null, CommandType.Text).FirstOrDefault <T>());
}
}
catch (Exception ex)
{
return(default(T));
}
}
/// <summary>
/// 判断重复
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="id">主键</param>
/// <returns>是否存在</returns>
public Enums.TickLingEnum IsExists <T>(string where, string id)
{
try
{
Type typeFromHandle = typeof(T);
string strTableName = typeFromHandle.Name;
string strwhere = where;
if (!string.IsNullOrEmpty(id))
{
strwhere += " and id='" + id + "'";
}
string strSql = string.Format("select count(*) from [" + strTableName + "] where {0}", strwhere);
using (DbConnection Connection = new DBFactory().GetInstance())
{
Connection.Open();
return(Convert.ToInt32(Connection.Query <int>(strSql, null, null, false, null, CommandType.Text).ToList()[0]) > 0 ? Enums.TickLingEnum.Existence : Enums.TickLingEnum.NonExistence);
}
}
catch (Exception ex)
{
return(Enums.TickLingEnum.Abnormity);
}
}