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