Search code examples
c#mysqlmariadbdapper

Query works fine via GUI, but returns generic error when try to run through Dapper

I have a query that runs just fine on MariaDB server via GUI, however as soon I will try to run through Dapper C# I will get an error:

MySql.Data.MySqlClient.MySqlException: 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DELIMITER $ BEGIN NOT ATOMIC

IF (@EndDate < NOW()) THEN SET @GroupId = 3; ' at line 1'

I am using regulard IDbConnection i.e.

using (IDbConnection con = new MySqlConnection(SQLConfig.UI))

and to call the query:

return con.Query<MembershipDynamic.MembershipStatus>(sqlQuery, new{ @MembershipId = membershipId}).Single();

Same query works perfectly well with the same membershipId.

I have no idea why exactly same query wouldn't run via Dapper.

My connection string is regular one with only one extra parameter

AllowUserVariables=True

As the query is not a problem as runs fine through anything else, I am guessing that connection string is missing something ???

I also tried to set 

 public MembershipsRepo()
    {
        DapperExtensions.DapperExtensions.SqlDialect = new DapperExtensions.Sql.MySqlDialect();
    }

Here is the query. I've tried without DELIMITER on direct implementation of MySqlConnection but it will only return result for first select (???) ignoring the rest... The SQL is modified MS SQL procedure.

SET @TypeId = NULL;
SET @StartDate = NULL;
SET @EndDate = NULL;
SET @GroupId = NULL;
SET @Status = NULL;

SELECT @TypeId := TypeId, @StartDate := StartDate, @EndDate := EndDate
FROM Membership_Memberships
WHERE MembershipId = @MembershipId;

DELIMITER $
BEGIN NOT ATOMIC

IF (@EndDate < NOW()) THEN
SET @GroupId = 3;
SET @STATUS = 'Expired';
END IF;

IF (SELECT COUNT(Id) FROM Membership_Trials WHERE MembershipId = 
@MembershipId AND Active = 1 ) > 0  THEN
SET @GroupId = 4;
SET @Status = 'Trial';

ELSE
SELECT @GroupId := T.GroupType, @Status := T.Name FROM Membership_Types T 
WHERE T.Id = @TypeId;
-- RETURN  Select @GroupId, @Status;
END IF;

END $
DELIMITER ;

SELECT @GroupId, @Status;

Dapper / ADO.NET works fine with simple queries, but fails with a bit more advanced queries :/

***** EDIT

After some investigation it looks like MySQL is limited to 1 query per request, so pretty much not possible to write even a bit more advanced query :/ (with variables, as those are set by Select).

Only way is to create procs which I won't, or go back to Microsoft SQL or use some other SQL...

It would be a huge hit as I've invested almost 6 months into moving from SQL to MySQL :/ (architecture, replication, etc. etc.)

:(((((((((((

Solution

  • Ok looks like we've lost few months of work.

    MySQL is a not something that can replace Microsoft SQL.

    Basically stored procedures will return all selects (and to join / process all cases etc. IT HAS to do a lot of selects / joins / if's).

    Unfortunately this is a deal breaker as it would result returning 100's of MB of unnecessary data and break whole platform. (currently running on Microsoft SQL)