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