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); } }
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); } } }