How to use SqlDataAdapter to update multiple row in c#

Question

I use SqlDataAdapter and SqlCommandBuilder to perform DML transactions on rows in a SQL Server database.

I am able to add and delete multiple rows in database but update.

This is the code:

SqlDataAdapter da = new SqlDataAdapter(@"select top 1 * from " + tableName, 
ConnectionString);
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(da);
da.Update(dt);

I'm trying to use AcceptChanges, so far it doesn't work.

Why not use SqlCommand, add sql compatible parameters and then do an executeNonQuery? — aspiring
– aspiring, Commented Jun 11, 2015 at 3:40
@meomeo include the cmdBuilder.GetUpdateCommand(); before your da.Update(dt); line and check if your database gets updated. — fujiFX
– fujiFX, Commented Jun 11, 2015 at 4:07

aspiring · Accepted Answer · 2015-06-11 03:56:39Z

0

This is how I usually do within C#. If you want to give this a try.

//you may put this as a direct string or in a static class when layering
//you can pass table as hard-coded value or as a parameter
String SqlQuery = "UPDATE " +
            " [tableName] " +
            " SET [Column1ToBeUpdated]=@Column1Value," +
            " [Column2ToBeUpdated]=@Column2Value" +
            " WHERE ([ColumnxWithCondition] = @Condition)";

//add OR, AND operators as per your needs
//choose the correct SqlDbType for your column data types

public bool UpdateMyTable(String SqlQuery, Someclass obj)
 {
        SqlCommand sCommand = new SqlCommand(this.SqlQuery, (new SqlConnection(ConnectionString)));

        sCommand.Parameters.Add("@Column1Value", SqlDbType.VarChar).Value = obj.col1Value;
        sCommand.Parameters.Add("@Column2Value", SqlDbType.VarChar).Value = obj.col2Value;
        sCommand.Parameters.Add("@Condition", SqlDbType.VarChar).Value = obj.condition;

        sCommand.Connection.Open();
        var rowsAffected = sCommand.ExecuteNonQuery();
        sCommand.Connection.Close();
        return rowsAffected > 0;
 }

 //if you want to see the number, you may return rowsAffected

answered Jun 11, 2015 at 3:56

aspiring

1,6672 gold badges21 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

aspiring Over a year ago

I just noticed that @TimSchmelter has a great answer here with SqlDataAdapter for batch updates. You can try that out.

Bader · Accepted Answer · 2015-06-12 08:23:45Z

0

i have found reason, a column is set DateTime type. So, this Column isn't saving to the database.

answered Jun 12, 2015 at 8:23

Bader

336 bronze badges

Collectives™ on Stack Overflow

How to use SqlDataAdapter to update multiple row in c#

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related