How to get the Output value of SP using C#
- by karthik
I am using the following Code to execute the SP of MySql and get the output value. I need to get the output value to my c# after SP is executed. How ? Thanks.
Code :
public static string GetInsertStatement(string DBName, string TblName, string ColName, string ColValue)
{
string strData = "";
MySqlConnection conn = new MySqlConnection(ConfigurationSettings.AppSettings["Con_Admin"]);
MySqlCommand cmd = conn.CreateCommand();
try
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "InsGen";
cmd.Parameters.Clear();
cmd.Parameters.Add("in_db", MySqlDbType.VarChar, 20);
cmd.Parameters["in_db"].Value = DBName;
cmd.Parameters.Add("in_table", MySqlDbType.VarChar, 20);
cmd.Parameters["in_table"].Value = TblName;
cmd.Parameters.Add("in_ColumnName", MySqlDbType.VarChar, 20);
cmd.Parameters["in_ColumnName"].Value = ColName;
cmd.Parameters.Add("in_ColumnValue", MySqlDbType.VarChar, 20);
cmd.Parameters["in_ColumnValue"].Value = ColValue;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
catch (System.Exception e)
{
Console.WriteLine(e.Message);
}
return strData;
}
SP :
DELIMITER $$
DROP PROCEDURE IF EXISTS `InsGen` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsGen`
(
in_db varchar(20),
in_table varchar(20),
in_ColumnName varchar(20),
in_ColumnValue varchar(20)
)
BEGIN
declare Whrs varchar(500);
declare Sels varchar(500);
declare Inserts varchar(2000);
declare tablename varchar(20);
declare ColName varchar(20);
set tablename=in_table;
# Comma separated column names - used for Select
select group_concat(concat('concat(\'"\',','ifnull(',column_name,','''')',',\'"\')'))
INTO @Sels from information_schema.columns where table_schema=in_db and table_name=tablename;
# Comma separated column names - used for Group By
select group_concat('`',column_name,'`')
INTO @Whrs from information_schema.columns where table_schema=in_db and table_name=tablename;
#Main Select Statement for fetching comma separated table values
set @Inserts=concat("select concat('insert into ", in_db,".",tablename," values(',concat_ws(',',",@Sels,"),');')
from ", in_db,".",tablename, " where ", in_ColumnName, " = " , in_ColumnValue, " group by ",@Whrs, ";");
PREPARE Inserts FROM @Inserts;
select Inserts;
EXECUTE Inserts;
END $$
DELIMITER ;
