Solved

Returning an array back from my sp

Posted on 2006-06-12
10
314 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

895 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

12 Experts available now in Live!

Get 1:1 Help Now