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

// January 30th, 2010 // No Comments » // Uncategorized

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:

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:

• Wordpress’ database schema

Re: 8 Things That Suck About the iPad

// January 27th, 2010 // No Comments » // Uncategorized

I can be a Mac fanboy, I admit it.  But even if I wasn’t, I’d like to think I would still be excited about the iPad.  Jokes around the name aside, it’s a pretty cool device.  I’ve wanted a tablet for years – but nothing on the market for the past 7 years has come close to being what I want.  Just look at the Lenovo X Series Tablet.  Really?  People bought that?Come on.

I couldn’t ever find a tablet that did what I wanted, so I often looked for substitutes.  Dell has come out with a couple sleek laptops that look neat (until you get to the price tag) but they’re all still laptops.  My iPod Touch has been my friend for a couple years now.  It’s really the closest I’ve come to getting what I want – but it’s so small it’s hard to think of it as a tablet.

Anyway, my tablet dream started to come true today.   I’ve been reading all the articles about the device…informational, critical or otherwise.  I like most of what I’m reading, even when the reviews aren’t too positive.  I just read a post on Gizmodo titled the “8 Things That Suck About the iPad” and I must say, most of these things don’t bother me.

Here’s what they complain about and why I don’t personally care:

• Big, Ugly Bezel
  Sure  a full width display would be nice (like the newest MacBook Pro) – but this just isn’t a deal breaker for me
• No Multitasking
  I think a software update will address this down the road.  But even for now, I’m okay with this.  I’m not going to be doing any design or development work on this thing – which is where multitasking is absolutely critical.  I just want to be able to get on the web, manage my email and calendar, and run a handful of other useful apps on a daily basis in a nice, notepad form factor.  That alone would make me happy.
• No Cameras
  Okay, honestly.  Who is going to stop to take a photo and pull out a 10″ device to do so.  Even SLRs are more compact that this thing (due to the 10″ size).  As long as it could display photos I take using another device nicely…I’m happy.
• Touch Keyboard
  I haven’t tried it yet, so I could be wrong…but it just doesn’t seem like a deal breaker to me.  Initially I didn’t love to iPod Touch keyboard.  I got used to that.  This seems like an improvement when I compare the two…
• No HDMI Out
  Maybe it’s just me, but I don’t see this thing as a super, catch-all, does-everything device.  I could watch movies on it and that’s nice…but if I really want to watch a movie on my TV I’ve got loads of other options.  I just don’t think a tablet PC would be my go to solution for HD movies on my HDTV.
• The Name iPad
  Personally, I think the jokes already running around are funny.  But I’d still want the device if you called it the iSuck.  The name just doesn’t matter to me if it does what I want it to do.  Silly point Gizmodo.
• No Flash
  This is the one thing that bothers me the most.  But it’s not a hardware issue.  It’s all software and licensing from my understanding.  Apple and Adobe just aren’t on the same page.  But if they get in line and offer flash support then I think it’ll come in the form of a software update – so I will remain hopeful and keep this (barely) off a deal breaker list.
• Adapters, Adapters, Adapters
  I’ll give them this one.  I think it will be annoying for sure.  But I don’t see this annoyance as a deal breaker.  I know there is going to be something I don’t love about the device.  If something like this is it, I’m okay with that.
• It’s Not Widescreen
  If I were purchasing this to watch movies I would be pissed off – and this could be a dealbreaker.  But I’m not, so once again, no big deal.  Perhaps a v2 or v3 will be widescreen as most displays are moving to widescreen aspect ratios…but a website, my email, or calendar will look just fine without.
• Doesn’t support T-Mobile 3G
  I don’t have AT&T and I hate it.   But Apple really saved themselves by allowing non-contract use of AT&T’s 3G.  That would have been a deal-breaker.
• Closed App Ecosystem
  As a developer I actually like the App-Store model.  Sure, it’s closed and Apple has more control than we might ideally like.  But I think without this type of closed ecosystem the entire model wouldn’t have gotten as big as it is.  It allowed Apple to keep quality and other bars set high and will probably continue to do so.

I guess in the end it’s all personal preference.  If the stuff Gizmodo points out really bothers you then perhaps the device isn’t for you.  I’m personally excited about the device.  I think it’s a big step forward in computing.  More importantly it’s going to revolutionize the currently craptastic tablet landscape (just like the iPhone did the smartphone market)…and that’s badly needed.

I’ll keep reading in the coming weeks and if I’m still happy when it’s out for sale I’ll report back on how my personal experience with it is going.