protected List<T> IdenticalMatch(SqlCeParameter[] 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); SqlCeDataReader reader = executer.ExecuteSelectStatement(statement, parameters); if (reader == null) return new List<T>(); return ReadAllRecords(reader); }
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); SqlCeParameter[] parameters = { MakeSqlParameter(messageDate, toDeleteDate, SqlDbType.DateTime), MakeSqlParameter(messageFrom, from.Id), MakeSqlParameter(messageTo, to.Id) }; return(executer.ExecuteDeleteStatement(statement, parameters)); }
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)); }
private List <DocumentVO> ExactFindByStringColumn(string title, string columnName) { string condition = string.Format("{0} like '{1}'", columnName, title); string statement = SqlStatememtGenerator.Select_ReadAllColumnsByCondition(tableName, condition); SqlCeDataReader 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); SqlCeDataReader reader = executer.ExecuteSelectStatement(statement, null); return(ReadAllRecords(reader)); }
public List <LogEntryVO> Find(Common.Search.DateRange range, OperationVO operation, UserVO user) { SqlCeParameter[] parameters = GetParameters(range); string condition = GetCondition(range, operation, user); string statement = SqlStatememtGenerator.Select_ReadAllColumnsByCondition(tableName, condition); SqlCeDataReader 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); SqlCeParameter[] parameters = new SqlCeParameter[] { MakeSqlParameter(providedTableColumnName, tableElement.Id) }; return(executer.ExecuteDeleteStatement(statement, parameters)); }
public virtual bool DeleteItem(int id) { string statement = SqlStatememtGenerator.Delete_ById(tableName, idColumnName); SqlCeParameter[] parameters = new SqlCeParameter[1]; parameters[0] = MakeSqlParameter(idColumnName, id); bool result = executer.ExecuteDeleteStatement(statement, parameters); return result; }
public virtual List<T> GetAll() { string statement = SqlStatememtGenerator.Select_ReadAllColumnsByCondition(tableName, "1=1"); SqlCeDataReader reader = executer.ExecuteSelectStatement(statement, null); if (reader == null) return new List<T>(); return ReadAllRecords(reader); }
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); SqlCeParameter[] parameters = new SqlCeParameter[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); SqlCeDataReader reader = executer.ExecuteSelectStatement(statement, null); List <DocumentVO> result = ReadAllRecords(reader); FullLoadList(result); return(result); }
public int GetAssociationsCountOfSecondTable(T2 secondTableVO) { string condition = string.Format("{0}=@{0}", secondTableIdColumnName); string statement = SqlStatememtGenerator.Select_ReadSomeColumnsByCondition(tableName, new string[] { "count(*)" }, condition); SqlCeParameter[] parameters = new SqlCeParameter[1]; parameters[0] = MakeSqlParameter(secondTableIdColumnName, secondTableVO.Id); int count = Convert.ToInt32(executer.ExecuteScalarSelectStatement(statement, parameters).ToString()); return(count); }
public virtual bool Update(T vo) { string[] columnsNames = ConvertColumnNamesToArray(); string statement = SqlStatememtGenerator.Update_ById(tableName, columnsNames, idColumnName); SqlCeParameter[] parameters = new SqlCeParameter[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); SqlCeDataReader 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 statement = SqlStatememtGenerator.Update_ByCondition(tableName, new string[] { messageIsSeen }, condition); SqlCeParameter[] parameters = { MakeSqlParameter(messageFrom, fromUser.Id), MakeSqlParameter(messageTo, toUser.Id), MakeSqlParameter(messageIsSeen, true, SqlDbType.Bit) }; return(executer.ExecuteUpdateStatement(statement, parameters)); }
/// <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); SqlCeParameter[] parameters = MakeSqlParameters(vo); if (!executer.ExecuteInsertStatement(statement, parameters)) return -1; int id = GetMaxId(); vo.Id = id; return id; }
public int Attach(T1 firstTableVO, T2 secondTableVO) { string statement = SqlStatememtGenerator.Insert(tableName, ConvertToArray(firstTableIdColumnName, secondTableIdColumnName)); SqlCeParameter[] parameters = new SqlCeParameter[2]; parameters[0] = MakeSqlParameter(firstTableIdColumnName, firstTableVO.Id); parameters[1] = MakeSqlParameter(secondTableIdColumnName, secondTableVO.Id); if (!executer.ExecuteInsertStatement(statement, parameters)) { return(-1); } return(GetMaxId()); }
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); SqlCeParameter[] paramters = new SqlCeParameter[] { MakeSqlParameter(rStart, range.Start, SqlDbType.DateTime), MakeSqlParameter(rEnd, range.End, SqlDbType.DateTime) }; SqlCeDataReader reader = executer.ExecuteSelectStatement(statement, paramters); List <FileVO> result = ReadAllRecords(reader); return(result); }
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); SqlCeParameter[] paramters = new SqlCeParameter[] { MakeSqlParameter(rStart, range.Start, SqlDbType.DateTime), MakeSqlParameter(rEnd, range.End, SqlDbType.DateTime) }; SqlCeDataReader reader = executer.ExecuteSelectStatement(statement, paramters); List <DocumentVO> result = ReadAllRecords(reader); FullLoadList(result); return(result); }
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); SqlCeParameter[] parameters = new SqlCeParameter[] { MakeSqlParameter(providedTableColumnName, tableElement.Id) }; SqlCeDataReader reader = executer.ExecuteSelectStatement(statement, parameters); try { while (reader.Read()) { result.Add(reader.GetInt32(reader.GetOrdinal(requiredTableColumnName))); } } catch { } reader.Close(); return(result); }
public virtual T GetItem(int id) { string statement = SqlStatememtGenerator.Select_ReadAllColumnsById(tableName, idColumnName); SqlCeParameter[] parameters = new SqlCeParameter[1]; parameters[0] = MakeSqlParameter(idColumnName, id); SqlCeDataReader reader = executer.ExecuteSelectStatement(statement, parameters); if (reader == null) return null; T result = null; try { reader.Read(); result = ReadRecord(reader); } catch { } reader.Close(); return result; }
public virtual bool DeleteAll() { string statement = SqlStatememtGenerator.Delete_ByCondition(tableName, "1=1"); return executer.ExecuteDeleteStatement(statement, null); }