Using Wordpress/MySql as a CMS for a .NET Site

Posted January 30th, 2010 at 4:16 am by Matt Chepeleff

I’ve got a couple of projects I’m working on that are written in C#/.NET and each have a lot of static content. “Static” meaning about us, product description, and FAQ-ish content.  Stupidly, I’ve got these pages as hard-coded HTML for one site and database driven through a primitive “CMS” for the other (I use quotes because I merely put this database-driven content into a TinyMCE enabled field on a back-end site and let users have at formatting).

Obviously the hard-coded solution isn’t the way to go because every change requires a new build onto the server – which is overkill to say the least.  The quick-and-dirty WYSIWYG/database solution is only marginally better in my opinion (no saved versions and all the text is free form, so associating other fields like data posted, modified, or author requires incremental work).

While thinking up a better way to handle this content I realized both sites also have a Wordpress powered blog.  The business users are already used to Wordpress and it’s a great platform…plus it’s a more powerful CMS out of the box.  That got me thinking: Why not setup an additional Wordpress site at static.domain.com (or something) and let the business users handle the static content in this familiar environment.  This would enable the business users, make my life easier, and make the world a better place.

This approach does, in addition to a few other things, require the existing .NET sites to be able to talk to Wordpress’ database and grab the right content.  More specifically, here’s what needs to be done conceptually:

  1. Setup the new Wordpress environment for storage of the new “static” content
  2. Install the MySQL Connector/Net so .NET can talk to MySQL
  3. Write code in C# to pull content out of the MySQL database

Now the only other thing we need to figure out is how we’re going to grab an individual record from the wp_posts table.  We could query by ID, Title, Author, etc…  As I need to call content from the existing .NET sites, I want the code to be able to grab something that isn’t going to change – like IDs.  Now, you would be correct thinking ahead and wondering how Wordpress stores multiple versions of the same page or post.   That won’t pose issues, because the post_parent field always references the original post:

MySQL wp_posts Query

But I don’t want to use the ID because Wordpress users can’t modify it if something changes.  Fortunately, Wordpress allows a user to quickly add custom fields – so we’ll go this route because it gives the business users more control and they’ll like this (feel more important).

Before I get to the code, let’s quickly recap.  From .NET I will call some code that will lookup a post in the wp_posts table using a custom field value.  In Wordpress, all that a user needs to do is add this custom field – the code will look up the most recent revision and handle the rest.

Here’s a class I wrote in C#:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285

class WordPressPostInfo
{
    #region private variables
    string _title, _content, _name, _excerpt, _status, _password, _type, _mimetype, _posternicename, _posterdisplayname, _posteremail, _websiteloc;
    DateTime _posted, _postedgmt, _modified, _modifiedgmt;
    int _posterId;
    #endregion
 
    #region public attributes
    /// <summary>
    /// The Post Title
    /// </summary>
    public string Title
    {
        get { return _title; }
        set { _title = value; }
    }
    /// <summary>
    /// The Post Content
    /// </summary>
    public string Content
    {
        get { return _content; }
        set { _content = value; }
    }
    /// <summary>
    /// The Post Name
    /// </summary>
    public string Name
    {
        get { return _name; }
        set { _name = value; }
    }
    /// <summary>
    /// The Post Excerpt
    /// </summary>
    public string Excerpt
    {
        get { return _excerpt; }
        set { _excerpt = value; }
    }
    /// <summary>
    /// The Post Status
    /// </summary>
    public string Status
    {
        get { return _status; }
        set { _status = value; }
    }
    /// <summary>
    /// The Post Password
    /// </summary>
    public string Password
    {
        get { return _password; }
        set { _password = value; }
    }
    /// <summary>
    /// The Post Date/Time Originally Posted
    /// </summary>
    public DateTime Posted
    {
        get { return _posted; }
        set { _posted = value; }
    }
    /// <summary>
    /// The Post Date/Time Originally Posted (GMT)
    /// </summary>
    public DateTime PostedGMT
    {
        get { return _postedgmt; }
        set { _postedgmt = value; }
    }
    /// <summary>
    /// The Post Date/Time Modified
    /// </summary>
    public DateTime Modified
    {
        get { return _modified; }
        set { _modified = value; }
    }
    /// <summary>
    /// The Post Date/Time Modified (GMT)
    /// </summary>
    public DateTime ModifiedGMT
    {
        get { return _modifiedgmt; }
        set { _modifiedgmt = value; }
    }
    /// <summary>
    /// The Post MIME Type
    /// </summary>
    public string MimeType
    {
        get { return _mimetype; }
        set { _mimetype = value; }
    }
    /// <summary>
    /// The Post Publish Status
    /// </summary>
    public bool IsPublished
    {
        get
        {
            if (Status == "publish" || Status == "inherit")
                return true;
            else
                return false;
        }
    }
    /// <summary>
    /// The Post Type
    /// </summary>
    public PostType Type
    {
        get
        {
            if (_type == "post")
                return PostType.Post;
            else if (_type == "page")
                return PostType.Page;
            else if (_type == "revision")
                return PostType.Revision;
            else
                return PostType.Unknown;
        }
    }
    /// <summary>
    /// The Poster's Wordpress "Nice Name"
    /// </summary>
    public string PosterNiceName
    {
        get { return _posternicename; }
        set { _posternicename = value; }
    }
    /// <summary>
    /// The Poster's Wordpress "Display Name"
    /// </summary>
    public string PosterDisplayName
    {
        get { return _posterdisplayname; }
        set { _posterdisplayname = value; }
    }
    /// <summary>
    /// The Poster's Wordpress Email
    /// </summary>
    public string PosterEmail
    {
        get { return _posteremail; }
        set { _posteremail = value; }
    }
    /// <summary>
    /// The Website Location to display this post (set in Wordpress as custom field with name "websiteDisplayLocation")
    /// </summary>
    public string WebsiteLocation
    {
        get { return _websiteloc; }
        set { _websiteloc = value; }
    }
    #endregion
 
    #region constructors
    /// <summary>
    /// default constructor
    /// </summary>
    public WordPressPostInfo() { }
 
    /// <summary>
    /// construct by website location custom field in Wordpress
    /// </summary>
    /// <param name="WebsiteLocation"></param>
    public WordPressPostInfo(string WebsiteLocation)
    {
        GetSinglePost("wp_postmeta.meta_value", WebsiteLocation);
    }
 
    /// <summary>
    /// construct by post title
    /// </summary>
    /// <param name="PostTitle"></param>
    /// <param name="byTitle"></param>
    public WordPressPostInfo(string PostTitle, bool byTitle)
    {
        GetSinglePost("post_title", PostTitle);
    }
 
    /// <summary>
    /// private construct by MySqlDataReader
    /// </summary>
    /// <param name="reader"></param>
    private WordPressPostInfo(MySqlDataReader reader)
    {
        FillWordPressObj(reader);
    }
    #endregion
 
    #region methods
    /// <summary>
    /// Get a single post
    /// </summary>
    /// <param name="title"></param>
    protected void GetSinglePost(string byField, string byValue)
    {
        string strProvider = ConfigurationManager.AppSettings["mysqlConnectionString"].ToString();
        MySqlConnection mySql = new MySqlConnection(strProvider);
        mySql.Open();
 
        string query = string.Format("select wp_posts.*, wp_postmeta.meta_value as websiteLoc, wp_users.display_name, wp_users.user_nicename, wp_users.user_email from wp_posts left join wp_postmeta ON wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'websiteDisplayLocation' inner join wp_users on wp_posts.post_author = wp_users.ID where {0} = '{1}' AND post_status = 'publish' ORDER BY ID ASC LIMIT 1", byField, byValue);
        MySqlCommand mysqlCmd = new MySqlCommand(query, mySql);
        MySqlDataReader reader = mysqlCmd.ExecuteReader();
 
        while (reader.Read())
        {
            FillWordPressObj(reader);
        }
        reader.Close();
        reader.Dispose();
        mySql.Close();
        mySql.Dispose();
    }
 
    /// <summary>
    /// Get all the published posts and pages
    /// </summary>
    /// <param name="title"></param>
    public List<WordPressPostInfo> GetAllPosts()
    {
        string strProvider = ConfigurationManager.AppSettings["mysqlConnectionString"].ToString();
        MySqlConnection mySql = new MySqlConnection(strProvider);
        mySql.Open();
 
        string query = "select wp_posts.*, wp_postmeta.meta_value as websiteLoc, wp_users.display_name, wp_users.user_nicename, wp_users.user_email from wp_posts left join wp_postmeta ON wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'websiteDisplayLocation' inner join wp_users on wp_posts.post_author = wp_users.ID where post_status = 'publish'";
        MySqlCommand mysqlCmd = new MySqlCommand(query, mySql);
        MySqlDataReader reader = mysqlCmd.ExecuteReader();
 
        List<WordPressPostInfo> posts = new List<WordPressPostInfo>();
        while (reader.Read())
        {
            posts.Add(new WordPressPostInfo(reader));
        }
 
        reader.Close();
        mySql.Close();
        reader.Dispose();
        mySql.Dispose();
 
        return posts;
    }
 
    /// <summary>
    /// Fill the main WordpressPostInfo object with the attributes in the reader
    /// </summary>
    /// <param name="reader"></param>
    protected void FillWordPressObj(MySqlDataReader reader)
    {
        this.Title = reader.GetString(reader.GetOrdinal("post_title"));
        this.Content = reader.GetString(reader.GetOrdinal("post_content"));
        this.Name = reader.GetString(reader.GetOrdinal("post_name"));
        this.Excerpt = reader.GetString(reader.GetOrdinal("post_excerpt"));
        this.Status = reader.GetString(reader.GetOrdinal("post_status"));
        this.Password = reader.GetString(reader.GetOrdinal("post_password"));
        this.Posted = reader.GetDateTime(reader.GetOrdinal("post_date"));
        this.PostedGMT = reader.GetDateTime(reader.GetOrdinal("post_date_gmt"));
        this.Modified = reader.GetDateTime(reader.GetOrdinal("post_modified"));
        this.ModifiedGMT = reader.GetDateTime(reader.GetOrdinal("post_modified_gmt"));
        this._type = reader.GetString(reader.GetOrdinal("post_type"));
        this.MimeType = reader.GetString(reader.GetOrdinal("post_mime_type"));
        this._posterId = reader.GetInt32(reader.GetOrdinal("post_author"));
        this.WebsiteLocation = reader.IsDBNull(reader.GetOrdinal("websiteLoc")) ? "" : reader.GetString(reader.GetOrdinal("websiteLoc"));
        this.PosterNiceName = reader.GetString(reader.GetOrdinal("user_nicename"));
        this.PosterDisplayName = reader.GetString(reader.GetOrdinal("display_name"));
        this.PosterEmail = reader.GetString(reader.GetOrdinal("user_email"));
    }
    #endregion
 
    #region enums
    public enum PostType
    {
        Post = 1,
        Page = 2,
        Revision = 3,
        Unknown = 4
    }
    #endregion
}

After you copy over this class it will only take a line of code to get a post for display on your .aspx:

1

WordPressPostInfo post = new WordPressPostInfo("about_us");

Or if you needed to iterate through all of the posts you could use:

1
2
3
4
5
6

WordPressPostInfo wppi = new WordPressPostInfo();
List<WordPressPostInfo> posts = wppi.GetAllPosts();
foreach (WordPressPostInfo _post in posts)
{
     //do something with each post here
}

Each WordPressPostInfo object is going to contain all the fields you might want to display in the UI, so it should be easy from here.  While we do have to hardcode the call to the post on the .aspx – the source of the content can be changes and updated easily from Wordpress now!

A few notes to get this class working in your project:

  • Be sure to add a reference to MySql.Data to your project first, then add:
    using System.Configuration;
    using MySql.Data.MySqlClient;
    using MySql.Data.Types;
  • Add the assembly to your web.config under compilation > assemblies:
    <add assembly=”MySql.Data, Version=5.2.5.0, Culture=neutral, PublicKeyToken=C5687FC88969C44D”/>
  • Add the connection string App Setting your web.config too:
    <add key=”mysqlConnectionString” value=”Data Source=localhost;Database=your_databaseName;User ID=your_userId;Password=your_password;”/>
  • I’m using a custom field in Wordpress called websiteDisplayLocation so be sure you use this field name or change the code
  • I’m only returning published status records – change the SQL as needed if this doesn’t work for you…
  • The Type property can be used to differentiate posts and pages.  I think I’ll have the end users create posts, but you could use either.  Wordpress stores posts and pages in the same wp_posts table.

Other useful reference for this project:

Share and Enjoy:
  • Twitter
  • Facebook
  • Digg
  • StumbleUpon
  • Ping.fm
  • Suggest to Techmeme via Twitter
  • Yahoo! Buzz
  • Google Bookmarks
  • Live
  • RSS

Tags:

Leave a Comment