public void TestDelete()
{
Task task = new Task("Darbas", DateTime.Now, "sourceId|15");
task.Save();
task.Delete();
using (IConnection con = new ConnectionCollector().GetConnection())
{
IDataReader reader = con.ExecuteReader("SELECT NAME, DATE_REMAINDER, SOURCE_ID FROM TASK");
Assert.IsTrue(reader.Read());
}
}
public void TestDelete()
{
Task task = new Task("Darbas", DateTime.Now, "sourceId|15");
task.Save();
task.Delete();
using (IConnection con = new ConnectionCollector().GetConnection())
{
IDataReader reader = con.ExecuteReader("SELECT NAME, DATE_REMAINDER, SOURCE_ID FROM TASK");
Assert.IsTrue(reader.Read());
}
}
public void TestExecutesDBVersions()
{
DBVersionRepository repo = new DBVersionRepository()
.AddVersions(typeof(TestDBVersionSystem).Assembly)
;
new DBVersionSystem(new DBVersionRepository().AddVersions(typeof(TestDBVersionsFile).Assembly)).UpdateToNewest();
using (IConnection con = new ConnectionCollector().GetConnection())
{
// DB Version was executed?
IDataReader reader = con.ExecuteReader("SELECT COUNT(*) FROM TROL_TEST");
Assert.IsTrue(reader.Read());
Assert.AreEqual(1, reader.GetInt32(0));
// Version number was written?
Assert.AreEqual(1, DBVersionSystem.GetCurrentVersion());
// DB version information is written corretly (was bug, which duplicated lines)
reader = con.ExecuteReader("SELECT COUNT(*) FROM DB_VERSION");
Assert.IsTrue(reader.Read());
Assert.AreEqual(1, reader.GetInt32(0));
}
}
public void TestSave()
{
DateTime now = DateTime.Now;
Task task = new Task("Darbas", now, "sourceId|15");
task.Save();
using (IConnection con = new ConnectionCollector().GetConnection())
{
IDataReader reader = con.ExecuteReader("SELECT NAME, DATE_REMAINDER, SOURCE_ID, SHOWED FROM TASK");
Assert.IsTrue(reader.Read());
Assert.AreEqual(task.Text, reader.GetString(0));
Assert.AreEqual(DBTypesConverter.ToFullDateStringByCultureInfo(task.DateRemainder), reader.GetString(1));
Assert.AreEqual(task.SourceId, reader.GetString(2));
Assert.AreEqual(0, reader.GetInt32(3));
}
}
public static int GetCurrentVersion()
{
using(IConnection con = new ConnectionCollector().GetConnection())
{
int max = 0;
try {
IDataReader dataReader = con.ExecuteReader("SELECT max(VERSION_NR) FROM DB_VERSION");
dataReader.Read();
if (dataReader.IsDBNull(0))
return max;
else
max = dataReader.GetInt32(0);
} catch (Exception) {
}
return max;
}
}
public Task NextTaskToShow()
{
using (IConnection connection = new ConnectionCollector().GetConnection())
{
string query = string.Format("select id, name, date_remainder, source_id from task where (showed is null " +
"or showed = 0 or SHOWED = '') and (IS_DELETED = 0 or IS_DELETED is null)" +
" order by date_remainder, id",
DBTypesConverter.ToFullDateStringByCultureInfoWithQuotes(DateTime.Now.AddSeconds(-15)));
IDataReader reader = connection.ExecuteReader(query);
while (reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
DateTime date = Convert.ToDateTime(reader.GetString(2), Static.StaticData.Settings.CultureData.CultureInfo);
string sourceId = reader.GetString(3);
return(new Task(id, name, date, sourceId));
}
return(null);
}
}
public void TestExecutesDBVersions()
{
DBVersionRepository repo = new DBVersionRepository()
.AddVersions(typeof(TestDBVersionSystem).Assembly)
;
new DBVersionSystem(new DBVersionRepository().AddVersions(typeof(TestDBVersionsFile).Assembly)).UpdateToNewest();
using (IConnection con = new ConnectionCollector().GetConnection())
{
// DB Version was executed?
IDataReader reader = con.ExecuteReader("SELECT COUNT(*) FROM TROL_TEST");
Assert.IsTrue(reader.Read());
Assert.AreEqual(1, reader.GetInt32(0));
// Version number was written?
Assert.AreEqual(1, DBVersionSystem.GetCurrentVersion());
// DB version information is written corretly (was bug, which duplicated lines)
reader = con.ExecuteReader("SELECT COUNT(*) FROM DB_VERSION");
Assert.IsTrue(reader.Read());
Assert.AreEqual(1, reader.GetInt32(0));
}
}
public List<Task> LoadTasksForShowing()
{
using(IConnection con = new ConnectionCollector().GetConnection())
{
string query = string.Format("SELECT ID, NAME, DATE_REMAINDER, SOURCE_ID, SHOWED from task where DATE_REMAINDER <= {0}" +
"and (SHOWED = 0 or SHOWED is null or SHOWED = '') and (IS_DELETED = 0 or IS_DELETED is null " +
"or IS_DELETED = '')",
DBTypesConverter.ToFullDateStringByCultureInfoWithQuotes(DateTime.Now));
IDataReader reader = con.ExecuteReader(query);
List<Task> tasks = new List<Task>();
while(reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
DateTime date = Convert.ToDateTime(reader.GetString(2), Static.StaticData.Settings.CultureData.CultureInfo);
string sourceId = reader.GetString(3);
tasks.Add(new Task(id, name, date, sourceId));
}
return tasks;
}
}
public List<Task> LoadAllTasks()
{
using(IConnection con = new ConnectionCollector().GetConnection())
{
string query = string.Format("SELECT ID, NAME, DATE_REMAINDER, SOURCE_ID, SHOWED from task where " +
" (IS_DELETED = 0 or IS_DELETED is null or IS_DELETED = '')");
IDataReader reader = con.ExecuteReader(query);
List<Task> tasks = new List<Task>();
while(reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
DateTime date = DateTime.Parse(reader.GetString(2));
string sourceId = reader.GetString(3);
Task task = new Task(id, name, date, sourceId);
task.Showed = GetBoolValue(reader[4]);
tasks.Add(task);
}
return tasks;
}
}
public List <Task> LoadAllTasks()
{
using (IConnection con = new ConnectionCollector().GetConnection())
{
string query = string.Format("SELECT ID, NAME, DATE_REMAINDER, SOURCE_ID, SHOWED from task where " +
" (IS_DELETED = 0 or IS_DELETED is null or IS_DELETED = '')");
IDataReader reader = con.ExecuteReader(query);
List <Task> tasks = new List <Task>();
while (reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
DateTime date = DateTime.Parse(reader.GetString(2));
string sourceId = reader.GetString(3);
Task task = new Task(id, name, date, sourceId);
task.Showed = GetBoolValue(reader[4]);
tasks.Add(task);
}
return(tasks);
}
}
public List <Task> LoadTasksForShowing()
{
using (IConnection con = new ConnectionCollector().GetConnection())
{
string query = string.Format("SELECT ID, NAME, DATE_REMAINDER, SOURCE_ID, SHOWED from task where DATE_REMAINDER <= {0}" +
"and (SHOWED = 0 or SHOWED is null or SHOWED = '') and (IS_DELETED = 0 or IS_DELETED is null " +
"or IS_DELETED = '')",
DBTypesConverter.ToFullDateStringByCultureInfoWithQuotes(DateTime.Now));
IDataReader reader = con.ExecuteReader(query);
List <Task> tasks = new List <Task>();
while (reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
DateTime date = Convert.ToDateTime(reader.GetString(2), Static.StaticData.Settings.CultureData.CultureInfo);
string sourceId = reader.GetString(3);
tasks.Add(new Task(id, name, date, sourceId));
}
return(tasks);
}
}
public static int GetCurrentVersion()
{
using (IConnection con = new ConnectionCollector().GetConnection())
{
int max = 0;
try {
IDataReader dataReader = con.ExecuteReader("SELECT max(VERSION_NR) FROM DB_VERSION");
dataReader.Read();
if (dataReader.IsDBNull(0))
{
return(max);
}
else
{
max = dataReader.GetInt32(0);
}
} catch (Exception) {
}
return(max);
}
}
private bool ExistTask(Task task)
{
if (string.IsNullOrEmpty(task.SourceId))
{
throw new Exception("Task without sourceId");
}
using (IConnection con = new ConnectionCollector().GetConnection())
{
IDataReader reader = con.ExecuteReader(string.Format("SELECT COUNT(ID) FROM TASK WHERE SOURCE_ID = '{0}'", task.SourceId));
int count = 0;
while (reader.Read())
{
count = reader.GetInt32(0);
}
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
}
private bool ExistTask(Task task)
{
if(string.IsNullOrEmpty(task.SourceId))
throw new Exception("Task without sourceId");
using(IConnection con = new ConnectionCollector().GetConnection())
{
IDataReader reader = con.ExecuteReader(string.Format("SELECT COUNT(ID) FROM TASK WHERE SOURCE_ID = '{0}'", task.SourceId));
int count = 0;
while(reader.Read())
{
count = reader.GetInt32(0);
}
if(count > 0)
return true;
else
return false;
}
}
public Task NextTaskToShow()
{
using(IConnection connection = new ConnectionCollector().GetConnection())
{
string query = string.Format("select id, name, date_remainder, source_id from task where (showed is null " +
"or showed = 0 or SHOWED = '') and (IS_DELETED = 0 or IS_DELETED is null)" +
" order by date_remainder, id",
DBTypesConverter.ToFullDateStringByCultureInfoWithQuotes(DateTime.Now.AddSeconds(-15)));
IDataReader reader = connection.ExecuteReader(query);
while(reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
DateTime date = Convert.ToDateTime(reader.GetString(2), Static.StaticData.Settings.CultureData.CultureInfo);
string sourceId = reader.GetString(3);
return new Task(id, name, date, sourceId);
}
return null;
}
}
public void TestUpdate()
{
DateTime now = DateTime.Now;
Task task = new Task("Darbas", now, "sourceId|15");
task.Save();
task.DateRemainder = now.AddYears(1);
task.Text = "Krabas";
task.Showed = true;
task.SourceId = "skriptas";
task.Update();
using (IConnection con = new ConnectionCollector().GetConnection())
{
IDataReader reader = con.ExecuteReader("SELECT NAME, DATE_REMAINDER, SOURCE_ID, SHOWED FROM TASK");
Assert.IsTrue(reader.Read());
Assert.AreEqual(task.Text, reader.GetString(0));
Assert.AreEqual(DBTypesConverter.ToFullDateStringByCultureInfo(task.DateRemainder), reader.GetString(1));
Assert.AreEqual(task.SourceId, reader.GetString(2));
Assert.AreEqual(1, reader.GetInt32(3));
}
}