Query the MySQL / WordPress Database from an ASP.NET site

We run a community site on ASP.NET Webforms that also has a blog based on WordPress. For a couple of years now we have loaded the data from WordPress to the ASP.NET site via WordPress RSS Feeds but we have had issues with this for a long time.

Some days ago I decided to just query the MySQL database that WordPress uses directly from .NET instead of going via any WordPress API. The whole process was kind of straightforward.

Connection to MySQL from .Net

To just do a simple connection you just need the MySQL Nuget package then it works pretty much like any ADO.NET connection.

using (MySqlConnection myConnection = new MySqlConnection(ConfigurationManager.ConnectionStrings["mysql"].ToString())) {
     MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
     myConnection.Open();

     using (MySqlDataReader myReader = myCommand.ExecuteReader())                  {
      aListView.DataSource = myReader;
      aListView.DataBind();
   }
}

 Query the WordPress Database to get Posts by Category

I needed to get my posts by category and it was kind of simple.

SELECT DISTINCT posts.post_title, posts.post_content, posts.post_date, posts.post_name FROM wp_posts posts
 JOIN wp_term_relationships term_rel 
  ON posts.ID = term_rel.object_id
 JOIN wp_term_taxonomy term_tax 
  ON term_rel.term_taxonomy_id = term_tax.term_taxonomy_id
 JOIN wp_terms terms 
  ON term_tax.term_id = terms.term_id
WHERE taxonomy = 'category' 
 AND posts.post_type = 'post' 
 AND terms.slug != 'MyCategory' 
 AND posts.post_status = 'publish' 
ORDER BY posts.post_date desc LIMIT 15;

I also needed to get the “Attached Image” or “Featured Image” from WordPress.

SELECT DISTINCT posts.post_title, posts.post_content, posts.post_date, meta2.meta_value, posts.post_name 
FROM wp_posts posts
   JOIN wp_term_relationships term_rel 
     ON posts.ID = term_rel.object_id
   JOIN wp_term_taxonomy term_tax 
     ON term_rel.term_taxonomy_id = term_tax.term_taxonomy_id
   JOIN wp_terms terms 
     ON term_tax.term_id = terms.term_id
   JOIN wp_postmeta meta 
     ON (meta.post_id = posts.ID 
     AND meta.meta_value IS NOT NULL 
     AND meta.meta_key = '_thumbnail_id')
   JOIN wp_postmeta meta2 
     ON (meta2.post_id = meta.meta_value 
     AND meta2.meta_key = '_wp_attached_file' 
     AND meta2.meta_value IS NOT NULL)
WHERE taxonomy = 'category' 
 AND posts.post_type = 'post' 
 AND terms.slug = 'MyCategory' 
 AND posts.post_status = 'publish' 
ORDER BY posts.post_date desc
LIMIT 5";