Solved

Returning an array back from my sp

Posted on 2006-06-12
10
317 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: 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!

 
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
 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

730 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