How to call MySQL Stored Procedures from Lightswitch?

Question

I followed the instructions here to try and call a (parameterless) MySQL SP from within a LightSwitch 2012 application. This cited instructions are for SQL Server SPs.

Here is the relevant code:

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace LightSwitchApplication
{
    public partial class StoredProcceduresService
    {
        partial void MakeMasterOperations_Inserting(MakeMasterOperation entity)
        {
            using (SqlConnection connection = new SqlConnection())
            {
                string connectionStringName = this.DataWorkspace.SystemInfo.Details.Name;
                connection.ConnectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;

                string storedProcedure = "make_master";
                using (SqlCommand command = new SqlCommand(storedProcedure, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    connection.Open();
                    command.ExecuteNonQuery();
                }            
            }
        this.Details.DiscardChanges();
        }
    }
}

This fails on connection.Open(); with a SqlException "Login failed for user 'root'." I know that userid and password are OK, since other database manipulations with the same connection string work just fine from within LightSwitch.

Is it possible to call MySQL SPs in LightSwitch? If so, how?

score 0 · Answer 1 · answered Oct 01 '13 at 11:58

Well, I found the answer.

In order to use MySQL instead of SQL Server, one has to add a reference to MySql.Data to the server project and change the code to:

    using System;
    using System.Configuration;
    using System.Data;
    using System.Linq;
    using MySql.Data.MySqlClient;

    namespace LightSwitchApplication
    {
        public partial class StoredProcceduresService
        {
            partial void MakeMasterOperations_Inserting(MakeMasterOperation entity)
            {
                using (MySqlConnection connection = new MySqlConnection())
                {
                    string connectionStringName = this.DataWorkspace.SystemInfo.Details.Name;
                    connection.ConnectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;

                    string storedProcedure = "make_master";
                    using (MySqlCommand command = new MySqlCommand(storedProcedure, connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        connection.Open();
                        command.ExecuteNonQuery();
                    }            
                }
            this.Details.DiscardChanges();
            }
        }
    }

Too bad there does not appear to be a common base class that allows instantiation depending on which DB server is used..

How to call MySQL Stored Procedures from Lightswitch?

1 Answers1