Henry Vuong

Henry Vuong

  • NA
  • 27
  • 0

Read XML from database, modify and save back to database

Aug 23 2014 9:06 PM

I have a table "Items" in my database with the following fields:

ItemID (PK, int)         Title (nvarchar)         ItemSpecifics (XML)
101                          Item A                       <SelectedValues><SelectedValue><Name>...
102                          Item B                       <SelectedValues><SelectedValue><Name>...
105                          Item F                       <SelectedValues><SelectedValue><Name>...
110                          Item H                       <SelectedValues><SelectedValue><Name>...
200                          Item X                       <SelectedValues><SelectedValue><Name>...

The XML value of one of the rows looks like this:

<SelectedValues>
   <SelectedValue>
          <Name>Brand</Name>
          <Value>Nike</Value>
   </SelectedValue>
   <SelectedValue>
          <Name>Item Condition</Name>
          <Value>New</Value>
          <ValueID>3000</ValueID>
   </SelectedValue>
   <SelectedValue>
          <Name>Details</Name>
          <Value />
   </SelectedValue>
</SelectedValues>

What I want to do:
1) Read the data from the database and load it into a dataset
2) Bind the dataset to a datagridview so a user can modify the data
3) Save the changes back to database

The main point of this operation is to add some text value into the last node of the XML field, "Details". So when reading data from table "Items", I only want to get the value of "Details" node, like so:

SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();

string str = @"SELECT  ItemID, Title,
       ItemSpecifics.value('(/SelectedValues/SelectedValue[Name=""Details""]/Value)[1]',              'varchar(max)') AS Details FROM Items";

try
{
          sqlCmd = new SqlCommand(str, connection);
          da.SelectCommand = sqlCmd;
}
catch (SqlException ex)
{
          MessageBox.Show(ex.ToString());
 }

da.Fill(ds, "Items");

//bind the dataset to a datagridview:
BindingSource bs= new BindingSource();
bs.DataSource = ds;
bs.DataMember = "Items";
dataGridView1.DataSource = bs;

When the program runs, user can add text to the "Details" column and click a button to save the changes back to database. This is the method to save changes back to database:

private void UpdateItems()
{
  try
  {
          SqlCommandBuilder cb = new SqlCommandBuilder(da);
          da.Update(ds.Tables["Items"]);
  }
  catch (SqlException ex)
  {
          MessageBox.Show(ex.ToString());
  }
}

My expectation is that when user added text to the "Details" column in datagridview and click "save" button to call the method UpdateItems(), whatever added to the details of any item should be saved to the value of the "Details" node in XML column of the database table, but it's not the case. The program runs fine but the XML in database is not updated. What am I missing?