示例#1
0
        /// <summary>
        /// This method is used to get a record from the database by using Id of the record as search parameter.
        /// </summary>
        /// <param name="id">Id of the record.</param>
        /// <param name="table">Table on which operation has to be performed.</param>
        /// <returns>Returns the record from the database.</returns>
        public T GetRecord(int id, DatabaseTable table)
        {
            string tableName = DatabaseTableNameFetcher.GetName(table);
            string query     = string.Format("SELECT * FROM {0} where {1} = {2}", tableName, TableColumns.COMMON_ID, id);

            var result = ApplicationSettingData.Setting.DataCommunicator.Read(query);

            return(result.Rows.Cast <T>().FirstOrDefault());
        }
示例#2
0
        /// <summary>
        /// This method is used to fetch data from the database with joins.
        /// </summary>
        /// <param name="conditions">An object that contains logical operator and conditions.</param>
        /// <returns>Returns fetched data.</returns>
        public DataTable GetRecords(DatabaseTable mainTable, JoinConditions conditions)
        {
            string mainTableName = DatabaseTableNameFetcher.GetName(mainTable);
            string mainColumn    = TableColumns.GetBaseTablePrimaryColumn(mainTable);

            string query = string.Format("SELECT DISTINCT {0}.{1}", mainTableName, TableColumns.COMMON_ID);

            // If mainColumn value is not empty = It is not a relationship table.
            if (!mainColumn.Equals(string.Empty))
            {
                query = string.Format("{0}, {1}.{2}", query, mainTableName, mainColumn);
            }

            string from = string.Format("FROM {0}", mainTableName);

            string where = string.Format("WHERE {0}.{1} = 0", mainTableName, TableColumns.COMMON_DELETED);

            // If search conditions are provided.
            // All conditions are calculated with AND operator.
            if (conditions.Search != null)
            {
                foreach (var condition in conditions.Search)
                {
                    string baseTable = DatabaseTableNameFetcher.GetName(condition.BaseTable);
                    where = string.Format("{0} AND {1}.{2} = {3}", where, baseTable, condition.Column, condition.Value);
                }
            }

            // Create Join Conditions
            foreach (var condition in conditions.Conditions)
            {
                string baseTable  = DatabaseTableNameFetcher.GetName(condition.BaseTable);
                string joinTable  = DatabaseTableNameFetcher.GetName(condition.JoinTable);
                string baseColumn = TableColumns.GetBaseTablePrimaryColumn(condition.BaseTable);

                query = string.Format("{0}, {1}.{2}", query, baseTable, baseColumn);
                where = string.Format("{0} {5} {1}.{2} = {3}.{4}",
                                      where,
                                      baseTable,
                                      TableColumns.COMMON_ID,
                                      joinTable,
                                      condition.JoinTableColumnName,
                                      conditions.LogicalOperator);

                if (!from.Contains(baseTable))
                {
                    from = string.Format("{0}, {1}", from, baseTable);
                }
            }

            where = ApplicationSettingData.Setting.Helper.EndsWith(where, "WHERE");
            where = ApplicationSettingData.Setting.Helper.EndsWith(where, conditions.LogicalOperator);
            query = string.Format("{0} {1} {2}", query, from, where);

            return(ApplicationSettingData.Setting.DataCommunicator.Read(query));
        }
示例#3
0
        /// <summary>
        /// This method is used to mark a record as deleted in the database.
        /// </summary>
        /// <param name="Id">Id of the record to be marked as deleted.</param>
        /// <param name="table">Table on which operation has to be performed.</param>
        /// <returns>Returns true if operation completed successfully, otherwise false.</returns>
        public bool DeleteRecord(int Id, DatabaseTable table)
        {
            string tableName = DatabaseTableNameFetcher.GetName(table);
            string query     = string.Format("UPDATE {0} SET {1} = 1 WHERE {2} = {3}",
                                             tableName,
                                             TableColumns.COMMON_DELETED,
                                             TableColumns.COMMON_ID,
                                             Id);

            return(ApplicationSettingData.Setting.DataCommunicator.Commit(query));
        }
示例#4
0
        /// <summary>
        /// This method is used to edit or modify the records of the database.
        /// </summary>
        /// <param name="Id">Id of the record to be modfied.</param>
        /// <param name="data">A Dictionary that contains column names as keys and their values.</param>
        /// <param name="table">Table on which manipulation has to be done.</param>
        /// <returns>Returns true if operation completed successfully, otherwise false.</returns>
        public bool EditRecord(int Id, Dictionary <string, object> data, DatabaseTable table)
        {
            string tableName = DatabaseTableNameFetcher.GetName(table);
            string query     = string.Format("UPDATE {0} SET ", tableName);
            List <SqlParameter> parameters = new List <SqlParameter>();
            int count = 0;

            // Generate query.
            foreach (var key in data.Keys)
            {
                query += string.Format("{0} = @p{1},", key, count);
                parameters.Add(new SqlParameter("@p" + count, data[key]));
                ++count;
            }

            query = ApplicationSettingData.Setting.Helper.EndsWith(query, ",");
            query = string.Format("{0} WHERE {1} = {2}", query, TableColumns.COMMON_ID, Id);

            return(ApplicationSettingData.Setting.DataCommunicator.Commit(query, parameters.ToArray()));
        }
示例#5
0
        /// <summary>
        /// This method is used to get a record from the database by matching multiple search criterias.
        /// </summary>
        /// <param name="table">Table to which the record belongs.</param>
        /// <param name="condition">Different search conditions. Keys are the column names.</param>
        /// <returns>Returns the record from the database.</returns>
        public T GetRecord(DatabaseTable table, Dictionary <string, object> condition)
        {
            string tableName = DatabaseTableNameFetcher.GetName(table);
            string query     = string.Format("SELECT * FROM {0} WHERE", tableName);
            List <SqlParameter> parameters = new List <SqlParameter>();
            int count = 0;

            // Generate query.
            foreach (var key in condition.Keys)
            {
                query = string.Format("{0} {1} = @p{2} AND", query, key, count);
                parameters.Add(new SqlParameter("@p" + count, condition[key]));
                ++count;
            }

            query = ApplicationSettingData.Setting.Helper.EndsWith(query, "AND");
            query = ApplicationSettingData.Setting.Helper.EndsWith(query, "WHERE");

            var result = ApplicationSettingData.Setting.DataCommunicator.Read(query, parameters.ToArray());

            return(GetConvertedData(result));
        }
示例#6
0
        /// <summary>
        /// This method is used to save or insert a new record in the database.
        /// </summary>
        /// <param name="data">A Dictionary containing column names as keys and their values.</param>
        /// <param name="table">Table in which data has to be inserted.</param>
        /// <returns>Returns true if the operation completed successfully, otherwise false.</returns>
        public bool SaveRecord(Dictionary <string, object> data, DatabaseTable table)
        {
            string tableName = DatabaseTableNameFetcher.GetName(table);
            string query     = string.Format("INSERT INTO {0}(", tableName);
            string values    = "VALUES(";
            List <SqlParameter> parameters = new List <SqlParameter>();
            int count = 0;

            // Generate proper query.
            foreach (var key in data.Keys)
            {
                query  += string.Format("{0},", key);
                values += string.Format("@p{0},", count);
                parameters.Add(new SqlParameter("@p" + count, data[key]));
                ++count;
            }

            query   = ApplicationSettingData.Setting.Helper.EndsWith(query, ",");
            values  = ApplicationSettingData.Setting.Helper.EndsWith(values, ",");
            values += ");";
            query  += ") " + values;

            return(ApplicationSettingData.Setting.DataCommunicator.Commit(query, parameters.ToArray()));
        }
示例#7
0
        /// <summary>
        /// This method is used to get multiple records from the database.
        /// </summary>
        /// <param name="table">Table from which records are to be fetched.</param>
        /// <param name="condition">Search conditions. Keys are column names.</param>
        /// <returns>Returns a dataTable object with retrieved records.</returns>
        public DataTable GetRecords(DatabaseTable table, Dictionary <string, object> condition = null)
        {
            string tableName = DatabaseTableNameFetcher.GetName(table);
            string query     = string.Format("SELECT * FROM {0} WHERE {1} = 0 AND", tableName, TableColumns.COMMON_DELETED);

            if (condition != null)
            {
                int count = 0;
                List <SqlParameter> parameters = new List <SqlParameter>();

                foreach (var key in condition.Keys)
                {
                    query += string.Format(" {0} = @p{1} AND", key, count);
                    parameters.Add(new SqlParameter("@p" + count, condition[key]));
                    ++count;
                }

                query = ApplicationSettingData.Setting.Helper.EndsWith(query, "AND");
                return(ApplicationSettingData.Setting.DataCommunicator.Read(query, parameters.ToArray()));
            }

            query = ApplicationSettingData.Setting.Helper.EndsWith(query, "AND");
            return(ApplicationSettingData.Setting.DataCommunicator.Read(query));
        }