Archive for December 2008

MySql in C#

First download the Mysql ODBC driver from the download page.

Then download the .net connector, at this writing it was Mysql Connector/Net 5.2

You can choose to download only the binaries, extract and add /bin/MySql.Data.dll to your project. A help file for the classes is found at /Docs/MySql.Data.chm of the download.

With the dll added to the project, it’s very simple to connect to a MySql database as the snippet below.

using MySql.Data.MySqlClient;

namespace test
{
    class testit
    {
        private MySqlConnection mConn = null;
        private const string msConnStr = “SERVER=myserv.com” +
            “;DATABASE=somedbase;UID=theuser;PWD=thepassword”;

        public void test()
        {
            try
            {
                // Setup and open acceess to the database
                mConn = new MySqlConnection(msConnStr);
                mConn.Open();
                // simple count select
                MySqlCommand command = new MySqlCommand(“select count(*) from cars”, mConn);
                long ret = (long)command.ExecuteScalar();
                // how many rows in the table
                Console.WriteLine(“cars count=” + ret );
            }
            catch (Exception ex)
            {
                Console.WriteLine(“err=” + ex);
            }
            finally
            {
                if( mConn != null ) mConn.Close();
            }
        }

SQLite in C#

As one of my readers pointed out, SQLite is a small database fit for programs that need only minimal database activity looking for small footprint database. It’s used by Firefox / google chrome browser among others.

First let’s take a look at creating a small test SQLite database.  From the command prompt start up SQLite command line tool.

>sqlite3 test.db
sqlite>CREATE TABLE cars ( id INTEGER PRIMARY KEY AUTOINCREMENT, model text, year integer );
sqlite>insert into cars( model, year ) values( “Ford 350”, 2007 );
sqlite>insert into cars( model, year ) values( “Buick Skylark”, 1953 );
sqlite>insert into cars( model, year ) values( “Honda Civic”, 2002 );
sqlite>Select * from cars;
1|Ford 350|2007
2|Buick Skylark|1953
3|Honda Civic|2002
sqlite>.quit

You can activate the help by issuing .help
sqlite>.help

With the database created lets access it from C#. First you need some kind of a .net SQLite wrapper library. I choose to use system.data.SQLite it pretty much wraps the whole SQLite engine, it also works under Mono. All you need is their dll, add it to your project and reference the SQLite namespace. When you ship just include the System.Data.SQLite.DLL and your all set.

using System.Data.SQLite;

// Connection to the database
using( DbConnection conn = new SQLiteConnection(“Data Source=test.db”))
using( DbCommand cmd = conn.CreateCommand())
{
    // Open the database
    conn.Open();
    // Insert to the table
    cmd.CommandText = “insert into cars( model, year ) values( ‘Hummer’, 2000 )”;
    // exec
    cmd.ExecuteNonQuery();

    // Select from the table
    cmd.CommandText = “SELECT * FROM cars”;
    using (DbDataReader reader = cmd.ExecuteReader())
    {
        while( reader.Read() )
        {
            Console.WriteLine(“id=” + reader[0] +
                   “, model=” + reader[1] + “, year=” + reader[2]);
        }
    }
}

The SQLite dll is about 820k that’s the only software you need when you deploy. Along with the database, unless you want to create the database on the fly. The upside is that now you have a neat little database at your disposal. It’s fast and has a small footprint, which is what we were looking for.

But… yes there is always one of those. It turns out the Mono version is totally another distro, so don’t expect to fold this in your C# progam and expect it to run under Mono also. See further details on that here.

Boulder tech meetup, Dec 2nd

Went to the Boulder meetup yesterday, it was exciting as usual. With some nifty companies such as Me.dium now gone oneRiot search engine of current trends on the internet. The most interesting talk of the night was the new Yahoo BrowserPlus by Lloyd Hilaiel.

What is it ? “BrowserPlus™ is a technology for web browsers that allows developers to create rich web applications with desktop capabilities”
More development info here One impressive demo that Lloyd showed is motion censor driven. His laptop censors affected the code running in the browser. Pretty nice integration there. Check out the demos.

In hindsight, I should have snagged a T-shirt from him…

Json in C# – Part 2

I posted before Json in C# but I was not satisfied with what I found so far. I went looking again and found some C# classes in the newer frameworks. namely JavaScriptSerializer. Let’s take a look with a simple json data feed.

using System.Web.Script.Serialization;
   
    internal static class Program
    {
        class CarList
        {
            public List<Car> cars;
        }
        class Car
        {
            public string model = string.Empty;
            public int year = 0;
        }

        private static void Main(string[] args)
        {
            string jsonString = “{\”cars\”:[{\”model\”:\”Ford 350\”,\”year\”:2007},{\”model\”:\”Buick Skylark\”,\”year\”:1953},{\”model\”:\”Honda Civic\”,\”year\”:2002}]}”;

            JavaScriptSerializer serializer = new JavaScriptSerializer();
            CarList carObjects = serializer.Deserialize<CarList>(jsonString);
            Console.WriteLine(“cars count=” + carObjects.cars.Count);
        }



As far as I can tell JavaScriptSerializer was introduced in .Net 3.0, but depreciated in 3.5. Instead your supposed to use DataContractJsonSerializer. With that change you need to add serialize attribute to your classes, you also need to include Xml and serialization libs. Let’s take a look at the new code.

using System.Runtime.Serialization.Json;

    internal static class Program
    {
        [Serializable]
        class CarList
        {
            public List<Car> cars;
        }
        [Serializable]
        class Car
        {
            public string model = string.Empty;
            public int year = 0;
        }

        private static void Main(string[] args)
        {
            string jsonString = “{\”cars\”:[{\”model\”:\”Ford 350\”,\”year\”:2007},{\”model\”:\”Buick Skylark\”,\”year\”:1953},{\”model\”:\”Honda Civic\”,\”year\”:2002}]}”;
            //         
            DataContractJsonSerializer jsonserialize = new DataContractJsonSerializer(typeof(CarList));
            Stream jsonStream = new MemoryStream(ASCIIEncoding.Default.GetBytes(jsonString));
            CarList carObjectsj = (CarList)jsonserialize.ReadObject(jsonStream);
            Console.WriteLine(“cars count=” + carObjectsj.cars.Count);
        }



As a curve ball Microsoft decided to Un-depreciate the JavaScriptSerializer in .Net 3.5 SP1, which is good news. It’s easier coding when using the JavaScriptSerializer. I don’t know if both classes will be permanently available, we will see.