/// <summary>
/// 从SQLite读取数据
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="_tableAttribute">表属性</param>
/// <returns>数据集</returns>
static Dictionary <int, T> OnReadFromSQLite <T>(SQLiteTableMapAttribute _tableAttribute)
where T : AbsStrayFogSQLiteEntity
{
Dictionary <int, T> result = new Dictionary <int, T>();
SqliteDataReader reader = msStrayFogSQLiteHelperMaping[_tableAttribute.dbSQLiteKey].ReadFullTable(_tableAttribute.sqliteTableName);
T tempEntity = default(T);
string tempPropertyName = string.Empty;
int tempPropertyKey = 0;
object tempValue = null;
if (_tableAttribute.isDeterminant)
{
tempEntity = OnCreateInstance <T>(_tableAttribute);
#region 行列式表
while (reader.Read())
{
tempPropertyName = reader.GetString(_tableAttribute.xlsColumnNameIndex - 1);
tempPropertyKey = tempPropertyName.UniqueHashCode();
tempValue = reader.GetString(_tableAttribute.xlsColumnValueIndex - 1);
tempValue = StrayFogSQLiteDataTypeHelper.GetXlsCSTypeColumnValue(tempValue, msEntityPropertyInfoMaping[_tableAttribute.id][tempPropertyKey], msEntitySQLitePropertySQLiteFieldTypeAttributeMaping[_tableAttribute.id][tempPropertyKey].dataType, msEntitySQLitePropertySQLiteFieldTypeAttributeMaping[_tableAttribute.id][tempPropertyKey].arrayDimension);
msEntityPropertyInfoMaping[_tableAttribute.id][tempPropertyKey].SetValue(tempEntity, tempValue, null);
}
#endregion
tempEntity.Resolve();
result.Add(tempEntity.pkSequenceId, tempEntity);
}
else
{
#region 普通表
while (reader.Read())
{
tempEntity = OnCreateInstance <T>(_tableAttribute);
for (int i = 0; i < reader.FieldCount; i++)
{
tempPropertyName = reader.GetName(i);
tempPropertyKey = tempPropertyName.UniqueHashCode();
if (!msEntitySQLitePropertySQLiteFieldTypeAttributeMaping[_tableAttribute.id][tempPropertyKey].isIngore)
{
tempValue = reader.GetValue(i);
tempValue = StrayFogSQLiteDataTypeHelper.GetXlsCSTypeColumnValue(tempValue, msEntityPropertyInfoMaping[_tableAttribute.id][tempPropertyKey], msEntitySQLitePropertySQLiteFieldTypeAttributeMaping[_tableAttribute.id][tempPropertyKey].dataType, msEntitySQLitePropertySQLiteFieldTypeAttributeMaping[_tableAttribute.id][tempPropertyKey].arrayDimension);
msEntityPropertyInfoMaping[_tableAttribute.id][tempPropertyKey].SetValue(tempEntity, tempValue, null);
}
}
tempEntity.Resolve();
result.Add(tempEntity.pkSequenceId, tempEntity);
}
reader.Close();
reader = null;
#endregion
}
return(result);
}
/// <summary>
/// 插入数据到SQLite
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="_entity">实体</param>
/// <param name="_tableAttribute">表属性</param>
static void OnInsertIntoSQLite <T>(T _entity, SQLiteTableMapAttribute _tableAttribute)
where T : AbsStrayFogSQLiteEntity
{
List <string> values = new List <string>();
List <SqliteParameter> valueSps = new List <SqliteParameter>();
foreach (KeyValuePair <int, SQLiteFieldTypeAttribute> key in msEntitySQLitePropertySQLiteFieldTypeAttributeMaping[_tableAttribute.id])
{
values.Add(key.Value.sqliteParameterName);
valueSps.Add(new SqliteParameter(key.Value.sqliteParameterName, StrayFogSQLiteDataTypeHelper.GetValueFromEntityPropertyToXlsColumn(_entity, msEntityPropertyInfoMaping[_tableAttribute.id][key.Key], key.Value)));
}
string sql = string.Format("INSERT INTO {0} VALUES({1})", _tableAttribute.sqliteTableName, string.Join(",", values.ToArray()));
msStrayFogSQLiteHelperMaping[_tableAttribute.dbSQLiteKey].ExecuteNonQuery(sql, valueSps.ToArray());
}
/// <summary>
/// 从SQLite删除数据
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="_entity">实体</param>
/// <param name="_tableAttribute">表属性</param>
static void OnDeleteFromSQLite <T>(T _entity, SQLiteTableMapAttribute _tableAttribute)
{
List <string> pks = new List <string>();
List <SqliteParameter> sps = new List <SqliteParameter>();
foreach (KeyValuePair <int, SQLiteFieldTypeAttribute> key in msEntitySQLitePropertySQLiteFieldTypeAttributeMaping[_tableAttribute.id])
{
if (key.Value.isPK)
{
pks.Add(key.Value.sqliteColumnName + "=" + key.Value.sqliteParameterName);
sps.Add(new SqliteParameter(key.Value.sqliteParameterName, StrayFogSQLiteDataTypeHelper.GetValueFromEntityPropertyToXlsColumn(_entity, msEntityPropertyInfoMaping[_tableAttribute.id][key.Key], key.Value)));
}
}
string sql = string.Format("DELETE FROM {0} WHERE {1}", _tableAttribute.sqliteTableName, string.Join(",", pks.ToArray()));
msStrayFogSQLiteHelperMaping[_tableAttribute.dbSQLiteKey].ExecuteNonQuery(sql, sps.ToArray());
}
/// <summary>
/// 更新数据到SQLite
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="_entity">实体</param>
/// <param name="_tableAttribute">表属性</param>
static void OnUpdateToSQLite <T>(T _entity, SQLiteTableMapAttribute _tableAttribute)
where T : AbsStrayFogSQLiteEntity
{
Dictionary <string, List <SqliteParameter> > dicSql = new Dictionary <string, List <SqliteParameter> >();
if (_tableAttribute.isDeterminant)
{
string pfxSet = "Set";
foreach (KeyValuePair <int, SQLiteFieldTypeAttribute> key in msEntitySQLitePropertySQLiteFieldTypeAttributeMaping[_tableAttribute.id])
{
dicSql.Add(string.Format("UPDATE {0} SET {1} WHERE {2}",
_tableAttribute.sqliteTableName,
key.Value.sqliteColumnValue + "=" + key.Value.sqliteParameterName + pfxSet,
key.Value.sqliteColumnName + "=" + key.Value.sqliteParameterName),
new List <SqliteParameter>()
{
new SqliteParameter(key.Value.sqliteParameterName + pfxSet, StrayFogSQLiteDataTypeHelper.GetValueFromEntityPropertyToXlsColumn(_entity, msEntityPropertyInfoMaping[_tableAttribute.id][key.Key], key.Value)),
new SqliteParameter(key.Value.sqliteParameterName, msEntityPropertyInfoMaping[_tableAttribute.id][key.Key].Name)
}
);
}
msStrayFogSQLiteHelperMaping[_tableAttribute.dbSQLiteKey].ExecuteTransaction(dicSql);
}
else
{
List <string> pks = new List <string>();
List <string> sets = new List <string>();
List <SqliteParameter> sps = new List <SqliteParameter>();
foreach (KeyValuePair <int, SQLiteFieldTypeAttribute> key in msEntitySQLitePropertySQLiteFieldTypeAttributeMaping[_tableAttribute.id])
{
if (key.Value.isPK)
{
pks.Add(key.Value.sqliteColumnName + "=" + key.Value.sqliteParameterName);
}
else
{
sets.Add(key.Value.sqliteColumnName + "=" + key.Value.sqliteParameterName);
}
sps.Add(new SqliteParameter(key.Value.sqliteParameterName, StrayFogSQLiteDataTypeHelper.GetValueFromEntityPropertyToXlsColumn(_entity, msEntityPropertyInfoMaping[_tableAttribute.id][key.Key], key.Value)));
}
string sql = string.Format("UPDATE {0} SET {1} WHERE {2}", _tableAttribute.sqliteTableName, string.Join(",", sets.ToArray()), string.Join(",", pks.ToArray()));
msStrayFogSQLiteHelperMaping[_tableAttribute.dbSQLiteKey].ExecuteNonQuery(sql, sps.ToArray());
}
}
/// <summary>
/// 更新数据到XLS表
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="_entity">实体</param>
/// <param name="_tableAttribute">表属性</param>
/// <param name="_xlsRowIndex">行索引</param>
static void OnUpdateToXLS <T>(T _entity, SQLiteTableMapAttribute _tableAttribute, int _xlsRowIndex)
where T : AbsStrayFogSQLiteEntity
{
if (File.Exists(_tableAttribute.xlsFilePath))
{
ExcelPackage pck = OnGetExcelPackage(_tableAttribute);
{
if (pck.Workbook.Worksheets.Count > 0)//消耗2秒
{
ExcelWorksheet sheet = pck.Workbook.Worksheets[1];
if (_tableAttribute.isDeterminant)
{
foreach (KeyValuePair <int, SQLiteFieldTypeAttribute> key in msEntitySQLitePropertySQLiteFieldTypeAttributeMaping[_tableAttribute.id])
{
sheet.Cells[key.Value.xlsColumnValueIndex, _tableAttribute.xlsColumnValueIndex].Value = StrayFogSQLiteDataTypeHelper.GetValueFromEntityPropertyToXlsColumn(_entity, msEntityPropertyInfoMaping[_tableAttribute.id][key.Key], key.Value);
}
}
else
{
if (sheet.Dimension.Rows >= _tableAttribute.xlsColumnValueIndex)
{
foreach (KeyValuePair <int, SQLiteFieldTypeAttribute> key in msEntitySQLitePropertySQLiteFieldTypeAttributeMaping[_tableAttribute.id])
{
sheet.Cells[_xlsRowIndex, key.Value.xlsColumnValueIndex].Value = StrayFogSQLiteDataTypeHelper.GetValueFromEntityPropertyToXlsColumn(_entity, msEntityPropertyInfoMaping[_tableAttribute.id][key.Key], key.Value);
}
}
}
}
}
}
}
/// <summary>
/// 从XLS读取数据
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="_tableAttribute">表属性</param>
/// <returns>数据集</returns>
static Dictionary <int, T> OnReadFromXLS <T>(SQLiteTableMapAttribute _tableAttribute)
where T : AbsStrayFogSQLiteEntity
{
Dictionary <int, T> result = new Dictionary <int, T>();
if (File.Exists(_tableAttribute.xlsFilePath))
{
T tempEntity = default;
int tempPropertyKey = 0;
object tempValue = null;
string tempName = string.Empty;
bool tempIsAllValueNull = false;
ExcelPackage pck = OnGetExcelPackage(_tableAttribute);
{
if (pck.Workbook.Worksheets.Count > 0)
{
ExcelWorksheet sheet = pck.Workbook.Worksheets[1];
if (_tableAttribute.isDeterminant)
{
#region 行列式数据写入
if (sheet.Dimension.Rows >= _tableAttribute.xlsColumnValueIndex)
{
tempEntity = OnCreateInstance <T>(_tableAttribute);
for (int row = _tableAttribute.xlsDataStartRowIndex; row <= sheet.Dimension.Rows; row++)
{
tempName = sheet.GetValue <string>(row, _tableAttribute.xlsColumnNameIndex).Trim();
tempValue = sheet.GetValue(row, _tableAttribute.xlsColumnValueIndex);
//如果名称为空，则认为是数据结束
if (string.IsNullOrEmpty(tempName))
{
break;
}
else
{
tempPropertyKey = tempName.UniqueHashCode();
tempValue = StrayFogSQLiteDataTypeHelper.GetXlsCSTypeColumnValue(tempValue, msEntityPropertyInfoMaping[_tableAttribute.id][tempPropertyKey], msEntitySQLitePropertySQLiteFieldTypeAttributeMaping[_tableAttribute.id][tempPropertyKey].dataType, msEntitySQLitePropertySQLiteFieldTypeAttributeMaping[_tableAttribute.id][tempPropertyKey].arrayDimension);
msEntityPropertyInfoMaping[_tableAttribute.id][tempPropertyKey].SetValue(tempEntity, tempValue, null);
}
}
tempEntity.Resolve();
result.Add(tempEntity.pkSequenceId, tempEntity);
}
#endregion
}
else
{
#region 普通数据写入
if (sheet.Dimension.Rows >= _tableAttribute.xlsColumnValueIndex)
{
for (int row = _tableAttribute.xlsDataStartRowIndex; row <= sheet.Dimension.Rows; row++)
{
tempEntity = OnCreateInstance <T>(_tableAttribute);
tempIsAllValueNull = true;
for (int col = 1; col <= sheet.Dimension.Columns; col++)
{
tempName = sheet.GetValue <string>(_tableAttribute.xlsColumnNameIndex, col).Trim();
tempPropertyKey = tempName.UniqueHashCode();
#if UNITY_EDITOR
if (!msEntitySQLitePropertySQLiteFieldTypeAttributeMaping[_tableAttribute.id].ContainsKey(tempPropertyKey))
{
Debug.LogErrorFormat("Can't find column 【{0}】for table 【{1}】", tempName, _tableAttribute.sqliteTableName);
}
#endif
if (!msEntitySQLitePropertySQLiteFieldTypeAttributeMaping[_tableAttribute.id][tempPropertyKey].isIngore)
{
tempValue = sheet.GetValue(row, col);
tempIsAllValueNull &= (tempValue == null);
tempValue = StrayFogSQLiteDataTypeHelper.GetXlsCSTypeColumnValue(tempValue, msEntityPropertyInfoMaping[_tableAttribute.id][tempPropertyKey], msEntitySQLitePropertySQLiteFieldTypeAttributeMaping[_tableAttribute.id][tempPropertyKey].dataType, msEntitySQLitePropertySQLiteFieldTypeAttributeMaping[_tableAttribute.id][tempPropertyKey].arrayDimension);
msEntityPropertyInfoMaping[_tableAttribute.id][tempPropertyKey].SetValue(tempEntity, tempValue, null);
}
}
if (tempIsAllValueNull)
{//如果所有列为空，则认为是数据结束
break;
}
else
{
tempEntity.Resolve();
result.Add(tempEntity.pkSequenceId, tempEntity);
}
}
}
#endregion
}
}
}
}
else
{
result = OnLoadViewFromXLS <T>(_tableAttribute);
}
return(result);
}
