TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
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?
Reply
Answers (
0
)
c#.net login form
how to get all feature classes from geodatabase to combobox