public int Update <T>(Hashtable ht, Expression <Func <T, bool> > condition) where T : class, new()
{
StringBuilder sb = new StringBuilder();
sb.Append($" Update {typeof(T).GetMappingAttributeName()} SET");
bool isFirstValue = true;
foreach (string key in ht.Keys)
{
if (ht[key] != null)
{
if (isFirstValue)
{
isFirstValue = false;
sb.Append($"{key}=?{key}");
}
else
{
sb.Append($",{key}=?{key}");
}
}
}
sb.Append(" Where ").Append(ExpressionToSql.GetWhereByLambda(condition));
return(ExecuteNonQuery(sb.ToString(), DatabaseCommon.GetParameter(ht)));
}
/// <summary>
/// 实体插入(dapper)不作实现
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entity"></param>
/// <returns></returns>
public int Insert <T>(T entity) where T : class
{
string sql = DatabaseCommon.InsertSql <T>(entity).ToString();
DbParameter[] parameter = DatabaseCommon.GetParameter <T>(entity);
int res = ExecuteBySql(sql, parameter);
return(res);
}
/// <summary>
/// 实体插入
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entity"></param>
/// <returns></returns>
public int Insert <T>(T entity) where T : class
{
int res = 0;
if (DbTransaction == null)
{
string sql = DatabaseCommon.InsertSql <T>(entity).ToString();
//DbParameter[] parameter = DatabaseCommon.GetParameter<T>(entity);
using (var connection = Connection)
{
res = connection.Execute(sql, entity);
}
}
else
{
string sql = DatabaseCommon.InsertSql <T>(entity).ToString();
DbParameter[] parameter = DatabaseCommon.GetParameter <T>(entity);
res = DbTransaction.Connection.Execute(sql, parameter, DbTransaction);
}
return(res);
}
/// <summary>
/// 更新一条数据
/// </summary>
/// <param name="entity">实体对象</param>
/// <returns></returns>
public int Update <T>(T entity) where T : class
{
int res = 0;
if (SqlTransaction == null)
{
string sql = DatabaseCommon.UpdateSql <T>(entity).ToString();
DbParameter[] parameter = DatabaseCommon.GetParameter <T>(entity);
using (var conn = Connection)
{
res = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, DatabaseCommon.DbParameterToSqlParameter(parameter));
}
}
else
{
string sql = DatabaseCommon.UpdateSql <T>(entity).ToString();
DbParameter[] parameter = DatabaseCommon.GetParameter <T>(entity);
res = SqlHelper.ExecuteNonQuery(SqlTransaction, CommandType.Text, sql, DatabaseCommon.DbParameterToSqlParameter(parameter));
}
return(res);
}
public int Update <T>(T entity) where T : class
{
return(ExecuteBySql(DatabaseCommon.UpdateSql <T>(entity).ToString(), DatabaseCommon.GetParameter <T>(entity)));
}
/// <summary>
/// 实体插入(dapper)不作实现
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entity"></param>
/// <returns></returns>
public int Insert<T>(T entity) where T : class
{
return ExecuteBySql(DatabaseCommon.InsertSql<T>(entity).ToString(), DatabaseCommon.GetParameter<T>(entity));
}
/// <summary>
/// 解析XML字符串格式
/// </summary>
/// <param name="Xml"></param>
/// <param name="arrSql">返回SQL</param>
/// <param name="arrParam">返回参数化</param>
/// <param name="Action">操作动作</param>
private void AnalysisXml(string Xml, out ArrayList arrSql, out ArrayList arrParam, out string Action)
{
Xml = GZipHelper.Uncompress(Xml);
ArrayList _arrSql = new ArrayList();
ArrayList _arrParam = new ArrayList();
string _Action = "-1";
try
{
XmlDocument xd = new XmlDocument();
xd.LoadXml(Xml);
foreach (XmlNode node in xd.ChildNodes)
{
if (node.Name.Trim() == "Request")
{
foreach (XmlNode subnode in node.ChildNodes)
{
if (subnode.Name.Trim() == "data")
{
//根据接口代码获取对象
Base_InterfaceManage interfacemanage = Repository().FindEntity("Code", subnode.Attributes["code"].Value);
if (interfacemanage.InterfaceId != null)
{
_Action = interfacemanage.Action; //动作类型
string Constraint = interfacemanage.Constraints; //约束达式
foreach (XmlNode node_data in subnode.ChildNodes)
{
if (node_data.Name.Trim() == "no")
{
string pkName;
string pkVal;
switch (_Action)
{
case "Insert": //新增
Hashtable ht_add = this.XmlNodeToHashtable(node_data);
_arrSql.Add(DatabaseCommon.InsertSql(Constraint.Trim(), ht_add));
_arrParam.Add(DatabaseCommon.GetParameter(ht_add));
break;
case "Update": //修改
Hashtable ht_edit = this.XmlNodeToHashtable(node_data, out pkName, out pkVal);
_arrSql.Add(new StringBuilder(Constraint.Trim()));
_arrParam.Add(DatabaseCommon.GetParameter(ht_edit));
break;
case "Delete": //删除
Hashtable ht_Delete = this.XmlNodeToHashtable(node_data, out pkName, out pkVal);
_arrSql.Add(DatabaseCommon.DeleteSql(Constraint.Trim(), pkName));
_arrParam.Add(DbFactory.CreateDbParameter("@" + pkName, pkVal));
break;
case "Select": //查询
Hashtable ht_Param = this.XmlNodeToHashtable(node_data);
_arrSql.Add(Constraint.Trim());
_arrParam.Add(DatabaseCommon.GetParameter(ht_Param));
break;
case "Procedure": //存储过程
Hashtable ExecuteByProc = this.XmlNodeToHashtable(node_data);
_arrSql.Add(Constraint.Trim());
_arrParam.Add(ExecuteByProc);
break;
case "DataTableProc": //存储过程-DataTableProc
Hashtable DataTableProc = this.XmlNodeToHashtable(node_data);
_arrSql.Add(Constraint.Trim());
_arrParam.Add(DataTableProc);
break;
case "DataSetProc": //存储过程-DataSetProc
Hashtable DataSetProc = this.XmlNodeToHashtable(node_data);
_arrSql.Add(Constraint.Trim());
_arrParam.Add(DataSetProc);
break;
case "-1": //异常信息
break;
default:
break;
}
}
}
}
else
{
log.Error(Xml.ToString() + "\r\n接口不存在\r\n");
}
}
}
break;
}
}
}
catch (Exception e)
{
log.Error(Xml.ToString() + "\r\n" + e.Message + "\r\n");
}
finally
{
arrSql = _arrSql;
arrParam = _arrParam;
Action = _Action;
}
}
public int UpdateNew <T>(T entity, bool isBatch = false) where T : class
{
return(ExecuteBySql(DatabaseCommon.UpdateSql <T>(entity).ToString(), DatabaseCommon.GetParameter <T>(entity)));
}
public void ImportExcel(string fid, DataTable dt, out DataTable Result)
{
//获得导入模板
//模板主表
ExcelImportEntity base_excellimport = GetEntity(fid);
//string pkName = DatabaseCommon.GetKeyField("LeaRun.Entity." + base_excellimport.ImportTable).ToString();
//模板明细表
var listBase_ExcelImportDetail = GetDetails(fid);
//构造导入返回结果表
DataTable Newdt = new DataTable("Result");
foreach (ExcelImportFiledEntity excelImportDetail in listBase_ExcelImportDetail)
{
if (excelImportDetail.F_RelationType == 0 || excelImportDetail.F_RelationType == 2 || excelImportDetail.F_RelationType == 3)
{
Newdt.Columns.Add(excelImportDetail.F_ColName, typeof(System.String));
}
}
Newdt.Columns.Add("learunColOk", typeof(System.String)); //位置
Newdt.Columns.Add("learunColError", typeof(System.String)); //原因
//取出要插入的表名
string tableName = base_excellimport.F_DbTable; ////////////////
if (dt != null && dt.Rows.Count > 0)
{
IRepository db = this.BaseRepository().BeginTrans();
try
{
#region 遍历Excel数据行
//bool IsOk = true;
int learunColOk = 1;
string strError = "";
foreach (DataRow item in dt.Rows)
{
Hashtable entity = new Hashtable();//最终要插入数据库的hashtable
StringBuilder sb = new StringBuilder();
//entity[pkName] = Guid.NewGuid().ToString();//首先给主键赋值
DataRow dr = Newdt.NewRow();
dr = Newdt.NewRow();
#region 遍历模板，为每一行中每个字段找到模板列并赋值
int i = 0;
foreach (ExcelImportFiledEntity excelImportDetail in listBase_ExcelImportDetail)
{
string value = "";
if (excelImportDetail.F_RelationType == 0 || excelImportDetail.F_RelationType == 2 || excelImportDetail.F_RelationType == 3)
{
value = item[excelImportDetail.F_ColName].ToString();
dr[i] = value;
}
#region 单个字段赋值
switch (excelImportDetail.F_RelationType)
{
//字符串
case 0:
entity[excelImportDetail.F_FliedName] = value;
i++;
break;
//GUID
case 1:
entity[excelImportDetail.F_FliedName] = Guid.NewGuid().ToString();
break;
//数据字典
case 2:
i++;
entity[excelImportDetail.F_DbSaveFlied] = value;
//查询Excel字符串是否存在于外表
sb = DatabaseCommon.SelectSql("Base_DataItem");
sb.Append(" and " + excelImportDetail.F_DataItemEncode + "='" + value + "'");
DataTable dt0 = this.BaseRepository().FindTable(sb.ToString());
if (dt0.Rows.Count == 0)
{
//不存在此外键
learunColOk = 0;
strError += "【 数据字典 】 找不到对应的数据";
}
sb.Remove(0, sb.Length);
break;
//数据表
case 3:
i++;
entity[excelImportDetail.F_DbSaveFlied] = value;
//查询Excel字符串是否存在于外表
sb = DatabaseCommon.SelectSql(tableName);
sb.Append(" and " + excelImportDetail.F_DbSaveFlied + "='" + value + "'");
DataTable dt1 = this.BaseRepository().FindTable(sb.ToString());
if (dt1.Rows.Count == 0)
{
//不存在此外键
learunColOk = 0;
strError += "【" + excelImportDetail.F_ColName + "】 找不到对应的数据";
}
sb.Remove(0, sb.Length);
break;
//固定数值
case 4:
entity[excelImportDetail.F_FliedName] = excelImportDetail.F_Value;
break;
//操作人
case 5:
entity[excelImportDetail.F_FliedName] = OperatorProvider.Provider.Current().UserId;
break;
//操作人名字
case 6:
entity[excelImportDetail.F_FliedName] = OperatorProvider.Provider.Current().UserName;
break;
//操作人时间
case 7:
entity[excelImportDetail.F_FliedName] = DateTime.Now;
break;
default:
break;
}
#endregion 单字段赋值结束
}
dr[i] = learunColOk;
dr[i + 1] = strError;
#endregion 遍历模板结束
//写入表
if (learunColOk == 0)
{
continue;
}
StringBuilder strSql = DatabaseCommon.InsertSql(tableName, entity);
DbParameter[] parameter = DatabaseCommon.GetParameter(entity);
Newdt.Rows.Add(dr);
db.ExecuteBySql(strSql.ToString(), parameter);
}
#endregion 遍历Excel数据行结束
db.Commit();
}
catch (System.Exception)
{
db.Rollback();
throw;
}
}
Result = Newdt;
}
public int Update <T>(T entity, string pkName) where T : class
{
return(ExecuteNonQuery(DatabaseCommon.UpdateSql <T>(entity, pkName).ToString(), DatabaseCommon.GetParameter <T>(entity)));
}
public int Delete <T>(T entity) where T : class
{
return(ExecuteNonQuery(DatabaseCommon.DeleteEntitySql <T>(entity).ToString(), DatabaseCommon.GetParameter <T>(entity)));
}
