Getting started with SQLite and .NET

February 16, 2010

SQLite is a “self-contained, serverless, zero-configuration, transactional SQL database engine.” It is utilized in a few systems you may have heard of: Firefox, Skype, iPhone, and Andriod to name a few. The entire database is stored in a single file, which means your application or website can utilize a fully functional relational database without the constraints or costs of a traditional database server.

So when you don’t want to shell out the cash for SQL Server, your host doesn’t offer MySQL, and you’re tired of dancing around XML files as a database alternative, you can turn to SQLite!

System.Data.SQLite

You can install and use the core SQLite library on the official download page, but as a .NET developer your best bet is go with System.Data.SQLite – an ADO.NET provider for the SQLite engine. It also includes design-time support in Visual Studio 2005/2008!

I’m going to demonstrate the full process of setup using a simple C# .NET console application:

Install System.Data.SQLite from sqlite.phxsoftware.com

Include Visual Studio support for the version(s) desired.

sqlite ado.net provider installer sqlite designer installation

Create a new application, reference System.Data.SQLite

I’ve created a simple console application.

add reference to system.data.sqlite System.Data.SQLite referenced

Create a new database

Go to the Server Explorer and add a new Connection. Select SQLite as the data source, and on the following screen click “New…” to specify the name and location of the database you wish to create. Click OK and you’ve just established your connection!

Note: there is no specific naming convention for the file extension, and in this case I did not provide a file extension at all. Using something like .sqlite, .db, or .sdb all make sense to me. SQLite Administrator (a freeware GUI tool) seems to expect .sdb as the default file extension, whereas SQLite Manager (a Firefox addon) looks for .sqlite files by default.

SQLite change data source SQLite add connection

Right click on the new connection and view properties to examine the connection string. In my case it is: data source="C:\path\to\my\project\SQLiteFun\SQLiteDb"

Add a new table

Expand the database connection and right-click “Add New Table.” The table designer is still in development, so it’s important to note that you don’t have to use Visual Studio as the GUI tool for the database. There are a number of 3rd party tools you can use, and I’ll mention a couple below.

SQLite add new table SQLite add new table pt2

I’ve created a table named “Colors” and filled it with some data just for this demonstration.

SQLite table retrieve data

Write some traditional ADO.NET code

The System.Data.SQLite namespace contains all the classes used below (SQLiteConnection, SQLiteCommand, SQLiteDataReader). Here I’m using the typical ADO.NET way of creating a connection, a command, and executing a datareader. The name and hex database fields are outputted to the console one record at a time.

static void Main(string[] args)
{
    string connStr = @"data source=""C:\path-to-my-project\SQLiteFun\SQLiteDb""";
    string sql = "select * from Colors";

    using (SQLiteConnection conn = new SQLiteConnection(connStr))
    {
        SQLiteCommand cmd = new SQLiteCommand(sql, conn);
        SQLiteDataReader rdr = null;
        conn.Open();
        try
        {
            rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            while (rdr.Read())
            {
                Console.WriteLine(rdr["name"] + " " + rdr["hex"]);
            }
        }
        finally
        {
            rdr.Close();
        }
    }

    Console.Read();
}

And the output:

program output

That’s it!

That’s all there is to get started with SQLite in .NET using System.Data.SQLite!

GUI Tools

There are a number of third party GUI tools to interact with your SQLite database (thus, installing and using Visual Studio design-time support is purely optional). Below are examples of two of them in use.

SQlite Administrator

SQLite Administrator is a great little freeware tool supporting multiple languages and many features.

SQLite Administrator GUI

Firefox plugin

SQLite Manager is a Firefox addon (or see project hosted on google code: http://code.google.com/p/sqlite-manager/)

SQLite Manager GUI

Next up: SQLite with ASP.NET

In the beginning of this article I alluded to the fact that you may choose to use SQLite as a free database alternative to a paid SQL Server (or other) host, but I’ve completely ignored ASP.NET in this article.

I’m going to tinker with SQLite some more, using it with ASP.NET both locally and on a shared hosting provider. The results I will leave for a follow-up article.

Visual Studio External Tools for Git and more

February 13, 2010

I admit it. For an application I've had open for literally over 10,000 total hours, there's stuff about Visual Studio that I have no clue about. None. It's a fun little revelation each time I inadvertently learn something new or useful. This post is about the most recent such time, when I got to learn about and employ some custom External Tools coupled with a customized Toolbar.

Rob Conery got to do the honors, via the excellent but somehow incomplete Mastering Git screen cast on TekPub. Upon doing his best to convince us that command line tools aren't that scary, Rob showed off how you can create your own external tools to launch programs/commands right from within Visual Studio 2008. Yes, my friend, you can fully integrate Git into Visual Studio on your own, going from this:

visual studio and git

to this:

visual studio git integration!

But seriously, the ability to add your own External Tools to Visual Studio is pretty cool, so lets take a look at how it's done, whether you want to open up a Git bash or, for example, Windows Explorer.

Note: everything below was done on Windows 7 x64. If you’re on a 32-bit system, your path to various commands will be different.

External Tools

Go to Tools > External Tools...

visual studio external tools visual studio tools menu

You'll probably have a couple listed by default: Dotfuscator and a Create GUID, for example. Notice that these are propagated to the actual Tools menu, in the order specified (second screen-shot above).

Clicking Add, you can create a new tool by specifying a title, command, arguments, initial directory and some other options. Some explanations from the online help:

  • Command: Enter the path to the file you intend to launch, or choose Browse to navigate to the file. Files types that you can launch include .exe, .bat, .com, .cmd, and .pif. If the file resides on the system path, you can enter just the file name.
  • Arguments: Specifies the variables that are passed to the tool when launched.
  • Initial directory: Specifies the working directory of the tool.

Since you may not be familiar with Git, let's start with a simple tool anyone may find useful. It's going to open Windows Explorer at the location of the currently opened file (thanks to this .NET Tip of The Day). The argument, if you can’t see in the image is: /select,"$(ItemPath)"

external tools editor

I'm going to hit OK and move this command to the first in the list and now we see it in the Tools menu

Explorer tool in tools menu

Select this menu item and up pops an Explorer window at the location of the current file (or solution directory)! Not impressed? This is arguably useless since there is similar functionality if you right click on a folder in your solution and select "Open Folder in Windows Explorer". Fair enough. I, however, do this right-click-selecting from the context menu all the time and it really does take a bit more effort than I would like, so let’s go with our new Explorer tool and improve the situation just a bit.

Custom Toolbars

Right click on any empty space in your toolbar area and go to "Customize..." and then click "New..." and give it a title. This will create and popup an empty floating toolbar that you can then drag a bunch of commands to.

Customize toolbars

Click on the Commands tab, select the Tools category, and then drag over "External Command 1" to your toolbar, and voila! There are 24 available custom commands each corresponding to the order number of your defined external tools. Above we moved the “Explorer” tool to the top of the list, so it will represent “External Command 1”. Once you close out the customize tools window, your toolbar will correctly show the “Explorer” tool.

adding commands to toolbar

Select a place to dock the toolbar, and now we are only 1 click away from opening Explorer at the location of the current file. I'd say that's pretty useful, if you ask me.

Lets get on to Git

Git isn't quite the most friendly tool for GUI driven Windows folks, although there is a set of Git Extensions aimed at making it more intuitive, including a Visual Studio plug-in.

But even if you are comfortable with the command line interface, you can employ some very helpful short-cuts by creating custom External Tools that perform or launch common Git actions. Take for instance, a tool that opens a Git bash at the solution folder (assumed to be the repository location) in one click:

git console external tool

custom git toolbar

Create a “Git Console” tool as such (and integrate it into a toolbar) and rather than having to open the Git bash by leaving Visual Studio and opening it from a folder or manually navigating to your solution directory, and you have 1-click access to Git right within Visual Studio!

Rob enumerates a handful of tools for the most common functionality such as initializing a new repository, adding all, committing, opening up Gitk and more. The full list of tools and their respective command, arguments and properties is below:

Git Console
Command: C:\Windows\SysWOW64\cmd.exe
Arguments: /c ""C:\Program Files (x86)\Git\bin\sh.exe" --login -i"
Initial Dir: $(SolutionDir)
Additional: Close on exit
Git Gui
Command: c:\Program Files (x86)\Git\bin\git.exe
Arguments: gui
Initial Dir: $(SolutionDir)
Additional: Close on exit
Git Init
Command: C:\Program Files (x86)\Git\bin\git.exe
Arguments: init
Initial Dir: $(SolutionDir)
Additional: Use Output window, Close on exit
Ignore (opens a new .gitignore file)
Command: C:\Windows\System32\notepad.exe
Arguments: ".gitignore"
Initial Dir: $(SolutionDir)
Additional: Close on exit
Add All
Command: c:\Program Files (x86)\git\bin\git.exe
Arguments: add .
Initial Dir: $(SolutionDir)
Additional: Use Output window, Close on exit
Git Commit
Command: c:\Program Files (x86)\git\bin\git.exe
Arguments: commit -am *MESSAGE*
Initial Dir: $(SolutionDir)
Additional: Use Output window, Close on exit
Git Status
Command: c:\Program Files (x86)\git\bin\git.exe
Arguments: status
Initial Dir: $(SolutionDir)
Additional: Use Output window, Close on exit
Gitk
Command: c:\Program Files (x86)\git\cmd\gitk.cmd
Arguments: --all
Initial Dir: $(SolutionDir)
Additional: Close on exit

Please check out Rob’s Mastering Git screen cast for further details and information. After going through the series I really do plan to test my will and use the console exclusively, and with a little help from these external tools, it shouldn’t be too hard indeed.

Using C# .NET 4.0 named arguments and optional parameters

February 6, 2010

The new C# 4.0 features, named arguments and optional parameters, provide a great amount of flexibility and readability in how methods are defined and called. This article demonstrates specifically how named arguments and optional parameters (parameters with default values) help us clean up and improve overloaded methods with a lot of parameters of the same type.

First, an example

Below is a somewhat contrived and exaggerated example, but consider a helper class that allows us to send an email by calling a SendMail method with a number of overloads to give callers the most flexibility possible in determining which parameters to utilize:

public class MailHelper
{
    public static void SendMail(string subject, string body)
    {
        SendMail(subject, body, true /*default isHtml = true*/);
    }
    public static void SendMail(string subject, string body, bool isHtml)
    {
        SendMail(subject, body, null, null, isHtml);
    }
    public static void SendMail(string subject, string body, string from, string to)
    {
        SendMail(subject, body, from, to, true /*default isHtml = true*/);
    }
    public static void SendMail(string subject, string body, string from, string to, bool isHtml)
    {
        SendMail(subject, body, from, to, null, null, isHtml);
    }
    public static void SendMail(string subject, string body, string from, string to, string cc, string bcc)
    {
        SendMail(subject, body, from, to, cc, bcc, true /*default isHtml = true*/);
    }
    public static void SendMail(string subject, string body, string from, string to, string cc, string bcc, bool isHtml)
    {
        if (string.IsNullOrEmpty(to))
            to = "defaultTo@email.com";

        if (string.IsNullOrEmpty(from))
            from = "defaultFrom@email.com";

        //create and send email here...
    }
}

This is certainly a little ugly, but a paradigm used all over the .NET framework and other API methods. The effort here pays off a little for callers of this method. There’s only one method and Intellisense gives a great overview of the options provided:

method overloading intellisense

The real problem rears it’s ugly head when the arguments passed into the method aren’t clear what they represent. Take for example, the six different ways this method might be called:

string mailSubject = "Check out .NET 4";
string mailBody = "body...";

MailHelper.SendMail(mailSubject, mailBody);
MailHelper.SendMail(mailSubject, mailBody, true);
MailHelper.SendMail(mailSubject, mailBody, "a@b.com", "b@a.com"); 
MailHelper.SendMail(mailSubject, mailBody, "a@b.com", "b@a.com", false); 
MailHelper.SendMail(mailSubject, mailBody, "a@b.com", "b@a.com", "c@d.com", "d@c.com"); 
MailHelper.SendMail(mailSubject, mailBody, "a@b.com", "b@a.com", "c@d.com", "d@c.com", false);

Again, this is exaggerated, but demonstrates a real problem. What does true mean on line 5? Does the third argument in line 5 correspond to the same parameter as the seventh argument in line 9, or do these booleans represent different things? Which of these email addresses represent To, From, CC and BCC?

Using named arguments to improve clarity, flexibility

With C#/.NET 4.0, we can use named arguments to make the intent of the previous code much more clear. The convention is to specify the parameter name with a colon prior to the argument, such as [parametername]: [argument]. Let’s take the second call to SendMail from above and make it clear what true represents:

MailHelper.SendMail(mailSubject, mailBody, isHtml: true);

It’s that easy, and doesn’t require the method definition to be altered at all. Now it’s abundantly clear what we’re doing: Sending an email with a given subject and body that is formatted in html.

The only rule here is that named arguments must appear after all fixed (normal) arguments. Named arguments themselves can be specified in any order, however!

MailHelper.SendMail(subject: mailSubject, mailBody); //wont compile
//error: Named argument specifications must appear after all fixed arguments have been specified.

MailHelper.SendMail(mailSubject, body: mailBody); //valid
MailHelper.SendMail(subject: mailSubject, body: mailBody); //valid 
MailHelper.SendMail(body: mailBody, subject: mailSubject); //also valid!

As a final example, let’s take the previous SendMail method call that specified all 7 possible arguments, and use named arguments to make it abundantly clear what each argument value represents:

//unclear
MailHelper.SendMail(mailSubject, mailBody, "a@b.com", "b@a.com", "c@d.com", "d@c.com", false); 

//clear
MailHelper.SendMail(subject: mailSubject,
                    body: mailBody,
                    from: "a@b.com",
                    to: "b@a.com",
                    cc: "c@d.com",
                    bcc: "d@c.com",
                    isHtml: false);

Using optional parameters with default values to reduce the amount of method overloading

While named arguments gave us some improvements to the way a method can be called, optional (or default) parameters give us a huge benefit in how a method is declared and implemented. In the case of method overloading, it gives us the chance to reduce the amount of method signatures greatly, as well.

Consider the first two SendMail methods. The second requires a subject, body, and boolean for isHtml, while the first method only requires a subject and body, calling the second with isHtml as true automatically. This is how  you would produce the effect of having a “singular” method with an “optional” parameter, isHtml, in previous versions of C#. With optional parameters and default vallues in C# 4.0, it’s entirely unnecessary:

//no longer needed
//public static void SendMail(string subject, string body)
//{
//    SendMail(subject, body, true /*default isHtml = true*/);
//}
public static void SendMail(string subject, string body, bool isHtml = true)
{
    SendMail(subject, body, null, null, isHtml);
}


//both callers work as before
MailHelper.SendMail(mailSubject, mailBody);
MailHelper.SendMail(mailSubject, mailBody, true);

optional parameters intellisense

So, just by turning isHtml into an optional parameter, we can eliminate three of our overloaded methods without requiring any change to the method signatures or callers.

//new and improved
public class MailHelper
{
    public static void SendMail(string subject, string body, bool isHtml = true)
    {
        SendMail(subject, body, null, null, isHtml);
    }
    public static void SendMail(string subject, string body, string from, string to, bool isHtml = true)
    {
        SendMail(subject, body, from, to, null, null, isHtml);
    }
    public static void SendMail(string subject, string body, string from, string to, string cc, string bcc, bool isHtml = true)
    {
        if (string.IsNullOrEmpty(to))
            to = "defaultTo@email.com";

        if (string.IsNullOrEmpty(from))
            from = "defaultFrom@email.com";

        //create and send email here...      
    }
}

Two steps forward, one step back

You’d be right to notice that isHtml isn’t the only parameter which could be made optional – all of them could. Unfortunately, we do run into some complications the further we optionalize these parameters. This is best shown by example, so let’s make the cc and bcc parameters optional by specifying null as their default value:

//only showing the 3 method signatures
public static void SendMail(string subject, string body, bool isHtml = true)
public static void SendMail(string subject, string body, string from, string to, bool isHtml = true)
public static void SendMail(string subject, string body, string from, string to, string cc = null, string bcc = null, bool isHtml = true)

This compiles, but since the last two methods have the same required parameters, it would make the following call ambiguous between the two of them:

//no longer valid
MailHelper.SendMail(mailSubject, mailBody, "a@b.com", "b@a.com");

This is only a problem for this particular implementation, but its necessary to show that if you intend to refactor an existing group of overloaded methods in this way, there will be a time when you have to either stop, or reduce the number of possible method signatures that callers may use, if the callers aren’t using named arguments. If they are, then this isn’t a problem at all!

Putting it all together

Let’s go all the way and make everything but the subject and body optional:

public class MailHelper
{
    public static void SendMail(string subject, string body, string from = "defaultFrom@email.com", string to = "defaultTo@email.com", string cc = null, string bcc = null, bool isHtml = true)
    {
        //create and send email here...    
    }
}

optional parameters default values intellisense

Now take a look at just some of the possible ways the SendMail method can be called:

MailHelper.SendMail(mailSubject, mailBody, isHtml: true);

MailHelper.SendMail(mailSubject, mailBody, cc: "c@d.com", bcc: "d@c.com");

MailHelper.SendMail(mailSubject, mailBody, to: "a@b.com", cc: "c@d.com", isHtml: false);

MailHelper.SendMail(subject: mailSubject,
                   body: mailBody,
                   from: "a@b.com",
                   to: "b@a.com",
                   cc: "c@d.com",
                   bcc: "d@c.com",
                   isHtml: false);

So that’s named arguments and optional parameters, just one of the cool new features of C# / .NET 4.0.  Optional parameters helped us take a class with six overloaded methods in 30 lines of code, down to a single method in 4 lines of code, and named arguments allowed us to call these methods with improved clarity and flexibility.

Creating a widget for BlogEngine.NET 1.5

February 1, 2010

Note: this also applies to BlogEngine.NET 1.6
New widget zones have been added, but widget architecture remains the same.

BlogEngine.NET has a nice and simple plug-in model for “widgets” (those areas you see to the right of this blog) and this article will show you how simple it is to create your own with version 1.5.

If you open up the source code you can see the widget architecture is based on the following conventions: widgets must be contained in a folder of the same name within the widgets folder, and consist of a (required) widget.ascx user control and an (optional) edit.ascx user control. All of the built-in and custom widgets reside here.

widget folder structure

A simple widget

Create a folder named “TestWidget” and add to it a user control named “widget”. In the code-behind, change the control to inherit WidgetBase instead of System.Web.UI.UserControl. Provide an implementation for the 3 abstract WidgetBase members: Name, IsEditable, and LoadWidget().

Some notes about the these WidgetBase members:

  • Name: This must be the same as the folder name the widget resides in, in this case, “TestWidget”.
  • IsEditable: This tells BE.NET whether to serve up a custom edit control for administering the widget. Set to false for now.
  • LoadWidget(): This is the method that fires when the widget is loaded and should be the entry point for the widget’s logic.

A simple “Hello World” example is below. Not shown is a Label control added to the .ascx page titled “lblTest”.

public partial class widgets_TestWidget_widget : WidgetBase
{
    public override string Name
    {
        get { return "TestWidget"; }
    }

    public override bool IsEditable
    {
        get { return false; }
    }

    public override void LoadWidget()
    {
        lblTest.Text = "Hello World!";
    }
}

test widget

Note: Even without providing any edit functionality, BE.NET allows you to edit (or hide) the title of the widget within the application. In this case, we might want to add a space in the title.

A slightly more complex widget

Any normal ASP.NET server control can be used within a widget, even those with their own events such as a GridView or Repeater. Please note that when attempting to use a button or any other control that causes a postback, it “worked,” but caused a script error (in IE7 at least: Line 6, Char 3314, 'BlogEngine' is undefined) for me. For that reason, I’d suggest you avoid any use of postbacks. A widget control really shouldn’t be interactive through postbacks anyway.

Here’s an example of a slightly more complex widget that uses a Repeater control, an OnItemCreated event, and a custom data class:

Markup:

<asp:Repeater ID="rTest" runat="server" 
    onitemcreated="rTest_ItemCreated" >
    <ItemTemplate>
        <%#Eval("Name") %><br />
    </ItemTemplate>
</asp:Repeater>

Codebehind:

public override void LoadWidget()
{
    List<TestClass> collection = new List<TestClass>();
    collection.Add(new TestClass("item1"));
    collection.Add(new TestClass("item2"));
    collection.Add(new TestClass("item3"));

    rTest.DataSource = collection;
    rTest.DataBind();
}

protected void rTest_ItemCreated(object sender, RepeaterItemEventArgs e)
{
    if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
    {
        ((TestClass)e.Item.DataItem).Name = ((TestClass)e.Item.DataItem).Name + " - DataBound!";
    }
}

public class TestClass
{
    public TestClass(string name){
        _Name = name;
    }

    private string _Name;
    public string Name { get { return _Name; } set { _Name = value; } }
}

The Result:

test widget with repeater

Defining and editing custom properties

To give blog administrators the ability to edit custom defined widget properties, a user control named edit must be added to the widget’s folder. This control must inherit the WidgetEditBase class and implement a single method, Save().

Each widget can use a StringDictionary to get and save settings pertaining to it. The WidgetEditBase class exposes a GetSettings() and SaveSettings() method to do so. Please note that SaveSettings() must be called after you change any settings, or else they will not be committed. The following is a simple example demonstrating this:

Markup:

A Text Field: <asp:TextBox ID="txtTest" runat="server"></asp:TextBox>
<br />
A Check Box: <asp:CheckBox ID="cbTest" runat="server" />

Codebehind:

public partial class widgets_TestWidget_edit : WidgetEditBase
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            StringDictionary settings = GetSettings();
            if (settings.ContainsKey("test1"))
                txtTest.Text = settings["test1"];

            if (settings.ContainsKey("test2"))
                cbTest.Checked = Convert.ToBoolean(settings["test2"]);
        }
    }

    public override void Save()
    {
        StringDictionary settings = GetSettings();
        settings["test1"] = txtTest.Text.ToString();
        settings["test2"] = cbTest.Checked.ToString();
        SaveSettings(settings); //commit changes
    }
}

Editing the widget:

widget editing

Utilizing custom properties

It should be no surprise that the widget user control can access its settings in the very same way. Here’s an example of how this same widget would retrieve and display these two settings demonstrated above:

public override void LoadWidget()
{
    StringDictionary settings = GetSettings();
    lblTest.Text = settings["test1"] + "<br/>" + settings["test2"];
}

test widget

Widget inspiration

BlogEngine.NET has a number of solid widgets built in, plus there is a codeplex project containing links to a number of other custom ones: http://blogenginewidgets.codeplex.com/

I’d encourage you to poke through the source code of any of these to see how they work under the hood. They are quite straightforward once you understand the basic requirements.

I started working on a “Delicious Bookmarks” widget today, modeled after the built-in Twitter widget (which is essentially just an RSS reader specifically branded for tweet feeds). It is implemented just as described above, but of course with a bit more code do do the RSS retrieval, caching, etc.  Stay tuned, as I might release the source code on an upcoming post, but here’s a preview:

delicious bookmark widget

Tags: widget
Categories: BlogEngine.NET

About me

I'm Kurt, and I make web applications. Want to know more? Check out my about page.

WTF is all this code? I came here for food!

My wife made a new year's resolution to try out at least one new recipe each week. Want to know what she's been feeding me? resolutionfood.blogspot.com