SQL Server, SQL Server Express, and SqlDataSource Control

Re: Complicated SP

TATWORTH — Wed, 23 Jul 2008 04:47:20 GMT

>When I do the call to the database I can't get it to work if I do that in my db-class.
Does it produce any error message or just no output?

Re: Complicated SP

svetsarn — Tue, 22 Jul 2008 17:32:44 GMT

After alot of searching on the Internet and testing I have finaly got it to work. There will be no xml-file, but I guess I have to live with that. By the way I have ordered a book about this subject:)

I have one last question about this. When I do the call to the database I can't get it to work if I do that in my db-class. I must do something wrong. Can you please show me how to do that?

<asp:Menu ID="menu" Orientation="Horizontal" DataSourceID="XmlDataSource1" runat="server"
DynamicHorizontalOffset="2" StaticSubMenuIndent="10px" StaticDisplayLevels="1" >
<DataBindings>
   <asp:MenuItemBinding DataMember="MenuItem" NavigateUrlField="NavigateUrl"
             TextField="Text" ToolTipField="ToolTip"/>
          </DataBindings>
<StaticMenuItemStyle HorizontalPadding="5px" VerticalPadding="2px" />
<DynamicMenuItemStyle HorizontalPadding="5px" VerticalPadding="2px" />
</asp:Menu>
<asp:XmlDataSource ID="XmlDataSource1" TransformFile="~/TransformXSLT.xsl"
XPath="MenuItems/MenuItem" runat="server"/>

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        DataSet ds = new DataSet();
        using (conn)
        {
            string sql = "Select MenuID, Text, Description, ParentID, menuPath from MenuDB";
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            da.Fill(ds);
            da.Dispose();
        }

        ds.DataSetName = "Menus";
        ds.Tables[0].TableName = "Menu";
        DataRelation relation = new DataRelation("ParentChild",
         ds.Tables["Menu"].Columns["MenuID"],
         ds.Tables["Menu"].Columns["ParentID"], false);
        relation.Nested = true;
        ds.Relations.Add(relation);
        XmlDataSource1.Data = ds.GetXml();

        XmlDataSource1.EnableCaching = false;
        XmlDataSource1.DataBind();

<xsl:template match="/Menus">
    <MenuItems>
      <xsl:call-template name="MenuListing" />
    </MenuItems>
  </xsl:template>

  <xsl:template name="MenuListing">
    <xsl:apply-templates select="Menu" />
    </xsl:template>

    <xsl:template match="Menu">
      <MenuItem>

        <xsl:attribute name="Text">
          <xsl:value-of select="Text"/>
          </xsl:attribute>
          <xsl:attribute name="ToolTip">
            <xsl:value-of select="Description"/>
            </xsl:attribute>
            <xsl:attribute name="NavigateUrl">
              <xsl:value-of select="menuPath"/>
              </xsl:attribute>

              <xsl:if test="count(Menu) > 0">
                <xsl:call-template name="MenuListing" />
              </xsl:if>
            </MenuItem>
    </xsl:template>

Re: Complicated SP

svetsarn — Tue, 22 Jul 2008 12:36:08 GMT

How do you mean with an explicit orderBy column? That might be the only way to do.

Re: Complicated SP

svetsarn — Tue, 22 Jul 2008 09:22:37 GMT

Thank you for trying to help me TATWORTH. I tried your SP but get this error message " I by the way changed id to menuID.

No rows affected.

(0 row(s) returned)

FOR XML EXPLICIT requires the second column to hold NULL or nonnegative integers that represent XML parent tag IDs.

XML_F52E2B61-18A1-11d1-B105-00805F49916B
"

Re: Complicated SP

TATWORTH — Tue, 22 Jul 2008 05:52:32 GMT

I have seen suggestions if using a stylesheet with an xsl:sort to order the rows.

Re: Complicated SP

TATWORTH — Tue, 22 Jul 2008 05:40:36 GMT

The stored procedure

ALTER PROC doXml
as
select
1 as tag ,
ParentId as parent ,
CONVERT(VARCHAR(10), Id) + '-' AS [menuItem!1!X],
[text] as [menuItem!1!text] ,
[description] as [menuItem!1!description] ,
menuPath as [menuItem!1!path],
null as [menuItem!2!text] ,
null as [menuItem!2!description],
null as [menuItem!2!path]
from menuDB
where ParentId Is Null
union all
select
2 ,
m1.ParentId ,
CONVERT(VARCHAR(10), m2.ParentId) + '-' + CONVERT(VARCHAR(10), m2.Id) AS [menuItem!1!X],
m1.[text] ,
m1.[description] ,
m1.menuPath,
m2.[text] ,
m2.description,
m2.menuPath
from menuDB m1 , menuDB m2
where m2.parentid = m1.Id
ORDER BY 3
for xml explicit
GO
EXEC dbo.doXml

gave

This is at least ordered better

It might be best to an explicit OrderBy column to order children within parent

Re: Complicated SP

dhimant — Tue, 22 Jul 2008 05:40:03 GMT

svetsarn:
Inow have gone back to try with one table, but I got same result now. All childnodes comesunder the last node.

You need to provide the order by clause in order to get the correct results back. Please visit the below discussion if you have not yet.

http://forums.asp.net/p/1182514/2008292.aspx

Re: Complicated SP

TATWORTH — Tue, 22 Jul 2008 05:37:39 GMT

Using

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MenuDB](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ParentId] [int] NULL,
    [Description] [nvarchar](50) NOT NULL,
    [MenuPath] [nvarchar](50) NOT NULL,
    [Text] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_menuDB] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[MenuDB] WITH CHECK ADD CONSTRAINT [FK_menuDB_menuDB] FOREIGN KEY([ParentId])
REFERENCES [dbo].[MenuDB] ([Id])
GO
ALTER TABLE [dbo].[MenuDB] CHECK CONSTRAINT [FK_menuDB_menuDB]

Re: Complicated SP

svetsarn — Mon, 21 Jul 2008 22:50:02 GMT

Inow have gone back to try with one table, but I got same result now. All childnodes comesunder the last node.

ALTER PROC doXml
as
select
1 as tag ,
null as parent ,
[text] as [menuItem!1!text] ,
description as [menuItem!1!description] ,
menuPath as [menuItem!1!path],
null as [menuItem!2!text] ,
null as [menuItem!2!description],
null as [menuItem!2!path]
from menuDB
where parentid = -1
union all
select
2 ,
1 ,
m1.[text] ,
m1.description ,
m1.menuPath,
m2.[text] ,
m2.description,
m2.menuPath
from menuDB m1 , menuDB m2
where m2.parentid = m1.menuID
for xml explicit

Re: Complicated SP

svetsarn — Mon, 21 Jul 2008 20:45:16 GMT

Well, this is not working att all:( All childnodes comes under the last node. I really need help with this

Re: Complicated SP

svetsarn — Mon, 21 Jul 2008 20:01:12 GMT

I really think I have done it:) This is my code, if you se something I can change please write and tell me. I will not mark this ar solved just yet if it's not working as I expect. Now I will build an xsl-file to format the whole thing.

SP
ALTER PROC doXml
as
select 1 as tag,
null as parent,
m.menuText AS [menuItem!1!title],
m.menuToolTip as [menuItem!1!tool],
m.menuPath as[menuItem!1!url],
null as [subMenuItem!2!title],
null as [subMenuItem!2!tool],
null as [subMenuItem!2!url]
FROM menu m
UNION ALL
select 2 as tag,
1 as parent,
null,
null,
null,
sm.subMenuText,
sm.subMenuToolTip,
sm.subMenuPath
from submenu sm
WHERE exists(select * from menu WHERE menuID = sm.parentID)
FOR XML EXPLICIT

That generates this:

<?xml version="1.0" encoding="UTF-8"?>

<menu>
<menuItem title="Hem" tool="Till F”rstasidan" url="~/default.aspx" />
<menuItem title="Kontakt" tool="Till kontaktsidan" url="~/contact.aspx" />
<menuItem title="Podcasts" tool="Till sidan med pods" url="~/pods.aspx">
<subMenuItem title="Lab podden" tool="En pod jag har gjort" url="~/labPod.aspx" />
<subMenuItem title="Musik podden" tool="En musikpod jag har gjort" url="~/musicPod.aspx" />
</menuItem>
</menu>

DB-class

public void DoXml()
{
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("doXml", conn);
cmd.CommandType = CommandType.StoredProcedure;
XmlDocument xmlDoc = new XmlDocument();
string myXml = null;
try
{
conn.Open();
myXml = cmd.ExecuteScalar().ToString();
xmlDoc.LoadXml("<?xml version=\"1.0\" encoding=\"UTF-8\"?><menu>" + myXml.ToString() + "</menu>");
xmlDoc.Save(@"c:\inetpub\wwwroot\lab1\XML\menu.xml");
}
catch (Exception err)
{
throw new Exception(err.Message.ToString());
}
finally
{
conn.Close();
}
}

Re: Complicated SP

svetsarn — Mon, 21 Jul 2008 16:52:20 GMT

I think I got it right now. Now it's two other problems I have. How can I retorn the result from the spand how do I receive it in my dbclass so I can build anxml-file or what I want to do? I'm not sure this i correct beacuse I have not seen all file. It's correct as muck I can se in the output window.

CREATE PROC doXml
@xmlFile XML OUTPUT
as
select 1 as tag,
null as parent,
m.menuText AS [menuItem!1!title],
m.menuToolTip as [menuItem!1!tool],
m.menuPath as[menuItem!1!url],
null as [subMenuItem!2!title],
null as [subMenuItem!2!tool],
null as [subMenuItem!2!url]
FROM menu m
UNION ALLselect 2 as tag,
1 as parent,
null,
null,
null,
sm.subMenuText,
sm.subMenuToolTip,
sm.subMenuPath
from submenu sm
WHERE exists(select * from menu WHERE menuID = sm.parentID)
FOR XML EXPLICIT

Re: Complicated SP

svetsarn — Mon, 21 Jul 2008 09:49:58 GMT

Yes I konw, but I thought i should be easer to biuld the Sql query if I had two tables. It's the sql query I can't build. I have never used "FOR XML" before. I have red in Books Online but I don't understand how I should do:(

Re: Complicated SP

TATWORTH — Mon, 21 Jul 2008 09:42:49 GMT

You could just have a menu table like this:

Id - Identity integer primary key
ParentId - Nullable Int FK to menu table - null value indicates a parent record
Text
ToolTip
Path

Re: Complicated SP

svetsarn — Mon, 21 Jul 2008 09:29:34 GMT

Thank you for your answers.

I have rebuilded my db. No I have two tables menu and subMenu.

(menu)
menuID
menuText
menuToolTip
menuPath

(subMenu)
subMenuID
parentID (FK)
subMenuText
subMenuToolTip
subMenuPath

Can you help me build a Sql query so I can get it like this?