none
HOW TO return the ROWCOUNT from the Stored Procedure into C#, sql server 2005. ROWCOUNT always returns null in c# Please help! RRS feed

  • Question

  • Hi Guys,

    I have a C# code that connects to sql server 2005 where the sotred procedures are. Now I have written the following Stored procedure

    ALTER PROCEDURE [dbo].[VENUES_GETALL]
        @TOTAL_ROWS INT OUTPUT   
    AS

        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
    SELECT V.VENUE_ID, V.VENUE_NAME
    FROM VENUES V JOIN VEN_STATUS_HIST VS ON VS.ITEM_ID = V.VENUE_ID JOIN LOOK_UP LU ON LU.LOOK_UP_ID = VS.STATUS_LOOKUP
    WHERE VS.UPDATE_DATETIME = (SELECT MAX(UPDATE_DATETIME) FROM VEN_STATUS_HIST WHERE ITEM_ID = V.VENUE_ID)

    SELECT @TOTAL_ROWS = @@ROWCOUNT

    Now thid proceedure runs perfectly the way i want, it returns all the records plus the rowcount when i execute it in the query analyzer.

    DECLARE    @TOTAL_ROWS int

    EXEC [dbo].[VENUES_GETALL] @TOTAL_ROWS output

    SELECT    'Return Value' = @TOTAL_ROWS OR PRINT @TOTAL_ROWS

    But when i try and call it from the C# i get a null value from the parameter.

    Below is the C# code

    SqlParameter[] sp = null;
    sp = new SqlParameter[1];
                    sp[0] = new SqlParameter();
                    sp[0].ParameterName = "@TOTAL_ROWS";
                    sp[0].Direction = ParameterDirection.Output;
                    sp[0].SqlDbType = SqlDbType.Int;

    using
    (reader = SqlHelper.ExecuteReader(DataAccessBase.ConnectionString, CommandType.StoredProcedure,  EStoredProcedure.Venues.VENUES_GETALL.ToString(), sp))
         {                
         int i = 0;
              while (reader != null && reader.Read())
              {
              if (vResults.VenuesListLength == 0)                       
                   vResults.VenuesListLength = int.Parse(sp[0].Value.ToString()); // THE //VALUE COMES NULL INSTEAD OF TOTAL ROWS               
                            vResults.VenuesIdea = new Venue(reader);
                            i++;
                        }                   
                    }


    I have tried both using OUTPUT and RETURN but i jst cant get the fu*!@ng value.

    Any ideas why ????????



    Friday, August 29, 2008 3:58 PM

All replies


  • Still not sure how
    i can return it but i know why it returns null. As i am trying to return it back to the output parameter, it will only return once the reader is closed, so i have had to read all the values in to an ArrayList and close the reader and i get the rowcount output value, but now its useless because the arraylist.count can tell me how many there are.

    but it can hamper the performance if there are thousands of records because i will have to read them into the arraylist and then move it again into my array of object.

    I am sure there is another way, but dont know it yet, when i do i will post it. unless someone else has an idea.

    I have also tried to return it as a column just like you do in Oracle.

    SELECT @@ROWCOUNT as TOTAL_ROWS, V.VENUE_ID, V.VENUE_NAME, V.DATE_ADDED, V.MIN_AGE, V.MAX_AGE, V.CLIENT_REFERENCE, V.WEBSITE, V.EMAIL,
        LU.STATUS_NAME
        FROM VENUES V...

    When i run this in the query analyzer it works fine and if i put that in stored procedure with the SET NOCOUNT ON it returns 0 but when i remove the SET NOCOUNT ON it return the number that i want, Perfect!

    But, But, but, things change when i call this procedure from my code. Guess what it returns 0 even after removing the SET NOCOUNT ON. Jeez.

    So still trying to figure it out....

    - Noms


    Friday, August 29, 2008 4:41 PM
  • Paul,

    Thanks for the link, it says the same thing

     

    "When you use a DataReader object, you must close it or read to the end of the data before you can view the output parameters."

     

     

    And is it is mentioned that this behavious is by design.

     

    I also dont understand why you cant return the rowcount as a column when using a stored procedure as opposed to just using the query in the query analyzer. Strange!

    Sunday, August 31, 2008 2:27 AM
  • What if you return multiple resultsets and implement the NextResult method of the DataReader?

    Sunday, August 31, 2008 4:34 AM
  • Paul,

     

    Yea u can return multiple result sets but you have to read the first set to move on to the next one, which is fine but the problem is that you can only get the rowcount after the first select statement and you need to know the row count first to instantiate the array of objects, although you are returning both but the last one you are returning needs to be read first, and unfortunately there si no reader.PreviousResult(). Smile.

    Sunday, August 31, 2008 9:34 PM
  • OK, I took a look at your code but I don't see where you are using the row count. You mentioned using an ArrayList but I don't see where you need the row count for this class.

    Sunday, August 31, 2008 11:26 PM
  • Paul,

     

    Rowcount is required to instantiate the array of venue objects, look at the current code where i am now using an array list, first and then converting.

     

    ArrayList list = new ArrayList();

    using (reader = SqlHelper.ExecuteReader(DataAccessBase.ConnectionString, CommandType.StoredProcedure, (categoryID == -1 ? "dbo." + EStoredProcedure.Venues.VENUES_GETALL.ToString() : "dbo." + EStoredProcedure.Venues.VENUES_GETBY_CATIDORNAME.ToString()), sp))

    {

    while (reader != null && reader.Read())

    {

    Venue v = new Venue(reader);

    list.Add(v);

    }

    }

    if (list.Count > 0)

    {

    // Instantiate the array of Venue objects based on the List count

    vResults.VenuesListLength = list.Count;

    vResults.Venues = (Venue[])list.ToArray(typeof(Venue));

    }

    list.Clear();

     

    So if i knew the Rowcount i would not have to first add to the array i wud just instantiate the Venues array based on the rowcount. something like;

    vResults.VenuesListLength = Rowcount;

     

    Let me know if that's clear.

     

    Thanks

    Monday, September 1, 2008 12:58 PM
  • I'm not really sure what some of your objects are, such as Venue and vResults. Maybe you could clarify a bit more since some of the functionality is hidden from me.

     

    Also, I'm kind of wondering why you're filling an ArrayList and then moving it to a Venues array of an object (vResults).

    Wednesday, September 3, 2008 5:04 PM
  • Paul,

     

    Sorry if its a bit confusing. I'l explain

     

    ArrayList list = new ArrayList();

    using (reader = SqlHelper.ExecuteReader(DataAccessBase.ConnectionString, CommandType.StoredProcedure, (categoryID == -1 ? "dbo." + EStoredProcedure.Venues.VENUES_GETALL.ToString() : "dbo." + EStoredProcedure.Venues.VENUES_GETBY_CATIDORNAME.ToString()), sp))

    {

    while (reader != null && reader.Read())

    {

    // Venue is an object which has properties, which i am sure is clear

    Venue v = new Venue(reader);

    /// I am now adding Venue object in to the array list

    list.Add(v);

    }

    }

    if (list.Count > 0)

    {

    // vResult is a Venue Results object. A VenueResult object has an array of Venue objects

    // i.e. Venue[] Venues

    // And setting the VenuesListLength basically instantiates number of Venue objects/

    // ie.

    // internal int VenuesListLength

    // {

    // get {

    // if (_venueList == null)

    //        return 0;

    //   return _venueList.Length; }

    //set { _venueList = new Venue[value]; }

    //}

    // The above essentially gives me vResults.VenueIdea.VenueName etc

    // Hope this clarifies it, so i need to know the row count to assign that to the List length like i am doing below

    // using arraylist.count, instantiating the number of Venue array objects.

    vResults.VenuesListLength = list.Count;

    vResults.Venues = (Venue[])list.ToArray(typeof(Venue));

    }

    list.Clear();

     

    Thanks

     

    Wednesday, September 3, 2008 5:13 PM
  • The datareader has a .RecordsAffected function that will give you the equivalent response.

     

    A.D.T.

     

    Wednesday, September 3, 2008 5:17 PM
  • Hi BeforeAndAfter,

     

    RecordsAffected property can only be used when doing an INSERT, UPDATE AND DELETE to find the number of records affected and not useful for SELECT statements.

     

    Thanks

    Wednesday, September 3, 2008 5:21 PM
  • My mistake. You are just selecting.

     

    I'm with Paul on why you're choosing this approach. Since you're loading the resultset into memory, a datatable would give a lot more functionality including a recordcount. It can also be converted to the dataview that can be sorted and also used as a datasource for controls. Arrays are slowly becoming extinct.

     

    A.D.T.

     

    Wednesday, September 3, 2008 5:23 PM
  •  BeforeAndAfter1974 wrote:

    My mistake. You are just selecting.

     

    I'm with Paul on why you're choosing this approach. Since you're loading the resultset into memory, a datatable would give a lot more functionality including a recordcount. It can also be converted to the dataview that can be sorted and also used as a datasource for controls. Arrays are slowly becoming extinct.

     

    A.D.T.

     

     

    This is part of the business objects that i am creating and these business objects will be used to both power a webservice or a website. This is totally separate to my UI and is just part of the business logic and in business logic i would keep my logic and UI separate.

     

    Thanks

    Wednesday, September 3, 2008 5:29 PM
  • OK, bear with me because I may be missing something here but why are using using an intermediate ArrayList to populate your Array of Venue objects. Why not populate the Array directly? Or is it because you can't re-dimension an Array dynamically in C# and preserve the contents (this is possible in VB)?

     

    Could you use an ArrayList instead of an Array for your Venue class?

     

    Wednesday, September 3, 2008 5:50 PM
  •  Paul P Clement IV wrote:

    OK, bear with me because I may be missing something here but why are using using an intermediate ArrayList to populate your Array of Venue objects. Why not populate the Array directly? Or is it because you can't re-dimension an Array dynamically in C# and preserve the contents (this is possible in VB)?

     

    Could you use an ArrayList instead of an Array for your Venue class?

     

     

    Paul,

     

    C# has plenty of Collections classes that i can use. I think i will probably use List as its better than an ArrayList as it will not have to box and unbox each element and can have an impact on the performance, where as with the List you are specifying the type so the CLR does not need to box or unbox it.

     

    I was or am using an array of Object (Venue).

    Object[] obj = new Object[here i have to specify the size unlike arraylist or List]

     

    I am using this as I was not too sure how the SOAP request would be if another environment except .Net such as PHP, tries to consume the webservice, would it be easier for PHP to cast an array of object as i suppose they would not have List.

     

    But i suppose the SOAP would parse it like a normal array.

     

    Code Snippet

    HTTP/1.1 200 OK
    Content-Type: application/soap+xml; charset=utf-8
    Content-Length: length

    <?xml version="1.0" encoding="utf-8"?>
    <soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
      <soap12:Body>
        <GetVenueByIDResponse xmlns="http://tempuri.org/">
          <GetVenueByIDResult>
            <Venues>
              <Venue>
                <VenueID>

      </VenueID>

              </Venue>

              <Venue>

                <VenueID>

                </VenueID>

              </Venue>
            </Venues>
          </FetchUserByIDResult>
        </FetchUserByIDResponse>
      </soap12:Body>
    </soap12:Envelope>

     

     

    What do you think, Any experience with PHP Webservice consuming your .NET service. But that still does not answer the original issue as to how can the rowcount be returned Smile.

     

    -Noms

     

    Thursday, September 4, 2008 12:04 PM