Esempio n. 1
0
        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);
        }
Esempio n. 2
0
        //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)));
        }
Esempio n. 3
0
        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)));
        }
Esempio n. 4
0
        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));
        }
Esempio n. 5
0
        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));
        }
Esempio n. 6
0
        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));
        }
Esempio n. 7
0
        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));
        }
Esempio n. 8
0
        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));
        }
Esempio n. 9
0
        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));
        }
Esempio n. 10
0
        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));
        }
Esempio n. 11
0
        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);
        }
Esempio n. 12
0
        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));
        }
Esempio n. 13
0
        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));
        }
Esempio n. 14
0
        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);
        }
Esempio n. 15
0
        /// <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);
        }
Esempio n. 16
0
        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));
        }
Esempio n. 17
0
        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);
        }
Esempio n. 18
0
        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));
        }
Esempio n. 19
0
        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));
        }
Esempio n. 20
0
        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);
            }
        }
Esempio n. 21
0
        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);
        }
Esempio n. 22
0
        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);
            }
        }
Esempio n. 23
0
        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);
        }
Esempio n. 24
0
        //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);
        }
Esempio n. 25
0
        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);
        }
Esempio n. 26
0
        public virtual bool DeleteAll()
        {
            string statement = SqlStatememtGenerator.Delete_ByCondition(tableName, "1=1");

            return(executer.ExecuteDeleteStatement(statement, null));
        }