/// <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>
/// 获取查询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>
/// 获取查询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);
}
public void Configuration(IAppBuilder app)
{
HttpConfiguration httpConfig = new HttpConfiguration();
UnityConfig.Register(httpConfig);
ConfigureAuth(app);
//enable CORS policy
app.Map("/signalr", map =>
{
// Setup the CORS middleware to run before SignalR.
// By default this will allow all origins.
map.UseCors(CorsOptions.AllowAll);
map.UseOAuthBearerAuthentication(new OAuthBearerAuthenticationOptions()
{
Provider = new QueryStringOAuthBearerProvider()
});
var hubConfiguration = new HubConfiguration
{
Resolver = GlobalHost.DependencyResolver
};
map.RunSignalR(hubConfiguration);
});
WebApiConfig.Register(httpConfig);
app.UseWebApi(httpConfig);
//config logging system
LoggingConfig.LogToDb();
//config automapper for the solution
DtoMapping.Map();
}
/// <summary>
/// 更新数据对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dto">实体对象</param>
/// <param name="select">更新属性</param>
public int Update <T>(T dto, Expression <Func <T, object> > select = null) where T : class, new()
{
ExHelper.ThrowIfNull(dto, "操作对象不能为空.");
int rowCount = 0;
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
List <PiMap> piMapList = DtoMappingHelper.GetPiMapList <T>(select);
using (DbCommand dbCommand = dbProvider.GetUpdateDbCmd(dto, piMapList, this.GetSubTableArg <T>()))
{
try
{
dbCommand.Connection = GetDbConnection();
rowCount = dbCommand.ExecuteNonQuery();
if (rowCount <= 0)
{
//throw new Exception("没有更新任何记录.");
}
CloseDbConnection(dbCommand);
}
catch (Exception ex)
{
CloseDbConnection(dbCommand);
throw ex;
}
}
return(rowCount);
}
/// <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>
/// 获取删除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>
/// 获取查询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>
/// <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>
/// 获取建表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>
/// 获取建表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>
/// 查询对象表字段信息
/// </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="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 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 * from sqlite_master where type='table' and 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>
/// dr转换为Dto实体对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dr"></param>
/// <returns></returns>
public static T ToEntity <T>(this DataRow dr) where T : class, new()
{
DtoMapping dtoMapping = DtoMappingHelper.GetDtoMapping <T>();
if (dtoMapping.EntityConvertor == null)
{
dtoMapping.EntityConvertor = EntityConvertor.CreateEntityConvertor <T>(); //存入dtoMapping中,缓存起来
}
return((T)((EntityConvertorDelegate)dtoMapping.EntityConvertor)(dr));
}
/// <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>
/// This method gets called by the runtime. Use this method to add services to the container.
/// </summary>
public void ConfigureServices(IServiceCollection services)
{
DtoMapping.WebApiConfigure();
// Register the IConfiguration instance which MyOptions binds against.
services.Configure <MyOptions>(Configuration.GetSection("MyOptions"));
// Add framework services.
services.AddDbContext <LovePlatformContext>(options =>
options.UseMySql(Configuration.GetConnectionString("DefaultConnection")));
services.AddMvc(options =>
{
options.Filters.Add(new ValidateModelAttribute());
options.Filters.Add(new CustomExceptionFilterAttribute());
}).AddJsonOptions(op => op.SerializerSettings.ContractResolver = new DefaultContractResolver());
// Add application services.
services.AddScoped <PatientService>();
services.AddScoped <WeightService>();
services.AddScoped <UserService>();
services.AddScoped <TreatService>();
services.AddScoped <TreatImageService>();
services.AddScoped <DiagnoseService>();
services.AddScoped <OssService>();
services.AddScoped <UserService>();
services.AddScoped <BloodPressureService>();
services.AddScoped <PatientRepository>();
services.AddScoped <WeightRepository>();
services.AddScoped <UserRepository>();
services.AddScoped <TreatRepository>();
services.AddScoped <TreatImageRepository>();
services.AddScoped <DiagnoseRepository>();
services.AddScoped <OssRepository>();
services.AddScoped <UserRepository>();
services.AddScoped <BloodPressureRepository>();
services.AddScoped <IUnitWork, UnitWork>();
// Register the Swagger generator, defining one or more Swagger documents
services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1", new Info {
Title = "WebAPI", Version = "v1"
});
//Set the comments path for the swagger json and ui.
var basePath = PlatformServices.Default.Application.ApplicationBasePath;
var webapiXmlPath = Path.Combine(basePath, "LovePlatform.WebAPI.xml");
c.IncludeXmlComments(webapiXmlPath);
var domainXmlPath = Path.Combine(basePath, "LovePlatform.DTO.xml");
c.IncludeXmlComments(domainXmlPath);
});
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
DtoMapping.H5Configure();
// Add framework services.
services.AddMvc().AddJsonOptions(op => op.SerializerSettings.ContractResolver = new DefaultContractResolver());
// Register the IConfiguration instance which MyOptions binds against.
services.Configure <MyOptions>(Configuration.GetSection("MyOptions"));
services.AddDirectoryBrowser();
}
/// <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);
}
public static void WireUp(Container container)
{
DtoMapping.Config();
container.Register <IFlightRepository, FlightRepository>(Lifestyle.Scoped);
container.Register <IFlightCalculationService, FlightCalculationService>(Lifestyle.Singleton);
container.Register <IFlightGenerationService, FlightGenerationService>(Lifestyle.Scoped);
container.Register <FlightScheduleContext>(Lifestyle.Scoped);
container.Register <DbConnection>(() =>
{
});
}
/// <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>
/// 查询符合条件的记录数
/// </summary>
/// <param name="where">查询条件</param>
/// <returns></returns>
public int Count <T>(Expression <Func <T, bool> > where = null) where T : class, new()
{
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
if (dtoDbMapping.TableAttr.AutoCreate)
{ //如果是自动建表
if (!CheckTableExists <T>())
{
return(0);
}
}
IQuery <T> query = IQuery <T>().FromTable(this.GetSubTableArg <T>());
return(query.Count(where));
}
/// <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);
}
public static List <T> ToList <T>(this DataTable dt) where T : class, new()
{
List <T> list = new List <T>();
if (dt != null && dt.Rows.Count > 0)
{
DtoMapping dtoMapping = DtoMappingHelper.GetDtoMapping <T>();
foreach (DataRow dr in dt.Rows)
{
list.Add(dr.ToEntity <T>());
}
dt.Dispose();
dt = null;
}
return(list);
}
/// <summary>
/// 保存or更新数据对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dto">实体对象</param>
/// <param name="select">查询属性</param>
public int Set <T>(T dto, Expression <Func <T, object> > select = null) where T : class, new()
{
ExHelper.ThrowIfNull(dto, "操作对象不能为空.");
int rowCount = 0;
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
object pkValue = dtoDbMapping.PkMap.Pi.GetValue(dto);
if (ExHelper.IsNullOrEmpty(pkValue))
{
rowCount = Add(dto, select);
}
else
{
rowCount = Update(dto, select);
}
return(rowCount);
}
/// <summary>
/// 批量保存数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list">实体对象</param>
/// <param name="select">查询属性</param>
/// <param name="useTransaction">使用事务操作 默认false</param>
/// <param name="progress">保存进度</param>
public int SetList <T>(List <T> list, Expression <Func <T, object> > select = null, bool useTransaction = false, IProgress <double> progress = null) where T : class, new()
{
if (list == null || list.Count <= 0)
{
return(0);
}
int rowCount = 0;
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
List <T> addList = new List <T>();
List <T> updateList = new List <T>();
#region 拆分新增或更新对象List
for (int i = 0; i < list.Count; i++)
{
object pkValue = dtoDbMapping.PkMap.Pi.GetValue(list[i]);
if (ExHelper.IsNullOrEmpty(pkValue))
{
addList.Add(list[i]);
}
else
{
updateList.Add(list[i]);
}
}
#endregion
if (useTransaction)
{
if (addList.Count > 0 && updateList.Count > 0)
{
throw new Exception("插入和更新操作同时存在时,不能启用事务.");
}
}
if (addList.Count > 0)
{
rowCount += AddList(addList, select, useTransaction, progress);
}
if (updateList.Count > 0)
{
rowCount += UpdateList(updateList, select, useTransaction, progress);
}
return(rowCount);
}
public static void Register(HttpConfiguration config)
{
// Web API configuration and services
/*Remove XML Formatter for JSON response*/
GlobalConfiguration.Configuration.Formatters.XmlFormatter.SupportedMediaTypes.Clear();
// Web API routes
config.MapHttpAttributeRoutes();
//AutoMapper Initialize
DtoMapping.Map();
config.Routes.MapHttpRoute(
name: "DefaultApi",
routeTemplate: "api/{controller}/{id}",
defaults: new { id = RouteParameter.Optional }
);
}
/// <summary>
/// 添加分表参数.返回当前SubTableDbContext
/// </summary>
/// <typeparam name="T">操作对象类型</typeparam>
/// <param name="subTableArg">分表参数</param>
/// <returns>返回当前SubTableDbContext</returns>
public SubTableDbContext AddSubTableArg <T>(object subTableArg)
{
if (subTableArgList.Any(kv => kv.Key == typeof(T)))
{
throw new Exception($"不能为类型{typeof(T).Name}重复添加分表参数");
}
DtoMapping dtoDbMapping = DtoMappingHelper.GetDtoMapping <T>();
string tableName = dtoDbMapping.TableAttr.Name;
if (string.IsNullOrEmpty(tableName) || !tableName.Contains("{0}"))
{
throw new Exception("必须为分表对象" + typeof(T).Name + "指定包含{0}参数的TableName属性");
}
if (subTableArg == null)
{
throw new Exception("分表参数不能为空");
}
this.subTableArgList.Add(new KeyValuePair <Type, string>(typeof(T), subTableArg.ToString()));
return(this);
}
