Posts Tagged ‘Dot.NET’

Using SQLite with .NET


SQLite is a serverless SQL database engine.
It can be easily embedded into any software application, without the need to run any other database services on the host machine. The entire database engine and data are included in a single file, which is platform-independent.

Therefore depending on the project type, and data requirements, it could be a brilliant idea to use SQLite instead of another RDBMS system. Mozilla, Apple, Adobe and others are efficiently using SQLite.

This tutorial is showing how to connect and retrieve SQLite data from a .NET application.

Creating the tutorial’s database

We are going to create a new database, using one of the available SQLite management tools.
I am using wxSQLite+, which is pretty simple and doesn’t require any special setup.
However feel free to browse and maybe select an alternative one.

So, if running wxSQLite+ for the first time, from the Transaction menu, uncheck “Auto start transaction”.

We are going to create a new SQLite database,  called contacts.db.
Simply click on File -> Open/Create and save contacts.db on your preferred location.

Creating a table and adding sample data

Right click on Tables and select Create Table.

We are going to create a table named “friends” and add three columns (ID, name, phone). The ID will be the primary key of the table, having the autoincrement option ON.

If you are familiar with the SQLite SQL syntax, you can always use a “create table” command, instead of using the gui to create the table.

Now let’s add a couple of sample rows via SQL.

VALUES (null, 'Mary', '0900200200');

VALUES (null, 'John', '0800100100');

Do a “SELECT” query at the end to verify that the sample data have been successfully stored.


Getting the SQLite ADO.NET provider

SQLite can be used with any modern programming language, however an appropriate SQLite driver/wrapper is required to handle the database connection.

For .NET, I would recommend the SQLite ADO.NET 2.0/3.5 provider, which you can download here for free.

Creating a new C# Console Application

For the purpose of this tutorial, let’s create a new C# Console Application.
From the SQLite ADO.NET archive which you have downloaded, we only need the System.Data.SQLite.DLL file (\bin). So let’s just extract the dll and add a reference to it on our project.

The SQLite ADO.NET provider’s syntax is very similar to the corresponding Microsoft SQL Server ADO.NET provider’s syntax.

The connection string must contain the full path of the SQLite database file (contacts.db).

Here is the complete source code which is required to select data from the “friends” table and output the result on the Console:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;

namespace SQLiteTestCS
    class Program
        static void Main(string[] args)
            String connString = "Data Source=E:\\contacts.db";
            using (SQLiteConnection conn = new SQLiteConnection(connString))
                StringBuilder query = new StringBuilder();
                query.Append("SELECT * ");
                query.Append("FROM friends ");
                query.Append("ORDER BY name");
                using (SQLiteCommand cmd = new SQLiteCommand(query.ToString(), conn))
                    using (SQLiteDataReader dr = cmd.ExecuteReader())
                        while (dr.Read())
                            Console.WriteLine(dr.GetValue(0) + " " + dr.GetValue(1) + " " + dr.GetValue(2));

And the result should look like this:


As it was demonstrated on the tutorial, it is quite easy to setup an SQLite database, and use it as a data source on any .NET project.

Read Full Post »

A couple of days ago I had installed the .NET Framework 3.5 SP1 on my system, as part of the VS Studio 2008 SP1 upgrade.

I thought that Microsoft would have enough of their stuff to update and worry about.
However, I was wrong, as they decided to “invade” Firefox as well.

The first annoyance is the “Microsoft .NET Framework Assistant”, which gets installed as a Firefox add-on.
The “uninstall” button is grayed-out for some reason.
I would prefer to keep all .NET material and tools on the Internet Explorer side, there is absolutely no need to mess Firefox.

And the second annoyance is the addition of the .NET version on the browser’s user-agent string.
Why should I report my .NET info on every Web site that I visit? Not every Web server is IIS.
Well, at least this can be easily altered on the about:config section (general.useragent.extra.microsoftdotnet).

Read Full Post »

Great news for the .NET developers.


Read Full Post »