DataAdapter .Update does not update back table

Question

my problem is very common, but I have not found any solution. This is my code:

public async Task<QueryResult> RollbackQuery(ActionLog action)
{
    var inputParameters = JsonConvert.DeserializeObject<Parameter[]>(action.Values);
    var data = DeserailizeByteArrayToDataSet(action.RollBackData);

    using (var structure = PrepareStructure(action.Query, action.Query.DataBase, inputParameters))
    {
        //_queryPlanner is the implementor for my interface
        return await _queryPlanner.RollbackQuery(structure, data);
    }
}

I need to load DataTable (from whereever) and replace data to database. This is my Rollback function. This function use a "CommandStructure" where I've incapsulated all SqlClient objects. PrepareStructure initialize all objects

//_dataLayer is an Helper for create System.Data.SqlClient objects
//ex: _dataLayer.CreateCommand(preSelect) => new SqlCommand(preSelect)
private CommandStructure PrepareStructure(string sql, string preSelect, DataBase db, IEnumerable<Parameter> inputParameters)
{
    var parameters = inputParameters as IList<Parameter> ?? inputParameters.ToList();

    var structure = new CommandStructure(_logger);
    structure.Connection = _dataLayer.ConnectToDatabase(db);
    structure.SqlCommand = _dataLayer.CreateCommand(sql);
    structure.PreSelectCommand = _dataLayer.CreateCommand(preSelect);
    structure.QueryParameters = _dataLayer.CreateParemeters(parameters);
    structure.WhereParameters = _dataLayer.CreateParemeters(parameters.Where(p => p.IsWhereClause.HasValue && p.IsWhereClause.Value));
    structure.CommandBuilder = _dataLayer.CreateCommandBuilder();
    structure.DataAdapter = new SqlDataAdapter();

    return structure;
}

So, my function uses SqlCommandBuilder and DataAdapter to operate on Database. PreSelectCommand is like "Select * from Purchase where CustomerId = @id" The table Purchase has one primaryKey on ID filed

public virtual async Task<QueryResult> RollbackQuery(CommandStructure cmd, DataTable oldData)
{
    await cmd.OpenConnectionAsync();

    int record = 0;
    using (var cmdPre = cmd.PreSelectCommand as SqlCommand)
    using (var dataAdapt = new SqlDataAdapter(cmdPre))
    using (var cmdBuilder = new SqlCommandBuilder(dataAdapt))
    {
        dataAdapt.UpdateCommand = cmdBuilder.GetUpdateCommand();
        dataAdapt.DeleteCommand = cmdBuilder.GetDeleteCommand();
        dataAdapt.InsertCommand = cmdBuilder.GetInsertCommand();

        using (var tbl = new DataTable(oldData.TableName))
        {

            dataAdapt.Fill(tbl);
            dataAdapt.FillSchema(tbl, SchemaType.Source);

            tbl.Merge(oldData);
            foreach (DataRow row in tbl.Rows)
            {
                row.SetModified();
            }

            record = dataAdapt.Update(tbl);
        }
    }
    return new QueryResult
    {
        RecordAffected = record
    };
}

I Execute the code and I don't have any errors, but the data are not updated. variable "record" contain the right number of modified (??) record, but..... on the table nothing

can someone help me?

EDIT 1: With SQL Profiler I saw that no query is executed on DB. Only select query on .Fill(tbl) command.

EDIT 2: Now I have made one change:

tbl.Merge(oldData) => tbl.Merge(oldData, true)

so I see perform the expected query but, with reversed parameters.

UPDATE Purchase SET price=123 where id=6 and price=22

instead of

UPDATE Purchase SET price=22 where id=6 and price=123

DataAdapter .Update does not update back table

0 Answers0