C# List<int> populated from SQL Stored Procedure results

I'm using C# in VS 2005 (.NET 2.0) and SQL Studio 2005 on an older CMS made in the mid-'00s. I'm tasked with creating a new permission gate that allows only certain users to see certain parts of the site.

I need help populating a List list based on fee开发者_开发百科dback I got when I posted this question: Populate ArrayList from Stored Procedure result set

So, now, how do get get the values from the stored procedure into a List? I realize this is a novice question but I'm a novice...

Any help is greatly appreciated.

Assuming you are getting your results from a DataReader, all you have to do is read each row to add the value to a list.

List<int> ReadList(IDataReader reader)
{
    List<int> list = new List<int>();
    int column = reader.GetOrdinal("MyColumn");

    while (reader.Read())
    {
        list.Add(reader.GetInt32(column));
    }

    return list;
}

Remember to dispose of the DataReader when you are done with it.

You can try using the model located on this MSDN page under Using Parameters with a SqlCommand and a Stored Procedure. The example is shown here:

static void GetSalesByCategory(string connectionString, string categoryName)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        // Create the command and set its properties.
        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandText = "SalesByCategory"; //Stored Procedure Name
        command.CommandType = CommandType.StoredProcedure;

        // Add the input parameter and set its properties.
        SqlParameter parameter = new SqlParameter();
        parameter.ParameterName = "@CategoryName";
        parameter.SqlDbType = SqlDbType.NVarChar;
        parameter.Direction = ParameterDirection.Input;
        parameter.Value = categoryName;

        // Add the parameter to the Parameters collection. 
        command.Parameters.Add(parameter);

        // Open the connection and execute the reader.
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
                //Instead of displaying to console this is where you would add
                // the current item to your list
                Console.WriteLine("{0}: {1:C}", reader[0], reader[1]);
            }
        }
        else
        {
            Console.WriteLine("No rows found.");
        }
        reader.Close();
    }
}

it depends on how you have retreived the results reader? dataset? something else?

walk through the results using

foreach (int item in object...) {
List.Add(item);
}

or possibly (I dont remember the exact DataRow syntax off the top of my head...)

foreach (datarow row in object.table[0].rows) {
List.Add(row[0]);
}

IList<int> myInts = new List<int>();

using (IDbConnection connection = new SqlConnection("yourConnectionStringGoesHere"))
{
    using (IDbCommand command = new SqlCommand("spName", connection))
    {
        command.CommandType = CommandType.StoredProcedure;

        //command.Parameters.Add(...) if you need to add any parameters to the SP.
        connection.Open();

        using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
        {
            myInts.Add(Int32.Parse(reader["someIntField"].ToString()));
        }
    }
}

Since you already have the table the idea would be to iterate over that table while adding the IDs of the vendor into a list.

List<VendorID_Data_Type> myList = new List<VendorID_Data_Type>();
foreach(DataRow r in GetAllVendors().Rows)
{
    myList.Add(r["VendorID"]);
}

What I ended up doing is using a DataTable as an intermediary data type, which is populated by the stored procedure. Then, refactoring the DataTable as the data-source in a foreach loop, I populated the List. I needed to open a second question to get to this conclusion: 2-Column DataTable to List<int> .NET 2.0