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

Sudipta Saha

8y5.5k.NET

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?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sudipta Saha8y ago
thanks for reply :) ..I dont have too much experience in C# and store procedure.once again thank you for guiding me :)
Suraj Kumar8y ago
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();
}
Dharmraj Thakur8y ago
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;