/// <summary> /// Numbering the target column /// 対象列を採番(数値) /// </summary> /// <param name="column"></param> /// <returns></returns> public string GetAssignNumber(string column) { DataTable dataTable = new DataTable(); string countColumn = "Count"; string countSql = "select max(cast({0} as interger)) as " + countColumn + " from {1}"; this.ExecutetSql.Append(string.Format(countSql, column, this.GetDataAccessAttribute <DataAccessAttribute>(this.BaseType).TableName)); SqliteDatabase sqliteDataBase = new SqliteDatabase(CONNECT_TABLE); dataTable = sqliteDataBase.ExecuteQuery(this.ExecutetSql.ToString()); PropertyInfo propertyInfo = this.BaseType.GetProperty(column); if (propertyInfo == null) { Debug.Log("not found assign column"); return(string.Empty); } DataPropertyAttribute attribute = this.GetDataPropertyAttribute <DataPropertyAttribute>(propertyInfo); if (dataTable.Rows.Count.Equals(0)) { return(this.GetAssignNumberFormat(attribute.MaxLength, "1")); } else { int no = int.Parse(dataTable.Rows[0][countColumn].ToString()) + 1; return(this.GetAssignNumberFormat(attribute.MaxLength, no.ToString())); } }
/// <summary> /// Get the column that is included in the SQLiteComponent /// SQLiteComponentに含まれる列を取得 /// </summary> /// <param name="type"></param> /// <returns></returns> private string GetSqliteColumn(Type type) { StringBuilder stringBuilder = new StringBuilder(); bool first = true; PropertyInfo[] propertyInfoArray = type.GetProperties(); string tableName = this.GetDataAccessAttribute <DataAccessAttribute>(type).TableName; string format = "{0}.{1}"; foreach (PropertyInfo propertyInfo in propertyInfoArray) { DataPropertyAttribute attribute = this.GetDataPropertyAttribute <DataPropertyAttribute>(propertyInfo); if (attribute != null) { if (first) { first = false; } else { stringBuilder.Append(","); } stringBuilder.AppendLine(string.Format(format, tableName, propertyInfo.Name)); } } return(stringBuilder.ToString()); }
private object GetSqliteTypeValue(DataPropertyAttribute attribute, PropertyInfo propertyInfo, object component) { object value = null; switch (attribute.SqliteType) { case DataPropertyAttribute.SQLITE_TYPE.TEXT: case DataPropertyAttribute.SQLITE_TYPE.NUMERIC: value = propertyInfo.GetValue(component, null); break; case DataPropertyAttribute.SQLITE_TYPE.BOOLEAN: bool flg = (bool)propertyInfo.GetValue(component, null); value = (flg) ? "1" : "0"; break; case DataPropertyAttribute.SQLITE_TYPE.ARRAY: if (propertyInfo.PropertyType.Equals(typeof(List <string>))) { List <string> listArray = (List <string>)propertyInfo.GetValue(component, null); if (listArray == null) { value = null; } else { value = string.Join(",", listArray.ToArray()); } } break; case DataPropertyAttribute.SQLITE_TYPE.DATETIME: if (propertyInfo.PropertyType.Equals(typeof(DateTime))) { value = this.GetDateFormat((DateTime)propertyInfo.GetValue(component, null)); } break; default: break; } if (value != null) { value = this.EscapeSingleQuotation(value.ToString()); } return(value); }
/// <summary> /// Recovering the primary key and values /// プライマリキーと値を回収 /// </summary> private void GetPrimaryKeyValue(object component) { Type type = component.GetType(); PropertyInfo[] propertyInfoArray = type.GetProperties(); foreach (PropertyInfo propertyInfo in propertyInfoArray) { if (propertyInfo.DeclaringType.Equals(type)) { DataPropertyAttribute attribute = this.GetDataPropertyAttribute <DataPropertyAttribute>(propertyInfo); if (attribute != null) { if (attribute.IsPrimaryKey) { this.AddPrimaryKey(propertyInfo.GetValue(component, null), propertyInfo.Name); } } } } }
/// <summary> /// 获取单个对象 /// </summary> /// <typeparam name="T">需要获取的对象</typeparam> /// <param name="dt">对象的表</param> /// <param name="index">当前需要获取的表中的行索引</param> /// <returns></returns> protected T GetObjectInfo <T>(DataTable dt, int index) { T t = default(T); T tobj = Activator.CreateInstance <T>(); //创建一个对象 PropertyInfo[] pr = tobj.GetType().GetProperties(); //获取对象的所有公共属性 int columnCount = dt.Columns.Count; //循环获取 foreach (PropertyInfo item in pr) { for (int i = 0; i < columnCount; i++) { //判断当前的属性是否实现了对应的特性 DataPropertyAttribute attr = null; var attrs = item.GetCustomAttributes(typeof(DataPropertyAttribute), false); if (null != attrs && attrs.Length > 0) { attr = (DataPropertyAttribute)attrs[0]; } // var attr = item.GetCustomAttribute<DataPropertyAttribute>(); string currentName = item.Name.ToLower(); if (attr != null) { currentName = attr.DataName; } //如果当前循环到的对象属性名称与当前数据表中的列名一致 if (currentName.Equals(dt.Columns[i].ColumnName.ToLower())) { //将当前的行列值设置到对象中 var value = dt.Rows[index][i]; //获取当前列类型 Type colType = dt.Columns[i].DataType; //获取当前字段类型 Type itemType = item.PropertyType; //如果两个类型不一致 if (colType.ToString() != itemType.ToString()) { //如果值实现了IConvertible接口 if (value is IConvertible) { //直接将当前值转化成用户传进来的T类型 try { value = Convert.ChangeType(value, itemType); } catch { throw new FormatException($"{value} 的类型为 {colType},但在希望将其转成 {itemType} 时无法转换,请更正!"); } } else { //没有实现,直接返回其字符串形式 value = value.ToString(); } } if (value != DBNull.Value) { item.SetValue(tobj, value, null); } else { item.SetValue(tobj, null, null); } } } } t = tobj; return(t); }
/// <summary> /// 获取指定对象集合 /// </summary> /// <typeparam name="T">要获取的对象</typeparam> /// <param name="query">sql语句</param> /// <returns></returns> public List <T> QueryList <T>(string query) { DbCommand cmd = null; try { cmd = CreateCommand(query, CommandType.Text); using (var reader = cmd.ExecuteReader()) { if (!reader.HasRows) { return(new List <T>()); } List <T> list = new List <T>(); while (reader.Read()) { T tobj = Activator.CreateInstance <T>(); //创建一个对象 PropertyInfo[] pr = tobj.GetType().GetProperties(); //获取对象的所有公共属性 foreach (PropertyInfo property in pr) { //判断当前的属性是否实现了对应的特性 DataPropertyAttribute attr = null; var attrs = property.GetCustomAttributes(typeof(DataPropertyAttribute), false); if (null != attrs && attrs.Length > 0) { attr = (DataPropertyAttribute)attrs[0]; } string currentName = property.Name.ToLower(); if (attr != null) { currentName = attr.DataName; } object value = null; try { value = reader[currentName]; } catch { continue;//没有这一列,直接略过 } //获取当前列类型 Type colType = value.GetType(); //获取当前字段类型 Type itemType = property.PropertyType; //如果两个类型不一致 if (colType.ToString() != itemType.ToString()) { //如果值实现了IConvertible接口 if (value is IConvertible) { //直接将当前值转化成用户传进来的T类型 try { value = Convert.ChangeType(value, itemType); } catch { this.ErroMsg = $"{value} 的类型为 {colType},但在希望将其转成 {itemType} 时无法转换,请更正!"; return(null); } } else { //没有实现,直接返回其字符串形式 value = value.ToString(); } } if (value != DBNull.Value) { property.SetValue(tobj, value, null); } else { property.SetValue(tobj, null, null); } } list.Add(tobj); } return(list); } } catch (Exception ex) { this.ErroMsg = ex.Message; return(null); } finally { this.DisposeCommand(cmd); } }
/// <summary> /// Registration , update for SQL contents generation . Primary key recovery . /// 登録、更新用SQL中身生成。プライマリキー回収。 /// </summary> /// <param name="component"></param> /// <param name="isInsert"></param> /// <returns></returns> private string GetInsertUpdateSql(object component, bool isInsert) { StringBuilder stringBuilder = new StringBuilder(); StringBuilder propertyBuilder = new StringBuilder(); stringBuilder.AppendLine(); string insertSql = " ({0}) values ({1})"; string updateSql = "{0} = '{1}'"; bool firstProperty = true; Type type = component.GetType(); PropertyInfo[] propertyInfoArray = type.GetProperties(); foreach (PropertyInfo propertyInfo in propertyInfoArray) { if (propertyInfo.DeclaringType.Equals(type)) { DataPropertyAttribute attribute = this.GetDataPropertyAttribute <DataPropertyAttribute>(propertyInfo); if (attribute != null) { object value = this.GetSqliteTypeValue(attribute, propertyInfo, component); // プライマリキー取得 // Primary key acquisition if (attribute.IsPrimaryKey && !isInsert) { this.AddPrimaryKey(value, propertyInfo.Name); } if (value == null && !isInsert) { continue; } if (firstProperty) { firstProperty = false; } else { stringBuilder.Append(","); propertyBuilder.Append(","); } // insert if (isInsert) { stringBuilder.AppendLine(string.Format("'{0}'", (value == null) ? "" : value.ToString())); propertyBuilder.AppendLine(propertyInfo.Name); } // update else { stringBuilder.AppendLine(string.Format(updateSql, propertyInfo.Name, value.ToString())); } } } } if (isInsert) { propertyBuilder.AppendLine(",CreateDate"); propertyBuilder.AppendLine(",UpdateDate"); stringBuilder.AppendLine(string.Format(",'{0}'", this.GetDateFormat(DateTime.Now))); stringBuilder.AppendLine(string.Format(",'{0}'", this.GetDateFormat(DateTime.Now))); return(string.Format(insertSql, propertyBuilder.ToString(), stringBuilder.ToString())); } else { stringBuilder.AppendLine(string.Format(",UpdateDate = '{0}'", this.GetDateFormat(DateTime.Now))); return(stringBuilder.ToString()); } }