I've got a simple c# console that uses the Dapper ORM to make a call to a local MySql database in order to execute a stored procedure called users.UserCreate
.
However, when running the query I get an exception saying
Procedure or function 'UserCreate' cannot be found in database 'users'
But users
isn't the database local_db
is.
Here an example use:
public virtual Task CreateAsync(User user)
{
using (var con = new MySqlConnection(_dbConn))
return con.ExecuteAsync("users.UserCreate", user, commandType: CommandType.StoredProcedure);
}
_dbConn contains the connection string, also stating the name of the database as local_db
.
This is what the stored procedure looks like:
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `users.UserCreate`(IN `@UserId` VARCHAR(128), IN `@UserName` VARCHAR(255), IN `@PasswordHash` LONGTEXT, IN `@SecurityStamp` LONGTEXT)
NO SQL
INSERT INTO Users
(Id, UserName, PasswordHash, SecurityStamp, EmailConfirmed, PhoneNumberConfirmed, TwoFactorEnabled, LockoutEnabled, DateCreated, DateUpdated, IsDeleted)
VALUES
(@UserId, @UserName, @PasswordHash, @SecurityStamp, 0, 0, 0, 0, CURRENT_DATE, CURRENT_DATE, 0)$$
DELIMITER ;
Is the problem something to do with MySql or Dapper? I use a similar naming convention for stored procedures in SQL Server, also using Dapper, and haven't had this problem before.
I have tried:
Any ideas?
Well, turns out conversion from using SQL to MySQL has been full of learning curves in terms of MySQL's (in)capabilities. To get around the schema naming conventions using full-stops/periods (".") I've just replaced it with an underscore instead.
For anyone else facing the same dilemma, use underscores, you'll save yourself hours of headaches in the long run!