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`. — John Saunders, Aug 30 '11 at 01: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, Aug 30 '11 at 16:34

Asbjørn Ulsberg · Answer 1 · 2011-09-05T13:00:37.707

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 05 '11 at 13:00

answered Aug 29 '11 at 23:56

Asbjørn Ulsberg

8,721
3
45
61

do you mean ExecuteReader() ? – Jon Mitten Aug 30 '11 at 16:36
1

while this fine looks fine in principle, I wouldn't want to be holding the reader and connection open .... – Mitch Wheat Aug 31 '11 at 00:01
@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. – Asbjørn Ulsberg Sep 05 '11 at 13:01
1

@asbjornu: I'm well aware of that! – Mitch Wheat Sep 05 '11 at 23:37

score 2 · Answer 2 · answered Aug 30 '11 at 01:01

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.

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. — Jon Mitten, Aug 30 '11 at 18:22

score 1 · Answer 3 · edited May 23 '17 at 10:09

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 '17 at 10:09

Community

1
1

answered Aug 30 '11 at 00:13

Dmitry Samuylov

1,554
2
14
37

Populate ArrayList from Stored Procedure result set

3 Answers3

Linked