How to call MySQL stored procedure with select from C#

Question

Can any one please help me with this. I didn't use C# a lot , and i need to call MySQL stored procedure with 1 input parameter and i need to write down it query output.

My Stored Procedure:

CREATE DEFINER=`root`@`%` PROCEDURE `SP_For_Insert`(var_username varchar(50))

BEGIN

Declare counter int default 0;
Select Count(username) into counter From users where username = var_username;
if counter = 0  then
    select 0 as zero_results;
else 
    Select username from users where username=var_username;
end if;
END

I know how to call simple query and store result, but i need this to work.

It would be awesome if you could share a [mcve] of your attempt so far. — mjwills, Jun 28 '18 at 12:38
You can tell the command what commandtype to use. One of those it CommandType.StoredProcedure — Charles May, Jun 28 '18 at 12:39
Check the doc. https://dev.mysql.com/doc/dev/connector-net/6.10/html/P_MySql_Data_MySqlClient_MySqlCommand_Parameters.htm — Cleptus, Jun 28 '18 at 12:49

score 1 · Accepted Answer · answered Jul 02 '18 at 06:46

So i found a solution, if some one else had this problem i will post it now

  //for connection parameters
  private static string connectionString = "datasource=localhost;"+
                                "port=3306;"+
                                "Initial Catalog='authorization_schema';"+
                                "username=admin;"+
                                "password=admin123;";

  MySqlConnection connection = new MySqlConnection(connectionString);

  // construct SQL command with parameters (this way you can call SP also)
  IDbCommand cmd = new MySqlCommand("SELECT account, action FROM AUTHORIZATIONS WHERE username=@UserName", connection);

  // construct parameters
  MySqlParameter usernameParam = new MySqlParameter();
  usernameParam.ParameterName = "@UserName";
  usernameParam.Value = username;

  cmd.Parameters.Add(usernameParam);

  / construct data adapter
  IDataAdapter adapter = new MySqlDataAdapter((MySqlCommand)cmd);

  // construct DataSet to store result
  DataSet ds = new DataSet();
  adapter.Fill(ds);

How to call MySQL stored procedure with select from C#

1 Answers1