public DodoItemCollection GetItems(int skip, int take, string userId)
{
var ret = new DodoItemCollection();
using (var conn = CreateConnection())
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "select uniqueId from {0} where " +
"visible = true and " +
"userid = ?userId ";
cmd.CommandText += "order by keywords {1} ";
cmd.CommandText += "limit {2}, {3} ";
cmd.CommandText = string.Format(cmd.CommandText,
DodoDb.table_poweq1,
DodoApplication.collateMySql,
skip, take);
cmd.Parameters.AddWithValue("userId", userId);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ret.Add(DodoItemBase.GetItem <DodoItem>(
app, reader.GetInt64(reader.GetOrdinal("uniqueId"))));
}
}
}
}
return(ret);
}
public DodoItemCollection SearchMyItems(string userId, string keySearch)
{
var ret = new DodoItemCollection();
using (var conn = CreateConnection())
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "select uniqueId from poweq1 where " +
"visible = true and " +
"userid = ?userId and ";
if (keySearch.IndexOf("|") > 0)
{
keySearch = keySearch.Replace("|", @"\.*");
cmd.CommandText += string.Format("keywords regexp '{0}' ", keySearch);
}
else
{
cmd.CommandText += "keywords like concat('%', @keywords, '%') ";
cmd.Parameters.AddWithValue("keywords", keySearch);
}
cmd.CommandText += "order by keywords {0}";
cmd.CommandText = string.Format(cmd.CommandText,
DodoApplication.collateMySql);
cmd.Parameters.AddWithValue("userId", userId);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ret.Add(DodoItemBase.GetItem <DodoItem>(
app, reader.GetInt64(reader.GetOrdinal("uniqueId"))));
}
}
}
}
return(ret);
}
public DodoItemCollection SearchMyRecords(int userId, string keySearch, long folderId)
{
var ret = new DodoItemCollection();
using (var conn = CreateConnection())
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "select a.uniqueId " +
"from poweq1 a where " +
"a.userid = ?userId and " +
"a.keywords like concat('%', ?keywords, '%')";
if (folderId > -1)
{
cmd.CommandText += " and a.folderId = ?folderId";
}
cmd.CommandText += " order by a.weight desc, a.keywords {0}";
cmd.CommandText = string.Format(cmd.CommandText, DodoApplication.collateMySql);
cmd.Parameters.AddWithValue("userId", userId);
cmd.Parameters.AddWithValue("keywords", keySearch);
cmd.Parameters.AddWithValue("folderId", folderId);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ret.Add(DodoItemBase.GetItem <DodoItem>(
app, reader.GetInt64(reader.GetOrdinal("uniqueId"))));
}
}
}
return(ret);
}