I have a table which has some 100-200 records. I have fetch those records into a dataset.
Now i am looping through all the records using foreach
dataset.Tables[0].AsEnumerable()
I want to update a column for each record in the loop. How can i do this. Using the same dataset.
I'm Assumng your using a Data Adapter to Fill the Data Set, with a Select Command?
To edit the data in your Data Table and save changes back to your database you will require an Update Command for you Data Adapter. Something like this :-
SQLConnection connector = new SQLConnection(@"Your connection string");
SQLAdaptor Adaptor = new SQLAdaptor();
Updatecmd = new sqlDbCommand("UPDATE YOURTABLE SET FIELD1= @FIELD1, FIELD2= @FIELD2 WHERE ID = @ID", connector);
You will also need to Add Parameters for the fields :-
Updatecmd.Parameters.Add("@FIELD1", SQLDbType.VarCHar, 8, "FIELD1");
Updatecmd.Parameters.Add("@FIELD2", SQLDbType.VarCHar, 8, "FIELD2");
var param = Updatecmd.Parameters.Add("@ID", SqlDbType.Interger, 6, "ID");
param.SourceVersion = DataRowVersion.Original;
Once you have created an Update Command with the correct SQL statement, and added the parameters, you need to assign this as the Insert Command for you Data Adapter :-
Adaptor.UpdateCommand = Updatecmd;
You will need to read up on doing this yourself, go through some examples, this is a rough guide.
The next step is to Enumerate through your data table, you dont need LINQ, you can do this :-
foreach(DataRow row in Dataset.Tables[0].Rows)
{
row["YourColumn"] = YOURVALUE;
}
One this is finished, you need to call the Update() method of yout Data Adapter like so :-
DataAdapter.Update(dataset.Tables[0]);
What happens here, is the Data Adapter calls the Update command and saves the changes back to the database.
Please Note, If wish to ADD new rows to the Database, you will require am INSERT Command for the Data Adapter.
This is very roughly coded out, from the top of my head, so the syntax may be slightly out. But will hopefully help.
You should use original DataAdapter (adapter in code below) that was used to fill DataSet and call Update method, you will need CommandBuilder also, it depends what DB you are using, here is the example for SQL server :
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.Update(dataset);
dataset.AcceptChanges();
Here is the good example :
The steps would be something like: - create a new DataColumn[^] - add it to the data table's Columns[^] collection - Create a DataRow [^] for example using NewRow[^] - Modify the values of the row as per needed using Item[^] indexer - add the row to Rows[^] collection - after succesful modifications AcceptChanges[^]
Like this:
DataTable table = new DataTable();
table.Columns.Add("ID", typeof(int));
table.Columns.Add("ProductName");
table.Rows.Add(1, "Chai");
table.Rows.Add(2, "Queso Cabrales");
table.Rows.Add(3, "Tofu");
EnumerableRowCollection<DataRow> Rows = table.AsEnumerable();
foreach (DataRow Row in Rows)
Row["ID"] = (int)Row["ID"] * 2;
Add the column like below.
dataset.Tables[0].Columns.Add(new DataColumn ("columnname"));
Update the columns values like below.
for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
dataset.Tables[0].Rows[i]["columnname"] = "new value here";
}
Update Database
dataset.AcceptChanges();
