LINQ to SQL and the XML datatype
Today i have done some stuff with LINQ to SQL and the xml data type from SQL Server 2005. I had some problem finding information about this and spent some hours figuring out how the insert/update/delete worked. Post a comment if you find something I’ve done wrong. This is mostly for my own reference.
Get data
var q = (from ec in Business.PostCalc. ExtraCompensation.Descendants("compensation")
select new { id = ec.Attribute("id").Value,
code = ec.Element("code").Value,
amount = ec.Element("amount").Value,
description = ec.Element("description").Value }).ToList();
lwExtraCompensation.DataSource = q;
lwExtraCompensation.DataBind();
Delete data
You should of course run SubmitChanges and so on in some method. I do this way so that we can save everything at once later.
XElement ec = Business.PostCalc.ExtraCompensation;
XElement newExtraComp= new XElement(ec);
newExtraComp.DescendantsAndSelf("compensation")
.Where(x => x.Attribute("id").Value.ToString() == e.CommandArgument.ToString()).First().Remove();
Business.PostCalc.ExtraCompensation = newExtraComp;
txtDescription.Text = "";
txtAmount.Text = "";
txtCode.Text = "";
PostCalculationChanged(this, new EventArgs());
Update data
XElement ec = Business.PostCalc.ExtraCompensation;
XElement newExtraCompensation = new XElement(ec);
newExtraCompensation.Add(new XElement("compensation",
new XElement("description", txtDescription.Text),
new XElement("amount", txtAmount.Text),
new XElement("code", txtCode.Text),
new XAttribute("id", Guid.NewGuid()))
);
Business.PostCalc.ExtraCompensation = newExtraCompensation;
txtDescription.Text = "";
txtAmount.Text = "";
txtCode.Text = "";
PostCalculationChanged(this, new EventArgs());
