3

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 feedback 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.

Improve this question
2
  • 1
    Look at this for an example of the process of getting data from the database to IU controls, using generic lists in the middle. codeproject.com/KB/tree/TreeViewWithDatabase.aspx Commented Aug 30, 2011 at 18:55
  • Note that the code for populating an ArrayList and populating a List<> will be mostly the same. Commented Aug 30, 2011 at 19:33

6 Answers 6

Reset to default
2

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.

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

2 Comments

MSDN states for C#: No code example is currently available or this language may not be supported.
@Jon - What are you talking about?
2

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();
    }
}
Improve this answer

Comments

1

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]);
}
Improve this answer

Comments

1 
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()));
        }
    }
}
Improve this answer

4 Comments

What's that "someIntField"? The column name of the table returned by the stored procedure?
What is the cmd supposed to be before ExecuteReader()? When I implement it, I get the name 'cmd' does not exist in the current context. message.
@Jon Mitten - I added a full code example. someIntField is the field you're returning in your SP from some integer that you want to add to your list. I just called it that because I have no idea what you're returning. I'm also assuming that you're using SQLServer.
Depending on what you're doing, you may want to look into an ORM, stackoverflow.com/questions/146087/best-performing-orm-for-net
0

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"]);
}
Improve this answer

9 Comments

I get the following 3 errors :( Error 26 foreach statement cannot operate on variables of type 'System.Data.DataTable' because 'System.Data.DataTable' does not contain a public definition for 'GetEnumerator' C:\GFLD\trunk\CMS\CMS\CMS\InformationAndHelp.aspx.cs and Error 27 The best overloaded method match for 'System.Collections.Generic.List<int>.Add(int)' has some invalid arguments C:\GFLD\trunk\CMS\CMS\CMS\InformationAndHelp.aspx.cs and this one, which I can handle: Error 28 Argument '1': cannot convert from 'object' to 'int' C:\GFLD\trunk\CMS\CMS\CMS\InformationAndHelp.aspx.cs
Look at the edit. Also you should try parsing the content of r["VendorID"] into an int before adding it to the list.
I'm getting a type mismatch somehow: Error 29 Argument '1': cannot convert from 'object' to 'int'
Also, another error: Error 28 The best overloaded method match for 'System.Collections.Generic.List<int>.Add(int)' has some invalid arguments
I've done: List<int> WDSHEVendorList = new List<int>(); foreach (DataRow datarow in GetAllWDSHEVendors().Rows) { //Loop through each row foreach (DataColumn column in GetAllWDSHEVendors().Columns) { WDSHEVendorList.Add(datarow[column]); } }
|
0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.