How to capture stored procedure query or print output in C#

Question

I have a stored procedure like this

PROCEDURE PRC_ABCD (resultset_out out TYPES.cursorType)
AS
SELECT * FROM ABCD;
END PRC_ABCD ;

And my C# code is like this

string cnn = "connectionstring";
OracleConnection conn = new OracleConnection(cnn);
conn.Open();
var cmd = adapter.CreateSpCommand(con, "PRC_ABCD");
cmd = adapter.outField(cmd, "resultset_out", OracleDbType.RefCursor);
using (var reader = cmd.ExecuteReader())
{
 while (reader.Read())
    {
       yield return Total.FromDataReader(reader, vProductCount, vProductAreaCount, vMonitoredByCount, customerCount);
 }
}

My question is how do I get to see the results of PRC_ABCD into console message or how do I get to print the query SELECT * FROM ABCD?Anybody have any info about this?

Answer 1

Hi Sudipta,

If you are looking to static print query then Change your stored procedure like below and featch that column in c# as usual...

Declare @query as varchar(max);
set @query = 'SELECT * FROM ABCD';
select @query as query;

Answer 2

thanks for reply :) ..I dont have too much experience in C# and store procedure.once again thank you for guiding me :)

Answer 3

Hi Sudipta,

You can write sql query as below and I have attached the source code also

-- ProcedureQueryOutput 'Suraj Kumar Mandal', ''

ALTER PROCEDURE [dbo].[ProcedureQueryOutput]

(

@EmpName VARCHAR(50),

@SQLQuery NVarchar(2000) out

)

AS

IF EXISTS ( SELECT * FROM EmpTable WHERE EmpName = @EmpName )

BEGIN

SET @SQLQuery = ('SELECT * FROM EmpTable WHERE EmpName =''' + @EmpName +'''')

END

ELSE

BEGIN

SET @SQLQuery = 'INSERT INTO EmpTable (EmpName) VALUES ( '''+ @EmpName +''' )'

END

EXECUTE sp_executesql @SQLQuery

And after that you can call in web form as below

protected void btnGetSQLText_Click(object sender, EventArgs e)

{

string message = String.Empty;

SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=TestDB; User Id=sa; Password=password12");

con.Open();

SqlCommand cmd = new SqlCommand("ProcedureQueryOutput", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@EmpName", txtName.Text.Trim().Replace("'","''"));

cmd.Parameters.Add("@SQLQuery", SqlDbType.NVarChar, 500);

cmd.Parameters["@SQLQuery"].Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();

message = (string)cmd.Parameters["@SQLQuery"].Value;

con.Close();

}