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