Solved

Returning an array back from my sp

Posted on 2006-06-12
10
313 Views
Last Modified: 2012-06-27
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
Comment
Question by:watherton
  • 5
  • 4
10 Comments
 
LVL 4

Expert Comment

by:fffej78
ID: 16888650
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
 

Author Comment

by:watherton
ID: 16888887
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
 
LVL 9

Assisted Solution

by:msdixon
msdixon earned 100 total points
ID: 16889899
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
 
LVL 4

Accepted Solution

by:
fffej78 earned 400 total points
ID: 16891992
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
 

Author Comment

by:watherton
ID: 16893147
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 4

Expert Comment

by:fffej78
ID: 16893403
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
 

Author Comment

by:watherton
ID: 16893434
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
 
LVL 4

Expert Comment

by:fffej78
ID: 16893516
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
 

Author Comment

by:watherton
ID: 16893593
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
 

Author Comment

by:watherton
ID: 16893631
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now