none
How to pass DB output paramenter messga from WEB API to JSON 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 to my them using ADO .net into my WEBAPI controoller. From WEB API to Ajax call finally display on my html page .

    I'm unable to give link between these theree 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 notgetting 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


    • Edited by Lakshmi Sundari Monday, February 11, 2019 7:03 AM
    • Moved by pituachMVP Tuesday, February 12, 2019 12:37 PM Confirmed with the user that this question related to C#
    Monday, February 11, 2019 7:02 AM

All replies

  • Good day Lakshmi,

    This forum (Getting started with SQL Server) focuses in the SQL Server side, and it seems to me like your question has nothing to do with the SQL Server but with the application side. It sound like your best option is to ask the question in C#forum for example.

    If you want I can move your question to the C# forum or to another forum which deal with the technologies and language which you use. please confirm which forum

    In the meantime, you might want to take a look on the following post, where I explained exactly this requirement using PowerShell: How to control the output of a QUERIES including the PRINT statement and ERROR massages using PowerShell

    It might give you some ideas 


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    • Edited by pituachMVP Monday, February 11, 2019 3:17 PM
    Monday, February 11, 2019 3:16 PM
  • Yes Please move

    Sundari

    Tuesday, February 12, 2019 11:18 AM
  • Yes Please move

    Sundari

    Done, Good luck and have a great day

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Tuesday, February 12, 2019 12:38 PM
  • I don't understand what you're trying to do. Your code calls a sproc. You then enumerate through the rows and add them to a collection of Attribute? I hope this isn't your custom Attribute class (which it probably is) as that conflicts with the existing .NET type. You'll want to rename it to avoid issues. Are you trying to get this list of items back in addition to the output parameters? If so then create a type to store all the data. Something like this maybe.

    class MyData
    {
        public int Code { get; set; }
        public string Message { get; set; }
    
        public List<UserAttribute> Attributes { get; } = new List<UserAttribute>();
    }
    
    class UserAttribute
    {        
    }
    
    //And your updated method with the DB call removed
    public static MyData AdminSearch ( int pid, string email, string userId )
    {
        ...
    
        var data = new MyData();
    
        using (var rdr = db.ExecuteReader(cmd))
        {
            while (rdr.Read())
                data.Add(new UserAttribute(rdr));
        };
                  
        //Guessing on the syntax here because I don't use the EntLib data access library
        data.Code = db.GetParameterValue(cmd, "returnCode");
        data.Message = db.GetParameterValue(cmd, "returnMessage");
    
        return data;
    }
    If the return code and message are important than the above will work. However if they are returning the success or failure of the call (instead of throwing an exception) then you should probably look at the code to determine if something failed and if so then throw an exception instead of enumerating the results or trying to return it back to the caller.

    Note that this forum is not for web-related questions. The question you're asking here is related to DB stuff so we can answer that but as far as how to get this integrated into a web API or AJAX call you'll need to post in the ASP.NET forums.


    Michael Taylor http://www.michaeltaylorp3.net

    • Proposed as answer by Teige Gao Thursday, February 14, 2019 6:19 AM
    Tuesday, February 12, 2019 2:56 PM
    Moderator