Asp.net Core: Create Sitemap dynamically with MySQL database [ Razor Page]

/ Updated : / 0 Comments

Asp.net Core Sitemap Generator: Here in this article we are going to learn about creating sitemap dynamically in the Asp.net Core application. Every website must have a sitemap, as it's a major part of the SEO world. These sitemaps are submitted to the search engine for better indexing of the website.
Well am not going deep about SEO, let head back to create a sitemap in the asp.net core application. 

What is a sitemap?

A sitemap is nothing but a simple XML formatted file, which has the blueprint of your website. It has all the URL and other optional details (image, last updated date) of your website, which helps Google to crawl your website easily.

Output:

Steps to create sitemap dynamically.

  1. Create a project with EntityframeworkCore database connectivity.
  2. Code to generate a sitemap XML string.
  3. Option to set a friendly URL.

Note: If you aware of EntityFrameworkCore db connectivity, then direct jump to Step 2.

#1 Create project EntityframeworkCore database connectivity.

In visual studio, first we select the Asp.net Core website application from the project template. Then we add a folder named Models, and inside that folder, we add a class file as BlogPost with the following properties. The BlogPost class represents our database table name with respective columns as their properties. 

Project Structure: 



Our BlogPost class file looks like as written below: 

public class BlogPost
{
	[Key]
	public int Id { get; set; }
	public string url { get; set; }
	public DateTime CreatedDate { get; set; }
	public DateTime ModifiedDate { get; set; }
	public string Content { get; set; }
} 

Note: We need to add a namespace System.ComponentModel.DataAnnotations.

As we are using MySQL database and Entityframeworkcore, so we need to install some NuGet packages which are listed below:

Pomelo.EntityFrameworkCore.MySql
Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.Tools

Once all the packages are installed, go to the appsettings.json file. Appsettings.json file is nothing but a simple application configuration JSON file used to store configuration settings such as database connection string etc. Here we add a connection string with the values to connect our local MySQL database. 

Appsettings.json file looks like as written below:

 {
  "ConnectionStrings": {
    "DefaultConnection": "server=localhost;port=3306;database=dbDemo;uid=root;password=secret"
  },
    "Logging": {
      "LogLevel": {
        "Default": "Information",
        "Microsoft": "Warning",
        "Microsoft.Hosting.Lifetime": "Information"
      }
    },
    "AllowedHosts": "*"
}

Next, we create a new folder named Data, and inside it add a class file name as ApplicationDbContext. In the ApplicationDbContext class file, we add properties as DbSet<entity> of our model class i.e (BlogPost), which is mapped by default to the database tables. 

Our ApplicationDbContext.cs file code look like as below:

public class ApplicationDbContext : DbContext
{
	public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options): base(options)
	{
	}

	public DbSet<BlogPost> BlogPosts { get; set; }
}

Now we are done with the model, Dbcontext, respective NuGet installation. The next step is to create a database and table if not exists using the below command in PMC i.e go to Tool -> Nuget Package Manager -> Package Manager Console.

 add-migration dbInitial

This will generate migration script.

update-database

It will create the database, table if not exists. Check your MySQL, add some dummy data into the table blogpost.

#2 Code to generate a sitemap XML string.

A sitemap is a standalone page, so we add a razor page and name it sitemap. On the razor page model, we inject ApplicationDbcontext as we have to work with the database. Below code will generate XML string and return as ContentResult having ContentType = "application/xml". 

Our page model code looks like as written below:

public class SitemapModel : PageModel
{
	public readonly ApplicationDbContext dbContext;
	public SitemapModel(ApplicationDbContext dbContext)
	{
		this.dbContext = dbContext;
	}
	public IActionResult OnGet()
	{
		var blogPost = dbContext.BlogPosts.ToList();
		StringBuilder sb = new StringBuilder();
		sb.Append("<?xml version='1.0' encoding='UTF-8' ?><urlset xmlns = 'http://www.sitemaps.org/schemas/sitemap/0.9'>");

		foreach (var item in blogPost)
		{
			string mDate = item.ModifiedDate.ToString("yyyy-MM-dd");
			sb.Append("<url><loc>" + item.url + "</loc><lastmod>" + mDate + "</lastmod><priority> 0.8</priority></url>");
		}           
		sb.Append("</urlset>");

		return new ContentResult
		{
			ContentType = "application/xml",
			Content = sb.ToString(),
			StatusCode = 200
		};
	}
}

Now visit page https://localhost:portno/sitemap it will give output something like as shown below.

OUTPUT:

#3 Option to set friendly URL.

In asp.net core we have the option to set a friendly URL. For example to display product page sitemap as product.xml or in our case instead of just sitemap we want sitemap.xml or post_sitemap.xml. By using AddPageRoute we configure a route to a page with a friendly URL. 

Code as written below:

public void ConfigureServices(IServiceCollection services)
{
	services.AddDbContext<ApplicationDbContext>(
	   option => option.UseMySql(Configuration.GetConnectionString("DefaultConnection")));
	
	services.AddRazorPages(options =>
	{
		options.Conventions.AddPageRoute("/Sitemap", "Sitemap.xml");
	});
}

Conclusion: Here in this article we successfully able to create dynamic sitemap, (xml file) based on our MySql database in Asp.net core application. Also we able to set custom page name i.e friendly url using Conventions.AddPageRoute. 

Other Resource:

Thank you for reading, pls keep visiting this blog and share this in your network. Also, I would love to hear your opinions down in the comments.

PS: If you found this content valuable and want to do a favor, then Buy me a coffee

Subscribe to our newsletter

Get the latest and greatest from Codepedia delivered straight to your inbox.


Post Comment

Your email address will not be published. Required fields are marked *

Comments