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 InitOnce()
{
ConfigLoader.Load(@"CoreConfig.xml");
// DbHelper.TestMode = true;
try
{
using (IConnection con = new ConnectionCollector().GetConnection())
{
GenericDbHelper.DropAllTables();
new DbHelper().CreateTable();
}
} catch (Exception ex) { ex.ToString(); }
}
public void InitOnce()
{
ConfigLoader.Load(@"CoreConfig.xml");
// DbHelper.TestMode = true;
try
{
using (IConnection con = new ConnectionCollector().GetConnection())
{
GenericDbHelper.DropAllTables();
new DbHelper().CreateTable();
}
} catch (Exception ex) { ex.ToString(); }
}
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 static IDisposable WhateverBind <T>(this ICell <T> cell, Action <T, ConnectionCollector> action, Priority p = Priority.Normal)
{
var collector = new ConnectionCollector();
var disps = new ListDisposable
{
collector,
cell.Bind(val =>
{
collector.DisconnectAll();
action(val, collector);
}, p)
};
return(disps);
}
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 void TestLoadTasksForShowing()
{
Task task1 = new Task("Darbas", DateTime.Now.AddSeconds(-1), "sourceId|sekundė-1");
task1.Save();
Task task2 = new Task("Darbas2", DateTime.Now.AddMonths(-1), "sourceId|mėnuo-1");
task2.Save();
Task task3 = new Task("Darbas3", DateTime.Now.AddMinutes(1), "sourceId|mintė+1");
task3.Save();
using (IConnection con = new ConnectionCollector().GetConnection())
{
List <Task> tasks = new DbHelper().LoadTasksForShowing();
Assert.AreEqual(2, tasks.Count);
}
}
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 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 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 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 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 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 DBVersionSystemRunner ExecuteUpdateToDB(DBVersionRepository repo)
{
m_log.Debug(string.Format("Preparing to inject {0} new versions into database",
repo.VersionsTable.Count));
foreach (IDBVersionInformation info in Sort(repo).Values)
{
string versionInfo = string.Format(
"[{0} - {1}]",
info.VersionAttribute.Version,
info.VersionAttribute.Date);
try
{
using (IConnection con = new ConnectionCollector().GetConnection())
{
// m_log.InfoFormat("Injecting version {0} into database", versionInfo);
info.VersionBody.Execute();
CreateVersionInDB(info);
con.Commit();
}
}
catch (Exception e)
{
m_log.Error(string.Format("Could not commit version {0}", versionInfo), e);
m_log.Error(string.Format("Rolling back"));
throw new DBUpdateException(string.Format("Error injecting DB version {1}, date '{0}'.",
info.VersionAttribute.Date,
info.VersionAttribute.Version)
, e);
}
}
m_log.Debug(string.Format("Success - Injected {0} new versions into database",
repo.VersionsTable.Count));
return this;
}
public DBVersionSystemRunner ExecuteUpdateToDB(DBVersionRepository repo)
{
m_log.Debug(string.Format("Preparing to inject {0} new versions into database",
repo.VersionsTable.Count));
foreach (IDBVersionInformation info in Sort(repo).Values)
{
string versionInfo = string.Format(
"[{0} - {1}]",
info.VersionAttribute.Version,
info.VersionAttribute.Date);
try
{
using (IConnection con = new ConnectionCollector().GetConnection())
{
// m_log.InfoFormat("Injecting version {0} into database", versionInfo);
info.VersionBody.Execute();
CreateVersionInDB(info);
con.Commit();
}
}
catch (Exception e)
{
m_log.Error(string.Format("Could not commit version {0}", versionInfo), e);
m_log.Error(string.Format("Rolling back"));
throw new DBUpdateException(string.Format("Error injecting DB version {1}, date '{0}'.",
info.VersionAttribute.Date,
info.VersionAttribute.Version)
, e);
}
}
m_log.Debug(string.Format("Success - Injected {0} new versions into database",
repo.VersionsTable.Count));
return(this);
}
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 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));
}
}
public void TestLoadTasksForShowing()
{
Task task1 = new Task("Darbas", DateTime.Now.AddSeconds(-1), "sourceId|sekundė-1");
task1.Save();
Task task2 = new Task("Darbas2", DateTime.Now.AddMonths(-1), "sourceId|mėnuo-1");
task2.Save();
Task task3 = new Task("Darbas3", DateTime.Now.AddMinutes(1), "sourceId|mintė+1");
task3.Save();
using (IConnection con = new ConnectionCollector().GetConnection())
{
List<Task> tasks = new DbHelper().LoadTasksForShowing();
Assert.AreEqual(2, tasks.Count);
}
}
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;
}
}
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;
}
}
