VirtueMart Manager: VirtueMart Price Updater, or LINQ with MySQL using DB_Linq

One of my clients runs Joomla with the VirtueMart extension (an online shop component). To make it simpler to look after, I thought about writing an application that would update prices pulled from some source or other.

The application uses DB_Linq, a LINQ provider for MySQL, Oracle and PostgreSQL. Unfortunately DB_Linq currently has no documentation at all. I did find a few scraps of information on the Primary Objects site. I used version 0.18, which is deprecated, because with 0.19 I had trouble generating the LINQ classes.

Version 0.18 had a problem of its own...

DbMetal failed:System.ArgumentException: magma string must not be empty

It turned out that there is a problem when table names in the database contain two underscores next to each other.

Naturally I dug around in the code a little and fixed it:

//\DbLinq-0.18\src\DbLinq\Language\Implementation\AbstractWords.cs, line 139
public virtual IList<string> GetWords(string text)
{
    text = text.Replace("__", "_"); //turns two underscores into one

Now I could start enjoying LINQ against a MySQL database.

Here is, step by step, what you need to do in order to poke around a MySQL database comfortably.

1. Download DB_Linq
2. Generate the model classes from the database structure. A single file will be created, named after the database
   

   dbmetal /server:1.2.3.4 /user:dbuser /password:password /provider:MySql /database:people /language:C#
   

3. That is all :)
   The classes are ready, so you can start working against the database.

Reading data from the database (SELECT).

public ObservableCollection GetProducts()
{
    using (var connection = new MySqlConnection("server=server.pl;user id=user; password=secret; database=mydb"))
    {
        connection.Open();
        using (var dataContext = new BazaDataContext(connection))
        {

  var items = from s in dataContext.JoSVMProduct
                        join pc in dataContext.JoSVMProductPrice on s.ProductID equals pc.ProductID
                        orderby s.ProductName ascending
                        select new ProduktVm
                                   {
                                       ProductId = s.ProductID,
                                       ProductSku = s.ProductSku,
                                       ProductName = s.ProductName,
                                       ProductPrice = pc.ProductPrice,
                                       ProductFullImage = s.ProductFullImage,
                                   };

            var collection = new ObservableCollection();
            foreach (var c in items)
                collection.Add(c);

            return collection;
        }
    }
}

In the example above I fetch product and price data and build myself an observable collection. ProduktVm is my own intermediate class.

Updating data in the database (UPDATE).

using (var conn = new MySqlConnection("server=server.pl;user id=user; password=secret; database=mydb"))
{
    conn.Open();
    using (var context = new BazaDataContext(conn))
    {
        var items = from pc in context.JoSVMProductPrice
                    where pc.ProductID == 1
                    select pc;
        var i = items.First();
        i.ProductPrice = -1;
        context.SubmitChanges();
    }
}

Adding data to the database (INSERT).

using (PeopleDataContext context = new PeopleDataContext(connection))
{
    // Create a LINQ to SQL class to fill the properties.
    Person person = new Person();
    person.FirstName = txtFirstName.Text;
    person.LastName = txtLastName.Text;
    person.Age = Convert.ToInt32(txtAge.Text);

    context.Person.InsertOnSubmit(person);
    context.SubmitChanges();
}

I am not doing any inserts in my application yet, so the example comes from Primary Objects

One last thing: VMManager was supposed to read its data from Excel, but unfortunately I found no working way of getting data out of a worksheet. I saved the sheet as a tab-separated text file, and that kind of data can be read without any trouble.