public virtual T GetItem(int id)
{
string statement = SqlStatememtGenerator.Select_ReadAllColumnsById(tableName, idColumnName);
SqlParameter[] parameters = new SqlParameter[1];
parameters[0] = MakeSqlParameter(idColumnName, id);
SqlDataReader reader = executer.ExecuteSelectStatement(statement, parameters);
if (reader == null)
{
return(null);
}
T result = null;
if (reader.HasRows)
{
reader.Read();
result = ReadRecord(reader);
}
reader.Close();
return(result);
}
//Does not load the fileData column
public override List <FileVO> GetAll()
{
string[] columns = { idColumnName, fileName, fileDocumentId, fileType, fileAdditionDate };
string statement = SqlStatememtGenerator.Select_ReadSomeColumnsByCondition(tableName, columns, "1 = 1");
return(ReadAllRecords(executer.ExecuteSelectStatement(statement, null)));
}
public virtual int GetRecordsCount()
{
string condition = " 1 = 1 ";
string statement = SqlStatememtGenerator.Select_ReadSomeColumnsByCondition(tableName, new string[] { "count(*)" }, condition);
return(Convert.ToInt32(executer.ExecuteScalarSelectStatement(statement, null)));
}
public bool DeleteOldMessages(UserVO from, UserVO to)
{
List <MessageVO> m = GetMessages(from, to);
if (m.Count <= ConfigurationManager.MAXIMUM_MESSAGES_COUNT)
{
return(true);
}
m.Sort(new MessageSorter());
int firstOldMessageIndex = m.Count - 1 - ConfigurationManager.MAXIMUM_MESSAGES_COUNT;
MessageVO firstOldMessage = m[firstOldMessageIndex];
DateTime toDeleteDate = firstOldMessage.Date;
string condition = string.Format("({0} <= @{0} and {1}=@{1} and {2}=@{2})", messageDate, messageFrom, messageTo);
string statement = SqlStatememtGenerator.Delete_ByCondition(tableName, condition);
SqlParameter[] parameters =
{
MakeSqlParameter(messageDate, toDeleteDate, SqlDbType.DateTime),
MakeSqlParameter(messageFrom, from.Id),
MakeSqlParameter(messageTo, to.Id)
};
return(executer.ExecuteDeleteStatement(statement, parameters));
}
protected List <T> IdenticalMatch(SqlParameter[] parameters)
{
StringBuilder builder = new StringBuilder();
if (parameters.Length > 0)
{
for (int i = 0; i < parameters.Length; i++)
{
builder.AppendFormat("{0} = @{0}", parameters[i].ParameterName.Substring(1));
if (i < parameters.Length - 1)
{
builder.Append(" AND ");
}
}
}
else
{
builder.AppendFormat("1 = 1");
}
string condition = builder.ToString();
string statement = SqlStatememtGenerator.Select_ReadAllColumnsByCondition(tableName, condition);
SqlDataReader reader = executer.ExecuteSelectStatement(statement, parameters);
if (reader == null)
{
return(new List <T>());
}
return(ReadAllRecords(reader));
}
public List <FileVO> GetFilesOfDocument(DocumentVO document)
{
string condition = string.Format("{0}={1}", fileDocumentId, document.Id);
string statement = SqlStatememtGenerator.Select_ReadAllColumnsByCondition(tableName, condition);
SqlDataReader reader = executer.ExecuteSelectStatement(statement, null);
return(ReadAllRecords(reader));
}
private List <DocumentVO> ExactFindByStringColumn(string title, string columnName)
{
string condition = string.Format("{0} like '{1}'", columnName, title);
string statement = SqlStatememtGenerator.Select_ReadAllColumnsByCondition(tableName, condition);
SqlDataReader reader = executer.ExecuteSelectStatement(statement, null);
return(ReadAllRecords(reader));
}
public List <DocumentVO> GetDocumentsOfFolder(FolderVO folder)
{
string condition = string.Format("{0}={1}", documentFolderId, folder.Id);
string statement = SqlStatememtGenerator.Select_ReadAllColumnsByCondition(tableName, condition);
SqlDataReader reader = executer.ExecuteSelectStatement(statement, null);
return(ReadAllRecords(reader));
}
public List <LogEntryVO> Find(Common.Search.DateRange range, OperationVO operation, UserVO user)
{
SqlParameter[] parameters = GetParameters(range);
string condition = GetCondition(range, operation, user);
string statement = SqlStatememtGenerator.Select_ReadAllColumnsByCondition(tableName, condition);
SqlDataReader reader = executer.ExecuteSelectStatement(statement, parameters);
return(ReadAllRecords(reader));
}
private bool DeleteAssociationsOfTableElement(VO tableElement, string providedTableColumnName)
{
string condition = string.Format("{0} = @{0}", providedTableColumnName);
string statement = SqlStatememtGenerator.Delete_ByCondition(tableName, condition);
SqlParameter[] parameters = new SqlParameter[] { MakeSqlParameter(providedTableColumnName, tableElement.Id) };
return(executer.ExecuteDeleteStatement(statement, parameters));
}
public virtual bool DeleteItem(int id)
{
string statement = SqlStatememtGenerator.Delete_ById(tableName, idColumnName);
SqlParameter[] parameters = new SqlParameter[1];
parameters[0] = MakeSqlParameter(idColumnName, id);
bool result = executer.ExecuteDeleteStatement(statement, parameters);
return(result);
}
public int Attach(T1 firstTableVO, T2 secondTableVO)
{
string statement = SqlStatememtGenerator.Insert(tableName, ConvertToArray(firstTableIdColumnName, secondTableIdColumnName));
SqlParameter[] parameters = new SqlParameter[2];
parameters[0] = MakeSqlParameter(firstTableIdColumnName, firstTableVO.Id);
parameters[1] = MakeSqlParameter(secondTableIdColumnName, secondTableVO.Id);
return(executer.ExecuteInsertStatement(statement, parameters));
}
public bool Detach(T1 firstTableVO, T2 secondTableVO)
{
string condition = string.Format("{0} = @{0} and {1} = @{1}", firstTableIdColumnName, secondTableIdColumnName);
string statement = SqlStatememtGenerator.Delete_ByCondition(tableName, condition);
SqlParameter[] parameters = new SqlParameter[2];
parameters[0] = MakeSqlParameter(firstTableIdColumnName, firstTableVO.Id);
parameters[1] = MakeSqlParameter(secondTableIdColumnName, secondTableVO.Id);
return(executer.ExecuteDeleteStatement(statement, parameters));
}
private List <DocumentVO> FindByIntegerColumn(int value, string columnName)
{
string condition = string.Format("{0} = {1}", columnName, value);
string statement = SqlStatememtGenerator.Select_ReadAllColumnsByCondition(tableName, condition);
SqlDataReader reader = executer.ExecuteSelectStatement(statement, null);
List <DocumentVO> result = ReadAllRecords(reader);
FullLoadList(result);
return(result);
}
/// <summary>
/// Inserts the object in databse and sets its Id property to the inserted record id.
/// </summary>
/// <param name="vo">The VO object to be inserted.</param>
/// <returns>The inserted record id. -1 if the insertion was not successful.</returns>
public virtual int Insert(T vo)
{
string[] columnsNames = ConvertColumnNamesToArray();
string statement = SqlStatememtGenerator.Insert(tableName, columnsNames);
SqlParameter[] parameters = MakeSqlParameters(vo);
int id = executer.ExecuteInsertStatement(statement, parameters);
vo.Id = id;
return(id);
}
public virtual List <T> GetAll()
{
string statement = SqlStatememtGenerator.Select_ReadAllColumnsByCondition(tableName, "1=1");
SqlDataReader reader = executer.ExecuteSelectStatement(statement, null);
if (reader == null)
{
return(new List <T>());
}
return(ReadAllRecords(reader));
}
public virtual bool Update(T vo)
{
string[] columnsNames = ConvertColumnNamesToArray();
string statement = SqlStatememtGenerator.Update_ById(tableName, columnsNames, idColumnName);
SqlParameter[] parameters = new SqlParameter[columnsCount];
Array.Copy(MakeSqlParameters(vo), parameters, columnsCount - 1);
parameters[columnsCount - 1] = MakeSqlParameter(idColumnName, vo.Id);
bool result = executer.ExecuteUpdateStatement(statement, parameters);
return(result);
}
private List <DocumentVO> GetItemsInRange(List <int> ids)
{
if (ids == null || ids.Count == 0)
{
return(new List <DocumentVO>());
}
string cond = string.Format("{0} in {1}", idColumnName, MakeCommaSeparatedValues(ids));
string statement = SqlStatememtGenerator.Select_ReadAllColumnsByCondition(tableName, cond);
SqlDataReader reader = executer.ExecuteSelectStatement(statement, null);
return(ReadAllRecords(reader));
}
public bool SetMessagesSeen(UserVO fromUser, UserVO toUser)
{
string condition = string.Format("({0} = @{0} and {1} = @{1})", messageFrom, messageTo);
//string condition = string.Format("(1 = 1)", messageFrom, messageTo);
string statement = SqlStatememtGenerator.Update_ByCondition(tableName, new string[] { messageIsSeen }, condition);
SqlParameter[] parameters =
{
MakeSqlParameter(messageFrom, fromUser.Id),
MakeSqlParameter(messageTo, toUser.Id),
MakeSqlParameter(messageIsSeen, true, SqlDbType.Bit)
};
return(executer.ExecuteUpdateStatement(statement, parameters));
}
public int GetMaxId()
{
string[] temp = new string[] {
string.Format("max({0})", idColumnName)
};
string statement = SqlStatememtGenerator.Select_ReadSomeColumnsByCondition(tableName, temp, "1=1");
try
{
int result = Convert.ToInt32(executer.ExecuteScalarSelectStatement(statement, null));
return(result);
}
catch
{
return(-1);
}
}
public List <FileVO> FindFilesByArchivingDate(DateRange range)
{
string rStart = "rangeStart";
string rEnd = "rEnd";
string condition = string.Format("{0} >= @{1} and {0} < @{2}", fileAdditionDate, rStart, rEnd);
string statement = SqlStatememtGenerator.Select_ReadAllColumnsByCondition(tableName, condition);
SqlParameter[] paramters = new SqlParameter[]
{
MakeSqlParameter(rStart, range.Start, SqlDbType.DateTime),
MakeSqlParameter(rEnd, range.End, SqlDbType.DateTime)
};
SqlDataReader reader = executer.ExecuteSelectStatement(statement, paramters);
List <FileVO> result = ReadAllRecords(reader);
return(result);
}
public int GetAssociationsCountOfSecondTable(T2 secondTableVO)
{
try
{
string condition = string.Format("{0}=@{0}", secondTableIdColumnName);
string statement = SqlStatememtGenerator.Select_ReadSomeColumnsByCondition(tableName, new string[] { "count(*)" }, condition);
SqlParameter[] parameters = new SqlParameter[1];
parameters[0] = MakeSqlParameter(secondTableIdColumnName, secondTableVO.Id);
int count = Convert.ToInt32(executer.ExecuteScalarSelectStatement(statement, parameters).ToString());
return(count);
}
catch
{
return(-1);
}
}
private List <int> GetAssociationsOfTable(VO tableElement, string providedTableColumnName, string requiredTableColumnName)
{
List <int> result = new List <int>();
string condition = string.Format("{0} = @{0}", providedTableColumnName);
string statement = SqlStatememtGenerator.Select_ReadAllColumnsByCondition(tableName, condition);
SqlParameter[] parameters = new SqlParameter[] { MakeSqlParameter(providedTableColumnName, tableElement.Id) };
SqlDataReader reader = executer.ExecuteSelectStatement(statement, parameters);
if (reader.HasRows)
{
while (reader.Read())
{
result.Add(reader.GetInt32(reader.GetOrdinal(requiredTableColumnName)));
}
}
reader.Close();
return(result);
}
//Does not load the fileData column
public override FileVO GetItem(int id)
{
string[] columns = { idColumnName, fileName, fileDocumentId, fileType, fileAdditionDate };
string statement = SqlStatememtGenerator.Select_ReadSomeColumnsById(tableName, columns, idColumnName);
SqlParameter[] parameters = { MakeSqlParameter(idColumnName, id) };
SqlDataReader reader = executer.ExecuteSelectStatement(statement, parameters);
FileVO result = null;
if (reader.HasRows)
{
reader.Read();
result = ReadRecord(reader);
return(result);
}
reader.Close();
return(null);
}
private List <DocumentVO> FindByDateRangeColumn(DateRange range, string columName)
{
string rStart = "rangeStart";
string rEnd = "rEnd";
string condition = string.Format("{0} >= @{1} and {0} < @{2}", columName, rStart, rEnd);
string statement = SqlStatememtGenerator.Select_ReadAllColumnsByCondition(tableName, condition);
SqlParameter[] paramters = new SqlParameter[]
{
MakeSqlParameter(rStart, range.Start, SqlDbType.DateTime),
MakeSqlParameter(rEnd, range.End, SqlDbType.DateTime)
};
SqlDataReader reader = executer.ExecuteSelectStatement(statement, paramters);
List <DocumentVO> result = ReadAllRecords(reader);
FullLoadList(result);
return(result);
}
public virtual bool DeleteAll()
{
string statement = SqlStatememtGenerator.Delete_ByCondition(tableName, "1=1");
return(executer.ExecuteDeleteStatement(statement, null));
}