SiteMap for Google

Welcome Guest! To enable all features please Login or Register.

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

Wanna join the discussion?! Login to your YAF.NET Support Forum forum account or Register a new forum account

bigtoga
100% (Exalted)
YAF MVP

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
100% (Exalted)
YAF MVP

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
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
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
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";

Edited by user 2006-03-28T19:42:18Z | Reason: Not specified

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:

Edited by user 2006-12-06T03:16:33Z | Reason: Not specified

EfBforum.dk

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
51.8% (Neutral)
YAF Forumling

2011-03-29T16:48:15Z

thats my question.

how do i do this? where is the setting?

steve1986
50.6% (Neutral)
YAF Forumling

2011-04-04T11:49:42Z

#10

Use Sitemap Writer Pro , really amazing!!! 😉

Important Information:

The YAF.NET Support Forum uses cookies. By continuing to browse this site, you are agreeing to our use of cookies. More Details Close

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.

Project Twitter Updates

Tweets by YAFNET

Important Information:

About Us

Project Twitter Updates

Useful Links

Our Contacts