///// <summary> ///// 插入(添加) ///// </summary> ///// <param name="entity">实体</param> ///// <param name="isBackId">是否返回主键</param> ///// <returns></returns> //public object Insert(TEntity entity, bool isBackId) //{ // //参数列表 // List<SqlParameter> listPara = new List<SqlParameter>(); // string insertStr = "";//插入字段 // string paraStr = "";//参数 // //获取实体列的特性 // List<EntityPropColumnAttributes> columnAttrList = AttributeHelper.GetEntityColumnAtrributes<TEntity>(); // foreach (PropertyInfo prop in entity.GetType().GetProperties()) // { // EntityPropColumnAttributes columnAttribute = columnAttrList.Where(w => w.propName.ToLower() == prop.Name.ToLower()).First(); // //当不为自动增长时 // if (columnAttribute.isDbGenerated == false) // { // object val = prop.GetValue(entity, null); // if (val != null) // { // insertStr += columnAttribute.fieldName + ",";//字符拼接 // paraStr += "@" + columnAttribute.fieldName + ",";//字符拼接 // listPara.Add(new SqlParameter("@" + columnAttribute.fieldName, val));//参数添加 // } // } // } // //去掉最后的逗号 // insertStr = insertStr.TrimEnd(','); // paraStr = paraStr.TrimEnd(','); // //拼接SQL语句 // string cmdText = "insert into " + tableName + "(" + insertStr + ") values(" + paraStr + ");"; // if (isBackId) // { // cmdText += "SELECT @@IDENTITY AS ID;"; // return SqlConvertHelper.ExecuteScalar(cmdText, listPara); // } // else // { // return SqlConvertHelper.ExecuteNonQuery(cmdText, listPara); // } //} #endregion /// <summary> /// 插入(添加) /// </summary> /// <param name="entity">实体</param> /// <param name="isBackId">是否返回主键</param> /// <returns></returns> public object Insert(TEntity entity, bool isBackId) { var entityList = new List <TEntity>() { entity }; DBModel dBModel = GetInsertInfo(entityList); //实体事件--变更前 EventHelper.Instance.Execute(ChangingEventKey, entity); object result = null; if (isBackId) { dBModel.SQL += "SELECT @@IDENTITY AS ID;"; result = SqlConvertHelper.GetInstallSqlHelper(OrgCode).ExecuteScalar(dBModel.SQL, dBModel.Params); } else { result = SqlConvertHelper.GetInstallSqlHelper(OrgCode).ExecuteNonQuery(dBModel.SQL, dBModel.Params); } //实体事件--变更后 EventHelper.Instance.Execute(ChangedEventKey, entity); return(result); }
/// <summary> /// 大数据量插入 /// </summary> /// <param name="entityList">实体列表</param> /// <param name="isPkIdentity">是否保留标志源</param> /// <returns></returns> public bool Insert(List <TEntity> entityList, bool isPkIdentity) { //转换为DataTable DataTable dt = EntityListToDataTable(entityList); return(SqlConvertHelper.GetInstallSqlHelper(OrgCode).BatchCopyInsert(dt.TableName, dt, isPkIdentity)); }
/// <summary> /// 删除执行 /// </summary> /// <param name="coreFrameworkEntity"></param> /// <returns></returns> private int DeleteExecute(CoreFrameworkEntity coreFrameworkEntity) { //参数列表 List <SqlParameter> listPara = new List <SqlParameter>(); listPara.AddRange(coreFrameworkEntity.ParaList); //实体事件--变更前 TEntity entity = Get(PrimaryKey); EventHelper.Instance.Execute(ChangingEventKey, entity); //拼接SQL语句 string cmdText = "delete from " + TableName + " where " + coreFrameworkEntity.Where; int result = SqlConvertHelper.GetInstallSqlHelper(OrgCode).ExecuteNonQuery(cmdText, listPara); //设置缓存 object id = GetPrimaryKeyValue(entity); Cache.BusinessCachesHelper <TEntity> .RemoveEntityCache(id); //实体事件--变更后 EventHelper.Instance.Execute(ChangedEventKey, entity); return(result); }
/// <summary> /// 修改 /// </summary> /// <param name="entity">实体</param> /// <param name="express">表达式</param> /// <returns></returns> private int UpdateExecte(TEntity entity, CoreFrameworkEntity coreFrameworkEntity) { DBModel dbModel = GetUpdateInfo(entity); if (dbModel == null) { return(0); } dbModel.Params.AddRange(coreFrameworkEntity.ParaList); dbModel.SQL += " where " + coreFrameworkEntity.Where; //实体事件--变更前 EventHelper.Instance.Execute(ChangingEventKey, entity); //执行 int result = SqlConvertHelper.GetInstallSqlHelper(OrgCode).ExecuteNonQuery(dbModel.SQL, dbModel.Params); //设置缓存 object id = GetPrimaryKeyValue(entity); Cache.BusinessCachesHelper <TEntity> .AddEntityCache(id, entity); //实体事件--变更后 EventHelper.Instance.Execute(ChangedEventKey, entity); return(result); }
/// <summary> /// 公共搜索 /// </summary> /// <param name="coreFrameworkEntity"></param> /// <param name="count"></param> /// <param name="selectFields"></param> /// <param name="orderBy"></param> /// <returns></returns> private List <TEntity> CommonSearch(CoreFrameworkEntity coreFrameworkEntity, int?count = null, string selectFields = null, string orderBy = null) { coreFrameworkEntity.Where = string.IsNullOrEmpty(coreFrameworkEntity.Where) ? "1=1" : coreFrameworkEntity.Where; //条件 selectFields = string.IsNullOrEmpty(selectFields) ? "*" : selectFields; //查询字段 orderBy = string.IsNullOrEmpty(orderBy) ? "" : "order by " + orderBy; //排序 string topStr = count.HasValue == false ? "" : (" top " + count); //Top StringBuilder cmdText = new StringBuilder(); cmdText.Append("select"); cmdText.Append(" "); cmdText.Append(topStr); cmdText.Append(" "); cmdText.Append(selectFields); cmdText.Append(" "); cmdText.Append("from"); cmdText.Append(" "); cmdText.Append(TableName); cmdText.Append(" "); cmdText.Append("where"); cmdText.Append(" "); cmdText.Append(coreFrameworkEntity.Where); cmdText.Append(" "); cmdText.Append(orderBy); IDataReader sdr = SqlConvertHelper.GetInstallSqlHelper(OrgCode).ExecuteReader(cmdText.ToString(), coreFrameworkEntity.ParaList); return(DynamicBuilder <TEntity> .GetList(sdr, columnAttrList)); }
/// <summary> /// 传递参数分别是:表名,主键,页面大小,分页码,条件,查询总数,参数 /// </summary> /// <param name="tableName"></param> /// <param name="primaryKey"></param> /// <param name="selectValue"></param> /// <param name="pageSize"></param> /// <param name="pageIndex"></param> /// <param name="where"></param> /// <param name="orderBy"></param> /// <param name="recordCount"></param> /// <param name="spr"></param> /// <returns></returns> public DataSet GetPagerInfo(string tableName, string primaryKey, string selectValue, int pageSize, int pageIndex, string where, string orderBy, ref int recordCount, List <SqlParameter> spr) { where = string.IsNullOrEmpty(where) ? "1=1" : where; orderBy = string.IsNullOrEmpty(orderBy) ? (" Order By " + primaryKey + " ASC") : (" Order By " + orderBy); string cmdText = "select count(" + primaryKey + ") from " + tableName + " where " + where; recordCount = SqlConvertHelper.GetInstallSqlHelper().ExecuteScalar(cmdText, spr).ToInt(); //当页码为1,或小于1时 if (pageIndex <= 1) { cmdText = "select top " + pageSize + " " + selectValue + " from " + tableName + " where " + where + " " + orderBy; } else { string pageCmdText = @" SELECT {0} FROM ( SELECT ROW_NUMBER() OVER ( {5} ) rowindex,{0} FROM {1} WHERE {2} )_t WHERE rowindex BETWEEN {3} AND {4} ORDER BY rowindex "; int StartIndex = pageSize * (pageIndex - 1); int EndIndex = pageSize * pageIndex; cmdText = string.Format(pageCmdText, selectValue, tableName, where, StartIndex, EndIndex, orderBy); } return(SqlConvertHelper.GetInstallSqlHelper().GetDataSet(cmdText, spr)); }
/// <summary> /// Get执行 /// </summary> /// <param name="coreFrameworkEntity"></param> /// <returns></returns> private TEntity GetExecute(CoreFrameworkEntity coreFrameworkEntity) { //拼接SQL语句 string cmdText = "select * from " + TableName + " where " + coreFrameworkEntity.Where; IDataReader sdr = SqlConvertHelper.GetInstallSqlHelper(OrgCode).ExecuteReader(cmdText, coreFrameworkEntity.ParaList); List <TEntity> list = DynamicBuilder <TEntity> .GetList(sdr, columnAttrList); return(list.Count > 0 ? list.First() : default(TEntity)); }
/// <summary> /// 传递参数分别是:表名,主键,页面大小,分页码,条件,查询总数,参数 /// </summary> /// <param name="tableName"></param> /// <param name="selectValue"></param> /// <param name="pageSize"></param> /// <param name="pageIndex"></param> /// <param name="where"></param> /// <param name="orderBy"></param> /// <param name="recordCount"></param> /// <param name="spr"></param> /// <returns></returns> public IDataReader GetPagerInfo(string tableName, string selectValue, int pageSize, int pageIndex, string where, string orderBy, ref int recordCount, List <SqlParameter> spr) { where = string.IsNullOrEmpty(where) ? "1=1" : where; string cmdText = "select count(1) from " + tableName + " where " + where; recordCount = SqlConvertHelper.GetInstallSqlHelper().ExecuteScalar(cmdText, spr).ToInt(); //当页码为1,或小于1时 if (pageIndex <= 1) { cmdText = "select top " + pageSize + " " + selectValue + " from " + tableName + " where " + where + " " + orderBy; } else { cmdText = GetPagerSql(tableName, selectValue, pageSize, pageIndex, where, orderBy); } return(SqlConvertHelper.GetInstallSqlHelper().ExecuteReader(cmdText, spr)); }
/// <summary> /// 批量插入(添加) /// </summary> /// <param name="entityList">实体列表</param> /// <returns></returns> public object BatchInsert(List <TEntity> entityList) { int pageSize = 100; int pageCount = entityList.Count / pageSize; for (int i = 0; i < pageCount; i++) { List <TEntity> list = entityList.Skip(i * pageSize).Take(pageSize).ToList(); DBModel dBModel = GetInsertInfo(list); //实体事件--变更前 EventHelper.Instance.Execute(ChangingEventKey, list); //执行 SqlConvertHelper.GetInstallSqlHelper(OrgCode).ExecuteNonQuery(dBModel.SQL, dBModel.Params); //实体事件--变更后 EventHelper.Instance.Execute(ChangedEventKey, list); } return(true); }
/// <summary> /// SQL命令语句,返回第一行第一列的值 /// </summary> /// <param name="cmdText"></param> /// <returns></returns> public string SQLExecuteScalar(string cmdText, List <SqlParameter> listPara) { return(SqlConvertHelper.GetInstallSqlHelper(OrgCode).ExecuteScalar(cmdText, listPara)); }
/// <summary> /// SQL命令语句,返回所影响的行数 /// </summary> /// <param name="cmdText"></param> /// <returns></returns> public int SQLExecuteNonQuery(string cmdText, List <SqlParameter> listPara) { return(SqlConvertHelper.GetInstallSqlHelper(OrgCode).ExecuteNonQuery(cmdText, listPara)); }
/// <summary> /// SQL命令语句 /// </summary> /// <param name="cmdText"></param> /// <returns></returns> public DataSet SQLGetDataSet(string cmdText, List <SqlParameter> listPara) { return(SqlConvertHelper.GetInstallSqlHelper(OrgCode).GetDataSet(cmdText, listPara)); }
/// <summary> /// SQL命令语句 /// </summary> /// <param name="cmdText"></param> /// <returns></returns> public List <TEntity> SQLSearch(string cmdText, List <SqlParameter> listPara) { IDataReader sdr = SqlConvertHelper.GetInstallSqlHelper(OrgCode).ExecuteReader(cmdText, listPara); return(DynamicBuilder <TEntity> .GetList(sdr, columnAttrList)); }