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.
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().
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.
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
