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?

Answer 1

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.

Answer 2

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.

Answer 3

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).

Answer 4

hello i get the all value but display in one column of the datagrid someone tell how to display in separate columns.... code is SqlConnection _sqlConnection = new SqlConnection(); _sqlConnection.ConnectionString = "my connectios string";

    _sqlConnection.Open();
    SqlDataReader rdr = null;
    // Assuming your column's in your table are defined as username and password
    SqlCommand sqlcom = new SqlCommand("select top 10 employee_id, user_fname,user_lname from users", _sqlConnection);
    rdr = sqlcom.ExecuteReader();
    StringBuilder sb = new StringBuilder();
    while (rdr.Read())
    {
        sb.AppendLine(rdr[0].ToString());
        sb.Append(rdr[1].ToString());
        sb.Append(rdr[2].ToString());
    }

    return sb.ToString();

Answer 5

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