none
How to pass DB output paramenter messga from WEB API to AJAX error RRS feed

  • Question

  • HI Team,

    I've return code and return message as output parameters coming from my Stored proc and now i need to calling them using ADO .net into my WEBAPI controller. From WEB API to Ajax call finally display on my html page .

    I'm unable to give link between these three please help. Here are my code snippets

    I'm using database.outputpameter, i need to use this only not any from ado.net

                                                               

    I tried using throw exception but i'm not getting result as expected.

    public static AttributeCollection AdminSearch(int Pid, string email, string UserID)
            {
                AttributeCollection col = new AttributeCollection();
                try
                {
                    Microsoft.Practices.EnterpriseLibrary.Data.Database db = SqlDataHelper.CreateDatabase();
                    string sp = "getData";

                    using (DbCommand cmd = db.GetStoredProcCommand(sp))
                    {

                        if (!string.IsNullOrEmpty(UserID))
                            //  db.AddInParameter(cmd, "UserID", DbType.String, UserID);
                            if (Panelistid == 0)
                            {
                                db.AddInParameter(cmd, "pid", DbType.Int32, null);
                                db.AddInParameter(cmd, "email", DbType.String, email);
                            }
                        else
                            {
                                db.AddInParameter(cmd, "pid", DbType.Int32, Panelistid);
                                db.AddInParameter(cmd, "email", DbType.String, null);
                            }
                        db.AddOutParameter(cmd, "returnCode", DbType.Int32, 4);
                        db.AddOutParameter(cmd, "returnMessage", DbType.String, 250);


                        cmd.CommandTimeout = 700;
                        using (IDataReader rdr = db.ExecuteReader(cmd))
                        {
                           // int _rtncode = int.Parse(db.GetParameterValue(cmd, "returnCode").ToString());
                           // string _rtnstring= (db.GetParameterValue(cmd, "returnMessage").ToString());
                            // if (int.Parse(db.GetParameterValue(cmd, "returnCode").ToString()) != 200)
                            //  throw new Exception(db.GetParameterValue(cmd, "returnMessage").ToString());

                            while (rdr.Read())
                                    col.Add(new Attribute(rdr));

                        }
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                return col;
            }


    Sundari

    Monday, February 11, 2019 8:27 AM

Answers

  • I guess you're using Entlib. Why can't you use straight up ADO.NET, SQL Command objects, execute the stored procedure and get the out parameter from the stored procedure?

    https://www.aspsnippets.com/Articles/How-to-return-Output-parameter-from-Stored-Procedure-in-ASPNet-in-C-and-VBNet.aspx

    Below is simple code on how to do it even in the controller of a WebAPI, which  was taken from the above link. The controller should just call a private function using the below code as an example that is using  input and output parameters when calling a stored procedure.. 

    You can get further help by posting to the WebAPI forum in ASP.NET forums.

    https://forums.asp.net/

    string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constring))
        {
            using (SqlCommand cmd = new SqlCommand("GetFruitName", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@FruitId", int.Parse(txtFruitId.Text.Trim()));
                cmd.Parameters.Add("@FruitName", SqlDbType.VarChar, 30);
                cmd.Parameters["@FruitName"].Direction = ParameterDirection.Output;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
                lblFruitName.Text = "Fruit Name: " + cmd.Parameters["@FruitName"].Value.ToString();
            }

    Monday, February 11, 2019 11:06 AM