/// <summary>
/// Query the database by sql statement and return the array.
/// </summary>
/// <returns>Returns the result of the query as a array.</returns>
/// <param name="InSQLStatement">In SQL Statement.</param>
/// <typeparam name="T">Subclass of Base.</typeparam>
public T[] SelectArrayT <T>(string InSQLStatement = "") where T : Base, new()
{
SyncProperty property = SyncFactory.GetSyncProperty(typeof(T));
SQLite3Statement stmt = ExecuteQuery(InSQLStatement);
List <T> resultList = new List <T>();
SQLite3Result sqlite3Result;
int count = SQLite3.ColumnCount(stmt);
while (true)
{
sqlite3Result = SQLite3.Step(stmt);
if (SQLite3Result.Row == sqlite3Result)
{
resultList.Add(GetT(new T(), property.Infos, stmt, count));
}
else if (SQLite3Result.Done == sqlite3Result)
{
break;
}
else
{
throw new Exception(SQLite3.GetErrmsg(stmt));
}
}
SQLite3.Finalize(stmt);
return(resultList.ToArray());
}
/// <summary>
/// The value obtained by Key Reflection updates the table
/// </summary>
/// <param name="InIndex">The index of the object property.</param>
/// <param name="InValue">Base subclass object</param>
/// <typeparam name="T">Subclass of Base.</typeparam>
public void UpdateTByKeyValue <T>(int InIndex, T InValue) where T : Base
{
if (null == InValue)
{
throw new ArgumentNullException();
}
SyncProperty property = SyncFactory.GetSyncProperty(typeof(T));
if (InIndex < 0 || InIndex >= property.InfosLength)
{
throw new ArgumentOutOfRangeException();
}
stringBuilder.Remove(0, stringBuilder.Length);
stringBuilder.Append("UPDATE ")
.Append(property.ClassName)
.Append(" SET ")
.Append(property.Infos[InIndex].Name)
.Append(" = '")
.Append(property.Infos[InIndex].GetValue(InValue, null).ToString().Replace("'", "''"))
.Append("' WHERE ID = ")
.Append(property.Infos[0].GetValue(InValue, null));
Exec(stringBuilder.ToString());
}
/// <summary>
/// Query the database by property names and expected value and return the array.
/// </summary>
/// <returns>Returns the result of the query as a dictionary.</returns>
/// <param name="InPropertyNames">property names.</param>
/// <param name="InOperators">Operators between properties and expected values.</param>
/// <param name="InExpectedValues">Expected values.</param>
/// <typeparam name="T">Subclass of Base.</typeparam>
public T[] SelectArrayT <T>(string[] InPropertyNames, string[] InOperators, int[] InExpectedValues) where T : Base, new()
{
if (null == InPropertyNames || null == InOperators || null == InExpectedValues)
{
throw new ArgumentNullException();
}
int length = InPropertyNames.Length;
if (length != InOperators.Length || length != InExpectedValues.Length)
{
throw new ArgumentException("Parameter length does not match.");
}
SyncProperty property = SyncFactory.GetSyncProperty(typeof(T));
stringBuilder.Remove(0, stringBuilder.Length);
stringBuilder.Append("SELECT * FROM ")
.Append(property.ClassName)
.Append(" WHERE ");
for (int i = 0; i < length; i++)
{
stringBuilder.Append(InPropertyNames[i])
.Append(" ")
.Append(InOperators[i])
.Append(" ")
.Append(InExpectedValues[i])
.Append(" AND ");
}
stringBuilder.Remove(stringBuilder.Length - 5, 5);
return(SelectArrayT <T>(stringBuilder.ToString()));
}
/// <summary>
/// According to the subclass of Base to update the table.
/// </summary>
/// <param name="InValue">Base object.</param>
/// <typeparam name="T">subclass of Base</typeparam>
public void UpdateT <T>(T InValue) where T : Base
{
if (null == InValue)
{
throw new ArgumentNullException();
}
SyncProperty property = SyncFactory.GetSyncProperty(typeof(T));
stringBuilder.Remove(0, stringBuilder.Length);
stringBuilder.Append("UPDATE ").Append(property.ClassName).Append(" SET ");
int length = property.Infos.Length;
for (int i = 1; i < length; i++)
{
stringBuilder.Append(property.Infos[i].Name)
.Append(" = '")
.Append(property.Infos[i].GetValue(InValue, null).ToString().Replace("'", "''"))
.Append("', ");
}
stringBuilder.Remove(stringBuilder.Length - 2, 2);
stringBuilder.Append(" WHERE ID = ").Append(property.Infos[0].GetValue(InValue, null));
Exec(stringBuilder.ToString());
}
/// <summary>
/// Insert some Base subclasses into the table.
/// </summary>
/// <param name="InValue">Some Base subclasses list.</param>
/// <typeparam name="T">subclass of Base.</typeparam>
public void InsertAllT <T>(List <T> InValue) where T : Base
{
if (null == InValue)
{
throw new ArgumentNullException();
}
int count = InValue.Count;
if (count > 0)
{
SyncProperty property = SyncFactory.GetSyncProperty(typeof(T));
for (int i = 0; i < count; ++i)
{
stringBuilder.Remove(0, stringBuilder.Length);
stringBuilder.Append("INSERT INTO ").Append(property.ClassName).Append(" VALUES(");
int length = property.Infos.Length;
for (int j = 0; j < length; j++)
{
stringBuilder.Append("'")
.Append(property.Infos[j].GetValue(InValue[i], null).ToString().Replace("'", "''"))
.Append("', ");
}
stringBuilder.Remove(stringBuilder.Length - 2, 2);
stringBuilder.Append(")");
Exec(stringBuilder.ToString());
}
}
}
/// <summary>
/// Clear table data by Base of subclass.
/// </summary>
/// <typeparam name="T">Subclass of Base.</typeparam>
public void DeleteAllT <T>() where T : Base
{
SyncProperty property = SyncFactory.GetSyncProperty(typeof(T));
stringBuilder.Remove(0, stringBuilder.Length);
stringBuilder.Append("DELETE FROM ")
.Append(property.ClassName);
Exec(stringBuilder.ToString());
Exec("VACUUM"); //rebuild the built-in index.
}
/// <summary>
/// Query the object from the database by sql statement.
/// </summary>
/// <returns>Base subclass object.</returns>
/// <param name="InSQLStatement">In sql statement.</param>
/// <typeparam name="T">Subclass of Base.</typeparam>
public T SelectT <T>(string InSQLStatement) where T : Base, new()
{
SyncProperty property = SyncFactory.GetSyncProperty(typeof(T));
SQLite3Statement stmt = ExecuteQuery(InSQLStatement);
T t = GetT(new T(), property.Infos, stmt, property.InfosLength);
SQLite3.Finalize(stmt);
return(t);
}
/// <summary>
/// Query the object from the database by property name and perperty's value.
/// </summary>
/// <returns>Base subclass object.</returns>
/// <param name="InPropertyName">In property name.</param>
/// <param name="InExpectedValue">Expected values.</param>
/// <typeparam name="T">Subclass of Base.</typeparam>
public T SelectTByKeyValue <T>(string InPropertyName, object InExpectedValue) where T : Base, new()
{
stringBuilder.Remove(0, stringBuilder.Length);
stringBuilder.Append("SELECT * FROM ")
.Append(SyncFactory.GetSyncProperty(typeof(T)).ClassName)
.Append(" WHERE ")
.Append(InPropertyName)
.Append(" = ")
.Append(InExpectedValue);
return(SelectT <T>(stringBuilder.ToString()));
}
/// <summary>
/// Deletes the data by Base subclass object.
/// </summary>
/// <param name="InID">In Subclass object id.</param>
/// <typeparam name="T">Subclass of Base.</typeparam>
public void DeleteT <T>(T InID) where T : Base
{
SyncProperty property = SyncFactory.GetSyncProperty(typeof(T));
stringBuilder.Remove(0, stringBuilder.Length);
stringBuilder.Append("DELETE FROM ")
.Append(property.ClassName)
.Append(" WHERE ID = ")
.Append(property.Infos[0].GetValue(InID, null));
Exec(stringBuilder.ToString());
Exec("VACUUM"); //rebuild the built-in index.
}
/// <summary>
/// Creates the table.
/// </summary>
/// <typeparam name="T">Subclass of Base.</typeparam>
public void CreateTable <T>() where T : Base
{
SyncProperty property = SyncFactory.GetSyncProperty(typeof(T));
Exec("DROP TABLE IF EXISTS " + property.ClassName);
stringBuilder.Remove(0, stringBuilder.Length);
stringBuilder.Append("CREATE TABLE ").Append(property.ClassName).Append("(");
int length = property.Infos.Length;
for (int i = 0; i < length; ++i)
{
stringBuilder.Append(property.Infos[i].Name);
Type type = property.Infos[i].PropertyType;
if (type == typeof(int) || type == typeof(long))
{
stringBuilder.Append(" INTEGER ");
}
else if (type == typeof(string))
{
stringBuilder.Append(" TEXT ");
}
else if (type == typeof(float) || type == typeof(double))
{
stringBuilder.Append(" REAL ");
}
else
{
stringBuilder.Append(" BLOB ");
}
object[] objs = property.Infos[i].GetCustomAttributes(typeof(SQLite3ConstraintAttribute), false);
if (objs.Length == 1 && objs[0] is SQLite3ConstraintAttribute)
{
stringBuilder.Append((objs[0] as SQLite3ConstraintAttribute).Constraint);
}
stringBuilder.Append(", ");
}
stringBuilder.Remove(stringBuilder.Length - 2, 2);
stringBuilder.Append(")");
Exec(stringBuilder.ToString());
}
/// <summary>
/// Query the object from the database by property index and perperty's value.
/// </summary>
/// <returns>Base subclass object.</returns>
/// <param name="InPropertyIndex">In property index, The index value is specified by the SyncAttribute.</param>
/// <param name="InExpectedValue">Expected values.</param>
/// <typeparam name="T">Subclass of Base.</typeparam>
public T SelectTByKeyValue <T>(int InPropertyIndex, object InExpectedValue) where T : Base, new()
{
SyncProperty property = SyncFactory.GetSyncProperty(typeof(T));
if (InPropertyIndex < 0 || InPropertyIndex >= property.InfosLength)
{
throw new IndexOutOfRangeException();
}
stringBuilder.Remove(0, stringBuilder.Length);
stringBuilder.Append("SELECT * FROM ")
.Append(property.ClassName)
.Append(" WHERE ")
.Append(property.Infos[InPropertyIndex])
.Append(" = ")
.Append(InExpectedValue);
return(SelectT <T>(stringBuilder.ToString()));
}
/// <summary>
/// According to the Base subclass object updates the table or insert into the table.
/// </summary>
/// <param name="InT">Base subclass object.</param>
/// <typeparam name="T">Subclass of Base.</typeparam>
public void UpdateOrInsert <T>(T InT) where T : Base
{
if (null == InT)
{
throw new ArgumentNullException();
}
SyncProperty property = SyncFactory.GetSyncProperty(typeof(T));
SQLite3Statement stmt;
stringBuilder.Remove(0, stringBuilder.Length);
stringBuilder.Append("SELECT * FROM ")
.Append(property.ClassName)
.Append(" WHERE ID = ")
.Append(property.Infos[0].GetValue(InT, null));
bool isUpdate = false;
string sql = stringBuilder.ToString();
if (SQLite3Result.OK == SQLite3.Prepare2(handle, sql, GetUTF8ByteCount(sql), out stmt, IntPtr.Zero))
{
if (SQLite3Result.Row == SQLite3.Step(stmt))
{
isUpdate = SQLite3.ColumnCount(stmt) > 0;
}
}
else
{
throw new Exception(SQLite3.GetErrmsg(handle));
}
SQLite3.Finalize(stmt);
if (isUpdate)
{
UpdateT(InT);
}
else
{
InsertT(InT);
}
}
/// <summary>
/// Insert subclass of Base into the table.
/// </summary>
/// <param name="InValue">Subclass of Base object.</param>
/// <typeparam name="T">Subclass of Base</typeparam>
public void InsertT <T>(T InValue) where T : Base
{
SyncProperty property = SyncFactory.GetSyncProperty(typeof(T));
stringBuilder.Remove(0, stringBuilder.Length);
stringBuilder.Append("INSERT INTO ").Append(property.ClassName).Append(" VALUES(");
int length = property.Infos.Length;
for (int i = 0; i < length; i++)
{
stringBuilder.Append("'")
.Append(property.Infos[i].GetValue(InValue, null).ToString().Replace("'", "''"))
.Append("', ");
}
stringBuilder.Remove(stringBuilder.Length - 2, 2);
stringBuilder.Append(")");
Exec(stringBuilder.ToString());
}
/// <summary>
/// Query the dictionary from the database by sql statement.
/// </summary>
/// <returns>Returns the result of the query as a dictionary.</returns>
/// <param name="InSQLStatement">In SQL Statement</param>
/// <typeparam name="T">Subclass of Base.</typeparam>
public Dictionary <int, T> SelectDictT <T>(string InSQLStatement = "") where T : Base, new()
{
SyncProperty property = SyncFactory.GetSyncProperty(typeof(T));
stringBuilder.Remove(0, stringBuilder.Length);
stringBuilder.Append("SELECT * FROM ")
.Append(property.ClassName)
.Append(InSQLStatement);
SQLite3Statement stmt = ExecuteQuery(stringBuilder.ToString());
Dictionary <int, T> resultDict = new Dictionary <int, T>();
int count = SQLite3.ColumnCount(stmt), id;
SQLite3Result result;
while (true)
{
result = SQLite3.Step(stmt);
if (SQLite3Result.Row == result)
{
T t = GetT(new T(), property.Infos, stmt, count);
id = (int)property.Infos[0].GetValue(t, null);
if (!resultDict.ContainsKey(id))
{
resultDict.Add(id, t);
}
}
else if (SQLite3Result.Done == result)
{
break;
}
else
{
throw new Exception(SQLite3.GetErrmsg(stmt));
}
}
SQLite3.Finalize(stmt);
return(resultDict);
}
/// <summary>
/// Query the object from the database by index.
/// </summary>
/// <returns>Base subclass object.</returns>
/// <param name="InIndex">In index as key, the index value is automatically generated by the database.</param>
/// <typeparam name="T">Subclass of Base.</typeparam>
public T SelectTByIndex <T>(int InIndex) where T : Base, new()
{
return(SelectT <T>("SELECT * FROM "
+ SyncFactory.GetSyncProperty(typeof(T)).ClassName
+ " WHERE rowid = " + (InIndex + 1))); //SQLite3 rowid begin with 1.
}
/// <summary>
/// Query the object from the database by ID.
/// </summary>
/// <returns>Base subclass object.</returns>
/// <param name="InID">In table id as key.</param>
/// <typeparam name="T">Subclass of Base.</typeparam>
public T SelectTByID <T>(int InID) where T : Base, new()
{
return(SelectT <T>("SELECT * FROM "
+ SyncFactory.GetSyncProperty(typeof(T)).ClassName
+ " WHERE ID = " + InID));
}