• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

Returning an array back from my sp

Ok,

development base:

Visual Studio 2005
SQL 2005
c#

Question:

I have an n-tier project, where I need to return an array of userroles. I have the following:

protected CapitalIncentives.Users.UserRoles currentUserRoles = new CapitalIncentives.Users.UserRoles();
userManagement.GetRolesForUser(currentUser.UserID, out currentUserRoles);

// This is the code that is referenced above.
public struct UserRoles
    {
        public int userRoleId;

        public int UserRole
        {
            get { return userRoleId; }
        }
////


This is my business logic code:

public void GetRolesForUser(int userID, out UserRoles[] userRoles)
        {
            UserRoles returnedUserRoles = new UserRoles();

            try
            {
                using (UserDataAccess currentDataAccess = new UserDataAccess(CurrentDatabase, CurrentTransaction))
                {
                    currentDataAccess.UserGetRoles(userID, out userRoles);
                }
                userRoles = returnedUserRoles;
            }

            catch (Exception error)
            {
                HandleCriticalException(error);

            }

            userRoles = returnedUserRoles;
        }
         
    }

This is my data access tier:

  public void UserGetRoles(int userId, out UserRoles[] userRolesToGet)
        {
            userRolesToGet = null;
 
            int numberOfReturnedRecords = 0;

            Database db = CreateDatabase();

            try
            {

                if (userId <= 0)
                {
                    throw new DataException("The User Id must be provided to return it's details.");
                }

                using (DbCommand dbCommand = db.GetStoredProcCommand("usp_tblUserRole_sel"))
                {

                    db.AddInParameter(dbCommand, "UserID", DbType.Int32, userId);
                    //db.AddInParameter(dbCommand, "UserLogonID", DbType.String, userLogonId);

                    using (IDataReader returnedRecords = db.ExecuteReader(dbCommand))
                    {
                        if (CurrentTransaction == null)
                        {

                            returnedRecords.Read();
                            numberOfReturnedRecords = returnedRecords.GetInt32(0);

                            if (numberOfReturnedRecords > 0)
                            {
                                returnedRecords.NextResult();
                               
                                userRolesToGet = new UserRoles[numberOfReturnedRecords];

                                for (int recordNumber = 0; recordNumber < numberOfReturnedRecords; recordNumber++)
                                {
                                    returnedRecords.Read();
                                    userRolesToGet[recordNumber].userRoleId = returnedRecords.GetInt32((int)UserGetRoleFields.RoleID);
                                }
                            }

                             returnedRecords.Close();
                            returnedRecords.Dispose();
                        }

                    }
                }
            }

            catch (Exception error)
            {
                HandleCriticalException(error);


Can someone please help me to get this working, I am having trouble returning an array of roles.
            }
        }
0
watherton
Asked:
watherton
  • 5
  • 4
2 Solutions
 
fffej78Commented:
Is there a particular reason for the use of out parameters, and not  public UserRoles[] UserGetRoles(int userId ) ?

You can read about the specifics of arrays and "out" parameters at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/csref/html/vclrfpassingarraysusingrefoutpg.asp

I can't see a reason why yours wouldn't work.  What is the value you are getting back?  If it is null, then it is simply because your database opening and reading logic is never getting executed because the number of records is zero.  SO in short, as far as I can see there is nothing wrong with the way you are returning arrays, it is much more likely it's the logic that causes the array to be populated that is wrong.

Also, just a minor point, but the using block ensures that dispose is always called anyway, so returnRecords.dispose() is superflous.

0
 
wathertonAuthor Commented:
hi fffej78,

thanks for the quick repsonse.

I am recieving the following error messgae:

Error      1      Cannot implicitly convert type 'CapitalIncentives.Users.UserRoles' to 'CapitalIncentives.Users.UserRoles[]'      C:\Projects\Capital-Incentives\Development\CCSDEV v0.3\ObjectManagement\UserManagement.cs      55      29      ObjectManagement

i get this error message when I try and set the calling array with the returned array.

0
 
msdixonCommented:
i think you need to change it to this (but without being able to compile it i'm not positive this is the fix)...


public void GetRolesForUser(int userID, out UserRoles[] userRoles)
        {
            UserRoles[] returnedUserRoles;

            try
            {
                using (UserDataAccess currentDataAccess = new UserDataAccess(CurrentDatabase, CurrentTransaction))
                {
                    currentDataAccess.UserGetRoles(userID, out userRoles);
                }
                userRoles = returnedUserRoles;
            }

            catch (Exception error)
            {
                HandleCriticalException(error);

            }

            userRoles = returnedUserRoles;
        }
         
    }
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
fffej78Commented:
msdixon is mostly there.  I can't see the point of the "returnedUserRoles" variable, it doesn't do anything at all.  When you declare an "out" variable, you have to make sure it is definitely assigned to.

If you have compile errors, you should tell people in the question, makes lives a bit easier!  I think the corrected "GetRolesForUser" function below should work.

public void GetRolesForUser(int userID, out UserRoles[] userRoles)
{
    userRoles = null ; // ensure definite assigment
    try
    {
      using (UserDataAccess currentDataAccess = new UserDataAccess(CurrentDatabase, CurrentTransaction))
      {
          currentDataAccess.UserGetRoles(userID, out userRoles);
      }
    }
    catch (Exception error)
    {
      HandleCriticalException(error);
    }
}
0
 
wathertonAuthor Commented:
ok almost there,


I am now getting values back from the data reader, but it fails when it tries to assign the value to the array:


for (int recordNumber = 0; recordNumber < numberOfReturnedRecords; recordNumber++)
                                {
                                    returnedRecords.Read();
                                    userRolesToGet[recordNumber].userRoleId = returnedRecords.GetInt32((int)UserGetRoleFields.RoleID);
                                }
I have also tried

for (int recordNumber = 0; recordNumber < numberOfReturnedRecords; recordNumber++)
                                {
                                    returnedRecords.Read();
                                    userRolesToGet[recordNumber].userRoleId = returnedRecords.GetInt32(0);
                                }
I get the following error using both the above examples:

"Invalid attempt to read when no data is present."
0
 
fffej78Commented:
returnedRecords.Read();
numberOfReturnedRecords = returnedRecords.GetInt32(0);

This looks a bit dodgy to me.  If you return a number of records, why on earth would the number of returned records be the first integer?  I guess it depends on whatever stored procedure or SQL statements you are using.

I think you should have something like the following pseudo-code.  Basically read until you can read no more, then convert to an array at the end.

List<int> roleIDs= new LinkedList<int>(); // or some other expandable collection type
while ( returnedRecords.read() )
{
    roleIDs.add( returnedRecords.GetInt32( 0 ) );
}

userRoles = new UserRole[ records.size() ];

// Copy from the list into the array above.

0
 
wathertonAuthor Commented:
ok sorted this out.

I am now getting back my array of user roles - yippe - but, there's always a 'BUT'

when the program returns to userManagement.GetRolesForUser(currentUser.UserID, out currerntUserRoles) 'currentUserRoles is null and I can not perform any further checks based on my data.

I believe this is caused by the declaration:

protected CapitalIncentives.Users.UserRoles currentUserRoles = null;

I need to be able store my roles so that I can get at them from this page.

thanks for your help


0
 
fffej78Commented:
I think this is probably a naming conflict.  Your local parameter is hiding the currentUserRoles.  Simply one or the other and I'm sure it'll sort out.
0
 
wathertonAuthor Commented:
gents, gave myself a great big smack around the head, and it seemed to of dislodged something.

All sorted now.

Thanks for the prompts
0
 
wathertonAuthor Commented:
fff:

this is what I ended up with:

using (DbCommand dbCommand = db.GetStoredProcCommand("usp_tblUserRole_sel"))
                {

                    db.AddInParameter(dbCommand, "UserID", DbType.Int32, userId);
                    //db.AddInParameter(dbCommand, "UserLogonID", DbType.String, userLogonId);

                    using (IDataReader returnedRecords = db.ExecuteReader(dbCommand))
                    {
                        if (CurrentTransaction == null)
                        {

                            //returnedRecords.Read();
                            numberOfReturnedRecords = returnedRecords.FieldCount;

                            if (numberOfReturnedRecords > 0)
                            {
                                //returnedRecords.NextResult();
                               
                                userRolesToGet = new UserRoles[numberOfReturnedRecords];

                                for (int recordNumber = 0; recordNumber < numberOfReturnedRecords; recordNumber++)
                                {
                                    returnedRecords.Read();
                                    userRolesToGet[recordNumber].userRoleId = returnedRecords.GetInt32(0);
                                }
                            }


                           }

                    }
                }
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now