CODEWITHSUNDEEP
c#mysqlsqlstored-procedures
Arzu Suleymanov
Arzu Suleymanov

Reputation: 691

MySql parameterized stored procedure does not work in C#

I have parameterized stored procedure (tested and works) in mysql. I wanted to replace my long select statement with stored procedure in my code. With select statement it worked but now I am not able to run it with sp (commented string req).

My stored procedure:

CREATE DEFINER=`root`@`%` PROCEDURE `GetProjectVM`(
    IN projectName NVARCHAR(255)
)
BEGIN
   select VirtualMachines.Name,
   VirtualMachines.IpAddress,
   VirtualMachines.DiskSize,
   VirtualMachines.CPU,
   VirtualMachines.Ram,
   VirtualMachines.ImageUrl,
   VirtualMachines.Role,
   Hypervisors.Name as Hypervisor,
   Managements.Netmask,
   Managements.Gateway from VirtualMachines inner join Projects
   on Projects.id = VirtualMachines.ProjectId inner join Hypervisors
   on Hypervisors.HypervisorId = VirtualMachines.HypervisorId inner join Managements 
   on VirtualMachines.ManagementId = Managements.Id 
   where Projects.Name = projectName;
END

My code looks like:

private string _dbUrl;
private MySqlConnection _cnn;

public Dbmanagement(string dbUrl)
{
    this._dbUrl = dbUrl;
    this._cnn = new MySqlConnection(this._dbUrl);
}

private MySqlDataReader ExecuteMysqlCommand(string cmd)
{
    _cnn.Open();
    var result = new MySqlCommand(cmd, _cnn);
    return result.ExecuteReader();
} 

public IEnumerable<VM> GetProjectVM(string projectName)
{
    var vmList = new List<VM>();
    //string req =
    //    $"select VirtualMachines.Name,VirtualMachines.IpAddress,VirtualMachines.DiskSize,VirtualMachines.CPU,VirtualMachines.Ram,VirtualMachines.ImageUrl,VirtualMachines.Role,Hypervisors.Name as Hypervisor,Managements.Netmask,Managements.Gateway from VirtualMachines inner join Projects on Projects.id = VirtualMachines.ProjectId inner join Hypervisors on Hypervisors.HypervisorId = VirtualMachines.HypervisorId inner join Managements on VirtualMachines.ManagementId = Managements.Id where Projects.Name = \"{projectName}\" ;";

    MySqlCommand cmd = new MySqlCommand("GetProjectVM", _cnn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@projectName", projectName);          

    var req = cmd;

    var rd = ExecuteMysqlCommand(req.ToString());

    while (rd.Read())
    {
        vmList.Add(new VM(rd));
    }

    _cnn.Close();
    return vmList;
}

Upvotes: 0

Views: 172

Answers (1)

Jon Skeet
Jon Skeet

Reputation: 1500055

The problem - or at least the first problem - is how you're executing the command.

Calling ToString on a MySqlCommand is very unlikely to be able to convey all the relevant information. You're losing parameterization, command type etc.

Change this:

var req = cmd;

var rd = ExecuteMysqlCommand(req.ToString());

to

_cnn.Open();
var rd = req.ExecuteReader();

I'd also suggest not having a single MySqlConnection, but just constructing one when you need to, and letting the connection pool manage making that efficient. So something like:

using (var connection = new MySqlConnection(_dbUrl))
{
    connection.Open();
    using (var cmd = new MySqlCommand("GetProjectVM", connection))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        // Avoid AddWithValue where possible, to avoid conversion issues.
        cmd.Parameters.Add("@projectName", MySqlDbType. VarChar).Value = projectName;

        using (var reader = cmd.ExecuteReader())
        {
            var list = new List<VM>();
            while (reader.Read())
            {
                list.Add(new VM(reader));
            }
            return list;
        }
    }
}

Upvotes: 3

Related Questions