Populate ArrayList from Stored Procedure result set

Question

I have a stored procedure that returns a set of integers. I need help populating an ArrayList of these results in a C# (VS 2005) web application.

I realize this is probably a very simple process, but I don't have the skills to it, nor the vocabulary to research it, apparently.

My assumptions are this DataTable is being populated properly:

public static DataTable GetAllVendors()
{
     OleDbCommand cmd = Data.GetCommand(Configuration.DatabaseOwnerPrefix + ".GetAllInformationAndHelpVendorIds", Connections.MyDbConnection);
     return Data.RunCommand(cmd).Tables[0];
 }

What I don't know is how to get the results back into an ArrayList or another datatype that may be evaluated with the Contains() method.

Edit: ArrayList is an older technology, and I'll take the advice of not using it. Thanks.

Unless you're still using .NET 1.1, you should not use ArrayList. Instead, use List<int>. — John Saunders
– John Saunders, Commented Aug 30, 2011 at 1:30
GetCommand is a method used to connect to the sql database and call a stored procedure. It was written by a previous developer for this CMS. — Jon Mitten
– Jon Mitten, Commented Aug 30, 2011 at 16:34

Asbjørn Ulsberg · Accepted Answer · 2011-09-05 13:00:37Z

4

What you should do is invoke ExecuteReader() on that cmd object, like so:

public static IEnumerable<int> GetAllVendors()
{
    using (var cmd = Data.GetCommand(Configuration.DatabaseOwnerPrefix + ".GetAllInformationAndHelpVendorIds", Connections.MyDbConnection))
    {
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                yield return reader.GetInt32(0);
            }
        }
    }
}

This assumes that the SQL will return a table whose first column (identified by the index 0 to the method GetInt32()) will be the ID you're looking for. If the SQL returns the IDs in another column, just adjust the index to the column you're expecting to find them in.

This solution also expects the command's connection to already be open. If it isn't, you can do cmd.Connection.Open() just before cmd.ExecuteReader().

edited Sep 5, 2011 at 13:00

answered Aug 29, 2011 at 23:56

Asbjørn Ulsberg

8,8403 gold badges48 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jon Mitten Over a year ago

do you mean ExecuteReader() ?

Mitch Wheat Over a year ago

while this fine looks fine in principle, I wouldn't want to be holding the reader and connection open ....

Asbjørn Ulsberg Over a year ago

@Mitch, the using statements ensures disposal of the command and reader which will close them both. Disposal of the connection is not done in OP's code, so I'm not doing it either. @Jon, yes, I've fixed the typos, sorry about that.

Mitch Wheat Over a year ago

@asbjornu: I'm well aware of that!

Aaron Bertrand · Accepted Answer · 2011-08-30 01:01:09Z

2

Depending on what percentage of the list you're going to exclude, how much other data comes along with it, how loaded the database is and how slow your network is, it is usually going to work out better if you filter at the source instead of pulling down a bunch of data just to discard it.

In SQL Server 2008, you could go the opposite way - pass your list into a table-valued parameter, and perform the filter on the server.

In SQL Server 2005, you could use slightly less efficient ways to simulate the same thing - pass a comma-separated list of values into the stored procedure, the stored procedure then parses the list and makes a table or directly joins against the source table, then the resultset is still filtered down instead of the whole query.

answered Aug 30, 2011 at 1:01

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

1 Comment

Jon Mitten Over a year ago

I will not be excluding any parts of the list. I need to be able to find all the values in the list using the Contains method, for visibility / access privileges based on a user ID.

Community · Accepted Answer · 2017-05-23 10:09:46Z

1

How about something like this:

public static IList GetAllVendors()
{
     OleDbCommand cmd = Data.GetCommand(Configuration.DatabaseOwnerPrefix + ".GetAllInformationAndHelpVendorIds", Connections.MyDbConnection);
     return Data.RunCommand(cmd).Tables[0].AsEnumerable().ToList();
}

More details here how-do-you-convert-a-datatable-into-a-generic-list

edited May 23, 2017 at 10:09

CommunityBot

11 silver badge

answered Aug 30, 2011 at 0:13

Dmitry Samuylov

1,5622 gold badges14 silver badges38 bronze badges

Collectives™ on Stack Overflow

Populate ArrayList from Stored Procedure result set

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related