Example #1
0
        public async Task <object> AddAsync(Dictionary <string, object> item, List <CustomField> customFields,
                                            bool skipPrimaryKey)
        {
            if (string.IsNullOrWhiteSpace(this.Database))
            {
                return(null);
            }

            if (!this.SkipValidation)
            {
                if (!this.Validated)
                {
                    await this.ValidateAsync(AccessTypeEnum.Create, this.LoginId, this.Database, false).ConfigureAwait(false);
                }
                if (!this.HasAccess)
                {
                    Log.Information(
                        $"Access to add entity \"{this.FullyQualifiedObjectName}\" was denied to the user with Login ID {this.LoginId}. {item}");
                    throw new UnauthorizedException("Access is denied.");
                }
            }

            item = this.Crypt(item);

            item["audit_user_id"] = this.UserId;
            item["audit_ts"]      = DateTimeOffset.UtcNow;
            item["deleted"]       = false;

            using (var db = DbProvider.GetDatabase(this.Database))
            {
                string columns = string.Join
                                     (",",
                                     skipPrimaryKey
                            ? item.Where(x => !x.Key.ToUnderscoreLowerCase().Equals(this.PrimaryKey))
                                     .Select(x => Sanitizer.SanitizeIdentifierName(x.Key).ToUnderscoreLowerCase())
                            : item.Select(x => Sanitizer.SanitizeIdentifierName(x.Key).ToUnderscoreLowerCase()));

                string parameters = string.Join(",",
                                                Enumerable.Range(0, skipPrimaryKey ? item.Count - 1 : item.Count).Select(x => "@" + x));

                var arguments = skipPrimaryKey
                    ? item.Where(x => !x.Key.ToUnderscoreLowerCase().Equals(this.PrimaryKey))
                                .Select(x => x.Value).ToArray()
                    : item.Select(x => x.Value).ToArray();

                var sql = new Sql("INSERT INTO " + this.FullyQualifiedObjectName + "(" + columns + ")");
                sql.Append("SELECT " + parameters, arguments);

                sql.Append(FrapidDbServer.AddReturnInsertedKey(this.Database, this.PrimaryKey));

                var primaryKeyValue = await db.ScalarAsync <object>(sql).ConfigureAwait(false);

                await this.AddCustomFieldsAsync(primaryKeyValue, customFields).ConfigureAwait(false);

                return(primaryKeyValue);
            }
        }
Example #2
0
        public async Task <List <object> > BulkImportAsync(List <Dictionary <string, object> > items)
        {
            if (!this.SkipValidation)
            {
                if (!this.Validated)
                {
                    await this.ValidateAsync(AccessTypeEnum.ImportData, this.LoginId, this.Database, false).ConfigureAwait(false);
                }

                if (!this.HasAccess)
                {
                    Log.Information(
                        $"Access to import entity \"{this.FullyQualifiedObjectName}\" was denied to the user with Login ID {this.LoginId}.");
                    throw new UnauthorizedException("Access is denied.");
                }
            }

            var result = new List <object>();
            int line   = 0;

            using (var db = DbProvider.GetDatabase(this.Database))
            {
                try
                {
                    await db.BeginTransactionAsync().ConfigureAwait(false);

                    items = this.Crypt(items);

                    foreach (var item in items)
                    {
                        line++;

                        item["audit_user_id"] = this.UserId;
                        item["audit_ts"]      = DateTimeOffset.UtcNow;
                        item["deleted"]       = false;

                        var primaryKeyValue = item[this.PrimaryKey];

                        if (primaryKeyValue != null)
                        {
                            result.Add(primaryKeyValue);
                            var sql = new Sql("UPDATE " + this.FullyQualifiedObjectName + " SET");

                            int index = 0;

                            foreach (var prop in item.Where(x => !x.Key.Equals(this.PrimaryKey)))
                            {
                                if (index > 0)
                                {
                                    sql.Append(",");
                                }

                                sql.Append(Sanitizer.SanitizeIdentifierName(prop.Key) + "=@0", prop.Value);
                                index++;
                            }


                            sql.Where(this.PrimaryKey + "=@0", primaryKeyValue);

                            await db.NonQueryAsync(sql).ConfigureAwait(false);
                        }
                        else
                        {
                            string columns = string.Join(",",
                                                         item.Where(x => !x.Key.Equals(this.PrimaryKey))
                                                         .Select(x => Sanitizer.SanitizeIdentifierName(x.Key)));

                            string parameters = string.Join(",",
                                                            Enumerable.Range(0, item.Count - 1).Select(x => "@" + x));
                            var arguments =
                                item.Where(x => !x.Key.Equals(this.PrimaryKey)).Select(x => x.Value).ToArray();

                            var sql = new Sql("INSERT INTO " + this.FullyQualifiedObjectName + "(" + columns + ")");
                            sql.Append("SELECT " + parameters, arguments);

                            sql.Append(FrapidDbServer.AddReturnInsertedKey(this.Database, this.PrimaryKey));

                            result.Add(await db.ScalarAsync <object>(sql).ConfigureAwait(false));
                        }
                    }

                    db.CommitTransaction();

                    return(result);
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    string errorMessage = $"Error on line {line}. {ex.Message} ";
                    throw new DataAccessException(errorMessage, ex);
                }
            }
        }