Rick Strahl's FoxPro and Web Connection Web Log
White Papers | Products | Message Board | News |

Dropping in MetaWeblog Provider into my FoxPro Weblog


Monday, November 21, 2011, 11:53:00 AM

For a while I've been slacking on my FoxPro blogging. Part of the reason has been that I'm spoiled - spoiled by using Windows Live Writer on my main Weblog (where I post most of my .NET and general content). In case you're a blogger and you haven't used Live Writer, I urge you to RUN not walk over to the Windows Live site and download a copy. Seriously this will make your life much easier. With a couple of plug-ins - namely my SnagIt Screen Capture plug-in and the Paste from Visual Studio (which captures RTF colored text including with some limitations FoxPro code)

My main site's Weblog runs a custom-built ASP.NET application that built years ago and I built a MetawebLog API provider for it so it would work easily with Live Write (and other blogging tools). MetawebLog API is a standard format that can be used to post post information from the client to the server. The API is about 10 methods that implement things like retrieving blogs (for multi-blog hosts), retrieving recent posts, individual posts, deleting posts and of course allow you to post and update existing posts as well as handling binary/image uploads. One thing that's really painful about MetaWebLogAPI is that it's built using the Xml-RPC protocol which is an old and rarely used API. It would have been a lot easier if a generic SOAP or JSON API existed to do this. Alas, we're stuck with MetaWeblogAPI since that's what most editors suppport (as well as WordPress, Blogger and a few other specific formats).

Anyway, I wrote some .NET code a long while back to implement MetaWebLog API in my main blog and that's been working great. However, working on my FoxPro blog which is written with FoxPro code and Web Connection, I've been suffering from feature envy. For the Fox blog I used to use Word and then pasted content into a rich edit box - yuk! Later on I finally got wise and I used Live Writer to write my posts for my main blog, and post the FoxPro entries as Drafts. I'd then pick up the raw HTML and paste it into the FoxPro blogs entry form. Yeah, that sucked too.

So finally today I said - enough of this and sat down to create a provider to work against my Fox data in the FoxPro blog. Now I cheated a bit here - rather than using FoxPro and the Web Connection base code and reinvent XmlRpc and then build the MetaWeblog API ontop of it, I instead used my existing .NET MetawebLog base classes and recreated the final implementation talking to FoxPro data with OleDb. That's not something I like to do much - in fact I think this is the first ever application I've built for myself that uses OleDb against Fox data, but in the grand scheme of things this was simply the quickest way to go. It took me two hours to build the following implementation.

DotNet and FoxPro Data 

In the past I've gotten a lot of questions about how to access FoxPro data from .NET. Using FoxPro data from .NET is just about limited to using ADO.NET which in raw form is very verbose - much more so than FoxPro's concise Data Definition Language. A long while back I created a really basic Data Access Layer (DAL) that can be used with any SQL backend and it provides highlevel abstractions that make basic SQL commands single method calls which is a lot less verbose than low level ADO.NET code.

For the following code I use the low level SqlDataAccess helper class which is available as part of the West Wind Web Toolkit and the Westwind.Utilities .NET assembly. Using this class here's what a few data access commands look like:

SqlDataAccess Data = 
    new SqlDataAccess(ConfigurationManager.ConnectionStrings["WebLogFox"].ConnectionString,
                      "System.Data.OleDb");

DataTable table = Data.ExecuteTable("TRecentEntries",
            "select top 25 * from blog_entries order by entered desc");

// DataReaders are more efficient
DbDataReader reader = Data.ExecuteReader("select * from blog_entries where pk=?",
                                         Data.CreateParameter("?",pk) );

int maxPk = (int)Data.ExecuteScalar("select max(pk) from blog_Entries");

int affected = Data.ExecuteNonQuery("insert into blog_user (pk, username, password) values (?,?)",
            Data.CreateParameter("?","rick"),
            Data.CreateParameter("?","sekrit") );


As you can see, it's not too difficult to make database calls - it looks a lot like using SQL Passthrough in FoxPro code which is no accident. For down and dirty, quick data access this stuff is sweet and easy and I use it frequently for administrative task - like say posting blog entries to my site.

Without much further ado (lower case! And the pun is intended) here's the code for my FoxPro based MetaWeblog implementation which puts the DAL above to use:

    public class MetaWebLogApi : XmlRpcService, IMetaWeblog
    {
        private const string STR_RickStrahl = "Rick Strahl";
        private const string STR_WconnectweblogimageContent = "~/weblog/imageContent/";
        private const string STR_WebLogName = "Rick Strahl's FoxPro and Web Connection Weblog";

        private static string STR_WeblogBaseUrl =
                "http://" + HttpContext.Current.Request.ServerVariables["server_name"] +
                "/wconnect/weblog/";

        public SqlDataAccess Data = new SqlDataAccess(ConfigurationManager.ConnectionStrings["WebLogFox"].ConnectionString,
                                                      "System.Data.OleDb");


        /// <summary>
        /// Validates the user and throws exception on failure which will throw
        /// us out of any service method and return the error to the client.
        /// </summary>
        /// <param name="Username"></param>
        /// <param name="Password"></param>
        /// <returns></returns>
        private bool ValidateUser(string Username, string Password)
        {
            object val = Data.ExecuteScalar("select pk from weblogusersecurity where username==? and password=?",
                                            Data.CreateParameter("?", Username),
                                            Data.CreateParameter("?", Password));

            if (val == null)
                return false;

            return true;
        }

        #region IMetaWeblog Members


        public CategoryInfo[] getCategories(object blogid, string username, string password)
        {           
            this.ValidateUser(username, password);

            List<CategoryInfo> CategoryInfoList = new List<CategoryInfo>();
            CategoryInfo cat = new CategoryInfo();
            cat.categoryid = "1" ;
            cat.description = "FoxPro";
            cat.title = "FoxPro";            
            CategoryInfoList.Add(cat);
            
            return CategoryInfoList.ToArray();            
        }

        public Post getPost(string postid, string username, string password)
        {
            this.ValidateUser(username, password);
            
            int Pk = 0;
            if (!int.TryParse(postid,out Pk))
                throw new XmlRpcException("Invalid PostId  passed");


           DbDataReader reader = Data.ExecuteReader("select * from blog_entries where pk = " + Pk.ToString());
           if (reader == null || !reader.HasRows )
               throw new XmlRpcException("invalid Post - no matching post found for id: " + Pk.ToString());


           Post post = new Post();

           while (reader.Read())
           {
               post.title = (string)reader["Title"];
               post.description = (string)reader["body"];
               post.postid = (int)reader["Pk"];

               // *** Move to business object
               post.permalink = STR_WeblogBaseUrl + "ShowPost.blog?id=" + Pk.ToString();
               post.link = post.permalink;

               post.dateCreated = (DateTime)reader["Entered"];
               post.categories = reader["Categories"].ToString().Split(',');

               post.mt_keywords = "";
               post.mt_excerpt = (string)reader["abstract"];
           }

            return post;
        }

        public Post[] getRecentPosts(object blogid, string username, string password, int numberOfPosts)
        {
            this.ValidateUser(username, password);

            
            DbDataReader reader = Data.ExecuteReader("select TOP ? * from blog_Entries order by entered desc",
                                                     Data.CreateParameter("?",numberOfPosts));

            if (reader == null || !reader.HasRows)
                throw new XmlRpcException("Error retrieving posts: " + Data.ErrorMessage);
            
            List<Post> Posts = new List<Post>();

            while (reader.Read()) 
            {
                Post post = new Post();

                post.description = reader["body"].ToString();
                post.title = reader["title"].ToString();
                post.postid = (int)reader["pk"];

                // *** Move to business object
                post.permalink = STR_WeblogBaseUrl + "ShowPost.blog?id=" + reader["pk"].ToString();
                post.link = post.permalink;

                post.dateCreated = (DateTime) reader["Entered"];
                string[] Categories = reader["Categories"].ToString().Split(new char[1] {','},StringSplitOptions.RemoveEmptyEntries);
                post.categories = Categories;

                Posts.Add(post);
            }

            return Posts.ToArray();
        }

        public bool editPost(string postid, string username, string password, Post post, bool publish)
        {
            this.ValidateUser(username, password);

            int id = -1;
            int.TryParse(postid, out id);

            string sql =
            @"update blog_entries 
                set Title=?,
                body=?,
                abstract=?,
                active=?,
                categories=?,
                keywords=?
                where pk = ?";

            string abstr = post.mt_excerpt;
            if (string.IsNullOrEmpty(abstr))
                abstr = StringUtils.HtmlAbstract(post.description, 200);  
            string kwrds = post.mt_keywords;
            if (string.IsNullOrEmpty(kwrds))
                kwrds = string.Empty;
            string userid = post.userid;
            if (string.IsNullOrEmpty(userid))
                userid = STR_RickStrahl;
            string cats = "";
            if (post.categories != null || post.categories.Length > 0)
            {
                foreach (string cat in post.categories)
                    cats += cat + ",";
                cats.TrimEnd(',');
            }

            int res =
                Data.ExecuteNonQuery(sql,
                Data.CreateParameter("?", post.title),
                Data.CreateParameter("?", post.description),
                Data.CreateParameter("?", abstr),
                Data.CreateParameter("?", publish),
                Data.CreateParameter("?", cats),
                Data.CreateParameter("?", kwrds),
                Data.CreateParameter("?", id)
                );


            if (res == -1)
                throw new XmlRpcException("Error inserting data: " + Data.ErrorMessage);
            
            return true;
        }

        public string newPost(object blogid, string username, string password, Post post, bool publish)
        {
            this.ValidateUser(username, password);


            int id = (int)Data.ExecuteScalar("select max(pk) from blog_entries");
            id++;                        

            string sql =
@"insert into blog_entries (pk,blogPk,Title, body, abstract, entered, updated, author,active,categories,keywords,feedback,url,BodyMode) 
         values (?,0,?,?,?,?,?,?,?,?,?,0,'',0)";

            string abstr = post.mt_excerpt;
            if (string.IsNullOrEmpty(abstr))
                abstr = StringUtils.HtmlAbstract(post.description, 200);                
            string kwrds = post.mt_keywords;
            if (string.IsNullOrEmpty(kwrds))
                kwrds = string.Empty;
            string userid = post.userid;
            if (string.IsNullOrEmpty(userid))
                userid = STR_RickStrahl;
            string cats = "";
            if (post.categories != null || post.categories.Length > 0)
            {
                foreach (string cat in post.categories)
                    cats += cat + ",";
                cats.TrimEnd(',');
            }



            int res = 
                Data.ExecuteNonQuery(sql,
                    Data.CreateParameter("?",id),                    
                    Data.CreateParameter("?",post.title),
                    Data.CreateParameter("?",post.description),
                    Data.CreateParameter("?",abstr),
                    Data.CreateParameter("?",post.dateCreated < new DateTime(2010,1,1) ? DateTime.Now : post.dateCreated),
                    Data.CreateParameter("?", DateTime.Now),
                    Data.CreateParameter("?",userid),
                    Data.CreateParameter("?",publish),
                    Data.CreateParameter("?",cats),
                    Data.CreateParameter("?",kwrds)
                    );

            if (res == -1)
                throw new XmlRpcException("Error inserting data: " + Data.ErrorMessage);
                        
            
            return id.ToString();
        
       }


        public mediaObjectInfo newMediaObject(object blogid, string username, string password, mediaObject mediaobject)
        {
            this.ValidateUser(username,password);

            string relPath = STR_WconnectweblogimageContent + DateTime.Now.Year.ToString() + "/";

            string ImagePhysicalPath = HttpContext.Current.Server.MapPath(relPath);

            if (Directory.Exists(ImagePhysicalPath))
                Directory.CreateDirectory(ImagePhysicalPath);
                
            string ImageWebPath = WebUtils.ResolveServerUrl(relPath,false);

            if (mediaobject.bits != null)
            {
                MemoryStream ms = new MemoryStream(mediaobject.bits);
                Bitmap bitmap = new Bitmap(ms);

                ImagePhysicalPath = ImagePhysicalPath + mediaobject.name;
                string PathOnly = Path.GetDirectoryName(ImagePhysicalPath).Replace("/","\\");
                if (!Directory.Exists(PathOnly))
                    Directory.CreateDirectory(PathOnly);

                bitmap.Save(ImagePhysicalPath);
            }

            mediaObjectInfo mediaInfo = new mediaObjectInfo();
            mediaInfo.url = ImageWebPath + mediaobject.name;                        
            return mediaInfo;
        }

        public bool deletePost(string appKey, string postid, string username, string password, bool publish)
        {
            this.ValidateUser(username, password);

            int postpk = -1;
            if (!int.TryParse(postid, out postpk))
                throw new XmlRpcException("Invalid Pk passed");
            
            if (Data.ExecuteNonQuery("delete from entries where pk = ?",
                                 Data.CreateParameter("?", postpk)) > -1)
                return true;

            return false;            
        }

        public BlogInfo[] getUsersBlogs(string appKey, string username, string password)
        {
            if (!this.ValidateUser(username, password))
                return null;

            BlogInfo blog = new BlogInfo();
            blog.blogid = "0";            
            blog.blogName = STR_WebLogName;
            blog.url = STR_WeblogBaseUrl;
            
            return new BlogInfo[1] { blog };
        }

        public wp_author[] wp_getAuthors(object blogId, string username, string password)
        {
            this.ValidateUser(username, password);                

            wp_author author = new wp_author();
            author.display_name = STR_RickStrahl;
            author.user_email = "";
            author.user_login = "";
            author.meta_value = "";

            return new wp_author[1] { author };
        }
        #endregion
    }

This code is based on my original post which includes the XmlRpc class and IMetaWeblogApi implementation. The nice thing about this is that the process of converting this code to run with FoxPro data was pretty easy. The ASP.NET blog uses more sophisticated business objects since the entire app is running in .NET. The FoxPro app also uses business objects running against FoxPro data, but those business objects are not easily accessible to .NET. I wanted to avoid COM Interop at all costs here so I chose to go the direct SQL route.

I'm posting this here mainly because I've seen a lot of questions FoxPro about data choices. Here's an example that shows how to use a light abstraction layer around the ADO.NET API which goes a long way towards making it pretty easy to access FoxPro data with minimal fuss. You can use my data access layer (it's free for personal use and testing, and cheap for commercial use) or you can build something similar on your own.

And hey -  if you've read this far you can see the fruits of this labor in code above: This post was posted to the blog using the metaweblog api client and Windows Live Writer. Feeling a little déjà vu, when viewing this screen shot? :-)

LiveWriterEditing[10]

Resources

Posted in: FoxPro    .NET

 



© Rick Strahl, West Wind Technologies, 2003 - 2018