/// <summary>
/// 填充对象
/// </summary>
/// <param name="obj"></param>
/// <param name="dr"></param>
public void Fill(object obj, DataRow dr)
{
Type t = obj.GetType();
foreach (PropertyInfo pi in t.GetProperties())
{
object[] objAttrs = pi.GetCustomAttributes(typeof(DBAttribute), true);
foreach (object o in objAttrs)
{
DBAttribute attr = o as DBAttribute;
if (attr != null)
{
if (attr.IsMapping)
{
string strPIName = pi.Name;
if (dr.Table.Columns.Contains(strPIName))
{
if (dr[strPIName] != DBNull.Value)
{
pi.SetValue(obj, dr[strPIName], null);
}
else
{
pi.SetValue(obj, default(object), null);
}
}
}
break;
}
}
}
}
private T readRow(SQLiteDataReader reader)
{
if (!_reflectionCache.ContainsKey(typeof(T)))
{
PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Public);
List <DBReflectionInfo> props = new List <DBReflectionInfo>();
foreach (PropertyInfo property in properties)
{
DBAttribute dbAttribute = property.GetCustomAttribute <DBAttribute>();
if (dbAttribute != null)
{
if (property.GetSetMethod() == null)
{
props.Add(new DBReflectionInfo(dbAttribute, ReflectionHelper.GetBackingField(property)));
}
else
{
props.Add(new DBReflectionInfo(dbAttribute, property));
}
}
}
_reflectionCache.TryAdd(typeof(T), props);
}
T retVal = default;
if (_reflectionCache.TryGetValue(typeof(T), out List <DBReflectionInfo> informations))
{
retVal = Activator.CreateInstance <T>();
foreach (DBReflectionInfo information in informations)
{
object value = default;
try
{
value = Convert.ChangeType(reader[information.DBAttribute.QueryName], information.Property?.PropertyType ?? information.BackingField?.FieldType);
}
catch { }
if (value != default)
{
if (information.Property != null)
{
information.Property.SetValue(retVal, value);
}
else
{
information.BackingField.SetValue(retVal, value);
}
}
}
}
return(retVal);
}
/// <summary>
/// 取得Updata語法
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="conition"></param>
/// <param name="newData"></param>
/// <returns></returns>
private static string UpdateSql <T>(T conition, T newData)
{
Type type = typeof(T);
StringBuilder sql = new StringBuilder("");
string tableName = (type.GetCustomAttributes().FirstOrDefault(i => i is DBAttribute) as DBAttribute).TableName;
PropertyInfo[] properties = type.GetProperties();
List <string> columnList = new List <string>();
List <string> conitionList = new List <string>();
//columnList
foreach (PropertyInfo pi in properties) // [DBAttribute.ColumnName] AS [PropertyName]
{
DBAttribute attribute = GetColumnName(pi);
if (attribute.AutoGenerate)
{
continue; // 自動產生的值不要動
}
if (pi.GetValue(newData) != null || attribute.Nullable)
{
columnList.Add($"[{attribute.ColumnName}] = @Col_{pi.Name} ");
}
}
//conitionList
foreach (PropertyInfo pi in properties) // [DBAttribute.ColumnName] AS [PropertyName]
{
DBAttribute attribute = GetColumnName(pi);
if (pi.GetValue(conition) != null || attribute.Nullable)
{
string operation = "";
if (conitionList.Any())
{
operation = attribute.Operation.ToString();
}
conitionList.Add($"{operation} [{attribute.ColumnName}] = @Con_{pi.Name} ");
}
}
// Column
sql.AppendLine($"UPDATE [{tableName}] ");
// SET
if (!columnList.Any())
{
return(null);
}
sql.AppendLine($"SET {string.Join(", ", columnList)} ");
// Where
if (!conitionList.Any())
{
return(sql.ToString());
}
sql.AppendLine($"WHERE {string.Join("", conitionList)} ");
return(sql.ToString());
}
public static List <T> getAllObject <T>()
{
List <string> key = new List <string>();
DBAttribute.getDBAllKey(typeof(T), key);
StringBuilder sb = new StringBuilder();
sb.Append(string.Format("select {0} from {1}", string.Join(',', key.ToArray()), DBAttribute.getDBTable(typeof(T))));
List <T> res = DataBaseAccess.testQuery <T>(sb.ToString()).ToList();
return(res);
}
private object ConvertDataRow <T>(DataRow dr)
{
object obj = Activator.CreateInstance(typeof(T));
foreach (PropertyInfo pi in typeof(T).GetProperties())
{
object[] objAttrs = pi.GetCustomAttributes(typeof(DBAttribute), true);
foreach (object o in objAttrs)
{
DBAttribute attr = o as DBAttribute;
if (attr != null)
{
if (attr.IsMapping)
{
string strPIName = pi.Name;
if (dr.Table.Columns.Contains(strPIName))
{
try
{
if (dr[strPIName] != DBNull.Value)
{
if (pi.PropertyType == typeof(bool))
{
pi.SetValue(obj, Convert.ToBoolean(dr[strPIName]));
}
else
{
pi.SetValue(obj, dr[strPIName], null);
}
}
else
{
pi.SetValue(obj, default(object), null);
}
}
catch (Exception ex)
{
throw new Exception(string.Format("Exception:" + ex.Message), ex);
}
}
}
break;
}
}
}
return(obj);
}
/// <summary>
/// 把class根據標籤轉成含有前綴的字典
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="conition"></param>
/// <param name="prefix"></param>
/// <returns></returns>
public static Dictionary <string, object> ObjectToParm <T>(T conition, string prefix = "")
{
Dictionary <string, object> result = new Dictionary <string, object>();
PropertyInfo[] properties = typeof(T).GetProperties();
foreach (PropertyInfo pi in properties)
{
DBAttribute attribute = GetColumnName(pi);
if (pi.GetValue(conition) == null)
{
if (!attribute.Nullable)
{
continue;
}
result.Add($"@{prefix}{pi.Name}", System.DBNull.Value);
continue;
}
result.Add($"@{prefix}{pi.Name}", pi.GetValue(conition));
}
return(result);
}
public void Delete(List <object> lObj)
{
List <string> lSql = new List <string>();
Type t = null;
object[] objs = null;
string sql = @"delete * from {0} where {1} in ({2})";
for (int i = 0; i < lObj.Count; i++)
{
object obj = lObj[i];
if (t == null)
{
t = obj.GetType();
}
if (objs == null)
{
objs = t.GetCustomAttributes(typeof(DBAttribute), true);
}
string strTableName = string.Empty;
foreach (object o in objs)
{
DBAttribute attr = o as DBAttribute;
if (attr != null)
{
strTableName = attr.TableName;//表名只有获取一次
break;
}
}
StringBuilder sbUpdate = new StringBuilder();
//sql = string.Format(sql, strTableName, sbUpdate.ToString(), sbKey.ToString());
//lSql.Add(sql);
}
this.BatchExecuteNonQuery(lSql);
}
/// <summary>
/// 取得Delete語法
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="conition"></param>
/// <returns></returns>
private static string DeleteSql <T>(T conition)
{
Type type = typeof(T);
StringBuilder sql = new StringBuilder("");
string tableName = (type.GetCustomAttributes().FirstOrDefault(i => i is DBAttribute) as DBAttribute).TableName;
PropertyInfo[] properties = type.GetProperties();
List <string> conitionList = new List <string>();
foreach (PropertyInfo pi in properties) // [DBAttribute.ColumnName] AS [PropertyName]
{
DBAttribute attribute = GetColumnName(pi);
if (pi.GetValue(conition) != null || attribute.Nullable)
{
string operation = "";
if (conitionList.Any())
{
operation = attribute.Operation.ToString();
}
conitionList.Add($"{operation} [{attribute.ColumnName}] = @{pi.Name} ");
}
}
// Column
sql.AppendLine($"DELETE [{tableName}] ");
// Where
if (!conitionList.Any())
{
if ((type.GetCustomAttributes().FirstOrDefault(i => i is DBAttribute) as DBAttribute).TableDeleteAble)
{
return(sql.ToString());
}
return(null);
}
sql.AppendLine($"WHERE {string.Join("", conitionList)} ");
return(sql.ToString());
}
/// <summary>
/// 取得Insert語法
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="newData"></param>
/// <returns></returns>
private static string InsertSql <T>(T newData)
{
Type type = typeof(T);
StringBuilder sql = new StringBuilder("");
string tableName = (type.GetCustomAttributes().FirstOrDefault(i => i is DBAttribute) as DBAttribute).TableName;
PropertyInfo[] properties = type.GetProperties();
List <string> columnList = new List <string>();
List <string> conitionList = new List <string>();
foreach (PropertyInfo pi in properties) // [DBAttribute.ColumnName] AS [PropertyName]
{
DBAttribute attribute = GetColumnName(pi);
if (attribute.AutoGenerate)
{
continue; // 自動產生的值不要動
}
if (pi.GetValue(newData) != null || attribute.Nullable)
{
columnList.Add($"[{attribute.ColumnName}]");
conitionList.Add($"@{pi.Name}");
}
}
// 沒資料不用Insert
if (!columnList.Any())
{
return(null);
}
// Insert
sql.AppendLine($"INSERT INTO [{tableName}] ({string.Join(", ", columnList)}) ");
// Value
sql.AppendLine($"VALUES ({string.Join(", ", conitionList)})");
return(sql.ToString());
}
public string LoadUpdateSql(object obj)
{
Type t = null;
if (t == null)
{
t = obj.GetType();
}
string sql = @"Update {0} Set {1} Where {2}";
object[] objs = t.GetCustomAttributes(typeof(DBAttribute), true);
if (objs == null)
{
objs = t.GetCustomAttributes(typeof(DBAttribute), true);
}
string strTableName = string.Empty;
foreach (object o in objs)
{
DBAttribute attr = o as DBAttribute;
if (attr != null)
{
strTableName = attr.TableName;//表名只有获取一次
break;
}
}
StringBuilder sbUpdate = new StringBuilder();
StringBuilder sbKey = new StringBuilder();
foreach (PropertyInfo pi in t.GetProperties())
{
object[] objAttrs = pi.GetCustomAttributes(typeof(DBAttribute), true);
foreach (object o in objAttrs)
{
DBAttribute attr = o as DBAttribute;
if (attr != null)
{
if (attr.IsMapping)
{
string strPIName = pi.Name;
object objPIValue = pi.GetValue(obj, null);//用pi.GetValue获得值
//获得属性的类型,进行判断然后进行以后的操作
if (objPIValue != null && objPIValue.GetType() == typeof(string))
{
//进行你想要的操作
objPIValue = FormateValue((string)objPIValue);
objPIValue = '\'' + (string)objPIValue + '\'';
}
else if (objPIValue != null && objPIValue.GetType() == typeof(DateTime))
{
//进行你想要的操作
objPIValue = '\'' + objPIValue.ToString() + '\'';
}
if (objPIValue == null)
{
objPIValue = "NULL";
}
if (attr.IsKey)
{
if (Keywords.Contains(pi.Name.ToUpper()))
{
sbKey.Append(" `" + pi.Name + "`=" + objPIValue + " AND");
}
else
{
sbKey.Append(" " + pi.Name + '=' + objPIValue + " AND");
}
}
else
{
if (Keywords.Contains(pi.Name.ToUpper()))
{
sbUpdate.Append(" `" + pi.Name + "`=" + objPIValue + ',');
}
else
{
sbUpdate.Append(pi.Name + '=' + objPIValue + ',');
}
}
}
break;
}
}
}
if (sbUpdate.Length > 0)
{
sbUpdate.Remove(sbUpdate.Length - 1, 1);
}
if (sbKey.Length > 2)
{
sbKey.Remove(sbKey.Length - 3, 3);
}
sql = string.Format(sql, strTableName, sbUpdate.ToString(), sbKey.ToString());
return(sql);
}
/// <summary>
/// (+1重载)插入泛型对象
/// </summary>
/// <param name="DB"></param>
/// <param name="obj"></param>
public void Insert(List <object> lObj)
{
List <string> lSql = new List <string>();
Type t = null;
object[] objs = null;
for (int i = 0; i < lObj.Count; i++)
{
object obj = lObj[i];
if (t == null)
{
t = obj.GetType();
}
string sql = @"INSERT INTO {0}({1}) VALUES({2})";
if (objs == null)
{
objs = t.GetCustomAttributes(typeof(DBAttribute), true);
}
string strTableName = string.Empty;
foreach (object o in objs)
{
DBAttribute attr = o as DBAttribute;
if (attr != null)
{
strTableName = attr.TableName;//表名只有获取一次
break;
}
}
StringBuilder sbMember = new StringBuilder();
StringBuilder sbValue = new StringBuilder();
foreach (PropertyInfo pi in t.GetProperties())
{
object[] objAttrs = pi.GetCustomAttributes(typeof(DBAttribute), true);
foreach (object o in objAttrs)
{
DBAttribute attr = o as DBAttribute;
if (attr != null)
{
if (attr.IsMapping)
{
string strPIName = pi.Name;
object objPIValue = pi.GetValue(obj, null);//用pi.GetValue获得值
//获得属性的类型,进行判断然后进行以后的操作
if (objPIValue != null && objPIValue.GetType() == typeof(string))
{
//进行你想要的操作
objPIValue = FormateValue((string)objPIValue);
objPIValue = '\'' + (string)objPIValue + '\'';
}
else if (objPIValue != null && objPIValue.GetType() == typeof(DateTime))
{
//进行你想要的操作
objPIValue = '\'' + objPIValue.ToString() + '\'';
}
if (objPIValue == null)
{
objPIValue = "NULL";
}
if (Keywords.Contains(pi.Name.ToUpper()))
{
sbMember.Append("`" + pi.Name + "`,");
}
else
{
sbMember.Append(pi.Name + ',');
}
sbValue.Append(objPIValue);
sbValue.Append(',');
}
break;
}
}
}
sbMember.Remove(sbMember.Length - 1, 1);
sbValue.Remove(sbValue.Length - 1, 1);
sql = string.Format(sql, strTableName, sbMember.ToString(), sbValue.ToString());
lSql.Add(sql);
}
this.BatchExecuteNonQuery(lSql);
}
public string LoadInsertSql(object obj)
{
Type t = obj.GetType();//获得该类的Type
string sql = @"INSERT INTO {0}({1}) VALUES({2})";
object[] objs = t.GetCustomAttributes(typeof(DBAttribute), true);
string strTableName = string.Empty;
foreach (object o in objs)
{
DBAttribute attr = o as DBAttribute;
if (attr != null)
{
strTableName = attr.TableName;//表名只有获取一次
break;
}
}
StringBuilder sbMember = new StringBuilder();
StringBuilder sbValue = new StringBuilder();
foreach (PropertyInfo pi in t.GetProperties())
{
object[] objAttrs = pi.GetCustomAttributes(typeof(DBAttribute), true);
foreach (object o in objAttrs)
{
DBAttribute attr = o as DBAttribute;
if (attr != null)
{
if (attr.IsMapping)
{
string strPIName = pi.Name;
object objPIValue = pi.GetValue(obj, null);//用pi.GetValue获得值
//获得属性的类型,进行判断然后进行以后的操作
if (objPIValue == null)
{
objPIValue = "NULL";
}
else if (objPIValue.GetType() == typeof(string))
{
//进行你想要的操作
objPIValue = FormateValue((string)objPIValue);
objPIValue = '\'' + (string)objPIValue + '\'';
}
else if (objPIValue.GetType() == typeof(DateTime))
{
objPIValue = '\'' + ((DateTime)objPIValue).ToString() + '\'';
}
if (Keywords.Contains(pi.Name.ToUpper()))
{
sbMember.Append("`" + pi.Name + "`,");
}
else
{
sbMember.Append(pi.Name + ',');
}
sbValue.Append(objPIValue);
sbValue.Append(',');
}
break;
}
}
}
sbMember.Remove(sbMember.Length - 1, 1);
sbValue.Remove(sbValue.Length - 1, 1);
sql = string.Format(sql, strTableName, sbMember.ToString(), sbValue.ToString());
return(sql);
}
