How to have silverlight get its data from MySQL

Question

I've written a small hello world test app in Silverlight which i want to host on a Linux/Apache2 server. I want the data to come from MySQL (or some other linux compatible db) so that I can databind to things in the db.

I've managed to get it working by using the MySQL Connector/.NET:

MySqlConnection conn = new MySqlConnection("Server=the.server.com;Database=theDb;User=myUser;Password=myPassword;");
conn.Open();
MySqlCommand command = new MySqlCommand("SELECT * FROM test;", conn);
using (MySqlDataReader reader = command.ExecuteReader())
{
     StringBuilder sb = new StringBuilder();
     while (reader.Read())
     {
         sb.AppendLine(reader.GetString("myColumn"));
     }
     this.txtResults.Text = sb.ToString();
}

This works fine if I give the published ClickOnce app full trust (or at least SocketPermission) and run it locally.

I want this to run on the server and I can't get it to work, always ending up with permission exception (SocketPermission is not allowed).

The database is hosted on the same server as the silverlight app if that makes any difference.

EDIT Ok, I now understand why it's a bad idea to have db credentials in the client app (obviously). How do people do this then? How do you secure the proxy web service so that it relays data to and from the client/db in a secure way? Are there any examples out there on the web?

Surely, I cannot be the first person who'd like to use a database to power a silverlight application?

Solution 1

The easiest way to do what you want (having read through your edits now :)) will be to expose services that can be consumed. The pattern that Microsoft is REALLY pushing right now is to expose WCF services, but the truth is that your Silverlight client can use WCF to consume a lot of different types of services.

What may be easiest for you to do right now would be to use a .NET service on a web server or maybe a PHP REST service, and then point your Silverlight app at that service. By doing so, you're protecting your database not only from people snooping through it, but more importantly, you're restricting what people can do to your database. If your data is supposed to be read-only, and your service's contract only allows reading operations, you're set. Alternatively, your service may negotiate sessions with credentials, again, set up through WCF.

WCF can be a client-only, server-only, or client-server connector platform. What you choose will affect the code you write, but it's all going to be independent of your database. Your code can be structured such that it's a one-to-one mapping to your database table, or it can be far more abstract (you can set up classes that represent full logical views if you choose).

Solution 2

While the "official" answer is to use WCF to push a service to Silverlight, I kind of figure that anyone using MySQL would probably not be using a complete ASP.NET solution. My solution was to build a PHP webservice (like Rob suggested) to interact with the MySQL database and have the Silverlight access it in a RESTful manner.

Here is beginning of a three part tutorial for using Silverlight to access a MySQL database through a PHP web service:

PHP, MySQL and Silverlight: The Complete Tutorial

Solution 3

Silverlight does not have any capability to directly access database servers. What you can do is to expose your database operations through web services (ASMX or WCF, even non-.NET!) and use Silverlight to access those services.

Solution 4

I just got this working; ASP.NET4 site with Silverlight4 content on Linux Ubuntu 10 / Apache2 server. Content is developed using Visual Studio 2010. VS2008 should work fine too.

Server:

• Setup a Linux server with Apache2 and MySQL, there are tons of guides on this.
  • Make sure MySQL is accessible from the development PC and optionally from the Internet. See here for details: Causes of Access-Denied Errors.
  • Setup the database table structures and add some content for testing later. In our example we assume you have the table 'persons' with the column 'name'.
• Since Silverlight is a client-side technology you are pretty much good-to-go and can host the application with a simple HTML page.
• A web service is required between Silverlight and MySQL. Microsoft's WCF RIA is one flavor, but requires .NET. On the plus-side, you get to host ASP.NET4 pages as well. Here is a thorough guide to setting it up: Setting up Mono 2.8 with Asp.Net 4.0 and MVC2 on Ubuntu with MySql Membership

Visual Studio:

• Install latest MySQL Connector/Net and restart VS
• Add your MySQL database as data source
  • Open Server Explorer -> Add data connection -> Select 'MySQL Database'
  • Fill in credentials and test connection

Setting up the site with MySQL access:

Here is a guide I found helpful: Step By Step Guide to WCF RIA enabled SL4 application with Entity Framework

• Create or open a Silverlight project.
  • The server-side project is typically named 'ProjectName.Web'
  • The client-side project is typically named 'ProjectName'
• Add 'ADO.NET Entity Data Model' to the server project. This will be a model of your database structure.
  • Select 'Generate from database'
  • Choose the MySQL database connection you created
  • Select the tables you want to access
• Build your solution now before proceeding.
• Add 'Domain Service Class' to the server project, f.ex. 'FooDomain'. This will make the database entities available to the client-side Silverlight code.
  • In 'Available DataContext/ObjectContext classes:' select the Entity Framework model you created in the previous step.
  • Check the entities you want to access and check 'Enable editing' where appropriate
  • Check 'Generate associated classes for metadata'
• Build your solution again to generate 'FooDomainContext', based on 'FooDomain' in server project.

Testing:

Let's get data from MySQL into Silverlight. Assuming there is a table named 'persons' with column name 'name', we can bind a list box to show the names of the persons.

First add a Silverlight page, let's say 'Home'. In Home.xaml add:

<ListBox x:Name="TestList" Width="100" />

In Home.xaml.cs file add:

public partial class Home : Page
{
    public Home()
    {
        InitializeComponent();

        Loaded += Home_Loaded;
    }

    void Home_Loaded(object sender, RoutedEventArgs e)
    {
        var context = new FooDomainContext();
        var query = context.Load(context.GetPersonsQuery());
        TestList.ItemsSource = query.Entities;
        TestList.DisplayMemberPath = "name";
    }
}

Here we assume you named your domain service 'FooDomain', and this would generate the 'FooDomainContext' class used.

Hopefully, if all is set up properly, you will now see a list of person names when running your Silverlight project.

Edit: ASP.NET is not optional, but required for the WCF RIA web service used in my example.

Solution 5

Having DB connections directly to the server from the client side is usually a bad idea. I don't know how easy it is to decompile a Silverlight app, but I would guess it's possible in some way. Then you're basically giving away your DB credentials to your users.

View more solutions

Comments

[Isak Savo]

But I can talk to the DB, it's just that I'm not allowed to. Why is it allowed to talk to a web service (over http I presume?) but not to a database?