C# DataTable Update Multiple Lines

问题:

how can i do multiple update using datatable ?

i found this Update 1 row

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

回答1:

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.

回答2:

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

回答3:

You will have to call da.Update()