CODEWITHSUNDEEP
c#mysqlvisual-studiostored-procedures
Srdjan Suc
Srdjan Suc

Reputation: 27

How to call MySQL stored procedure with select from C#

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.

Upvotes: 0

Views: 2371

Answers (1)

Srdjan Suc
Srdjan Suc

Reputation: 27

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);

Upvotes: 1

Related Questions