예제 #1
0
        /// <summary>
        /// Get the item by id asynchronously
        /// </summary>
        /// <param name="id">Primary Key</param>
        /// <param name="customTransaction"> has a transaction object</param>
        /// <returns>Item</returns>
        public async Task <T> GetByIdAsync(object id, CustomTransaction customTransaction)
        {
            try
            {
                var isCustomTransaction = customTransaction != null;

                var primaryKey = "";
                primaryKey = GetPrimaryKey(typeof(T));

                if (string.IsNullOrEmpty(primaryKey))
                {
                    throw new CustomRepositoryException("PrimaryKeyAttribute not defined");
                }

                var connection = isCustomTransaction ? customTransaction.DbCommand.Connection : _connection.DataBaseConnection;

                if (isCustomTransaction)
                {
                    return(await connection.QueryFirstOrDefaultAsync <T>($"Select * from {typeof(T).Name} where {primaryKey} = @ID ", new { ID = id }, customTransaction.DbCommand.Transaction));
                }
                else
                {
                    return(await connection.QueryFirstOrDefaultAsync <T>($"Select * from {typeof(T).Name} where {primaryKey} = @ID ", new { ID = id }));
                }
            }
            catch (Exception ex)
            {
                throw new CustomRepositoryException(ex.Message);
            }
        }
예제 #2
0
        /// <summary>
        /// Get all rows in the table asynchronously
        /// </summary>
        /// <param name="customTransaction"> has a transaction object</param>
        /// <returns>All rows in the table</returns>
        public async Task <IEnumerable <T> > GetAsync(CustomTransaction customTransaction)
        {
            try
            {
                var isCustomTransaction = customTransaction != null;

                var connection = isCustomTransaction ? customTransaction.DbCommand.Connection : _connection.DataBaseConnection;

                if (isCustomTransaction)
                {
                    return(await connection.QueryAsync <T>($"Select * from {typeof(T).Name} ", customTransaction.DbCommand.Transaction));
                }
                else
                {
                    return(await connection.QueryAsync <T>($"Select * from {typeof(T).Name} "));
                }
            }
            catch (Exception ex)
            {
                throw new CustomRepositoryException(ex.Message);
            }
        }
예제 #3
0
        /// <summary>
        /// Delete an item by id asynchronously
        /// </summary>
        /// <param name="id">Primary Key</param>
        /// <param name="customTransaction"> has a transaction object</param>
        public async Task DeleteAsync(object id, CustomTransaction customTransaction)
        {
            try
            {
                var isCustomTransaction = customTransaction != null;

                var primaryKey = "";
                primaryKey = GetPrimaryKey(typeof(T));

                if (string.IsNullOrEmpty(primaryKey))
                {
                    throw new CustomRepositoryException("PrimaryKeyAttribute not defined");
                }

                var connection = isCustomTransaction ? customTransaction.DbCommand.Connection : _connection.DataBaseConnection;
                var sql        = new StringBuilder();

                var parameters = new Dictionary <string, object>
                {
                    { "@ID", id }
                };

                sql.AppendLine($"delete from {typeof(T).Name} where {primaryKey} = @ID");

                if (isCustomTransaction)
                {
                    await connection.ExecuteAsync(sql.ToString(), parameters, customTransaction.DbCommand.Transaction);
                }
                else
                {
                    await connection.ExecuteAsync(sql.ToString(), parameters);
                }
            }
            catch (Exception ex)
            {
                throw new CustomRepositoryException(ex.Message);
            }
        }
예제 #4
0
        /// <summary>
        /// Executes a Procedure with parameters asynchronously
        /// </summary>
        /// <param name="procedure">Query</param>
        /// <param name="parameters">Query parameters</param>
        /// <param name="customTransaction"> has a transaction object</param>
        /// <returns>Procedure return</returns>
        public async Task <int> ExecuteProcedureAsync(string procedure, Dictionary <string, object> parameters, CustomTransaction customTransaction)
        {
            var isCustomTransaction = customTransaction != null;

            try
            {
                if (isCustomTransaction)
                {
                    DbCommand = customTransaction.DbCommand;
                }
                else
                {
                    DbCommand.Connection = DBConnection;
                }

                DbCommand.CommandType = CommandType.StoredProcedure;
                DbCommand.CommandText = procedure;
                DbCommand.Parameters.Clear();

                if (DBConnection.State == ConnectionState.Closed)
                {
                    DBConnection.Open();
                }

                foreach (var parameter in parameters)
                {
                    DbCommand.Parameters.Add(_connection.GetParameter(parameter));
                }

                return(await DbCommand.ExecuteNonQueryAsync());
            }
            catch (Exception exp)
            {
                throw new Exception(exp.Message);
            }
            finally
            {
                DisposeDB(isCustomTransaction);
            }
        }
예제 #5
0
        /// <summary>
        /// Executes a insert with parameters asynchronously
        /// </summary>
        /// <param name="sql">Query</param>
        /// <param name="parameters">Query parameters</param>
        /// <param name="identity">Primary Key or Oracle sequence</param>
        /// <param name="customTransaction"> has a transaction object</param>
        /// <returns>Primary Key After Insert</returns>
        public async Task <string> ExecuteQueryAsync(string sql, Dictionary <string, object> parameters, string identity, CustomTransaction customTransaction)
        {
            var isCustomTransaction = customTransaction != null;

            try
            {
                StringBuilder sbSql = new StringBuilder();

                if (_connection.Database == DataBaseType.SqlServer)
                {
                    sbSql.AppendLine(sql);
                    sbSql.AppendLine("SELECT CAST(SCOPE_IDENTITY() as int);");
                }
                else
                {
                    sbSql.AppendLine($"BEGIN {sql} SELECT {identity}.currval FROM DUAL END; ");
                }

                if (isCustomTransaction)
                {
                    DbCommand = customTransaction.DbCommand;
                }
                else
                {
                    DbCommand.Connection = DBConnection;
                }

                DbCommand.CommandType    = CommandType.Text;
                DbCommand.CommandTimeout = 120;
                DbCommand.Parameters.Clear();
                foreach (var parameter in parameters)
                {
                    DbCommand.Parameters.Add(_connection.GetParameter(parameter));
                }

                DbCommand.CommandText = sbSql.ToString();

                if (DBConnection.State == ConnectionState.Closed)
                {
                    DBConnection.Open();
                }

                var identityReturn = await DbCommand.ExecuteScalarAsync();

                return(identityReturn.ToString());
            }
            catch (Exception exp)
            {
                throw new Exception(exp.Message);
            }
            finally
            {
                DisposeDB(isCustomTransaction);
            }
        }
예제 #6
0
 /// <summary>
 /// Executes a query with parameters
 /// </summary>
 /// <param name="sql">Query</param>
 /// <param name="parameters">Query parameters</param>
 /// <param name="customTransaction"> has a transaction object</param>
 /// <returns>Number of rows affected</returns>
 public int ExecuteQuery(string sql, Dictionary <string, object> parameters, CustomTransaction customTransaction)
 => ExecuteQueryAsync(sql, parameters, customTransaction).Result;
예제 #7
0
 /// <summary>
 /// Delete an item by id
 /// </summary>
 /// <param name="id">Primary Key</param>
 /// <param name="customTransaction"> has a transaction object</param>
 public void Delete(object id, CustomTransaction customTransaction)
 => DeleteAsync(id, customTransaction).Wait();
예제 #8
0
 /// <summary>
 /// Update an item
 /// </summary>
 /// <param name="item"> item to update</param>
 /// <param name="customTransaction"> has a transaction object</param>
 /// <returns></returns>
 public void Update(T item, CustomTransaction customTransaction)
 => UpdateAsync(item, customTransaction).Wait();
예제 #9
0
 /// <summary>
 /// Executes Scalar with parameters
 /// </summary>
 /// <param name="sql">Query</param>
 /// <param name="parameters">Query parameters</param>
 /// <param name="customTransaction"> has a transaction object</param>
 /// <returns>Scalar result</returns>
 public object ExecuteScalar(string sql, Dictionary <string, object> parameters, CustomTransaction customTransaction)
 => ExecuteScalarAsync(sql, parameters, customTransaction).Result;
예제 #10
0
 /// <summary>
 /// Get the result of a query with parameters
 /// </summary>
 /// <param name="sql">Query</param>
 /// <param name="parameters">Query parameters</param>
 /// <param name="customTransaction"> has a transaction object</param>
 /// <returns>List of results</returns>
 public IEnumerable <T> Get(string sql, Dictionary <string, object> parameters, CustomTransaction customTransaction)
 => GetAsync(sql, parameters, customTransaction).Result;
예제 #11
0
        /// <summary>
        /// Get the result of a query with parameters asynchronously
        /// </summary>
        /// <param name="sql">Query</param>
        /// <param name="parameters">Query parameters</param>
        /// <param name="customTransaction"> has a transaction object</param>
        /// <returns>List of results</returns>
        public async Task <IEnumerable <T> > GetAsync(string sql, Dictionary <string, object> parameters, CustomTransaction customTransaction)
        {
            try
            {
                var isCustomTransaction = customTransaction != null;

                var connection = isCustomTransaction ? customTransaction.DbCommand.Connection : _connection.DataBaseConnection;

                if (isCustomTransaction)
                {
                    return(await connection.QueryAsync <T>(sql, parameters, customTransaction.DbCommand.Transaction));
                }
                else
                {
                    return(await connection.QueryAsync <T>(sql, parameters));
                }
            }
            catch (Exception ex)
            {
                throw new CustomRepositoryException(ex.Message);
            }
        }
예제 #12
0
 /// <summary>
 /// Get all rows in the table
 /// </summary>
 /// <param name="customTransaction"> has a transaction object</param>
 /// <returns>All rows in the table</returns>
 public IEnumerable <T> Get(CustomTransaction customTransaction)
 => GetAsync(customTransaction).Result;
예제 #13
0
 /// <summary>
 /// Insert an item
 /// </summary>
 /// <param name="item"> item to insert</param>
 /// <param name="identity">  Return primary key</param>
 /// <param name="customTransaction"> has a transaction object</param>
 /// <returns>Table Primary key or number of rows affected</returns>
 public object Insert(T item, bool identity, CustomTransaction customTransaction) =>
 InsertAsync(item, identity, customTransaction).Result;
예제 #14
0
        /// <summary>
        /// Insert an item asynchronously
        /// </summary>
        /// <param name="item"> item to insert</param>
        /// <param name="identity">  Return primary key</param>
        /// <param name="customTransaction"> has a transaction object</param>
        /// <returns>Table Primary key or number of rows affected</returns>
        public async Task <object> InsertAsync(T item, bool identity, CustomTransaction customTransaction)
        {
            if (_connection.Database == DataBaseType.Oracle)
            {
                throw new NotImplementedDatabaseException();
            }

            var isCustomTransaction = customTransaction != null;

            try
            {
                var connection = isCustomTransaction ? customTransaction.DbCommand.Connection : _connection.DataBaseConnection;

                var sql           = new StringBuilder();
                var sqlParameters = new StringBuilder();

                var parameters = new Dictionary <string, object>();

                foreach (var p in item.GetType().GetProperties())
                {
                    if (item.GetType().GetProperty(p.Name) == null)
                    {
                        continue;
                    }

                    if (IgnoreAttribute(p.CustomAttributes))
                    {
                        continue;
                    }

                    sqlParameters.Append($"@{p.Name},");
                    parameters.Add($"@{p.Name}", item.GetType().GetProperty(p.Name)?.GetValue(item));
                }

                sqlParameters.Remove(sqlParameters.Length - 1, 1);

                sql.AppendLine($"insert into {typeof(T).Name} ({sqlParameters.ToString().Replace("@", "")}) ");

                if (identity)
                {
                    var primaryKey = "";
                    primaryKey = GetPrimaryKey(typeof(T));

                    sql.AppendLine($" OUTPUT inserted.{primaryKey} values ({sqlParameters.ToString()}) ");

                    if (isCustomTransaction)
                    {
                        return(connection.QuerySingleOrDefault <dynamic>(sql.ToString(), parameters, customTransaction.DbCommand.Transaction).Id);
                    }
                    else
                    {
                        return(connection.QuerySingleOrDefault <dynamic>(sql.ToString(), parameters).Id);
                    }
                }
                else
                {
                    sql.AppendLine($" values ({sqlParameters.ToString()}) ");

                    if (isCustomTransaction)
                    {
                        return(await connection.ExecuteAsync(sql.ToString(), parameters, customTransaction.DbCommand.Transaction));
                    }
                    else
                    {
                        return(await connection.ExecuteAsync(sql.ToString(), parameters));
                    }
                }
            }
            catch (Exception ex)
            {
                throw new CustomRepositoryException(ex.Message);
            }
        }
예제 #15
0
 /// <summary>
 /// Executes a Procedure with parameters
 /// </summary>
 /// <param name="procedure">Query</param>
 /// <param name="parameters">Query parameters</param>
 /// <param name="customTransaction"> has a transaction object</param>
 /// <returns>Procedure return</returns>
 public int ExecuteProcedure(string procedure, Dictionary <string, object> parameters, CustomTransaction customTransaction)
 => ExecuteProcedureAsync(procedure, parameters, customTransaction).Result;
예제 #16
0
        /// <summary>
        /// Get Procedure DataSet with parameters
        /// </summary>
        /// <param name="procedure">Query</param>
        /// <param name="parameters">Query parameters</param>
        /// <param name="customTransaction"> has a transaction object</param>
        /// <returns>DataSet</returns>
        public DataSet GetProcedureDataSet(string procedure, Dictionary <string, object> parameters, CustomTransaction customTransaction)
        {
            var isCustomTransaction = customTransaction != null;

            try
            {
                if (isCustomTransaction)
                {
                    DbCommand = customTransaction.DbCommand;
                }
                else
                {
                    DbCommand.Connection = DBConnection;
                }

                DbCommand.CommandType = CommandType.StoredProcedure;
                DbCommand.CommandText = procedure;
                DbCommand.Parameters.Clear();

                if (DBConnection.State == ConnectionState.Closed)
                {
                    DBConnection.Open();
                }

                foreach (var parameter in parameters)
                {
                    DbCommand.Parameters.Add(_connection.GetParameter(parameter));
                }

                using (var da = _connection.GetDataAdapter())
                {
                    da.SelectCommand = DbCommand;

                    using (var ds = new DataSet())
                    {
                        da.Fill(ds);
                        return(ds);
                    }
                }
            }
            catch (Exception exp)
            {
                throw new Exception(exp.Message);
            }
            finally
            {
                DisposeDB(isCustomTransaction);
            }
        }
예제 #17
0
 /// <summary>
 /// Get the item by id
 /// </summary>
 /// <param name="id">Primary Key</param>
 /// <param name="customTransaction"> has a transaction object</param>
 /// <returns>Item</returns>
 public T GetById(object id, CustomTransaction customTransaction)
 => GetByIdAsync(id, customTransaction).Result;
예제 #18
0
        /// <summary>
        /// Executes Scalar with parameters asynchronously
        /// </summary>
        /// <param name="sql">Query</param>
        /// <param name="parameters">Query parameters</param>
        /// <param name="customTransaction"> has a transaction object</param>
        /// <returns>Scalar result</returns>
        public async Task <object> ExecuteScalarAsync(string sql, Dictionary <string, object> parameters, CustomTransaction customTransaction)
        {
            var isCustomTransaction = customTransaction != null;

            try
            {
                if (isCustomTransaction)
                {
                    DbCommand = customTransaction.DbCommand;
                }
                else
                {
                    DbCommand.Connection = DBConnection;
                }

                DbCommand.CommandType = CommandType.Text;
                DbCommand.Parameters.Clear();

                if (DBConnection.State == ConnectionState.Closed)
                {
                    DBConnection.Open();
                }
                foreach (var parameter in parameters)
                {
                    DbCommand.Parameters.Add(_connection.GetParameter(parameter));
                }

                DbCommand.CommandText = sql;
                return(await DbCommand.ExecuteScalarAsync());
            }
            catch (Exception ex)
            {
                throw new CustomRepositoryException(ex.Message);
            }
            finally
            {
                DisposeDB(isCustomTransaction);
            }
        }
예제 #19
0
        /// <summary>
        /// Update an item asynchronously
        /// </summary>
        /// <param name="item"> item to update</param>
        /// <param name="customTransaction"> has a transaction object</param>
        /// <returns></returns>
        public async Task UpdateAsync(T item, CustomTransaction customTransaction)
        {
            if (_connection.Database == DataBaseType.Oracle)
            {
                throw new NotImplementedDatabaseException();
            }

            var isCustomTransaction = customTransaction != null;

            try
            {
                var connection = isCustomTransaction ? customTransaction.DbCommand.Connection : _connection.DataBaseConnection;

                var sql        = new StringBuilder();
                var primaryKey = "";
                var parameters = new Dictionary <string, object>();

                sql.AppendLine($"update {typeof(T).Name} set ");

                foreach (var p in item.GetType().GetProperties())
                {
                    if (item.GetType().GetProperty(p.Name) == null)
                    {
                        continue;
                    }

                    if (!IgnoreAttribute(p.CustomAttributes))
                    {
                        sql.Append($" {p.Name} = @{p.Name},");
                        parameters.Add($"@{p.Name}", item.GetType().GetProperty(p.Name)?.GetValue(item));
                    }

                    var primaryKeyAttribute = p.CustomAttributes.ToList().Any(x => x.AttributeType.ToString().ToUpper() == PrimaryKey);

                    if (primaryKeyAttribute)
                    {
                        primaryKey = p.Name;
                        parameters.Add($"@{primaryKey}", item.GetType().GetProperty(p.Name)?.GetValue(item));
                    }
                }

                sql.Remove(sql.Length - 1, 1);

                if (string.IsNullOrEmpty(primaryKey))
                {
                    throw new CustomRepositoryException("PrimaryKeyAttribute not defined");
                }

                sql.AppendLine($" where {primaryKey} = @{primaryKey}");

                if (isCustomTransaction)
                {
                    await connection.ExecuteAsync(sql.ToString(), parameters, customTransaction.DbCommand.Transaction);
                }
                else
                {
                    await connection.ExecuteAsync(sql.ToString(), parameters);
                }
            }
            catch (Exception ex)
            {
                throw new CustomRepositoryException(ex.Message);
            }
        }