filip_cmr
  • Posts: 29
  • Joined: 12/02/2006
For who may be interested:
I make a handler to dynamically generate site map using Google specifications.
Here is the code:
x.ashx:
<%@ WebHandler Class="yaf.x" CodeBehind="x.cs" Language="C#" %>


and x.cs


using System;
using System.Web;

namespace yaf
{
    public class x : IHttpHandler
    {
        protected static string mySQLCommText="select 1 as Tag,"+
            " null as parent,"+
            " null as [url!1],"+
            " 'http://forum.top-solutions.ro/default.aspx?g=topic&f='+cast(ForumId as varchar)+ "+
            " '&sd='+Name+'&ld='+Description"+
            " as [url!1!loc!element],"+
            " cast(Year(LastPosted) as varchar)+'-' +"+
            " cast(Month(LastPosted) as varchar)+'-'+"+
            " cast(Day(LastPosted) as varchar)as [url!1!changefreq!element],"+
            " 'weekly' as [url!1!changefreq!element],"+
            " '0.5' as [url!1!priority!element]"+
            " from yaf_Forum "+

            " union all "+

            " select 1 as Tag,"+
            " null as parent,"+
            " null as [url!1],"+
            " 'http://forum.top-solutions.ro/default.aspx?g=post&t='+cast(TopicId as varchar)+ "+
            " '&sd='+Topic as [url!1!loc!element],"+
            " cast(Year(LastPosted) as varchar)+'-' +"+
            " cast(Month(LastPosted) as varchar)+'-'+"+
            " cast(Day(LastPosted) as varchar)as [url!1!changefreq!element],"+
            " 'daily' as [url!1!changefreq!element],"+
            " '0.9' as [url!1!priority!element]"+
            " from yaf_Topic"+

            " for xml explicit";
        public void ProcessRequest(HttpContext context)
        {
            System.Web.HttpResponse myResp = context.Response;
			
                                                myResp.Clear();
			myResp.ClearContent();
			myResp.ClearHeaders();

			myResp.ContentEncoding=System.Text.Encoding.UTF8;
			myResp.Buffer=true;
			myResp.Expires=-1;
			myResp.AddHeader("content-disposition", @"attachment; filename=SiteMap.xml" );
			myResp.ContentType="text/xml";

                    myResp.Write("<?xml version=\"1.0\" encoding=\"UTF-8\"?><urlset xmlns=\"http://www.google.com/schemas/sitemap/0.84\">");
                    using (System.Data.SqlClient.SqlConnection myConn = DB.GetConnection())
                    {
                        using (System.Xml.XmlReader myXR = new System.Data.SqlClient.SqlCommand(mySQLCommText, myConn).ExecuteXmlReader())
                        {
                            while (!myXR.EOF)
                                if (myXR.IsStartElement())
                                    myResp.Write(myXR.ReadOuterXml());
                            myXR.Close();
                        }
                        myConn.Close();
                    }
                    myResp.Write("</urlset>");
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }

    }
}


Of course, you could make a stored procedure instead of text command, and you must replace the query with your site address.
Sponsor
bigtoga
  • Posts: 263
  • Joined: 10/03/2006
Wow - that's quite generous of you. I haven't tried it but I will. Thank you very much for sharing your tweaks 🙂
Check out my implementation of YAF: http://forums.learnsqlserver.com/ .
I am also a SQL Server and .NET trainer over at http://www.learnitfirst.com 
bigtoga
  • Posts: 263
  • Joined: 10/03/2006
I get an error when I compile x.cs:

C# Compiler wrote:

Cannot implicitly convert type 'System.Xml.XmlReader' to 'System.IDisposable'



Line 54 on mine. Any ideas? I'm using ASP.NET 1.1 BTW
Check out my implementation of YAF: http://forums.learnsqlserver.com/ .
I am also a SQL Server and .NET trainer over at http://www.learnitfirst.com 
filip_cmr
  • Posts: 29
  • Joined: 12/02/2006
Hello,

Yes, my code use ASP NET 2.0 . Yau must declare
System.Xml.XmlReader myXR = new System.Data.SqlClient.SqlCommand(mySQLCommText, myConn).ExecuteXmlReader();
and not use "using" syntax witch is intended for classes witch implement IDisposable.

Hope this help.
PS. If you are using SQL 2005 i have a new select able to provide the page on the topics. Tell me and I will send you.
BR. Filip Camara.
guest
  • #5
  •  guest
  • Rank: Guest
  • Posts: 442
  • Joined: 18/03/2003
Thanks for the code but there is a problem, the way you are formatting the date/time is incorrect. Google will show an error for times such as "1:00" as they should be zero padded like "01:00".

Rather than build each part of the date time and concat them like you are doing, just use the T-SQL Convert function using 120 as the format. Here is my version of the SQL you provided:

string mySQLCommText="select 1 as Tag,"+
" null as parent,"+
" null as [url!1],"+
" 'http://www.yoursite.com/forums/default.aspx?g=topic&f='+cast(ForumId as varchar)+ "+
" '&sd='+Name+'&ld='+Description"+
" as [url!1!loc!element],"+
" convert(varchar(20), LastPosted, 120) as [url!1!changefreq!element],"+
" 'weekly' as [url!1!changefreq!element],"+
" '0.5' as [url!1!priority!element]"+
" from yaf_Forum "+

" union all "+

" select 1 as Tag,"+
" null as parent,"+
" null as [url!1],"+
" 'http://www.yoursite.com/forums/default.aspx?g=post&t='+cast(TopicId as varchar)+ "+
" '&sd='+Topic as [url!1!loc!element],"+
" convert(varchar(20), LastPosted, 120) as [url!1!changefreq!element],"+
" 'daily' as [url!1!changefreq!element],"+
" '0.9' as [url!1!priority!element]"+
" from yaf_Topic"+

" for xml explicit";
filip_cmr
  • Posts: 29
  • Joined: 12/02/2006
You are right, I think that was a not tested version . Now I am using this (only for sql 2005). It is working on google from some time with succes. (I prefer to format date my self )

"select 1 as Tag,"+
            " null as parent,"+
            " null as [url!1],"+
            " 'http://forum.top-solutions.ro/default.aspx?g=topics&f='+cast(ForumId as varchar)+ "+
            " '&sd='+Name+'&ld='+Description"+
            " as [url!1!loc!element],"+
            " cast(Year(LastPosted) as varchar)+'-' +"+
            " case len(cast(Month(LastPosted) as varchar)) "+
			"	when 1 then '0'+cast(Month(LastPosted) as varchar) "+
			"	else cast(Month(LastPosted) as varchar) "+
			" end "+
			" +'-'+ "+
			" case len(cast(Day(LastPosted) as varchar)) "+
			"	when 1 then '0'+cast(Day(LastPosted) as varchar) "+
			"	else cast(Day(LastPosted) as varchar) "+
			" end as [url!1!lastmod!element], "+
            " 'weekly' as [url!1!changefreq!element],"+
            " '0.5' as [url!1!priority!element]"+
            " from yaf_Forum "+

            " union all "+

            " select distinct 1 as Tag,"+
            " null as parent,"+
            " null as [url!1],"+
            " 'http://forum.top-solutions.ro/default.aspx?g=posts&t='+cast(a.TopicId as varchar)+ "+

			" '&p='+ " +
			" cast((( ROW_NUMBER() OVER ( partition by a.TopicID order by a.TopicID,b.MessageID) )/31) +1 as varchar) + " +

            " '&sd='+a.Topic as [url!1!loc!element],"+
            " cast(Year(a.LastPosted) as varchar)+'-' +"+
            " case len(cast(Month(a.LastPosted) as varchar)) " +
            "	when 1 then '0'+cast(Month(a.LastPosted) as varchar) " +
            "	else cast(Month(a.LastPosted) as varchar) " +
            " end " +
            " +'-'+ " +
            " case len(cast(Day(a.LastPosted) as varchar)) " +
            "	when 1 then '0'+cast(Day(a.LastPosted) as varchar) " +
            "	else cast(Day(a.LastPosted) as varchar) " +
            " end as [url!1!lastmod!element], " +
            " 'daily' as [url!1!changefreq!element],"+
            " '0.9' as [url!1!priority!element]"+
            " from yaf_Topic a"+
            " inner join yaf_Message b " +
			"       on a.TopicID=b.TopicID "+

            " for xml explicit";
LaB
  • Posts: 27
  • Joined: 08/08/2006
I realize this topic is ancient, but when I implement the handler, it results in the browser trying to download the xml file on every page request - what am I doing wrong?

my web.config implementation looks like this:

<httpHandlers>
	<add verb="*" path="*" 
        type="yaf.GoogleSiteMap" />
</httpHandlers>


well, don't I feel stupid - completely missed the path asterisk... :oops:
ami8
  • Posts: 3
  • Joined: 14/03/2011
hi all,

this thread is quite old. but i can't seem to find any documentation for enabling a google sitemap for the forum?

Anyone have any suggestions?

Is the code shown at the beginning of this thread still valid 5 years on?

Thanks
ami8
  • Posts: 3
  • Joined: 14/03/2011

thats my question.

how do i do this? where is the setting?
steve1986
  • Posts: 1
  • Joined: 04/04/2011
Use Sitemap Writer Pro  , really amazing!!! 😉
Users browsing this topic
    Forum Jump  
    • You cannot post new topics in this forum.
    • You cannot reply to topics in this forum.
    • You cannot delete your posts in this forum.
    • You cannot edit your posts in this forum.
    • You cannot create polls in this forum.
    • You cannot vote in polls in this forum.

    About Us

    The YAF.NET is an open source .NET forum project. YAF.NET is supported by an team of international developers who are build community by building community software.

    Powered by Resharper Donate with PayPal button

    Project Twitter Updates

    Copyright © YetAnotherForum.NET & Ingo Herbote. All rights reserved