C# Read multiple rows from different columns sharing same Foreign key SQL

Question

I want an efficient way to retrieve all info that shares the same Foreign key in a table, and store the data in list/array.

I can read several rows from one column:

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

int idForeignKey = inputIdFkey //Implemented on the WebPage for testing purposes

List<string> result = new List<string>();
string oString = "Select Column from Table where foreignKey = @fKey";

conn.Open();
SqlCommand oCmd = new SqlCommand(oString, conn);
oCmd.Parameters.AddWithValue("@fKey", idForeignKey);
using (SqlDataReader oReader = oCmd.ExecuteReader())
{
    while (oReader.Read())
    {
        result.Add(oReader.GetString(0));
    }
}
conn.Close();

And I can read several columns if I am targeting one specific row:

int sqlData1;
int sqlData2;
int sqlData3;

string oString = "Select * from Table where TableID = @tId";
SqlCommand oCmd = new SqlCommand(oString, conn);
oCmd.Parameters.AddWithValue("@tId", 1001);
conn.Open();
using (SqlDataReader oReader = oCmd.ExecuteReader())
{
    while (oReader.Read())
    {
        sqlData1 = oReader["Row1"].ToString();
        sqlData2 = oReader["Row2"].ToString();
        sqlData3 = oReader["Row3"].ToString();
    }
}
conn.Close();

But I would love to be able to read all/specific data that has the same foreign key. So I want to be able to retrieve several rows, save them to a list, and retrieve several other row data from a different column that shares the same foreign key.

I imagine it to be something like this:

int idForeignKey = inputIdFkey //Implemented on the WebPage for testing purposes

List<int> intList = new List<int>();
List<string> stringList = new List<string>();
List<DateTime> dateList = new List<DateTime>();

string oString = "Select * from Table where ForeignKey = @fKey";

conn.Open();
SqlCommand oCmdSleep = new SqlCommand(oString, conn);
oCmdSleep.Parameters.AddWithValue("@fKey", idForeignKey);
using (SqlDataReader oReader = oCmdSleep.ExecuteReader())
{
    while (oReader.Read())
    {
        intList.Add(oReader["Column1"].GetDateTime(0));
        dstringList.Add(oReader["Column3"].GetDateTime(0));
        dateList.Add(oReader["Column4"].GetDateTime(0));
    }
}
conn.Close();

But this does not working... Please advice me

Answer 1

No need to give the name of the column, simply use the already established index:

int idForeignKey = inputIdFkey //Implemented on the WebPage for testing purposes

List<int> intList = new List<int>();
List<string> stringList = new List<string>();
List<DateTime> dateList = new List<DateTime>();

string oString = "Select * from Table where ForeignKey = @fKey";

conn.Open();
SqlCommand oCmdSleep = new SqlCommand(oString, conn);
oCmdSleep.Parameters.AddWithValue("@fKey", idForeignKey);
using (SqlDataReader oReader = oCmdSleep.ExecuteReader())
{
    while (oReader.Read())
    {
        intList.Add(oReader.GetDateTime(0));
        dstringList.Add(oReader.GetDateTime(3));
        dateList.Add(oReader.GetDateTime(4));
    }
}
conn.Close();

Here is a breakdown:

listVariable.Add(oReader.GetDataType("Index of column"));

This way you get to retrieve all row data for shared foreign key and the option to do so for as many columns as you want.