Usually you'll use an HTTP Handler for this. Given a request for...
  http://www.yoursite.com/sitemap.axd
...your handler will respond with a formatted XML sitemap. Whether that sitemap is generated on the fly, from a database, or some other method is up to the HTTP Handler implementation.
Here's roughly what it would look like:
void IHttpHandler.ProcessRequest(HttpContext context)
{
    //
    // Important to return qualified XML (text/xml) for sitemaps
    //
    context.Response.ClearHeaders();
    context.Response.ClearContent();
    context.Response.ContentType = "text/xml";
    //
    // Create an XML writer
    //
    XmlTextWriter writer = new XmlTextWriter(context.Response.Output);
    writer.WriteStartDocument();
    writer.WriteStartElement("urlset", "http://www.sitemaps.org/schemas/sitemap/0.9");
    //
    // Now add entries for individual pages..
    //
    writer.WriteStartElement("url");
    writer.WriteElementString("loc", "http://www.codingthewheel.com");
    // use W3 date format..
    writer.WriteElementString("lastmod", postDate.ToString("yyyy-MM-dd"));
    writer.WriteElementString("changefreq", "daily");
    writer.WriteElementString("priority", "1.0");
    writer.WriteEndElement();
    //
    // Close everything out and go home.
    //
    result.WriteEndElement();
    result.WriteEndDocument();
    writer.Flush();
}
This code can be improved but that's the basic idea.