public T newRow <T>() where T : new()
{
T tRow = default(T);
T_SQLiteTable sqlTable = findTable <T>();
if (sqlTable == null)
{
return(tRow);
}
tRow = new T();
foreach (F_SQLiteField tField in sqlTable.Fields)
{
F_SQLiteField fField = tField.clone();
typeof(T).GetProperty(fField.Name).SetValue(tRow, fField, null);
}
return(tRow);
}
public F_SQLiteField clone()
{
F_SQLiteField clone = new F_SQLiteField(this.tableName, this.name, this.schemaType);
return(clone);
}
public void readSchema()
{
validDB = false;
if (!File.Exists(dbFilePath))
{
return;
}
sqlTables = new List <T_SQLiteTable>();
using (SQLiteConnection sCon = new SQLiteConnection(dbDataSource))
{
sCon.Open();
using (SQLiteCommand sCmd = new SQLiteCommand(sCon))
{
sCmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table';";
using (SQLiteDataReader sReader = sCmd.ExecuteReader())
{
if (!sReader.HasRows)
{
return;
}
while (sReader.Read())
{
string tableName = sReader.GetString(0);
string @namespace = "SQLite806x";
string @rowClass = "R_" + tableName;
Type tableClassType = Type.GetType(string.Format("{0}.{1}", @namespace, @rowClass));
if (tableClassType == null)
{
continue;
}
sqlTables.Add(new T_SQLiteTable(tableName));
}
}
}
foreach (T_SQLiteTable sqlTable in sqlTables)
{
using (SQLiteCommand sCmd = new SQLiteCommand(sCon))
{
sCmd.CommandText = "PRAGMA table_info([" + sqlTable.Name + "]);";
using (SQLiteDataReader sReader = sCmd.ExecuteReader())
{
string @namespace = this.GetType().Namespace;
string @rowClass = "R_" + sqlTable.Name;
Type tableClassType = Type.GetType(string.Format("{0}.{1}", @namespace, @rowClass));
if (tableClassType == null)
{
continue;
}
if (!sReader.HasRows)
{
continue;
}
//RowId forced
F_SQLiteField rIdField = new F_SQLiteField(sqlTable.Name, "RowId", "INT");
if (tableClassType.GetProperty(rIdField.Name) != null)
{
sqlTable.Fields.Add(rIdField);
}
while (sReader.Read())
{
F_SQLiteField fField = new F_SQLiteField(sqlTable.Name, sReader.GetString(1), sReader.GetString(2));
if (tableClassType.GetProperty(fField.Name) == null)
{
continue;
}
sqlTable.Fields.Add(fField);
}
}
}
}
sCon.Close();
// TO BE REVIEWED
validDB = true;
}
}
public bool Write <T>(ref List <T> rList)
{
T_SQLiteTable sqlTable = findTable <T>();
if (sqlTable == null)
{
return(false);
}
Type rType = typeof(T);
using (SQLiteConnection sCon = new SQLiteConnection(dbDataSource))
{
sCon.Open();
string updateCmd = "UPDATE [" + sqlTable.Name + "] SET ";
string insertCmd = "INSERT INTO [" + sqlTable.Name + "] (";
foreach (F_SQLiteField tField in sqlTable.Fields)
{
if (tField.Name.ToLower() == "rowid")
{
continue;
}
updateCmd += tField.Name + " = :p" + tField.Name.ToUpper() + ", ";
insertCmd += tField.Name + ", ";
}
updateCmd = updateCmd.Substring(0, updateCmd.Length - 2);
updateCmd += " WHERE RowId = :pROWID;";
insertCmd = insertCmd.Substring(0, insertCmd.Length - 2);
insertCmd += ") VALUES (";
foreach (F_SQLiteField tField in sqlTable.Fields)
{
if (tField.Name.ToLower() == "rowid")
{
continue;
}
insertCmd += ":p" + tField.Name.ToUpper() + ", ";
}
insertCmd = insertCmd.Substring(0, insertCmd.Length - 2);
insertCmd += ");";
foreach (T rRow in rList)
{
bool insertMode = ((F_SQLiteField)rType.GetProperty("RowId").GetValue(rRow, null)).Value == null;
using (SQLiteCommand sCmd = new SQLiteCommand(sCon))
{
if (insertMode)
{
sCmd.CommandText = insertCmd;
}
else
{
sCmd.CommandText = updateCmd;
}
foreach (F_SQLiteField tField in sqlTable.Fields)
{
if (insertMode && tField.Name.ToLower() == "rowid")
{
continue;
}
F_SQLiteField fField = (F_SQLiteField)rType.GetProperty(tField.Name).GetValue(rRow, null);
sCmd.Parameters.Add("p" + tField.Name.ToUpper(), tField.EDbType).Value = fField.Value;
}
sCmd.ExecuteNonQuery();
}
}
sCon.Close();
}
return(true);
}
public List <T> Read <T>() where T : new()
{
List <T> lResult = new List <T>();
T_SQLiteTable sqlTable = findTable <T>();
if (sqlTable == null)
{
return(lResult);
}
Type rType = typeof(T);
using (SQLiteConnection sCon = new SQLiteConnection(dbDataSource))
{
sCon.Open();
using (SQLiteCommand sCmd = new SQLiteCommand(sCon))
{
sCmd.CommandText = "SELECT RowId, * FROM [" + sqlTable.Name + "];";
using (SQLiteDataReader sReader = sCmd.ExecuteReader())
{
if (!sReader.HasRows)
{
return(lResult);
}
List <F_SQLiteField> queryFields = null;
while (sReader.Read())
{
if (queryFields == null)
{
queryFields = new List <F_SQLiteField>();
for (int iField = 0; iField < sReader.FieldCount; iField++)
{
F_SQLiteField tField = sqlTable.getField(sReader.GetName(iField));
if (tField == null)
{
queryFields.Add(null);
continue;
}
PropertyInfo pInfo = rType.GetProperty(tField.Name);
if (rType == null)
{
queryFields.Add(null);
continue;
}
queryFields.Add(tField);
}
}
T tRow = new T();
for (int iField = 0; iField < sReader.FieldCount; iField++)
{
F_SQLiteField tField = queryFields[iField];
if (tField == null)
{
continue;
}
F_SQLiteField fField = tField.clone();
fField.Value = sReader.GetValue(iField);
rType.GetProperty(fField.Name).SetValue(tRow, fField, null);
}
lResult.Add(tRow);
}
}
}
sCon.Close();
}
return(lResult);
}