/// <summary>
/// Creates a list from the data entries in a cache table, using the given where clause and predicate.
/// </summary>
public static List <T> BuildListFromTable <T>(string connectionString, string table, WhereClause where, Func <CacheReader, T> func)
{
List <T> list = new List <T>();
using (SqliteConnection db = new SqliteConnection(connectionString))
{
db.Open();
// Check how large the result set will be so we're not constantly
// Reallocating the array.
string query = "select count(*) from " + table + " ";
if (where != null)
{
query += where.GetSql();
}
using (SqliteCommand cmd = new SqliteCommand(query, db))
{
if (where != null)
{
where.AddParameters(cmd);
}
int val = (int)(long)cmd.ExecuteScalar();
list = new List <T>(val);
}
// Set up the actual full query.
query = "select * from " + table;
if (where != null)
{
query += where.GetSql();
}
using (SqliteCommand cmd = new SqliteCommand(query, db))
{
if (where != null)
{
where.AddParameters(cmd);
}
using (CacheReader reader = new CacheReader(cmd.ExecuteReader()))
{
while (reader.NextRow())
{
try
{
list.Add(func(reader));
}
catch (Exception)
{
throw;
}
}
}
}
}
return(list);
}
/// <summary>
/// Creates a list from the data entries in a cache table, using the given where clause and predicate.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="table"></param>
/// <param name="where"></param>
/// <param name="func"></param>
/// <returns></returns>
private async Task <List <T> > BuildListFromTable <T>(string table, WhereClause where, Func <CacheReader, Task <T> > func)
{
List <T> list = new List <T>();
using (var db = new SQLiteConnection(_connectionString))
{
db.Open();
// Check how large the result set will be so we're not constantly
// Reallocating the array.
var query = "select count(*) from " + table + " ";
if (where != null)
{
query += where.GetSql();
}
using (var cmd = new SQLiteCommand(query, db))
{
if (where != null)
{
where.AddParameters(cmd);
}
int val = (int)((long)await cmd.ExecuteScalarAsync());
list = new List <T>(val);
}
// Set up the actual full query.
query = "select * from " + table;
if (where != null)
{
query += where.GetSql();
}
using (var cmd = new SQLiteCommand(query, db))
{
if (where != null)
{
where.AddParameters(cmd);
}
using (var reader = new CacheReader(cmd.ExecuteReader()))
{
while (reader.NextRow())
{
try
{
list.Add(await func(reader));
}
catch (Exception ex)
{
throw ex;
}
}
}
}
}
return(list);
}
public void Create()
{
var item1 = new WhereItem("field1", 123);
var parm = new QueryParameter("someparm", "testvalue");
var item2 = new WhereString("field2=@someparm", parm);
var clause = new WhereClause(JoinType.And, item1, item2);
Assert.AreEqual("field1=@field1 AND (field2=@someparm)", clause.GetSql());
Assert.AreEqual(2, clause.Parameters.Count());
}
public void Combine()
{
var item1 = new WhereItem("field1", 123);
var parm = new QueryParameter("someparm", "testvalue");
var item2 = new WhereString("field2=@someparm or field3='value'", parm);
var item3 = new WhereItem("field4", 123, parameterize: false);
var clause = new WhereClause(JoinType.And, item1, item2);
Assert.AreEqual("field1=@field1 AND (field2=@someparm or field3='value')", clause.GetSql());
var newClause = clause.Clone();
newClause.Add(item3, JoinType.Or);
Assert.AreEqual("(field1=@field1 AND (field2=@someparm or field3='value')) OR field4=123", newClause.GetSql());
Assert.AreEqual(2, newClause.Parameters.Count());
}