/// <summary>
/// 查询动态表分页
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public BaseResultModel <ResultModel> ListPageDynamicModelList(TableSelModel model)
{
try
{
System.Data.DataTable selTable = this.ModelRepository.ListPageDynamicModelList(model);
if (selTable.Columns.Contains(model.OrderByName ?? "~!@"))
{
selTable.DefaultView.Sort = model.OrderByName + " ASC";
selTable = selTable.DefaultView.ToTable();
}
int dataCount = (selTable?.Rows?.Count + string.Empty).ToInt(0);
int?totalPages = 0;
if (dataCount > 0)
{
model.PageSize = model.PageSize ?? 999999;
totalPages = (dataCount + model.PageSize - 1) / model.PageSize;
}
selTable = DynamicTableHelper.GetPagedTable(selTable, model.PageNo ?? 1, model.PageSize ?? 999999);
return(new SuccessResultModel <ResultModel>(new ResultModel()
{
DT = selTable, dataCount = dataCount, totalPages = totalPages
}));
}
catch (Exception e)
{
LogWriter.WriteLog(EnumLogLevel.Fatal, "ListViewPageDynamicModelList", JsonConvert.SerializeObject(model), "Model", "查询动态表视图分页.", e);
//return new ErrorResultModel<ResultModel>(EnumErrorCode.系统异常, "查询动态表视图分页!");
return(new ErrorResultModel <ResultModel>(EnumErrorCode.系统异常, e.Message));
}
}
/// <summary>
/// 新增动态模块表
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public BaseResultModel <int> InsertDynamicModel(TableSelModel model)
{
if (model == null || model.TableName.IsNullOrEmpty() || model.ColSel.Count == 0)
{
return(new ErrorResultModel <int>(EnumErrorCode.参数校验未通过, "新增表名列名不能为空!"));
}
return(ModelService.InsertDynamicModel(model));
}
/// <summary>
/// 查询动态表分页
/// </summary>
/// <param name="model">ViewModel</param>
/// <returns>ViewModel</returns>
public BaseResultModel <ResultModel> ListPageDynamicModelList(TableSelModel model)
{
if (model == null || model.TableName.IsNullOrEmpty())
{
return(new ErrorResultModel <ResultModel>(EnumErrorCode.系统异常, "传入表名不能为空!"));
}
return(ModelService.ListPageDynamicModelList(model));
}
/// <summary>
/// 获取动态表数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public DataTable ListPageDynamicModelList(TableSelModel model)
{
string sql = @"select * from " + model.TableName + " where isdelete=0 {0} ";
Collection <IDataParameter> parms = new Collection <IDataParameter>();
StringBuilder sqlWhere = new StringBuilder();
foreach (TableColSelModel item in model.WhereSel)
{
sqlWhere.Append($@" AND {item.ColName} like @{item.ColName}");
parms.Add(new SqlParameter($"@{item.ColName}", "%" + item.ColValue + "%"));
}
return(base.DataHelper.FillDataTable(string.Format(sql, sqlWhere), parms));
}
/// <summary>
/// 获取动态连接表数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public DataTable ListPageDynamicJoinModelList(TableSelModel model)
{
string joinTableID = model.JoinMasterTableName.Replace("tb_", "") + "ID";
string sql = @"select * from " + model.TableName + " TB left join " + model.JoinMasterTableName + " joinTB on TB.dynamicID=joinTB." + joinTableID + " where TB.isdelete=0 and joinTB.isdelete=0 {0} ";
Collection <IDataParameter> parms = new Collection <IDataParameter>();
StringBuilder sqlWhere = new StringBuilder();
foreach (TableColSelModel item in model.WhereSel)
{
sqlWhere.Append($@" AND TB.{item.ColName} like @{item.ColName}");
parms.Add(new SqlParameter($"@{item.ColName}", "%" + item.ColValue + "%"));
}
return(base.DataHelper.FillDataTable(string.Format(sql, sqlWhere), parms));
}
public void InsertDynamicModel()
{
// 新增表
ModelInputModel inputMaster = new ModelInputModel()
{
ModelCode = "tb_test",
ModelName = "测试表"
};
BaseResultModel <ModelOutputModel> resultMaster = this.ModelService.ModifyModel(inputMaster);
Assert.IsTrue(resultMaster.IsSuccess, resultMaster.ErrorMessage);
// 新增动态表字段
ModelDetailInputModel inputModel = new ModelDetailInputModel()
{
ColIndex = 1,
ColMemo = "AA",
ColName = "BB",
ColType = "nvarchar(256)",
ModelID = resultMaster?.Data?.ModelID
};
BaseResultModel <ModelDetailOutputModel> resultDetail = this.ModelDetailService.ModifyModelDetail(inputModel);
Assert.IsTrue(resultDetail.IsSuccess, resultDetail.ErrorMessage);
// 新增动态表数据
TableSelModel selTable = new TableSelModel()
{
TableName = inputMaster.ModelCode,
ColSel = new List <TableColSelModel>()
{
new TableColSelModel()
{
ColName = inputModel.ColName,
ColValue = "BBValue"
}
}
};
BaseResultModel <int> result = this.ModelService.InsertDynamicModel(selTable);
Assert.IsTrue(result.IsSuccess, result.ErrorMessage);
}
/// <summary>
/// 新增动态模块表
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public BaseResultModel <int> InsertDynamicModel(TableSelModel model)
{
string sql = @"
INSERT INTO [dbo].[{0}]({1} isDelete)
VALUES ({2} 0)";
Collection <IDataParameter> parms = new Collection <IDataParameter>();
StringBuilder sqlCol = new StringBuilder();
StringBuilder sqlColValue = new StringBuilder();
foreach (TableColSelModel col in model.ColSel)
{
sqlCol.Append(col.ColName + " , ");
sqlColValue.Append("@" + col.ColName + " , ");
parms.Add(new SqlParameter("@" + col.ColName, col.ColValue));
}
sql = string.Format(sql, model.TableName, sqlCol, sqlColValue);
return(new SuccessResultModel <int>(base.DataHelper.ExecuteNonQuery(sql, parms)));
}
/// <summary>
/// 新增动态模块表
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public BaseResultModel <int> InsertDynamicModel(TableSelModel model)
{
try
{
if (!model.ColSel.Exists(x => x.ColName.ToLower() == (model.TableName.Replace("tb_", "") + "ID").ToLower()))
{
model.ColSel.Add(new TableColSelModel
{
ColName = model.TableName.Replace("tb_", "") + "ID",
ColValue = Guid.NewGuid() + string.Empty
});
}
return(this.ModelRepository.InsertDynamicModel(model));
}
catch (Exception e)
{
LogWriter.WriteLog(EnumLogLevel.Fatal, "InsertDynamicModel", JsonConvert.SerializeObject(model), "Model", "新增动态模块表 异常.", e);
//return new ErrorResultModel<int>(EnumErrorCode.系统异常, "新增动态模块表 异常!");
return(new ErrorResultModel <int>(EnumErrorCode.系统异常, e.Message));
}
}
public void ListPageDynamicJoinModelList()
{
// 新增表
ModelInputModel inputMaster = new ModelInputModel()
{
ModelCode = "tb_test",
ModelName = "测试表"
};
BaseResultModel <ModelOutputModel> resultMaster = this.ModelService.ModifyModel(inputMaster);
Assert.IsTrue(resultMaster.IsSuccess, resultMaster.ErrorMessage);
ModelInputModel inputSon = new ModelInputModel()
{
ModelCode = "tb_testDetail",
ModelName = "测试子表"
};
BaseResultModel <ModelOutputModel> resultSon = this.ModelService.ModifyModel(inputSon);
Assert.IsTrue(resultSon.IsSuccess, resultSon.ErrorMessage);
// 新增动态表字段
ModelDetailInputModel inputSonModel = new ModelDetailInputModel()
{
ColIndex = 1,
ColMemo = "备注",
ColName = "dynamicID",
ColType = "nvarchar(256)",
ModelID = resultSon?.Data?.ModelID
};
BaseResultModel <ModelDetailOutputModel> resultSonDetail = this.ModelDetailService.ModifyModelDetail(inputSonModel);
Assert.IsTrue(resultSonDetail.IsSuccess, resultSonDetail.ErrorMessage);
// 新增动态表数据
string tempGuid = Guid.NewGuid() + string.Empty;
TableSelModel selMasterTable = new TableSelModel()
{
TableName = inputMaster.ModelCode,
ColSel = new List <TableColSelModel>()
{
new TableColSelModel()
{
ColName = "testID",
ColValue = tempGuid
}
}
};
BaseResultModel <int> resultMasterTab = this.ModelService.InsertDynamicModel(selMasterTable);
Assert.IsTrue(resultMasterTab.IsSuccess, resultMasterTab.ErrorMessage);
TableSelModel selSonTable = new TableSelModel()
{
TableName = inputSon.ModelCode,
ColSel = new List <TableColSelModel>()
{
new TableColSelModel()
{
ColName = "dynamicID",
ColValue = tempGuid
}
}
};
BaseResultModel <int> resultSonTab = this.ModelService.InsertDynamicModel(selSonTable);
Assert.IsTrue(resultSonTab.IsSuccess, resultSonTab.ErrorMessage);
// 查询动态表分页
TableSelModel selTableList = new TableSelModel()
{
PageNo = 1,
PageSize = 20,
TableName = inputSon.ModelCode,
JoinMasterTableName = inputMaster.ModelCode,
WhereSel = new List <TableColSelModel>()
{
new TableColSelModel()
{
ColName = "dynamicID",
ColValue = tempGuid
}
}
};
BaseResultModel <ResultModel> resultTableList = this.ModelService.ListPageDynamicJoinModelList(selTableList);
Assert.IsTrue(resultTableList.IsSuccess && resultTableList.Data.dataCount > 0, resultTableList.ErrorMessage);
}