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.

Community
  • 1
  • 1
Jon Mitten
  • 1,965
  • 4
  • 25
  • 53
  • 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. http://www.codeproject.com/KB/tree/TreeViewWithDatabase.aspx – daniloquio Aug 30 '11 at 18:55
  • Note that the code for populating an ArrayList and populating a List<> will be mostly the same. – Greg Aug 30 '11 at 19:33

6 Answers6

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.

Greg
  • 23,155
  • 11
  • 57
  • 79
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();
    }
}
Matt Klepeis
  • 1,724
  • 1
  • 14
  • 25
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]);
}
Michael Reed Schmidt
  • 21
  • 2
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()));
        }
    }
}
nickytonline
  • 6,855
  • 6
  • 42
  • 76
  • What's that "someIntField"? The column name of the table returned by the stored procedure? – Jon Mitten Aug 30 '11 at 18:51
  • 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 Aug 30 '11 at 18:52
  • @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. – nickytonline Aug 30 '11 at 19:00
  • Depending on what you're doing, you may want to look into an ORM, http://stackoverflow.com/questions/146087/best-performing-orm-for-net – nickytonline Aug 31 '11 at 00:25
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"]);
}
Greg
  • 23,155
  • 11
  • 57
  • 79
PedroC88
  • 3,708
  • 7
  • 43
  • 77
  • 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.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 ` – Jon Mitten Aug 30 '11 at 19:01
  • Look at the edit. Also you should try parsing the content of r["VendorID"] into an int before adding it to the list. – PedroC88 Aug 30 '11 at 19:03
  • I'm getting a type mismatch somehow: `Error 29 Argument '1': cannot convert from 'object' to 'int'` – Jon Mitten Aug 30 '11 at 21:33
  • Also, another error: `Error 28 The best overloaded method match for 'System.Collections.Generic.List.Add(int)' has some invalid arguments` – Jon Mitten Aug 30 '11 at 21:34
  • I've done: `List WDSHEVendorList = new List(); foreach (DataRow datarow in GetAllWDSHEVendors().Rows) { //Loop through each row foreach (DataColumn column in GetAllWDSHEVendors().Columns) { WDSHEVendorList.Add(datarow[column]); } }` – Jon Mitten Aug 30 '11 at 21:35
  • Does the column `VendorID` actually exist? Does it actually contain an int? Also note that in your example you're creating an additional loop that could substantially increase the time it takes to complete. You can set a breakpoint on the `myList.Add()` line to see what does `r["VendorID"]` contain. – PedroC88 Aug 30 '11 at 21:48
  • The columns are `ParentVendorId` and `ChildVendorId` in the DataTable, right? They would be the same names as the columns in the stored procedure. – Jon Mitten Aug 30 '11 at 21:59
  • Please note I've got 2 `foreach` loops, one for row, one for column. – Jon Mitten Aug 30 '11 at 22:00
  • In the code I gave you, replace `VendorID` with the name of the column that contains the ID of the vendor as it is in the database, that is, the column that contains the ID you want in the list. The foreach for the columns SEEMS unnecessary. – PedroC88 Aug 30 '11 at 22:10
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

Community
  • 1
  • 1
Jon Mitten
  • 1,965
  • 4
  • 25
  • 53