/// <summary> /// 删除符合条件的实体 /// </summary> /// <param name="predicate"></param> /// <returns></returns> public int DeleteByCondition(Expression <Func <TElement, bool> > predicate, IDbTransaction transaction = null) { TElement entity = new TElement(); string tableInDbName; System.Reflection.PropertyInfo[] propertys; string[] filelds; string[] paras; var sqlFieldMapping = ResolveEntity(entity, true, out tableInDbName, out propertys, out filelds, out paras); if (filelds.Length <= 1) { //除主键后 没有其他字段 return(-1); throw new Exception("未指定除主键后其他字段!"); } //解析查询条件 var whereStr = "1=1"; if (null != predicate) { whereStr = ResolveLambdaTreeToCondition.ConvertLambdaToCondition <TElement>(predicate, sqlFieldMapping, wrapperChar: this.FieldWrapperChar); } StringBuilder sb_Sql = new StringBuilder(); sb_Sql.AppendFormat("delete from {0} ", tableInDbName); if (null != predicate) { sb_Sql.AppendFormat("where {0} ", whereStr); } var sqlCmd = sb_Sql.ToString(); try { this.SqlOutPutToLogAsync(sqlCmd); using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig)) { var result = conn.Execute(sqlCmd, null, transaction); return(result); } } catch (Exception ex) { throw ex; } }
/// <summary> /// 删除一个实体 /// </summary> /// <param name="entity"></param> /// <param name="transaction"></param> /// <returns></returns> public int Delete(TElement entity, IDbTransaction transaction = null) { string tableInDbName; System.Reflection.PropertyInfo[] propertys; string[] filelds; string[] paras; var sqlFieldMapping = ResolveEntity(entity, true, out tableInDbName, out propertys, out filelds, out paras); var identityKey = sqlFieldMapping.Filelds.Where(x => x.FieldColumnName == EntityIdentityFiledName).FirstOrDefault(); if (null == identityKey) { //除主键后 没有其他字段 return(-1); throw new Exception("未指定主键字段!"); } var primaryValue = ReflectionHelper.GetPropertyValue(entity, identityKey.PropertyName); StringBuilder sb_Sql = new StringBuilder(); sb_Sql.AppendFormat("delete from {0} ", tableInDbName); sb_Sql.AppendFormat(" where {0}={1};", EntityIdentityFiledName, primaryValue); var sqlCmd = sb_Sql.ToString(); //清理构建器 sb_Sql.Clear(); sb_Sql = null; try { this.SqlOutPutToLogAsync(sqlCmd, entity); using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig)) { var result = conn.Execute(sqlCmd, null, transaction); return(result); } } catch (Exception ex) { throw ex; } }
/// <summary> /// 插入 实体 /// </summary> /// <typeparam name="TElement"></typeparam> /// <param name="entity">实体对象</param> /// <param name="transaction">db事务</param> /// <returns></returns> public long Insert(TElement entity, IDbTransaction transaction = null) { string tableInDbName; System.Reflection.PropertyInfo[] propertys; string[] filelds; string[] paras; ResolveEntity(entity, true, out tableInDbName, out propertys, out filelds, out paras); ///不含主键的属性 var noIdentityPropertys = propertys.Remove(x => x.Name == EntityIdentityFiledName); var noIdentityFileds = filelds.Remove(x => x == EntityIdentityFiledName); var noIdentityParas = paras.Remove(x => x.ToLower() == string.Format("@{0}", EntityIdentityFiledName.ToLower())); string splitor = string.Format("{0},{0}", this.FieldWrapperChar); var fieldSplitString = string.Concat(this.FieldWrapperChar, string.Join(splitor, noIdentityFileds), this.FieldWrapperChar); //返回逗号分隔的字符串 例如:`ProvinceCode`,`ProvinceName` var parasSplitString = string.Join(",", noIdentityParas); //参数 数组 的逗号分隔 StringBuilder sb_Sql = new StringBuilder(); sb_Sql.Append(string.Format("insert into {0}(", tableInDbName)); sb_Sql.Append(string.Format("{0})", fieldSplitString)); sb_Sql.Append(" values ("); sb_Sql.Append(string.Format("{0})", parasSplitString)); sb_Sql.Append(";select @@IDENTITY;"); var sqlCmd = sb_Sql.ToString(); ///清理掉字符串拼接构造器 sb_Sql.Clear(); sb_Sql = null; this.SqlOutPutToLogAsync(sqlCmd, entity); using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig)) { var result = conn.ExecuteScalar <long>(sqlCmd, entity, transaction); return(result); } }
/// <summary> ///执行命令 并返回首列结果 /// </summary> /// <param name="cmdText"></param> /// <param name="commandParameters"></param> /// <param name="cmdType"></param> /// <returns></returns> public object ExecuteScalar(string cmdText, DbParameter[] commandParameters, CommandType cmdType = CommandType.Text) { object val = null; this.SqlOutPutToLogAsync(cmdText, commandParameters); using (DbConnection conn = DatabaseFactory.GetDbConnection(this._dbConfig)) { DbCommand cmd = DatabaseFactory.GetDbDbCommand(this._dbConfig); try { if (conn.State != ConnectionState.Open) { conn.Open(); } if (conn.State != ConnectionState.Open) { conn.Open(); } PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); } catch (Exception ex) { throw ex; } finally { cmd.Dispose(); conn.Close(); conn.Dispose(); } } return(val); }
/// <summary> /// 执行命令 并返回一个可读的Reader /// </summary> /// <param name="cmdText"></param> /// <param name="commandParameters"></param> /// <param name="cmdType"></param> /// <returns></returns> public DbDataReader ExecuteReader(string cmdText, DbParameter[] commandParameters, CommandType cmdType = CommandType.Text) { this.SqlOutPutToLogAsync(cmdText, commandParameters); try { DbConnection conn = DatabaseFactory.GetDbConnection(this._dbConfig); if (conn.State != ConnectionState.Open) { conn.Open(); } DbCommand cmd = DatabaseFactory.GetDbDbCommand(this._dbConfig); PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); return(rdr); } catch (Exception ex) { throw ex; } }
/// <summary> /// 执行分页查询的核心方法:支持单表和多表分页 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="condition">执行条件</param> /// <returns></returns> public override PagedSqlDataResult <T> PageQuery <T>(PagedSqlCondition condition) { PagedSqlDataResult <T> pageData = null; if (null == condition) { return(pageData); } string errMsg = ""; if (!condition.IsValid(out errMsg)) { throw new Exception("分页查询错误:" + errMsg); } //记录打出日志 this.SqlOutPutToLogAsync(Contanst.PageSql_Call_Name, condition); try { //判断结果集类型 if (null == condition.TableOptions) { //简单sql语句类型判断 if (condition.TableNameOrSqlCmd.ToLower().Contains("select") && condition.TableNameOrSqlCmd.ToLower().Contains("from")) { condition.TableOptions = PageTableOptions.SqlScripts; } else { condition.TableOptions = PageTableOptions.TableOrView; } } //动态查询需要包装外层 if (condition.TableOptions == PageTableOptions.SqlScripts) { //condition.TableNameOrSqlCmd = condition.TableNameOrSqlCmd.Replace("'", "''"); condition.TableNameOrSqlCmd = string.Format(" ( {0} ) as tmpTable ", condition.TableNameOrSqlCmd); } //完整的sql参数转化 var fullPagerSqlParas = new DynamicParameters(); fullPagerSqlParas.Add("@PageIndex", condition.PageNumber - 1); fullPagerSqlParas.Add("@PageSize", condition.PageSize); fullPagerSqlParas.Add("@PrimaryKey", condition.PrimaryKey); //主键 fullPagerSqlParas.Add("@TableNameOrSqlCmd", condition.TableNameOrSqlCmd); //将查询结果结合作为分页的表数据 fullPagerSqlParas.Add("@SortField", condition.SortField); fullPagerSqlParas.Add("@SelectFields", condition.SelectFields); fullPagerSqlParas.Add("@ConditionWhere", condition.ConditionWhere); fullPagerSqlParas.Add("@IsDesc", condition.IsDesc == true ? 1 : 0); string pageSqlTemplate = PagerSQLProcedure.PAGE_SQL_CORE; string pagerSql = string.Empty; if (null != condition.SqlParameters) { //参数列表字符串--声明 //参数列表 -逗号分隔 string[] sqlParaToken = null; string parasStr = this.GetParamSqlTokenToSqlParas(condition.TableNameOrSqlCmd, out sqlParaToken); string paraKeyValueString = string.Empty; string paraDeinfine = this.GetSqlServerParamDefineString(condition.SqlParameters, sqlParaToken, out paraKeyValueString); string defWithWraper = string.Format("N'{0}'", paraDeinfine); string defDotWraper = string.Format("N',{0}'", paraDeinfine); if (!string.IsNullOrEmpty(paraDeinfine)) { pagerSql = string.Format(pageSqlTemplate, " ", defWithWraper, defDotWraper, paraKeyValueString);//需要带参数 } else { pagerSql = string.Format(pageSqlTemplate, "--", "N''", "N''", " ");//不用带参数 } //将参数整体注入到动态参数 fullPagerSqlParas.AddDynamicParams(condition.SqlParameters); } else { //fullPagerSqlParas.Add("@IsParamQuery", false); pagerSql = string.Format(pageSqlTemplate, "--", "N''", "N''", " ");//不用带参数 } //string using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig)) { //多部分结果 var multiResult = conn.QueryMultiple(pagerSql, fullPagerSqlParas); if (null != multiResult) { pageData = multiResult.ReadFirstOrDefault <PagedSqlDataResult <T> >();//分页信息 if (null != pageData) { pageData.DataList = multiResult.Read <T>().AsList();//结果行 } } //---------废弃的存储过程---------- ////////////////////var dataList = conn.Query<T>(pagerSql, fullPagerSqlParas, commandType: CommandType.Text).AsList(); ////////////////////if (dataList.IsNotEmpty()) ////////////////////{ //////////////////// pageData = new PagedSqlDataResult<T>(dataList); //////////////////// //查询完毕后 根据输出参数 返回总记录数 总页数 //////////////////// pageData.TotalRows = fullPagerSqlParas.Get<int>("@TotalRecords"); //////////////////// pageData.TotalPages = fullPagerSqlParas.Get<int>("@TotalPageCount"); ////////////////////} } } catch (Exception ex) { throw ex; } return(pageData); }
/// <summary> /// 通过特定的条件查询出元素集合 /// </summary> /// <param name="predicate"></param> /// <returns></returns> public List <TElement> GetElementsByCondition(Expression <Func <TElement, bool> > predicate) { TElement entity = new TElement(); string tableInDbName; System.Reflection.PropertyInfo[] propertys; string[] filelds; string[] paras; var sqlFieldMapping = ResolveEntity(entity, false, out tableInDbName, out propertys, out filelds, out paras); if (filelds.Length <= 1) { //除主键后 没有其他字段 return(null); throw new Exception("未指定除主键后其他字段!"); } //获取字段 //List<string> fieldAlias = new List<string>(); //foreach (var item in sqlFieldMapping.Filelds) //{ // var ailasName = string.Format("{0} as {1}", item.FieldColumnName, item.PropertyName); // fieldAlias.Add(ailasName); //} //获取字段 var fieldSplitString = "*";//entity.GetSqlQueryFieldsWithAlias();// String.Join(",", fieldAlias);//返回逗号分隔的字符串 例如:ProvinceCode,ProvinceName,Submmary //解析查询条件 string whereStr = "1=1"; if (null != predicate) { whereStr = ResolveLambdaTreeToCondition.ConvertLambdaToCondition <TElement>(predicate, sqlFieldMapping); } StringBuilder sb_Sql = new StringBuilder(); sb_Sql.AppendFormat("select {0} ", fieldSplitString); sb_Sql.AppendFormat(" from {0} ", tableInDbName); sb_Sql.AppendFormat(" where {0};", whereStr); var sqlCmd = sb_Sql.ToString(); sb_Sql.Clear(); sb_Sql = null; List <TElement> dataLst = null; try { this.SqlOutPutToLogAsync(sqlCmd); using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig)) { dataLst = conn.Query <TElement>(sqlCmd).AsList(); } } catch (Exception ex) { throw ex; } return(dataLst); }
/// <summary> /// 更新元素 通过 符合条件的 /// (更新机制为,模型载体设置的值的字段会被更新掉,不设置值 不更新) /// </summary> /// <param name="entity"></param> /// <param name="predicate"></param> /// <param name="transaction"></param> /// <returns></returns> public int UpdateByCondition(TElement entity, Expression <Func <TElement, bool> > predicate, IDbTransaction transaction = null) { string tableInDbName; System.Reflection.PropertyInfo[] propertys; string[] filelds; string[] paras; var sqlFieldMapping = ResolveEntity(entity, true, out tableInDbName, out propertys, out filelds, out paras); if (filelds.Length <= 1) { //除主键后 没有其他字段 return(-1); throw new Exception("未指定除主键后其他字段!"); } StringBuilder sb_FiledParaPairs = new StringBuilder(""); ///解析要更新的列 var settedValueDic = entity.GetSettedValuePropertyDic(); foreach (var item in settedValueDic) { var keyProperty = item.Key; //var value = item.Value; if (keyProperty != EntityIdentityFiledName) { string fieldName = ResolveLambdaTreeToCondition.SearchPropertyMappingField(sqlFieldMapping, keyProperty); sb_FiledParaPairs.AppendFormat("{1}{0}{1}=@{2},", fieldName, this.FieldWrapperChar, keyProperty); } } //移除最后一个逗号 var str_FiledParaPairs = sb_FiledParaPairs.ToString(); str_FiledParaPairs = str_FiledParaPairs.Remove(str_FiledParaPairs.Length - 1); StringBuilder sb_Sql = new StringBuilder(); sb_Sql.Append(string.Format("update {0} set ", tableInDbName)); //Set Table sb_Sql.Append(str_FiledParaPairs); //参数对 if (null != predicate) { string where = ResolveLambdaTreeToCondition.ConvertLambdaToCondition <TElement>(predicate, sqlFieldMapping); sb_Sql.Append(" where "); //解析条件 sb_Sql.Append(where); //条件中带有参数=值的 拼接字符串 } var sqlCmd = sb_Sql.ToString(); ///清理字符串构建 sb_FiledParaPairs.Clear(); sb_FiledParaPairs = null; sb_Sql.Clear(); sb_Sql = null; this.SqlOutPutToLogAsync(sqlCmd, entity); using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig)) { var result = conn.Execute(sqlCmd, entity, transaction); return(result); } }
/// <summary> /// 单次批量多次插入多个实体 /// (注意:sqlbuck插入,高效率sqlbuck方式插入) /// </summary> /// <param name="entities"></param> /// <param name="transaction"></param> /// <returns></returns> public bool InsertMulitiEntities(IEnumerable <TElement> entities, IDbTransaction transaction = null) { var result = -1; var count_entities = entities.Count(); if (count_entities <= 0) { return(false); } string tableInDbName; System.Reflection.PropertyInfo[] propertys; string[] filelds; string[] paras; ResolveEntity(entities.First(), true, out tableInDbName, out propertys, out filelds, out paras); try { this.SqlOutPutToLogAsync("InsertMulitiEntities", entities); ///不含主键的属性 var noIdentityPropertys = propertys.Remove(x => x.Name.ToLower() == EntityIdentityFiledName.ToLower()); using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig)) { if (conn.State != ConnectionState.Open) { conn.Open(); } if (null == transaction) { transaction = conn.BeginTransaction(); } using (var bulk = new SqlBulkCopy(conn as SqlConnection, SqlBulkCopyOptions.Default, (SqlTransaction)transaction)) { bulk.BulkCopyTimeout = 120;//命令超时时间 bulk.BatchSize = 1000; //指定写入的目标表 bulk.DestinationTableName = tableInDbName; //数据源中的列名与目标表的属性的映射关系 //bulk.ColumnMappings.Add("ip", "ip"); //bulk.ColumnMappings.Add("port", "port"); //bulk.ColumnMappings.Add("proto_name", "proto_name"); //bulk.ColumnMappings.Add("strategy_id", "strategy_id"); //init mapping foreach (var pi in noIdentityPropertys) { bulk.ColumnMappings.Add(pi.Name, pi.Name); } DataTable dt = SqlDataTableExtensions.ConvertListToDataTable <TElement>(entities, ref noIdentityPropertys);//数据源数据 //DbDataReader reader = dt.CreateDataReader(); bulk.WriteToServer(dt); if (null != transaction) { transaction.Commit(); } } } result = 1; } catch (Exception ex) { if (null != transaction) { transaction.Rollback(); } //抛出Native 异常信息 throw ex; } var isSuccess = result > 0 ? true : false; return(isSuccess); }
/// <summary> /// 执行分页查询的核心方法:支持单表和多表分页 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="condition">执行条件</param> /// <returns></returns> public override PagedSqlDataResult <T> PageQuery <T>(PagedSqlCondition condition) { PagedSqlDataResult <T> pageData = null; if (null == condition) { return(pageData); } string errMsg = ""; if (!condition.IsValid(out errMsg)) { throw new Exception("分页查询错误:" + errMsg); } //记录打出日志 this.SqlOutPutToLogAsync(Contanst.PageSql_Call_Name, condition); try { if (condition.PageNumber <= 0) { condition.PageNumber = 1; } //判断结果集类型 if (null == condition.TableOptions) { //简单sql语句类型判断 if (condition.TableNameOrSqlCmd.ToLower().Contains("select") && condition.TableNameOrSqlCmd.ToLower().Contains("from")) { condition.TableOptions = PageTableOptions.SqlScripts; } else { condition.TableOptions = PageTableOptions.TableOrView; } } //动态查询需要包装外层 if (condition.TableOptions == PageTableOptions.SqlScripts) { //condition.TableNameOrSqlCmd = condition.TableNameOrSqlCmd.Replace("'", "''"); string sqlCmd = condition.TableNameOrSqlCmd.Trim(); if (sqlCmd[sqlCmd.Length - 1] == ';') { sqlCmd = sqlCmd.TrimEnd(';'); } condition.TableNameOrSqlCmd = string.Format(" ( {0} ) as tmpTable ", condition.TableNameOrSqlCmd); } //# 分页sql 字符串 string countSql = string.Format("select count(*) as TotalRows, 0 as TotalPages from {0} {1} ;", condition.TableNameOrSqlCmd, condition.ConditionWhere); string bodySql = string.Concat( "select " , condition.SelectFields , " from " , condition.TableNameOrSqlCmd , condition.ConditionWhere , " order by " , condition.SortField.IsNullOrEmpty() ? condition.PrimaryKey : condition.SortField , condition.IsDesc ? " DESC " : " ASC " , " limit " , (condition.PageNumber - 1) * condition.PageSize , ',' , condition.PageSize ); string pagerSql = string.Concat(countSql, bodySql, ";"); //string using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig)) { //多部分结果 var multiResult = conn.QueryMultiple(pagerSql, condition.SqlParameters); if (null != multiResult) { pageData = multiResult.ReadFirstOrDefault <PagedSqlDataResult <T> >();//分页信息 if (pageData.TotalRows > 0) { //计算分页总页数 if (pageData.TotalRows < condition.PageSize) { pageData.TotalPages = 1; } else { if (pageData.TotalRows % condition.PageSize > 0) { pageData.TotalPages = pageData.TotalRows / condition.PageSize + 1; } else { pageData.TotalPages = pageData.TotalRows / condition.PageSize; } } } if (null != pageData) { pageData.DataList = multiResult.Read <T>().AsList();//结果行 } } } } catch (Exception ex) { throw ex; } return(pageData); }
/// <summary> /// Sql 语句批量执行 /// </summary> /// <param name="SqlCmdList"></param> /// <returns></returns> public bool SqlBatchExcute(Dictionary <string, DbParameter[]> sqlCmdList) { bool result = false; if (null == sqlCmdList || sqlCmdList.Count <= 0) { return(result); } using (DbConnection conn = DatabaseFactory.GetDbConnection(this._dbConfig)) { conn.Open(); using (DbTransaction trans = conn.BeginTransaction()) { try { var entor = sqlCmdList.GetEnumerator(); StringBuilder sbSqlCmdBagList = new StringBuilder(); var sqlParasBagList = new List <DbParameter>(); int pos = 0; while (entor.MoveNext() || pos >= sqlCmdList.Count) { //到达sql参数上限或者达到命令列表边界 执行sql 包 if (sqlParasBagList.Count >= MAX_SQL_PARAS_LIMIT || pos >= sqlCmdList.Count) { using (DbCommand cmd = conn.CreateCommand()) { //到达最大参数临界的时候 执行sql 批语句 cmd.Transaction = trans; cmd.CommandText = sbSqlCmdBagList.ToString(); cmd.Parameters.AddRange(sqlParasBagList.ToArray()); cmd.ExecuteNonQuery(); } //重置 sbSqlCmdBagList.Clear(); sqlParasBagList.Clear(); if (pos >= sqlCmdList.Count) { break;//超出临界 跳出循环 } } var cmdPair = entor.Current; if (string.IsNullOrEmpty(cmdPair.Key)) { continue; } string sqlCmd = cmdPair.Key; DbParameter[] sqlParas = cmdPair.Value; if (!sqlCmd.TrimEnd().EndsWith(";")) { sqlCmd += ";"; } //处理sql 命令,和参数,对sql命令中的@ 和参数中的@的进行匹配识别 if (null == sqlParas || sqlParas.Length <= 0) { sbSqlCmdBagList.Append(sqlCmd); } else { //按照计数游标 对参数进行处理 foreach (DbParameter itemDbPara in sqlParas) { var paraName = itemDbPara.ParameterName; string nameWithPos = string.Concat(paraName, pos); itemDbPara.ParameterName = nameWithPos; sqlCmd = sqlCmd.Replace(paraName, nameWithPos); } sbSqlCmdBagList.Append(sqlCmd);//追加格式化 后缀的sql 命令 sqlParasBagList.AddRange(sqlParas); } pos += 1;//计数后移 } trans.Commit(); result = true; } catch (Exception ex) { result = false; trans.Rollback(); throw ex; } } } return(result); }
/// <summary> /// 获取此上下文的新的db连接对象 /// </summary> /// <returns></returns> public IDbConnection GetCurrentDbConnection() { return(DatabaseFactory.GetDbConnection(this._dbConfig)); }