Finding my way back to the Bread Crumbs Project

Due to a life changing experience that I ran into 2 weeks ago, I’ve been unable to continue on with my regular R&D work. I’ve got a small breather that might last the rest of the week and so figured I should try to finish up some of my projects: 1st off, I managed some work on the Bread Crumbs list control.

Here’s what the control looks right now:


Items support Hot-Tracking

Vista’s Windows Explorer style item selection

Sub-item selection

Sub-item Menu Support

Also added support for item editing:

Design

The controls design is pretty basic and the code not overly elegant at this stage.

Each row on the List Box is an instance of the BreadCrumbsItem class. The BreadCrumbsItem holds an array of strings with each element corresponding to  an individual column. The list box requires that you insert only items with equal number of columns. Otherwise an exception will be thrown.

I had trouble getting the scrollbars inherent in UserControl/ContainerControl to function properly. The OnScroll method wasn’t receiving the proper scroll value and was reverting to 0 all the time. So, I had to go with adding a vertical scroll bar manually. Anyway, check out the code (49.5 KB zipped folder).

My Favorite Database Wrapper for C#

As a guy who started programming back in the days of DAO, I have nothing but love for ADO.NET. But let’s face it, you can still end up writing pretty lousy code with it. Not everybody’s fully aware of the best uses of the ADO.NET. One of the most common problems I keep stumbling onto in other peoples code is the poor use of ADO.NET Connection Pooling feature.

Somewhere, down the line Microsoft probably started noticing that their cool new data access model wasn’t being fully utilized. The birth of the Microsoft Enterprise Library was probably due to this.

As cool as the Enterprise Library is, I’ve found it to be an over kill for most of my projects. In fact one of my favorite projects featured on CodeProject, was because Enterprise Library’s Offline Application Block was so clunky (and very limited).

Around the same time that I worked on on the SCOAB (Smart Client Offline Application Block), I also wrote a ADO.NET wrapper similar to the Enterprise Library. It pretty much follows the public interface of the Enterprise Library, but it’s a very tiny wrapper compared to the enormous DAAB (Data Access Application Block).

You can download the .cs file here: AbstractDatabase.zip (1.88 kb)

This contains a generic abstract base class called AbstractDatabase. You can extend this class to use it with any type of relational database type. If you take a look at my SQLite Membership Provider, you’ll find this class derived to be used with SQLite ADO.NET Wrapper. I have simply called that class Database, and it’s code looks something like this:

public class Database : AbstractDatabase<SQLiteConnection, SQLiteCommand, SQLiteDataAdapter>
{
    protected override string  GetConnectionString()
    {
        return string.Format(ConfigurationManager.ConnectionStrings["BlogEngine"].ConnectionString,
                            HttpContext.Current.Server.MapPath("~/App_Data"));
    }
}

As you see, the only member you need to override is the GetConnectionString() method. This brings up the question as to why I made this abstract. Why not read the connection string value from the web.config? Well, I’ve done this because I use this class on both web and desktop projects. And in some cases, especially with WinForms applications, the database is SQLite and resides in the application directory. When that happens, I can always hard code the database file name.

Here’s a example of the the Database class’s usage (taken from the SQLiteBlogProvier class).

using (Database db = new Database())
    using (DbTransaction txn = db.BeginTransaction())
    {
        // SQLite doesn't support TRUNCATE
        DbCommand cmd = db.GetSqlStringCommand("DELETE FROM be_Settings"); 
        db.ExecuteNonQuery(cmd, txn);

        cmd = db.GetSqlStringCommand(
                    "INSERT INTO be_Settings (SettingName, SettingValue) VALUES (@name, @value)");
        db.AddInParameter(cmd, "@name", DbType.String, "");
        db.AddInParameter(cmd, "@value", DbType.String, "");
        
        foreach (string key in settings.Keys)
        {
            cmd.Parameters[0].Value = key;
            cmd.Parameters[1].Value = settings[key];
            cmd.ExecuteNonQuery();
        }
        txn.Commit();
    }

A quick rundown of the public interface of AbstractDatabase class:

void AddInParameter(System.Data.Common.DbCommand, string, System.Data.DbType, object)
void AddInParameter(System.Data.Common.DbCommand, string, System.Data.DbType, int, object)
DbTransaction BeginTransaction()
void Dispose()
DataSet ExecuteDataSet(System.Data.Common.DbCommand)
int ExecuteNonQuery(System.Data.Common.DbCommand)
ExecuteNonQuery(System.Data.Common.DbCommand, System.Data.Common.DbTransaction)
DbReader ExecuteReader(System.Data.Common.DbCommand)
ExecuteReader(System.Data.Common.DbCommand, System.Data.CommandBehavior)
T ExecuteScalar<T>(System.Data.Common.DbCommand, T)
abstract string GetConnectionString()
DbCommand GetSqlStringCommand(string)
DbCommand GetSqlStringCommand(string, params object[])
DbCommand GetStoredProcedureCommand(string)
Connection { get; }

Update (June 20, 2008): I have updated this class with a minor bug fix and have added support for other parmeter types including Output and InputOuput parameters (Thanks Yordan).

Update (Oct 20, 2008): This wrapper has been updated and made a part of the Codoxide Common Library

Breadcrumbs User Control – Part 1

Over the past few months, I’ve been working on a pretty high-end VSTO application. There had been some really ground breaking concepts thrown into that project which unfortunately, cannot be posted here for obvious reasons. What I can do is discuss a little widget I’m hoping to build that would eventually find it’s way into that project.

Background

The application contains an Advanced Search feature where the user can build reusable search template (called a Saved Search) as well as a reusable results templates. A “Saved Search” is basically a collection of search criteria linked to a results template. The results template decides which fields from are displayed from the “results set”.

We have 2 wizard UIs for configuring Saved Searches and Result Templates. The first page for each of the  wizards are almost identical in the fact that they asked the user to

  1. Pick an Entity class and then,
  2. Type in the name for a new saved search/result template to create or pick from a list of existing templates.

Since both the UI requirements were similar (and much similar UIs were required on few other places), this was implemented as a User Control that looked like:

All the text labels on the control were configurable and each time the selection changed in the Entity class drop down, it would fire an even asking for an array of existing items. On the wizard page this control was placed, the event was handled and the array filled with either the list of Results templates or saved searches for that particular Entity class.

Limitations of the Existing UI

The above interface was good enough initially. But it became quite cumbersome as the number of Entity classes grew (and it could potentially grow up to hundreds) and as more and more templates/searches were being saved. The user  was forced to go through all the Entity classes one by one if he wished to see all the saved items. I later added a Recent Items list but, the problem still required a better solution.

Adopting Vista Breadcrumbs Concept

One of the coolest new features in Vista is the breadcrumbs bar in Windows Explorer. The ease of which this allows you to navigate through folders is simply amazing.

And that got me thinking; can I adopt this to improve the user experience in the above case?  Here are my initial designs:

You would notice the peculiar menu items at the bottom indicating a character range (e.g. A-N). Well, I hope to make this work like Outook’s Address book buttons; probably using the code found here.

In the coming weeks, I hope to get some code out based on this concept UI. Obviously, there’s a lot of work required for a clean implementation. I can in the meantime get a quick and dirty , proof of concept control done using a auto scrolling Flow Panel and an array of Toolbar Strips. Well, stay tuned…

BlogEngine.NET configuration for SQLite Providers

This is a quick rundown of the changes required for gettting BlogEngine.NET work with SQLite.

  1. Get log4net (http://logging.apache.org/log4net/) and SQLite ADO.NET Provider (http://sqlite.phxsoftware.com).
  2. Change “blogProvider” to SQLiteBlogProvider
    <BlogEngine>
    <blogProvider defaultProvider="SQLiteBlogProvider">
    <providers>
    <add name="SQLiteBlogProvider" type="BlogEngine.Core.Providers.SQLiteProvider.SQLiteBlogProvider" />
    </providers>
    </blogProvider>
    </BlogEngine>
    
  3. Set role and membership providers to SQLite. Remember that you should set the applicationName as soon as possible (trust me, I got burnt a few times by not doing so).
    <membership defaultProvider="SQLiteMembershipProvider">
    <providers>
    <clear/>
    <!-- <add name="XmlMembershipProvider" type="BlogEngine.Core.Providers.XmlMembershipProvider" description="XML membership provider" xmlFileName="~/App_Data/users.xml"/> -->
    <add name="SQLiteMembershipProvider"
    type="BlogEngine.Core.Providers.SQLiteProvider.SQLiteMembershipProvider"
    applicationName="/Codoxide" />
    </providers>
    </membership>
    <roleManager defaultProvider="SQLiteRoleProvider" enabled="true">
    <providers>
    <!-- <add name="XmlRoleProvider" type="BlogEngine.Core.Providers.XmlRoleProvider" description="XML role provider" xmlFileName="~/App_Data/roles.xml"/> -->
    <add name="SQLiteRoleProvider"
    type="BlogEngine.Core.Providers.SQLiteProvider.SQLiteRoleProvider"
    applicationName="/Codoxide" />
    </providers>
    </roleManager>
    
  4. Setup log4net appender of your choosing.

The above is obviously a very lazy piece of documentation. It’s only there to serve as a guide for you to understand the projects web.config file where you’d find the complete customization requried.

Changes affecting sql.config

I have used the sql.config file that was used by the SQLBlogProvider with few modifications. Firstly, the connectionString attribute  is set to “Data Source={0}\blogengine.db”. The SQLite ADO.net provider requries that the data source contains the full path to the SQLite database. Therefore, the above string is used as the pattern for a string.Format operation where {0} receives the absolute file system path to app_data folder.

You will also notice an additional tag for “BlogEngine_LocalPath”, which contains an absolute path to the SQLite db file. Now, unlike the default XML provider in BE.NET, I’ve been too lazy to include a default user account into the database. So, you’d have to use ASP.NET Web Site Administration Tool to create user accounts. When invoked through the Admin Tool it becomes impossible to use Server.MapPath as the current HTTP Context turns out to be null. The “BlogEngine_LocalPath” element is there to be used in this situation.

BlogEngine.NET 1.2 SQLite Edition v0.1

Click here to download the project (2.9 MB ZIP file).

Files that were harmed during the makings of BlogEngine.NET 1.2 SQLite Edition Big Grin:

Moved

  • Moved TinyMCE out of Admin folder (which has restircted accesses) to a new Common folder. This was done to fix the problem of simlies being hidden from non-member users.

Added/Deleted

  • None

Modified

  • Take a look at this comprehensive folder comparison report (2.8 MB compressed folder containing folder comparison report in HTML format).

The above files are hosted on my SkyDrive.

Next Up:

There are few configuration steps that you'd need to go through before using BE.NET with SQLite. Hopefully, I'd be able to explain them in my next post. I seem to have messed up the live preview for comments.Oops! I'd have to check that out now.

Synopsis: BlogEngine.NET 1.2 with SQLite – BlogEngine.Core

Now that my SQLite Provider implementations seem to be running smoothly enough, it's time I move towards releasing the code.  Before I do, I want to post all the changes that I have made (excluding those are specific to this website's design). To start off here are the changes affecting the BlogEngine.Core library project:

Added

  • AbstractDatabase.cs
    • Path: Providers/
    • Generic base class abstracting database specific implementations
  • Database.cs
    • Path: Providers/SQLiteProvider/
    • Extends AbstractDatabase to provide SQLite specific implementations
  • SQLiteBlogProvider.cs, SQLiteMembershipProvider.cs, SQLiteRoleProvider.cs
    • Path: Providers/SQLiteProvider/
    • Self explanatory

Removed or Modified

  • None.

There is also an additional reference to log4net, used by Membership and Role providers. Original code from Peter A. Bromberg used the Event Log for loggging errors which fails if you are running on a Shared Host machine. Log4Net on the other hand executes without a problem under medium trust scenarios. I have used the File Appender of log4net. You could also easily switch to using SQLite database itself for logging, if that's your preference.

Changes to BlogEngine.Web and the downloadable source code are comming up on the next post… 

SQLite Providers for BlogEngine.net 1.2

This is a test run for my SQLite providers for BlogEngine 1.2. This includes, in addition to a SQLite blog-provider, a port of the SQLite Membership and Role providers originally posted by Peter A. Bromberg. While it was quite possible for me to adopt the same code used by MSSqlBlogProvider class and directly integrate the SQLite Membership/Role provider classes, I chose to make some syntatic adjustments to make those classes fit into my own style of coding.

Anyways, this post will follow up with imported posts from my Blogger account. It will give me a chance to test out the code I've written, with some real data.

:fingers-crossed: Cool

Data Tier Generator

In our last episode, I was trying to come up with a CodeSmith template that could generate my Data Tiers. CodeSmith does come with a huge bundle of pre-written templates. But, I wanted to go ahead and write my own firstly because I wanted to explore CodeSmith’s capabilities. Secondly, I have my own style of writing entity classes and needless to say, the existing templates didn’t fit my mould.

Before I get started, there’s one thing I should note: If you’re reading a template written by someone else, trust me, you will find it extremely difficult to follow. Other than for the obvious reasons, this is partly due to the fact that its extremely difficult to get the formatting just right on both the generated code as well as the template code. The template writer would of course try to get the generated code as readable as possible, and break the formatting of the template while doing so. Getting back to the task at hand, you should know that I’m one of those people who just hate working with databases. To me, something about them feel very low tech. So this is in part, an attempt to escape the torture of creating tables, store procs and writing data plumbing code and the like.

Here are my end-goals:

  1. To come up with an easy to follow XML configuration format that would drive the code generation. The config file would be the only input an end user would need to code.
  2. Generate the SQL required for creating tables, indexes and relationships.
  3. Generate stored procedures that perform basic CRUD and retrieval operations
  4. Generate the C# entity classes along with the C# methods for data retrieval and manipulation.

The template I have so far created can be found here (11.65 kb). You may also want to check out the XML that would drive this template. I’ll get in to more details about each of these components in my future posts.

Data Tier Generator – Planning

Now, here's my Entity definition. This definition would vary from project to project and would be the only thing I'd have to change.

[code:xml]
<nTier projectPrefix="prj">
  <entity name="Tag" primaryKeys="id">
      <properties>
          <property name="id" sqlType="bigint" notnull="true" />
          <property name="text" sqlType="nvarchar(50)" notnull="true" />
      </properties>
      <indices>
          <index columns="text" />
      </indices>
  </entity>
  <entity name="Article" primaryKeys="id">
      <properties>
          <property name="id" sqlType="bigint" notnull="true" />
          <property name="title" sqlType="nvarchar(255)" notnull="true" />
          <property name="content" sqlType="xml" />
      </properties>
      <indices>
          <index columns="title" />
      </indices>
  </entity>
  <entity name="ArticleTag" primaryKeys="articleId, tagId">
      <properties>
          <property name="articleId" sqlType="bigint" notnull="true" />
          <property name="tagId" sqlType="bigint" notnull="true" />
      </properties>
  </entity>
</nTier>
[/code]

Now, here's how it's all structured:

  • Each entity tag will correspond to a database table and most of the time an C# class. There're exceptions such as the link-tables. I'll explain that when I get to one.
  • The projectPrefix attribute specifies the prefix that would be added to each table and stored procedure generated. For example, the entity Article would generate a table prj_Article
  • Each property tag inside properties tag will correspond to a table column and a public property in the generated class.
  • The primaryKeys attribute can be used to provide a comma seperated list of primary key columns
  • The indices tag is used to specify nonclustered indexes for the table. The columns attribute accepts a comma seperated list of columns