CODEWITHSUNDEEP
c#.netado.net
Kishore Kumar
Kishore Kumar

Reputation: 12874

Updating using DataTable in database table?

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.

Upvotes: 2

Views: 23286

Answers (5)

Antonio Bakula
Antonio Bakula

Reputation: 20693

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 :

http://support.microsoft.com/kb/307587

Upvotes: 3

Derek
Derek

Reputation: 8628

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.

Upvotes: 4

cshemby
cshemby

Reputation: 198

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();

Upvotes: -1

cshemby
cshemby

Reputation: 198

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[^]

Upvotes: 0

Steve Wellens
Steve Wellens

Reputation: 20620

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;

Upvotes: -1

Related Questions