Click here to Skip to main content
13,046,184 members (111,914 online)
Click here to Skip to main content
Add your own
alternative version

Stats

17.5K views
9 bookmarked
Posted 7 Aug 2013

Connecting MySQL From SilverLight With Web Services

, 8 Aug 2013
Rate this:
Please Sign up or sign in to vote.
Silverlight access to MySQL using Web Services.

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.
  5. 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";
            }
        }
    }
  6. 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:
  7. using MySql.Data.MySqlClient; 
  8. 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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

adriancs
Software Developer
Malaysia Malaysia
Writing programs is an art.

You may also be interested in...

Pro

Comments and Discussions

 
QuestionAwesome Pin
Luiey Ichigo1-Oct-14 18:53
memberLuiey Ichigo1-Oct-14 18:53 
QuestionNow how to display data from a table in a grid? thank you Pin
dedian28-Sep-14 11:37
memberdedian28-Sep-14 11:37 
AnswerRe: Now how to display data from a table in a grid? thank you Pin
adriancs28-Sep-14 13:06
professionaladriancs28-Sep-14 13:06 
GeneralRe: Now how to display data from a table in a grid? thank you Pin
dedian10-Oct-14 5:06
memberdedian10-Oct-14 5:06 
Questionsilverlightsoap is not showing the methods that i have written Pin
Member 1000065812-Nov-13 19:56
memberMember 1000065812-Nov-13 19:56 
AnswerRe: silverlightsoap is not showing the methods that i have written Pin
adriancs18-Nov-13 2:45
mvpadriancs18-Nov-13 2:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170713.1 | Last Updated 8 Aug 2013
Article Copyright 2013 by adriancs
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid