private ComboClass <DataTable, List <TableFieldAttribute> > BuildBulkCache(Type t)
{
DataTable table = new DataTable();
FieldInfo[] fields = t.GetFields();
List <TableFieldAttribute> attrs = new List <TableFieldAttribute>();
foreach (FieldInfo field in fields)
{
TableFieldAttribute fieldAttr = AttributeHelper.TryGetAttribute <TableFieldAttribute>(field);
if (fieldAttr != null)
{
DataColumn column = new DataColumn();
column.ColumnName = fieldAttr.ColumnName;
column.DataType = fieldAttr.FieldType ?? field.FieldType;
table.Columns.Add(column);
fieldAttr.Field = field;
fieldAttr.Column = column;
fieldAttr.Context = new SqlBulkCopyColumnMapping(column.ColumnName, column.ColumnName);
attrs.Add(fieldAttr);
}
}
ComboClass <DataTable, List <TableFieldAttribute> > cache = new ComboClass <DataTable, List <TableFieldAttribute> >();
cache.V1 = table;
cache.V2 = attrs;
return(cache);
}
private ComboClass <string, string[], List <TableFieldAttribute> > BuildBulkCache(string tableName, Type t, int batchCount)
{
List <string> param = new List <string>();
FieldInfo[] fields = t.GetFields();
List <TableFieldAttribute> attrs = new List <TableFieldAttribute>();
foreach (FieldInfo field in fields)
{
TableFieldAttribute fieldAttr = AttributeHelper.TryGetAttribute <TableFieldAttribute>(field);
if (fieldAttr != null)
{
attrs.Add(fieldAttr);
fieldAttr.Field = field;
}
}
StringBuilder sqlAll = new StringBuilder();
for (int i = 0; i < batchCount; i++)
{
StringBuilder sqlBefore = new StringBuilder();
StringBuilder sqlAfter = new StringBuilder();
StringBuilder sqlUpdate = new StringBuilder();
sqlBefore.AppendFormat("INSERT INTO {0} (", tableName);
for (int j = 0; j < attrs.Count; j++)
{
var attr = attrs[j];
sqlBefore.AppendFormat("{0}", attr.ColumnName);
int paramOrder = i * attrs.Count + j;
sqlAfter.AppendFormat("@v_p{0}", paramOrder);
sqlUpdate.AppendFormat("{0}=@v_p{1}", attr.ColumnName, paramOrder);
param.Add("@p" + paramOrder);
if (j != attrs.Count - 1)
{
sqlBefore.Append(",");
sqlAfter.Append(",");
sqlUpdate.Append(",");
}
}
sqlAll.Append(sqlBefore.ToString());
sqlAll.Append(") VALUES (");
sqlAll.Append(sqlAfter.ToString());
sqlAll.Append(") ON DUPLICATE KEY UPDATE ");
sqlAll.Append(sqlUpdate.ToString());
sqlAll.Append(";\r\n");
}
var cache = new ComboClass <string, string[], List <TableFieldAttribute> >();
cache.V1 = sqlAll.ToString();
cache.V2 = param.ToArray();
cache.V3 = attrs;
return(cache);
}
//-------------------------------------------
//-------------------------------------------
//小更新
// \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
// \\ \\
// \\ teenyda \\
// \\ \\
// \\ __ __ \\
// \\ / /____ ___ ___ __ _____/ /__ _ \\
// \\ / __/ -_) -_) _ \/ // / _ / _ `/ \\
// \\ \__/\__/\__/_//_/\_, /\_,_/\_,_/ \\
// \\ /___/ \\
// \\ \\
// \\ \\
// \\ \\
// \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
/// 2020/6/25
/// <summary>
/// by teenyda
/// 遍历对象的属性，获取特性值
/// TableFieldAttribute、EnumFieldAttribute
/// </summary>
/// <param name="t"></param>
/// <param name="iTake"></param>
private void foreachAttribute(T t, ITakeAttribute iTake)
{
if (iTake == null)
{
throw new System.Exception("ITakeAttribute对象为空，请检查");
}
//遍历p的属性
foreach (System.Reflection.PropertyInfo p in t.GetType().GetProperties())
{
iTake.onTakeAttribute(p);
object[] tableObjs = p.GetCustomAttributes(typeof(TableFieldAttribute), true);
//表字段特性
if (tableObjs.Length > 0)
{
TableFieldAttribute tableField = (TableFieldAttribute)tableObjs[0];
iTake.onTakeTableFieldAttribute(tableField);
if (tableField.PrimaryKey && p.GetValue(t) != null)
{
iTake.onTakePrimaryKey(tableField.FieldName, p.GetValue(t));
}
}
object[] enumObjs = p.GetCustomAttributes(typeof(EnumFieldAttribute), true);
//枚举特性
if (enumObjs.Length > 0)
{
iTake.onTakeEnumFieldAttribute((EnumFieldAttribute)enumObjs[0]);
}
}
}
public static string GenerateCountSql <T>() where T : class
{
Type ty = typeof(T);
string table = ty.Name;
List <string> ignoreField = new List <string>();
object[] obj = ty.GetCustomAttributes(typeof(TableFieldAttribute), false);
string selectColumn = string.Empty;
if (obj != null && obj.Length > 0)
{
TableFieldAttribute map = obj[0] as TableFieldAttribute;
table = string.IsNullOrEmpty(map.TableName) ? table : map.TableName;
if (map.IgnoreProperty != null)
{
ignoreField.AddRange(map.IgnoreProperty);
}
if (!string.IsNullOrEmpty(map.PrimaryKey))
{
selectColumn = map.PrimaryKey;
}
else if (!string.IsNullOrEmpty(map.UniqueColumn))
{
selectColumn = map.UniqueColumn;
}
}
if (string.IsNullOrEmpty(selectColumn))
{
foreach (PropertyInfo item in ty.GetProperties())
{
string pn = item.Name;
//是否是被忽略的属性
object[] ignore = item.GetCustomAttributes(typeof(PropertyIgnoreFieldAttribute), false);
if (ignore != null && ignore.Length > 0)
{
ignoreField.Add(pn);
continue;
}
object[] propertyMapColumn = item.GetCustomAttributes(typeof(ColumnAttribute), false);
if (propertyMapColumn != null && propertyMapColumn.Length > 1)
{
ColumnAttribute col = propertyMapColumn[0] as ColumnAttribute;
pn = string.IsNullOrEmpty(col.Name) ? pn : col.Name;
}
selectColumn = "[" + pn + "]";
break;
}
if (string.IsNullOrEmpty(selectColumn))
{//没有匹配的数据库列
return("Select count(*) from dbo.[{table}]".Replace("{table}", table));
}
}
return("Select count({columns}) from dbo.[{table}]".Replace("{columns}", selectColumn).Replace("{table}", table));
}
//获取列名[PropertyInfo 扩展方法]
/// <summary>
/// 获取字段在数据库中是否存在
/// </summary>
/// <param name="prop"></param>
/// <returns></returns>
public static bool IsExist(this PropertyInfo prop)
{
TableFieldAttribute tableFieldAttribute = GetColumnAttriBute(prop);
if (tableFieldAttribute != null)
{
return(tableFieldAttribute.IsExist);
}
else
{
PrimaryKeyAttribute primaryKeyAttribute = prop.GetCustomAttribute <PrimaryKeyAttribute>();
return(primaryKeyAttribute != null);
}
}
private List <TableFieldAttribute> BuildBulkCache(Type t)
{
FieldInfo[] fields = t.GetFields();
List <TableFieldAttribute> attrs = new List <TableFieldAttribute>();
foreach (FieldInfo field in fields)
{
TableFieldAttribute fieldAttr = AttributeHelper.TryGetAttribute <TableFieldAttribute>(field);
if (fieldAttr != null)
{
fieldAttr.Field = field;
attrs.Add(fieldAttr);
}
}
return(attrs);
}
//获取列名[PropertyInfo 扩展方法]
/// <summary>
/// 获取数据库列名
/// </summary>
/// <param name="prop"></param>
/// <returns></returns>
public static string GetColumnName(this PropertyInfo prop)
{
string columnName = null;
TableFieldAttribute tableFieldAttribute = GetColumnAttriBute(prop);
if (tableFieldAttribute != null)
{
columnName = tableFieldAttribute.ColumName;
}
else
{
PrimaryKeyAttribute primaryKeyAttribute = prop.GetCustomAttribute <PrimaryKeyAttribute>();
columnName = primaryKeyAttribute != null ? primaryKeyAttribute.ColumnName : columnName;
}
return(columnName);
}
public static string GenerateSampleSelectSql <T>() where T : class
{
Type ty = typeof(T);
string table = ty.Name;
List <string> ignoreField = new List <string>();
object[] obj = ty.GetCustomAttributes(typeof(TableFieldAttribute), false);
if (obj != null && obj.Length > 0)
{
TableFieldAttribute map = obj[0] as TableFieldAttribute;
table = string.IsNullOrEmpty(map.TableName) ? table : map.TableName;
if (map.IgnoreProperty != null)
{
ignoreField.AddRange(map.IgnoreProperty);
}
}
List <string> columns = new List <string>();
foreach (PropertyInfo item in ty.GetProperties())
{
string pn = item.Name;
//是否是被忽略的属性
object[] ignore = item.GetCustomAttributes(typeof(PropertyIgnoreFieldAttribute), false);
if (ignore != null && ignore.Length > 0)
{
ignoreField.Add(pn);
continue;
}
object[] propertyMapColumn = item.GetCustomAttributes(typeof(ColumnAttribute), false);
if (propertyMapColumn != null && propertyMapColumn.Length > 1)
{
ColumnAttribute col = propertyMapColumn[0] as ColumnAttribute;
pn = string.IsNullOrEmpty(col.Name) ? pn : col.Name;
}
columns.Add("[" + pn + "]");
}
if (columns.Count == 0)
{//没有匹配的数据库列
return(string.Empty);
}
return("Select {columns} from dbo.[{table}]".Replace("{columns}", string.Join(",", columns)).Replace("{table}", table));
}
/// <summary>
/// 实体中数据库列-实体属性匹配字典
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static Dictionary <string, string> GenerateColumnMapPropertyDict <T>() where T : class
{
Type ty = typeof(T);
string table = ty.Name;
List <string> ignoreField = new List <string>();
object[] obj = ty.GetCustomAttributes(typeof(TableFieldAttribute), false);
if (obj != null && obj.Length > 0)
{
TableFieldAttribute map = obj[0] as TableFieldAttribute;
table = string.IsNullOrEmpty(map.TableName) ? table : map.TableName;
if (map.IgnoreProperty != null)
{
ignoreField.AddRange(map.IgnoreProperty);
}
}
Dictionary <string, string> columns = new Dictionary <string, string>();
foreach (PropertyInfo item in ty.GetProperties())
{
string pn = item.Name;
//是否是被忽略的属性
object[] ignore = item.GetCustomAttributes(typeof(PropertyIgnoreFieldAttribute), false);
if (ignore != null && ignore.Length > 0)
{
ignoreField.Add(pn);
continue;
}
object[] propertyMapColumn = item.GetCustomAttributes(typeof(ColumnAttribute), false);//是否定义属性转换匹配
if (propertyMapColumn != null && propertyMapColumn.Length > 1)
{
ColumnAttribute col = propertyMapColumn[0] as ColumnAttribute;
pn = string.IsNullOrEmpty(col.Name) ? pn : col.Name;
}
columns.Add(pn, item.Name);
}
return(columns);
}
/// <summary>
/// 根据实体生成insert语句
/// </summary>
/// <typeparam name="T">实体类
/// 【TableFieldAttribute可以匹配实体和表关系,
/// PropertyIgnoreFieldAttribute修饰的属性不组装到命令中】</typeparam>
/// <returns>返回拼装的insert语句</returns>
public static string GenerateInsertSql <T>() where T : class
{
Type ty = typeof(T);
string table = ty.Name;
//判断是否存在特性指定表名称
object[] att = ty.GetCustomAttributes(typeof(TableFieldAttribute), false);
List <string> dbGenrateField = new List <string>(); //数据库生成字段
List <string> ignoreProperty = new List <string>(); //忽略的属性
if (att != null && att.Length > 0)
{
TableFieldAttribute mapp = att[0] as TableFieldAttribute;
table = string.IsNullOrEmpty(mapp.TableName)?table: mapp.TableName;
if (mapp.DbGeneratedFields != null)
{
dbGenrateField.AddRange(mapp.DbGeneratedFields);
}
if (mapp.IgnoreProperty != null)
{
ignoreProperty.AddRange(mapp.IgnoreProperty);
}
}
Dictionary <string, string> columnMapProperty = new Dictionary <string, string>();
foreach (PropertyInfo item in ty.GetProperties())
{
string property = item.Name;
#region 过滤掉不组装到语句中的属性
if (ignoreProperty.Contains(property))
{
continue;
}
if (dbGenrateField.Contains(property))
{
continue;
}
//该属性是否存在被忽略特性
object[] isIgnoreProperty = item.GetCustomAttributes(typeof(PropertyIgnoreFieldAttribute), false);
if (isIgnoreProperty != null && isIgnoreProperty.Length > 0)
{
continue;
}
#endregion
string field = property;
//判断是否存在转换特性
object[] obj = item.GetCustomAttributes(typeof(ColumnAttribute), false);
if (obj != null && obj.Length > 0)
{
ColumnAttribute column = obj[0] as ColumnAttribute;
field = column.Name;
}
columnMapProperty.Add("{" + property + "}", field);
}
if (columnMapProperty.Count == 0)
{
return(string.Empty);
}
string sql = "Insert into dbo.[{table}] ({columns}) values({columnsValueFormat})";
return(sql.Replace("{table}", table).Replace("{columns}", string.Join(",", columnMapProperty.Values.ToArray()))
.Replace("{columnsValueFormat}", string.Join(",", columnMapProperty.Keys.ToArray())));
}
/// <summary>
/// DataTable转list
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dt"></param>
/// <returns></returns>
public static List <T> TableToList <T>(DataTable dt)
{
List <T> list = new List <T>();
if (dt == null)
{
return(list);
}
//BindingFlags bindingFlags = BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public | BindingFlags.NonPublic;
Type type = typeof(T);
PropertyInfo[] propertyInfos = typeof(T).GetProperties();
var plist = new List <PropertyInfo>(propertyInfos);
TableFieldAttribute tableFieldAttr = null;
PrimaryKeyAttribute keyAttribute = null;
Dictionary <string, PropertyInfo> dictionAry = new Dictionary <string, PropertyInfo>();
for (int p = 0; p < propertyInfos.Length; p++)
{
tableFieldAttr = propertyInfos[p].GetCustomAttribute <TableFieldAttribute>();
keyAttribute = propertyInfos[p].GetCustomAttribute <PrimaryKeyAttribute>();
if (tableFieldAttr != null && tableFieldAttr.ColumName != null)
{
dictionAry.Add(tableFieldAttr.ColumName, propertyInfos[p]);
}
else if (keyAttribute != null && keyAttribute.ColumnName != null)
{
dictionAry.Add(keyAttribute.ColumnName, propertyInfos[p]);
}
}
PropertyInfo currentProp = null;
//实体
T s;
foreach (DataRow item in dt.Rows)
{
s = Activator.CreateInstance <T>();
for (int i = 0; i < dt.Columns.Count; i++)
{
if (dictionAry.ContainsKey(dt.Columns[i].ColumnName))
{
currentProp = dictionAry[dt.Columns[i].ColumnName];
object v = null;
if (currentProp.PropertyType.ToString().Contains("System.Nullable"))
{
v = Convert.ChangeType(item[i], Nullable.GetUnderlyingType(currentProp.PropertyType));
}
else
{
if (!(item[i] is DBNull))
{
v = Convert.ChangeType(item[i], currentProp.PropertyType);
}
}
currentProp.SetValue(s, v, null);
}
}
list.Add(s);
}
return(list);
}
