multiple rows of a single table

Question

i am having a table with 3 col. viz id,profile_id,plugin_id.there can be more than 1 plugins associated with a single profile now how can i fetch from the database all the plugins associated with a profile_id which comes from the session variable defined in the login page when I try to apply the query for the same it returns the data with the plugin_id of the last record the query is as follows

SqlCommand cmd1 = new SqlCommand(
   "select plugin_id from profiles_plugins where profile_id=" +
    Convert.ToInt32(Session["cod"]), con);

    SqlDataReader dr1 = cmd1.ExecuteReader();
    if (dr1.HasRows)
    {
        while (dr1.Read())
        {
            Session["edp1"] = Convert.ToInt32(dr1[0]);
        }
    }
    dr1.Close();
    cmd1.Dispose();

Answer 1

I guess you want to save all the values in the session and here is how you do it:

SqlCommand cmd1 = new SqlCommand("select plugin_id from profiles_plugins where id=(select id from profiles_plugins where profile_id=" + Convert.ToInt32(Session["cod"]) + ")", con);

SqlDataReader dr1 = cmd1.ExecuteReader();
var yourList = new List<int>();
if (dr1.HasRows)
{
    while (dr1.Read())
    {
       yourList.Add(Convert.ToInt32(dr1[0]));

    }
}
Session["edp1"] = yourList;
dr1.Close();
cmd1.Dispose();

And when you read from the session you just type:

var yourList = (List<int>)Session["edp1"];

But you should really refactor your code, the code shouldn't manage data access and session handling in the same place.

Answer 2

there is an "error" because you assign during your while loop everytime to the same variable, thats why it looks like you get only the last row!

Answer 3

I would recommend you writing a separate function for retrieving values from the database. Also you should use parametrized queries to avoid SQL injection:

public IEnumerable<int> GetPluginIds(int profileId)
{
    using (var connection = new SqlConnection("SOME CONNECTION STRING"))
    using (var cmd = connection.CreateCommand())
    {
        connection.Open();
        cmd.CommandText = "SELECT plugin_id FROM profiles_plugins WHERE profile_id = @profile_id";
        cmd.Parameters.AddWithValue("@profile_id", profileId);
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                yield return reader.GetInt32(0);
            }
        }
    }
}

and then call the function like this:

// get the profile_id from session
int profileId = Convert.ToInt32(Session["cod"]);

// fetch associated plugin ids
int[] pluginIds = GetPluginIds(profileId).ToArray();

// store the resulting array into session
Session["pluginIds"] = pluginIds;