/// <summary> /// 实体批量插入 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="connection"></param> /// <param name="entities"></param> /// <param name="transaction"></param> /// <param name="timeout"></param> /// <returns></returns> public int Insert <T>(IDbConnection connection, List <T> entities, IDbTransaction transaction = null, int?timeout = Timeout) where T : class { int res = 0; this.Logger(this.GetType(), "实体批量插入-Insert", () => { if (entities != null && entities.Count > 0) { string sql = DatabaseCommon.InsertSql <T>(entities.FirstOrDefault()).ToString(); res = connection.Execute(sql, entities, transaction, timeout); } }, e => { if (transaction != null) { transaction.Rollback(); } connection.Close(); connection.Dispose(); }, () => { }); return(res); }
/// <summary> /// 实体批量插入 /// </summary> /// <typeparam name="T">动态对象</typeparam> /// <param name="entities">实体集合</param> /// <param name="timeout">超时时间</param> /// <returns></returns> public int BatchInsert <T>(List <T> entities, int?timeout = Timeout) where T : IEntity { int res = 0; this.Logger(this.GetType(), "实体批量插入-BatchInsert", () => { if (entities != null && entities.Count > 0) { string sql = DatabaseCommon.InsertSql <T>(entities.First()).ToString(); if (this.BaseDbTransaction == null) { using (IDbConnection connection = this.BaseDbConnection) { res = connection.Execute(sql, entities, null, timeout, CommandType.Text); } } else { res = this.BaseDbTransaction.Connection.Execute(sql, entities, this.BaseDbTransaction, timeout, CommandType.Text); } } }, e => { this.Rollback(); }); return(res); }
/// <summary> /// 实体插入 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="connection"></param> /// <param name="entity"></param> /// <param name="transaction"></param> /// <param name="timeout"></param> /// <returns></returns> public int Insert <T>(IDbConnection connection, T entity, IDbTransaction transaction = null, int?timeout = Timeout) where T : class { int res = 0; this.Logger(this.GetType(), "实体插入-Insert", () => { if (entity != null) { string sql = DatabaseCommon.InsertSql <T>(entity).ToString(); res = connection.Execute(sql, entity, transaction, timeout); } }, e => { if (transaction != null) { transaction.Rollback(); } connection.Close(); connection.Dispose(); }, () => { }); return(res); }
/// <summary> /// 实体插入 /// </summary> /// <typeparam name="T">动态对象</typeparam> /// <param name="entity">实体</param> /// <param name="timeout">超时时间</param> /// <returns></returns> public int Insert <T>(T entity, int?timeout = Timeout) where T : IEntity { int res = 0; this.Logger(this.GetType(), "实体插入-Insert", () => { if (entity != null) { string sql = DatabaseCommon.InsertSql <T>(entity).ToString(); if (this.BaseDbTransaction == null) { using (IDbConnection connection = this.BaseDbConnection) { res = connection.Execute(sql, entity, null, timeout, CommandType.Text); } } else { res = this.BaseDbTransaction.Connection.Execute(sql, entity, this.BaseDbTransaction, timeout, CommandType.Text); } } }, e => { this.Rollback(); }); return(res); }
/// <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="entities"></param> /// <returns></returns> public int Insert <T>(List <T> entities) where T : class { int res = 0; if (entities.Count > 0) { string sql = DatabaseCommon.InsertSql <T>(entities.FirstOrDefault()).ToString(); //DbParameter[] parameter = DatabaseCommon.GetParameter<T>(entity); using (var connection = Connection) { res = connection.Execute(sql, entities); } } 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 Insert <T>(T entity) where T : class { int res = 0; if (SqlTransaction == null) { string sql = DatabaseCommon.InsertSql <T>(entity).ToString(); DbParameter[] parameter = DatabaseCommon.GetParameter <T>(entity); using (var connection = Connection) { res = SqlHelper.ExecuteNonQuery(connection, CommandType.Text, sql, DatabaseCommon.DbParameterToSqlParameter(parameter)); } } else { string sql = DatabaseCommon.InsertSql <T>(entity).ToString(); DbParameter[] parameter = DatabaseCommon.GetParameter <T>(entity); res = SqlHelper.ExecuteNonQuery(SqlTransaction, CommandType.Text, sql, DatabaseCommon.DbParameterToSqlParameter(parameter)); //res = this.Commit(); } return(res); }
/// <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 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; }