Hello, I'm trying to use BulkMerge with a response from JSON web API call.
I created a dataTable from the result set and am trying to dynamically create the table then a PK, then bulk merge, however I keep getting same error regarding primary key.
IList<dynamic> table = JsonConvert.DeserializeObject<List<dynamic>>(Convert.ToString(json!.response.results));
DataTable dataTable = JsonConvert.DeserializeObject<DataTable>(Convert.ToString(json.response.results));
var columns = dataTable.Columns.OfType<DataColumn>().Select(c => c.ColumnName).ToList();
dataTable.PrimaryKey = new DataColumn[] { dataTable.Columns["policy_id"] };
_connection = new NpgsqlConnection(_configuration.GetConnectionString("DbConnection"));
_connection.Open();
var sqlCreateCommand = $"CREATE TABLE IF NOT EXISTS public.crm_{module} (";
sqlCreateCommand += string.Join(" ", (from dc in dataTable.Columns.Cast<DataColumn>() select $"{dc.ColumnName}" + $" {dc.DataType.ToPgSqlDataType(dc.ColumnName)},").ToArray()).RemoveTrailingComma() + ");";
var cmd = new NpgsqlCommand(sqlCreateCommand, (NpgsqlConnection)_connection);
cmd.ExecuteNonQuery();
var sqlCreatePK = $@"do $$ begin if not exists (select 1 from information_schema.table_constraints where table_name = 'crm_{module}' and constraint_type = 'PRIMARY KEY') then ALTER TABLE crm_{module} ADD PRIMARY KEY ({dataTable.PrimaryKey[0].ColumnName}); end if; end; $$";
cmd.CommandText = sqlCreatePK;
cmd.ExecuteNonQuery();
var bulk = new BulkOperation((DbConnection)_connection)
{
DestinationTableName = module,
AllowDuplicateKeys = false,
AutoMapOutputIdentity = true,
BatchSize = 1000,
UseInternalTransaction = true,
AllowUpdatePrimaryKeys = false,
AutoMapIdentityName = "policy_id",
AutoMapKeyName = "policy_id;lead_id"
};
await bulk.BulkMergeAsync(dataTable, CancellationToken.None);
cmd.Dispose();
return bulk.RowsAffected;
The table does have a primary key and I'm setting the value in the call, but I still get this exception:
System.Exception: An error occured, no primary key could be found or resolved.
at Z.BulkOperations.BulkOperation.()
at Z.BulkOperations.BulkOperation.Execute()
at System.Threading.Tasks.Task.InnerInvoke()
Hello, I'm trying to use BulkMerge with a response from JSON web API call. I created a dataTable from the result set and am trying to dynamically create the table then a PK, then bulk merge, however I keep getting same error regarding primary key.
The table does have a primary key and I'm setting the value in the call, but I still get this exception: