YAFLogo

filip_cmr
  • filip_cmr
  • 59% (Neutral)
  • YAF Lover Topic Starter
2006-02-28T23:38:00Z
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
2006-03-10T03:23:36Z
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
2006-03-23T13:09:48Z
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
  • filip_cmr
  • 59% (Neutral)
  • YAF Lover Topic Starter
2006-03-26T08:24:12Z
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
2006-03-28T18:07:32Z
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
  • filip_cmr
  • 59% (Neutral)
  • YAF Lover Topic Starter
2006-03-28T19:40:58Z
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
  • LaB
  • 63.8% (Friendly)
  • YAF Forumling
2006-12-06T03:01:20Z
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
  • ami8
  • 51.8% (Neutral)
  • YAF Forumling
2011-03-25T16:54:24Z
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
  • ami8
  • 51.8% (Neutral)
  • YAF Forumling
2011-03-29T16:48:15Z

thats my question.

how do i do this? where is the setting?

steve1986
2011-04-04T11:49:42Z
Use Sitemap Writer Pro  , really amazing!!! 😉
Users browsing this topic