how to get output parameter value using sqlDB.AddOutParameter function

Topics: Building and extending application blocks
Nov 6, 2008 at 2:13 PM
Hi Experts,
            I am using Microsoft enterprise Library in my project.
I have a stored procedure which contain an Output parameter. With the help of  "sqlDB.AddOutParameter" method, I am not able to get this parameter value in my Code behind.


Pls give me a solution that how can I get Output parameter value.

Regards,
Anuj Rathi
Nov 6, 2008 at 2:55 PM
Edited Nov 6, 2008 at 2:57 PM
Hi Anuj,

To retrive the value of the output parameter, use the GetParameterValue method of your database instance. Just like in the code below:

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand command = db.GetStoredProcCommand("GetCustomerID");

            string name = Console.ReadLine();

            db.AddInParameter(command, "@name", System.Data.DbType.String, name);

            db.AddOutParameter(command, "@CustID", System.Data.DbType.Int32, int.MaxValue);

            db.ExecuteNonQuery(command);


            int custId = (int)db.GetParameterValue(command, "@CustID");

            Console.WriteLine("CustomerID is : " + custId.ToString());
            Console.ReadLine();

 

Reference: http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic37700.aspx

Hope this Helps.


Valiant Dudan
Global Technology and Solutions
Avanade, Inc.
valiant.a.dudan@avanade.com

Nov 7, 2008 at 5:12 AM
Hi Dudan,
             Thanks for ur reply. Actually In my function, I have to return a table from database. I am using generic List<CLASS OBJECT>to get this Table & then return this generic L:ist to my Buisness Layer. & for this purpaose i am using

IDataReader objReader = sqlDB.ExecuteReader(dbCmd);

Then in awhile loop, List will be populated. Here problem is that List is populating sucessfully. But the output parameter is only a SINGLE ONE.

So now how can I return This output parameter value.

Regards,
Anuj Rathi

      
Nov 7, 2008 at 10:50 AM
Hi,

The output parameter returns only a single scalar value. If I understand your situation correctly, you are trying to retrive a resultset from your database and then transform it to a List<CLASS OBJECT>. In your case, you cannot use the output parameter since it has only one scalar value. You can use a regular sql statement with your defined criteria, ex: SELECT * FROM [Customers] WHERE [Name]='Foo'. And then use the IDataReader objReader = sqlDB.ExecuteReader(dbCmd); to construct your list and return it. 


Valiant Dudan
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com
Nov 7, 2008 at 12:24 PM
No Dear, I am doing right.

Actually, I have to return some records of a table (according to condition) & total no. of records in my table to show eg (10 out of 50 records)

In my List collection, I am fetching table records & for total count, I have passed a output parameter. In my Stored procedure, I set total no. of records to this output parameter.

Now after executing

sqlDB.ExecuteReader(dbCmd);

I populate my List via datareader & now want to get that output parameter here. According to ur reply, I used  "int custId = (int)db.GetParameterValue(command, "@CustID");"
but I am not able to get the value here. It is NULL.

pls suggect me.

Regards,
Anuj
Nov 8, 2008 at 2:17 AM
Hi Anuj, output parameters are not available until you close the datareader.  You may use ExecuteDataset in place of ExecuteReader but I don't know if you have a special need for using ExecuteReader.


Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com
Jan 10, 2011 at 11:36 AM
Edited Jan 10, 2011 at 12:11 PM

Hi Anuj,

Sarah is right,

using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    // Use reader and put your code here
                }
               
                //Close Reader
                dataReader.Close();

                if (dataReader.IsClosed)
                {
                    //Get you parameter values and use them as you like
                }
            }