C# DataTable Update Multiple Lines

Question

how can i do multiple update using datatable ?

my code:

public void ExportCSV(string SQLSyntax, string LeFile, bool Is_Ordre, int TypeDonne)
        {
            try
            {
                using (var connectionWrapper = new Connexion())
                {
                    var connectedConnection = connectionWrapper.GetConnected();


                    SqlDataAdapter da = new SqlDataAdapter(SQLSyntax, connectionWrapper.conn);
                    DataSet ds = new DataSet();
                    da.Fill(ds, "Emp");
                    DataTable dt = ds.Tables["Emp"];
                    CreateCSVFile(dt, LeFile, Is_Ordre, TypeDonne);

                    //Update all lines, it not save in Database
                    foreach (DataRow row in dt.Rows)
                    {
                        row["IS_IMPORT"] = true;
                    }
                }
            }
            catch (Exception excThrown)
            {
                throw new Exception(excThrown.Message);
            }



        }

the problem is :

foreach (DataRow row in dt.Rows)
                        {
                            row["IS_IMPORT"] = true;
                        }

it not save it into database.

Thanks you in advance, Stev

beware: this approach is high memory consuming. If your table has more than 1000 rows (arbitrary number), you will have a high memory impact. Why don't you move to a SqlDataReader, that can read lines by lines, then append a string line to the csv file using a file stream — Steve B
– Steve B, Commented Jan 13, 2012 at 14:24

AdaTheDev · Accepted Answer · 2012-01-13 12:58:54Z

4

You need to first set the UpdateCommand property on the DataAdapter to the UPDATE statement that will be executed to update a row in the database.

Then, after updating values in the DataTable, you need to pass it to DataAdapter.Update(). This will then execute the UpdateCommand for each updated row in the DataTable.

References:

MSDN - SqlDataAdapter.Update
MSDN - SqlDataAdapter.UpdateCommand

answered Jan 13, 2012 at 12:58

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

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

Comments

Steve B · Accepted Answer · 2012-01-13 13:01:47Z

3

You are updating the value in-memory. The DataTable class is not a sql view, but a memory representation. The Sql Data Adapter only copy the data.

You have to write back the changes to the DB. Try this :

public void ExportCSV(string SQLSyntax, string LeFile, bool Is_Ordre, int TypeDonne)
    {
        try
        {
            using (var connectionWrapper = new Connexion())
            {
                var connectedConnection = connectionWrapper.GetConnected();


                SqlDataAdapter da = new SqlDataAdapter(SQLSyntax, connectionWrapper.conn);

                da.UpdateCommand = connectedConnection.CreateCommand();
                da.UpdateCommand.XXXX = YYYY; // construct the SQL Command                    

                DataSet ds = new DataSet();
                da.Fill(ds, "Emp");
                DataTable dt = ds.Tables["Emp"];
                CreateCSVFile(dt, LeFile, Is_Ordre, TypeDonne);

                //Update all lines, it not save in Database
                foreach (DataRow row in dt.Rows)
                {
                    row["IS_IMPORT"] = true;
                }

                da.Update(dt);
            }
        }
        catch (Exception excThrown)
        {
            throw new Exception(excThrown.Message);
        }
    }

This should works.

answered Jan 13, 2012 at 13:01

Steve B

38k24 gold badges109 silver badges186 bronze badges

2 Comments

user609511 Over a year ago

Thanks you for your response. It work well. Just curiosity why i need both: da.UpdateCommand.CommandText = "UPDATE ORDRE SET IS_IMPORT = 'true'"; and

foreach (DataRow row in dt.Rows)                     {                         row["IS_IMPORT"] = true;                     }

Steve B Over a year ago

your sql query update all data in the table. You have to build a sql query than can used by the SqlDataAdapter Update method. when you wrote row["IS_import"] = true; it mark the row as updated. call to SqlDataAdapter.Update will take all updated rows of the table, then issue the corresponding update command. Suggestion : as you seems to discover these class, give a chance to the visual studio designer to build everything for you.

Skyrim · Accepted Answer · 2012-01-13 13:03:37Z

0

You will have to call da.Update()

answered Jan 13, 2012 at 13:03

Skyrim

8655 silver badges6 bronze badges

Collectives™ on Stack Overflow

C# DataTable Update Multiple Lines

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related