0

Procedure name: AGENCY_STATE_REPORT

Input: 

(frm_date date, 
 to_date1 date)

Output; 

p_cur out SYS_REFCURSOR

My C# code:

public DataTable retdt1(string frmdate,string todate)
{
    try
    {
        // Logfile("retdt", query);
        con.Open();

        if (con.State == ConnectionState.Open)
        {
            objCmd = new OracleCommand();
            objCmd.CommandText = "AGENCY_STATE_REPORT ";
            objCmd.CommandType = CommandType.StoredProcedure;

            objCmd.Parameters.Add("@frm_date",OracleDbType.Date).Value=frmdate;
            objCmd.Parameters.Add("to_date1", OracleDbType.Date).Value = todate;
            objCmd.Parameters["@ p_cur"].Direction = ParameterDirection.Output;

            dap = new OracleDataAdapter(objCmd);
            dt = new DataTable();

            dap.Fill(dt);

            if (dt.Rows.Count > 0)
            {
                return dt;
            }
        }

        return null;
    }
    catch (Exception ex)
    {
        HttpContext.Current.Response.Write(ex.Message);
        throw ex;
    }
    finally
    {
        if (con != null)
            if (con.State != ConnectionState.Closed)
                con.Close();

        if (objCmd != null)
        {
            objCmd.Dispose();
            dap.Dispose();
            dt.Dispose();
        }
    }
}

I know it is wrong code, but how to send those two input parameters to the stored procedure?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
gowtham
  • 1
  • 2

1 Answers1

0

Auto-populate Binding GridView to dynamic DataTable

You can also lose the manual disposal by implementing "Using" blocks, example:-

using (var cnn = new SqlConnection(WebConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString))
                {
                    cnn.Open();

                    using (var cmd = new SqlCommand(sql.ToString(), cnn))
                    {
                        cmd.CommandType = System.Data.CommandType.Text;    
                        cmd.ExecuteNonQuery();
                    }
                }