Connecting MySQL From SilverLight With Web Services

• Download sample project

Introduction

This article is about connecting MySQL from Silverlight by using Web Services.

Walkthrough

Step 1: Create Web Services

Step 2: Add Service Reference to Silverlight

Step 1: Create Web Services

1. Add a new Silverlight project.



2. Create a new Web Service. Right click on the web project > Add > New Item



3. Select "Web Service".



4. Initial code of a new Web Service.
C#

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.Services;
 
namespace SilverlightApplication1.Web
{
    /// <summary>
    /// Summary description for WebService1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    public class WebService1 : System.Web.Services.WebService
    {
        [WebMethod]
        public string HelloWorld()
        {
            return "Hello World";
        }
    }
}

5. In order for the Web Service able to connect to MySQL, we need to add a reference of MySql.Data.DLL into the web project and add the Using statement at top of the Web Service class:
C#

using MySql.Data.MySqlClient; 

6. HelloWorld() is an initial sample method created by Visual Studio. You may want to delete it as it is not needed. I'm going to create 2 simple method to demonstrate how Web Services are used to communicate between SilverLight and MySQL.

First method: ExecuteScalar()

This method is simple. Get a single object from MySQL.

public string ExecuteScalar(string sql)
{
    try
    {
        string result = "";
        using (MySqlConnection conn = new MySqlConnection(constr))
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = sql;
                result = cmd.ExecuteScalar() + "";
                conn.Close();
            }
        }
        return result;
    }
    catch (Exception ex)
    {
        return ex.Message;
    }
} 

Second method: ExecuteNonQuery()

For single SQL execution. Example of SQL type: INSERT, UPDATE, DELETE.

public string ExecuteNonQuery(string sql)
{
    try
    {
        long i = 0;
        using (MySqlConnection conn = new MySqlConnection(constr))
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = sql;
                i = cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
        return i + " row(s) affected by the last command, no resultset returned.";
    }
    catch (Exception ex)
    {
        return ex.Message;
    }
}  

This is how the Web Service looks like after adding the two methods above:

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.Services;
using MySql.Data.MySqlClient;
 
namespace SilverlightApplication1.Web
{
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    public class WebService1 : System.Web.Services.WebService
    {
        string constr = "server=localhost;user=root;pwd=1234;database=test;";

        [WebMethod]
        public string ExecuteScalar(string sql)
        {
            try
            {
                string result = "";
                using (MySqlConnection conn = new MySqlConnection(constr))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        conn.Open();
                        cmd.Connection = conn;
                        cmd.CommandText = sql;
                        result = cmd.ExecuteScalar() + "";
                        conn.Close();
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
 
        [WebMethod]
        public string ExecuteNonQuery(string sql)
        {
            try
            {
                long i = 0;
                using (MySqlConnection conn = new MySqlConnection(constr))
                {
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        conn.Open();
                        cmd.Connection = conn;
                        cmd.CommandText = sql;
                        i = cmd.ExecuteNonQuery();
                        conn.Close();
                    }
                }
                return i + " row(s) affected by the last command, no resultset returned.";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }  
    }
} 

You will notice that an attribute of [WebMethod] is added to the methods.

Rebuild the project and let the Web Service be ready for next step.

Web Service Access Permission

Please note that, by default, Web Service only allow those Silverlight that is hosted at the same domain with the Web Service to access. If the Silverlight application is hosted on another website/domain, Web Service will deny the communication. Therefore we have to configure the permission for the Web Service to be accessed by Silverlight which is hosted at different domain.

You have to create two additional files: clientaccesspolicy.xml and crossdomain.xml.

These files has to be put at the root of the domain where the Web Services are hosted.

Example: http://www.mywebsite.com/clientaccesspolicy.xml and http://www.mywebsite.com/crossdomain.xml

clientaccesspolicy.xml

<?xml version="1.0" encoding="utf-8"?>
<access-policy>
  <cross-domain-access>
    <policy>
      <allow-from http-request-headers="SOAPAction">
        <domain uri="*"/>
      </allow-from>
      <grant-to>
        <resource path="/" include-subpaths="true"/>
      </grant-to>
    </policy>
  </cross-domain-access>
</access-policy>

If you only want to allow the Web Service to be accessed by specific domain (example: www.myanotherwebsite.com), you can add it within <allow-from>. Example:

<?xml version="1.0" encoding="utf-8"?>
<access-policy>
  <cross-domain-access>
    <policy>
      <allow-from http-request-headers="SOAPAction">
        <domain uri="http://www.myanotherwebsite.com"/>
      </allow-from>
      <grant-to>
        <resource path="/" include-subpaths="true"/>
      </grant-to>
    </policy>
  </cross-domain-access>
</access-policy>

crossdomain.xml

<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE cross-domain-policy SYSTEM 
"http://www.macromedia.com/xml/dtds/cross-domain-policy.dtd">
<cross-domain-policy>
  <allow-http-request-headers-from domain="*" headers="SOAPAction,Content-Type"/>
</cross-domain-policy>

To understand more about this, please read: Making a Service Available Across Domain Boundaries (MSDN)

Step 2: Add Service Reference to Silverlight

Add a Service Reference to Silverlight.

Type the address of the Web Service and press [Go].

Example of address: http://www.mywebsite.com/MyCoolWebService.asmx

Change the Namespace to your favor, and press [OK].

Visual Studio will analyze the Web Service, do the data binding and create a class.

Before continue coding, let's us see what methods that we can use in the new created class. Right click the new class and select [View in Object Browser].

The class that we are going to use is WebService1SoapClient (in this example). The naming is based on the Service name. If we name our service class as MyCoolWebService, then MyCoolWebServiceSoapClient will be chosen as the name of the class in Silverlight. At the right panel, two methods and two events are highlighted. Those are the methods used to call the Web Services.

Lets create a simple Silverlight application by adding a Textbox and two Buttons.

In this example, user will key in SQL query directly into the Textbox.

Button of [ExecuteScalar] will send the SQL to the Web Service and retrieve data back. (SELECT, SHOW, etc.)

Button of [ExecuteNonQuery] will send the SQL to the Web Service for execution only. (INSERT, UPDATE, DELETE, etc.)

This is the initial code behind of MainPage.xaml:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
 
namespace SilverlightApplication1
{
    public partial class MainPage : UserControl
    {
        public MainPage()
        {
            InitializeComponent();
        }
 
        private void btExecuteScalar_Click(object sender, RoutedEventArgs e)
        {
        }
 
        private void btExecuteNonQuery_Click(object sender, RoutedEventArgs e)
        {
        }
    }
}

Now, these are what we are going to do here:

• Declare the service as static object at class level: ServiceReference1.WebService1SoapClient
• Create the service completed event of the two methods.
• Call the service in the event of button click.
• Display the service result: MessageBox.Show()

public partial class MainPage : UserControl
{
    ServiceReference1.WebService1SoapClient myService;

    public MainPage()
    {
        InitializeComponent();
        myService = new ServiceReference1.WebService1SoapClient();
        myService.ExecuteScalarCompleted += myService_ExecuteScalarCompleted;
        myService.ExecuteNonQueryCompleted += myService_ExecuteNonQueryCompleted;
    }

    void myService_ExecuteNonQueryCompleted(object sender, 
                   ServiceReference1.ExecuteNonQueryCompletedEventArgs e)
    {
        MessageBox.Show(e.Result);
    }

    void myService_ExecuteScalarCompleted(object sender, 
         ServiceReference1.ExecuteScalarCompletedEventArgs e)
    {
        MessageBox.Show(e.Result);
    }

    private void btExecuteScalar_Click(object sender, RoutedEventArgs e)
    {
        myService.ExecuteScalarAsync(textBox1.Text);
    }

    private void btExecuteNonQuery_Click(object sender, RoutedEventArgs e)
    {
        myService.ExecuteNonQueryAsync(textBox1.Text);
    }
}

Press [F5], run and test the Silverlight application.

Together with your creativity, I believe you can do something more than this for now :)

If you have done any changes to the Web Service, maybe you added new Service (new web methods), you have to update the Service Reference at Silverlight to re-bind the Services. You might want to update the Web Service address, if you uploaded the files to a different web hosting.

Happy coding.

Read More

• Access a Web Service from a Silverlight Application
• HOW TO: Write a Simple Web Service by Using Visual C# .NET
• How to: Build a Service for Silverlight Clients