Resolved Passing a list to Oracle Parameter

[Anonymous]

I am currently iterating my list items one by one and sending them to Oracle Parameter, but instead of sending one by one, how can I send the entire list in one go ? Can I achieve same results as I am getting from below code?

foreach (var country in countryList)
                {

                    OracleConnection conn = new OracleConnection(oradb);
                    OracleCommand cmd = new OracleCommand();


                    cmd.CommandText = "select r.region_name from HR.regions r inner join HR.countries c on r.region_id = c.region_id and c.COUNTRY_NAME = :country";
                    cmd.Parameters.Add(new OracleParameter("COUNTRY_NAME", country));
                    cmd.Connection = conn;
                    conn.Open();
                    Console.WriteLine("Connected to Oracle" + conn.ServerVersion);
                    OracleDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        string region = dr["REGION_NAME"].ToString();
                        region = country + ",It is " + region;
                        _OutputFile.Add(country, region);
                    }



                    // Close and Dispose OracleConnection object
                    conn.Close();
                    conn.Dispose();

                }

 

[jmcilhinney]

E.g.

var countryNames = Enumerable.Range(0, countryList.Count).Select(n => $":COUNTRY_NAME{n}");

cmd.CommandText = $"SELECT ... WHERE c.COUNTRY_NAME IN ({string.Join(", ", countryNames)})";

for(var i = 0; i < countryList.Count; i++)
{
    var countryName = countryList(i);
    
    cmd.Parameters.Add(new OracleParameter($":COUNTRY_NAME{i}", countryName));
}

 

[Anonymous]

In countryList it says "Method name expected".

 

[jmcilhinney]

Sorry, I wrote that freehand and I tend to forget and use VB syntax for indexing when I do that.

var countryName = countryList[i];

Mind you, that's something that you probably should have been able to work out for yourself with a bit of thought.

 

[NoUserHere]

You are 40 posts in, and that's long enough to be on the forums to know not to screenshot code. Please use the code tags button </> when posting code on the forums.