Inserting NULL into DataTable
Is ParentID of cntIuvo.MenuNavs nullable?
myDR["ParentID"] = rec.ParentID ?? Convert.DBNull; // Replace null value to DBNull
Paul Innes
Updated on June 04, 2022Comments
-
Paul Innes almost 2 years
Morning (in RSA at least),
I'm trying to create a data driven menu, using data from a self-referencing table to create a (2-level) hierarchical structure. Example Data is:
MenuID ParentID Text Url CSS 1 Null Top topCSS 2 Null Second secCSS 3 1 abc z.aspx abcCSS 4 1 def y.aspx abcCSS 5 2 ghi x.aspx defCSS
I'm using LINQ to Entities to get this data. Then I populate a DataTable, then populate a DataSet and then create a DataRelation before converting it to XML to use in a xmlDataSource where it gets transformed for use as the Menu's DataSource.
I must admit I've taken a lot of code from these Forums, and it should work. Except the transform requires a NULL value in ParentID to indicate a top-level menu item, but I can't insert a NULL into the DataTable. Code is below:
using (var cntIuvo = new iuvocexi_dbEnts()) { var b = (from a in cntIuvo.MenuNavs select a); DataTable myTB = new DataTable(); myTB.Columns.Add("MenuID"); myTB.Columns.Add("ParentID"); myTB.Columns.Add("url"); myTB.Columns.Add("CSS"); DataRow myDR; foreach (var rec in b) { myDR = myTB.NewRow(); myDR["MenuID"] = rec.MenuID; myDR["ParentID"] = rec.ParentID; // error is generated here myDR["url"] = rec.url; myDR["CSS"] = rec.CSS; myTB.Rows.Add(myDR); } DataSet ds = new DataSet(); ds.Tables.Add(myTB); ds.DataSetName = "Menus"; ds.Tables[0].TableName = "Menu"; DataRelation relation = new DataRelation("ParentChild", ds.Tables["Menu"].Columns["MenuID"], ds.Tables["Menu"].Columns["ParentID"], true); relation.Nested = true; ds.Relations.Add(relation); xmlDataSource1.Data = ds.GetXml(); if (Request.Params["Sel"] != null) Page.Controls.Add(new System.Web.UI.LiteralControl("You selected " + Request.Params["Sel"])); }
My question is: How do I insert NULL into the DataTable, or, failing that, how do I get the LINQ to Entities to populate a DataTable/DataSet, or, failing that how do I set up the Transform to allow for (say) a 0 instead of NULL.
Transform.xslt is below:
<?xml version="1.0" encoding="utf-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="xml" indent="yes" encoding="utf-8"/> <!-- Replace root node name Menus with MenuItems and call MenuListing for its children--> <xsl:template match="/Menus"> <MenuItems> <xsl:call-template name= "MenuListing" /> </MenuItems> </xsl:template> <!-- Allow for recursive child nodeprocessing --> <xsl:template name="MenuListing"> <xsl:apply-templates select ="Menu" /> </xsl:template> <xsl:template match="Menu"> <MenuItem> <!-- Convert Menu child elements to MenuItem attributes --> <xsl:attribute name="Text"> <xsl:value-of select="Text"/> </xsl:attribute> <xsl:attribute name="ToolTip"> <xsl:value-of select="Text"/> </xsl:attribute> <xsl:attribute name="NavigateUrl"> <xsl:text>?Sel=</xsl:text> <xsl:value-of select = "url"/> </xsl:attribute> <!-- Recursively call MenuListing forchild menu nodes --> <xsl:if test="count(Menu) >0"> <xsl:call-template name="MenuListing" /> </xsl:if> </MenuItem> </xsl:template> </xsl:stylesheet>
Thank you very much for your attention so far!
Regards
Paul