/// <summary>
/// 获取删除DbCmd
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="filter">过滤条件</param>
/// <param name="subTableArg">表名称参数.如果TableAttr设置Name.则根据Name格式化</param>
/// <returns></returns>
internal DbCommand GetDeleteDbCmd <T>(QueryFilter filter, string subTableArg = null)
{
DbCommand dbCommand = CreateDbCommand();
#region 设置DbCommand
string sqlStr = "Delete From {0} ";
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
if (filter == null || filter.FilterParameters.Count <= 0)
{
throw new Exception("条件删除时,删除条件不能为空.");
}
sqlStr += filter.FilterSQLString;
for (int i = 0; i < filter.FilterParameters.Count; i++)
{
DbParameter dbParameter = dbCommand.CreateParameter();
dbParameter.Direction = ParameterDirection.Input;
dbParameter.ParameterName = filter.FilterParameters[i].ParameterName;
dbParameter.Value = filter.FilterParameters[i].ParameterValue;
dbParameter.DbType = filter.FilterParameters[i].ParameterDbType;
dbCommand.Parameters.Add(dbParameter);
}
dbCommand.CommandText = string.Format(sqlStr, dtoDbMapping.GetTableName(subTableArg));
#endregion
return(dbCommand);
}
/// <summary>
/// 获取建表Sql
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="subTableArg">表名称,如果为空,则使用T对应表名称</param>
/// <returns></returns>
public override string GetCreateTableSql <T>(string subTableArg = null)
{
//表名 查询字段名 主键字段名
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
List <PiMap> piMapList = DtoMappingHelper.GetPiMapList <T>();
string tableName = dtoDbMapping.GetTableName(subTableArg);
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append($"Create table [{tableName}](\r\n");
for (int i = 0; i < piMapList.Count; i++)
{
if (i < piMapList.Count - 1)
{
sqlBuilder.Append(CreateField(piMapList[i]));
}
else
{
sqlBuilder.Append(CreateField(piMapList[i], true));
}
}
sqlBuilder.Append(")\r\n");
return(sqlBuilder.ToString());
}
/// <summary>
/// 获取查询DbCommand
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <param name="piMapList">查询属性MapList</param>
/// <param name="subTableArg">表名称参数.如果TableAttr设置Name.则根据Name格式化</param>
/// <returns></returns>
internal DbCommand GetQueryByIdDbCommand <T>(object id, List <PiMap> piMapList, string subTableArg = null)
{
DbCommand dbCommand = CreateDbCommand();
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
string sqlStr = "Select {1} From {0} where {2}";
string selectParams = null;
string whereParams = null;
foreach (PiMap piMap in piMapList)
{
selectParams += string.IsNullOrEmpty(selectParams) ? piMap.FieldName : "," + piMap.FieldName;
}
DbParameter dbParameter = dbCommand.CreateParameter();
dbParameter.Direction = ParameterDirection.Input;
dbParameter.ParameterName = "@" + dtoDbMapping.PkMap.FieldName;
dbParameter.Value = id;
dbParameter.DbType = dtoDbMapping.PkMap.DbType;
dbCommand.Parameters.Add(dbParameter);
whereParams = dtoDbMapping.PkMap.FieldName + "=@" + dtoDbMapping.PkMap.FieldName;
dbCommand.CommandText = string.Format(sqlStr, dtoDbMapping.GetTableName(subTableArg), selectParams, whereParams);
return(dbCommand);
}
/// <summary>
/// 获取查询DbCommand
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="filter"></param>
/// <param name="subTableArg">表名称参数.如果TableAttr设置Name.则根据Name格式化</param>
/// <returns></returns>
internal DbCommand GetQueryDbCommand <T>(QueryFilter filter = null, string subTableArg = null)
{
DbCommand dbCommand = CreateDbCommand();
string sqlStr = "Select {1} From {0}";
string selectParams = null;
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
List <PiMap> piMapList = DtoMappingHelper.GetPiMapList <T>(filter);
foreach (PiMap piMap in piMapList)
{
selectParams += string.IsNullOrEmpty(selectParams) ? piMap.FieldName : "," + piMap.FieldName;
}
if (filter != null && filter.ItemList.Count > 0)
{
sqlStr += filter.FilterSQLString;
}
if (filter != null && !string.IsNullOrEmpty(filter.OrderSQLString))
{
sqlStr += filter.OrderSQLString;
}
if (filter != null)
{
for (int i = 0; i < filter.FilterParameters.Count; i++)
{
DbParameter dbParameter = dbCommand.CreateParameter();
dbParameter.Direction = ParameterDirection.Input;
dbParameter.ParameterName = filter.FilterParameters[i].ParameterName;
dbParameter.Value = filter.FilterParameters[i].ParameterValue;
dbParameter.DbType = filter.FilterParameters[i].ParameterDbType;
dbCommand.Parameters.Add(dbParameter);
}
}
dbCommand.CommandText = string.Format(sqlStr, dtoDbMapping.GetTableName(subTableArg), selectParams);
return(dbCommand);
}
/// <summary>
/// 检查表是否已存在
/// </summary>
/// <typeparam name="T">T对象</typeparam>
/// <returns></returns>
public bool CheckTableExists <T>() where T : class, new()
{
bool result = false;
string subTableArg = this.GetSubTableArg <T>();
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
string tableName = dtoDbMapping.GetTableName(subTableArg);
using (DbCommand dbCommand = dbProvider.GetCheckTableExistsDbCommand(tableName))
{
try
{
SetDbConnection(dbCommand);
using (DbDataReader dr = dbCommand.ExecuteReader())
{
result = dr.HasRows;
dr.Close();
}
CloseDbConnection(dbCommand);
}
catch (Exception ex)
{
CloseDbConnection(dbCommand);
throw ex;
}
}
return(result);
}
/// <summary>
/// 获取字段求和DbCommand 返回Total列
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="filter"></param>
/// <param name="subTableArg">表名称参数.如果TableAttr设置Name.则根据Name格式化</param>
/// <returns></returns>
internal DbCommand GetMaxDbCommand <T>(QueryFilter filter = null, string subTableArg = null)
{
DbCommand dbCommand = CreateDbCommand();
string sqlStr = "Select Max({1}) as Total From {0}";
string selectParams = null;
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
PiMap piMap = DtoMappingHelper.GetPiMapList <T>(filter).FirstOrDefault();
if (piMap == null)
{
throw new Exception("计算字段不能为空");
}
selectParams = piMap.FieldName;
if (filter != null && filter.ItemList.Count > 0)
{
sqlStr += filter.FilterSQLString;
}
if (filter != null && filter.FilterParameters.Count > 0)
{
for (int i = 0; i < filter.FilterParameters.Count; i++)
{
DbParameter dbParameter = dbCommand.CreateParameter();
dbParameter.Direction = ParameterDirection.Input;
dbParameter.ParameterName = filter.FilterParameters[i].ParameterName;
dbParameter.Value = filter.FilterParameters[i].ParameterValue;
dbParameter.DbType = filter.FilterParameters[i].ParameterDbType;
dbCommand.Parameters.Add(dbParameter);
}
}
dbCommand.CommandText = string.Format(sqlStr, dtoDbMapping.GetTableName(subTableArg), selectParams);
return(dbCommand);
}
/// <summary>
/// 获取建表Sql
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="subTableArg">表名称,如果为空,则使用T对应表名称</param>
/// <returns></returns>
public override string GetCreateTableSql <T>(string subTableArg = null)
{
//表名 查询字段名 主键字段名
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
List <PiMap> piMapList = DtoMappingHelper.GetPiMapList <T>();
string tableName = dtoDbMapping.GetTableName(subTableArg);
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append($"Create table public.{tableName}(\r\n");
for (int i = 0; i < piMapList.Count; i++)
{
if (i < piMapList.Count - 1)
{
sqlBuilder.Append(CreateField(piMapList[i]));
}
else
{
sqlBuilder.Append(CreateField(piMapList[i], true));
}
}
sqlBuilder.Append(")\r\n");
for (int i = 0; i < piMapList.Count; i++)
{
if (piMapList[i].IsIgnore != true)
{
FieldAttribute attr = piMapList[i].FieldAttr;
if (!string.IsNullOrEmpty(attr.DisplayText))
{
sqlBuilder.Append($" comment on column public.{tableName}.{piMapList[i].FieldName} is '{attr.DisplayText}';\r\n");
}
}
}
return(sqlBuilder.ToString());
}
/// <summary>
/// 获取导入DbCmd 导入时,自带Id
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dto"></param>
/// <param name="piMapList">查询属性MapList</param>
/// <param name="subTableArg">表名称参数.如果TableAttr设置Name.则根据Name格式化</param>
/// <returns></returns>
internal DbCommand GetImportDbCmd <T>(T dto, List <PiMap> piMapList, string subTableArg = null) where T : class, new()
{
DbCommand dbCommand = CreateDbCommand();
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
#region 设置DbCommand
string sqlStr = "Insert into {0} ({1}) values({2})";
string fieldParams = null;
string valueParams = null;
foreach (PiMap piMap in piMapList)
{
PropertyInfo pi = piMap.Pi;
if (piMap.FieldAttr.ReadOnly)
{
continue; //跳过只读字段
}
fieldParams = string.IsNullOrEmpty(fieldParams) ? piMap.FieldName : fieldParams + "," + piMap.FieldName;
valueParams = string.IsNullOrEmpty(valueParams) ? "@" + piMap.FieldName : valueParams + ",@" + piMap.FieldName;
DbParameter dbParameter = dbCommand.CreateParameter();
dbParameter.Direction = ParameterDirection.Input;
dbParameter.ParameterName = "@" + piMap.FieldName;
dbParameter.Value = GetParameterValue(piMap, dto);
dbParameter.DbType = piMap.DbType;
dbCommand.Parameters.Add(dbParameter);
}
dbCommand.CommandText = string.Format(sqlStr, dtoDbMapping.GetTableName(subTableArg), fieldParams, valueParams);
#endregion
return(dbCommand);
}
/// <summary>
/// 获取查询RecCountDbCommand
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="filter"></param>
/// <param name="subTableArg">表名称参数.如果TableAttr设置Name.则根据Name格式化</param>
/// <returns></returns>
internal DbCommand GetCountDbCommand <T>(QueryFilter filter, string subTableArg = null)
{
DbCommand dbCommand = CreateDbCommand();
string sqlStr = "Select Count(*) as RecCount From {0} {1}";
string queryStr = "";
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
if (filter != null && filter.ItemList.Count > 0)
{
queryStr = filter.FilterSQLString;
}
if (filter != null && filter.FilterParameters.Count > 0)
{
for (int i = 0; i < filter.FilterParameters.Count; i++)
{
DbParameter dbParameter = dbCommand.CreateParameter();
dbParameter.Direction = ParameterDirection.Input;
dbParameter.ParameterName = filter.FilterParameters[i].ParameterName;
dbParameter.Value = filter.FilterParameters[i].ParameterValue;
dbParameter.DbType = filter.FilterParameters[i].ParameterDbType;
dbCommand.Parameters.Add(dbParameter);
}
}
dbCommand.CommandText = string.Format(sqlStr, dtoDbMapping.GetTableName(subTableArg), queryStr);
return(dbCommand);
}
/// <summary>
/// 查询对象表字段信息
/// </summary>
/// <returns>受影响记录数</returns>
public List <FieldModel> GetTableFieldList <T>()
{
string subTableArg = this.GetSubTableArg <T>();
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
string tableName = dtoDbMapping.GetTableName(subTableArg);
return(GetTableFieldList(tableName));
}
/// <summary>
/// 获取检查表是否存在DbCommand
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="subTableArg">表名称,如果为空,则使用T对应表名称</param>
/// <returns></returns>
public override DbCommand GetCheckTableExistsDbCommand <T>(string subTableArg = null)
{
DbCommand dbCommand = CreateDbCommand();
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
string tableName = dtoDbMapping.GetTableName(subTableArg);
dbCommand.CommandText = $"Select * from information_schema.tables where table_schema='public' and table_type='BASE TABLE' and table_name='{tableName}';";
return(dbCommand);
}
/// <summary>
/// 获取检查表是否存在DbCommand
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="subTableArg">表名称,如果为空,则使用T对应表名称</param>
/// <returns></returns>
public override DbCommand GetCheckTableExistsDbCommand <T>(string subTableArg = null)
{
DbCommand dbCommand = CreateDbCommand();
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
string tableName = dtoDbMapping.GetTableName(subTableArg);
dbCommand.CommandText = $"Select id from dbo.sysobjects where id = object_id(N'[dbo].[{tableName}]')";
return(dbCommand);
}
/// <summary>
/// 获取分页查询DbCommand
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="filter">过滤条件</param>
/// <param name="subTableArg">表名称,如果为空,则使用T对应表名称</param>
/// <returns></returns>
public override DbCommand GetQueryRecordsPageDbCommand <T>(QueryFilter filter, string subTableArg = null)
{
DbCommand dbCommand = CreateDbCommand();
//表名 查询字段名 主键字段名
string sqlStr = "Select {1} From {0} t1" +
",(Select Top (@UpRecNum) row_number() OVER (@OrderStr) Num,{2} From {0} @QueryStr) t2" +
" Where t1.{2}=t2.{2} And t2.Num>@LowRecNum Order By t2.Num Asc";
string selectParams = null;
string queryStr = null;
string orderStr = null;
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
List <PiMap> piMapList = DtoMappingHelper.GetPiMapList <T>(filter);
foreach (PiMap piMap in piMapList)
{
selectParams += string.IsNullOrEmpty(selectParams) ? "t2.Num,t1." + piMap.FieldName : ",t1." + piMap.FieldName;
}
if (filter != null && filter.ItemList.Count > 0)
{
queryStr = filter.FilterSQLString;
}
if (filter != null && !string.IsNullOrEmpty(filter.OrderSQLString))
{
orderStr = filter.OrderSQLString;
}
else
{ //默认By 主键 Asc
orderStr = "order by {2} asc";
}
sqlStr = sqlStr.Replace("@QueryStr", queryStr);
sqlStr = sqlStr.Replace("@LowRecNum", filter.FilterStartIndex.ToString());
sqlStr = sqlStr.Replace("@UpRecNum", filter.FilterEndIndex.ToString());
sqlStr = sqlStr.Replace("@OrderStr", orderStr);
if (filter != null)
{
for (int i = 0; i < filter.FilterParameters.Count; i++)
{
DbParameter dbParameter = dbCommand.CreateParameter();
dbParameter.Direction = ParameterDirection.Input;
dbParameter.ParameterName = filter.FilterParameters[i].ParameterName;
dbParameter.Value = filter.FilterParameters[i].ParameterValue;
dbParameter.DbType = filter.FilterParameters[i].ParameterDbType;
dbCommand.Parameters.Add(dbParameter);
}
}
DbParameter outDbParameter = dbCommand.CreateParameter();
outDbParameter.Direction = ParameterDirection.Output;
outDbParameter.ParameterName = "@RecCount";
outDbParameter.Size = 8;
dbCommand.Parameters.Add(outDbParameter); //RecCount 总记录数
dbCommand.CommandText = string.Format(sqlStr, dtoDbMapping.GetTableName(subTableArg), selectParams, dtoDbMapping.PkMap.FieldName);
return(dbCommand);
}
/// <summary>
/// 获取检查表是否存在DbCommand
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="subTableArg">表名称,如果为空,则使用T对应表名称</param>
/// <returns></returns>
public override DbCommand GetCheckTableExistsDbCommand <T>(string subTableArg = null)
{
DbCommand dbCommand = CreateDbCommand();
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
string tableName = dtoDbMapping.GetTableName(subTableArg);
dbCommand.CommandText = $"select * from sqlite_master where type='table' and name = '{tableName}';";
return(dbCommand);
}
/// <summary>
/// 获取插入DbCmd
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <param name="piMapList">查询属性MapList</param>
/// <param name="subTableArg">表名称参数.如果TableAttr设置Name.则根据Name格式化</param>
/// <returns></returns>
internal DbCommand GetInsertDbCmd <T>(List <T> list, List <PiMap> piMapList, string subTableArg = null) where T : class, new()
{
DbCommand dbCommand = CreateDbCommand();
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
#region 设置DbCommand
string sqlStr = "Insert into {0} ({1}) values{2}";
string fieldParams = null;
string valueParams = null;
foreach (PiMap piMap in piMapList)
{
PropertyInfo pi = piMap.Pi;
if (piMap.FieldAttr.ReadOnly)
{
continue; //跳过只读字段
}
if (piMap == dtoDbMapping.PkMap && dtoDbMapping.IsAutoIncrementPk)
{ //如果是自增主键 跳过插入
continue;
}
fieldParams = string.IsNullOrEmpty(fieldParams) ? piMap.FieldName : fieldParams + "," + piMap.FieldName;
}
StringBuilder strBuilder = new StringBuilder();
for (int i = 0; i < list.Count; i++)
{
strBuilder.Append("(");
StringBuilder itemBuilder = new StringBuilder();
foreach (PiMap piMap in piMapList)
{
PropertyInfo pi = piMap.Pi;
if (piMap.FieldAttr.ReadOnly)
{
continue; //跳过只读字段
}
if (piMap == dtoDbMapping.PkMap && dtoDbMapping.IsAutoIncrementPk)
{ //如果是自增主键 跳过插入
continue;
}
itemBuilder.Append($"@{piMap.FieldName}_{i},");
DbParameter dbParameter = dbCommand.CreateParameter();
dbParameter.Direction = ParameterDirection.Input;
dbParameter.ParameterName = $"@{piMap.FieldName}_{i}";
dbParameter.Value = GetParameterValue(piMap, list[i]);
dbParameter.DbType = piMap.DbType;
dbCommand.Parameters.Add(dbParameter);
}
strBuilder.Append(itemBuilder.ToString().TrimEnd(','));
strBuilder.Append("),");
}
valueParams = strBuilder.ToString().TrimEnd(',');
dbCommand.CommandText = string.Format(sqlStr, dtoDbMapping.GetTableName(subTableArg), fieldParams, valueParams);
#endregion
return(dbCommand);
}
/// <summary>
/// 使用BulkCopy插入数据
/// 目前仅支持MsSql(Sql Server)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list">数据List</param>
/// <param name="batchSize">BatchSize</param>
/// <param name="timeout">BulkCopyTimeout</param>
/// <param name="useTransaction">使用事务</param>
/// <param name="progress">0,1 进度</param>
public void BulkCopy <T>(List <T> list, int batchSize, int timeout = 0, bool useTransaction = true, IProgress <float> progress = null)
{
if (list == null || list.Count <= 0)
{
return;
}
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
string tableName = dtoDbMapping.GetTableName();
DataTable dt = EntityToDataTable(list);
dbProvider.BulkCopy(tableName, dt, batchSize, timeout, useTransaction, progress);
}
/// <summary>
/// 获取更新DbCmd
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <param name="piMapList">查询属性MapList</param>
/// <param name="subTableArg">表名称参数.如果TableAttr设置Name.则根据Name格式化</param>
/// <returns></returns>
internal DbCommand GetUpdateDbCmd <T>(List <T> list, List <PiMap> piMapList, string subTableArg = null)
{
DbCommand dbCommand = CreateDbCommand();
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
#region 设置DbCommand
StringBuilder strBuilder = new StringBuilder();
string sqlStr = "Update {0} set {1} where {2};";
for (int i = 0; i < list.Count; i++)
{
string setParams = null;
string whereParams = null;
T dto = list[i];
foreach (PiMap piMap in piMapList)
{
if (piMap == dtoDbMapping.PkMap && dtoDbMapping.IsAutoIncrementPk)
{ //如果是自增主键 跳过更新
continue;
}
DbParameter dbParameter = dbCommand.CreateParameter();
dbParameter.Direction = ParameterDirection.Input;
dbParameter.ParameterName = $"@{piMap.FieldName}_{i}";
dbParameter.Value = GetParameterValue(piMap, dto);
dbParameter.DbType = piMap.DbType;
dbCommand.Parameters.Add(dbParameter);
if (!string.IsNullOrEmpty(setParams))
{
setParams += ",";
}
setParams += $"{piMap.FieldName}={dbParameter.ParameterName}";
}
DbParameter whereParameter = dbCommand.CreateParameter();
whereParameter.Direction = ParameterDirection.Input;
whereParameter.ParameterName = $"@where{dtoDbMapping.PkMap.FieldName}{i}";
object pkValue = dtoDbMapping.PkMap.Pi.GetValue(dto);
whereParameter.Value = pkValue != null ? pkValue : DBNull.Value;
dbCommand.Parameters.Add(whereParameter);
whereParams = $"{dtoDbMapping.PkMap.FieldName}={whereParameter.ParameterName}";
string updateStr = string.Format(sqlStr, dtoDbMapping.GetTableName(subTableArg), setParams, whereParams);
strBuilder.Append(updateStr);
}
dbCommand.CommandText = strBuilder.ToString();
#endregion
return(dbCommand);
}
/// <summary>
/// 获取分页查询DbCommand
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="filter">过滤条件</param>
/// <param name="subTableArg">表名称,如果为空,则使用T对应表名称</param>
/// <returns></returns>
public override DbCommand GetQueryRecordsPageDbCommand <T>(QueryFilter filter, string subTableArg = null)
{
DbCommand dbCommand = CreateDbCommand();
//表名 查询字段名 主键字段名
string sqlStr = "Select {1} From {0} @QueryStr @OrderStr limit @PageSize offset @LowRecNum;";
string selectParams = null;
string queryStr = null;
string orderStr = null;
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
List <PiMap> piMapList = DtoMappingHelper.GetPiMapList <T>(filter);
foreach (PiMap piMap in piMapList)
{
selectParams += string.IsNullOrEmpty(selectParams) ? piMap.FieldName : "," + piMap.FieldName;
}
if (filter != null && filter.ItemList.Count > 0)
{
queryStr = filter.FilterSQLString;
}
if (filter != null && !string.IsNullOrEmpty(filter.OrderSQLString))
{
orderStr = filter.OrderSQLString;
}
else
{ //默认By 主键 Asc
orderStr = "order by {2} asc";
}
sqlStr = sqlStr.Replace("@QueryStr", queryStr);
sqlStr = sqlStr.Replace("@LowRecNum", filter.FilterStartIndex.ToString());
sqlStr = sqlStr.Replace("@PageSize", filter.PageSize.ToString());
sqlStr = sqlStr.Replace("@OrderStr", orderStr);
if (filter != null)
{
for (int i = 0; i < filter.FilterParameters.Count; i++)
{
DbParameter dbParameter = dbCommand.CreateParameter();
dbParameter.Direction = ParameterDirection.Input;
dbParameter.ParameterName = filter.FilterParameters[i].ParameterName;
dbParameter.Value = filter.FilterParameters[i].ParameterValue;
dbParameter.DbType = filter.FilterParameters[i].ParameterDbType;
dbCommand.Parameters.Add(dbParameter);
}
}
dbCommand.CommandText = string.Format(sqlStr, dtoDbMapping.GetTableName(subTableArg), selectParams, dtoDbMapping.PkMap.FieldName);
return(dbCommand);
}
/// <summary>
/// 获取删除DbCmd
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="subTableArg">表名称参数.如果TableAttr设置Name.则根据Name格式化</param>
/// <returns></returns>
internal DbCommand GetDeleteByIdDbCmd <T>(object pkValue, string subTableArg = null)
{
DbCommand dbCommand = CreateDbCommand();
#region 设置DbCommand
string sqlStr = "Delete From {0} Where {1}";
string whereParams = null;
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
DbParameter dbParameter = dbCommand.CreateParameter();
dbParameter.Direction = ParameterDirection.Input;
dbParameter.ParameterName = "@" + dtoDbMapping.PkMap.FieldName;
dbParameter.Value = pkValue;
dbParameter.DbType = dtoDbMapping.PkMap.DbType;
dbCommand.Parameters.Add(dbParameter);
whereParams = dtoDbMapping.PkMap.FieldName + "=@" + dtoDbMapping.PkMap.FieldName;
dbCommand.CommandText = string.Format(sqlStr, dtoDbMapping.GetTableName(subTableArg), whereParams);
#endregion
return(dbCommand);
}
/// <summary>
/// Convert List To DataTable
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <returns></returns>
private DataTable EntityToDataTable <T>(List <T> list)
{
DataTable dt = new DataTable();
List <FieldModel> fields = GetTableFieldList <T>();
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
string tableName = dtoDbMapping.GetTableName();
List <PiMap> piMaps = DtoMappingHelper.GetPiMapList <T>();
List <PiMap> mappingPis = new List <PiMap>();
for (int i = 0; i < fields.Count; i++)
{
FieldModel field = fields[i];
PiMap piMap = piMaps.FirstOrDefault(a => a.FieldName.ToLower() == field.FieldName.ToLower());
if (piMap == null)
{
throw new Exception($"{dtoDbMapping.EntityType.FullName} 未定义与表 '{tableName}' 列名为 '{field.FieldName}' 映射的属性");
}
mappingPis.Add(piMap);
Type realType = piMap.PropertyType.GenericTypeArguments.Length > 0 ?
piMap.PropertyType.GenericTypeArguments[0] : piMap.PropertyType;
if (realType.IsEnum)
{
realType = typeof(int);
}
dt.Columns.Add(new DataColumn(field.FieldName, realType));
}
foreach (T dto in list)
{
DataRow dr = dt.NewRow();
for (int i = 0; i < mappingPis.Count; i++)
{
PiMap piMap = mappingPis[i];
object value = piMap.Pi.GetValue(dto);
if (piMap == dtoDbMapping.PkMap && !dtoDbMapping.IsAutoIncrementPk)
{ //是否需要生成Guid
if (value == null || (Guid)value == Guid.Empty)
{
value = Guid.NewGuid();
piMap.Pi.SetValue(dto, value);
}
}
if (piMap.IsEnum)
{
if (value != null)
{
value = (int)value;
}
}
dr[i] = value ?? DBNull.Value;
}
dt.Rows.Add(dr);
}
return(dt);
}