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