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)); }