locked
DataAdapter.Update does not update table RRS feed

  • 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();
    
            await cmd.OpenConnectionAsync();
    using (var tbl = new DataTable(oldData.TableName)) { dataAdapt.Fill(tbl); dataAdapt.FillSchema(tbl, SchemaType.Source); tbl.Merge(oldData); foreach (DataRow row in tbl.Rows)
    { try { row.SetModified(); } catch { } } 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:

    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


    Programamtore ASP.NET
    http://glucolo.wordpress.com






    • Moved by DotNet Wang Tuesday, December 29, 2015 3:00 AM more related to ado.net
    • Edited by Glauco Cucchiar Tuesday, December 29, 2015 1:42 PM
    Monday, December 28, 2015 4:06 PM

All replies

  • If this project is based on a local database file, not an SQL Server, then check the folders. Sometimes, for debugging purposes, the database file is duplicated by Visual Studio, therefore you will not see the changes inside the original database.

    How did you check the results within database tables?

    Monday, December 28, 2015 4:42 PM
  • yes, on my workstation the database is located in (localdb)\v11.0

    I'm checking results in two ways:

    • I've opened the table on visual studio "server explorer" windows
    • I've activated "Express profiler" on Sql server

    Now I have made same changes:

    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


    Programamtore ASP.NET
    http://glucolo.wordpress.com

    Monday, December 28, 2015 8:55 PM
  • Hi Glauco,

    >> I Execute the code and I don't have any errors, but the data are not updated.

    Could you please check if Datable(tbl)’s data is the same with Database.  I can’t find the following code snippet where the ‘data’ come from.

    foreach (DataRow row in data.Rows)

    If possible, could you please share your project and database to me. I can better provider a solution.

    Best regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Fred Bao Tuesday, December 29, 2015 8:00 AM
    Tuesday, December 29, 2015 8:00 AM
  • Hi Fred.

    this is my use of software:

    1 - perform a "select * from purchase where customerID = @id" with @id=3
    results are three record. the result is saved in a log record

    2 - perform a "update purchase set price=123 where customerID = @id" with @id=3
    three record updated (checked with profiler and viewving table data).

    3 - now I would like to run rollback with this steps:
    - I perform the same query "select * from purchase where customerID = @id" with @id=3 (into tbl)
    - I load previous data from log record (oldData)
    - I merge tables

    in debug mode, I see the right status of tbl rows (all records with price=123), and after merge I see all records with status=modified and old price values. But with the istruction dataAdapter.Update(tbl) nothing happens on the database.

    Using tbl.Merge(oldData, true) I see that a query is executed, but with wrong parameters value

    >> I can’t find the following code snippet where the ‘data’ come from.

    Sorry, I was wrong to write. The correct code (redundant because rows already have right status) is

    foreach (DataRow row in tbl.Rows)


    Programamtore ASP.NET
    http://glucolo.wordpress.com

    Tuesday, December 29, 2015 1:41 PM
  • Hi Glauco,

    I reproduced your code snippet on my side, I met the same problem as yours. I think that DataAdapter may generate an error of sql by using merge.  It works fine if modify your code snippet like below.

    using (var cmdBuilder = new SqlCommandBuilder(dataAdapt))
    {
           //dataAdapt.UpdateCommand = cmdBuilder.GetUpdateCommand();
           dataAdapt.UpdateCommand = new SqlCommand("UPDATE Demo1 SET Price=@Price WHERE id=@id;", con);
         dataAdapt.UpdateCommand.Parameters.Add("@Price", SqlDbType.Int, 4, "Price");
         dataAdapt.UpdateCommand.Parameters.Add("@id", SqlDbType.Int, 4, "id");
         dataAdapt.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
         dataAdapt.DeleteCommand = cmdBuilder.GetDeleteCommand();
         dataAdapt.InsertCommand = cmdBuilder.GetInsertCommand();
         await cmd.OpenConnectionAsync();        
         using (var tbl = new DataTable(oldData.TableName))
           {
                dataAdapt.Fill(tbl);
                dataAdapt.FillSchema(tbl, SchemaType.Source);
                tbl.Merge(oldData);
                foreach (DataRow row in tbl.Rows)             {
                    try { row.SetModified(); }
                    catch { }
                }
                record = dataAdapt.Update(tbl);
           }
       }
    

    Best regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Proposed as answer by Fred Bao Tuesday, January 5, 2016 7:04 AM
    • Marked as answer by Fred Bao Wednesday, January 6, 2016 9:36 AM
    • Edited by Fred Bao Friday, January 8, 2016 9:37 AM
    • Unmarked as answer by Fred Bao Wednesday, January 13, 2016 1:12 AM
    Wednesday, December 30, 2015 7:52 AM
  • Hi Fred,

    thanks for your help.

    I can't use your solution because my code is used in many cases. I can't create UpdateCommand manually, but using command buider for create query case by case


    Programamtore ASP.NET
    http://glucolo.wordpress.com

    Monday, January 11, 2016 5:48 PM
  • Hi Glauco,

    >> I can't use your solution because my code is used in many cases. I can't create UpdateCommand manually.

    You could override method “PrepareStructure”, and add a parameter “preUpdate” like “preSelect”, and add a property “PreUpdateCommand” on class “CommandStructure”. Then you doesn’t need to change method “RollbackQuery “

    Best regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, January 13, 2016 3:22 AM