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