CodeSnippet: Using SQL Compact Edition

Many multi-user applications require a database, be it a web app (probably more often a web app!) or win forms. In single user applications, often it is beneficial if there is a database (in speed, complexity and robustness), but the problem is distributing the database server. I've had a few applications that come bundled with MySQL, and it just plain ole sucks.

Some of the reasons for using a database in your application include

• Faster than flat file storage such as XML
• Can have complex relationships
• Can have varying datatypes that don't break an XML parser
• Can then later redeploy in another project, such as a website using SQL Server easier

One of the easier ways to distribute your winform/WPF application with a database is with Microsoft's SQL Compact Edition (SQLCE). It has a fairly low footprint, is very fast, and is reasonably powerful despite the reduced operations (which allows it to be fast/low footprint/etc). Visual Studio's deployment tools even allow you to check for the SQL CE prerequisite on the target machine before installing, and will download SQL CE if it isn't found!

Adding SQL CE to your project

Providing SQL CE was installed during your Visual Studio install (default install settings), to get a SQL CE database in to your project, all you need to do is add a New Item > Local Database to your project. This will automagically add the references your project will need to access SQL CE databases (although you'll still need Using System.Data.SqlServerCE;)

Double clicking on your new database in the Project Explorer will activate the Server Explorer, allowing you to add Tables to the database.

Using SQL CE

The way you access data is nearly identical to a normal SQL Server, except it all lives in the System.Data.SqlServerCE namespace, and usually has SqlCeClass rather than SqlClass. ie:

SqlCeConnection sqlCon = new SqlCeConnection(TestApp.Properties.Settings.Default.TestConnectionString);
SqlCeCommand sqlCom = new SqlCeCommand();
sqlCom.Connection = sqlCon;
sqlCom.CommandText = "SELECT * FROM TestTable";

SqlCeDataAdapter sqlDa = new SqlCeDataAdapter(sqlCom);
DataSet ds = new DataSet();
sqlCon.Open();
sqlDa.Fill(ds);
sqlCon.Close();

Using LINQ with SQL CE

In .NET 3.5, you can also use LINQ against SQL CE. Currently (VS2008) you have to use the SQLMetal tool from the Visual Studio CLI, then add the generated dbml file to your project (Add -> Existing Item)

SqlMetal SqlCeFileName.sdf /dbml:LinqClassName.dbml

In the new version of MahTweetsMediaHorn (correction, thanks Will), I'll be using my own database rather than relying on Windows Media Players database. The LINQ to select all the albums (after generated the dbml via SqlMetal) is..

Music db = new Music(MediaHorn.Properties.Settings.Default.MusicConnectionString);
var album = from a in db.Album select a;
listBox2.ItemsSource = album;

Any and all of the usual tricks for LINQ apply to SQLCE - once you generate the dbml via SQLMetal, it really doesn't care what is underneath. If you want to bind it properly to WPF with automagic updates for when the datasource updates, I strongly recommend looking into the very clever SyncLINQ by the equally clever Paul Stovell, which also supports Silverlight.

Deploying apps with SQL CE

As I mentioned, the Visual Studio "Publish" tool makes sure the target system has all the prerequisites before installing. SQL CE is automatically added to the list, but if you need to modify it, right click on your project, Properties, then down to the Publish tab, and the Prerequisites option will launch a new dialog.

Just make sure you distribute your application via Build -> Publish :)

I'm all nekkid

see : http://griffmiester.com/2008/04/08/css-naked-day/ http://www.purecaffeine.com/ and the official site http://naked.dustindiaz.com/

Needed: 1x New WebHost

Thanks to the super reliable Dreamhost, you may have noticed I had no blog yesterday. Or email. Or Jabber. It took them over 12 hours after contact them that, yes, my site has mysteriously vanished, as have all the automagic backups.

In all fairness, you pay peanuts, you get monkeys - that sort of thing. Given DreamHost's last year has been abysmal, I can no longer 'afford' to defend them.

I've finally got the site back, so I'm backing it up like crazy and will be moving the blog to another host as soon as I can. I'm looking into VPS', spending about AUD$50/m. Any suggestions?

In the meantime while I'm hunting around for a new host (I'll have one by the 11th), I'll probably not be blogging so it is less effort to continuously backup.

Mah Tweets: Now available and on Codeplex

What is Mah Tweets? According to the description (that Will wrote, I corrected),

"Mah Tweets is a Twitter client with a difference.

In addition to using the regular Twitter API, it uses the Jabber protocol to get instant notifications from Twitter. This also means it's capable of using Twitter's Keyword Tracking notification services. "

To sum that up, Mah Tweets gives you your twitter feed in real time (for people you have on device/im notifications), rather than what the usual Twitter client does, which is scrape the API feed every minute or so. Mah Tweets does that too, so those you don't have on notification will still be seen!

Requirements

• Microsoft .NET Framework 3.0
• Twitter Account
• Jabber/XMPP-compliant IM Account (such as Gmail / Gtalk)

Download

PLEASE NOTE THIS IS AN ALPHA RELEASE.
Mah Tweets Alpha v2.1

Thanks to the help of Will, MahTweets is now also available on Codeplex…that means source code and all!

Codeplex for those who don't know, is sort of like Microsoft's answer to SourceForge. Through TeamExplorer 2005/2008, it allows you to manage it all through Visual Studio, instead of using a standalone tool (which for me, I'd always forget. My tests of CVS/SVN have failed because of that)

CodeSnippet: Multithreading your app

Ever noticed how some applications tend to hang when you perform an operation? For example in MahTweets (my Twitter client), it used to have a looong pause every time it updated the Tweet list, and often the application would go black, almost to the 'not responding' stage. The GUI and application logic shared the same thread, meaning a massive change in GUI or lots of processing in the logic would stop the other.

The way around this is to use a separate thread for anything that requires a lot of processing, or that is bottled by another factor (Disk IO, or network speed, etc).

public delegate void MyDelegate();

// This method could be the method behind a button.clickpublic void InvokingMethod()
{
    Thread myThread = new Thread(new ThreadStart(new MyDelegate(MyMethod)));
    myThread.Start();
}
public void MyMethod()
{
    //Code to execute on new thread goes here
}

There is one caveat, your new thread cannot access the GUI because this could cause some serious synchronisation issues. The way around this (in WPF at least!) is to use the Dispatcher.Invoke method.

public void GuiMethod()
{
    if (!Dispatcher.CheckAccess())
    {
        Dispatcher.Invoke(DispatcherPriority.Normal, new voidDelegate(GuiMethod));
        return;
    }

    //Code that accesses the GUI
}

If you need to pass parameters, one (quick) way is with an anonymous delegate like below, I'll let you figure out the different ways to go about it though.

Thread t = new Thread(delegate() { MyMethod(MyParam); });

If you are having problems with concurrency and synchronisation, that's outside the scope of this post…start looking at wikipedia's article on threading, and go from there.