How might I set up data plumbing for Silverlight to MySQL in my situation?

Question

In short: What is a good method for setting up read-only data access from Silverlight to a MySQL database?

---

Here are the details of my situation:

I'm currently trying to set up a Silverlight application to present data from a MySQL database. Currently, I need to set-up read-only access to the MySQL database (I may set up other tables for complete CRUD functionality at a later, date, but for these particular tables, I'm only ever going to be concerned with the retrieve aspect).

I tried setting it up using RIA Services (CTP July 2009) with Entity Framework, but I had trouble debugging it and ended up trying to recompile the source code from the MySQL ADO.NET connector in order to install custom DLLs into the GAC. I wasn't able to get any of this stuff to work correctly.

My problem was that I had date values stored as 0000-00-00 in lots of my MySQL tables. The MySQL ADO.NET Connector throws an exception everytime it tries to bring down a row with an invalid date in it. I would try to recompile the connector (see links above), but that's feeling very much like a hack. I would try to update the values in the MySQL database to be within the appropriate spec for dates, but our IT manager (and effectively our DBA) does not want to do it.

I don't mind learning to work with LINQ (LINQ-to-what?), but I want to avoid concatenating my own strings of SQL commands. Because of the Date restrictions, I need a way to specify Case When orders.OrderDate = '0000-00-00' Then '0001-01-01' Else orders.OrderDate End for pretty much every date instance.

I'm especially interested to hear from folks who have worked with .NET and MySQL together. What will work in my situation?

Answer 1

Why has no one suggested using a ORM to hide the mySQL details? Both NHibernate and Subsonic support mySQL. Both are very customisable in how they interact with the database and should allow you to cater for malformed dates.

By using an ORM your data objects are now POCOs, and you can use whatever you want to get the data to the Silverlight client. Vanilla web services or WCF should be fine. RIA services if you want to try out the bleeding edge.

IMHO, this will be simpler than setting up a mysql->php->xml->asp.net->silverlight chain.

Answer 2

My problem was that I had date values stored as 0000-00-00 in lots of my MySQL tables.

Can you just write Select NullIf( SomeDate, '0000-00-00') As SomeDate From SomeTable in your SQL queries? I don't know MySQL, but that's what I would do in T-SQL.

Answer 3

Here is what I did for a similar problem I was facing.

I used php to get data from the MySQL database and turned it into an XML file. I called that file from my silverlight app and used LINQtoXML to parse the data and make it available in my XAML controls. I am not a programmer by trade so maybe there is a better way to do it but this works for my app. Hope this helps. LINQ ROCKS!

Here is a portion of the code:

< ?php

header("Content-type: text/xml");

$grb_hostname = "host";
$grb_database = "dbName";
$grb_username = "dbUser";
$grb_password = "dbPwd";
$grb = mysql_connect($grb_hostname, $grb_username, $grb_password); 

mysql_select_db($grb_database, $grb);

$results = mysql_query("SELECT * FROM bursts ORDER BY bursts.id DESC");

$xmlOutput = "<?xml version=\"1.0\"?>\n"; 
$xmlOutput .= "<grbs>\n";

while($row = mysql_fetch_array($results)) {
    $xmlOutput .= "\t<grb id=\"".$row['id']."\" trigger=\"".$row['trigger']."\">\n";
    $xmlOutput .= "\t\t<grb_id>".$row['grb_id']."</grb_id>\n";
    $xmlOutput .= "\t\t<burst_ra>".$row['burst_ra']."</burst_ra>\n";
    $xmlOutput .= "\t\t<burst_dec>".$row['burst_dec']."</burst_dec>\n";
    $xmlOutput .= "\t</grb>\n";
}

$xmlOutput .= " < /grbs>"; // no space before /

echo $xmlOutput;

?>

then in my Silverlight I have the following:

private void LoadGrbs()

    {

        WebClient grbXmlFile = new WebClient();

        // Make sure the crossdomainpolicy.xml file exists on the remote server.
        grbXmlFile.DownloadStringAsync(new Uri("url_xml_generating_php_file", UriKind.Absolute));
        grbXmlFile.DownloadStringCompleted += new DownloadStringCompletedEventHandler(grbsXmlLoaded);
    }

    private void grbsXmlLoaded(object sender, DownloadStringCompletedEventArgs e)
    {
        processGrbXml(e.Result);
    }

    private void processGrbXml(string grbData)
    {
        XDocument grbs = XDocument.Parse(grbData);

        var query = from g in grbs.Descendants("grb")
                    select new
                        {
                            grbId = (string)g.Element("grb_id"),
                            grbDec = (string)g.Element("burst_dec")
                        };

        foreach (var grb in query)
        {
            grbListbox.Items.Add(grb.grbId);
        }

    }

grbListbox is a Listbox control in my Silverlight app.

Answer 4

You should use RIA Services, the newest version came out last week, and it's included in the silverlight 4 beta now.

http://silverlight.net/getstarted/silverlight-4-beta/

You don't have to use the entity framework with RIA, there are other options. We do, but we use SQL Server so that might not be your favorite.

They have changed the errors some in the new RIA Stuff, so I'd recommend taking a 2nd look. Here's Brad Abrams' example from last week:

http://microsoftpdc.com/Sessions/CL21

Finally, if you're having a lot of trouble debugging, you could take a look at Fiddler. It's a program that watches the traffic and it can display you the errors you're having in a more obvious fashion.