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